MySQL Bugs: #37281: Time stamps in Falcon on big-endian machine give wrong results

Bug #37281	Time stamps in Falcon on big-endian machine give wrong results
Submitted:	9 Jun 2008 13:30	Modified:	15 May 2009 12:57
Reporter:	Olav Sandstå	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.6 alpha	OS:	Any (big-endian)
Assigned to:	Olav Sandstå	CPU Architecture:	Any
Tags:	big-endian, F_ENCODING, PPC, pushbuild, SPARC

Description:
Storing time stamps in Falcon on a big endian machine (SPARC) give wrong result when reading back the data.

Example:

Create a table and store some timestamps:

create table t (ts1 timestamp, ts2 timestamp);
insert into t values();
insert into t values(now(), current_timestamp);
insert into t values (now(), "2005-11-14 01-01-01");

And the result when reading the table:

mysql> select * from t;
+---------------------+---------------------+
| ts1                 | ts2                 |
+---------------------+---------------------+
| 1976-12-22 09:01:12 | 0000-00-00 00:00:00 |
| 2053-08-01 10:09:28 | 2053-08-01 10:09:28 |
| 1986-01-06 04:58:00 | 2034-10-27 07:59:15 |
+---------------------+---------------------+

How to repeat:
Create a table using timestamps and insert some data:

create table t (ts1 timestamp, ts2 timestamp);
insert into t values();
insert into t values(now(), current_timestamp);
insert into t values (now(), "2005-11-14 01-01-01");

Check the result when reading the table:

select * from t;

Thank you for the report.

In current falcon tree support for SPARC is disabled. If I manually edit configure file and enale its support I get compilation error. Please indicate tree you use and how you compile falcon tree on SPARC machine.

Can be related to bug #37076

To compile and reproduce this problem I used:

Tree: mysql-6.0-falcon

Operating system: a fairly recent version of Solaris 10 (it will not work on an "old" version of Solaris 10)

Compiler: Sun Studio 12

Configure command I used:

./configure CC=/opt/studio12/SUNWspro/bin/cc  CXX=/opt/studio12/SUNWspro/bin/CC CFLAGS="-Xa -m64 -mt -g" CXXFLAGS="-m64 -mt -g" --prefix=/home/os136802/install/mysql6  --with-named-curses=-lcurses --with-mysql-ldflags=-static --with-client-ldflags=-static --with-plugins=myisam,innobase,falcon

If you have any feedback on the configure command or suggestions for improvements/changes please let me know.

This is not specific to Sparc, I get similar effects when testing current 6.0 on a Linux/PPC machine.

Examples:

=====
falcon.falcon_bug_28028        [ fail ]

--- /PATH/mysql-test/suite/falcon/r/falcon_bug_28028.result
+++ /PATH/mysql-test/suite/falcon/r/falcon_bug_28028.reject
@@ -9,8 +9,8 @@
 INSERT INTO t3 VALUES (1,20000928174434);
 SELECT * FROM t3 WHERE place_id = 1 AND ts>='2000-09-28 00:00:00';
 place_id       ts
-1      2000-09-28 17:44:34
+1      2013-10-13 01:54:49
 SELECT * FROM t3 WHERE place_id = 1 AND ts >= 20000928000000;
 place_id       ts
-1      2000-09-28 17:44:34
+1      2013-10-13 01:54:49
 DROP TABLE t3;

mysqltest: Result content mismatch
=====

=====
falcon.falcon_bug_22172        [ fail ]

--- /PATH/mysql-test/suite/falcon/r/falcon_bug_22172.result
+++ /PATH/mysql-test/suite/falcon/r/falcon_bug_22172.reject
@@ -84,30 +84,30 @@
 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
 select * from t1 ORDER BY number, cname, carrier_id;
 number cname   carrier_id      privacy last_mod_date   last_mod_id     last_app_date   last_app_id     version assigned_scps   status
-333    tubs    99      2       2002-01-09 11:34:40     501     2002-01-09 11:34:40     500     3       10      0
-650    San Francisco   0       0       2001-12-27 11:13:36     342     0000-00-00 00:00:00     -1      1       24      1
-302467 Sue's Subshop   90      3       2002-01-09 11:32:41     500     2002-01-02 11:51:11     501     7       24      0
-4077711111     SeanWheeler     90      2       2002-01-11 11:28:46     500     0000-00-00 00:00:00     -1      2       3       1
-6014911113     SudzCarwash     520     1       2002-01-02 11:52:34     500     2002-01-02 11:52:59     501     33      32768   0
-9197722223     berry   90      3       2002-01-11 11:28:09     500     2002-01-02 11:45:32     501     4       10      0
+333    tubs    99      2       2018-12-18 22:51:24     501     2018-12-18 22:51:24     500     3       10      0
+650    San Francisco   0       0       1995-12-20 18:22:36     342     0000-00-00 00:00:00     -1      1       24      1
+302467 Sue's Subshop   90      3       1991-10-19 17:07:56     500     1969-11-20 13:19:40     501     7       24      0
+4077711111     SeanWheeler     90      2       2003-04-20 09:29:16     500     0000-00-00 00:00:00     -1      2       3       1
+6014911113     SudzCarwash     520     1       2014-01-06 07:34:04     500     2027-04-22 20:00:44     501     33      32768   0
+9197722223     berry   90      3       1983-08-18 16:59:24     500     2025-10-04 19:05:16     501     4       10      0
 select * from t2 ORDER BY number, cname, carrier_id;
.... (this goes on)
=====

There are more tests with such problems, 5 in total.

The tree on the Linux/PPC machine is still available,
see bug#40206 for more.

As expected, fails on Mac OS X PPC as well.

Two backup related tests also fail due to this bug:
    backup_engines.backup_ptr_row 'falcon' [ fail ]
    backup_engines.backup_ptr_mixed 'falcon' [ fail ]

Excerpt of failure:
-2008-06-01 16:23:30	1984-09-08	1984
+2045-11-18 21:17:44	1984-09-08	1984

The problem with timestamps on big-endian machines was introduced (or became visible?) by the following change done to ha_falcon.cpp in May 21 2008:

=== modified file 'storage/falcon/ha_falcon.cpp'
--- storage/falcon/ha_falcon.cpp	2008-05-06 21:57:29 +0000
+++ storage/falcon/ha_falcon.cpp	2008-05-21 14:58:08 +0000
@@ -2668,11 +2668,6 @@ void StorageInterface::decodeRecord(ucha
 				case MYSQL_TYPE_TIMESTAMP:
 					{
 					int value = (int) (dataStream->value.integer64 / 1000);
-#ifdef _BIG_ENDIAN
-					if (table->s->db_low_byte_first)
-					int4store(field->ptr, value);
-					else
-#endif
 					longstore(field->ptr, value);
 					}
 					break;

Reverting this change makes the failing tests in the Falcon test suite run without errors.

We should understand whether this code is necessary and if it is the correct way to fix this before reverting the change.

Ann Harrison wrote the following useful information in an email sent to the Falcon team:
===========================================================================

All,

  I still worry about reintroducing the code that was removed without
understanding exactly why the timestamp should be inverted only on decode.  MySQL can TIMESTAMP fields have different behaviors - some
are automatically updated.  I think the code that was remove fixes
an endian problem on automatically updated timestamps, but probably
messes it up for others.

  When a timestamp field with the TIMESTAMP_AUTO_SET_ON_INSERT flag
set is inserted, ha_falcon.cpp appears to call back into the item
to get a timestamp value.  That value is little endian, I think.
Similarly, when a timestamp field update, ha_falcon checks the
state of the auto update flag, and makes the same call.

  I think that the code that was removed rearranged the automatically
generated values.  If I'm right, then it should not be used on
timestamp fields that are not automatically set.  However, a better
solution, if that is the case, would be to invert the values when
they are set, so Falcon actually stores all values in big endian
format on a big endian machine. 

============================================================================

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/commits/62975

2955 Olav Sandstaa	2009-01-12
      Fix for Bug#37281 Time stamps in Falcon on big-endian machine give wrong results
      
      The server expects timestamps to be returned on little-endian format while Falcon
      returned these on big-endian format on big-endian machines. Change to use a call-back
      to server code for storing timestamps when returning the results. The server code
      handles the conversion from big-endian to little-endian.

Olav, if you consider the committed patch to be a complete fix, please check and re-enable tests that were disabled because of this bug:

./suite/backup_engines/t/disabled.def:12:backup_ptr_mixed     : Bug#37281 2008-11-18 hakank Dates in Falcon on big-endian have wrong result.
./suite/backup_engines/t/disabled.def:13:backup_ptr_row       : Bug#37281 2008-11-18 hakank Dates in Falcon on big-endian have wrong result.

John, Thanks for making me aware of the disabled tests. I will check that these tests now run without errors, and if they do, then I will re-enable them.

Nice fix! All tests in falcon test suite pass now on my Mac/PPC

falcon.rowid_order_falcon      [ pass ]            225
falcon.rpl_bug_33688           [ pass ]           1488
falcon.rpl_falcon_bug_37221    [ pass ]           1873
-------------------------------------------------------
Stopping All Servers
All 221 tests were successful.
The servers were restarted 12 times
Spent 1051.915 of 1197 seconds executing testcases

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/commits/63204

2958 Olav Sandstaa	2009-01-14
      Follow-up after fixing Bug#37281 Time stamps in Falcon on big-endian machine give wrong results
      
      Enable two tests that were disabled due to this bug.

Pushed into 6.0.10-alpha (revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (version source revid:olav@sun.com-20090114110844-r50l39dcxakeftew) (merge vers: 6.0.10-alpha) (pib:6)

An entry has been added to the 6.0.10 changelog: 

Storing TIMESTAMP values in Falcon tables on a machine supporting big endian storage (for example SPARC), the time stamp information returned would be incorrect.