MSQL Unable to set the UTC timezone permamently

English Applications
1

I am using this time-zone-support for setting the UTC timezone on my machine, But the trouble is that I am not able to set it permanently, it changes to System time when I restart the MySQL server.

The box is OpenSuse 12.3, and the MySQL version is 5.5.33


mysql> SET time_zone = UTC;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| UTC                | UTC                 |
+--------------------+---------------------+
1 row in set (0.00 sec)

now when I restart the mysql server, it reverts back to system. After restarting the server:


mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

I have tried doing the default time zone as well, but its giving me error.


mysql> default-time-zone=UTC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default-time-zone=UTC' at line 1
mysql>

I have also added the following

default_time_zone='+00:00'

to the following files:

/etc/my.cnf
/etc/my.cnf.rpmsave
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf

I think above files are not getting read when mysql server starts, can someone please help me with this?

One more thing, I have set up this openSuse as a virtual machine on virtual box

Thanks
Harbir

2

I don’t have any clue about this (I never used this), but some notes:

Of course.
If you read the things in that link you gave, “SET time_zone” is only for the current connection.
Try to use “SET GLOBAL time_zone=UTC;” instead, I’d say.

I have tried doing the default time zone as well, but its giving me error.

mysql> default-time-zone=UTC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘default-time-zone=UTC’ at line 1
mysql>

Well, as the error message says, this is no valid SQL command.

I have also added the following

default_time_zone='+00:00'

to the following files:

/etc/my.cnf
/etc/my.cnf.rpmsave
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf

I think above files are not getting read when mysql server starts, can someone please help me with this?

The first file is definitely read when mysql server starts, the rest are not.
/etc/my.cnf.rpmsave is just a backup, /usr/share/mysql/ contains sample config files.

Maybe you should post your /etc/my.conf to show how you added it.
But you write you added “default_time_zone=‘+00:00’”. This doesn’t work of course.
The option is named “default-time-zone”, i.e ‘-’ not ‘_’, according to the link you gave.

The initial global server time zone value can be specified explicitly at startup with the --default-time-zone=timezone option on the command line, or you can use the following line in an option file:

default-time-zone='timezone'
3

On 2014-06-15 14:56, harbir linuxuser wrote:


> /etc/my.cnf
> /etc/my.cnf.rpmsave
> /usr/share/mysql/my-small.cnf
> /usr/share/mysql/my-large.cnf
> /usr/share/mysql/my-huge.cnf
> /usr/share/mysql/my-innodb-heavy-4G.cnf
> /usr/share/mysql/my-medium.cnf

my.cnf.rpmsave is an older version of the configuration, that was
replaced by an update to my.cnf. You have to compare both, and decide if
you want some old setting to be moved to the new config file, or not.

I suggest you run “meld /etc/my.cnf /etc/my.cnf.rpmsave” to do it.

You probably have more files like that over the system. Run
“rcrpmconfigcheck” to find them.

> One more thing, I have set up this openSuse as a virtual machine on
> virtual box

I don’t think it matters.

–
Cheers / Saludos,

Carlos E. R.
(from 13.1 x86_64 “Bottle” at Telcontar)

4

Hi Robin_Listas

I have tried with the default-time-zone earlier.
Following in the content of the my.cnf (I have changed default_time_zone to default-time-zone, and tried, but didn’t wrok)

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /var/run/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = /var/run/mysql/mysql.sock
# Change following line if you want to store your database elsewhere
datadir    = /var/lib/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin

# binary logging format - mixed recommended
# binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
log-error    = /var/log/mysql/mysqld.log
socket        = /var/run/mysql/mysql.sock

!includedir /etc/mysql

[mysqldump]
socket        = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
# pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql

# [mysqld2]
# port       = 3307
# datadir    = /var/lib/mysql-databases/mysqld2
# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
# user       = mysql

# [mysqld3]
# port       = 3308
# datadir    = /var/lib/mysql-databases/mysqld3
# pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
# user       = mysql

# [mysqld6]
# port       = 3309
# datadir    = /var/lib/mysql-databases/mysqld6
# pid-file   = /var/lib/mysql-databases/mysqld6/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld6/mysql.sock
# user       = mysql
#init-file= /etc/mysql_initial.sql
default-time-zone='+00:00'

event_scheduler=ON
5

I am having a look at the following thread
configure time zone server for mysql
but do not know how to do

dpkg-reconfigure tzdata

in openSuse,
what would be the alternative of dpkg-reconfigure in OpenSuse?

6

There’s no such thing on openSUSE.

Well, you put it into the “[mysql_multi]” section.
I suppose you have to put it into the “[mysqld]” section to have effect. That’s where the global MySQL-Server configuration belongs.
F.e. directly after:

# The MySQL server
[mysqld]
default-time-zone = UTC
...
7

Maybe I don’t understand but why not set the hardware clock to UTC.

Is the host OS Windows or Linux based?

8

This is set to UTC by default when you select a Linux OS in VirtualBox.

But it should not matter at all for MySQL to what the hardware clock is set to. The system clock should be correct though.

Is the host OS Windows or Linux based?

This should not matter either, his openSUSE installation is running inside VirtualBox.

9

Well it shouldn’t but I have seen the guest affected by the Host settings. I run XP as quest and at one point the VM wanted to reset the guest because I was running UTC on the host and Win XP wanted this to be local time. It was a VM problems and have not seen it for a while but feel all basic time functions should always be set to UTC to avoid these odd time normalization problems. Even Windows will use UTC as a base now a days though you do have to beat it over the head sometimes rotfl!

10

On 2014-06-15 15:36, harbir linuxuser wrote:
>
> Hi Robin_Listas
>
> I have tried with the default-time-zone earlier.
> Following in the content of the my.cnf (I have changed default_time_zone
> to default-time-zone, and tried, but didn’t wrok)

Sorry, I said nothing bout your time zone.

I only explained why you have both “/etc/my.cnf” and
“/etc/my.cnf.rpmsave”, and what to do about it.

About the contents of your file, I say nothing.

–
Cheers / Saludos,

Carlos E. R.
(from 13.1 x86_64 “Bottle” at Telcontar)

11

@wolfi323](https://forums.opensuse.org/member.php/40214-wolfi323)

You are a legend.
Thank you very much.
Thank you to all others you have tried to help, really appreciate it …

So the trick is at a certain location of the file. Actually I was placing it at the end, the way I did it in my windows machine, so certainly this is not the same :slight_smile:

I have placed

default-time-zone = UTC

in the [mysqld] section of the my.cnf file.

its all working rotfl!

12

Probably on your windows machine the “[mysqld]” section actually is at the end of the file. As it is a standard configuration file, it can of course differ between installations. And openSUSE’s default file may look different than the default file on a windows installation.

I really doubt that the windows version of MySQL would behave differently in regard to parsing the config file than the Linux version.

Well, yes, to make sure the system clock is correct.
Still I don’t see the connection to MySQL running in the VM.

And again, neither the VM nor MySQL (regardless whether it is running in the guest or the host) should care what the host’s hardware clock is set to.
They don’t read the hardware clock.

VirtualBox has an explicit setting whether the guest’s “hardware” clock should be in UTC or local time though. This is needed as it just takes that time from the host’s system clock, it doesn’t really store the guest’s hardware clock’s time.

Even Windows will use UTC as a base now a days though you do have to beat it over the head sometimes rotfl!

Yes. AFAIK all NT-based windows versions (so NT, 2000, XP, and up) use UTC internally.
But also all of them keep the local time in the hardware clock.
It is possible to change that via a registry “hack”, but at least on XP this has severe problems (f.e. the system time keeps jumping to UTC all the time).
It should work better in later windows versions, but IIANM there are still issues with suspend/hibernate/resume at least (or have they been fixed already?)
Anyway, I’d say this is getting off-topic here… :wink:

13

Well the time base for SQL will be the OS it is running in the time base for the OS will be in this case the VM the VM 's time base comes from either the host OS or the hardware, the host OS gets its reference from the hardware and the hardware is set periodically to an external time server. Now depending on the the OS’s and VM being used time server etc you can get some odd results if you don’t just set everything to use a UTC reference. Note that is the reference time not the displayed local time. Add a Windows that thinks the hardware is set to local and you may not really know the time rotfl!

Since SQL may be used from all over the world you may want the actually datetime strings to be in UTC and then an offset can be calculated from the UTC to display relative to any given local time. The thing is the system has to actually know the true UTC time and that may not be really available depending on the string of things I listed above.

Datetime is not as simple as it first appears to be LOL

14

No, as I said, in the case of VirtualBox the guest’s “hardware” clock comes from the host OS’s system time, not from the hardware. And it is adapted according the UTC setting in the VM’s configuration.

the host OS gets its reference from the hardware and the hardware is set periodically to an external time server.

Yes, but it doesn’t necessarily have to be set periodically to an external time server.
But again, this doesn’t really matter for the guest, as VirtualBox just takes the system time as base for the guest’s emulated hardware clock.

Now depending on the the OS’s and VM being used time server etc you can get some odd results if you don’t just set everything to use a UTC reference. Note that is the reference time not the displayed local time. Add a Windows that thinks the hardware is set to local and you may not really know the time rotfl!

Yes, but again, VirtualBox only cares about the host’s OS time and passes that as hardware clock time to the guest, either directly or translated.

Since SQL may be used from all over the world you may want the actually datetime strings to be in UTC and then an offset can be calculated from the UTC to display relative to any given local time.

Well, that’s how a Linux system does it. Use UTC internally and add an offset according to the timezone when the time is presented to the user.
And AFAIK, Windows NT does the same as I said already.

The thing is the system has to actually know the true UTC time and that may not be really available depending on the string of things I listed above.

No idea about that.
But the guest’s kernel calculates in UTC, and the UTC time as well as local time should be available the same as on a “real” Linux system.
Whether the time is correct or not is a different matter though, as I said.

I’ve no idea whether VirtualBox gets the UTC time or the local time from Windows.

Anyway, nowhere here comes the host’s hardware clock into play (that was my point), except when you boot the Windows host system as it gets the time from the hardware clock.
But as long as the Windows’ system clock is correct, the guest should have the correct time as well (no matter whether it uses UTC or local time).

Datetime is not as simple as it first appears to be LOL

Well, it is, as long as no localization/timezones come into play… :wink: