| 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: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 6.0.8alpha | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[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)
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.