From a63dde5a5b9a519ad2e4343c1d0b27de5bf41fc0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Apr 2008 20:01:20 +0400 Subject: Tentative implementation of WL#4165 Prepared statements: validation WL#4166 Prepared statements: automatic re-prepare Fixes Bug#27430 Crash in subquery code when in PS and table DDL changed after PREPARE Bug#27690 Re-execution of prepared statement after table was replaced with a view crashes Bug#27420 A combination of PS and view operations cause error + assertion on shutdown The basic idea of the patch is to keep track of table metadata between prepared statement prepare and execute. If some table used in the statement has changed, the prepared statement is re-prepared before execution. See WL#4165 and WL#4166 contents and comments in the code for details of the implementation. include/my_global.h: Remove 'register' keyword to avoid warnings when swapping large structures that don't fit into a register. Any modern compiler is capable of placing a variable in a register when that would benefit performance. mysql-test/r/ps_1general.result: Update test results: since now we re-prepare automatically, more correct results are produced in prepare-ddl-execute scenario. mysql-test/r/query_cache_merge.result: Ensure that the table definition cache is large enough for the test to pass in --ps-protocol mysql-test/r/trigger.result: Update test results to reflect automatic statement reprepare. mysql-test/t/disabled.def: Enable ps_ddl.test, which now passes. mysql-test/t/ps_1general.test: Since now we re-execute prepared statements after DDL successfully, change the test to produce repeatable results. Remove expectancy of an error in one place where now we automatically reprepare the prepared statement. mysql-test/t/query_cache_merge.test: Ensure the table definition cache is large enough for the test to pass in --ps-protocol mysql-test/t/trigger.test: Sinc sql/item.cc: Implement Item_param "copy" functionality, used at re-prepare of a prepared statement. We copy the type of the original parameter, and move the assigned value, if any. Sic, the value is "moved", since it can be quite big -- e.g. in case we deal with a LONG DATA parameter. It's essential to move the value from the old parameter since at the time of re-prepare the client packet with the necessary information may be not available. sql/item.h: Declare a new method used for reprepare. sql/my_decimal.h: Implement "swap()" functionality of class my_decimal to be able to easily swap two decimal values. sql/mysql_priv.h: Declare enum_metadata_type. sql/mysqld.cc: Implement a status variable for the number of reprepared statements. sql/sql_base.cc: Implement metadata version validation. sql/share/errmsg.txt: Add two new error messages: ER_NEED_REPREPARE and ER_PS_REBIND. The first error (theoretically) never reaches the user. It is issued by the metadata validation framework when a metadata version has changed between prepare and execute. Later on it's intercepted and the statement is automatically re-prepared. Only if the error has occurred repeatedly MAX_REPREPARE_ATTEMTS (3) times do we return it to the user. The second error is issued when after re-prepare we discover that the metadata we sent over to the client using the binary protocol differs drammatically from the new result set metadata that the reprepared statement produces (e.g. number of result set columns is different). sql/sql_class.cc: Implement metadata version validation framework. sql/sql_class.h: Declarations for metadata version validation framework. sql/sql_parse.cc: Mark commands for which we must invalidate and reprepare a prepared statement when metadata has changed. sql/sql_prepare.cc: Implement WL#4165 and WL#4166 (limited support of metadata validation and re-prepare). sql/table.h: Implement metadata validation. tests/mysql_client_test.c: Add a test case for WL#4166 --- mysql-test/t/query_cache_merge.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t/query_cache_merge.test') diff --git a/mysql-test/t/query_cache_merge.test b/mysql-test/t/query_cache_merge.test index 36b8662f088..5247d29a83c 100644 --- a/mysql-test/t/query_cache_merge.test +++ b/mysql-test/t/query_cache_merge.test @@ -25,6 +25,15 @@ while ($1) } --enable_warnings +# +# In order for the test to pass in --ps-protocol, we must +# set table_definition_cache size to at least 258 elements. +# Otherwise table versions are bound to change between +# prepare and execute, and we will get a constant validation +# error. See WL#4165 for details. +# +set @save_table_definition_cache= @@global.table_definition_cache; +set @@global.table_definition_cache=512; create table t00 (a int) engine=MERGE UNION=(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257) INSERT_METHOD=FIRST; enable_query_log; select count(*) from t00; @@ -36,5 +45,6 @@ show status like "Qcache_queries_in_cache"; drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257,t00; SET @@global.query_cache_size=0; +set @@global.table_definition_cache=@save_table_definition_cache; # End of 4.1 tests -- cgit v1.2.1 From 2100ec9ec91642e214cb043aac3a728a018d5b15 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Tue, 3 Jun 2008 10:59:46 -0300 Subject: Bug#33362: Query cache invalidation (truncate) may hang if cached query uses many tables The problem was that query cache would not properly cache queries which used 256 or more tables but yet would leave behind query cache blocks pointing to freed (destroyed) data. Later when invalidating (due to a truncate) query cache would attempt to grab a lock which resided in the freed data, leading to hangs or undefined behavior. This was happening due to a improper return value from the function responsible for registering the tables used in the query (so the cache can be invalidated later if one of the tables is modified). The function expected a return value of type boolean (char, 8 bits) indicating success (1) or failure (0) but the number of tables registered (unsigned int, 32 bits) was being returned instead. This caused the function to return failure for cases where it had actually succeed because when a type (unsigned int) is converted to a narrower type (char), the excess bits on the left are discarded. Thus if the 8 rightmost bits are zero, the return value will be 0 (failure). The solution is to simply return true (1) only if the number of registered table is greater than zero and false (0) otherwise. mysql-test/r/query_cache_merge.result: Add test case result for Bug#33362 mysql-test/t/query_cache_merge.test: Add test case for Bug#33362 sql/sql_cache.cc: Return 1 or 0 depending on the number of registered tables. --- mysql-test/t/query_cache_merge.test | 58 +++++++++++++++++++++++++++++++++++++ 1 file changed, 58 insertions(+) (limited to 'mysql-test/t/query_cache_merge.test') diff --git a/mysql-test/t/query_cache_merge.test b/mysql-test/t/query_cache_merge.test index 5247d29a83c..4e25bc3ed14 100644 --- a/mysql-test/t/query_cache_merge.test +++ b/mysql-test/t/query_cache_merge.test @@ -48,3 +48,61 @@ SET @@global.query_cache_size=0; set @@global.table_definition_cache=@save_table_definition_cache; # End of 4.1 tests + +# +# Bug#33362: Query cache invalidation (truncate) may hang if cached query uses many tables +# + +SET @save_table_definition_cache = @@global.table_definition_cache; +SET @@global.table_definition_cache = 512; + +let $c= 255; + +while ($c) +{ + eval CREATE TABLE t$c (a INT); + eval INSERT INTO t$c VALUES ($c); + dec $c; +} + +let $c= 254; +let $str= t255; + +while ($c) +{ + let $str= t$c,$str; + dec $c; +} + +eval CREATE TABLE t0 (a INT) ENGINE=MERGE UNION($str); +SET GLOBAL query_cache_size = 1048576; +FLUSH STATUS; +SELECT a FROM t0 WHERE a = 1; +SHOW STATUS LIKE "Qcache_queries_in_cache"; + +let $c= 255; +let $i= 1; + +while ($c) +{ + eval TRUNCATE TABLE t$c; + eval SELECT a FROM t$i; + dec $c; + inc $i; +} + +SELECT a FROM t0; +DROP TABLE t0; + +let $c= 255; + +while ($c) +{ + eval DROP TABLE t$c; + dec $c; +} + +SET @@global.query_cache_size = 0; +SET @@global.table_definition_cache = @save_table_definition_cache; + +--echo End of 5.1 tests -- cgit v1.2.1