MySQL Bugs: #44763: ndb_restore - timestamp data is printed in wrong format when using --print-data

Bug #44763	ndb_restore - timestamp data is printed in wrong format when using --print-data
Submitted:	9 May 2009 20:07	Modified:	8 Mar 2010 20:56
Reporter:	Johan Andersson	Email Updates:
Status:	Patch pending	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ndb_restore, timestamp

Description:
ndb_restore used to create a csv dump of backup file

Timestamp data is printed out as a big number, instead of a proper timestamp (as MySQL server likes it).

This makes it impossible to load the data into an innodb table e.g, since data is truncated for the timestamp, and the timestamp gets the values 0000-00-00 00:00:00 instead of the real timestamp

How to repeat:
Created:

CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=NDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1

insert into t1(a,b) values (1,'hello');

The following is the output from

ndb_mgm -e "start backup"
ndb_restore  -b 1  -n 3 --append --print-data --print-log --tab=.  --fields-enclosed-by="" --fields-terminated-by=","

cat t1.txt:
1,hello,1241896016

CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=INNODB;

mysql> LOAD DATA INFILE '/tmp/t1.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n';
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'ts' at row 1 | 
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+---+-------+---------------------+
| a | b     | ts                  |
+---+-------+---------------------+
| 1 | hello | 0000-00-00 00:00:00 | 
+---+-------+---------------------+
1 row in set (0.00 sec)

mysql> 

Suggested fix:
convert the timestamp from "number" to correct mysql timestamp so it is useful.

mysql-5.1.31-ndb-6.3.22, mysql-5.1.32-ndb-7.0.5

mysqldump?

Suggested change (this opens another can of worms due to different
time zone handling in DATETIME and TIMESTAMP though ...):

=== modified file 'storage/ndb/src/ndbapi/NdbRecAttr.cpp'
--- storage/ndb/src/ndbapi/NdbRecAttr.cpp	2009-04-08 22:28:04 +0000
+++ storage/ndb/src/ndbapi/NdbRecAttr.cpp	2009-05-11 19:55:02 +0000
@@ -500,8 +500,20 @@ ndbrecattr_print_formatted(NdbOut& out, 
     break;
     case NdbDictionary::Column::Timestamp:
     {
-      time_t time = r.u_32_value();
-      out << (uint)time;
+      time_t time_result = r.u_32_value();
+      struct tm tm_result;
+      char asc_result[20];
+
+      gmtime_r(&time_result, &tm_result);
+      sprintf(asc_result, "%4d-%02d-%02d/%02d:%02d:%02d", 
+	      tm_result.tm_year + 1900,
+	      tm_result.tm_mon + 1,
+	      tm_result.tm_mday,
+	      tm_result.tm_hour,
+	      tm_result.tm_min,
+	      tm_result.tm_sec);
+    
+      out << asc_result;
     }
     break;
     case NdbDictionary::Column::Blob:

what happens?

"TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval."   So, ndb_restore will report timestamps in UTC, but mysql will want them in local time.

Patch submitted, based on Hartmut's, but changing gmtime_r to localtime_r.  I think this should make the output consistent with other data types such as Datetime.  

Caveat: the timestamp fields in the file created by ndb_restore are expressed in the time zone where ndb_restore was run.  If you were to load this file into a server in some other time zone, the data there would be corrupted.

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/102616

3132 John David Duncan	2010-03-08
      bug#44763 : print Timestamp fields as YYYY-MM-DD/HH:MM:SS in local time zone,
      so that ``ndb_restore --print-data'' output can be loaded back into mysql.

Hi,

will this patch ever be pushed into ndb tree ?

Still happening in latest 7.2

Regards,
Joffrey