We have a C# tool to create a (MySQL) schema on a developer machine based on the (MySQL) schema used in production. This tool runs SHOW CREATE TABLE queries on the source connection, grab the resulting table creation scripts and run them locally.
I'm using MySQL 5.7.7 Community Server locally.
One of the creation script looks like this:
CREATE TABLE `agency` (
`id` int(11) unsigned NOT NULL,
`name` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
`url` varchar(512) CHARACTER SET ascii NOT NULL,
`location_path` varchar(10) CHARACTER SET ascii NOT NULL,
`email_pattern` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`generic_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` text COLLATE utf8_unicode_ci,
`zip_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`region` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`inserted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
When I run this query in MySQL Workbench, the table is successfully created.
However, when this query is executed on the destination connection by our C# tool, the following error occurs: Failure: Invalid default value for 'inserted_at'.
My MySQL Workbench is configured with the following SQL_MODE: TRADITIONAL,ALLOW_INVALID_DATES.
My MySQL Server instance is configured with the following sql-mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Here are the connection strings used by our C# tool:
Local connection string:
Server=localhost;Database=XXX;Uid=XXX;Pwd=XXX;AllowZeroDateTime=trueSource connection string:
Server=X.X.X.X;Database=XXX;Uid=XXX;Pwd=XXX;Use Compression=true;AllowZeroDateTime=true
Any idea appreciated!
