#1067 - Invalid default value

KeithK

Error: #1067 - Invalid default value
happens with deployment script from MySQL compare
Caused by statement like: MODIFY COLUMN `created_at` timestamp NULL DEFAULT 'NULL';

This statement should be:
MODIFY COLUMN `created_at` timestamp NULL DEFAULT NULL;

(without the single quotes around 2nd NULL)

OR

MODIFY COLUMN `created_at` timestamp NULL;

(without DEFAULT 'NULL')

Also, the addition of DEFAULT 'NULL' causes unnecessary difference in database comparisons. I'm not sure, but it seems DEFAULT 'NULL' is coming from a new version of MySQL Database.

How are people handling this issue?

thosky

I also experience this error. It occured after changing from Mysql version 14.14 to MariaDB 10.4.

It seems like Mysql Compare Bundle does not work with later versions of MariaDB. The comparison works but the deployment script does not work because it puts an extra quote around the default value which makes a syntax error.

Here an example of how it makes an syntax error at my server:

CREATE TABLE `database`.`test` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `title` varchar(50) NOT NULL DEFAULT ''0'',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

The DEFAULT ''0'' should be DEFAULT '0'

I hope the dev team will solve this error in the application.

GerardLakke

Same problem here after migrating to another host (and another MariaDB). 
Should be great when the developers should solve this.... 

andryan

Did anyone manage to find a workaround on this issue?

GerardLakke

Any succes? I was forced to switch to HeidiSQL (also excellent), but I still miss some of Redgate's great features.

andryan

GerardLakke said:

Any succes? I was forced to switch to HeidiSQL (also excellent), but I still miss some of Redgate's great features.

Nope.  I sent a support email, but MySQL-related tools are not supported.  Only community support is provided and there isn't even a community here.  If they are not planning to support it anymore, might as well release its source so it can be properly maintained by the community.

GerardLakke

I totally agree!