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

Bug #655954 reported by Patrick Crews on 2010-10-06
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
High
Andrew Hutchings
7.0
High
Andrew Hutchings

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.

Related branches

Patrick Crews (patrick-crews) wrote :
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...

Read more...

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

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

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
>

Patrick Crews (patrick-crews) wrote :

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.

Changed in drizzle:
importance: Undecided → High
milestone: none → 2010-10-11
status: Confirmed → Triaged
Changed in drizzle:
status: Triaged → Fix Committed
Andrew Hutchings (linuxjedi) wrote :

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

Lee Bieber (kalebral) on 2010-10-11
Changed in drizzle:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers