| Bug #37146 | Online Backup table is not affected by time zones | ||
|---|---|---|---|
| Submitted: | 2 Jun 2008 23:01 | Modified: | 8 Jan 2009 11:39 |
| Reporter: | Hema Sridharan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | mysql-6.0-backup | OS: | Linux |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[9 Oct 2008 20:03]
Sveta Smirnova
Same problem with new table backup_history Test case: set time_zone='-5:00'; show variables like '%zone%'; create table t2(id int); --vertical_results show table status like 't2'; --eval backup database test to '$MYSQL_TEST_DIR/var/tmp/x1'; select * from mysql.backup_history; Result: =====mysql-6.0-backup===== =====bug37146===== set time_zone='-5:00'; show variables like '%zone%'; Variable_name Value system_time_zone GMT time_zone -05:00 create table t2(id int); show table status like 't2'; Name t2 Engine MyISAM Version 10 Row_format Fixed Rows 0 Avg_row_length 0 Data_length 0 Max_data_length 1970324836974591 Index_length 1024 Data_free 0 Auto_increment NULL Create_time 2008-10-09 14:59:24 Update_time 2008-10-09 14:59:24 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options Comment backup database test to '/users/ssmirnova/src/mysql-6.0-backup/mysql-test/var/tmp/x1';; backup_id 252 select * from mysql.backup_history; backup_id 252 process_id 0 binlog_pos 0 binlog_file backup_state complete operation backup error_num 0 num_objects 1 total_bytes 1025 validity_point_time 2008-10-09 19:59:26 start_time 2008-10-09 19:59:24 stop_time 2008-10-09 19:59:26 host_or_server_name localhost username root backup_file /users/ssmirnova/src/mysql-6.0-backup/mysql-test/var/tmp/x1 user_comment command backup database test to '/users/ssmirnova/src/mysql-6.0-backup/mysql-test/var/tmp/x1' drivers MyISAM
[9 Oct 2008 20:24]
Hema Sridharan
The backup table should show the timing when backup was performed as the "show table status" command shows when table was created. The time_zone is changed to GMT-5. "Show table status" shows the correct time table was created after changing the time_zone to GMT-5. Backup table shows different time than GMT-5. This should not be the case. As Sveta mentioned, this behavior is seen even with new backup_history logs/tables.
[11 Nov 2008 14:14]
Øystein Grøvlen
I have sent the following questions to the dev-backup alias: I am working at Bug#37146 where it is requested that times displayed when querying the backup history table should be according to time_zone and not UTC. If I do not remember wrong, I asked the same question when reviewing the new implementation for the backup history table, but was told that it should be UTC. I think there are at least two good arguments for presenting it in local time: 1. Improved usability since DBA does not have to do number gymnastics to figure out when the backup was actually taken. 2. To be consistent with show table. Any counter arguments? If I understand correctly, this is a timestamp vs. datetime issue. Is there anything about the characteristics of timestamps that caused datetime to be used?
[31 Dec 2008 13:19]
Øystein Grøvlen
Peter Gulutzan replied in an email of Nov 25, 2008: Mr/Ms Anonymous might be thinking of the email thread "Re: Online Backup article" https://intranet.mysql.com/secure/mailarchive/mail.php?folder=230&mail=762 where Paul, I, Lars and Rafal all seem to have agreed about UTC. As far as I can tell, Lars's instructions are the last word on the subject. https://intranet.mysql.com/secure/mailarchive/mail.php?folder=230&mail=820 Lars suggested that the affair should be stored UTC, viewed local. Since time zones affect TIMESTAMP but not DATETIME, I take it that he meant for the columns to be TIMESTAMP.
[5 Jan 2009 14:34]
Øystein Grøvlen
As far as I can tell this is a more general problem than just for online backup tables. See Bug#34612 "CSV storage engine does not honor time_zone". Inspecting Field_timestamp::val_str() in debugger, it seems like CSV engines compensates for the time zone since the value val_str() reads is not UTC, but UTC minus time_zone at read time. Hence, value presented will always be UTC regardless of time zone.
[5 Jan 2009 18:32]
Øystein Grøvlen
I think the issue is that when a timestamp is read from the CSV file it is interpreted as local time, not UTC. (This happens since Timestamp_field::store() is used to convert the timestamp to seconds for internal storage). Hence, it is stored internally as (UTC - timezone). When it later is to be sent to the client, the timezone calculation converts it back to UTC. (If it had been stored internally as UTC, it would have been converted to local time.)
[8 Jan 2009 11:37]
Øystein Grøvlen
A fix for this depends on Bug#34612 being fixed. When that bug has been fixed, backup code should be fixed to rely on the standard timezone to UTC conversion as done by methods in field.cc, instead of trying to do the conversion in backup specific code.
[8 Jan 2009 11:39]
Øystein Grøvlen
Work on this bug will have to be suspended until Bug#34612 has been fixed.
[26 Aug 2009 15:24]
Chuck Bell
BUG#37146 is relying on WL#5051 which Guilhem has kindly estimated the completion in weeks rather than near future. I have considered resolutions to this bug in the meantime, but I cannot find a solution that does not attempt to circumvent the problem inherent in the server code itself that is the heart of WL#5051. However, there is one thing we could do to prepare for WL#5051 if you feel we must fix this bug without waiting for WL#5051. This solution involves removing the timezone conversion on line# 905 in image_info.h as shown: === modified file 'sql/backup/image_info.h' --- sql/backup/image_info.h 2009-08-21 15:43:19 +0000 +++ sql/backup/image_info.h 2009-08-26 15:21:44 +0000 @@ -898,11 +898,7 @@ time_t read_time(const bstream_time_t &b time.tm_min= buf.min; time.tm_sec= buf.sec; - /* - Note: mktime() assumes that time is expressed as local time and vp_time is - in UTC. Hence we must correct the result to get it right. - */ - return mktime(&time) - (time_t)timezone; + return mktime(&time); } Incidentally, this will also fix BUG#43221. However, it will cause the VP test cases in the backup_logs test to fail. I would recommend disabling the portions of this test that fail and append WL#5051 to re-enable them when that work is complete.
[28 Aug 2009 13:48]
Chuck Bell
[from Lars' email to Jorgen's reply to my email referring to the solution above] Ok, do that. If we do no longer will have a bug report on this, then please add to the quality report that backup is not showing correct time: https://inside.mysql.com/wiki/BackupQualityReport > > Your suggestion sounds like the best we can do at the moment. We can > > either have the current broken functionality, or the all-UTC > > functionality which at least will start to work when Guilhem is done > > with his WL. I suggest you go for this solution.
Description: 1) I set the time_zone to '-5:00' from the GMT time which is my local time. 2) I create table t1 and check the create time.It shows local time. 3) I backup database and check the Backup start time, it shows me GMT time instead of local time. The Backup start time should also show the Local time.Backup tables should not be dependent on GMT time zones. How to repeat: When I started the test the Local time was 17:55 set time_zone='-5:00'; mysql> show variables like '%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | GMT | | time_zone | -05:00 | +------------------+--------+ 2 rows in set (0.00 sec) mysql> create table t2(id int); Query OK, 0 rows affected (0.05 sec) *************************** 2. row *************************** Name: t2 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-06-02 17:56:22 Update_time: 2008-06-02 17:56:22 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.00 sec) mysql> backup database x to '/tmp/x1'; +-----------+ | backup_id | +-----------+ | 12 | +-----------+ 1 row in set (0.07 sec) mysql> select * from mysql.online_backup where backup_id=12\G *************************** 1. row *************************** backup_id: 12 process_id: 0 binlog_pos: 0 binlog_file: NULL backup_state: complete operation: backup error_num: 0 num_objects: 2 total_bytes: 2050 validity_point_time: 2008-06-02 22:57:22 start_time: 2008-06-02 22:57:22 stop_time: 2008-06-02 22:57:22 host_or_server_name: localhost username: root backup_file: /tmp/x1 user_comment: command: backup database x to '/tmp/x1' engines: MyISAM 1 row in set (0.01 sec) Above table shows that Backup start_time and stop time are unaffected by change of time_zones. Online Backup tables should be independent of time_zones.