Bug #655954 “Drizzledump conversion of '0000-00-00' date values ...” : Bugs : Drizzle

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2010-10-06:

#1

Download full text (8.4 KiB)

MySQL dump output for the MySQL table (created on version 5.1)
CREATE TABLE `dump_table1` (
  `col_mediumint_not_null` mediumint(9) NOT NULL,
  `col_mediumint` mediumint(9) DEFAULT NULL,
  `col_smallint_not_null_key` smallint(6) NOT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `col_bigint` bigint(20) DEFAULT NULL,
  `col_tinyint_not_null` tinyint(4) NOT NULL,
  `col_enum_not_null_key` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') NOT NULL,
  `col_bigint_key` bigint(20) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_timestamp_key` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_enum` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL,
  `col_bigint_not_null_key` bigint(20) NOT NULL,
  `col_smallint_not_null` smallint(6) NOT NULL,
  `col_tinyint_key` tinyint(4) DEFAULT NULL,
  `col_timestamp_not_null` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_datetime` datetime DEFAULT NULL,
  `col_bigint_not_null` bigint(20) NOT NULL,
  `col_char_128` char(128) DEFAULT NULL,
  `col_mediumint_key` mediumint(9) DEFAULT NULL,
  `col_timestamp_not_null_key` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_timestamp` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_datetime_not_null_key` datetime NOT NULL,
  `col_char_10` char(10) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_char_128_not_null` char(128) NOT NULL,
  `col_tinyint` tinyint(4) DEFAULT NULL,
  `col_char_128_key` char(128) DEFAULT NULL,
  `col_char_128_not_null_key` char(128) NOT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_not_null` datetime NOT NULL,
  `col_mediumint_not_null_key` mediumint(9) NOT NULL,
  `col_smallint_key` smallint(6) DEFAULT NULL,
  `col_tinyint_not_null_key` tinyint(4) NOT NULL,
  `col_char_10_not_null_key` char(10) NOT NULL,
  `col_smallint` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_smallint_not_null_key` (`col_smallint_not_null_key`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`),
  KEY `col_enum_not_null_key` (`col_enum_not_null_key`),
  KEY `col_bigint_key` (`col_bigint_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_timestamp_key` (`col_timestamp_key`),
  KEY `col_bigint_not_null_key` (`col_bigint_not_null_key`),
  KEY `col_tinyint_key` (`col_tinyint_key`),
  KEY `col_mediumint_key` (`col_mediumint_key`),
  KEY `col_timestamp_not_null_key` (`col_timestamp_not_null_key`),
  KEY `col_datetime_not_null_key` (`col_datetime_not_null_key`),
  KEY `col_char_128_key` (`col_char_128_key`),
  KEY `col_char_128_not_null_key` (`col_char_128_not_null_key`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_mediumint_not_null_key` (`col_mediumint_not_null_key`),
  KEY `col_smallint_key` (`col_smallint_key`),
  KEY `col_tinyint_not_null_key` (`col_tinyint_not_null_key`),
  KEY `col_char_10_not_null_key` (`col_char_10_not_null_key`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `dump_table1` VALUES (-1445632,1013248,27139,8,3,8,'x...

MySQL dump output for the MySQL table (created on version 5.1)
CREATE TABLE `dump_table1` (
  `col_mediumint_not_null` mediumint(9) NOT NULL,
  `col_mediumint` mediumint(9) DEFAULT NULL,
  `col_smallint_not_null_key` smallint(6) NOT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `col_bigint` bigint(20) DEFAULT NULL,
  `col_tinyint_not_null` tinyint(4) NOT NULL,
  `col_enum_not_null_key` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') NOT NULL,
  `col_bigint_key` bigint(20) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_timestamp_key` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_enum` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL,
  `col_bigint_not_null_key` bigint(20) NOT NULL,
  `col_smallint_not_null` smallint(6) NOT NULL,
  `col_tinyint_key` tinyint(4) DEFAULT NULL,
  `col_timestamp_not_null` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_datetime` datetime DEFAULT NULL,
  `col_bigint_not_null` bigint(20) NOT NULL,
  `col_char_128` char(128) DEFAULT NULL,
  `col_mediumint_key` mediumint(9) DEFAULT NULL,
  `col_timestamp_not_null_key` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_timestamp` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `col_datetime_not_null_key` datetime NOT NULL,
  `col_char_10` char(10) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_char_128_not_null` char(128) NOT NULL,
  `col_tinyint` tinyint(4) DEFAULT NULL,
  `col_char_128_key` char(128) DEFAULT NULL,
  `col_char_128_not_null_key` char(128) NOT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_not_null` datetime NOT NULL,
  `col_mediumint_not_null_key` mediumint(9) NOT NULL,
  `col_smallint_key` smallint(6) DEFAULT NULL,
  `col_tinyint_not_null_key` tinyint(4) NOT NULL,
  `col_char_10_not_null_key` char(10) NOT NULL,
  `col_smallint` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_smallint_not_null_key` (`col_smallint_not_null_key`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`),
  KEY `col_enum_not_null_key` (`col_enum_not_null_key`),
  KEY `col_bigint_key` (`col_bigint_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_timestamp_key` (`col_timestamp_key`),
  KEY `col_bigint_not_null_key` (`col_bigint_not_null_key`),
  KEY `col_tinyint_key` (`col_tinyint_key`),
  KEY `col_mediumint_key` (`col_mediumint_key`),
  KEY `col_timestamp_not_null_key` (`col_timestamp_not_null_key`),
  KEY `col_datetime_not_null_key` (`col_datetime_not_null_key`),
  KEY `col_char_128_key` (`col_char_128_key`),
  KEY `col_char_128_not_null_key` (`col_char_128_not_null_key`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_mediumint_not_null_key` (`col_mediumint_not_null_key`),
  KEY `col_smallint_key` (`col_smallint_key`),
  KEY `col_tinyint_not_null_key` (`col_tinyint_not_null_key`),
  KEY `col_char_10_not_null_key` (`col_char_10_not_null_key`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `dump_table1` VALUES (-1445632,1013248,27139,8,3,8,'x',4,'2006-04-17 19:30:58',1,'2009-05-15 21:00:00','f',4,4,3,'0000-00-00 00:00:00','0000-00-00 00:00:00',0,'IVVHM',7280640,'2001-09-04 21:00:00','2004-10-20 21:00:00','2003-08-26 00:00:00','n',-1131610112,'f',4,'go','b',NULL,'2001-06-08 05:09:46',4783104,NULL,6,'v',3);
INSERT INTO `dump_table1` VALUES (4848128,NULL,7,3,5,-97,'o',-4817162751426166784,'2004-05-10 04:03:35',2,'2003-09-27 21:00:00','x',5,9,53,'2008-04-17 21:00:00','2002-11-07 00:00:00',1847601747128745984,'WAZGE',3004672,'2001-06-10 21:00:00','2002-07-15 22:50:02','0000-00-00 00:00:00','it',NULL,'BZLEH',-20,'good','at',NULL,'2007-09-13 00:00:00',2887680,-9293,62,'LKXKB',NULL);
INSERT INTO `dump_table1` VALUES (9,NULL,-17258,-754122752,NULL,0,'v',2110499375376498688,'0000-00-00 00:00:00',3,NULL,'f',1401745384019066880,13096,7,'2000-09-16 21:00:00','2007-09-04 00:00:00',1605814742134292480,'d',NULL,'2002-06-11 08:32:41',NULL,'0000-00-00 00:00:00','o',-1274347520,'ROAMX',7,'yes','IHQEK',806813696,'2009-12-02 18:07:27',5770752,-721,-112,'b',NULL);
INSERT INTO `dump_table1` VALUES (7324160,NULL,4,1615462400,9,8,'d',5592344837287313408,'0000-00-00 00:00:00',4,'0000-00-00 00:00:00','k',6,9,-24,'0000-00-00 00:00:00',NULL,-566327653141839872,'r',-8127232,'2009-11-10 08:17:56','2006-11-09 21:00:00','0000-00-00 00:00:00','f',427556864,'there',0,'the','b',NULL,'2008-11-02 08:58:58',-7484416,-429,61,'o',9258);
INSERT INTO `dump_table1` VALUES (6,3,-16214,-1163264000,NULL,3,'d',NULL,'0000-00-00 00:00:00',5,'0000-00-00 00:00:00','n',7733243510148562944,11463,NULL,'2009-12-15 14:47:16',NULL,5300455286438363136,'didn\'t',4,'2006-11-03 14:16:16','0000-00-00 00:00:00','2002-01-08 18:32:07','f',5,'JJAAP',79,'CSCVP','oh',3,'2003-12-28 00:00:00',275456,NULL,1,'m',NULL);
INSERT INTO `dump_table1` VALUES (8,8084480,9,1768947712,2,-45,'g',4584101470709743616,NULL,6,'2000-05-06 21:00:00','d',-4701758010974797824,6,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00',8,'h',3,'2005-12-09 21:06:51',NULL,'0000-00-00 00:00:00','going',-1234436096,'XUAJR',-70,'time','KTWWG',-1666187264,'0000-00-00 00:00:00',3118848,12000,-47,'look',4284);
INSERT INTO `dump_table1` VALUES (-4323072,NULL,4,7,3772327637876211712,-76,'j',-5822591368236630016,'2006-05-20 00:18:52',7,NULL,'t',1,0,47,'2000-05-22 16:31:23',NULL,0,'PHLEZ',0,'2007-07-09 05:32:49','0000-00-00 00:00:00','2002-02-14 00:00:00','mean',NULL,'one',-123,'v','MXVXT',NULL,'2006-03-25 00:00:00',-5732608,0,0,'JSFQL',3);
INSERT INTO `dump_table1` VALUES (-5080064,NULL,8,1936392192,NULL,-113,'a',NULL,'2000-12-22 01:25:23',8,NULL,'l',8426234902810198016,5,8,'2007-04-07 21:00:00','0000-00-00 00:00:00',-5481162221486604288,'right',0,'2000-01-11 11:59:31','0000-00-00 00:00:00','2008-11-16 00:00:00','he\'s',7,'IJYPK',NULL,'here','GOEID',-430309376,'0000-00-00 00:00:00',4492800,-15369,-5,'POGPF',3);
INSERT INTO `dump_table1` VALUES (-680192,-7694336,3,5,2086574002356092928,86,'m',6,'0000-00-00 00:00:00',9,'2003-01-10 21:00:00','r',-3817082159173206016,9,6,'2003-04-22 21:00:00','0000-00-00 00:00:00',9,'VWQFM',NULL,'2009-08-17 21:00:00','2002-01-12 11:01:45','2000-12-14 00:00:00','OGNJD',NULL,'say',46,'that\'s','g',-703266816,'2007-11-18 00:00:00',1,5,8,'VTFLV',32414);
INSERT INTO `dump_table1` VALUES (9,2,0,8,8792152372534050816,9,'m',7,'2008-07-18 01:15:56',10,'2002-05-13 21:00:00','q',-1739796831048564736,18742,NULL,'2003-01-26 04:08:44',NULL,1300132917426520064,'MLEHZ',-6183680,'2000-08-11 21:00:00','2001-07-12 07:46:37','2003-11-09 08:15:38','g',NULL,'b',7,'LNLUI','OIMRW',-260308992,'2005-09-21 04:37:15',4,NULL,-93,'is',3);
INSERT INTO `dump_table1` VALUES (2,9,-10093,880148480,NULL,1,'e',NULL,'0000-00-00 00:00:00',11,'2000-07-31 21:00:00','l',-1203305525438054400,-26885,5,'2000-08-13 21:00:00','0000-00-00 00:00:00',5050786982096011264,'this',2011136,'0000-00-00 00:00:00','0000-00-00 00:00:00','2008-12-17 00:00:00','VYVBI',NULL,'q',89,'if','YTUNQ',9,'2008-02-07 19:27:42',-6970368,-30993,4,'f',-7788);
INSERT INTO `dump_table1` VALUES (3,4,22401,2052653056,NULL,8,'o',NULL,'2005-02-07 00:00:00',12,NULL,'v',1,18882,NULL,'0000-00-00 00:00:00',NULL,5794443870565564416,'o',NULL,'2007-07-07 23:51:22','2003-11-06 01:59:50','2006-08-28 09:48:16','him',NULL,'GBPMQ',5,'t','q',4,'0000-00-00 00:00:00',5859584,NULL,7,'q',9);
INSERT INTO `dump_table1` VALUES (0,3353856,-24434,4,-474566810734166016,8,'w',-64176294690029568,'2007-04-25 00:00:00',13,'0000-00-00 00:00:00','i',8365154832863985664,20973,94,'0000-00-00 00:00:00',NULL,-8546987667819069440,'q',-3834112,'2007-03-01 21:00:00','2004-04-25 12:55:43','2003-09-07 00:00:00','had',NULL,'what',7,'e','w',918487040,'0000-00-00 00:00:00',2,7,-109,'k',20552);
INSERT INTO `dump_table1` VALUES (6,NULL,-2286,1097072640,2348064255720292352,-30,'h',NULL,'0000-00-00 00:00:00',14,'2008-07-16 21:00:00','k',4764245455804563456,16488,NULL,'2008-07-13 21:00:00','2004-10-11 06:31:14',4251116573261037568,'BQOEU',NULL,'2007-04-21 21:00:00','2006-05-27 02:46:38','2001-11-13 00:00:00','VURBR',4,'CBKHA',NULL,'k','o',7,'2003-03-26 00:10:15',6,-15716,76,'RWMPK',-7649);
INSERT INTO `dump_table1` VALUES (-6255104,-407296,-15615,275447808,-8195143946930749440,124,'d',0,'0000-00-00 00:00:00',15,'2009-03-22 23:48:35','p',-2458965396544290816,2,NULL,'0000-00-00 00:00:00','2005-01-05 20:34:15',2,'o',-1428992,'2003-01-25 20:27:56','2007-10-10 21:00:00','0000-00-00 00:00:00','the',-1924988928,'you\'re',-4,'but','MECQI',NULL,'2005-02-25 00:00:00',1384704,1,-24,'we',NULL);

Changed in drizzle:
assignee:	nobody → Andrew Hutchings (linuxjedi)
status:	New → Confirmed

Revision history for this message

Andrew Hutchings (linuxjedi) wrote on 2010-10-06:

#2

is this using latest trunk? It should have been fixed as part of bug #653300 merged 18 hours ago

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2010-10-06: Re: [Bug 655954] Re: Drizzledump conversion of '0000-00-00' date values to NULL can be problematic on NOT NULL columns

#3

I'll refresh my tree and make sure. Will update / close the bug as needed.

On Wed, Oct 6, 2010 at 4:27 PM, Andrew Hutchings
<email address hidden>wrote:

> is this using latest trunk? It should have been fixed as part of bug
> #653300 merged 18 hours ago
>
> --
> Drizzledump conversion of '0000-00-00' date values to NULL can be
> problematic on NOT NULL columns
> https://bugs.launchpad.net/bugs/655954
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud Infrastructure and Web
> Applications: Confirmed
>
> Bug description:
> Drizzledump converts the value '0000-00-00' into NULL when it encounters
> them in a MySQL database, however, this can cause problems when we do this
> conversion on a NOT NULL column:
>
> When attempting to migrate the attached MySQL table (provided in MySQL dump
> form) to Drizzle via drizzledump, we see errors like:
> Error executing query: Column 'col_datetime_not_null_key' cannot be null
> Error executing query: Column 'col_datetime_not_null' cannot be null
> Error executing query: Column 'col_datetime_not_null_key' cannot be null
> Error executing query: Column 'col_datetime_not_null_key' cannot be null
>
> And unpopulated tables.
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/655954/+subscribe
>

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2010-10-06:

#4

Just retested with a fresh pull of lp:drizzle and am still seeing this.
It appears that the NOT NULL isn't being disabled as noted in the bug#653300 report.

Andrew Hutchings (linuxjedi) on 2010-10-06

Changed in drizzle:
importance:	Undecided → High
milestone:	none → 2010-10-11
status:	Confirmed → Triaged

Andrew Hutchings (linuxjedi) on 2010-10-11

Changed in drizzle:
status:	Triaged → Fix Committed

Revision history for this message

Andrew Hutchings (linuxjedi) wrote on 2010-10-11:

#5

MySQL date/time columns in schemas were not converted correctly if there was no default value.

Lee Bieber (kalebral-deactivatedaccount) on 2010-10-11

Changed in drizzle:
status:	Fix Committed → Fix Released

Drizzle

Drizzledump conversion of '0000-00-00' date values to NULL can be problematic on NOT NULL columns

Bug Description

Related branches

Other bug subscribers

Related blueprints

Remote bug watches

Affects		Status	Importance	Assigned to	Milestone
	Drizzle	Fix Released	High	Andrew Hutchings	Drizzle 2010-10-11
	7.0	Fix Released	High	Andrew Hutchings	Drizzle 2010-10-11