• Home
    • 2018
    • 2017
  • Oracle GoldenGate
    • Core Platform
    • Cloud Service
    • For Oracle DB
    • For Big Data
    • For MySQL
    • For DB2 z/OS
    • For DB2 LUW
    • For DB2 i
    • For Informix
    • For SQL Server
    • For Teradata
    • For AWS Aurora
    • Foundation Suite
    • Use Cases
  • Data Management
    • General
    • Big Data
    • Oracle Database
    • MySQL
    • MS SQL Server
    • IBM DB2
    • IBM Informix
    • Teradata
    • SAP ASE
  • Data Science
  • IT
    • IT Management
    • IT Technology
  • Reading
  • About
Jinyu's Blog
About Data, Database, Integration and IT 

What should I do when I get the "'Unknown or incorrect time zone: 'UTC'' on query." error?

2/5/2018

0 Comments

 
Details: I got the "Unknown or incorrect time zone: 'UTC'' on query." error on my EC2 MySQL slave database when apply transacation on the RDS database. The error message is shown as follows:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: jinyumaster.xxx.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000683
          Read_Master_Log_Pos: 593259
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 720
        Relay_Master_Log_File: mysql-bin-changelog.000682
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: ggdemo
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1298
                   Last_Error: Error 'Unknown or incorrect time zone: 'UTC'' on query. Default database: 'ggdemo'. Query: 'BEGIN'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 521
              Relay_Log_Space: 2970632
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1298
               Last_SQL_Error: Error 'Unknown or incorrect time zone: 'UTC'' on query. Default database: 'ggdemo'. Query: 'BEGIN'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1701769414
                  Master_UUID: ..
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 180206 04:24:27
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Answer: You need to enable the timezone support in your MySQL database. The following are the steps to fix this:
$ mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql -p
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
[ec2-user@ip-172-30-0-198 ~]$ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.6.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: jinyumaster.xxx.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000684
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 1761707
        Relay_Master_Log_File: mysql-bin-changelog.000682
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: ggdemo
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1761508
              Relay_Log_Space: 2971987
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 326
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1701769414
                  Master_UUID: ...
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
0 Comments

    GoldenGate for MySQL

    Oracle GoldenGate for MySQL replicates DML operations for MySQL databases. Here, I discuss Oracle GoldenGate for MySQL, MySQL database and MySQL replication.

    ​Top Posts
    • Summary
    • MySQL 5.7 Support
    • Veridata Support 
    • How to replicat MySQL RDS Database  
    • How to enable binary logging for MySQL
    Resources
    • Cheat sheet: GoldenGate for MySQL
    • Documentation: ​GoldenGate for MySQL Installation and Setup Guide (12.2)
    Buzzwords
    Heterogeneity Security High Performance ​Real-Time ​DML Globalization

    Archives

    February 2018
    January 2018
    July 2017
    April 2017
    January 2017
    September 2016
    August 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    June 2015

    Categories

    All
    Amazon AWS
    Feature Inquiry
    GoldenGate
    How To
    MySQL
    Release
    Troubleshooting

    RSS Feed

Copyright © 2010-2027 Jinyu Wang.