TIMESTAMP broken in 4.05 / 4.06 BETA 6?

[planetthoughtful]

Hi,

I'm currently test-driving SQLYog Enterprise addition (ver 4.06 BETA 6), primarily because I want to use the structure synchronization tool between my development server and my blog site.

However, the TIMESTAMP field type generated by SQLYog (even in the current BETA) doesn't seem to be defined properly.

When I create a simple table with a TIMESTAMP field in SQLYog it generates:

Code:

CREATE TABLE `table3` (                                    
         `recid` int(11) NOT NULL auto_increment,                
         `recdate` timestamp NULL default NULL,                  
         `myval` int(11) default NULL,                            
         PRIMARY KEY  (`recid`)                                  
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC  

This results in NULL values being stored in the recdate field (MySQL ver 4.1.7-nt-max).

The same simple table definition generated by MySQL Query Browser is:

Code:

CREATE TABLE `table2` (
 `recid` int(10) unsigned NOT NULL auto_increment,
 `recdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `myvalue` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`recid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Note the automatic generation of 'default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP' by MySQL Query Browser. This table definition provides timestamps as expected.

Unfortunately, even if I create the table in MySQL Query Browser, and then use the SQLYog structure synchronization tool, the SQL generated by SQLYog for the synchronization of table2, is:

Code:

/* Create table in Second database */
create table `table2` (
`recid` int(10) unsigned NOT NULL  auto_increment ,
`recdate` timestamp NULL  DEFAULT 'CURRENT_TIMESTAMP' ,
`myvalue` int(10) unsigned NOT NULL  DEFAULT '0' ,
PRIMARY KEY (`recid`)
)Type=MyISAM;

Note that: a) the CURRENT_TIMESTAMP appears in single quotes (which I believe is a bug that was reported fixed in 4.06 BETA 6? It doesn't appear to have been) and 😎 the 'on update CURRENT_TIMESTAMP' clause is missing entirely.

Even if the DEFAULT CURRENT_TIMESTAMP didn't have CURRENT_TIMESTAMP quoted, as I understand it this would mean that records would be timestamped on INSERT, but the timestamp would not be be changed on UPDATEs, which is what the 'on update CURRENT_TIMESTAMP' clause is supposed to do?

I would dearly love to see this bug fixed before investing in the enterprise edition of SQLYog.

Many thanks for giving this consideration.

Regards,

PlanetThoughtful

[Ritesh]

We have issues with TIMESTAMP datatypes in the current version of SQLyog.

We plan to fix all issues related to TIMESTAMP in v4.07.

[Author]

Posts