From 9a29bd543e664e87a923e0e1529223cefc6607f9 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Thu, 25 Feb 2010 12:39:43 +0200 Subject: Bug #51089 SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS' backporting of bug@30703 to 5.1. The fixes are backed up with a regression test. mysql-test/include/test_fieldsize.inc: waiting to stop is to be actually exclusively for SQL thread. mysql-test/suite/rpl/r/rpl_show_slave_running.result: new results file is added. mysql-test/suite/rpl/t/rpl_show_slave_running.test: regression test for bug#30703 is added. sql/mysqld.cc: refining `show status like slave_running' handler to correspond to one of `show slave status'. sql/slave.cc: A dbug-sync point is added to complement the regression test. --- mysql-test/include/test_fieldsize.inc | 2 +- .../suite/rpl/r/rpl_show_slave_running.result | 42 ++++++++++++ mysql-test/suite/rpl/t/rpl_show_slave_running.test | 76 ++++++++++++++++++++++ sql/mysqld.cc | 3 +- sql/slave.cc | 1 + 5 files changed, 122 insertions(+), 2 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_show_slave_running.result create mode 100644 mysql-test/suite/rpl/t/rpl_show_slave_running.test diff --git a/mysql-test/include/test_fieldsize.inc b/mysql-test/include/test_fieldsize.inc index cbe63e26318..606bc63779d 100644 --- a/mysql-test/include/test_fieldsize.inc +++ b/mysql-test/include/test_fieldsize.inc @@ -22,7 +22,7 @@ eval $test_insert; connection slave; START SLAVE; -wait_for_slave_to_stop; +--source include/wait_for_slave_sql_to_stop.inc --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # --query_vertical SHOW SLAVE STATUS diff --git a/mysql-test/suite/rpl/r/rpl_show_slave_running.result b/mysql-test/suite/rpl/r/rpl_show_slave_running.result new file mode 100644 index 00000000000..d8450607185 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_show_slave_running.result @@ -0,0 +1,42 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +include/stop_slave.inc +SELECT GET_LOCK("debug_lock.before_get_running_status_yes", 1000); +GET_LOCK("debug_lock.before_get_running_status_yes", 1000) +1 +set global debug= 'd,debug_lock.before_get_running_status_yes'; +Slave_running, Slave_IO_Running, Slave_SQL_Running, must be OFF, NO, NO in three following queries +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running OFF +Slave_IO_Running= No +Slave_IO_Running= No +start slave io_thread; +Slave_running, Slave_IO_Running, Slave_SQL_Running must be OFF NO NO in three following queries +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running OFF +Slave_IO_Running= No +Slave_IO_Running= No +SELECT RELEASE_LOCK("debug_lock.before_get_running_status_yes"); +RELEASE_LOCK("debug_lock.before_get_running_status_yes") +1 +Slave_running, Slave_IO_Running, Slave_SQL_Running must be OFF YES NO in three following queries +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running OFF +Slave_IO_Running= Yes +Slave_IO_Running= No +start slave sql_thread; +Slave_running, Slave_IO_Running, Slave_SQL_Running must be ON, YES, YES in three following queries +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running ON +Slave_IO_Running= Yes +Slave_IO_Running= Yes +set global debug= ''; +End of tests diff --git a/mysql-test/suite/rpl/t/rpl_show_slave_running.test b/mysql-test/suite/rpl/t/rpl_show_slave_running.test new file mode 100644 index 00000000000..9dc93ed7056 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_show_slave_running.test @@ -0,0 +1,76 @@ +# +# Bug #30703 SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS' +# The test verifies that SHOW STATUS LIKE 'Slave_running' displays ON +# if and only if `SHOW SLAVE STATUS' displays YES for Slave_IO_Running and Slave_SQL_Running +# +source include/master-slave.inc; +source include/have_debug.inc; + +connection slave; + +source include/stop_slave.inc; +let $debug_saved= `select @@global.debug`; +let $debug_lock= "debug_lock.before_get_running_status_yes"; +eval SELECT GET_LOCK($debug_lock, 1000); +set global debug= 'd,debug_lock.before_get_running_status_yes'; + +# Test 1. Slave is stopped + +--echo Slave_running, Slave_IO_Running, Slave_SQL_Running, must be OFF, NO, NO in three following queries + +SHOW STATUS LIKE 'Slave_running'; +let $status= query_get_value("show slave status", Slave_IO_Running, 1); +echo Slave_IO_Running= $status; +let $status= query_get_value("show slave status", Slave_SQL_Running, 1); +echo Slave_IO_Running= $status; + +# Test 2. The slave IO thread is started but not yet got connected to master +# and SQL thread is not started + +start slave io_thread; + +--echo Slave_running, Slave_IO_Running, Slave_SQL_Running must be OFF NO NO in three following queries + +SHOW STATUS LIKE 'Slave_running'; +let $status= query_get_value("show slave status", Slave_IO_Running, 1); +echo Slave_IO_Running= $status; +let $status= query_get_value("show slave status", Slave_SQL_Running, 1); +echo Slave_IO_Running= $status; + +# Test 3. The slave IO thread is started and got connected to master +# and SQL thread is still not started + +eval SELECT RELEASE_LOCK($debug_lock); +let $slave_param= Slave_IO_Running; +let $slave_param_value= YES; +source include/wait_for_slave_param.inc; + +--echo Slave_running, Slave_IO_Running, Slave_SQL_Running must be OFF YES NO in three following queries + +SHOW STATUS LIKE 'Slave_running'; +let $status= query_get_value("show slave status", Slave_IO_Running, 1); +echo Slave_IO_Running= $status; +let $status= query_get_value("show slave status", Slave_SQL_Running, 1); +echo Slave_IO_Running= $status; + +# Test 4. The slave IO thread is started and got connected to master +# and SQL thread is started + +start slave sql_thread; +source include/wait_for_slave_sql_to_start.inc; + +--echo Slave_running, Slave_IO_Running, Slave_SQL_Running must be ON, YES, YES in three following queries + +SHOW STATUS LIKE 'Slave_running'; +let $status= query_get_value("show slave status", Slave_IO_Running, 1); +echo Slave_IO_Running= $status; +let $status= query_get_value("show slave status", Slave_SQL_Running, 1); +echo Slave_IO_Running= $status; + +# cleanup + +connection slave; + +eval set global debug= '$debug_saved'; + +--echo End of tests diff --git a/sql/mysqld.cc b/sql/mysqld.cc index a483b9e2381..c4f459d9581 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -7162,7 +7162,8 @@ static int show_slave_running(THD *thd, SHOW_VAR *var, char *buff) var->type= SHOW_MY_BOOL; pthread_mutex_lock(&LOCK_active_mi); var->value= buff; - *((my_bool *)buff)= (my_bool) (active_mi && active_mi->slave_running && + *((my_bool *)buff)= (my_bool) (active_mi && + active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT && active_mi->rli.slave_running); pthread_mutex_unlock(&LOCK_active_mi); return 0; diff --git a/sql/slave.cc b/sql/slave.cc index 271b3635cf1..a89ac2e682b 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -2557,6 +2557,7 @@ pthread_handler_t handle_slave_io(void *arg) connected: + DBUG_SYNC_POINT("debug_lock.before_get_running_status_yes", 10); // TODO: the assignment below should be under mutex (5.0) mi->slave_running= MYSQL_SLAVE_RUN_CONNECT; thd->slave_net = &mysql->net; -- cgit v1.2.1 From ac9619fe05eac331948e9a14214f52c6b631b866 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Fri, 26 Feb 2010 14:07:47 +0200 Subject: bug#51089 correcting c&p typos in the test --- mysql-test/suite/rpl/t/rpl_show_slave_running.test | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/t/rpl_show_slave_running.test b/mysql-test/suite/rpl/t/rpl_show_slave_running.test index 9dc93ed7056..62cc311e6b2 100644 --- a/mysql-test/suite/rpl/t/rpl_show_slave_running.test +++ b/mysql-test/suite/rpl/t/rpl_show_slave_running.test @@ -22,7 +22,7 @@ SHOW STATUS LIKE 'Slave_running'; let $status= query_get_value("show slave status", Slave_IO_Running, 1); echo Slave_IO_Running= $status; let $status= query_get_value("show slave status", Slave_SQL_Running, 1); -echo Slave_IO_Running= $status; +echo Slave_SQL_Running= $status; # Test 2. The slave IO thread is started but not yet got connected to master # and SQL thread is not started @@ -35,7 +35,7 @@ SHOW STATUS LIKE 'Slave_running'; let $status= query_get_value("show slave status", Slave_IO_Running, 1); echo Slave_IO_Running= $status; let $status= query_get_value("show slave status", Slave_SQL_Running, 1); -echo Slave_IO_Running= $status; +echo Slave_SQL_Running= $status; # Test 3. The slave IO thread is started and got connected to master # and SQL thread is still not started @@ -51,7 +51,7 @@ SHOW STATUS LIKE 'Slave_running'; let $status= query_get_value("show slave status", Slave_IO_Running, 1); echo Slave_IO_Running= $status; let $status= query_get_value("show slave status", Slave_SQL_Running, 1); -echo Slave_IO_Running= $status; +echo Slave_SQL_Running= $status; # Test 4. The slave IO thread is started and got connected to master # and SQL thread is started @@ -65,7 +65,7 @@ SHOW STATUS LIKE 'Slave_running'; let $status= query_get_value("show slave status", Slave_IO_Running, 1); echo Slave_IO_Running= $status; let $status= query_get_value("show slave status", Slave_SQL_Running, 1); -echo Slave_IO_Running= $status; +echo Slave_SQL_Running= $status; # cleanup -- cgit v1.2.1 From 991c69e991e1f8068c0681be42c44c1f87440ca0 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Fri, 26 Feb 2010 14:09:26 +0200 Subject: bug#51089 correcting c&p typos in the results --- mysql-test/suite/rpl/r/rpl_show_slave_running.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_show_slave_running.result b/mysql-test/suite/rpl/r/rpl_show_slave_running.result index d8450607185..da26190e76d 100644 --- a/mysql-test/suite/rpl/r/rpl_show_slave_running.result +++ b/mysql-test/suite/rpl/r/rpl_show_slave_running.result @@ -14,14 +14,14 @@ SHOW STATUS LIKE 'Slave_running'; Variable_name Value Slave_running OFF Slave_IO_Running= No -Slave_IO_Running= No +Slave_SQL_Running= No start slave io_thread; Slave_running, Slave_IO_Running, Slave_SQL_Running must be OFF NO NO in three following queries SHOW STATUS LIKE 'Slave_running'; Variable_name Value Slave_running OFF Slave_IO_Running= No -Slave_IO_Running= No +Slave_SQL_Running= No SELECT RELEASE_LOCK("debug_lock.before_get_running_status_yes"); RELEASE_LOCK("debug_lock.before_get_running_status_yes") 1 @@ -30,13 +30,13 @@ SHOW STATUS LIKE 'Slave_running'; Variable_name Value Slave_running OFF Slave_IO_Running= Yes -Slave_IO_Running= No +Slave_SQL_Running= No start slave sql_thread; Slave_running, Slave_IO_Running, Slave_SQL_Running must be ON, YES, YES in three following queries SHOW STATUS LIKE 'Slave_running'; Variable_name Value Slave_running ON Slave_IO_Running= Yes -Slave_IO_Running= Yes +Slave_SQL_Running= Yes set global debug= ''; End of tests -- cgit v1.2.1 From 5693e220d5335ec7f5e3cdf756730bc949dc6604 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Fri, 26 Feb 2010 14:18:13 +0200 Subject: Bug #45576 rpl_row_create_table fails on PB2 The test failed due to Bug #29790. However, logics of the failing part does not need I_S selecting. Fixing to remove the non-deterministic I_S selecting as redundant from a part of the test dealing with BUG@22864. mysql-test/suite/rpl/r/rpl_row_create_table.result: results updated. mysql-test/suite/rpl/t/disabled.def: rpl_row_create_table is re-enabled. mysql-test/suite/rpl/t/rpl_row_create_table.test: removed a non-deterministic I_S selecting as redundant from a part of the test dealing with BUG@22864. --- mysql-test/suite/rpl/r/rpl_row_create_table.result | 28 +++++----------------- mysql-test/suite/rpl/t/disabled.def | 2 -- mysql-test/suite/rpl/t/rpl_row_create_table.test | 18 +++----------- 3 files changed, 9 insertions(+), 39 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index 4c2b9ebeaee..8a1b7a805aa 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -241,10 +241,12 @@ STOP SLAVE; SET GLOBAL storage_engine=@storage_engine; START SLAVE; ================ BUG#22864 ================ -STOP SLAVE; -RESET SLAVE; -RESET MASTER; -START SLAVE; +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; SET AUTOCOMMIT=0; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); @@ -264,15 +266,6 @@ t1 t2 t3 t4 -SELECT TABLE_NAME,ENGINE -FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_NAME LIKE 't_' -ORDER BY TABLE_NAME; -TABLE_NAME ENGINE -t1 MyISAM -t2 InnoDB -t3 InnoDB -t4 InnoDB SELECT * FROM t1 ORDER BY a; a 1 @@ -334,15 +327,6 @@ t1 t2 t3 t4 -SELECT TABLE_NAME,ENGINE -FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_NAME LIKE 't_' -ORDER BY TABLE_NAME; -TABLE_NAME ENGINE -t1 MyISAM -t2 InnoDB -t3 InnoDB -t4 InnoDB SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index d2cb1ce9518..888298bbb09 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -9,5 +9,3 @@ # Do not use any TAB characters for whitespace. # ############################################################################## - -rpl_row_create_table : Bug#45576 2009-12-01 joro rpl_row_create_table fails on PB2 diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test index 54dcbd24419..e30982da605 100644 --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test @@ -136,13 +136,9 @@ START SLAVE; # BUG#22864 (Rollback following CREATE ... SELECT discards 'CREATE # table' from log): --echo ================ BUG#22864 ================ -connection slave; -STOP SLAVE; -RESET SLAVE; -connection master; -RESET MASTER; -connection slave; -START SLAVE; + +--source include/master-slave-reset.inc + connection master; SET AUTOCOMMIT=0; CREATE TABLE t1 (a INT); @@ -160,10 +156,6 @@ INSERT INTO t1 VALUES (4),(5),(6); ROLLBACK; SHOW TABLES; -SELECT TABLE_NAME,ENGINE - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_NAME LIKE 't_' -ORDER BY TABLE_NAME; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; @@ -173,10 +165,6 @@ SELECT * FROM t4 ORDER BY a; SHOW BINLOG EVENTS FROM 106; sync_slave_with_master; SHOW TABLES; -SELECT TABLE_NAME,ENGINE - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_NAME LIKE 't_' -ORDER BY TABLE_NAME; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; -- cgit v1.2.1 From b0a1ae09ae12bb3eefda03ff91263a3c860fd49b Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 26 Feb 2010 09:22:48 -0300 Subject: Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL The problem was that the CSV storage engine does not support NULL fields, yet in some early 5.1 version the log tables (general_log and slow_log) were created with null fields. On top of this, when altering a CSV table column, all fields of the table must be NOT NULL otherwise the alteration fails. The solution is to ensure that during upgrade all columns of the log tables are NOT NULL. mysql-test/r/log_tables_upgrade.result: Add test case result for Bug#49823. mysql-test/std_data/bug49823.CSV: Sample data for test. mysql-test/std_data/bug49823.frm: Add a CSV table which mimics the general_log table, except that it contains a nullable column. mysql-test/t/log_tables_upgrade.test: Add test case for Bug#49823. scripts/mysql_system_tables_fix.sql: Ensure that all columns of the log tables are NOT NULL. --- mysql-test/r/log_tables_upgrade.result | 47 +++++++++++++++++++++++++++++++++ mysql-test/std_data/bug49823.CSM | Bin 0 -> 35 bytes mysql-test/std_data/bug49823.CSV | 1 + mysql-test/std_data/bug49823.frm | Bin 0 -> 8776 bytes mysql-test/t/log_tables_upgrade.test | 32 ++++++++++++++++++++++ scripts/mysql_system_tables_fix.sql | 21 +++++++++++++-- 6 files changed, 99 insertions(+), 2 deletions(-) create mode 100644 mysql-test/r/log_tables_upgrade.result create mode 100644 mysql-test/std_data/bug49823.CSM create mode 100644 mysql-test/std_data/bug49823.CSV create mode 100644 mysql-test/std_data/bug49823.frm create mode 100644 mysql-test/t/log_tables_upgrade.test diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result new file mode 100644 index 00000000000..5d9be85a48a --- /dev/null +++ b/mysql-test/r/log_tables_upgrade.result @@ -0,0 +1,47 @@ +# +# Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL +# +USE test; +SET @saved_general_log = @@GLOBAL.general_log; +SET GLOBAL general_log = OFF; +USE mysql; +FLUSH TABLES; +REPAIR TABLE test.bug49823; +Table Op Msg_type Msg_text +test.bug49823 repair status OK +RENAME TABLE general_log TO renamed_general_log; +RENAME TABLE test.bug49823 TO general_log; +mtr.global_suppressions OK +mtr.test_suppressions OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.general_log +Error : You can't use locks with log tables. +status : OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.ndb_binlog_index OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.renamed_general_log OK +mysql.servers OK +mysql.slow_log +Error : You can't use locks with log tables. +status : OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +DROP TABLE general_log; +RENAME TABLE renamed_general_log TO general_log; +SET GLOBAL general_log = @saved_general_log; +USE test; diff --git a/mysql-test/std_data/bug49823.CSM b/mysql-test/std_data/bug49823.CSM new file mode 100644 index 00000000000..ee345049d99 Binary files /dev/null and b/mysql-test/std_data/bug49823.CSM differ diff --git a/mysql-test/std_data/bug49823.CSV b/mysql-test/std_data/bug49823.CSV new file mode 100644 index 00000000000..ffc319e2446 --- /dev/null +++ b/mysql-test/std_data/bug49823.CSV @@ -0,0 +1 @@ +"2010-02-26 13:32:26","root[root] @ localhost []",2,1,"Query","SELECT 1" diff --git a/mysql-test/std_data/bug49823.frm b/mysql-test/std_data/bug49823.frm new file mode 100644 index 00000000000..527807da1c9 Binary files /dev/null and b/mysql-test/std_data/bug49823.frm differ diff --git a/mysql-test/t/log_tables_upgrade.test b/mysql-test/t/log_tables_upgrade.test new file mode 100644 index 00000000000..5d1b2b5aed6 --- /dev/null +++ b/mysql-test/t/log_tables_upgrade.test @@ -0,0 +1,32 @@ +--source include/not_embedded.inc +--source include/have_csv.inc + +# Only run test if "mysql_upgrade" is found +--require r/have_mysql_upgrade.result +--disable_query_log +select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; +--enable_query_log + +--echo # +--echo # Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL +--echo # + +USE test; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +copy_file std_data/bug49823.frm $MYSQLD_DATADIR/test/bug49823.frm; +copy_file std_data/bug49823.CSM $MYSQLD_DATADIR/test/bug49823.CSM; +copy_file std_data/bug49823.CSV $MYSQLD_DATADIR/test/bug49823.CSV; + +SET @saved_general_log = @@GLOBAL.general_log; +SET GLOBAL general_log = OFF; +USE mysql; +FLUSH TABLES; +REPAIR TABLE test.bug49823; +RENAME TABLE general_log TO renamed_general_log; +RENAME TABLE test.bug49823 TO general_log; +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +DROP TABLE general_log; +RENAME TABLE renamed_general_log TO general_log; +SET GLOBAL general_log = @saved_general_log; +USE test; diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 4260aee9142..deeb4d4de82 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -221,12 +221,29 @@ ALTER TABLE func SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; -ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL; +ALTER TABLE general_log + MODIFY event_time TIMESTAMP NOT NULL, + MODIFY user_host MEDIUMTEXT NOT NULL, + MODIFY thread_id INTEGER NOT NULL, + MODIFY server_id INTEGER UNSIGNED NOT NULL, + MODIFY command_type VARCHAR(64) NOT NULL, + MODIFY argument MEDIUMTEXT NOT NULL; SET GLOBAL general_log = @old_log_state; SET @old_log_state = @@global.slow_query_log; SET GLOBAL slow_query_log = 'OFF'; -ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL; +ALTER TABLE slow_log + MODIFY start_time TIMESTAMP NOT NULL, + MODIFY user_host MEDIUMTEXT NOT NULL, + MODIFY query_time TIME NOT NULL, + MODIFY lock_time TIME NOT NULL, + MODIFY rows_sent INTEGER NOT NULL, + MODIFY rows_examined INTEGER NOT NULL, + MODIFY db VARCHAR(512) NOT NULL, + MODIFY last_insert_id INTEGER NOT NULL, + MODIFY insert_id INTEGER NOT NULL, + MODIFY server_id INTEGER UNSIGNED NOT NULL, + MODIFY sql_text MEDIUMTEXT NOT NULL; SET GLOBAL slow_query_log = @old_log_state; # -- cgit v1.2.1 From ca6691533a7e2a454bbb614583a0058bf7acd2d2 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Fri, 26 Feb 2010 17:40:01 +0400 Subject: Bug#47669 Query showed by EXPLAIN EXTENDED gives different result from original query Item_field::print method does not take into account fields whose values may be null. The fix is to print 'NULL' if field value is null. mysql-test/r/explain.result: test case mysql-test/r/func_str.result: result fix mysql-test/r/having.result: result fix mysql-test/r/select.result: result fix mysql-test/r/subselect.result: result fix mysql-test/r/union.result: result fix mysql-test/t/explain.test: test case sql/item.cc: print 'NULL' if field value is null. --- mysql-test/r/explain.result | 15 +++++++++++++++ mysql-test/r/func_str.result | 4 ++-- mysql-test/r/having.result | 2 +- mysql-test/r/select.result | 10 +++++----- mysql-test/r/subselect.result | 8 ++++---- mysql-test/r/union.result | 2 +- mysql-test/t/explain.test | 12 ++++++++++++ sql/item.cc | 11 ++++++++--- 8 files changed, 48 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 5a1bf1a1290..2bf85e35139 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -210,4 +210,19 @@ Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP colum Note 1003 select 1 AS `1` from `test`.`t1` where ((...)) SET SESSION sql_mode=@old_sql_mode; DROP TABLE t1; +# +# Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query +# +CREATE TABLE t1 (c int); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (d int); +INSERT INTO t2 VALUES (NULL), (0); +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +DROP TABLE t1, t2; End of 5.1 tests. diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index d144e84dfdc..8cf46e5534e 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1307,12 +1307,12 @@ explain extended select encode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select encode('','zxcv') AS `enc` from `test`.`t1` +Note 1003 select encode(NULL,'zxcv') AS `enc` from `test`.`t1` explain extended select decode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select decode('','zxcv') AS `enc` from `test`.`t1` +Note 1003 select decode(NULL,'zxcv') AS `enc` from `test`.`t1` drop table t1; create table t1 (a bigint not null)engine=myisam; insert into t1 set a = 1024*1024*1024*4; diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 68ba34e353c..9568ef88786 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -12,7 +12,7 @@ explain extended select count(a) as b from t1 where a=0 having b >=0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select count('0') AS `b` from `test`.`t1` where 0 having (`b` >= 0) +Note 1003 select count(NULL) AS `b` from `test`.`t1` where 0 having (`b` >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index bd7968583dc..fb4175ed5ca 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4050,10 +4050,10 @@ join_0.c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +Note 1003 select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL SHOW WARNINGS; Level Code Message -Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +Note 1003 select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL DROP TABLE t1; SELECT 1 AS ` `; @@ -4648,17 +4648,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 DROP TABLE t1; # # Bug#45266: Uninitialized variable lead to an empty result. diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 2dc26bfb048..bda33109524 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1496,7 +1496,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select max('0') from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) select * from t3 where a >= some (select b from t2); a explain extended select * from t3 where a >= some (select b from t2); @@ -1504,7 +1504,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) select * from t3 where a >= all (select b from t2 group by 1); a 6 @@ -1515,7 +1515,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select '0' AS `b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select NULL AS `b` from `test`.`t2` group by 1))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1523,7 +1523,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select '0' AS `b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select NULL AS `b` from `test`.`t2` group by 1))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 43d7cade227..98d177a5448 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1585,7 +1585,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNION t1 system NULL NULL NULL NULL 0 0.00 const row not found NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: -Note 1003 select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a` +Note 1003 select NULL AS `a` from `test`.`t1` union select NULL AS `a` from `test`.`t1` order by `a` DROP TABLE t1; End of 5.0 tests # diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 77b49a8b1a5..2af0ffc0cbd 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -187,4 +187,16 @@ SET SESSION sql_mode=@old_sql_mode; DROP TABLE t1; +--echo # +--echo # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query +--echo # + +CREATE TABLE t1 (c int); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (d int); +INSERT INTO t2 VALUES (NULL), (0); +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +DROP TABLE t1, t2; + + --echo End of 5.1 tests. diff --git a/sql/item.cc b/sql/item.cc index 934e897f923..04496338b8f 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5690,9 +5690,14 @@ void Item_field::print(String *str, enum_query_type query_type) char buff[MAX_FIELD_WIDTH]; String tmp(buff,sizeof(buff),str->charset()); field->val_str(&tmp); - str->append('\''); - str->append(tmp); - str->append('\''); + if (field->is_null()) + str->append("NULL"); + else + { + str->append('\''); + str->append(tmp); + str->append('\''); + } return; } Item_ident::print(str, query_type); -- cgit v1.2.1 From 600a28368916ae07c0694ae090dcba7f2c52b12a Mon Sep 17 00:00:00 2001 From: Staale Smedseng Date: Fri, 26 Feb 2010 15:30:14 +0100 Subject: Bug #45058 init_available_charsets uses double checked locking A client doing multiple mysql_library_init() and mysql_library_end() calls over the lifetime of the process may experience lost character set data, potentially even a SIGSEGV. This patch reinstates the reloading of character set data when a mysql_library_init() is done after a mysql_library_end(). --- include/my_sys.h | 2 +- libmysql/libmysql.c | 1 + mysys/charset.c | 5 +++++ mysys/my_init.c | 1 + sql/mysqld.cc | 1 + 5 files changed, 9 insertions(+), 1 deletion(-) diff --git a/include/my_sys.h b/include/my_sys.h index a4ff5c30de7..59b44307b6f 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -950,7 +950,7 @@ extern my_bool resolve_charset(const char *cs_name, extern my_bool resolve_collation(const char *cl_name, CHARSET_INFO *default_cl, CHARSET_INFO **cl); - +extern void free_charsets(void); extern char *get_charsets_dir(char *buf); extern my_bool my_charset_same(CHARSET_INFO *cs1, CHARSET_INFO *cs2); extern my_bool init_compiled_charsets(myf flags); diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c index f626361536f..6ea777a0702 100644 --- a/libmysql/libmysql.c +++ b/libmysql/libmysql.c @@ -211,6 +211,7 @@ void STDCALL mysql_server_end() } else { + free_charsets(); mysql_thread_end(); } diff --git a/mysys/charset.c b/mysys/charset.c index b1b91d716ba..584b33b2d6a 100644 --- a/mysys/charset.c +++ b/mysys/charset.c @@ -427,6 +427,11 @@ static void init_available_charsets(void) } +void free_charsets(void) +{ + charsets_initialized= MY_PTHREAD_ONCE_INIT; +} + uint get_collation_number(const char *name) { my_pthread_once(&charsets_initialized, init_available_charsets); diff --git a/mysys/my_init.c b/mysys/my_init.c index 453c72f999f..a60927be693 100644 --- a/mysys/my_init.c +++ b/mysys/my_init.c @@ -165,6 +165,7 @@ void my_end(int infoflag) my_print_open_files(); } } + free_charsets(); my_error_unregister_all(); my_once_free(); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index a483b9e2381..122fadf9847 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -1287,6 +1287,7 @@ void clean_up(bool print_message) lex_free(); /* Free some memory */ item_create_cleanup(); set_var_free(); + free_charsets(); if (!opt_noacl) { #ifdef HAVE_DLOPEN -- cgit v1.2.1 From a3f59677f886a45e0d0b1b5cf0225966ecf6ee15 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 26 Feb 2010 23:09:36 -0300 Subject: Workaround the pthread_once_t static initialization. Per the POSIX standard, reinitialization of a pthread_once is a gray area, but it is needed to support subsequent initializations of the client library. mysys/charset.c: Reinitialize a pthread_once_t variable. --- mysys/charset.c | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/mysys/charset.c b/mysys/charset.c index 584b33b2d6a..9678febc9ba 100644 --- a/mysys/charset.c +++ b/mysys/charset.c @@ -400,6 +400,7 @@ static void *cs_alloc(size_t size) static my_pthread_once_t charsets_initialized= MY_PTHREAD_ONCE_INIT; +static my_pthread_once_t charsets_template= MY_PTHREAD_ONCE_INIT; static void init_available_charsets(void) { @@ -429,7 +430,7 @@ static void init_available_charsets(void) void free_charsets(void) { - charsets_initialized= MY_PTHREAD_ONCE_INIT; + charsets_initialized= charsets_template; } uint get_collation_number(const char *name) -- cgit v1.2.1 From 0ecc8963a089d64afd97971a6c4631aefc4157e1 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 26 Feb 2010 23:19:59 -0300 Subject: Force execution now that another test case might run mysql_upgrade first. mysql-test/t/mysql_upgrade.test: For execution, mostly testing the presence of the binary anyway. --- mysql-test/t/mysql_upgrade.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 24a1d2e1b5d..6c01f3b2027 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -32,7 +32,7 @@ if (`SELECT $VALGRIND_TEST`) # expected binaries it uses. # --echo Run mysql_upgrade once ---exec $MYSQL_UPGRADE --skip-verbose 2>&1 +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 # It should have created a file in the MySQL Servers datadir let $MYSQLD_DATADIR= `select @@datadir`; -- cgit v1.2.1 From 723e67b92548221d887f7e21b4d654da45eb0d1a Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Sat, 27 Feb 2010 17:08:53 +0200 Subject: Bug #51574 rpl_row_create_table fails in ps-protocol at CREATE TABLE IF NOT EXISTS bug48506 disabling the test to not run on PB --- mysql-test/suite/rpl/t/disabled.def | 2 ++ 1 file changed, 2 insertions(+) diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index 888298bbb09..ef77c54e067 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -9,3 +9,5 @@ # Do not use any TAB characters for whitespace. # ############################################################################## + +rpl_row_create_table : Bug#51574 Feb 27 2010 andrei failed different way than earlier with bug#45576 -- cgit v1.2.1 From 9715539ebd9519ca66e9b803f435878a819d3f99 Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Sun, 28 Feb 2010 21:29:19 +0400 Subject: Fix for bug#51304: checksum table gives different results for same data when using bit fields Problem: checksum for BIT fields may be computed incorrectly in some cases due to its storage peculiarity. Fix: convert a BIT field to a string then calculate its checksum. mysql-test/r/myisam.result: Fix for bug#51304: checksum table gives different results for same data when using bit fields - test result. mysql-test/t/myisam.test: Fix for bug#51304: checksum table gives different results for same data when using bit fields - test case. sql/sql_table.cc: Fix for bug#51304: checksum table gives different results for same data when using bit fields - convert BIT fields to strings calculating its checksums as some bits may be saved among NULL bits in the record buffer. --- mysql-test/r/myisam.result | 17 +++++++++++++++++ mysql-test/t/myisam.test | 17 ++++++++++++++++- sql/sql_table.cc | 36 +++++++++++++++++++++--------------- 3 files changed, 54 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 82655dd46ec..b08435cb1a7 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2339,4 +2339,21 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +# +# Bug#51304: checksum table gives different results +# for same data when using bit fields +# +CREATE TABLE t1(a INT, b BIT(1)); +INSERT INTO t1 VALUES(1, 0), (2, 1); +CREATE TABLE t2 SELECT * FROM t1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 3775188275 +CHECKSUM TABLE t2 EXTENDED; +Table Checksum +test.t2 3775188275 +CHECKSUM TABLE t3 EXTENDED; +Table Checksum +test.t3 3775188275 +DROP TABLE t1, t2, t3; End of 5.1 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index d12dbce1cc1..ea7ddc88b77 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1587,5 +1587,20 @@ REPLACE INTO t1 VALUES CHECK TABLE t1; DROP TABLE t1; ---echo End of 5.1 tests +--echo # +--echo # Bug#51304: checksum table gives different results +--echo # for same data when using bit fields +--echo # +CREATE TABLE t1(a INT, b BIT(1)); +INSERT INTO t1 VALUES(1, 0), (2, 1); +CREATE TABLE t2 SELECT * FROM t1; +--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t3.frm +--copy_file $MYSQLD_DATADIR/test/t1.MYD $MYSQLD_DATADIR/test/t3.MYD +--copy_file $MYSQLD_DATADIR/test/t1.MYI $MYSQLD_DATADIR/test/t3.MYI +CHECKSUM TABLE t1 EXTENDED; +CHECKSUM TABLE t2 EXTENDED; +CHECKSUM TABLE t3 EXTENDED; +DROP TABLE t1, t2, t3; + +--echo End of 5.1 tests diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 871b2f2d552..eb88b1e70a5 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7934,22 +7934,28 @@ bool mysql_checksum_table(THD *thd, TABLE_LIST *tables, for (uint i= 0; i < t->s->fields; i++ ) { Field *f= t->field[i]; - enum_field_types field_type= f->type(); - /* - BLOB and VARCHAR have pointers in their field, we must convert - to string; GEOMETRY is implemented on top of BLOB. - */ - if ((field_type == MYSQL_TYPE_BLOB) || - (field_type == MYSQL_TYPE_VARCHAR) || - (field_type == MYSQL_TYPE_GEOMETRY)) - { - String tmp; - f->val_str(&tmp); - row_crc= my_checksum(row_crc, (uchar*) tmp.ptr(), tmp.length()); + + /* + BLOB and VARCHAR have pointers in their field, we must convert + to string; GEOMETRY is implemented on top of BLOB. + BIT may store its data among NULL bits, convert as well. + */ + switch (f->type()) { + case MYSQL_TYPE_BLOB: + case MYSQL_TYPE_VARCHAR: + case MYSQL_TYPE_GEOMETRY: + case MYSQL_TYPE_BIT: + { + String tmp; + f->val_str(&tmp); + row_crc= my_checksum(row_crc, (uchar*) tmp.ptr(), + tmp.length()); + break; + } + default: + row_crc= my_checksum(row_crc, f->ptr, f->pack_length()); + break; } - else - row_crc= my_checksum(row_crc, f->ptr, - f->pack_length()); } crc+= row_crc; -- cgit v1.2.1 From 85e47d03cdeea7a24d66f9c5380108aba9261102 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Mon, 1 Mar 2010 10:43:24 +0200 Subject: tree name change --- .bzr-mysql/default.conf | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/.bzr-mysql/default.conf b/.bzr-mysql/default.conf index f044f8e62da..e613cefc614 100644 --- a/.bzr-mysql/default.conf +++ b/.bzr-mysql/default.conf @@ -1,4 +1,4 @@ [MYSQL] post_commit_to = "commits@lists.mysql.com" post_push_to = "commits@lists.mysql.com" -tree_name = "mysql-5.1" +tree_name = "mysql-5.1-bugteam" -- cgit v1.2.1 From dbb9e58c136919f1f67d828537cdc6bd95faadfe Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Mon, 1 Mar 2010 14:33:15 +0200 Subject: Bug #51600 rpl_slave_skip waits for slave to stop incorrectly START SLAVE UNTIL MASTER ... specifies only SQL thread to stop. rpl_slave_skip erronously deployed waiting for stop of both threads. Corrected with deploying the correct macro. Notice, earlier a similar bug@47749 was fixed in mysql-trunk. mysql-test/suite/rpl/t/rpl_slave_skip.test: changing two waiting calls for offline of both threads into waiting for SQL to stop. --- mysql-test/suite/rpl/t/rpl_slave_skip.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rpl/t/rpl_slave_skip.test b/mysql-test/suite/rpl/t/rpl_slave_skip.test index f4cb0f69e93..c5ee6793277 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_skip.test +++ b/mysql-test/suite/rpl/t/rpl_slave_skip.test @@ -27,7 +27,7 @@ connection slave; # Stop when reaching the the first table map event. START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762; -wait_for_slave_to_stop; +source include/wait_for_slave_sql_to_stop.inc; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 # query_vertical SHOW SLAVE STATUS; @@ -59,7 +59,7 @@ source include/show_binlog_events.inc; connection slave; START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=106; -wait_for_slave_to_stop; +source include/wait_for_slave_sql_to_stop.inc; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; sync_with_master; -- cgit v1.2.1 From d6678deab8ec0628aa57522705adccaedea5f8cd Mon Sep 17 00:00:00 2001 From: Staale Smedseng Date: Mon, 1 Mar 2010 14:49:51 +0100 Subject: Bug#49417 some complaints about mysqld --help --verbose output This patch fixes some typos and poorly formulated sentences in the output from mysqld --help --verbose. Some of the problems described in the bug report are already handled by the patch for Bug#49447, and are therefore not included in this patch. --- sql/mysqld.cc | 26 ++++++++++++++++++++------ 1 file changed, 20 insertions(+), 6 deletions(-) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5638b074e4a..f658a7c8c3c 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -6077,8 +6077,8 @@ each time the SQL thread starts.", TC_LOG_PAGE_SIZE, 0}, #endif {"log-update", OPT_UPDATE_LOG, - "The update log is deprecated since version 5.0, is replaced by the binary \ -log and this option justs turns on --log-bin instead.", + "The update log is deprecated since version 5.0, is replaced by the binary " + "log and this option just turns on --log-bin instead.", (uchar**) &opt_update_logname, (uchar**) &opt_update_logname, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0}, {"log-warnings", 'W', "Log some not critical warnings to the log file.", @@ -6338,7 +6338,10 @@ thread is in the relay logs.", "Tells the slave thread to not replicate to the specified database. To specify more than one database to ignore, use the directive multiple times, once for each database. This option will not work if you use cross database updates. If you need cross database updates to work, make sure you have 3.23.28 or later, and use replicate-wild-ignore-table=db_name.%. ", 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"replicate-ignore-table", OPT_REPLICATE_IGNORE_TABLE, - "Tells the slave thread to not replicate to the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-datbase updates, in contrast to replicate-ignore-db.", + "Tells the slave thread to not replicate to the specified table. To specify " + "more than one table to ignore, use the directive multiple times, once for " + "each table. This will work for cross-database updates, in contrast to " + "replicate-ignore-db.", 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"replicate-rewrite-db", OPT_REPLICATE_REWRITE_DB, "Updates to a database with a different name than the original. Example: replicate-rewrite-db=master_db_name->slave_db_name.", @@ -6360,7 +6363,13 @@ Can't be set to 1 if --log-slave-updates is used.", 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, // In replication, we may need to tell the other servers how to connect {"report-host", OPT_REPORT_HOST, - "Hostname or IP of the slave to be reported to to the master during slave registration. Will appear in the output of SHOW SLAVE HOSTS. Leave unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP of the slave off the socket once the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.", + "Hostname or IP of the slave to be reported to the master during slave " + "registration. Will appear in the output of SHOW SLAVE HOSTS. Leave unset " + "if you do not want the slave to register itself with the master. Note that " + "it is not sufficient for the master to simply read the IP of the slave " + "from the socket once the slave connects. Due to NAT and other routing " + "issues, that IP may not be valid for connecting to the slave from the " + "master or other hosts.", (uchar**) &report_host, (uchar**) &report_host, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"report-password", OPT_REPORT_PASSWORD, "Undocumented.", @@ -6665,7 +6674,10 @@ log and this option does nothing anymore.", (uchar**) &max_system_variables.keep_files_on_create, 0, GET_BOOL, OPT_ARG, 0, 0, 0, 0, 0, 0}, {"key_buffer_size", OPT_KEY_BUFFER_SIZE, - "The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common.", + "The size of the buffer used for index blocks for MyISAM tables. Increase " + "this to get better index handling (for all reads and multiple writes) to " + "as much as you can afford; 1GB on a 4GB machine that mainly runs MySQL is " + "quite common.", (uchar**) &dflt_key_cache_var.param_buff_size, (uchar**) 0, 0, (GET_ULL | GET_ASK_ADDR), @@ -6836,7 +6848,9 @@ The minimum value for this variable is 4096.", (uchar**) &myisam_mmap_size, (uchar**) &myisam_mmap_size, 0, GET_ULL, REQUIRED_ARG, SIZE_T_MAX, MEMMAP_EXTRA_MARGIN, SIZE_T_MAX, 0, 1, 0}, {"myisam_repair_threads", OPT_MYISAM_REPAIR_THREADS, - "Number of threads to use when repairing MyISAM tables. The value of 1 disables parallel repair.", + "Specifies whether several threads should be used when repairing MyISAM " + "tables. For values > 1, one thread is used per index. The value of 1 " + "disables parallel repair.", (uchar**) &global_system_variables.myisam_repair_threads, (uchar**) &max_system_variables.myisam_repair_threads, 0, GET_ULONG, REQUIRED_ARG, 1, 1, ULONG_MAX, 0, 1, 0}, -- cgit v1.2.1 From ee4a3099b3ebf0c9b448e4404665bb0a8638dc3f Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 2 Mar 2010 14:43:21 +0200 Subject: Moved the ndb related tests out of the per-push pb2 run to the daily pb2 run. --- mysql-test/collections/default.daily | 6 ++++++ mysql-test/collections/default.push | 10 +++++----- 2 files changed, 11 insertions(+), 5 deletions(-) diff --git a/mysql-test/collections/default.daily b/mysql-test/collections/default.daily index 56714662b8f..c71297878f7 100644 --- a/mysql-test/collections/default.daily +++ b/mysql-test/collections/default.daily @@ -1 +1,7 @@ perl mysql-test-run.pl --timer --force --comment=rpl_ndb_row --vardir=var-rpl_ndb_row --suite=rpl_ndb,ndb --mysqld=--binlog-format=row --experimental=collections/default.experimental + +perl mysql-test-run.pl --timer --force --parallel=auto --comment=n_mix --vardir=var-n_mix --mysqld=--binlog-format=mixed --experimental=collections/default.experimental +perl mysql-test-run.pl --timer --force --parallel=auto --comment=ps_row --vardir=var-ps_row --ps-protocol --mysqld=--binlog-format=row --experimental=collections/default.experimental +perl mysql-test-run.pl --timer --force --parallel=auto --comment=embedded --vardir=var-emebbed --embedded --experimental=collections/default.experimental +perl mysql-test-run.pl --timer --force --parallel=auto --comment=rpl_binlog_row --vardir=var-rpl_binlog_row --suite=rpl,binlog --mysqld=--binlog-format=row --experimental=collections/default.experimental +perl mysql-test-run.pl --timer --force --parallel=auto --comment=funcs_1 --vardir=var-funcs_1 --suite=funcs_1 --experimental=collections/default.experimental diff --git a/mysql-test/collections/default.push b/mysql-test/collections/default.push index 0503bd49f73..77dc9a586db 100644 --- a/mysql-test/collections/default.push +++ b/mysql-test/collections/default.push @@ -1,5 +1,5 @@ -perl mysql-test-run.pl --timer --force --parallel=auto --comment=n_mix --vardir=var-n_mix --mysqld=--binlog-format=mixed --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=ps_row --vardir=var-ps_row --ps-protocol --mysqld=--binlog-format=row --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=embedded --vardir=var-emebbed --embedded --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=rpl_binlog_row --vardir=var-rpl_binlog_row --suite=rpl,binlog --mysqld=--binlog-format=row --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=funcs_1 --vardir=var-funcs_1 --suite=funcs_1 --experimental=collections/default.experimental +perl mysql-test-run.pl --timer --force --parallel=auto --comment=n_mix --vardir=var-n_mix --mysqld=--binlog-format=mixed --experimental=collections/default.experimental --skip-ndb +perl mysql-test-run.pl --timer --force --parallel=auto --comment=ps_row --vardir=var-ps_row --ps-protocol --mysqld=--binlog-format=row --experimental=collections/default.experimental --skip-ndb +perl mysql-test-run.pl --timer --force --parallel=auto --comment=embedded --vardir=var-emebbed --embedded --experimental=collections/default.experimental --skip-ndb +perl mysql-test-run.pl --timer --force --parallel=auto --comment=rpl_binlog_row --vardir=var-rpl_binlog_row --suite=rpl,binlog --mysqld=--binlog-format=row --experimental=collections/default.experimental --skip-ndb +perl mysql-test-run.pl --timer --force --parallel=auto --comment=funcs_1 --vardir=var-funcs_1 --suite=funcs_1 --experimental=collections/default.experimental --skip-ndb -- cgit v1.2.1 From c610e9783aa4474d2b4418f1b3b3685716a83454 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Tue, 2 Mar 2010 18:00:53 +0000 Subject: Bug#48295: explain extended crash with subquery and ONLY_FULL_GROUP_BY sql If an outer query is broken, a subquery might not even get set up. EXPLAIN EXTENDED did not expect this and merrily tried to de-ref all of the half-setup info. We now catch this case and print as much as we have, as it doesn't cost us anything (doesn't make regular execution slower). backport from 5.1 mysql-test/r/explain.result: Show that EXPLAIN EXTENDED with subquery and illegal out query doesn't crash. Show also that SHOW WARNINGS will render an additional Note in the hope of being, well, helpful. mysql-test/t/explain.test: If we have only half a query for EXPLAIN EXTENDED to print (i.e., incomplete subquery info as outer query is illegal), we should provide the user with as much info as we easily can if they ask for it. What we should not do is crash when they come asking for help, that violates etiquette in some countries. sql/item_subselect.cc: If the sub-query's actually set up, print it. Otherwise, elide. --- mysql-test/r/explain.result | 17 +++++++++++++++++ mysql-test/t/explain.test | 22 +++++++++++++++++++++- sql/item_subselect.cc | 11 ++++++++--- 3 files changed, 46 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 8d3859fd68e..90a4136d030 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -167,3 +167,20 @@ EXPLAIN SELECT DISTINCT 1 FROM t1, WHERE t1.a = d1.a; ERROR 42S22: Unknown column 'd1.a' in 'where clause' DROP TABLE t1; +# +# Bug#48295: +# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +# +CREATE TABLE t1 (f1 INT); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +EXPLAIN EXTENDED SELECT 1 FROM t1 +WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SHOW WARNINGS; +Level Code Message +Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +Note 1003 select 1 AS `1` from `test`.`t1` where ((...)) +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1; +End of 5.0 tests. diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 7b7bdd3563c..1b16c811dbd 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -147,4 +147,24 @@ EXPLAIN SELECT DISTINCT 1 FROM t1, WHERE t1.a = d1.a; DROP TABLE t1; -# End of 5.0 tests. +--echo # +--echo # Bug#48295: +--echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +--echo # + +CREATE TABLE t1 (f1 INT); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE. +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +EXPLAIN EXTENDED SELECT 1 FROM t1 + WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); +SHOW WARNINGS; + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1; + +--echo End of 5.0 tests. diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 80fbc2c74d3..1822a7ced56 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -262,9 +262,14 @@ void Item_subselect::update_used_tables() void Item_subselect::print(String *str) { - str->append('('); - engine->print(str); - str->append(')'); + if (engine) + { + str->append('('); + engine->print(str); + str->append(')'); + } + else + str->append("(...)"); } -- cgit v1.2.1 From 2eb9d388f6ed28c6bd7065d540110d78ad3e765e Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Fri, 5 Mar 2010 14:51:37 +0400 Subject: Fix for bug#32426: "FEDERATED query returns corrupt results for ORDER BY on a TEXT or VARCHAR field" backported to 5.1. --- mysql-test/suite/federated/federated.result | 23 ++++ mysql-test/suite/federated/federated.test | 22 +++ storage/federated/ha_federated.cc | 202 ++++++++++++++++------------ storage/federated/ha_federated.h | 9 ++ 4 files changed, 172 insertions(+), 84 deletions(-) diff --git a/mysql-test/suite/federated/federated.result b/mysql-test/suite/federated/federated.result index 57f665995a1..db4ffc38213 100644 --- a/mysql-test/suite/federated/federated.result +++ b/mysql-test/suite/federated/federated.result @@ -2153,6 +2153,29 @@ DROP TABLE t1; End of 5.0 tests create server 's1' foreign data wrapper 'mysql' options (port 3306); drop server 's1'; +# +# Bug #32426: FEDERATED query returns corrupt results for ORDER BY on a TEXT +# +CREATE TABLE federated.t1(a TEXT); +INSERT INTO federated.t1 VALUES('abc'), ('gh'), ('f'), ('ijk'), ('de'); +CREATE TABLE federated.t1(a TEXT) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +SELECT * FROM federated.t1 ORDER BY A; +a +abc +de +f +gh +ijk +SELECT * FROM federated.t1 ORDER BY A DESC; +a +ijk +gh +f +de +abc +DROP TABLE federated.t1; +DROP TABLE federated.t1; End of 5.1 tests SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; diff --git a/mysql-test/suite/federated/federated.test b/mysql-test/suite/federated/federated.test index 870bbcb8f71..a1d86462c11 100644 --- a/mysql-test/suite/federated/federated.test +++ b/mysql-test/suite/federated/federated.test @@ -1971,6 +1971,28 @@ connection default; create server 's1' foreign data wrapper 'mysql' options (port 3306); drop server 's1'; + +--echo # +--echo # Bug #32426: FEDERATED query returns corrupt results for ORDER BY on a TEXT +--echo # +connection slave; +CREATE TABLE federated.t1(a TEXT); +INSERT INTO federated.t1 VALUES('abc'), ('gh'), ('f'), ('ijk'), ('de'); + +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1(a TEXT) ENGINE=FEDERATED + CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +SELECT * FROM federated.t1 ORDER BY A; +SELECT * FROM federated.t1 ORDER BY A DESC; +DROP TABLE federated.t1; + +connection slave; +DROP TABLE federated.t1; + +connection default; + + --echo End of 5.1 tests SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; connection slave; diff --git a/storage/federated/ha_federated.cc b/storage/federated/ha_federated.cc index 8deb8693960..f8dde34b82b 100644 --- a/storage/federated/ha_federated.cc +++ b/storage/federated/ha_federated.cc @@ -1621,11 +1621,10 @@ int ha_federated::open(const char *name, int mode, uint test_if_locked) DBUG_ASSERT(mysql == NULL); - ref_length= (table->s->primary_key != MAX_KEY ? - table->key_info[table->s->primary_key].key_length : - table->s->reclength); + ref_length= sizeof(MYSQL_RES *) + sizeof(MYSQL_ROW_OFFSET); DBUG_PRINT("info", ("ref_length: %u", ref_length)); + my_init_dynamic_array(&results, sizeof(MYSQL_RES *), 4, 4); reset(); DBUG_RETURN(0); @@ -1645,21 +1644,17 @@ int ha_federated::open(const char *name, int mode, uint test_if_locked) int ha_federated::close(void) { - int retval; DBUG_ENTER("ha_federated::close"); - /* free the result set */ - if (stored_result) - { - mysql_free_result(stored_result); - stored_result= 0; - } + free_result(); + + delete_dynamic(&results); + /* Disconnect from mysql */ mysql_close(mysql); mysql= NULL; - retval= free_share(share); - DBUG_RETURN(retval); + DBUG_RETURN(free_share(share)); } /* @@ -2326,8 +2321,7 @@ int ha_federated::index_read(uchar *buf, const uchar *key, { DBUG_ENTER("ha_federated::index_read"); - if (stored_result) - mysql_free_result(stored_result); + free_result(); DBUG_RETURN(index_read_idx_with_result_set(buf, active_index, key, key_len, find_flag, &stored_result)); @@ -2359,7 +2353,8 @@ int ha_federated::index_read_idx(uchar *buf, uint index, const uchar *key, &mysql_result))) DBUG_RETURN(retval); mysql_free_result(mysql_result); - DBUG_RETURN(retval); + results.elements--; + DBUG_RETURN(0); } @@ -2415,18 +2410,20 @@ int ha_federated::index_read_idx_with_result_set(uchar *buf, uint index, retval= ER_QUERY_ON_FOREIGN_DATA_SOURCE; goto error; } - if (!(*result= mysql_store_result(mysql))) + if (!(*result= store_result(mysql))) { retval= HA_ERR_END_OF_FILE; goto error; } - if (!(retval= read_next(buf, *result))) + if ((retval= read_next(buf, *result))) + { + mysql_free_result(*result); + results.elements--; + *result= 0; + table->status= STATUS_NOT_FOUND; DBUG_RETURN(retval); - - mysql_free_result(*result); - *result= 0; - table->status= STATUS_NOT_FOUND; - DBUG_RETURN(retval); + } + DBUG_RETURN(0); error: table->status= STATUS_NOT_FOUND; @@ -2486,12 +2483,6 @@ int ha_federated::read_range_first(const key_range *start_key, create_where_from_key(&sql_query, &table->key_info[active_index], start_key, end_key, 0, eq_range_arg); - - if (stored_result) - { - mysql_free_result(stored_result); - stored_result= 0; - } if (real_query(sql_query.ptr(), sql_query.length())) { retval= ER_QUERY_ON_FOREIGN_DATA_SOURCE; @@ -2499,14 +2490,13 @@ int ha_federated::read_range_first(const key_range *start_key, } sql_query.length(0); - if (!(stored_result= mysql_store_result(mysql))) + if (!(stored_result= store_result(mysql))) { retval= HA_ERR_END_OF_FILE; goto error; } - - retval= read_next(table->record[0], stored_result); - DBUG_RETURN(retval); + + DBUG_RETURN(read_next(table->record[0], stored_result)); error: table->status= STATUS_NOT_FOUND; @@ -2516,10 +2506,8 @@ error: int ha_federated::read_range_next() { - int retval; DBUG_ENTER("ha_federated::read_range_next"); - retval= rnd_next(table->record[0]); - DBUG_RETURN(retval); + DBUG_RETURN(rnd_next(table->record[0])); } @@ -2585,23 +2573,11 @@ int ha_federated::rnd_init(bool scan) if (scan) { - if (stored_result) - { - mysql_free_result(stored_result); - stored_result= 0; - } - - if (real_query(share->select_query, strlen(share->select_query))) - goto error; - - stored_result= mysql_store_result(mysql); - if (!stored_result) - goto error; + if (real_query(share->select_query, strlen(share->select_query)) || + !(stored_result= store_result(mysql))) + DBUG_RETURN(stash_remote_error()); } DBUG_RETURN(0); - -error: - DBUG_RETURN(stash_remote_error()); } @@ -2615,11 +2591,7 @@ int ha_federated::rnd_end() int ha_federated::index_end(void) { DBUG_ENTER("ha_federated::index_end"); - if (stored_result) - { - mysql_free_result(stored_result); - stored_result= 0; - } + free_result(); active_index= MAX_KEY; DBUG_RETURN(0); } @@ -2679,6 +2651,9 @@ int ha_federated::read_next(uchar *buf, MYSQL_RES *result) DBUG_ENTER("ha_federated::read_next"); table->status= STATUS_NOT_FOUND; // For easier return + + /* Save current data cursor position. */ + current_position= result->data_cursor; /* Fetch a row, insert it back in a row format. */ if (!(row= mysql_fetch_row(result))) @@ -2691,24 +2666,38 @@ int ha_federated::read_next(uchar *buf, MYSQL_RES *result) } -/* - store reference to current row so that we can later find it for - a re-read, update or delete. - - In case of federated, a reference is either a primary key or - the whole record. - - Called from filesort.cc, sql_select.cc, sql_delete.cc and sql_update.cc. +/** + @brief Store a reference to current row. + + @details During a query execution we may have different result sets (RS), + e.g. for different ranges. All the RS's used are stored in + memory and placed in @c results dynamic array. At the end of + execution all stored RS's are freed at once in the + @c ha_federated::reset(). + So, in case of federated, a reference to current row is a + stored result address and current data cursor position. + As we keep all RS in memory during a query execution, + we can get any record using the reference any time until + @c ha_federated::reset() is called. + TODO: we don't have to store all RS's rows but only those + we call @c ha_federated::position() for, so we can free memory + where we store other rows in the @c ha_federated::index_end(). + + @param[in] record record data (unused) */ -void ha_federated::position(const uchar *record) +void ha_federated::position(const uchar *record __attribute__ ((unused))) { DBUG_ENTER("ha_federated::position"); - if (table->s->primary_key != MAX_KEY) - key_copy(ref, (uchar *)record, table->key_info + table->s->primary_key, - ref_length); - else - memcpy(ref, record, ref_length); + + DBUG_ASSERT(stored_result); + + position_called= TRUE; + /* Store result set address. */ + memcpy_fixed(ref, &stored_result, sizeof(MYSQL_RES *)); + /* Store data cursor position. */ + memcpy_fixed(ref + sizeof(MYSQL_RES *), ¤t_position, + sizeof(MYSQL_ROW_OFFSET)); DBUG_VOID_RETURN; } @@ -2724,23 +2713,19 @@ void ha_federated::position(const uchar *record) int ha_federated::rnd_pos(uchar *buf, uchar *pos) { - int result; + MYSQL_RES *result; DBUG_ENTER("ha_federated::rnd_pos"); + ha_statistic_increment(&SSV::ha_read_rnd_count); - if (table->s->primary_key != MAX_KEY) - { - /* We have a primary key, so use index_read_idx to find row */ - result= index_read_idx(buf, table->s->primary_key, pos, - ref_length, HA_READ_KEY_EXACT); - } - else - { - /* otherwise, get the old record ref as obtained in ::position */ - memcpy(buf, pos, ref_length); - result= 0; - } - table->status= result ? STATUS_NOT_FOUND : 0; - DBUG_RETURN(result); + + /* Get stored result set. */ + memcpy_fixed(&result, pos, sizeof(MYSQL_RES *)); + DBUG_ASSERT(result); + /* Set data cursor position. */ + memcpy_fixed(&result->data_cursor, pos + sizeof(MYSQL_RES *), + sizeof(MYSQL_ROW_OFFSET)); + /* Read a row. */ + DBUG_RETURN(read_next(buf, result)); } @@ -2943,6 +2928,16 @@ int ha_federated::reset(void) insert_dup_update= FALSE; ignore_duplicates= FALSE; replace_duplicates= FALSE; + + /* Free stored result sets. */ + for (uint i= 0; i < results.elements; i++) + { + MYSQL_RES *result; + get_dynamic(&results, (uchar *) &result, i); + mysql_free_result(result); + } + reset_dynamic(&results); + return 0; } @@ -3206,6 +3201,45 @@ bool ha_federated::get_error_message(int error, String* buf) DBUG_RETURN(FALSE); } + +/** + @brief Store a result set. + + @details Call @c mysql_store_result() to save a result set then + append it to the stored results array. + + @param[in] mysql MySLQ connection structure. + + @return Stored result set (MYSQL_RES object). +*/ + +MYSQL_RES *ha_federated::store_result(MYSQL *mysql) +{ + MYSQL_RES *result= mysql_store_result(mysql); + DBUG_ENTER("ha_federated::store_result"); + if (result) + { + (void) insert_dynamic(&results, (uchar*) &result); + } + position_called= FALSE; + DBUG_RETURN(result); +} + + +void ha_federated::free_result() +{ + DBUG_ENTER("ha_federated::free_result"); + if (stored_result && !position_called) + { + mysql_free_result(stored_result); + stored_result= 0; + if (results.elements > 0) + results.elements--; + } + DBUG_VOID_RETURN; +} + + int ha_federated::external_lock(THD *thd, int lock_type) { int error= 0; diff --git a/storage/federated/ha_federated.h b/storage/federated/ha_federated.h index 2f1c62decca..2e0a19e592f 100644 --- a/storage/federated/ha_federated.h +++ b/storage/federated/ha_federated.h @@ -84,6 +84,11 @@ class ha_federated: public handler FEDERATED_SHARE *share; /* Shared lock info */ MYSQL *mysql; /* MySQL connection */ MYSQL_RES *stored_result; + /** + Array of all stored results we get during a query execution. + */ + DYNAMIC_ARRAY results; + bool position_called; uint fetch_num; // stores the fetch num MYSQL_ROW_OFFSET current_position; // Current position used by ::position() int remote_error_number; @@ -251,6 +256,10 @@ public: THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to, enum thr_lock_type lock_type); //required bool get_error_message(int error, String *buf); + + MYSQL_RES *store_result(MYSQL *mysql); + void free_result(); + int external_lock(THD *thd, int lock_type); int connection_commit(); int connection_rollback(); -- cgit v1.2.1 From 63a88e137365c4158e22c1c8aba7847b5a0ffe86 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Fri, 5 Mar 2010 23:45:55 +0400 Subject: Bug #39653: find_shortest_key in sql_select.cc does not consider clustered primary keys Choosing a shortest index for the covering index scan, the optimizer ignored the fact, that the clustered primary key read involves whole table data. The find_shortest_key function has been modified to take into account that fact that a clustered PK has a longest key of possible covering indices. mysql-test/r/innodb_mysql.result: Test case for bug #39653. mysql-test/t/innodb_mysql.test: Test case for bug #39653. sql/sql_select.cc: Bug #39653: find_shortest_key in sql_select.cc does not consider clustered primary keys The find_shortest_key function has been modified to take into account that fact that a clustered PK has a longest key of possible covering indices. --- mysql-test/r/innodb_mysql.result | 22 ++++++++++++++++++++++ mysql-test/t/innodb_mysql.test | 18 ++++++++++++++++++ sql/sql_select.cc | 27 +++++++++++++++++++++++++-- 3 files changed, 65 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index c4279018cb8..586cd5477a7 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2295,4 +2295,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index drop table t1,t2; # +# +# Bug #39653: find_shortest_key in sql_select.cc does not consider +# clustered primary keys +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, +KEY (b,c)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), +(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), +(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), +(11,11,11,11,11,11); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 10 +ref NULL +rows 10 +Extra Using index +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index a3c11b8b8d6..75fff9656e2 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -558,4 +558,22 @@ drop table t1,t2; --echo # +--echo # +--echo # Bug #39653: find_shortest_key in sql_select.cc does not consider +--echo # clustered primary keys +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, + KEY (b,c)) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), + (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), + (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), + (11,11,11,11,11,11); + +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP TABLE t1; + + --echo End of 5.1 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 773aa7da139..bc68d3b03e0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12912,12 +12912,35 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, uint find_shortest_key(TABLE *table, const key_map *usable_keys) { - uint min_length= (uint) ~0; uint best= MAX_KEY; + uint usable_clustered_pk= (table->file->primary_key_is_clustered() && + table->s->primary_key != MAX_KEY && + usable_keys->is_set(table->s->primary_key)) ? + table->s->primary_key : MAX_KEY; if (!usable_keys->is_clear_all()) { + uint min_length= (uint) ~0; for (uint nr=0; nr < table->s->keys ; nr++) { + /* + As far as + 1) clustered primary key entry data set is a set of all record + fields (key fields and not key fields) and + 2) secondary index entry data is a union of its key fields and + primary key fields (at least InnoDB and its derivatives don't + duplicate primary key fields there, even if the primary and + the secondary keys have a common subset of key fields), + then secondary index entry data is always a subset of primary key + entry, and the PK is always longer. + Unfortunately, key_info[nr].key_length doesn't show the length + of key/pointer pair but a sum of key field lengths only, thus + we can't estimate index IO volume comparing only this key_length + value of seconday keys and clustered PK. + So, try secondary keys first, and choose PK only if there are no + usable secondary covering keys: + */ + if (nr == usable_clustered_pk) + continue; if (usable_keys->is_set(nr)) { if (table->key_info[nr].key_length < min_length) @@ -12928,7 +12951,7 @@ uint find_shortest_key(TABLE *table, const key_map *usable_keys) } } } - return best; + return best != MAX_KEY ? best : usable_clustered_pk; } /** -- cgit v1.2.1