MySQL Bugs: #70879: Error Code: 1785 when executing simple UPDATE statement

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

Description:
This error is REPEATABLE on multiple 5.6.14 servers!

Receiving the following error:

Query: update `oti_lw_prod`.`fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666'

Error Code: 1785
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.

NOTE: This error ONLY OCCURS when BINLOG is enabled!
See server my.cnf in further down.

Key points:
1) Server is a SLAVE server for some databases (see config)
2) BINLOG was enabled (turned off to prevent error)

It is not clear why we are receiving this error. We are not doing the three NO-NO's as specified here: http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html

In order to by-pass this error we had to disable the binlog.

How to repeat:
(see create table statement further down - won't let me add it here)

Execute this statement:

update `oti_lw_prod`.`fcl_bl` set `shipper_address` = '2401 N.W. 69 STREET ' where `Bol` = '1666'

the the error occurs.

Suggested fix:
Permit the transaction if it doesn't violate the what the documentation says or update the documentation.

Server my.cnf file.

Attachment: my.cnf (application/octet-stream, text), 8.03 KiB.

Create table statement for FCL_BL

Attachment: create_table_fcl_bl.sql (text/x-sql), 10.72 KiB.

Thank you for the report.

I can not repeat described behavior. Please send us full error log file.

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>

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.

Logs and session capture - see comment.

Attachment: logs.zip (application/zip, text), 8.90 KiB.

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.

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.

Also see http://bugs.mysql.com/bug.php?id=70895

Ups, I wanted to say replication from newer master is not supported.

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.

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.

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>

I will load official binaries on a test server and respond.

Thank you for the feedback.

Will wait your response after you try with Oracle binaries.

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)

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.

Thank you for the feedback.

Better would be if you could upload actual table files.

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.

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 ;

Thank you for the feedback and files.

Yes, trigger most likely caused this issue.

Please check which engine uses table fcl_bl_temp

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.