ID #1164

Problem with string default '' (empty string) and Schema Synchronisation


You experience that Schema Sync finds no difference for a string type declared NOT NULL default '' (empty string) and NOT NULL (with no default).

This is happening because SHOW FULL FIELDS statement does not return proper information. Try simply to create two tables like this

CREATE TABLE ´t1´ (´id´ varchar(20) NOT NULL);
CREATE TABLE ´t2´ (´id´ varchar(20) NOT NULL default '');

And execute SHOW FULL FIELDS FROM .. against both tables. 

The most recent server versions at the time of writing (including 5.0.67 and 5.1.30) will return NULL in the 'default' column for the first table but older servers (like 4.1.22 and 5.0.45) will return an empty string in the 'default' column for both. Consequently SQLyog Schema Sync cannot distinguish the default setting for the two tables (and parsing the returns of SHOW CREATE TABLE is not something we want to do as there is a simple workaround for this: upgrade the server).

The same server bug affects Data Sync.  This is described here.


Categories for this entry

Tags: -

Related entries:

You can comment this FAQ