| Bug #18350 | Use consistent read in CREATE ... SELECT... if innodb_locks_unsafe_for_binlog | ||
|---|---|---|---|
| Submitted: | 20 Mar 2006 11:43 | Modified: | 27 Apr 2006 17:22 |
| Reporter: | Heikki Tuuri | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Any (All) |
| Assigned to: | Bugs System | Target Version: | |
[21 Mar 2006 10:57]
Jan Lindström
Applied to 5.0 and 5.1 in InnoDB internal svn.
[24 Apr 2006 13:42]
Alexander Ivanov
Fixed in 5.0.21 and 5.1.10-beta.
[27 Apr 2006 17:22]
Paul DuBois
Noted in 5.0.21, 5.1.10 changelogs. <literal>InnoDB</literal> did not use a consistent read for <literal>CREATE ... SELECT</literal> when <literal>innodb_locks_unsafe_for_binlog</literal> was set. (Bug #18350)
Description: In 5.0, InnoDB sets prebuilt->select_lock_type=LOCK_S in CREATE ... SELECT... even if innodb_locks_unsafe_for_binlog is in my.cnf. It should use a consistent read. Workaround: create the table first and use INSERT ... SELECT ... How to repeat: See above. Suggested fix: In ha_innodb.cc, in ::store_lock(): " if (srv_locks_unsafe_for_binlog && prebuilt->trx->isolation_level != TRX_ISO_SERIALIZABLE && (lock_type == TL_READ || lock_type == TL_READ_NO_INSERT) && (thd->lex->sql_command == SQLCOM_INSERT_SELECT || thd->lex->sql_command == SQLCOM_UPDATE)) { /* In case we have innobase_locks_unsafe_for_binlog option set and isolation level of the transaction is not set to serializable and MySQL is doing INSERT INTO...SELECT or UPDATE ... = (SELECT ...) without FOR UPDATE or IN SHARE MODE in select, then we use consistent read for select. */ prebuilt->select_lock_type = LOCK_NONE; prebuilt->stored_select_lock_type = LOCK_NONE; " replace: (thd->lex->sql_command == SQLCOM_INSERT_SELECT || thd->lex->sql_command == SQLCOM_UPDATE)) { by: (thd->lex->sql_command == SQLCOM_INSERT_SELECT || thd->lex->sql_command == SQLCOM_UPDATE || thd->lex->sql_command == SQLCOM_CREATE_TABLE)) {