SQLyog FAQ

If you experience problems when using MySQL 4.1 with TIMESTAMP columns declared NOT NULL the reason is simply that SHOW FULL FIELDS returns wrong information for such datatype on this MySQL server version.  Just try yourself this:

CREATE TABLE ´ts1´ (                                 
          ´id´ int(11) NOT NULL AUTO_INCREMENT,              
          ´ts´ timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
          PRIMARY KEY (´id´)                                 
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ´ts2´ (                                 
          ´id´ int(11) NOT NULL AUTO_INCREMENT,              
          ´ts´ timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
          PRIMARY KEY (´id´)                                 
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 .. and you will see that SHOW FULL FIELDS returns the same for both - the 'NULL' column returns "YES" in both cases. This affects SQLyog in two different ways:

1) ALTER TABLE will show the NOT NULL checkbox for the column unchecked in both cases.

2) (more serious): Schema Sync will *think* that the TIMESTAMP column is defined NULL and not NOT NULL. When such table is the <source>, the <target>  will be CREATED as NULL (or ALTERED to NULL if it exists on <target> as NOT NULL) .

As MySQL 4.1 is now out of active support we have decided that we will not apply any 'workaround' for this in our code, because all still-supported server versions including MySQL 5.0 are not affected by this server bug.

However notice that in SQLyog 8.05 we worked around a similar server bug affecting both 4.1 and 5.0 servers: On those servers SHOW FULL FIELDS will not expose an ON UPDATE CURRENT_TIMESTAMP clause for a TIMESTAMP column, what would cause similar problems with Schema Sync when syncing a TIMESTAMP ON UPDATE CURRENT_TIMESTAMP from 4.1/5.0 to 5.1/6.x servers.  Here we will get information from SHOW CREATE TABLE and an ON UPDATE clause for a TIMESTAMP column will be synced correctly on all server versions where this clause applies (4.1 and higher).