Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: MSQL Unable to set the UTC timezone permamently

  1. #1

    Default MSQL Unable to set the UTC timezone permamently

    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

    Code:
    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:

    Code:
    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.
    Code:
    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
    Code:
    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. #2

    Default Re: MSQL Unable to set the UTC timezone permamently

    I don't have any clue about this (I never used this), but some notes:
    Quote Originally Posted by harbir_linuxuser View Post
    Code:
    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:

    Code:
    mysql> SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | SYSTEM             | SYSTEM              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    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.
    Code:
    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
    Code:
    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:
    Code:
    default-time-zone='timezone'
    Last edited by wolfi323; 15-Jun-2014 at 06:07.

  3. #3
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: MSQL Unable to set the UTC timezone permamently

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

    Code:
    
    > /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. #4

    Default Re: MSQL Unable to set the UTC timezone permamently

    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)


    Code:
    # 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. #5

    Default Re: MSQL Unable to set the UTC timezone permamently

    I am having a look at the following thread
    configure time zone server for mysql
    but do not know how to do
    Code:
    dpkg-reconfigure tzdata
    in openSuse,
    what would be the alternative of dpkg-reconfigure in OpenSuse?

  6. #6

    Default Re: MSQL Unable to set the UTC timezone permamently

    Quote Originally Posted by harbir_linuxuser View Post
    I am having a look at the following thread
    configure time zone server for mysql
    but do not know how to do
    Code:
    dpkg-reconfigure tzdata
    in openSuse,
    what would be the alternative of dpkg-reconfigure in OpenSuse?
    There's no such thing on openSUSE.

    Quote Originally Posted by harbir_linuxuser View Post
    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)
    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:
    Code:
    # The MySQL server
    [mysqld]
    default-time-zone = UTC
    ...

  7. #7
    Join Date
    Nov 2009
    Location
    West Virginia Sector 13
    Posts
    15,762

    Default Re: MSQL Unable to set the UTC timezone permamently

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

    Is the host OS Windows or Linux based?

  8. #8

    Default Re: MSQL Unable to set the UTC timezone permamently

    Quote Originally Posted by gogalthorp View Post
    Maybe I don't understand but why not set the hardware clock to UTC.
    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. #9
    Join Date
    Nov 2009
    Location
    West Virginia Sector 13
    Posts
    15,762

    Default Re: MSQL Unable to set the UTC timezone permamently

    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

  10. #10
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: MSQL Unable to set the UTC timezone permamently

    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)

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •