Search code examples
mysqllinuxdatabaseopensuse

OpenSuse - Setting the timezone on mysql

I am following this 'MySQL Server Time Zone Support' to set the default time zone on myqlon OpenSuse virtual machine. I have read some other questions regarding this, but it seems I am missing something.

I can see the zone info on my machine:

linux-gn77:~ # ls -l /usr/share/zoneinfo/
total 280
drwxr-xr-x  2 root root  4096 May  4 10:35 Africa
drwxr-xr-x  6 root root  4096 May  4 10:35 America
drwxr-xr-x  2 root root  4096 May  4 10:35 Antarctica
drwxr-xr-x  2 root root  4096 May  4 10:35 Arctic
drwxr-xr-x  2 root root  4096 May  4 10:35 Asia
drwxr-xr-x  2 root root  4096 May  4 10:35 Atlantic
drwxr-xr-x  2 root root  4096 May  4 10:35 Australia
drwxr-xr-x  2 root root  4096 May  4 10:35 Brazil
-rw-r--r--  2 root root  2102 Oct 28  2013 CET
-rw-r--r--  2 root root  2294 Oct 28  2013 CST6CDT
drwxr-xr-x  2 root root  4096 May  4 10:35 Canada
drwxr-xr-x  2 root root  4096 May  4 10:35 Chile
-rw-r--r--  4 root root  2411 Oct 28  2013 Cuba
-rw-r--r-- 13 root root   118 Oct 28  2013 UTC
-rw-r--r-- 13 root root   118 Oct 28  2013 Universal
-rw-r--r--  4 root root  1464 Oct 28  2013 W-SU
-rw-r--r--  2 root root  1873 Oct 28  2013 WET
-rw-r--r-- 13 root root   118 Oct 28  2013 Zulu

Now I try to load the timezone by issuing the following command:

mysql_tzinfo_to_sql /usr/share/zoneinfo/UTC UTC | mysql -u root -p mysql

I get the following error:

ERROR 1064 (42000) at line 1: 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 'mysql_tzinfo_to_sql: command not found' at line 1

when I checked the version of mysql, its the following:

mysql> SHOW VARIABLES LIKE "%version%";


+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| innodb_version          | 5.5.33           |
| protocol_version        | 10               |
| slave_type_conversions  |                  |
| version                 | 5.5.33           |
| version_comment         | openSUSE package |
| version_compile_machine | i686             |
| version_compile_os      | Linux            |
+-------------------------+------------------+

Can someone suggest me how can I set the timezone on this?

Solution

  • I have found the issue to this problem.

    Basically the script mysql_tzinfo_to_sql is not in the system, and is a part of the mysql-community-server-tools, hence first we have to install mysql_tzinfo_to_sql.

    zypper install mysql-community-server-tools

    This will place the script mysql_tzinfo_to_sql in /usr/bin/ folder.

    Now you will be able to load the timezone information using the following command:

    mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql