MySQL Bugs: #8894: TIMESTAMP values scrambled/misaligned when using --new

Bug #8894	TIMESTAMP values scrambled/misaligned when using --new
Submitted:	2 Mar 2005 16:31	Modified:	27 Apr 2005 15:44
Reporter:	Anders Henke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.23	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
TIMESTAMP values are scrambled when using them in numeric context in a MySQL 4.0 server with an active --new forward compatibility mode.

Depending upon exact usage of --new, this may also lead to "altered" data both in "--new" as well as "non--new" mode without having the user actually inserting "scrambled" data.

How to repeat:
mysql> set @@new=off;
mysql> create table `foo` (`bar` timestamp(14) NOT NULL default '00000000000000');
mysql> insert into foo values(NULL);
mysql> SELECT sql_no_cache bar+0, bar, now()+0, now() FROM foo;

Re-Run the SELECT using 'set @@new=on' and 'set @@new=off' on a MySQL 4.0 server in comparison to a MySQL 4.1 server:

On 4.0.23:
mysql> set @@new=off ; SELECT sql_no_cache bar+0, bar, now()+0, now() FROM foo;
Query OK, 0 rows affected (0.00 sec)

+----------------+----------------+----------------+---------------------+
| bar+0          | bar            | now()+0        | now()               |
+----------------+----------------+----------------+---------------------+
| 20050302171137 | 20050302171137 | 20050302171144 | 2005-03-02 17:11:44 |
+----------------+----------------+----------------+---------------------+
1 row in set (0.00 sec)

(ok)

mysql> set @@new=on ; SELECT sql_no_cache bar+0, bar, now()+0, now() FROM foo;
Query OK, 0 rows affected (0.00 sec)

+-------------------+---------------------+----------------+---------------------+
| bar+0             | bar                 | now()+0        | now()               |
+-------------------+---------------------+----------------+---------------------+
| 50302171137050008 | 2005-03-02 17:11:37 | 20050302171147 | 2005-03-02 17:11:47 |
+-------------------+---------------------+----------------+---------------------+
1 row in set (0.00 sec)
(bar+0 gives a misaligned result set)

On 4.1.10:
mysql> SELECT sql_no_cache bar+0, bar, now()+0, now() FROM foo;
+----------------+---------------------+----------------+---------------------+
| bar+0          | bar                 | now()+0        | now()               |
+----------------+---------------------+----------------+---------------------+
| 20050302171438 | 2005-03-02 17:14:38 | 20050302171443 | 2005-03-02 17:14:43 |
+----------------+---------------------+----------------+---------------------+
1 row in set (0.00 sec)
(bar+0 is correct).

Additionally, if the INSERTs do happen while being in --new-mode, even a "SET @@new=off ; SELECT bar+0" gives out the same misaligned results as if running in "new=on"-mode, while "bar" alone is still correct.
So in some way, "--new" may also alter data upon insert/update, even effective
after using "--new", although it is not intended to influence any data on disk.

Use of a timestamp in numeric context is the supposed workaround when relying on the same string format while upgrading from 4.0 to 4.1 (see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html).
As --new currently isn't compatible to this use, --new is enough broken to really prevent one from an easy migration to 4.1 (by adding a "+0" while still running on their 4.0-installations and testing this by using --new) and seriously renders usage of "--new" to a very limited use.

Suggested fix:
Make it work :)

I've raised this issue to serious for the following reasons:
-no workaround exists, 
-this somehow may alter data, although it isn't supposed to do so
-this effectively disables the suggested workaround (use of timestamp in numeric context) for an easy migration path towards 4.1.

Additionional information regarding the "alteration" of data:
mysql> show variables like 'new';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| new           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(19) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

So, "set @@new=off" doesn't reset to "timestamp(14)",this may also be the reason for the "scrambled" data. A simple "alter table foo modify bar timestamp(14);" does reset this issue and corrects any corrupted data for this case.

I was unable to reproduce your bug report.

mysql> show variables like 'new';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| new           | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> desc foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(19) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set @@new=off ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'new';      
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| new           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> desc foo;                 
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(14) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

I've verified the bug on too many :) servers so far, most
of them are running the same binaries taken from the Official MySQL RPM.

The bugs do also appear on the the Debian-compiled binary for 4.0.23 being
active on the server which hosts our No-Softwarepatents-Alliance with MySQL 
and RedHat and where e.g. gunnar from mysql already has access to.

If it helps, I can provide you another testbed and setup a world-accessible
MySQL server running the same configuration and binaries.

Yesterday I ran a script which resetted any found (persistent) timestamp(19) to timestamp(14); this script fixed databases on a total of 35 servers, so I'm quite sure 
that this issue does really exist, is not left to my imagination, faulty hardware
or some miscompiled binary :-)

When trying to verify the bug for myself, it didn't show up on 4.0.18
(also binaries from the Official MySQL RPM), as 4.0.18 always does treat
the column as being timestamp(14) even while running with new=on, so
an 'describe foo' while running with new=on does show the field 'bar' as
being of 'timestamp(14)' (instead of timestamp(19) - this is different to
the example you gave in the bug report as well as I experienced on 4.0.23,
maybe this is also related to the real source of the problem).

As far as I see, this problem may also split up into two seperate bugs,
where one only shows up as the other one exists - to clear up, I've
added two more testcases below:

mysql> set @@new=off;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `foo` (`bar` timestamp(14) NOT NULL default
    -> '00000000000000');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> set @@new=off ; SELECT sql_no_cache bar+0, bar FROM foo;
Query OK, 0 rows affected (0.00 sec)

+----------------+----------------+
| bar+0          | bar            |
+----------------+----------------+
| 20050303110443 | 20050303110443 |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(14) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

This is the supposed "working" output.

mysql> set @@new=on ; SELECT sql_no_cache bar+0, bar FROM foo;
Query OK, 0 rows affected (0.00 sec)

+-------------------+---------------------+
| bar+0             | bar                 |
+-------------------+---------------------+
| 50303110443050008 | 2005-03-03 11:04:43 |
+-------------------+---------------------+
1 row in set (0.00 sec)  

mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(19) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set @@new=off; describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(14) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

So if the table is created while in "new=off", the numeric result is
still wrong, while the table structure on disk is still correct and
setting new=off does "reverse" the handling. Subsequent selects in new=off 
also return the same (valid) results.

Contrary to the case where the table is created with "new=on":

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)
mysql> set @@new=on;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `foo` (`bar` timestamp(14) NOT NULL default
'00000000000000');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT sql_no_cache bar+0, bar FROM foo;
+-------------------+---------------------+
| bar+0             | bar                 |
+-------------------+---------------------+
| 50303111021050008 | 2005-03-03 11:10:21 |
+-------------------+---------------------+
1 row in set (0.00 sec)  

mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(19) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set @@new=off; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'new';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| new           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar   | timestamp(19) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT sql_no_cache bar+0, bar FROM foo;
+-------------------+---------------------+
| bar+0             | bar                 |
+-------------------+---------------------+
| 50303111021050008 | 2005-03-03 11:10:21 |
+-------------------+---------------------+
1 row in set (0.00 sec)  

In this case, the table structure is persistently using the "wrong" width
for the timestamp and so always gives out a wrong result. With "new=off"
on 4.0.23, simply selecting "bar" should give out the numeric result
"20050303111021", but instead it prints out the string it is supposed
to only print while running with new=on - but this isn't the case.

The workaround for 4.1 ('select bar+0" to force a numeric result) also
gives out a similar misaligned/scrambled result, so I believe the wrong
table structure to be the reason for this.

MySQL 4.0.22 adresses bug #4131, where another TIMESTAMP-related bug for mysqld running in --new mode has been fixed; maybe exactly that bugfix is the reason for the problem in bug #8894.

Thanks Anders, I was able to verify this bug in 4.0.24 from bk.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23417

Hi, Anders!

In @@new mode all TIMESTAMP columns always have display width equal to 19. When you are creating table with TIMESTAMP column in @@new mode it will be created as TIMESTAMP(19) column. Once the column with such display width is created it won't change its display width even if you will turn @@new mode off. Also values from TIMESTAMP(19) column are always returned as strings (it does not matter whenever @@new mode is on or off).

Thus it is not a bug that you always have TIMESTAMP(19) column in table which were
created when @@new mode was on (altough probably we should more clearly say this
in our documentation).

Of course, it is a bug that you get scrambled values when selecting from such columns. I have proposed patch which fixes this issue.

Thank you for noticing this!

Fixed in 4.0.25

Bugfix noted in 4.0.25 changelog.

Also, I've added a more explicit description of
the effect of the new system variable to this
section of the manual:

http://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html

This should show up on the web site after the next
manual-rebuild cycle.