Description:
The manual says:
Beginning with MySQL 4.1.1, the MySQL server can be run in @code{MAXDB}
mode. When the server runs in this mode, @code{TIMESTAMP} is identical
with @code{DATETIME}. That is, if the server is running in @code{MAXDB}
mode at the time that a table is created, @code{TIMESTAMP} columns
are created as @code{DATETIME} columns. As a result, such columns use
@code{DATETIME} display format, have the same range of values, and there is
no automatic initialization or updating to the current date and time.
This appears to be true, but _only_ if TIMESTAMP is given as exactly
that (TIMESTAMP with no display width).
With no display width, TIMESTAMP becomes DATETIME:
set sql_mode=maxdb;
drop table if exists t;
create table t (t1 timestamp, t2 timestamp, t3 timestamp, t4 timestamp);
desc t;
Results in:
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t1 | datetime | YES | | NULL | |
| t2 | datetime | YES | | NULL | |
| t3 | datetime | YES | | NULL | |
| t4 | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
When a display width is given, TIMESTAMP(n) is still created as a
TIMESTAMP.
drop table if exists t;
create table t (t1 timestamp, t2 timestamp(10), t3 timestamp(10),
t4 timestamp(10));
desc t;
Results in:
+-------+-----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-------+
| t1 | datetime | YES | | NULL | |
| t2 | timestamp | YES | | CURRENT_TIMESTAMP | |
| t3 | timestamp | YES | | 0000-00-00 00:00:00 | |
| t4 | timestamp | YES | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-------+
How to repeat:
See above.
Suggested fix:
Convert TIMESTAMP to DATETIME in MAXDB
regardless of whether a display width is given.
Description: The manual says: Beginning with MySQL 4.1.1, the MySQL server can be run in @code{MAXDB} mode. When the server runs in this mode, @code{TIMESTAMP} is identical with @code{DATETIME}. That is, if the server is running in @code{MAXDB} mode at the time that a table is created, @code{TIMESTAMP} columns are created as @code{DATETIME} columns. As a result, such columns use @code{DATETIME} display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. This appears to be true, but _only_ if TIMESTAMP is given as exactly that (TIMESTAMP with no display width). With no display width, TIMESTAMP becomes DATETIME: set sql_mode=maxdb; drop table if exists t; create table t (t1 timestamp, t2 timestamp, t3 timestamp, t4 timestamp); desc t; Results in: +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | t1 | datetime | YES | | NULL | | | t2 | datetime | YES | | NULL | | | t3 | datetime | YES | | NULL | | | t4 | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ When a display width is given, TIMESTAMP(n) is still created as a TIMESTAMP. drop table if exists t; create table t (t1 timestamp, t2 timestamp(10), t3 timestamp(10), t4 timestamp(10)); desc t; Results in: +-------+-----------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+-------+ | t1 | datetime | YES | | NULL | | | t2 | timestamp | YES | | CURRENT_TIMESTAMP | | | t3 | timestamp | YES | | 0000-00-00 00:00:00 | | | t4 | timestamp | YES | | 0000-00-00 00:00:00 | | +-------+-----------+------+-----+---------------------+-------+ How to repeat: See above. Suggested fix: Convert TIMESTAMP to DATETIME in MAXDB regardless of whether a display width is given.