MySQL "bug" _ MySQL about timestamp and mysqldump

MySQL's "bug" about timestamp and mysqldump

BitsCN.com

MySQL's "bug" about timestamp and mysqldump"

Recurrence

The problem that a colleague encountered when performing a data dump is simplified as follows:

1. create a table

Drop table if exists tb;

Create table tb (

C timestamp not null default '2017-00-00 00:00:00'

) ENGINE = InnoDB default charset = gbk;

Insert into tb values (now ());

Select * from tb;

Return

Mysql> select * from tb;

+ --------------------- +

| C |

+ --------------------- +

| 00:42:45 |

+ --------------------- +

1 row in set (0.00 sec)

2. dump "error"

Mysqldump-Srun/mysql. sock-uroot test tb -- where = 'C = "00:42:45" '| grep INSERT

The returned result is null, that is, no data is exported.

Analysis

From the above conclusion, it seems to be a "bug" of mysqldump, and no data can be exported. If we do not add the where condition first,

Mysqldump-Srun/mysql. sock-uroot test tb | grep INSERT

Insert into 'TB' VALUES ('2017-12-13 16:42:45 ');

Next we will talk about the field type of timestamp.

First, you can see from the size that it is not a string, it is actually an integer. So when we run where c = "00:42:45", we need to convert it to an integer. This involves conversion rules. That is to say, the results of the same timestamp are different in different time zones. Likewise, different timestamps are obtained for the same string in different time zones.

Let's take a look at the results of mysqldump. In the file header, you can see

/*! 40103 SET TIME_ZONE = '+' */; indicates that mysqldump is '+' by default ).

What about the default value of the mysql client:

Mysql> select @ time_zone;

+ ------------- +

| @ Time_zone |

+ ------------- +

| SYSTEM |

+ ------------- +

This SYSTEM indicates that MySQL uses the default time zone of the operating SYSTEM, so it is the East 8 zone. If we set it to the same time zone as mysqldump,

Mysql> set time_zone = '+ 00:00 ';

Query OK, 0 rows affected (0.00 sec)

Mysql> select * from tb;

+ --------------------- +

| T |

+ --------------------- +

| 16:42:45 |

+ --------------------- +

1 row in set (0.00 sec)

The result is the same as that of the full table export shown above.

That is to say, this problem is caused by mysqldump forcibly setting the time zone as the central time zone.

Solution 1

From the code of mysqldump, we can use the -- tz-utc = 0 parameter to remove the previously set time zone action. This is also the default time zone.

Mysqldump -- tz-utc = 0-Srun/mysql. sock-uroot test tb -- where = 'C = "00:42:45" '| grep INSERT

Insert into 'TB' VALUES ('2017-12-14 00:42:45 ');

As you can see, this seems to be the result we want and the export result is reasonable.

Further

If this is so good, why don't mysqldump developers use-tz-utc = 0 as the default behavior? That is to say, what are the risks of doing so?

In fact, it is necessary to prevent data export across time zones. Assume that you import data from a machine in China to a mysqld in the United States (this is the case when @ plinux says B2B). If you do not explicitly set a time zone, an error occurs during import. Because the system uses the default time zone, different timestamps are obtained for the same string value. As we mentioned earlier, timestamps are stored in integer format.

Solution 2

Therefore, the preceding -- tz-utc = 0 is risky. Of course, if you confirm that the source and target system Time zones are not changed, it is OK. Let's discuss whether there are any more safe methods.

Since the timestamp is safe, you can consider using the timestamp as the where condition.

Mysql> select unix_timestamp (c) from tb;

+ ------------------- +

| Unix_timestamp (c) |

+ ------------------- +

| 1, 1355416965 |

+ ------------------- +

According to the value in the table, change the dump command

Mysqldump-Srun/mysql. sock-uroot test tb -- where = 'unix_timestamp (c) = 100' | grep INSERT

Insert into 'TB' VALUES ('2017-12-13 16:42:45 ');

This is correct, and it has nothing to do with whether to use -- tz-utc = 0. The difference is that the problem is displayed.

However, those familiar with MySQL know that there is still a problem with this writing method: they cannot use the index, because we have performed the unix_timestamp operation on the field. Sometimes we have an index dedicated to the timestamp field for export convenience in such a table.

So I want to use the inverse function.

Mysqldump-Srun/mysql. sock-uroot test tb -- where = 'C = from_unixtime (1355416965) '| grep INSERT

Insert into 'TB' VALUES ('2017-12-13 16:42:45 ');

BitsCN.com