4

I'm having issues with timezones with MYSQL. Right now, I'm dumping the data via mysqldump, as such: 

mysqldump -uuser -ppass --tab c:/temp --skip-dump-date dbName

This works exactly as intended, with the data in the database matching the .txt files that are generated. The problem comes with importing the data. To get around some foreign key issues during the import phase, I'm using the following code:

SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA LOCAL INFILE 'c:/temp/tableName.txt' INTO TABLE tableName;
SET FOREIGN_KEY_CHECKS = 1;

This also has no issues, except afterwards when I check the database, all the TIMESTAMPs are shifted by 5 hours forwards. I know this has to be a problem with time zones (I'm UTC-05:00, so the shift time makes sense), but I don't understand what should be done to stop the database from assuming that a shift in time needs to be done.

Along my searches for answers, I came across a similar SO problem, but the issue was backwards. Importing was fine, exporting was shifted. MySQL data export changes times

Furthermore, I have seen some suggestions to look at this information in MYSQL, but I don't exactly know what I should do with this information now that I have it. 

SELECT @@global.time_zone, @@session.time_zone;

Gives me:

SYSTEM    +00:00

Is there a way to tell MYSQL to import without changing TIMESTAMPs? Should I change some sort of time zone setting? If so, should I change it for import, or export? I'm not planning on moving the database across any time zones.

UPDATE 1

In the mean time until I know best practice, I have tried the following change directly before using the block of LOAD DATA commands: 

SET TIME_ZONE = '+00:00';

This has solved my problem (where I expect my dump to be the same as the files I used to create the database). Afterwards, I change the time back to -05:00, but I'm not sure if it was necessary.

Community
  • 1
  • 1
sonoftunk
  • 168
  • 1
  • 7

1 Answers1

5

Use the --tz-utc option to `mysqldump. From the documentatin:

This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Using `--tz-utc` and `--skip-tz-utc` does not change the data files whatsoever. I am using `--tab`, and loading the data at a separate time from the tables. The problem remains in the import, and not the export. – sonoftunk Jul 13 '14 at 19:18
  • Sorry, I didn't notice that option before. Do you have to use `--tab`? – Barmar Jul 13 '14 at 19:35
  • Yes, `--tab` is necessary; this is for database version control. Also, I believe the question is moot if I do not use `--tab`. Exporting using `mysqldump` without `--tab` can export using `INSERT` statements, which should not be affected by this problem. – sonoftunk Jul 13 '14 at 19:38
  • 1
    It's still affected by the problem, because the times are interpreted in each server's timezone. That's why the `--tz-utc` option exists, to force everything into a common timezone. – Barmar Jul 13 '14 at 19:40