Bug #67894 unsuitable default behavior in mysqldump
Submitted: 13 Dec 2012 13:09 Modified: 13 Dec 2012 17:23
Reporter: xiaobin lin (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump

[13 Dec 2012 13:09] xiaobin lin
Description:
After insert a timestamp value into table, the select value will not be the same with that in the dump result. It will not affect the result after restore.
But if user look into the dump file, it will causes some puzzle.

How to repeat:
It is a script file bellow
======================
mysql  -Srun/mysql.sock -uroot   test -e "
drop table if exists tb;
CREATE TABLE tb (
  t timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
insert into tb values(now());
select * from tb;
"
mysqldump   -Srun/mysql.sock -uroot   test tt | grep 2012
======================
Because the mysql use "SYSTEM" as default (in my timezone, it is '+08:00')
but the mysqldump use "+00:00" as default

Suggested fix:
use --tz-utc=0 when mysqldump can get the look-more-reasonable result. 
If the --tz-utc=0 can be the default behavior?
[13 Dec 2012 13:39] xiaobin lin
Sorry, when I look into the comments, it says that this is on purpose, for that when users will restore data between machines that do not have the same default-time-zone.
So I think this can be closed.
But I also think that it is better for mysqldump to recognize the default timezone of the source-machine. That will make less puzzle.
[13 Dec 2012 13:46] xiaobin lin
for example when dump with where clause.

mysqldump   -Srun/mysql.sock -uroot   test tt   --where='c="2012-12-14 04:36:03"' 

will dump *nothing* though there

mysql> select * from tt where c="2012-12-14 04:36:03";
+---------------------+
| c                   |
+---------------------+
| 2012-12-14 04:36:03 |
+---------------------+
[13 Dec 2012 17:23] Sveta Smirnova
Thank you for the report.

Closed as not a bug because last comment.