OK .. it is not a big deal either! Thanks for replying. -- Peter On Mon, Oct 13, 2014 at 2:35 PM, Sergei Golubchik <serg@mariadb.org> wrote:

Hi, Peter!

On Oct 06, Peter Laursen wrote:

...

Refer: http://bugs.mysql.com/bug.php?id=74238

here I prefer the 'relaxed parsing' in Maria DB.

But look at this:

CREATE TABLE `d` ( `rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `datetime` DATETIME(4) DEFAULT NULL, `timestamp` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(5) ON UPDATE CURRENT_TIMESTAMP(4), PRIMARY KEY (`rec_id`) ) ENGINE=INNODB; -- it actually creates the table

SHOW CREATE TABLE d; /* returns

CREATE TABLE `d` ( `rec_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `datetime` datetime(4) DEFAULT NULL, `timestamp` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), PRIMARY KEY (`rec_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 */

I don't think DEFAULT CURRENT_TIMESTAMP(5) should be accepted for the datatype DATETIME(4).

What say?

I checked the code, and it happens to be intentional. If the default value is CURRENT_TIMESTAMP(5), it's the same as if you explicitly insert CURRENT_TIMESTAMP(5) into the TIMESTAMP(4) column. And you can do that - explicitly insert, I mean - that's not a bug, the value will be truncated, as expected. So, the result is exactly the same as inserting CURRENT_TIMESTAMP(4). That's why specifying a default of CURRENT_TIMESTAMP(5) is allowed - it produces the same result as a default of CURRENT_TIMESTAMP(4).

On the other hand, if you insert a CURRENT_TIMESTAMP(3) value into TIMESTAMP(4) column - the result will be different, the last digit in the column will be always 0. For default values it's not supported (frm file does not currently remember the width for the default value). That's why you cannot specify a default of CURRENT_TIMESTAMP(3) for a TIMESTAMP(4) column.

Regards, Sergei