| Bug #22898 | Wrong binary log replay for substitution "IS NULL" -> "= <LAST_INSERT_ID>" | ||
|---|---|---|---|
| Submitted: | 2 Oct 2006 16:08 | Modified: | 28 May 2007 8:17 |
| Reporter: | Tomash Brechko | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1.12 | OS: | Any |
| Assigned to: | Chuck Bell | CPU Architecture: | Any |
[3 Oct 2006 12:11]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.1.12-BK on Linux.
[28 May 2007 8:17]
Lars Thalmann
On Thu, May 24, 2007 at 04:25:22PM +0200, Sergei Golubchik wrote: > This is clearly documented in the manual, and as such, it's not a bug: > > * You can find the row that contains the most recent > `AUTO_INCREMENT' value by issuing a statement of the > following form immediately after generating the value: > > SELECT * FROM TBL_NAME WHERE AUTO_COL IS NULL > > This behavior can be disabled by setting > `SQL_AUTO_IS_NULL=0'. See *Note set-option::. > > > The main questions are: > > > > 1. Can (*) be used in other connections to find the last auto > > increment number, or can it only find the value of the same > > connection? > > Note above "immediately after generating the value". > No. > > > 2. Can (*) be used only in the next-coming query or can it be used > > forever in the same thread to find the last auto inc value? > > Only.
Description: See "How to repeat" section. Version 5.0 and below does not have this bug. How to repeat: With enabled binary log: Connection 1 Connection 2 0 DROP TABLE IF EXISTS t1, t2; 1 CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY); 2 CREATE TABLE t2 (i INT); 3 INSERT INTO t2 VALUES (123); 4 INSERT INTO t1 VALUES (NULL); 5 INSERT INTO t2 SELECT * FROM t1 WHERE i IS NULL; 6 SELECT * FROM t2; #+------+ #| i | #+------+ #| 123 | #+------+ But if you replay binary log, you'll get: SELECT * FROM t2; #+------+ #| i | #+------+ #| 123 | #| 1 | #+------+ Suggested fix: In 5.1.12, THD::substitute_null_with_insert_id is set in THD::cleanup_after_query(). But this function is not suitable for setting thread data to some non-default values, as there is only one replication thread, so different master threads may overwrite data of each other on slave. In the example, statement 3 does not set THD::substitute_null_with_insert_id in THD::cleanup_after_query(), but statement 4 does. And in a binary log statement 4 will come between 3 and 5, so for 5 THD::substitute_null_with_insert_id will be set when replaying binary log. THD::substitute_null_with_insert_id should be set elsewhere.