Bug #40378 SHOW FULL FIELDS should have ON UPDATE column
Submitted: 28 Oct 2008 19:50 Modified: 16 Dec 2008 20:35
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:4.1, 5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc, regression
Triage: Triaged: D3 (Medium)

[28 Oct 2008 19:50] Peter Laursen
Description:
The TIMESTAMP syntax since early 4.1contains an ON UPDATE clause.
SHOW FULL FIELDS was not properply updated to inform about this!

How to repeat:
CREATE TABLE `tablename1` (
`id` int(11) NOT NULL auto_increment,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY  (`id`)) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW FULL FIELDS FROM tablename1;

Suggested fix:
I set it S2 and *not* S4! That is my opinion.  It is a *serious bug* and not a *feature requrest* :)

There should be an ON UPDATE column in the ouput from SHOW FULL FIELDS (even when it only applies for TIMETAMP columns and can only be CURRENT_TIMESTAMP).

An workaround would be add it in 'default' column.  I would find in incorrect, but maybe acceptable though!

But I also think I read somewhere (forge.mysql ?) that ON UPDATE option is considered for more datatypes and accepting more different values!

BTW: It is *not impressive* how issues keep on showing up reflecting that change of server functionalities added in 4.1 and 5.0 were never really 'brought to an end'!
[29 Oct 2008 4:27] Valeriy Kravchuk
Thank you for a problem report. Indeed, in 5.0.x we do not see this important information:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.70-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `tablename1` (
    -> `id` int(11) NOT NULL auto_increment,
    -> `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMES
TAMP, PRIMARY KEY
    ->  (`id`))
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql> show full fields from tablename1\G
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: ts
      Type: timestamp
 Collation: NULL
      Null: NO
       Key:
   Default: CURRENT_TIMESTAMP
     Extra:
Privileges: select,insert,update,references
   Comment:
2 rows in set (0.00 sec)

But the problem is fixed in 5.1 already, see Extra column:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.1.29-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `tablename1` (
    -> `id` int(11) NOT NULL auto_increment,
    -> `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMES
TAMP, PRIMARY KEY
    ->  (`id`))
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> show full fields from tablename1\G
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: ts
      Type: timestamp
 Collation: NULL
      Null: NO
       Key:
   Default: CURRENT_TIMESTAMP
     Extra: on update CURRENT_TIMESTAMP
Privileges: select,insert,update,references
   Comment:
2 rows in set (0.02 sec)

So, do you want this to be fixed in 5.0.x also or we can consider the problem solved?
[30 Nov 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 Nov 2008 9:00] Peter Laursen
I overlooked that I was asked for feedback whether *I want it fixed* or not. I would like to see it fixed, but of course there are priorities. I think it also depends on to what extend 5.0 will be supported after the 5.1 stable release.

Basically the problem is for me/us that automatic generation of sync scripts require this (it is not feasible to parse SHOW CREATE for every table!)
[16 Dec 2008 20:32] Omer Barnir
As this bug has a workaround and is is already fixed in 5.1 and the fact that a different fix will need to be implemented for 5.0, it will not be fixed in 5.0
[16 Dec 2008 20:35] Peter Laursen
ok .. accepted!