| Bug #33669 | Transactional temporary tables do not work under --read-only | ||
|---|---|---|---|
| Submitted: | 3 Jan 2008 18:26 | Modified: | 13 Mar 2010 17:44 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S2 (Serious) |
| Version: | 5.1.41, 6.0.4-p2 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | falcon, innodb, replication, temporary tables, transactions | ||
[3 Jan 2008 18:26]
Philip Stoev
[18 Jan 2008 16:49]
Philip Stoev
Setting to verified. Can you please triage?
[13 Feb 2008 20:38]
Kevin Lewis
Add a comment to release notes for 6.0.4...
[13 Feb 2008 21:34]
James Day
This is going to be fixed, just not in 6.0.4. Likely soon after.
[20 Feb 2008 23:09]
Kevin Lewis
The workaround is to not use --read-only. Even though this is a very common setting for a replication slave, it is acceptable for a beta release to not use this setting, considering that this will not be used in a production environment.
[20 Feb 2008 23:27]
Kevin Lewis
I am lowering the Impact to substantial because even though a replication slave normaly uses --readonly, the only way that a slave would use temporary tables is when replication is statement based. But Falcon only uses row-based replication. So temporary tables will not normally be used on a Falcon replication slave.
[27 Feb 2008 21:45]
James Day
Creating temporary tables while read_only is set is supported so that applications can do this, not for replication. Temporary tables will quite often be used on a replication slave with Falcon. See bug #4544 where the over-strict behavior of read_only blocking temporary tables was corrected in 5.0.16. Use is common in applications like message boards, where search results will be stored in a temporary table and then users will page through the temporary table with cheap queries instead of repeating the search. For example, I think that vBulletin uses this method. Other applications include breaking down queries into more efficient pieces. It's not ubiquitous but it's common. Turning off read_only gives the applications too much capability to change data on the slave and make master and slave inconsistent, so it's not an acceptable workaround. Permissions aren't really a good workaround because slaves are commonly used in failover sets and permission changing complicates failover.
[16 Oct 2008 16:57]
Kevin Lewis
Sergey, This just got bumped to SR60BETA. Please take a look. Ann is busy with other things.
[18 Nov 2008 14:10]
Sergey Vojtovich
The bug affects all transactional storage engines. Reassigning to runtime team as discussed before.
[20 Jan 2010 21:01]
Philip Stoev
Bug requires non-SUPER user and Innodb to reproduce.
[20 Jan 2010 21:18]
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/97660 3327 Davi Arnaut 2010-01-20 Bug#33669: Transactional temporary tables do not work under --read-only The problem was that in read only mode (read_only enabled), the server would mistakenly deny data modification attempts for temporary tables which belong to a transactional storage engine (eg. InnoDB). The solution is to allow transactional temporary tables to be modified under read only mode. As a whole, the read only mode does not apply to any kind of temporary table. @ mysql-test/r/read_only_innodb.result Add test case result for Bug#33669 @ mysql-test/t/read_only_innodb.test Add test case for Bug#33669 @ sql/lock.cc Allow write locks to temporary tables even under read-only.
[20 Jan 2010 21:28]
Davi Arnaut
The above patch only partially fixes the issue, so far it only works with replication disabled. There is a bigger problem due to commit/rollback not being allowed under read-only. Currently there is no way to check whether a transaction only changes temporary tables.
[21 Jan 2010 3:17]
James Day
Davi, 1. Incoming change is via replication. The replication thread is allowed to commit regardless of whether it's temporary or not because it runs as SUPER. 2. Incoming is via end user attempt. Read_only blocks non-SUPER users from committing non-temporary table changes and those changes need to be blocked whether it's a transactional or non-transactional engine. SUPER can commit anyway. In case 2 with the non-SUPER user committing how can there be a non-temporary table affected by a commit if the engines and SQL layer are blocking changes to the tables because the server is read_only?
[21 Jan 2010 10:28]
Konstantin Osipov
James, there may be pending changes from transactions that started before the server entered read-only mode, and we have no way to check it. Thus we currently block all COMMITs.
[21 Jan 2010 10:29]
Philip Stoev
Maybe moving to read-only mode should cause an implicit commit of all pending transactions?
[21 Jan 2010 11:26]
Konstantin Osipov
Philip, this is technically hard to accomplish - one needs to iterate over all THDs and commit their transactions. Whereas the bug can have a good fix in next-4284 tree, where we have an account inside MDL what tables a transaction has modified, and whether they are temporary or not. I would consider even the partial fix, commmitted by Davi yesterday, which only allows to use temporary tables in --read-only to be too risky for 5.1, as I don't fully understand its effects on the binary log. Perhaps after replication sign-off we could push it into 5.1. However, the full fix, that is, this bug (33669) and a better fix for Bug#11733/Bug#22009, which only disallows COMMIT in read-only if it touches non-temporary tables, needs to go into Celosia+.
[21 Jan 2010 18:26]
Alfranio Tavares Correia Junior
Hi all,
From the point of view of the replication, there is no risk with the current
patch. Note, however, that replication (binlog) must be disabled if one wants
to create, drop and "update temporary" tables while the read only mode is enabled. The limitation happens because the binary log registers itself as a transaction handler and by consequence the following code is executed in the handler.cc (int ha_commit_trans(THD *thd, bool all)):
if (rw_trans &&
opt_readonly &&
!(thd->security_ctx->master_access & SUPER_ACL) &&
!thd->slave_thread)
{
my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
ha_rollback_trans(thd, all);
error= 1;
goto end;
}
and the transaction aborts.
Unfortunately, as pointed out by Davi and Kostja, in 5.1, there are no means
to figure out if a transaction has changed only temporary tables or not when
it is about to commit.
----------
Said that. I have a couple of questions on the handler:
1 - Why is rw_trans= is_real_trans && (rw_ha_count > 0) and not simply rw_trans = (rw_ha_count > 0)?
2 - Would it be possible to change the if above by something like
if (rw_ha_count > 0 &&
opt_readonly &&
!(thd->security_ctx->master_access & SUPER_ACL) &&
!thd->slave_thread &&
thd->open_tables)
If we could change the code the limitation described above would no longer be valid.
Cheers.
[21 Jan 2010 18:35]
Alfranio Tavares Correia Junior
Hi all,
Please, ignore the following comment ([21 Jan 19:26] Alfranio Correia, 2 - Would it be possible to change the if above by something like).
3 - Would it be possible to change the if above by something like
if (rw_ha_count > 0 &&
opt_readonly &&
!(thd->security_ctx->master_access & SUPER_ACL) &&
!thd->slave_thread)
If so, we could throw an error early instead of waiting for the commit.
Cheers.
[12 Feb 2010 23:12]
Siva Nookala
Found a solution. I can insert into the temporary table if I create it using MEMORY engine.
[12 Feb 2010 23:12]
Siva Nookala
I can insert into the temporary table if I create it using MEMORY engine.
[8 Mar 2010 18: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/102611 3119 Davi Arnaut 2010-03-08 Bug#33669: Transactional temporary tables do not work under --read-only The problem was that in read only mode (read_only enabled), the server would mistakenly deny data modification attempts for temporary tables which belong to a transactional storage engine (eg. InnoDB). The solution is to allow transactional temporary tables to be modified under read only mode. As a whole, the read only mode does not apply to any kind of temporary table. @ mysql-test/r/read_only_innodb.result Add test case result for Bug#33669 @ mysql-test/t/read_only_innodb.test Add test case for Bug#33669 @ sql/lock.cc Rename mysql_lock_tables_check to lock_tables_check and make it static. Move locking related checks from get_lock_data to lock_tables_check. Allow write locks to temporary tables even under read-only.
[10 Mar 2010 13:37]
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/102886 2990 Davi Arnaut 2010-03-10 Bug#33669: Transactional temporary tables do not work under --read-only The problem was that in read only mode (read_only enabled), the server would mistakenly deny data modification attempts for temporary tables which belong to a transactional storage engine (eg. InnoDB). The solution is to allow transactional temporary tables to be modified under read only mode. As a whole, the read only mode does not apply to any kind of temporary table. @ mysql-test/r/read_only_innodb.result Add test case result for Bug#33669 @ mysql-test/t/read_only_innodb.test Add test case for Bug#33669 @ sql/lock.cc Rename mysql_lock_tables_check to lock_tables_check and make it static. Move locking related checks from get_lock_data to lock_tables_check. Allow write locks to temporary tables even under read-only.
[10 Mar 2010 16:34]
Davi Arnaut
Queued to mysql-trunk-bugfixing
[12 Mar 2010 18:10]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100312180926-0emfjrj8e9xnvl8h) (version source revid:alik@sun.com-20100312180447-2r0ak22y13s05134) (merge vers: 6.0.14-alpha) (pib:16)
[12 Mar 2010 18:11]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100312180340-0qpnap25xh4r5ql8) (version source revid:alik@sun.com-20100312180340-0qpnap25xh4r5ql8) (merge vers: 5.5.3-m3) (pib:16)
[12 Mar 2010 18:11]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100312180838-rk60kn38g0qwt78n) (version source revid:alik@sun.com-20100312180435-wk7nvsbfntfus5bu) (pib:16)
[13 Mar 2010 17:44]
Paul DuBois
Noted in 5.5.3, 6.0.14 changelogs. When read_only was enabled, the server incorrectly prevented data modifications to TEMPORARY tables belonging to transactional storage engines such as InnoDB.
[28 Oct 2010 11:46]
Tom Jansen
I'm also hitting this problem on mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------+ | Variable_name | Value | +-------------------------+------------------+ | innodb_version | 1.0.10 | | protocol_version | 10 | | version | 5.1.49-1 | | version_comment | (Debian) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | +-------------------------+------------------+ I can't seem to find a changelog item is 5.1.x where x >= 49 that reports fixing this bug. Any chance it will be in upcoming 5.1 releases?
