From: Dmitry Lenev Date: May 26 2011 10:57am Subject: bzr commit into mysql-5.5 branch (Dmitry.Lenev:3404) Bug#11762012 List-Archive: http://lists.mysql.com/commits/138182 X-Bug: 11762012 Message-Id: <20110526105716.53D3874067D@bandersnatch> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0013002167==" --===============0013002167== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/dlenev/src/bzr/mysql-5.5-11762012/ based on revid:anitha.gopi@stripped 3404 Dmitry Lenev 2011-05-26 Fix for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". Attempt to update an InnoDB temporary table under LOCK TABLES led to assertion failure in both debug and production builds if this temporary table was explicitly locked for READ. The same scenario works fine for MyISAM temporary tables. The assertion failure was caused by discrepancy between lock that was requested on the rows of temporary table at LOCK TABLES time and by update operation. Since SQL-layer requested a read-lock at LOCK TABLES time InnoDB engine assumed that upcoming statements which are going to be executed under LOCK TABLES will only read table and therefore should acquire only S-lock. An update operation broken this assumption by requesting X-lock. Possible approaches to fixing this problem are: 1) Skip locking of temporary tables as locking doesn't make any sense for connection-local objects. 2) Prohibit changing of temporary table locked by LOCK TABLES ... READ. Unfortunately both of these approaches have drawbacks which make them unviable for stable versions of server. So this patch takes another approach and changes code in such way that LOCK TABLES for a temporary table will always request write lock. In 5.5 version of this patch switch from read lock to write lock is done on SQL-layer. @ mysql-test/suite/innodb/r/innodb_mysql.result Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ mysql-test/suite/innodb/t/innodb_mysql.test Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ sql/sql_parse.cc Since a temporary table locked by LOCK TABLES can be updated even if it was only locked for read we always request TL_WRITE locks for such tables at LOCK TABLES time. This allows to avoid discrepancy between locks acquired at LOCK TABLES time and by a statement executed under LOCK TABLES. Such a discrepancy has caused problems for InnoDB storage engine. To support this change a part of code implementing LOCK TABLES has been moved to a helper function. modified: mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/innodb/t/innodb_mysql.test sql/sql_parse.cc === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-12-08 14:47:47 +0000 +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-05-26 10:57:08 +0000 @@ -2663,7 +2663,6 @@ COUNT(*) 1537 SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; -End of 5.1 tests # # Test for bug #39932 "create table fails if column for FK is in different # case than in corr index". @@ -2685,6 +2684,23 @@ t2 CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2, t1; # +# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +# +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +# Even though temporary table was locked for READ we +# still allow writes to it to be compatible with MyISAM. +# This is possible since due to fact that temporary tables +# are specific to connection and therefore locking for them +# is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; +End of 5.1 tests +# # Bug#44613 SELECT statement inside FUNCTION takes a shared lock # DROP TABLE IF EXISTS t1; === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test' --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-12-20 12:17:17 +0000 +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2011-05-26 10:57:08 +0000 @@ -830,8 +830,6 @@ SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; ---echo End of 5.1 tests - --echo # --echo # Test for bug #39932 "create table fails if column for FK is in different @@ -852,6 +850,28 @@ drop table t2, t1; --echo # +--echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +--echo # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +--echo # Even though temporary table was locked for READ we +--echo # still allow writes to it to be compatible with MyISAM. +--echo # This is possible since due to fact that temporary tables +--echo # are specific to connection and therefore locking for them +--echo # is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; + +--echo End of 5.1 tests + + +--echo # --echo # Bug#44613 SELECT statement inside FUNCTION takes a shared lock --echo # === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2011-05-21 08:21:08 +0000 +++ b/sql/sql_parse.cc 2011-05-26 10:57:08 +0000 @@ -1749,6 +1749,64 @@ bool sp_process_definer(THD *thd) /** + Auxiliary call that opens and locks tables for LOCK TABLES statement + and inits list of locked tables. + + @param thd Thread context. + @param tables List of tables to be locked. + + @return FALSE in case of success, TRUE in case of error. +*/ + +static bool lock_tables_open_and_lock_tables(THD *thd, TABLE_LIST *tables) +{ + Lock_tables_prelocking_strategy lock_tables_prelocking_strategy; + uint counter; + TABLE_LIST *table; + + thd->in_lock_tables= 1; + + if (open_tables(thd, &tables, &counter, 0, &lock_tables_prelocking_strategy)) + goto err; + + /* + We allow to change temporary tables even if they were locked for read + by LOCK TABLES. To avoid discrepancy between lock acquired at LOCK TABLES + time and by the statement which is later executed under LOCK TABLES we + ensure that for temporary tables we always request write lock (such a + discrepancy can cause problems in storage engine). + */ + for (table= tables; table; table= table->next_global) + if (!table->placeholder() && table->table->s->tmp_table) + table->table->reginfo.lock_type= TL_WRITE; + + if (lock_tables(thd, tables, counter, 0) || + thd->locked_tables_list.init_locked_tables(thd)) + goto err; + + thd->in_lock_tables= 0; + + return FALSE; + +err: + thd->in_lock_tables= 0; + + trans_rollback_stmt(thd); + /* + Need to end the current transaction, so the storage engine (InnoDB) + can free its locks if LOCK TABLES locked some tables before finding + that it can't lock a table in its list + */ + trans_commit_implicit(thd); + /* Close tables and release metadata locks. */ + close_thread_tables(thd); + DBUG_ASSERT(!thd->locked_tables_mode); + thd->mdl_context.release_transactional_locks(); + return TRUE; +} + + +/** Execute command saved in thd and lex->sql_command. @param thd Thread handle @@ -3093,31 +3151,11 @@ end_with_restore_list: goto error; thd->variables.option_bits|= OPTION_TABLE_LOCK; - thd->in_lock_tables=1; - { - Lock_tables_prelocking_strategy lock_tables_prelocking_strategy; - - res= (open_and_lock_tables(thd, all_tables, FALSE, 0, - &lock_tables_prelocking_strategy) || - thd->locked_tables_list.init_locked_tables(thd)); - } - - thd->in_lock_tables= 0; + res= lock_tables_open_and_lock_tables(thd, all_tables); if (res) { - trans_rollback_stmt(thd); - /* - Need to end the current transaction, so the storage engine (InnoDB) - can free its locks if LOCK TABLES locked some tables before finding - that it can't lock a table in its list - */ - trans_commit_implicit(thd); - /* Close tables and release metadata locks. */ - close_thread_tables(thd); - DBUG_ASSERT(!thd->locked_tables_mode); - thd->mdl_context.release_transactional_locks(); thd->variables.option_bits&= ~(OPTION_TABLE_LOCK); } else --===============0013002167== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dmitry.lenev@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dmitry.lenev@stripped # target_branch: file:///home/dlenev/src/bzr/mysql-5.5-11762012/ # testament_sha1: f789d29b04f321c1b5b82ea25995190ef9c41e55 # timestamp: 2011-05-26 14:57:15 +0400 # base_revision_id: anitha.gopi@stripped\ # 02clhxzjvc2hapdp # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWctcfRUABez/gHAQAIB59/// f+//4L////5gDs3Xn3rny+2919e7333nQA73bztkNvc7vt1b7vntPbXz2bm5ZzZrnfQdPB4SiJo0 hPJlPEBT2VPaaiPRlHqepo00AZAGhoGgkpomQ0yI00mIyp7RT0Q00ZqMg00NAAAGgZAjVMJomI1M T0BGgZGgAAAANAaCRFNAhNo0SbJk0psE1P1CNANNNDTQANANBFJNASYaTJgielPU/Qm1Go8poeo8 psoAANGICRIhomQ0Kn4RTeqb0JmVHlPUNBo2kDTQAGmgcgetROvtxWUiiXlWxomZ2i3FwxtRn1rP l2dUUd1l8do91SnN7/K3Po/qfrCUZR1250jAQWTZRWVduYbMz/jI3QlZnam222ytlOION/k2aosv M0L0phaDLiNTRvZ5WsHiQHfR3iIt5M85crrzTWzanOObVjbVGXfedjRmIA3XUAhYDRBpIByROAIh exNyGFLux/jAYeJoMft48J+J36Uum9r/LyMREQsQYjk7j2B56r9/RTMnVRh2y3V9LInBJ+Sl0i4j ybTLkwL6FbH3YnoNS8EPve8+k4XEpyEZnQ2IVllul9JW1dWsZlgYio3Ags438nCwau8qZffhY/wO pH7oPUCMksCohGXGLtZcMeBox7npy9ZM8Nx3R2gyqhE6z2JjEKliCmMcsXwFvW9WcHEo2DusqWkJ QBEGyH200QZGKr5NqLxQW1zTGW2OXNZQ3u5uGMJWYWTfEqQB76sPksfXaEIDmwM8Z5UlCeVBpWTd 0joOfsHk9pg8rqNc77EzWNf0cMokhmdsufBB+8lj2a2O+adk46iISVVdAmYokUUaMRsgikJGzkVq KHPKWhYRhC2WwW6ck6x8my6yeXRqLxn5jgeXDM0uj6PMPNMmmzaZNnNr5WE9dsB+0svz/KZ7BuFZ wtLMt4C6cum4ObxErGyrjSaDWqKL/tQjAOaasYVHg4PfdLDvVBxVLzF7gesKuiS8QEpx9J49+7w0 SR0ru3xvoT9vg2odQbUMrYl55GNtttsYxdzDnH5OnbKbSXCKHFiNObG7JEc91VdUb2I9sJzdAUYR H3o5jlQZ2IOlGeVaWA8jq8zwjgjoAO/REfTY4BzDintfBTwYmwRcomcQ6x1/9cmyErc9GzGGDwa6 oJNvPdO/c9jHxuGMnmvm0+XE5ywHmrPjeJGXesrBHlvfoj3nI8AYrtco2zRkuu5FfJOHfeJNi6vF CfUqgOuhWpKJpA22gDIRvnhrwuUguGBKlARekgwIjFvCQRWyCpttLFQGiasvKUGbC0qLi6jNtoUw zkiHAuY0dRsptTwyHBDBQwLITLwHo0G7s21FovGWmzuTqoXztRc5F1r7H6EjGBIyjFcEZLLg3x1i FDqivaxw47WIwFwfhE7uvrJ9CmXrY6/kGxeDp0v2sgjutzw0YuIXchsAAbiKo128KLpfQHXczmS+ fgMScCgIcwD07EaVN0SfRRgKFxYs3XnApuPEr+0RlK3PRrlCq64KTxzOJDyJcQhAYUzzLtWvXcjD jJ+3Yb+QjrWfFc1KU82vl0isjmA/N7zRZq+BB2+bx7qGCKWRl1pxSCYpwKzvB3NTC605dm7WW6i0 /2q3sQna1KWA6q0ersTeMoyVRLViqEEevjmjeEbVJG6EYCNo/VKEo5ppjs5FDTVrM5eddqhV15Xk UqUgtq1suY4vUBETGYnpz9ce15HWxU0sa5dlw+hBi4qbbkVwOfTbC5YTW1clJNWWpk9+pXbBFeHK PSXFnWyns20wNReRHQcLBjMqYJ3Pq6ICsqaLdkXbxxSoig7J+4eENtHx0BxKpBrXVJkrzAuMy0eY 1pMyZVuN4jo4lMMc5JYEjSNaC+NrUjUYwWFa0Mx0vQVmwRy9JVw3GVF2Oc8yMxlxdqpWm6RjW9S0 21TNasAwERbMEVK1RQLVpLAziS67FLXq3JCGEGvxlR3D2lRlKdeTfyCSgJQzky0b7FwkNDfV8S49 iPm33dT51MT6hQvHyPcaH2oaU99iVmtf8ToNZTNDQUMMREjyT2ChKyhBmVWVD9q0p6BpWF/s+4+h LUf7jEjGlCIc4gaKkF5QCsKxgEEiCaIuZ7BmS4pIqVcJew1IhExnYrj6jETD1we5B95rFC4mhcQu IhNAYiXrgodaEnhWRlJCViQH9WFCXoMJkDWhCawol9qDLftKkEC0npN4aUYgZjQghKEMTGixhoWN SHiXirCYqgjmeV1GJaOQkFhYxwghGyC0ZUiwhUa/lxMyGyQX+sZFZpRnNks5cg9vOiSV53Hf4ceC nBCYxT7yanVP0CGQho41C+z6164uLoLWpGi8bMAtepq9ZRgeg9R3yPLVd4nlK//sR80AnDnkIGbt a7zxQOFylhwLbHpPVJWA4UT0Evsvri/AC0usUoWBZRD0jsNwaKVQfQRNbiF56EA0GT8yBZbRKhMK 2asByDFlTTSjfaMKK7OGypVslUXKlpKFkoNcc2GBrlGufWVE1CILbdmuvmyrdtwEUU/J7nNffslo YxMaGhgQPG12NUhzQHVAUv2bhaC7sINgXPOV6if2yUVGQ4iGiVJ1QmqQ9+VK0tBtGMyXNgG49Kit VYicoelcLC8RRjMES0REAgbGgNqsWdAdXXumKH+LsWRYogvM0RmiK2sVNDnmpV0sraaqpKUy1Axw wgGKK++wSnhTwZa95jzqTKZUMoYYXI8VdaTK8GPUGsvYjuGtHWUDClAl1VmG9nf6S0FTPARot+ee UgNCahDtptTSaO9TPl2L0PHuSZ9i2WLxBuraLi8HqVlm1Iq4XsHqgoarAa296hAwd76uwructK7J CUWWokEXlxp5ouT/fZVH4Hd2LidpdgWuPIsVPR2LhrBVFkMe/jNLbBbLDwCKmi46LG86Sj2RwZ7y Zr6a65KLhya7d4/khhv1O5LjfYNeE1sU9Xbk8T7fXFqXNczSyFDAGbPaKwzQaFmzdXltEOApdx2H I2DvThG5Z4SPCbgnRf21SXTPVUUNtizduZXFc1iGprpZRu4hAZmlyny16g40PAggUoIbLUSa8a8Y LIYkxMKybSyWXq8bGwG3qQLCw4yzPtN7RpCSBBcXYODWEgZlOpNtaKl8TBzLlUoCjWAMrqgh9Ck2 oY2lxWqAabEOQwsdnlzW9q4nZYWd1q3nQDabXSMjWMgaxAgNyzY0Aj8lRV3KiS6xhlshddkAxm7u VkjGGqhpEyCXSpMFcrGtytziApLYm6296eizowtjqKGm+o0Yz07BIt18TliN4cRyY0Y26LSNpJbe CF8a9JoeIU+Y5xi7Vl3YElY7HNlsO3HvPgwTMybSB5/D3aN+/QlrbaaG896vOhNMbGM1i4CziOB5 nXka4bOXhgswyCVSgKuXH47tCyPJkT8S8y8uUBsPeElJHt5ySOHlo5xmu6DNCYwqqlHArvD428aZ Ljz2WIq5bOclr4Yd+1NNCHnQDUAxMynLpleziIYgKiuxOowNeeYO15GGF+agOuIIodbVR8rzsRPE gM6YS/VhiFMyIwM6514a/FBCxoRAA7wxEechQ5LKYXWMypejeELXRL/UdQhu7vXWIieVP6L67IlA oVEtTsnEd3iT5KYhrfVcTmzhqoTIgdJYTA4Xz1YVIrfgXUoVg7VBGCjCoLoGkeAybUxMmEBWzmUV At016WJWKYODowIknYLOKAVogn07l8apdbWEKjTHA1gpkgnAw0SZ6mzKJqfnhWM++i6KjkexiwS8 cbzaZMlDthFmDuK6TmrR30el5jVRpwyLkDhyyS+AG0jJ78nGiozNb1HGxTmWtCI9qA8CDyCVQbWv PIia2nqmaspu14E1it1qzPTLYIfuaWjScpJo1ohQqjghhwXOe9cRiGDXKshcaHkYlsMC8rZ4mlpD FM4uMTpLm5yAnK3arHOOlg1uUlELQ2JmMENDXmcPzNXHOomIVZZvCQk1M3TUI42POp0CBRNt4uXY 0jruXTHx5GidegmiwXfFHN5xqwT5XcUawzzTQPlyFtd27r9HQ2VqtpRsEehhuSEx9y8az0oxJwkN lDgVeffVGTVKh0biG4q62Cq5EiClNdnI4itWeSKuFIHAm4gd4twgi1Cq4uZcG3GpFZTKsdGpDmSb Oe8jpUnYzYuFK0yzMJ0XbgaWqCMLDs7HliIiIkzcyFNUqlaNBpTLpQxibLm1qIUD5IV4oUk97LiU aCa9cTF6UXkkBJ7TGSV2k1nTZSXFZ3vopUVKpYz3smM8GYFv15NdlqyECqsFaw1P29J7Z9jOZpFY pfnkIEbotFfkGNsZfKT4J6pAViXijOQ6EzxhiOCoYrKGJaoW+njYQ9KNLIWOuEqXQhY+jJU1u0lZ ltjgYU6C19FxBLNG2u5IyjShBcoXJKmoGRDKu2Sd06Nb8LDZr6xLt0eqCTWPWOMjfkm1Ghq66F5e iisKWBpKnbKK3PoYcGe57c0b2JIyZNeGZ3Gp05CUxwtZe+FrqLFvarmhLGAvaGRlg4mIEhLTB5bK enG8Dx03s8bAny52eyyHJXexKVEpaps5iwWzoR2Ev0aXC2njgeQdE3MjcyvDKq+RXmHs+ve3/i7k inChIZa4+io= --===============0013002167==--