| Subject: | strict mode breaks deployment of null timestamps |
MySQL allows null timestamp columns. However, attempting to deploy
these while strict mode is in place causes an error. Example code
follows...
# SQL:
create table if not exists shop_item (
...
updated timestamp null default
null,
...
);
# MySQL:
Show quoted text
mysql> desc shop_item;
+--------------+--------------+------+-----+-------------------+--------
--------+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+-------------------+--------
--------+
...
| updated | timestamp | YES | | NULL |
|
...
+--------------+--------------+------+-----+-------------------+--------
--------+
# DBIC::S::L output:
package ShinyCMS::Schema::Result::ShopItem;
...
=head2 updated
data_type: 'timestamp'
datetime_undef_if_invalid: 1
is_nullable: 1
...
__PACKAGE__->add_columns(
...
"updated",
{
data_type => "timestamp",
datetime_undef_if_invalid => 1,
is_nullable => 1,
},
...
);
# Failure to ->deploy() :
denny@serenity:~/code/ShinyCMS$ bin/database/deploy-schema
DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI
Exception: DBD::mysql::db do failed: Invalid default value for 'updated'
[for Statement "CREATE TABLE `shop_item` (
`id` integer NOT NULL auto_increment,
`product_type` integer NOT NULL,
`name` varchar(200),
`code` varchar(100),
`description` text,
`image` varchar(200),
`price` decimal(9, 2) NOT NULL DEFAULT 0.00,
`added` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp,
`hidden` tinyint DEFAULT 0,
`discussion` integer,
INDEX `shop_item_idx_discussion` (`discussion`),
INDEX `shop_item_idx_product_type` (`product_type`),
PRIMARY KEY (`id`),
UNIQUE `product_code` (`code`),
CONSTRAINT `shop_item_fk_discussion` FOREIGN KEY (`discussion`)
REFERENCES `discussion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shop_item_fk_product_type` FOREIGN KEY (`product_type`)
REFERENCES `shop_product_type` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB"] at bin/database/deploy-schema line 32
(running "CREATE TABLE `shop_item` (
`id` integer NOT NULL auto_increment,
`product_type` integer NOT NULL,
`name` varchar(200),
`code` varchar(100),
`description` text,
`image` varchar(200),
`price` decimal(9, 2) NOT NULL DEFAULT 0.00,
`added` timestamp NOT NULL DEFAULT current_timestamp,
`updated` timestamp,
`hidden` tinyint DEFAULT 0,
`discussion` integer,
INDEX `shop_item_idx_discussion` (`discussion`),
INDEX `shop_item_idx_product_type` (`product_type`),
PRIMARY KEY (`id`),
UNIQUE `product_code` (`code`),
CONSTRAINT `shop_item_fk_discussion` FOREIGN KEY (`discussion`)
REFERENCES `discussion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shop_item_fk_product_type` FOREIGN KEY (`product_type`)
REFERENCES `shop_product_type` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB") at bin/database/deploy-schema line 32
# Note 1: the SQL in the above error message does not match the
'nullable' state seen in all the previous stages. Specifically, it
doesn't include the desired 'null default null' for the 'updated'
column.
# Note 2: the SQL in the above error message, if pasted into the MySQL
command-line client, does not throw an error - even if TRADITIONAL mode
is enabled first. Nor does creating a null timestamp column as
specified in the first SQL fragment.