Did you successfully alter the table? What does SHOW CREATE TABLE give you?
mysql> CREATE TABLE test ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts_modify TIMESTAMP ); Query OK, 0 rows affected (0.00 sec) mysql> insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES(); Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) (the warnings are that the '' for id was truncated and the auto_increment was used) mysql> select * from test; +----+---------------------+---------------------+ | id | ts_create | ts_modify | +----+---------------------+---------------------+ | 1 | 2006-05-19 11:18:07 | 0000-00-00 00:00:00 | | 2 | 2006-05-19 11:18:24 | 0000-00-00 00:00:00 | | 3 | 2006-05-19 11:18:25 | 0000-00-00 00:00:00 | | 4 | 2006-05-19 11:18:25 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ 4 rows in set (0.00 sec) and then I update to see if it changes the timestamp at ts_create: mysql> update test set ts_modify=DATE_SUB(NOW(),INTERVAL 6 MONTH); Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from test; +----+---------------------+---------------------+ | id | ts_create | ts_modify | +----+---------------------+---------------------+ | 1 | 2006-05-19 11:18:07 | 2005-11-19 11:18:59 | | 2 | 2006-05-19 11:18:24 | 2005-11-19 11:18:59 | | 3 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | | 4 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | +----+---------------------+---------------------+ 4 rows in set (0.00 sec) So you see, it worked -- it did not update the ts_create timestamp. I would guess your alter table failed. -Sheeri On 5/18/06, Danish <[EMAIL PROTECTED]> wrote:
Hi, I have a table with a time stamp column defined as ts timestamp(14) NOT NULL this is the first timestamp value in a series of timestamp columns. Whenever I update a row in the table ts gets updated with the current timestamp. I read from the documentaion that the first timestamp column in a create statement with neither DEFAULT nor ON UPDATE clauses is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. To solve the problem I try to alter the table with the following command: ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT 'CURRENT_TIMESTAMP()' but whenever I update a row after running the command above ts still gets updated. Any ideas how I can restrict mysql to not update the ts value on update. BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well Regards, Danish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
