Bug #70879 | Error Code: 1785 when executing simple UPDATE statement | ||
---|---|---|---|
Submitted: | 11 Nov 2013 14:22 | Modified: | 14 Nov 2013 20:16 |
Reporter: | Van Stokes | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.6.14 | OS: | Linux (Ubuntu 12.04 x86_64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | binlog, MySQL, replication, server |
[11 Nov 2013 14:22]
Van Stokes
[11 Nov 2013 14:22]
Van Stokes
Server my.cnf file.
Attachment: my.cnf (application/octet-stream, text), 8.03 KiB.
[11 Nov 2013 14:24]
Van Stokes
Create table statement for FCL_BL
Attachment: create_table_fcl_bl.sql (text/x-sql), 10.72 KiB.
[12 Nov 2013 19:31]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please send us full error log file.
[12 Nov 2013 20:06]
Van Stokes
There are no errors in the error log, general log, or slow log. The error message is returned to the client. Did you configure your server using the MY.CNF I provided? We can replicate this issue every time on every 5.6.14 server. $ mysql --host=xxxx -p mysql> use eci_lw_prod; Database changed mysql> UPDATE `oti_lw_prod`.`fcl_bl` SET `shipper_address` = '2401 N.W. 69 STREET ' WHERE `Bol` = '1666'; ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. mysql>
[12 Nov 2013 20:16]
Sveta Smirnova
Thank you for the feedback. Yes, I configured the server using your configuration file. I understand you get error about GTIDs in the client. I want to check error log for other messages which can shed light on the issue. Also, I noticed you did not copy-paste output of SHOW CREATE TABLE `oti_lw_prod`.`fcl_bl` exactly as it was printed. Could you please re-run this query one more time and send complete output.
[13 Nov 2013 12:44]
Van Stokes
Logs and session capture - see comment.
Attachment: logs.zip (application/zip, text), 8.90 KiB.
[13 Nov 2013 12:49]
Van Stokes
I have uploaded (see above) logs.zip. A) I stopped the MySQL server. B) removed all the logs (for clean logs) C) restarted the MySQL server In the zip file is a file called SESSION.TXT. This contains all the commands I ran from the MySQL client interface - INCLUDING the SHOW CREATE TABLE fcl_bl. As you can see in the logs, no errors are displayed. Not sure what to do next. FYI, the SHOW FULL PROCESSLIST queries in the GENERAL.LOG is by our monitoring software MONyog.
[14 Nov 2013 16:14]
Sveta Smirnova
Thank you for the feedback. I noticed following note in the error log file: 2013-11-13 07:37:18 11481 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.34-0ubuntu0.12.04.1-log), even though it is on the slave (version: 5.6.14-1+debphp.org~precise+1-log), Error_code: 1193 Looks like this is master-master setup and one of masters runs version 5.5. I will check if the issue you report here is a result of the bug, but in any case replicating from older master is not supported configuration in MySQL which leads to unpredictable results.
[14 Nov 2013 16:19]
Peter Laursen
Also see http://bugs.mysql.com/bug.php?id=70895
[14 Nov 2013 17:06]
Sveta Smirnova
Ups, I wanted to say replication from newer master is not supported.
[14 Nov 2013 17:38]
Van Stokes
This server is 5.6.14. It is a SLAVE server. It is replicating from a 5.5 master server. There are no issues with replication. The problem is that 5.6.14 has a problem writing to the local BINLOG when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1. This issue has nothing to do with the replication process. We need write to the local BINLOG for snapshot backup purposes.
[14 Nov 2013 17:47]
Sveta Smirnova
I just tried with different replication configurations and options like in your configuration file, but could not repeat this error. Also, only place of code which prints this error checks for a non-transactional table. Please run following tests and send us results: 1. use oti_lw_prod; update `fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666' 2. begin; UPDATE `oti_lw_prod`.`fcl_bl` SET `shipper_address` = '2401 N.W. 69 STREET ' WHERE `Bol` = '1666'; Also I noticed you don't use Oracle's version of MySQL 5.6.14, but 5.6.14-1+debphp.org~precise+1-log Please download official Oracle binaries from dev.mysql.com/downloads, try with them and inform us if the problem still exits.
[14 Nov 2013 18:02]
Van Stokes
vstokes@mis13:~$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 437 Server version: 5.6.14-1+debphp.org~precise+1-log (Ubuntu) Copyright (c) 2000, 2013, 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, 1 warning (0.00 sec) mysql> SET GLOBAL general_log = 'on'; Query OK, 0 rows affected (0.00 sec) mysql> use oti_lw_prod; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update `fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where -> `Bol` = '1666'; ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update `fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666'; ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. mysql>
[14 Nov 2013 18:02]
Van Stokes
I will load official binaries on a test server and respond.
[14 Nov 2013 18:12]
Sveta Smirnova
Thank you for the feedback. Will wait your response after you try with Oracle binaries.
[14 Nov 2013 18:35]
Van Stokes
Same result using official Oracle distribution (vs Ubuntu repository). ---------------------------------------------------------------------- vstokes@mis13:/opt/mysql/mysql/bin$ ./mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.14-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> use oti_lw_prod; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update `fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666'; ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update `fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666'; ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. mysql> ---------------------------------------------------------------------- from MySQL server error.log 2013-11-14 13:29:15 10451 [Note] Plugin 'FEDERATED' is disabled. 2013-11-14 13:29:15 10451 [Note] InnoDB: The InnoDB memory heap is disabled 2013-11-14 13:29:15 10451 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-11-14 13:29:15 10451 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-11-14 13:29:15 10451 [Note] InnoDB: Using Linux native AIO 2013-11-14 13:29:15 10451 [Note] InnoDB: Using CPU crc32 instructions 2013-11-14 13:29:15 10451 [Note] InnoDB: Initializing buffer pool, size = 4.0G 2013-11-14 13:29:15 10451 [Note] InnoDB: Completed initialization of buffer pool 2013-11-14 13:29:15 10451 [Note] InnoDB: Highest supported file format is Barracuda. 2013-11-14 13:29:15 10451 [Note] InnoDB: 128 rollback segment(s) are active. 2013-11-14 13:29:15 10451 [Note] InnoDB: Waiting for purge to start 2013-11-14 13:29:15 10451 [Note] InnoDB: 5.6.14 started; log sequence number 2129498214821 2013-11-14 13:29:15 7f1bd8772700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool 2013-11-14 13:29:15 7f1bd8772700 InnoDB: Buffer pool(s) load completed at 131114 13:29:15 2013-11-14 13:29:16 10451 [Note] Server hostname (bind-address): '*'; port: 3306 2013-11-14 13:29:16 10451 [Note] IPv6 is available. 2013-11-14 13:29:16 10451 [Note] - '::' resolves to '::'; 2013-11-14 13:29:16 10451 [Note] Server socket created on IP: '::'. 2013-11-14 13:29:16 10451 [Note] Event Scheduler: Loaded 0 events 2013-11-14 13:29:16 10451 [Note] ./mysqld: ready for connections. Version: '5.6.14-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
[14 Nov 2013 18:48]
Van Stokes
If you would like, I can dump the fcl_bl table and upload it and/or upload the actual fcl_bl table file to you.
[14 Nov 2013 18:52]
Sveta Smirnova
Thank you for the feedback. Better would be if you could upload actual table files.
[14 Nov 2013 19:03]
Van Stokes
Ok, I will upload the file to the ftp site. Also, to ensure it wasn't a corrupt file, I did this: 1) mysqldump of table fcl_bl ( mysqldump -p --opt oti_lw_prod fcl_bl > fcl_bl.sql ) 2) drop table fcl_bl; 3) create table fcl_bl ... 4) restored tabled fcl_bl ( $ mysql -p < fcl_bl.sql ) Still no difference.
[14 Nov 2013 19:48]
Van Stokes
Oh boy. I just noticed that there is a trigger for this table. I wasn't aware of it. Could this be the issue!? DELIMITER $$ USE `oti_lw_prod`$$ DROP TRIGGER /*!50032 IF EXISTS */ `fcl_bl_insert_trigger`$$ CREATE /*!50017 DEFINER = 'root'@'%' */ TRIGGER `fcl_bl_insert_trigger` AFTER INSERT ON `fcl_bl` FOR EACH ROW BEGIN DECLARE insert_values TEXT; SET insert_values = concat_insert_values(insert_values,'Terminal',new.Terminal); SET insert_values = concat_insert_values(insert_values,'Port',new.Port); IF(insert_values!='') THEN IF EXISTS(SELECT * FROM fcl_bl_temp WHERE temp_file_no = new.file_no) THEN UPDATE fcl_bl_temp SET temp_update_date = NOW() WHERE temp_file_no = new.file_no; ELSE BEGIN INSERT INTO fcl_bl_temp(temp_file_no,temp_update_date) VALUES(new.file_no,NOW()); END; END IF; END IF; END; $$ DELIMITER ;
[14 Nov 2013 19:50]
Sveta Smirnova
Thank you for the feedback and files. Yes, trigger most likely caused this issue. Please check which engine uses table fcl_bl_temp
[14 Nov 2013 20:16]
Van Stokes
fcl_bl_temp was MyISAM... I changed it to InnoDB and the UPDATE worked fine. Now I need to go and check the engines of all the other tables. Sorry for the trouble.