Bug #7418 TIMESTAMP not always converted to DATETIME in MAXDB mode
Submitted: 20 Dec 2004 0:21 Modified: 14 Jan 2005 12:03
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Dmitry Lenev

[20 Dec 2004 0:21] Paul Dubois
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.
[14 Jan 2005 12:03] Dmitry Lenev
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be availiable in 4.1.10 release.