| Bug #40360 | Binlog related errors with binlog off | ||
|---|---|---|---|
| Submitted: | 27 Oct 2008 20:27 | Modified: | 18 Jun 2010 23:12 |
| Reporter: | David Stavisski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.29 | OS: | Windows |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[27 Oct 2008 20:27]
David Stavisski
[27 Oct 2008 20:29]
David Stavisski
intended: upgraded 5.1.28 -> 5.1.29
[28 Oct 2008 4:22]
Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf. I can not repeat the behaviour described: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.1.29-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> set global transaction isolation level read committed; Query OK, 0 rows affected (0.05 sec) mysql> show variables like '%bin%'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | binlog_cache_size | 32768 | | binlog_format | STATEMENT | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | sql_log_bin | ON | | sync_binlog | 0 | +---------------------------------+------------+ 10 rows in set (0.06 sec) mysql> create table tlb(c1 int primary key, c2 int) engine=InnoDB; Query OK, 0 rows affected (0.36 sec) mysql> insert into tlb values(1,1); Query OK, 1 row affected (0.11 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> update tlb set c2=2; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) What I am doing wrong?
[28 Oct 2008 15:34]
Valeriy Kravchuk
Sorry, my fault (I had forgotten that SET GLOBAL will work for new sessions). Verified like this:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.1.29-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `datetest` (
-> `a` bigint(20) unsigned NOT NULL,
-> `b` datetime default NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO test.datetest VALUES(1,'4269-02-29');
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'REA
D-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
mysql> show variables like '%bin%';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| binlog_cache_size | 32768 |
| binlog_format | STATEMENT |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| sql_log_bin | ON |
| sync_binlog | 0 |
+---------------------------------+------------+
10 rows in set (0.02 sec)
[29 Oct 2008 3:15]
Tom Daly
I have just downloaded and installed 5.1.29 on RHEL 5 and I am seeing the same problem. to reproduce : mysql < test.sql ERROR 1598 (HY000) at line 11: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' cat test.sql DROP TABLE IF EXISTS customer; CREATE TABLE customer (id integer not null, itemid char(20), quantity integer, customerid integer, value numeric(12,2), PRIMARY KEY C_custinvkey (id) ) ENGINE = InnoDB; CREATE INDEX test_idx ON customer(customerid); mysql> show variables like '%bin%'; +---------------------------------+----------------------+ | Variable_name | Value | +---------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_format | STATEMENT | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | max_binlog_cache_size | 18446744073709551615 | | max_binlog_size | 1073741824 | | sql_log_bin | ON | | sync_binlog | 0 | +---------------------------------+----------------------+ 10 rows in set (0.00 sec) mysql> show variables like '%innodb%'; +---------------------------------+---------------------------+ | Variable_name | Value | +---------------------------------+---------------------------+ | have_innodb | YES | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 20971520 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 5242880000 | | innodb_checksums | OFF | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10000M:autoextend | | innodb_data_home_dir | /data/mysql/var | | innodb_doublewrite | OFF | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | O_DIRECT | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 300 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 16777216 | | innodb_log_file_size | 1677721600 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /log/mysql/var/ | | innodb_max_dirty_pages_pct | 15 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | OFF | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | +--------------------------- regards Tom Daly (Sun Microsystems)
[29 Oct 2008 14:25]
Trudy Pelzer
Per Joro: This is a consequence of us changing the default binlog format for 5.1 to statement (http://bugs.mysql.com/bug.php?id=39812). It seems that the InnoDB code has an incomplete check in ha_innobase::external_lock() : 6529 /* Statement based binlogging does not work in isolation level 6530 READ UNCOMMITTED and READ COMMITTED since the necessary 6531 locks cannot be taken. In this case, we print an 6532 informative error message and return with an error. */ 6533 if (lock_type == F_WRLCK) 6534 { 6535 ulong const binlog_format= thd_binlog_format(thd); 6536 ulong const tx_isolation = thd_tx_isolation(current_thd); 6537 if (tx_isolation <= ISO_READ_COMMITTED && 6538 binlog_format == BINLOG_FORMAT_STMT) 6539 { 6540 char buf[256]; 6541 my_snprintf(buf, sizeof(buf), 6542 "Transaction level '%s' in" 6543 " InnoDB is not safe for binlog mode '%s'", 6544 tx_isolation_names[tx_isolation], 6545 binlog_format_names[binlog_format]); 6546 my_error(ER_BINLOG_LOGGING_IMPOSSIBLE, MYF(0), buf); 6547 DBUG_RETURN(HA_ERR_LOGGING_IMPOSSIBLE); 6548 } Note that it doesn't check if binary log is on or off when performing this check. Workaround: switch to MIXED mode for your installation (as should be the default for new installations using the config file generated by the installer).
[29 Oct 2008 15:26]
David Stavisski
Unfortunately the workaround does not work (or at least not completely): if you set: binlog_format=mixed without enabling the bin-log, the serve does not start complaining that the bon-log is off. You have to enable it: log-bin=mysql-bin which carries a performance penalty. Thx, David Stavisski
[29 Oct 2008 15:39]
Mikhail Izioumtchenko
Assigning to Vasil. R/E values based on the assumed one line fix. It would be also nice to add a test case for this.
[29 Oct 2008 17:34]
Mike Mulligan
I receive this same error when trying to use MySQL 5.1.29 as a respository for Sun Identity Manager 7.1.1 using Netbeans 6.1. When I set the repository in Netbeans and select MySQL JDBC driver, I get: java.sql.SQLException: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' I had to roll back to MySQL 5.0.67 to get Sun IdM/Netbeans to work.
[29 Oct 2008 21:08]
Vasil Dimov
From sql/log.h:
556 enum enum_binlog_format {
557 /*
558 statement-based except for cases where only row-based can work (UUID()
559 etc):
560 */
561 BINLOG_FORMAT_MIXED= 0,
562 BINLOG_FORMAT_STMT= 1, // statement-based
563 BINLOG_FORMAT_ROW= 2, // row_based
564 /*
565 This value is last, after the end of binlog_format_typelib: it has no
566 corresponding cell in this typelib. We use this value to be able to know if
567 the user has explicitely specified a binlog format at startup or not.
568 */
569 BINLOG_FORMAT_UNSPEC= 3
570 };
So, why is the value returned by thd_binlog_format() BINLOG_FORMAT_STMT when no --binlog_format has been specified at startup?
[30 Oct 2008 7:01]
Vasil Dimov
Sidenote: This problem is present in 5.1.28 too, and I guess has always been there: Start mysqld without any binlogging options: Version: '5.1.28-rc' socket: '/tmp/mysql.sock' port: 0 FreeBSD port: mysql-server-5.1.28_1 mysql> set tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> set binlog_format='statement'; Query OK, 0 rows affected (0.00 sec) mysql> create table t (a int) engine=innodb; Query OK, 0 rows affected (0.13 sec) mysql> insert into t values (1); ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' It has just not been discovered because noone bothered to set binlog_format without turning binlogging on (and also setting tx_isolation to 'read-committed').
[30 Oct 2008 11:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57440 2777 Georgi Kodinov 2008-10-30 Bug #40360: Binlog related errors with binlog off Fixed MySQL to return BINLOG_FORMAT_UNSPEC when bin_log is off and no binlog_format was specified.
[30 Oct 2008 11:37]
Georgi Kodinov
Proposed fix to MySQL committed
[30 Oct 2008 11:50]
Vasil Dimov
From the commit message: connect(localhost,root,,test,12000,/home/kgeorge/mysql/bzr/B40360-5.1-5.1.29-rc/mysql-test/var/tmp/master.sock); are you sure the above is correct?
[30 Oct 2008 13:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57450 2773 Mats Kindahl 2008-10-30 Bug #40360: Binlog related errors with binlog off When statement-based replication is used, and the transaction isolation level is READ-COMMITTED or stricter, InnoDB will print an error because statement-based replication might lead to inconsistency between master and slave databases. However, when the binary log is not engaged, this is not an issue and an error should not be printed. This patch extends the check in InnoDB to also check if the binary log is engaged for the thread, that is, open and with SQL_LOG_BIN = 1.
[30 Oct 2008 14:00]
Mats Kindahl
The submitted patch will is a tentative patch for fixing the issue, awaiting the final review by Innobase. The patch contain changes to both the server and the InnoDB handler.
[30 Oct 2008 14:15]
Vasil Dimov
Just for the records: The check inside ha_innobase::external_lock() probably originated from the fix of Bug#23051 READ COMMITTED breaks mixed and statement-based replication.
[3 Nov 2008 11:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57684 2773 Mats Kindahl 2008-11-03 Bug #40360: Binlog related errors with binlog off When statement-based replication is used, and the transaction isolation level is READ-COMMITTED or stricter, InnoDB will print an error because statement-based replication might lead to inconsistency between master and slave databases. However, when the binary log is not engaged, this is not an issue and an error should not be printed. This patch makes thd_binlog_format() return BINLOG_FORMAT_ UNSPEC when the binary log is not engaged for the given thread.
[3 Nov 2008 17:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57711 2774 Mats Kindahl 2008-11-03 Bug #40360: Binlog related errors with binlog off Adding missing drop of created table and tidying display.
[4 Nov 2008 9:20]
Bugs System
Pushed into 5.1.30 (revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (version source revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (pib:5)
[4 Nov 2008 16:26]
Paul DuBois
Noted in 5.1.30 changelog. With statement-based binary logging format and a transaction isolation level of READ COMMITTED or stricter, InnoDB printed an error because statement-based logging might lead to inconsistency between master and slave databases. However, this error was printed even when binary logging was not enabled (in which case, no such inconsistency can occur).
[4 Nov 2008 16:26]
Paul DuBois
Setting report to NDI pending push into 6.0.x.
[5 Nov 2008 10:28]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=40513 was marked as duplicate of this one.
[10 Nov 2008 10:50]
Bugs System
Pushed into 6.0.8-alpha (revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (version source revid:kgeorge@mysql.com-20081106161825-9biri1v40v7b0kg3) (pib:5)
[10 Nov 2008 19:12]
Paul DuBois
Note in 6.0.8 changelog.
[11 Nov 2008 16:33]
Paul DuBois
6.0.9 changelog, not 6.0.8.
[21 Nov 2008 21:02]
Bugs System
Pushed into 5.1.30-ndb-6.2.17 (revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (version source revid:tomas.ulin@sun.com-20081121161314-jhz3twf5xjt8stnh) (pib:5)
[21 Nov 2008 23:45]
Bugs System
Pushed into 5.1.30-ndb-6.3.20 (revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (version source revid:tomas.ulin@sun.com-20081121210644-zas6e9wa5kuj7d6f) (pib:5)
[27 Nov 2008 14:52]
Bugs System
Pushed into 5.1.30-ndb-6.4.0 (revid:mats@sun.com-20081103174647-s4jyy5vqe0eo68q0) (version source revid:tomas.ulin@sun.com-20081126125835-5sohkzk2jjwpq1wp) (pib:5)
[6 Feb 2009 3:48]
James Day
Note that if you use READ-COMMITTED and binary logging (including for replication) 5.1 you must use row-based binlogging. It's expected that you will see this error if you try to add rows to an InnoDB table otherwise. For more information see the manual at http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html . You should change the transaction isolation level or the binary logging mode.
[5 May 2010 15:21]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 16:52]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:51]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:20]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:49]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:24]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[15 Jun 2010 8:17]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:34]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:52]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:29]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:17]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
