Bug #10113 Using INSERT ... ON DUPLICATE .. with field timestamp

• Submitted: 23 Apr 2005 14:53
• Modified: 24 Apr 2005 18:12
• Reporter: Rudenko Ilya
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11-log
• OS: Linux (Fedora Core 3)
• CPU Architecture: Any

[23 Apr 2005 14:53] Rudenko Ilya

Description:
Using INSERT ... ON DUPLICATE .. with undefined in insert field timestamp will insert 0000-00-00 00:00:00

Try to execute sql below, last insert in table cvs_log will produce row with 0000-00-00 00:00:00 value in column dtime

How to repeat:
CREATE TABLE `cvs_lock` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `table_name` char(50) collate cp1251_ukrainian_ci NOT NULL default '',
  `edit_id` int(10) unsigned NOT NULL default '0',
  `admin_id` int(10) unsigned NOT NULL default '0',
  `dtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `table_name` (`table_name`,`edit_id`,`admin_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

INSERT INTO `cvs_lock` VALUES (9, 'site_structure', 99, 1, '2005-04-23 17:40:21');

CREATE TABLE `cvs_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `table_name` char(50) collate cp1251_ukrainian_ci NOT NULL default '',
  `edit_id` int(10) unsigned NOT NULL default '0',
  `admin_id` int(10) unsigned NOT NULL default '0',
  `lock_id` int(10) unsigned NOT NULL default '0',
  `dtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `table_name` (`table_name`,`edit_id`,`admin_id`,`lock_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

INSERT INTO `cvs_log` VALUES (1, 'site_structure', 99, 1, 1, '2005-04-23 17:28:45');
INSERT INTO `cvs_log` VALUES (2, 'site_structure', 109, 1, 2, '2005-04-23 17:11:05');
INSERT INTO `cvs_log` VALUES (3, 'site_structure', 99, 1, 3, '2005-04-23 17:30:28');
INSERT INTO `cvs_log` VALUES (4, 'site_structure', 109, 1, 4, '2005-04-23 17:30:50');
INSERT INTO `cvs_log` VALUES (5, 'site_structure', 99, 1, 6, '2005-04-23 17:32:22');
INSERT INTO `cvs_log` VALUES (6, 'site_structure', 99, 1, 8, '2005-04-23 17:34:06');

INSERT INTO cvs_log (table_name, edit_id, admin_id, lock_id)
SELECT table_name, edit_id, admin_id, id
FROM cvs_lock
WHERE 
table_name='site_structure'
AND admin_id='1'
AND edit_id='99'
ON DUPLICATE KEY UPDATE dtime=NULL

[24 Apr 2005 18:12] Jorge del Conde

I was unable to reproduce this bug using 4.1.12 from bk