Column 'id' cannot be null

Databases
1

ok, I know the obvious reason is to set the id column to auto-increment but what if it is already set to that? We have mysql 5.x and I am perplexed



Invalid SQL: INSERT INTO usertbl (id,OrgId,InstId,userType,timein) VALUES(NULL, '42824149', '12345678', '3','1229648361');
MySQL Error : Column 'id' cannot be null
Error Number : 1048
2

I seem to recall that because auto_increment is built by MySQL, that you don’t put an entry for that column. That means, it should be like this



 INSERT INTO usertbl (OrgId,InstId,userType,timein) VALUES( '42824149', '12345678', '3','1229648361');

hth

bazz

3

Sahajin, please do a SHOW CREATE TABLE for your table

bazz, you’re right, it should work without mentioning the auto_increment, but it should also work the way it was

and by the way, please don’t make strings out of numbers needlessly :wink:

4

Thanks,

r937, I don’t know what they will be, in this example they are numbers, but the next one could be letters as well.

I will get the table as soon as the admin gets online

5

I do have another table that it happens to,


mysql> explain sessions;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| session_id      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| session         | varchar(255)     | NO   | MUL | NULL    |                |
| session_expires | int(11) unsigned | NO   |     | 0       |                |
| session_data    | text             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

The query is this

$query = “INSERT INTO sessions (session_id,session,session_expires,session_data)
VALUES(NULL,‘$sessID’, $newExp, ‘$sessData’)”;

fails on session_id as well. “Cannot be NULL”

6

well, of course it works on that table, but that’s not the table you’re having the trouble with

:slight_smile:

7

no, it doesn’t work on that table, it has the same problem.

8

oh, okay

well, would you mind doing a SHOW CREATE TABLE please (not a DESCRIBE)

:slight_smile:

9

yeah, I will get that to when he comes online :slight_smile:

10

well, what about the sessions table

the reason i wanted to see a SHOW CREATE TABLE is so that i can test it on your exact table specs

see, i made one up and it works fine –

CREATE TABLE sessions
( session_id      INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, session         VARCHAR(255)     NOT NULL
, session_expires INTEGER UNSIGNED NOT NULL
, session_data    TEXT
);
INSERT
  INTO sessions
     ( session_id
     , session
     , session_expires
     , session_data )
VALUES
     (  NULL
     , 'qwerty'
     , 12345
     , 'qwerty qwerty qwerty ' )
;
SELECT * FROM sessions;
11

yeah, every once in a while it fails. which is the perplexing part. On my site it works perfect, never fails, on another server it fails like 2 out of 8 times.

12

sorry for the delay


CREATE TABLE `usertbl` (
  `id` int(11) NOT NULL,
  `OrgId` varchar(255) default NULL,
  `InstId` varchar(255) default NULL,
  `userType` tinyint(1) default '0',
  `timein` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `sessions` (
  `session_id` int(11) unsigned NOT NULL auto_increment,
  `session` varchar(255) NOT NULL,
  `session_expires` int(11) unsigned NOT NULL default '0',
  `session_data` text,
  PRIMARY KEY  (`session_id`),
  KEY `session` (`session`)
) ENGINE=MyISAM AUTO_INCREMENT=194 DEFAULT CHARSET=latin1

I see why it doesn’t work, it doesn’t have auto-increment like I was told. I guess it will be fixed when he does it

13

there had to be an explanation, and no auto_increment covers it

:slight_smile:

14

so if it has auto-increment why would it do it? I have seen session table do it while it had auto-increment on it.

15

no idea