MySQL Bugs: #40378: SHOW FULL FIELDS should have ON UPDATE column

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

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'!

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?

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".

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!)

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

ok .. accepted!