Bug #68265 Inconsistent behavior "INT NOT NULL" column inserting "NULL"

• Submitted: 5 Feb 2013 1:55
• Modified: 5 Feb 2013 3:01
• Reporter: Duncan MacRae
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Linux (Ubuntu Linux 3.2.0-30-generic x86_64)
• CPU Architecture: Any

[5 Feb 2013 1:55] Duncan MacRae

Description:
I was executing multiple inserts using MySQLdb Python package. Noticed that you could get away with multiple inserts in a single transaction, passing NULL to a INT column which does not accept nulls, but inserting a single row is unacceptable?

How to repeat:
CREATE TABLE `int_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fk_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/* Works */
INSERT INTO int_test(fk_id)
VALUES (1), (NULL);

/* Fails */
INSERT INTO int_test(fk_id)
VALUES (NULL);

[5 Feb 2013 3:01] Duncan MacRae

My bad, found docs on multiple inserts.