MySQL Bugs: #67894: unsuitable default behavior in mysqldump

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

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?

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.

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 |
+---------------------+

Thank you for the report.

Closed as not a bug because last comment.