1# Note that this test requires a fresh restart to not have problems with the 2# old status values 3 4set @save_use_stat_tables=@@use_stat_tables; 5 6set use_stat_tables='preferably'; 7 8--disable_warnings 9DROP DATABASE IF EXISTS dbt3_s001; 10--enable_warnings 11 12CREATE DATABASE dbt3_s001; 13 14use dbt3_s001; 15 16set @save_optimizer_switch=@@optimizer_switch; 17set optimizer_switch='extended_keys=off'; 18 19--disable_query_log 20--disable_result_log 21--disable_warnings 22--source include/dbt3_s001.inc 23truncate mysql.table_stats; 24truncate mysql.column_stats; 25truncate mysql.index_stats; 26ANALYZE TABLE 27customer, lineitem, nation, orders, part, partsupp, region, supplier; 28--enable_warnings 29--enable_result_log 30--enable_query_log 31 32select * from mysql.table_stats; 33select * from mysql.index_stats; 34 35 36# 37# Test for parallel memory allocation for statistical data 38# 39# assumes that start the code of memory allocation for stats data has this line: 40# 41# DEBUG_SYNC(thd, "statistics_mem_alloc_start1"); 42# DEBUG_SYNC(thd, "statistics_mem_alloc_start2"); 43# 44 45let $Q6= 46select round(sum(l_extendedprice*l_discount),4) as revenue 47from lineitem 48where l_shipdate >= date '1994-01-01' 49 and l_shipdate < date '1994-01-01' + interval '1' year 50 and l_discount between 0.06 - 0.01 and 0.06 + 0.01 51 and l_quantity < 24; 52 53flush table lineitem; 54set use_stat_tables='never'; 55eval $Q6; 56 57connect (con1, localhost, root,,); 58connect (con2, localhost, root,,); 59 60connection con1; 61set debug_sync='statistics_mem_alloc_start1 WAIT_FOR second_thread_started_too'; 62set debug_sync='statistics_mem_alloc_start2 SIGNAL first_thread_working'; 63use dbt3_s001; 64set use_stat_tables='preferably'; 65--send_eval $Q6 66 67connection con2; 68set debug_sync='statistics_mem_alloc_start1 SIGNAL second_thread_started_too'; 69set debug_sync='statistics_mem_alloc_start2 WAIT_FOR first_thread_working'; 70use dbt3_s001; 71set use_stat_tables='preferably'; 72--send_eval $Q6 73 74connection con1; 75--reap 76 77connection con2; 78--reap 79 80connection default; 81set use_stat_tables='preferably'; 82disconnect con1; 83disconnect con2; 84set debug_sync='RESET'; 85 86# 87# Test for parallel statistics collection 88# 89# assumes that start of stats collection code has this line: 90# 91# DEBUG_SYNC(thd, "statistics_collection_start1"); 92# DEBUG_SYNC(thd, "statistics_collection_start2"); 93# 94 95select * from mysql.index_stats where table_name='lineitem' order by index_name; 96delete from mysql.index_stats 97 where table_name='lineitem' and 98 index_name in ('i_l_shipdate', 'i_l_receiptdate'); 99select * from mysql.index_stats where table_name='lineitem' order by index_name; 100--disable_result_log 101--disable_warnings 102analyze table lineitem persistent for columns() indexes (i_l_shipdate); 103--enable_warnings 104--enable_result_log 105select * from mysql.index_stats where table_name='lineitem' order by index_name; 106delete from mysql.index_stats 107 where table_name='lineitem' and index_name= 'i_l_shipdate'; 108select * from mysql.index_stats where table_name='lineitem' order by index_name; 109 110connect (con1, localhost, root,,); 111connect (con2, localhost, root,,); 112 113connection con1; 114set debug_sync='statistics_collection_start1 WAIT_FOR second_thread_started_too'; 115set debug_sync='statistics_collection_start2 SIGNAL first_thread_working'; 116use dbt3_s001; 117set use_stat_tables='preferably'; 118--send analyze table lineitem persistent for columns() indexes (i_l_shipdate) 119 120connection con2; 121set debug_sync='statistics_collection_start1 SIGNAL second_thread_started_too'; 122set debug_sync='statistics_collection_start2 WAIT_FOR first_thread_working'; 123use dbt3_s001; 124set use_stat_tables='preferably'; 125--send analyze table lineitem persistent for columns() indexes (i_l_receiptdate) 126 127connection con1; 128--disable_result_log 129--disable_warnings 130--reap 131--enable_warnings 132--enable_result_log 133 134connection con2; 135--disable_result_log 136--disable_warnings 137--reap 138--enable_warnings 139--enable_result_log 140 141connection default; 142disconnect con1; 143disconnect con2; 144set debug_sync='RESET'; 145 146select * from mysql.index_stats where table_name='lineitem' order by index_name, prefix_arity; 147 148# 149# Test for parallel statistics collection and update (innodb) 150# 151 152select * from mysql.index_stats where table_name='lineitem' 153 order by index_name, prefix_arity; 154set debug_sync='RESET'; 155 156let $innodb_storage_engine= 0; 157if (`SELECT UPPER(@@default_storage_engine) = 'INNODB'`) 158{ 159 let $innodb_storage_engine= 1; 160} 161 162connect (con1, localhost, root,,); 163connect (con2, localhost, root,,); 164 165connection con1; 166set debug_sync='statistics_collection_start SIGNAL parked WAIT_FOR finish'; 167use dbt3_s001; 168set use_stat_tables='preferably'; 169--send analyze table lineitem persistent for all 170 171connection con2; 172set debug_sync='now WAIT_FOR parked'; 173use dbt3_s001; 174set use_stat_tables='never'; 175if ($innodb_storage_engine) 176{ 177 select * from lineitem where l_orderkey=1 and l_partkey=156; 178 delete from lineitem where l_orderkey=1 and l_partkey=156; 179 select * from lineitem where l_orderkey=1 and l_partkey=156; 180} 181set debug_sync='now SIGNAL finish'; 182 183connection con1; 184--disable_result_log 185--disable_warnings 186--reap 187--enable_warnings 188--enable_result_log 189 190connection default; 191disconnect con1; 192disconnect con2; 193set debug_sync='RESET'; 194 195select * from mysql.index_stats where table_name='lineitem' 196 order by index_name, prefix_arity; 197 198# 199# Bug mdev-3891: deadlock for ANALYZE and SELECT over mysql.index_stats 200# 201 202set @save_global_use_stat_tables=@@global.use_stat_tables; 203set global use_stat_tables='preferably'; 204set debug_sync='RESET'; 205 206connect (con1, localhost, root,,); 207connect (con2, localhost, root,,); 208 209connection con1; 210set debug_sync='statistics_update_start SIGNAL parker WAIT_FOR go1 EXECUTE 1'; 211set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go2 EXECUTE 3'; 212use dbt3_s001; 213--send analyze table lineitem persistent for all 214 215connection con2; 216set debug_sync='open_and_process_table WAIT_FOR parker'; 217set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2'; 218use dbt3_s001; 219--send select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity; 220 221connection con1; 222--disable_result_log 223--disable_warnings 224--reap 225--enable_warnings 226--enable_result_log 227 228connection con2; 229--disable_warnings 230--reap 231--enable_warnings 232 233connection default; 234disconnect con1; 235disconnect con2; 236set debug_sync='RESET'; 237 238set global use_stat_tables=@save_global_use_stat_tables; 239 240DROP DATABASE dbt3_s001; 241 242use test; 243 244# 245# Bug mdev-4019: crash when executing in parallel ANALYZE and 246# SELECT * FROM information_schema.statistics 247# 248 249set @save_global_use_stat_tables=@@global.use_stat_tables; 250set global use_stat_tables='preferably'; 251set debug_sync='RESET'; 252 253create table t1 (a int, b int, key(a)); 254insert t1 values (1,1),(2,2); 255 256analyze table t1; 257 258SET debug_sync='after_open_table_ignore_flush WAIT_FOR go'; 259send select * from information_schema.statistics where table_schema='test'; 260 261connect(con1, localhost, root); 262connection con1; 263select * from t1; 264SET DEBUG_SYNC= "now SIGNAL go"; 265 266connection default; 267reap; 268 269connection default; 270disconnect con1; 271set debug_sync='RESET'; 272 273drop table t1; 274set global use_stat_tables=@save_global_use_stat_tables; 275set use_stat_tables=@save_use_stat_tables; 276