select @@sql_mode; DROP TABLE IF EXISTS testautoinc; SELECT "USING UNSIGNED TINYINT" AS "[DATATYPE]"; CREATE TABLE `testautoinc` ( `id` tinyint unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB; INSERT INTO `testautoinc` VALUES (253,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; /* Why is it Without the ON DUPLICATE KEY UPDATE clause, it errors appropriately */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1); /* Test an insert that should duplicate the existing 2013-12-16 row */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* Notice the wrong row was updated! */ SELECT * FROM `testautoinc`; DROP TABLE IF EXISTS testautoinc; SELECT "USING UNSIGNED SMALLINT" AS "[DATATYPE]"; CREATE TABLE `testautoinc` ( `id` smallint unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB; INSERT INTO `testautoinc` VALUES (65533,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; /* Why is it Without the ON DUPLICATE KEY UPDATE clause, it errors appropriately */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1); /* Test an insert that should duplicate the existing 2013-12-16 row */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* Notice the wrong row was updated! */ SELECT * FROM `testautoinc`; DROP TABLE IF EXISTS testautoinc; SELECT "USING UNSIGNED MEDIUMINT" AS "[DATATYPE]"; CREATE TABLE `testautoinc` ( `id` mediumint unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB; INSERT INTO `testautoinc` VALUES (16777213,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; /* Why is it Without the ON DUPLICATE KEY UPDATE clause, it errors appropriately */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1); /* Test an insert that should duplicate the existing 2013-12-16 row */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* Notice the wrong row was updated! */ SELECT * FROM `testautoinc`; DROP TABLE IF EXISTS testautoinc; SELECT "USING UNSIGNED INTEGER" AS "[DATATYPE]"; CREATE TABLE `testautoinc` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB; INSERT INTO `testautoinc` VALUES (4294967293,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; /* Why is it Without the ON DUPLICATE KEY UPDATE clause, it errors appropriately */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1); /* Test an insert that should duplicate the existing 2013-12-16 row */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* Notice the wrong row was updated! */ SELECT * FROM `testautoinc`; DROP TABLE IF EXISTS testautoinc; SELECT "USING UNSIGNED BIGINT" AS "[DATATYPE]"; CREATE TABLE `testautoinc` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB; INSERT INTO `testautoinc` VALUES (18446744073709551613,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT * FROM `testautoinc`; /* will produced an error 1467 */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* will update the expected record id */ INSERT INTO `testautoinc` VALUES (18446744073709551614,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* will update the expected record id without considering the uniqueness of column date (bug #58637) */ INSERT INTO `testautoinc` VALUES (18446744073709551614,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; /* will add the record as it's boundary of the assigned data type, BIGINT UNSIGNED */ INSERT INTO `testautoinc` VALUES (18446744073709551615,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; SELECT* from testautoinc; /* now on its boundary and produced an error */ INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1;