| Bug #53075 | SBR: Strange warning around CONNECTION_ID() | ||
|---|---|---|---|
| Submitted: | 22 Apr 2010 18:12 | Modified: | 8 May 2010 17:03 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6.99-m4 | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
| Tags: | CONNECTION_ID | ||
[22 Apr 2010 18:36]
Matthias Leich
./client/mysqlbinlog var/log/main.rpl_ml4/mysqld.1/data/master-bin.000001 shows ... # at 1255 #100422 20:32:29 server id 1 end_log_pos 1352 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1271961149/*!*/; SET @@session.pseudo_thread_id=3/*!*/; UPDATE t1 SET f1 = CONNECTION_ID() /*!*/; So IMHO CONNECTION_ID() shoudn't be unsafe in SBR.
[23 Apr 2010 0:08]
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/106403 3015 Alfranio Correia 2010-04-23 BUG#53075 SBR: Strange warning around CONNECTION_ID Statements with CONNECTION_ID were forced to be kept in the transactional cache and by consequence non-transactional changes that were supposed to be flushed ahead of the transaction were kept in the transactional cache. This happened because after BUG#51894 any statement whose thd's thread_specific_used was set was kept in the transactional cache. The idea was to keep changes on temporary tables in the transactional cache. However, the thread_specific_used was set not only for statements that accessed temporary tables but also when the CONNECTION_ID was used. To fix the problem, we created a new variable to keep track of updates to temporary tables. @ sql/log_event.cc Uses the thread_temporary_used to decide if a statement should be kept in the transactional cache or not. @ sql/sql_class.cc Sets the thread_temporary_used while calling the decide_logging_format. @ sql/sql_class.h Defines the thread_temporary_used. @ sql/sql_parse.cc Resets the thread_temporary_used.
[23 Apr 2010 0:11]
Alfranio Tavares Correia Junior
This is a regression of BUG#51894.
[23 Apr 2010 8:58]
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/106414 3015 Alfranio Correia 2010-04-23 BUG#53075 SBR: Strange warning around CONNECTION_ID Statements with CONNECTION_ID were forced to be kept in the transactional cache and by consequence non-transactional changes that were supposed to be flushed ahead of the transaction were kept in the transactional cache. This happened because after BUG#51894 any statement whose thd's thread_specific_used was set was kept in the transactional cache. The idea was to keep changes on temporary tables in the transactional cache. However, the thread_specific_used was set not only for statements that accessed temporary tables but also when the CONNECTION_ID was used. To fix the problem, we created a new variable to keep track of updates to temporary tables. @ mysql-test/suite/rpl/r/rpl_temp_temporary.result Added a test case. @ mysql-test/suite/rpl/t/rpl_temp_temporary.test Added a test case. @ sql/log_event.cc Uses the thread_temporary_used to decide if a statement should be kept in the transactional cache or not. @ sql/sql_class.cc Sets the thread_temporary_used while calling the decide_logging_format. @ sql/sql_class.h Defines the thread_temporary_used. @ sql/sql_parse.cc Resets the thread_temporary_used.
[26 Apr 2010 9:03]
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/106478 3016 Alfranio Correia 2010-04-26 BUG#53075 SBR: Strange warning around CONNECTION_ID Statements with CONNECTION_ID were forced to be kept in the transactional cache and by consequence non-transactional changes that were supposed to be flushed ahead of the transaction were kept in the transactional cache. This happened because after BUG#51894 any statement whose thd's thread_specific_used was set was kept in the transactional cache. The idea was to keep changes on temporary tables in the transactional cache. However, the thread_specific_used was set not only for statements that accessed temporary tables but also when the CONNECTION_ID was used. To fix the problem, we created a new variable to keep track of updates to temporary tables. @ mysql-test/suite/rpl/r/rpl_temp_temporary.result Added a test case. @ mysql-test/suite/rpl/t/rpl_temp_temporary.test Added a test case. @ sql/log_event.cc Uses the thread_temporary_used to decide if a statement should be kept in the transactional cache or not. @ sql/sql_class.cc Sets the thread_temporary_used while calling the decide_logging_format. @ sql/sql_class.h Defines the thread_temporary_used. @ sql/sql_parse.cc Resets the thread_temporary_used.
[27 Apr 2010 9:45]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)
[27 Apr 2010 9:48]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:50]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 13:39]
Jon Stephens
Documented bugfix in the 5.5.5 and 6.0.14 changelogs as follows:
When using statement-based logging, statements that used
CONNECTION_ID() were always kept in the transaction cache;
consequently, nontransactional changes that should have been
flushed before the transaction were kept in the transaction
cache.
Closed.
[29 Apr 2010 10:44]
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/106919 3026 Alfranio Correia 2010-04-29 Post-merge fix for BUG#51894, BUG#53075 thread_temporary_used is not initialized causing valgrind's warnings.
[7 May 2010 9:21]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[8 May 2010 17:03]
Paul DuBois
Already fixed in 5.5.x, 6.0.x.
Description: My script: ---------- --source include/master-slave.inc CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM; INSERT INTO t1 SET f1 = 1; CREATE TABLE t2 (f1 BIGINT) ENGINE = MyISAM; INSERT INTO t2 SET f1 = 1; SET SESSION BINLOG_FORMAT = STATEMENT; SET SESSION AUTOCOMMIT = 0; COMMIT; UPDATE t1 SET f1 = CONNECTION_ID(); UPDATE t2 SET f1 = 2; Result on 5.6.99-m4 mysql-next-mr-bugfixing revno: 3147 2010-04-22 ---------------------------------------------- ... CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM; INSERT INTO t1 SET f1 = 1; CREATE TABLE t2 (f1 BIGINT) ENGINE = MyISAM; INSERT INTO t2 SET f1 = 1; SET SESSION BINLOG_FORMAT = STATEMENT; SET SESSION AUTOCOMMIT = 0; COMMIT; UPDATE t1 SET f1 = CONNECTION_ID(); UPDATE t2 SET f1 = 2; Warnings: Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. 1. There are no transactional reads or writes before the UPDATE t2 SET f1 = 2. So the warning is plain wrong. 2. If I replace the UPDATE t1 SET f1 = CONNECTION_ID(); with UPDATE t1 SET f1 = 13 than the warning disappears at all. 3. If I use UPDATE t1 SET f1 = UUID(); instead I get UPDATE t1 SET f1 = UUID() Warnings: Warning 1366 Incorrect integer value: ...for column 'f1' at row 1 <= correct, I really assign garbage Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statement uses a system function whose value may differ on slave. <== correct, this is unsafe The interesting fact is that - the warning depends on use of CONNECTION_ID within the preceding statement - the informations about CONNECTION_ID and replication are inconsistent http://bugs.mysql.com/bug.php?id=29928 Make CONNECTION_ID safe for SBR by using some tricky SET pseudo_thread_id= XXX http://forge.mysql.com/wiki/MySQL_Internals_Replication ... Note: the following non-deterministic functions are not marked unsafe: CONNECTION_ID (Query_log_events contain the connection number) <== If it is unsafe, why not mark it as unsafe ? I will add results for other MySQL versions soon. How to repeat: See above