why mysqldump time 1 hour wrong?
Geoff Cox
The files created by mysqldump have the time 1 hour behind what is in
the database.
Any ideas how to correct this?
Cheers,
Geoff
Peter H. Coffin
Sever's on BST and dump is in GMT maybe?
--
58. If it becomes necessary to escape, I will never stop to pose dramatically
and toss off a one-liner.
--Peter Anspach's list of things to do as an Evil Overlord
Geoff Cox
<hel...@ninehells.com> wrote:
>On Sat, 19 Jul 2008 13:38:19 +0100, Geoff Cox wrote:
>> Hello,
>>
>> The files created by mysqldump have the time 1 hour behind what is in
>> the database.
>>
>> Any ideas how to correct this?
>>
>> Cheers,
>
>Sever's on BST and dump is in GMT maybe?
Peter,
I am not sure what has been tried by the hosting people who control
this but I think
--default-time-zone=bst
and
--skip-tz-utc
have been added to the script but have not gieven the correct time in
the backup file created.
The time on the server is the same as that on the clock on my wall!
The times in the mysql database itself are correct - again meaning the
same as the clock on my wall (London, UK)
Any light given?!
Cheers
Geoff
Geoff Cox
<hel...@ninehells.com> wrote:
>On Sat, 19 Jul 2008 13:38:19 +0100, Geoff Cox wrote:
>> Hello,
>>
>> The files created by mysqldump have the time 1 hour behind what is in
>> the database.
>>
>> Any ideas how to correct this?
>>
>> Cheers,
>
>Sever's on BST and dump is in GMT maybe?
Peter,
It would seem that mysqldump is using GMT - how can that be changed to
give BST in the backup file?
I assume that backup_script.sh needs an addition - but what?
Cheers
Geoff
PS by backup_script.sh I mean that the following is used with crontab,
00 0-23 * * * /home/scripts/backupscript.sh
to produce a backup file every hour.
'just trying to be precise as I'm not very clear on this!
Geoff Cox
<hel...@ninehells.com> wrote:
>On Sat, 19 Jul 2008 13:38:19 +0100, Geoff Cox wrote:
>> Hello,
>>
>> The files created by mysqldump have the time 1 hour behind what is in
>> the database.
>>
>> Any ideas how to correct this?
>>
>> Cheers,
>
>Sever's on BST and dump is in GMT maybe?
Peter,
sorry - an error in what I wrote - this is correct ..
Geoff
It would seem that mysqldump is using GMT - how can that be changed to
give BST in the backup file?
I assume that backup_script.sh needs an addition - but what?
Cheers
Geoff
PS by backup_script.sh I mean that the following is used with crontab,
00 0-23 * * * /home/scripts/backup_script.sh
Jonathan
From the MySQL manual
(http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html):
The current session time zone setting affects display and storage of
time values that are zone-sensitive. This includes the values displayed
by functions such as NOW() or CURTIME(), and values stored in and
retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are
converted from the current time zone to UTC for storage, and from UTC to
the current time zone for retrieval. The current time zone setting does
not affect values displayed by functions such as UTC_TIMESTAMP() or
values in DATE, TIME, or DATETIME columns.
UTC was formerly known as GMT, so I guess that is your issue. Did you
try importing it in a the server again to see if the times retrieved
from your queries afterwards are in the correct timezone?
Kind regards,
Jonathan
Geoff Cox
wrote:
Jonathan,
I have just tried re-importing and although the time in mysqdump file
is 1 hour out, that in the db table is correct again as you suggest.
Is it not possible to have GMT converted to BST values inside the
mysqldump created backup file?
if yes I suppose the knowck on problem would be getting the correct
time in the db when re-importing!
Cheers
Geoff
>
>Kind regards,
>
>Jonathan
Peter H. Coffin
> On Sat, 19 Jul 2008 11:22:56 -0500, "Peter H. Coffin"
><hel...@ninehells.com> wrote:
>
>>On Sat, 19 Jul 2008 13:38:19 +0100, Geoff Cox wrote:
>>> Hello,
>>>
>>> The files created by mysqldump have the time 1 hour behind what is in
>>> the database.
>>>
>>> Any ideas how to correct this?
>>>
>>> Cheers,
>>
>>Sever's on BST and dump is in GMT maybe?
>
> Peter,
>
> sorry - an error in what I wrote - this is correct ..
>
> Geoff
>
>
> It would seem that mysqldump is using GMT
It is, by default.
> - how can that be changed to
> give BST in the backup file?
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
--skip-tz-utc option on the mysqldump command.
If you use it, though, it'll be up to you to make sure that the timezone
is set correctly before you process the dump back into a server to
restore though. With --skip-tz-utc set, I *think* the server will treat
all the times as being in whatever the timszone of your connection is,
which (obviously) may not be correct. SET time_zone = '+1:00'; will do
that, but you have to remember to do it. The automatic setting to GMT is
a protection against problems when moving data between servers with
different local times.
--
I didn't need to sabotage anything. Not being around to say "No that
won't work" or "you can't do it that way" is more than enough damage.
(Ego problem? It's not a problem.)
-- Graham Reed, on job endings
Geoff Cox
<hel...@ninehells.com> wrote:
>http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
>
>--skip-tz-utc option on the mysqldump command.
>
>If you use it, though, it'll be up to you to make sure that the timezone
>is set correctly before you process the dump back into a server to
>restore though. With --skip-tz-utc set, I *think* the server will treat
>all the times as being in whatever the timszone of your connection is,
>which (obviously) may not be correct. SET time_zone = '+1:00'; will do
>that, but you have to remember to do it. The automatic setting to GMT is
>a protection against problems when moving data between servers with
>different local times.
Peter,
I think I'm coming to the view that since when data is re-imported
from the mysqldump created backup file the time is correct, best to
leave things as they are!
Cheers,
Geoff