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
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[9 May 2009 20:07] Johan Andersson
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.
[11 May 2009 13:08] Jonathan Miller
mysql-5.1.31-ndb-6.3.22, mysql-5.1.32-ndb-7.0.5

mysqldump?
[11 May 2009 20:00] Hartmut Holzgraefe
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:
[17 Sep 2009 7:25] Johan Andersson
what happens?
[8 Mar 2010 19:09] John David Duncan
"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.
[8 Mar 2010 20:48] John David Duncan
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.
[8 Mar 2010 20:56] Bugs System
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.
[27 Aug 2013 14:02] Joffrey MICHAIE
Hi,

will this patch ever be pushed into ndb tree ?

Still happening in latest 7.2

Regards,
Joffrey