timestamp field not operated right

[advantis]

I have this table:

Code:

tbl     CREATE TABLE `tbl` (                                                                                                                                                                  
         `id` int(10) unsigned NOT NULL auto_increment,                                                                                                                                      
         `val` int(10) unsigned NOT NULL default '0',                                                                                                                                        
         `t` timestamp(14) NOT NULL,                                                                                                                                                        
         PRIMARY KEY  (`id`)                                                                                                                                                                
       ) TYPE=MyISAM                                                                                                                                                                        

Notice the timestamp field.

When I insert a new row using the F11 screen, it works ok: timestamp is set to NULL, which results in writing the current date and time in there. If I go then and change a value on that row, SQLyog issues an UPDATE … t=''.

Is there a way to update the timestamp automatically without me having to explicitely set that field to NULL?

The exact things happen with and without the “NOT NULL” attribute for the timestamp field.

Thanks.

[peterlaursen]

there should be three methods:

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

1) You explicitly set the column to NULL.

2) The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

3) The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value (allready has – I thik should have been there! – PL) it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

from:

http://dev.mysql.com/doc/mysql/en/timestam…mp-pre-4-1.html

I just tested it and it works here! But standard behaviour of sqlyog is to keep the current value of the timestamp in the UPDATE statement. You can copy the sql from the history-pane and delete the timestamp field from the UPDATE statement.

I think we are missing an option here to let the user decide whether first timestamp field should be updated.

[advantis]

peterlaursen wrote on Apr 26 2005, 11:07 PM:

I think we are missing an option here to let the user decide whether first timestamp field should be updated.

I want to remove dead steps, so an option like “Set timestamp fields to NULL on UPDATE/REPLACE INTO” would be useful. I just don't want to use PHPMyAdmin (too slow on displaying even from local server) only because it treats timestamps as I expect, and setting timestamps to NULL explicitely may sometime fail because of memory issues (i.e. I forget to set it).

[peterlaursen]

one comment to that …

with the INSERT and UPDATE statement it is only the FIRST timestamp field in the table that is effected.

Very often you'd like to have a timestamp showing record_create_time and another showing record_update_time.

If there is a change in data in some other colums and the UPDATE does not reference the first (and only that) timestamp field, then the first will be updated with current time.

Setting ALL timestamps defined with “NOT NULL” to NULL would update both and I don't like that!

But as much flexibility as possible without too much “fuzz” … 🙂

[peterlaursen]

There is a small bug too.

ALTERing tables with a TIMESTAMP field from sqlyog produces this code

alter table `test`.`tablename1` ,change `mytime` `mytime` timestamp DEFAULT 'CURRENT_TIMESTAMP' NOT NULL

should be

alter table `test`.`tablename1` ,change `mytime` `mytime` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL

String 'CURRENT_TIMESTAMP' is illegal as a timestamp!

[Ritesh]

peterlaursen wrote on Apr 26 2005, 10:37 PM:

There is a small bug too.

ALTERing tables with a TIMESTAMP field from sqlyog produces this code

alter table `test`.`tablename1` ,change `mytime` `mytime` timestamp DEFAULT 'CURRENT_TIMESTAMP' NOT NULL

should be

alter table `test`.`tablename1` ,change `mytime` `mytime` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL

String 'CURRENT_TIMESTAMP' is illegal as a timestamp!

v4.06 BETA has been released. Check http://www.webyog.com/forums/index.php?act…&st=0#entry5252 for more details.

[peterlaursen]

That's right … 4.06 fixes the problem with the “ALTER TABLE” statement. Sqlyog now generates SQL like

alter table `test`.`tablename1` ,change `v` `ID` bigint (20) NULL , change `b` `mydate` timestamp NULL , change `n` `mystring` varchar (20) NULL

And that runs withourt errors.

But I believe there is still a problem/undesired behaviour with the INSERT- and UPDATE-statements

Table definition in this example runs

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERTING

********

When INSERTING rows by adding values for column 'mystring' the value of 'mydate' generated is 0000-00-00 00:00:00 .

the SQL (from history pane) is

insert into `test`.`tablename1` ( `ID`, `mydate`, `mystring` ) values ( 'NULL', 'CURRENT_TIMESTAMP', 'newstring' )

should be

insert into `test`.`tablename1` ( `ID`, `mydate`, `mystring` ) values ( NULL, CURRENT_TIMESTAMP, 'thisstring' )

the first example generates TIMESTAMP value '0000-00-00 00:00:00'

the second example generates correct value 2005-05-03 09:22:56

UPDATING

********

Changing a string value in the example generates this sql

update `test`.`tablename1` set `ID`='10', `mydate`='2005-05-03 09:22:56', `mystring`='thatstring' where `ID`='10'

The value '2005-05-03 09:22:56' is current content of the field and thus the value is NOT changed. But that is what i call undesired behaviour, since it overrides the “normal” behaviour of the MySQL server. I think the sql should be (only)

update `test`.`tablename1` set `ID`='10', `mystring`='thatstring' where `ID`='10'

… because that wil allow the server to do as is normal. No overriding takes place with this example.

SUMMARY

*******

1) Problem with ALTER TABLE is fixed

2) There is still a regular BUG with INSERT

3) There is undesired behaviour with UPDATE

[peterlaursen]

More considerations

*******************

First TIMESTAMP in table definition could run either

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP,

`mydate2` timestamp NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

(with sqlyog 4.06 you achieve this by setting the default value to CURRENT_TIMESTAMP)

or

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mydate2` timestamp NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

(with sqlyog 4.06 you achieve this by omitting the default value AND specifying NOT NULL)

Please note:

************

I have added more TIMESTAMP columns – and they should behave different with INSERT and UPDATE statements.

mydate1 is FIRST TIMESTAMP and should behave in a “standard MySQL manner” as far as UPDATING 'first TIMESTAMP field' goes.

mydate1 and mydate2 are NOT FIRST TIMESTAMP so the “standard MySQL manner for first TIMESTAMPs” doesn't apply here so they just should behave as the definition goes (whether they are defined with NULL or NOT NULL).

Actually I think tests with TIMESTAMPs should run against SCHEMAs like (both of) these two ones.

The sql generated by sqlyog is now

update `test`.`tablename1` set `ID`='6', `mydate1`='2005-05-03 09:22:35', `mydate2`='2005-05-03 10:55:54', `mydate3`=NULL, `mystring`='thisstring' where `ID`='6'

I still think it should be

update `test`.`tablename1` set `ID`='6', `mystring`='thatstring' where `ID`='6'

IN CONCLUSION

*************

the UPDATE statement should not list TIMESTAMP fields unless the user explicitly wants to update a TIMESTAMP with a value that's entered from the keyboard.

BTW: when will pic upload work again ???

[peterlaursen]

what I meant …

First TIMESTAMP in table definition could run either

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mydate2` timestamp NOT NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

CREATE TABLE `tablename3` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP,

`mydate2` timestamp NOT NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

The point was that mydate2 should have been defined with NOT NULL, mydate3 with NULL

BTW:

did you change something with the code in Beta4. I don't think I can reproduce the exact behaviour of beta3!

Or maybe it's just my poor head that needs to rest 😮

But problems with INSERTs and UPDATEs are unchanged with Beta4.

[Ritesh]

Quote:

did you change something with the code in Beta4. I don't think I can reproduce the exact behaviour of beta3!

Or maybe it's just my poor head that needs to rest

Nope. We have not changed anything between BETA 3 and 4 with respect to this issue.

Quote:

But problems with INSERTs and UPDATEs are unchanged with Beta4.

We plan to fix this issue in v4.1

Quote:

BTW: when will pic upload work again ???

Very soon. We are working on the issue.

[jquiros]

So I downloaded 4.1, and it still sets the timestamp column value to the one already there!

Like another poster noted, it would be nice for that behavior to be optional, as I like to use “timestamp” to know when ANY change has been made to the record.

Otherwise thanks for a very useful tool.

Jon

Ritesh wrote on May 3 2005, 08:29 AM:

Nope. We have not changed anything between BETA 3 and 4 with respect to this issue.

We plan to fix this issue in v4.1

Very soon. We are working on the issue.

[post=”5293″]<{POST_SNAPBACK}>[/post]

[peterlaursen]

The “other poster” was that me ?

http://www.webyog.com/forums/index.php?sho…view=getnewpost

Actually timestamp should not change UNLESS it is defined as “default CURRENT_TIMESTAMP on update CURRENT_TEMESTAMP”.

However after some version/release MySQL defines THE FIRST timestamp defined with a table that way! But there might be several timestamps in a table and no2 + 3 etc should not be affected.

It is a little tricky, because there should also be a way to overwrite a timestamp ON PURPOSE 😀

But personally I agree that this issue should be fixed very soon.

[peterlaursen]

A solution could be an option to hide columns in DATA as well as RESULT-pane.

Hidden columns should not be INSERTED or UPDATED. Also there should be a general program option to hide “first TIMESTAMP in table definition”. The flag could be displayed in object browser for each column, and change when you Ctrl-click it for instance. A small green dot for visible, a red dot for hidden. Optimally these settings should be saveable.

Could something like that be done?

Of course the simple “select * …” would have to be replaced with select col1, col2, col3, col5 (NB! no col4!), col6 etc when sending data to data-pane.

Comments?

[Ritesh]

I have added it in the TO-DO list of SQLyog. Mostly, we will implement support for TIMESTAMP and OTHER FUNCTIONs in v4.4.

[Author]

Posts