MySQL Bugs: #5510: inserting Null in AutoIncrement primary key Column Fails

Bug #5510	inserting Null in AutoIncrement primary key Column Fails
Submitted:	10 Sep 2004 4:10	Modified:	17 Sep 2004 21:24
Reporter:	Jorge del Conde	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:	Any (All)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Issue a prepared insert statement, setting the primary key value to null (setNull()) on an auto increment column causes JConnect to throw the exception below.
This is also the behavior passing "null" straight on the command line.

It doesn't like the null. If I remove the pkey column from the insert, it does do the auto increment, and properly insert the row. I haven't tested if I can get the primary key value back... 

Is this working as intended, or a new delta between driver implementations?

The table definition is at the end of the message.

How to repeat:
If you have anything that proxies through the JDBC driver, you can
generate the error.

I forgot to mention this in my first comment:

Please note that this is also broken in a very similar way (same bug?)-

CREATE TABLE `nsdevices` (
  `DEVICEID` bigint(20) NOT NULL auto_increment,
  `NICKNAME` varchar(64) default NULL,
  `SYSOBJECTID` varchar(64) default NULL,
  `SYSDESCRIPTOR` varchar(255) default NULL,
  `STATUS` int(11) default NULL,
  `FIRMWARE` varchar(32) default NULL,
  `IP` varchar(32) default NULL,
  `BOOTPROM` varchar(80) default NULL,
  `SYSLOCATION` varchar(255) default NULL,
  `SYSNAME` varchar(255) default NULL,
  `SYSCONTACT` varchar(255) default NULL,
  `BASEMAC` varchar(32) default NULL,
  `CHASSISID` varchar(32) default NULL,
  `TIMELASTUPDATED` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `MONITORTYPE` int(11) default NULL,
  `POLLGROUP` int(11) default NULL,
  PRIMARY KEY  (`DEVICEID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you pass a NULL to the TIMELASTUPDATED column, you get the same sort of "Can not be Null" exception as you do in the primary key insert.

Your bug might be a general error, when NOT NULL and default are specified, then you can not pass a null on the insert.

Server bug:

mysql> set @a=null;
Query OK, 0 rows affected (0.02 sec)

mysql> prepare stmt1 from 'insert into nsdevices (DEVICEID) VALUES (?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt1 using @a;
ERROR 1048 (23000): Column 'DEVICEID' cannot be null
mysql>

same for timestamps

bk commit - 4.1 tree (konstantin:1.2018) BUG#5510

Fixed in 4.1.6