Bug #41510 timestamp(14) fails in table create (TikiWiki)
Submitted: 16 Dec 2008 14:53 Modified: 16 Dec 2008 15:27
Reporter: Henrik Falk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:6.0.8alpha OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[16 Dec 2008 14:53] Henrik Falk
Description:
I am a TikiWiki developer (not DB developer) and ran into this problem when I installed a new test environment for MySQL 6.0.8Alpha:

Executing the following SQL Query:
(from TikiWiki 3.x)

CREATE TABLE tiki_banning (
  banId int(12) NOT NULL auto_increment,
  mode enum('user','ip') default NULL,
  title varchar(200) default NULL,
  ip1 char(3) default NULL,
  ip2 char(3) default NULL,
  ip3 char(3) default NULL,
  ip4 char(3) default NULL,
  user varchar(200) default '',
  date_from timestamp(14) NOT NULL,
  date_to timestamp(14) NOT NULL,
  use_dates char(1) default NULL,
  created int(14) default NULL,
  message text,
  PRIMARY KEY (banId)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

fails with ErrorNr 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) NOT NULL,
  date_to timestamp(14) NOT NULL,
  use_dates char(1) default N' at line 10

My test environment is a "WIN XP Professional" with MySQL 6.0.8Alpha installed.

The SQL works fine on a Debian MySQL 5.0.32 server.

How to repeat:
Exceute the SQL Query against a Windows MySQL version 6.0.8Alpa on Windows XP Professional.
[16 Dec 2008 14:59] Henrik Falk
> The SQL works fine on a Debian MySQL 5.0.32 server.

The SQL runs without errors and generates a timestamp field which is 19 characters long.
[16 Dec 2008 15:27] Valeriy Kravchuk
This is not a bug. We have warnings about timestamp(N) deprecation for years already:

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 9
Server version: 5.0.72-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE tiki_banning (
    ->   banId int(12) NOT NULL auto_increment,
    ->   mode enum('user','ip') default NULL,
    ->   title varchar(200) default NULL,
    ->   ip1 char(3) default NULL,
    ->   ip2 char(3) default NULL,
    ->   ip3 char(3) default NULL,
    ->   ip4 char(3) default NULL,
    ->   user varchar(200) default '',
    ->   date_from timestamp(14) NOT NULL,
    ->   date_to timestamp(14) NOT NULL,
    ->   use_dates char(1) default NULL,
    ->   created int(14) default NULL,
    ->   message text,
    ->   PRIMARY KEY (banId)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 2 warnings (0.28 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'TIMESTAMP(14)' is deprecated; use 'TIMESTAMP' instead
*************************** 2. row ***************************
  Level: Warning
   Code: 1287
Message: 'TIMESTAMP(14)' is deprecated; use 'TIMESTAMP' instead
2 rows in set (0.08 sec)

mysql> exit
Bye

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 6
Server version: 5.1.30-enterprise-gpl-advanced-debug MySQL Enterprise Server - A
dvanced Edition Debug (GPL)

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

mysql> CREATE TABLE tiki_banning (
    ->   banId int(12) NOT NULL auto_increment,
    ->   mode enum('user','ip') default NULL,
    ->   title varchar(200) default NULL,
    ->   ip1 char(3) default NULL,
    ->   ip2 char(3) default NULL,
    ->   ip3 char(3) default NULL,
    ->   ip4 char(3) default NULL,
    ->   user varchar(200) default '',
    ->   date_from timestamp(14) NOT NULL,
    ->   date_to timestamp(14) NOT NULL,
    ->   use_dates char(1) default NULL,
    ->   created int(14) default NULL,
    ->   message text,
    ->   PRIMARY KEY (banId)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 2 warnings (0.36 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5
.2. Please use 'TIMESTAMP' instead
*************************** 2. row ***************************
  Level: Warning
   Code: 1287
Message: The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5
.2. Please use 'TIMESTAMP' instead
2 rows in set (0.05 sec)

mysql> exit
Bye

So, in 6.0 it is already NOT supported:

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

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

mysql> CREATE TABLE tiki_banning (
    ->   banId int(12) NOT NULL auto_increment,
    ->   mode enum('user','ip') default NULL,
    ->   title varchar(200) default NULL,
    ->   ip1 char(3) default NULL,
    ->   ip2 char(3) default NULL,
    ->   ip3 char(3) default NULL,
    ->   ip4 char(3) default NULL,
    ->   user varchar(200) default '',
    ->   date_from timestamp(14) NOT NULL,
    ->   date_to timestamp(14) NOT NULL,
    ->   use_dates char(1) default NULL,
    ->   created int(14) default NULL,
    ->   message text,
    ->   PRIMARY KEY (banId)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '(14)
NOT NULL,
  date_to timestamp(14) NOT NULL,
  use_dates char(1) default NUL' at line 10

Please, just use TIMESTAMP:

mysql> CREATE TABLE tiki_banning (
    ->   banId int(12) NOT NULL auto_increment,
    ->   mode enum('user','ip') default NULL,
    ->   title varchar(200) default NULL,
    ->   ip1 char(3) default NULL,
    ->   ip2 char(3) default NULL,
    ->   ip3 char(3) default NULL,
    ->   ip4 char(3) default NULL,
    ->   user varchar(200) default '',
    ->   date_from timestamp NOT NULL,
    ->   date_to timestamp NOT NULL,
    ->   use_dates char(1) default NULL,
    ->   created int(14) default NULL,
    ->   message text,
    ->   PRIMARY KEY (banId)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.33 sec)