diff options
author | Kristofer Pettersson <kristofer.pettersson@sun.com> | 2009-09-15 09:33:25 +0200 |
---|---|---|
committer | Kristofer Pettersson <kristofer.pettersson@sun.com> | 2009-09-15 09:33:25 +0200 |
commit | a041c79ec9122277a9c09d3082011f46d24d4578 (patch) | |
tree | e2b6157a2db75a4dc07850ee7a3c74ab9822b71f /mysql-test | |
parent | cae61b1ce435366e05b52cc9251257163bf4846a (diff) | |
parent | 09f07bc1107c0facc32c16c58bae0215ce08eeea (diff) | |
download | mariadb-git-a041c79ec9122277a9c09d3082011f46d24d4578.tar.gz |
autocommit
Diffstat (limited to 'mysql-test')
51 files changed, 1587 insertions, 76 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment.test b/mysql-test/extra/rpl_tests/rpl_auto_increment.test index 24448a38408..abf3b4ec676 100644 --- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test @@ -163,5 +163,81 @@ show create table t1; connection master; drop table t1; +# +# BUG#45999 Row based replication fails when auto_increment field = 0. +# Store engine of Slaves auto-generates new sequence numbers for +# auto_increment fields if the values of them are 0. There is an inconsistency +# between slave and master. When MODE_NO_AUTO_VALUE_ON_ZERO are masters treat +# +source include/master-slave-reset.inc; + +connection master; +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type; +eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type2; +SET SQL_MODE=''; +# Value of the id will be 1; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t2 VALUES(NULL); +SELECT * FROM t1; +SELECT * FROM t2; +# Value of the id will be 2; +INSERT INTO t1 VALUES(); +INSERT INTO t2 VALUES(); +SELECT * FROM t1; +SELECT * FROM t2; +# Value of the id will be 3. The master treats 0 as NULL or empty because +# NO_AUTO_VALUE_ON_ZERO is not assign to SQL_MODE. +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +SELECT * FROM t2; + +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +# Value of the id will be 0. The master does not treat 0 as NULL or empty +# because NO_AUTO_VALUE_ON_ZERO has assigned to SQL_MODE. +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +SELECT * FROM t2; + +INSERT INTO t1 VALUES(4); +INSERT INTO t2 VALUES(4); +FLUSH LOGS; +sync_slave_with_master; + +let $diff_table_1= master:test.t1; +let $diff_table_2= slave:test.t1; +source include/diff_tables.inc; + +let $diff_table_1= master:test.t2; +let $diff_table_2= slave:test.t2; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +sync_slave_with_master; + +connection master; +let $MYSQLD_DATADIR= `SELECT @@DATADIR`; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL test +sync_slave_with_master; + +let $diff_table_1= master:test.t1; +let $diff_table_2= slave:test.t1; +source include/diff_tables.inc; + +let $diff_table_1= master:test.t2; +let $diff_table_2= slave:test.t2; +source include/diff_tables.inc; + # End cleanup +DROP TABLE t1; +DROP TABLE t2; +SET SQL_MODE=''; sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_row_sp006.test b/mysql-test/extra/rpl_tests/rpl_row_sp006.test index 897d7e492bf..16a8374ae7f 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_sp006.test +++ b/mysql-test/extra/rpl_tests/rpl_row_sp006.test @@ -9,29 +9,27 @@ ############################################################################# # Begin clean up test section -connection master; --disable_warnings -create database if not exists mysqltest1; -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP TABLE IF EXISTS mysqltest1.t1; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; --enable_warnings # End of cleanup # Begin test section 1 -eval CREATE TABLE IF NOT EXISTS mysqltest1.t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=$engine_type; -eval CREATE TABLE IF NOT EXISTS mysqltest1.t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=$engine_type; +eval CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=$engine_type; +eval CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=$engine_type; delimiter |; -CREATE PROCEDURE mysqltest1.p1() +CREATE PROCEDURE p1() BEGIN DECLARE done INT DEFAULT 0; DECLARE spa CHAR(16); DECLARE spb INT; DECLARE cur1 CURSOR FOR SELECT name, (YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) - FROM mysqltest1.t1; + FROM t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; @@ -41,7 +39,7 @@ BEGIN FETCH cur1 INTO spa, spb; IF NOT done THEN START TRANSACTION; - INSERT INTO mysqltest1.t2 VALUES (spa,spb); + INSERT INTO t2 VALUES (spa,spb); COMMIT; END IF; UNTIL done END REPEAT; @@ -49,30 +47,29 @@ BEGIN SET AUTOCOMMIT=1; CLOSE cur1; END| -CREATE PROCEDURE mysqltest1.p2() +CREATE PROCEDURE p2() BEGIN - INSERT INTO mysqltest1.t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); + INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); END| delimiter ;| -CALL mysqltest1.p2(); +CALL p2(); sync_slave_with_master; connection master; -CALL mysqltest1.p1(); +CALL p1(); sync_slave_with_master; connection master; ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/sp006_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/sp006_slave.sql +--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_master.sql +--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_slave.sql -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t1; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP DATABASE mysqltest1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP PROCEDURE p2; # Lets compare. Note: If they match test will pass, if they do not match # the test will show that the diff statement failed and not reject file diff --git a/mysql-test/include/have_dynamic_loading.inc b/mysql-test/include/have_dynamic_loading.inc new file mode 100644 index 00000000000..1b2c85b3904 --- /dev/null +++ b/mysql-test/include/have_dynamic_loading.inc @@ -0,0 +1,7 @@ +# +# Whether server supports dynamic loading. +# +--require r/have_dynamic_loading.require +disable_query_log; +show variables like 'have_dynamic_loading'; +enable_query_log; diff --git a/mysql-test/include/have_example_plugin.inc b/mysql-test/include/have_example_plugin.inc index 8e57c725eb5..a2fffc17b97 100644 --- a/mysql-test/include/have_example_plugin.inc +++ b/mysql-test/include/have_example_plugin.inc @@ -2,10 +2,7 @@ # Check if server has support for loading udf's # i.e it will support dlopen # ---require r/have_dynamic_loading.require -disable_query_log; -show variables like 'have_dynamic_loading'; -enable_query_log; +--source include/have_dynamic_loading.inc # # Check if the variable EXAMPLE_PLUGIN is set diff --git a/mysql-test/include/have_simple_parser.inc b/mysql-test/include/have_simple_parser.inc index c85786bd524..5a4dc93ec81 100644 --- a/mysql-test/include/have_simple_parser.inc +++ b/mysql-test/include/have_simple_parser.inc @@ -2,10 +2,7 @@ # Check if server has support for loading udf's # i.e it will support dlopen # ---require r/have_dynamic_loading.require -disable_query_log; -show variables like 'have_dynamic_loading'; -enable_query_log; +--source include/have_dynamic_loading.inc # # Check if the variable SIMPLE_PARSER is set diff --git a/mysql-test/include/have_udf.inc b/mysql-test/include/have_udf.inc index 3f7e260c5ba..7be57bbb7a9 100644 --- a/mysql-test/include/have_udf.inc +++ b/mysql-test/include/have_udf.inc @@ -2,10 +2,7 @@ # Check if server has support for loading udf's # i.e it will support dlopen # ---require r/have_dynamic_loading.require -disable_query_log; -show variables like 'have_dynamic_loading'; -enable_query_log; +--source include/have_dynamic_loading.inc # # Check if the variable UDF_EXAMPLE_LIB is set diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 8c26ea1ff82..e865d775c6a 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12695,3 +12695,25 @@ a b 1 NULL 2 NULL DROP TABLE t1; +CREATE TABLE t1(a INT, b BLOB) ENGINE=archive; +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM +INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DATA_LENGTH AVG_ROW_LENGTH +8666 15 +INSERT INTO t1 VALUES(1, 'sampleblob1'),(2, 'sampleblob2'); +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM +INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DATA_LENGTH AVG_ROW_LENGTH +8700 4350 +DROP TABLE t1; +SET @save_join_buffer_size= @@join_buffer_size; +SET @@join_buffer_size= 8228; +CREATE TABLE t1(a CHAR(255)) ENGINE=archive; +INSERT INTO t1 VALUES('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), +('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), +('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +SELECT COUNT(t1.a) FROM t1, t1 a, t1 b, t1 c, t1 d, t1 e; +COUNT(t1.a) +729 +DROP TABLE t1; +SET @@join_buffer_size= @save_join_buffer_size; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 3373d206952..b829ef30fb1 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1572,6 +1572,19 @@ CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' DROP TABLE t1, t2; +# +# BUG#46384 - mysqld segfault when trying to create table with same +# name as existing view +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); +CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; +CREATE TABLE v1 AS SELECT * FROM t1; +ERROR 42S01: Table 'v1' already exists +DROP VIEW v1; +DROP TABLE t1,t2; End of 5.0 tests CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); diff --git a/mysql-test/r/ctype_gbk_binlog.result b/mysql-test/r/ctype_gbk_binlog.result index a49e170ff19..df927af9a6b 100644 --- a/mysql-test/r/ctype_gbk_binlog.result +++ b/mysql-test/r/ctype_gbk_binlog.result @@ -1,3 +1,4 @@ +RESET MASTER; SET NAMES gbk; CREATE TABLE t1 ( f1 BLOB diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index e0324af8cfd..b1cb70fa43c 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -763,4 +763,34 @@ a b d c 1 2 0 2 1 2 0 3 DROP TABLE t1; +# +# Bug #46159: simple query that never returns +# +SET @old_max_heap_table_size = @@max_heap_table_size; +SET @@max_heap_table_size = 16384; +SET @old_sort_buffer_size = @@sort_buffer_size; +SET @@sort_buffer_size = 32804; +CREATE TABLE t1(c1 int, c2 VARCHAR(20)); +INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; +c1 c2 COUNT(*) +1 1 2 +2 2 1 +3 1 2 +4 4 1 +SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; +c2 +1 +5 +DROP TABLE t1; +SET @@sort_buffer_size = @old_sort_buffer_size; +SET @@max_heap_table_size = @old_max_heap_table_size; End of 5.1 tests diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a81ff554ca2..96fcbc33d3f 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -159,6 +159,14 @@ CREATE TABLE t1 (a INT PRIMARY KEY); EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a); ERROR 42000: Key 'a' doesn't exist in table 't1' DROP TABLE t1; +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); +EXPLAIN SELECT DISTINCT 1 FROM t1, +(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 +WHERE t1.a = d1.a; +ERROR 42S22: Unknown column 'd1.a' in 'where clause' +DROP TABLE t1; # # Bug#37870: Usage of uninitialized value caused failed assertion. # diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index a0c3935fde0..2a2fe50ad0f 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2534,6 +2534,15 @@ SELECT LOAD_FILE(a) FROM t1; LOAD_FILE(a) NULL DROP TABLE t1; +CREATE TABLE t1 (f2 VARCHAR(20)); +CREATE TABLE t2 (f2 VARCHAR(20)); +INSERT INTO t1 VALUES ('MIN'),('MAX'); +INSERT INTO t2 VALUES ('LOAD'); +SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; +concat_name +LOAD_MIN +LOAD_MAX +DROP TABLE t1, t2; End of 5.0 tests drop table if exists t1; create table t1(f1 tinyint default null)engine=myisam; diff --git a/mysql-test/r/lowercase_mixed_tmpdir_innodb.result b/mysql-test/r/lowercase_mixed_tmpdir_innodb.result new file mode 100755 index 00000000000..a478b49cfda --- /dev/null +++ b/mysql-test/r/lowercase_mixed_tmpdir_innodb.result @@ -0,0 +1,6 @@ +drop table if exists t1; +create table t1 (id int) engine=InnoDB; +insert into t1 values (1); +create temporary table t2 engine=InnoDB select * from t1; +drop temporary table t2; +drop table t1; diff --git a/mysql-test/r/mysqlbinlog-cp932.result b/mysql-test/r/mysqlbinlog-cp932.result index 1640a3b1642..cbf6159516a 100644 --- a/mysql-test/r/mysqlbinlog-cp932.result +++ b/mysql-test/r/mysqlbinlog-cp932.result @@ -1,4 +1,4 @@ -flush logs; +RESET MASTER; create table t3 (f text character set utf8); create table t4 (f text character set cp932); flush logs; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index ad4d08e89ff..b8cfa25349d 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1,4 +1,18 @@ drop table if exists t1; +create table t1 (a int not null, +b datetime not null, +primary key (a,b)) +engine=innodb +partition by range (to_days(b)) +subpartition by hash (a) +subpartitions 2 +( partition p0 values less than (to_days('2009-01-01')), +partition p1 values less than (to_days('2009-02-01')), +partition p2 values less than (to_days('2009-03-01')), +partition p3 values less than maxvalue); +alter table t1 reorganize partition p1,p2 into +( partition p2 values less than (to_days('2009-03-01'))); +drop table t1; CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (5), @@ -256,3 +270,7 @@ SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ drop table t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB +PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); +CREATE INDEX i1 ON t1 (a); +DROP TABLE t1; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 3ad556b8c30..67514c314f4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6963,6 +6963,22 @@ CALL p1(); CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; +# +# Bug #46629: Item_in_subselect::val_int(): Assertion `0' +# on subquery inside a SP +# +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT, b INT PRIMARY KEY); +CREATE PROCEDURE p1 () +BEGIN +SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); +END| +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +DROP PROCEDURE p1; +DROP TABLE t1, t2; # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result new file mode 100644 index 00000000000..68577cb2a4c --- /dev/null +++ b/mysql-test/r/subselect4.result @@ -0,0 +1,30 @@ +# +# Bug #46791: Assertion failed:(table->key_read==0),function unknown +# function,file sql_base.cc +# +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,1),(2,2); +CREATE TABLE t3 LIKE t1; +# should have 1 impossible where and 2 dependent subqueries +EXPLAIN +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +# should not crash the next statement +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +1 +1 +# should not crash: the crash is caused by the previous statement +SELECT 1; +1 +1 +DROP TABLE t1,t2,t3; +End of 5.0 tests. diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 15410ac2039..601b364fbbe 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -392,4 +392,20 @@ a 4 DROP FUNCTION sequence; DROP TABLE t1,t2; +# +# Bug#46259: 5.0.83 -> 5.1.36, query doesn't work +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1), (2), (3); +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 ); +b +1 +2 +3 +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); +b +2 +3 +1 +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 2e393aea9e4..8a87852d582 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -313,4 +313,9 @@ ERROR 22001: Data too long for column 'c_tinytext' at row 1 insert into t2 values(@q); ERROR 22001: Data too long for column 'c_tinyblob' at row 1 drop table t1, t2; +DROP TABLE t1; +ERROR 42S02: Unknown table 't1' +SHOW ERRORS; +Level Code Message +Error 1051 Unknown table 't1' End of 5.0 tests diff --git a/mysql-test/suite/binlog/r/binlog_incident.result b/mysql-test/suite/binlog/r/binlog_incident.result index d8b0357b8c4..7a555743723 100644 --- a/mysql-test/suite/binlog/r/binlog_incident.result +++ b/mysql-test/suite/binlog/r/binlog_incident.result @@ -1,3 +1,4 @@ +RESET MASTER; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT * FROM t1; diff --git a/mysql-test/suite/binlog/r/binlog_tmp_table.result b/mysql-test/suite/binlog/r/binlog_tmp_table.result index e4928432324..14b1963ffd9 100644 --- a/mysql-test/suite/binlog/r/binlog_tmp_table.result +++ b/mysql-test/suite/binlog/r/binlog_tmp_table.result @@ -1,3 +1,4 @@ +RESET MASTER; create table foo (a int); flush logs; create temporary table tmp1_foo like foo; diff --git a/mysql-test/suite/binlog/t/binlog_incident.test b/mysql-test/suite/binlog/t/binlog_incident.test index 208c7f24df2..901ac49ea24 100644 --- a/mysql-test/suite/binlog/t/binlog_incident.test +++ b/mysql-test/suite/binlog/t/binlog_incident.test @@ -6,6 +6,7 @@ source include/have_log_bin.inc; source include/have_debug.inc; let $MYSQLD_DATADIR= `select @@datadir`; +RESET MASTER; CREATE TABLE t1 (a INT); @@ -24,4 +25,4 @@ exec $MYSQL_BINLOG --start-position=106 $MYSQLD_DATADIR/master-bin.000001 >$MYSQ eval SELECT cont LIKE '%RELOAD DATABASE; # Shall generate syntax error%' AS `Contain RELOAD DATABASE` FROM (SELECT load_file('$MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql') AS cont) AS tbl; --enable_query_log -remove_file $MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql;
\ No newline at end of file +remove_file $MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql; diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table.test b/mysql-test/suite/binlog/t/binlog_tmp_table.test index 6947959a5e0..54af8a8cb68 100644 --- a/mysql-test/suite/binlog/t/binlog_tmp_table.test +++ b/mysql-test/suite/binlog/t/binlog_tmp_table.test @@ -30,6 +30,7 @@ source include/have_binlog_format_mixed_or_statement.inc; connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,); +RESET MASTER; create table foo (a int); diff --git a/mysql-test/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc index 6963de90c83..2c615e58ef9 100644 --- a/mysql-test/suite/parts/inc/partition_auto_increment.inc +++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc @@ -623,3 +623,195 @@ SHOW CREATE TABLE t1; SELECT * FROM t1 ORDER BY c1; DROP TABLE t1; +if (!$skip_negative_auto_inc) +{ +--echo ############################################################################# +--echo # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +--echo # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +--echo ############################################################################## + +--echo # Inserting negative autoincrement values into a partition table (partitions >= 4) + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Reading from a partition table (partitions >= 2 ) after inserting a negative +--echo # value into the auto increment column + + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2; + +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting negative auto increment value into a partition table (partitions >= 2) +--echo # auto increment value > 2. + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2; + +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting -1 into autoincrement column of a partition table (partition >= 4) + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); + +SELECT * FROM t ORDER BY c1 ASC; + +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Deleting from an auto increment table after inserting negative values + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_delete) +{ +DELETE FROM t WHERE c1 > 1; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting a positive value that exceeds maximum allowed value for an +--echo # Auto Increment column (positive maximum) + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); + +--error ER_DUP_ENTRY +INSERT INTO t VALUES (128,50); +--error ER_DUP_ENTRY +INSERT INTO t VALUES (129,60); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting a negative value that goes below minimum allowed value for an +--echo # Auto Increment column (negative minimum) + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); + +--error ER_DUP_ENTRY +INSERT INTO t VALUES (-129,50); +--error ER_DUP_ENTRY +INSERT INTO t VALUES (-130,60); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Updating the partition table with a negative Auto Increment value + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = -6 WHERE c1 = 2; +} + +SELECT * FROM t ORDER BY c1 ASC; + +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); + +if (!$skip_update) +{ +UPDATE t SET c1 = -6 WHERE c1 = 2; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Updating the partition table with a value that crosses the upper limits +--echo # on both the positive and the negative side. + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = 130 where c1 = 127; +} + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = -140 where c1 = 126; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo ############################################################################## +} diff --git a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result index 9a23cd4364e..6295d14d98f 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result @@ -825,3 +825,194 @@ c1 4 5 DROP TABLE t1; +############################################################################# +# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +############################################################################## +# Inserting negative autoincrement values into a partition table (partitions >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DROP TABLE t; +# Reading from a partition table (partitions >= 2 ) after inserting a negative +# value into the auto increment column +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-2 -20 +1 30 +DROP TABLE t; +# Inserting negative auto increment value into a partition table (partitions >= 2) +# auto increment value > 2. +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-4 -20 +1 30 +2 40 +DROP TABLE t; +# Inserting -1 into autoincrement column of a partition table (partition >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +DROP TABLE t; +# Deleting from an auto increment table after inserting negative values +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DELETE FROM t WHERE c1 > 1; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +DROP TABLE t; +# Inserting a positive value that exceeds maximum allowed value for an +# Auto Increment column (positive maximum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +INSERT INTO t VALUES (128,50); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +INSERT INTO t VALUES (129,60); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +DROP TABLE t; +# Inserting a negative value that goes below minimum allowed value for an +# Auto Increment column (negative minimum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); +INSERT INTO t VALUES (-129,50); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +INSERT INTO t VALUES (-130,60); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 40 +-127 30 +1 10 +2 20 +DROP TABLE t; +# Updating the partition table with a negative Auto Increment value +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +4 40 +5 50 +DROP TABLE t; +# Updating the partition table with a value that crosses the upper limits +# on both the positive and the negative side. +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = 130 where c1 = 127; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = -140 where c1 = 126; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 30 +1 10 +2 20 +127 40 +DROP TABLE t; +############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_memory.result b/mysql-test/suite/parts/r/partition_auto_increment_memory.result index f4d783825f4..6e3b990dc0f 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result @@ -851,3 +851,194 @@ c1 4 5 DROP TABLE t1; +############################################################################# +# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +############################################################################## +# Inserting negative autoincrement values into a partition table (partitions >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DROP TABLE t; +# Reading from a partition table (partitions >= 2 ) after inserting a negative +# value into the auto increment column +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-2 -20 +1 30 +DROP TABLE t; +# Inserting negative auto increment value into a partition table (partitions >= 2) +# auto increment value > 2. +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-4 -20 +1 30 +2 40 +DROP TABLE t; +# Inserting -1 into autoincrement column of a partition table (partition >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +DROP TABLE t; +# Deleting from an auto increment table after inserting negative values +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DELETE FROM t WHERE c1 > 1; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +DROP TABLE t; +# Inserting a positive value that exceeds maximum allowed value for an +# Auto Increment column (positive maximum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +INSERT INTO t VALUES (128,50); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +INSERT INTO t VALUES (129,60); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +DROP TABLE t; +# Inserting a negative value that goes below minimum allowed value for an +# Auto Increment column (negative minimum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); +INSERT INTO t VALUES (-129,50); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +INSERT INTO t VALUES (-130,60); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 40 +-127 30 +1 10 +2 20 +DROP TABLE t; +# Updating the partition table with a negative Auto Increment value +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +4 40 +5 50 +DROP TABLE t; +# Updating the partition table with a value that crosses the upper limits +# on both the positive and the negative side. +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = 130 where c1 = 127; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = -140 where c1 = 126; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 30 +1 10 +2 20 +127 40 +DROP TABLE t; +############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result index 6abf08b68a0..047b974f0a3 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result @@ -870,3 +870,194 @@ c1 4 5 DROP TABLE t1; +############################################################################# +# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +############################################################################## +# Inserting negative autoincrement values into a partition table (partitions >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DROP TABLE t; +# Reading from a partition table (partitions >= 2 ) after inserting a negative +# value into the auto increment column +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-2 -20 +1 30 +DROP TABLE t; +# Inserting negative auto increment value into a partition table (partitions >= 2) +# auto increment value > 2. +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-4 -20 +1 30 +2 40 +DROP TABLE t; +# Inserting -1 into autoincrement column of a partition table (partition >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +DROP TABLE t; +# Deleting from an auto increment table after inserting negative values +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DELETE FROM t WHERE c1 > 1; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +DROP TABLE t; +# Inserting a positive value that exceeds maximum allowed value for an +# Auto Increment column (positive maximum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +INSERT INTO t VALUES (128,50); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +INSERT INTO t VALUES (129,60); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +DROP TABLE t; +# Inserting a negative value that goes below minimum allowed value for an +# Auto Increment column (negative minimum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); +INSERT INTO t VALUES (-129,50); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +INSERT INTO t VALUES (-130,60); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 40 +-127 30 +1 10 +2 20 +DROP TABLE t; +# Updating the partition table with a negative Auto Increment value +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +4 40 +5 50 +DROP TABLE t; +# Updating the partition table with a value that crosses the upper limits +# on both the positive and the negative side. +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='MyISAM' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = 130 where c1 = 127; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = -140 where c1 = 126; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 30 +1 10 +2 20 +127 40 +DROP TABLE t; +############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result index 5a1c5b06b36..317669be7ad 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result @@ -846,3 +846,194 @@ c1 4 5 DROP TABLE t1; +############################################################################# +# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +############################################################################## +# Inserting negative autoincrement values into a partition table (partitions >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DROP TABLE t; +# Reading from a partition table (partitions >= 2 ) after inserting a negative +# value into the auto increment column +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-2 -20 +1 30 +DROP TABLE t; +# Inserting negative auto increment value into a partition table (partitions >= 2) +# auto increment value > 2. +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2; +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-4 -20 +1 30 +2 40 +DROP TABLE t; +# Inserting -1 into autoincrement column of a partition table (partition >= 4) +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +DROP TABLE t; +# Deleting from an auto increment table after inserting negative values +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +2 20 +3 30 +4 40 +DELETE FROM t WHERE c1 > 1; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-3 -20 +-1 -10 +1 10 +DROP TABLE t; +# Inserting a positive value that exceeds maximum allowed value for an +# Auto Increment column (positive maximum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +INSERT INTO t VALUES (128,50); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +INSERT INTO t VALUES (129,60); +ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +DROP TABLE t; +# Inserting a negative value that goes below minimum allowed value for an +# Auto Increment column (negative minimum) +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); +INSERT INTO t VALUES (-129,50); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +INSERT INTO t VALUES (-130,60); +ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 40 +-127 30 +1 10 +2 20 +DROP TABLE t; +# Updating the partition table with a negative Auto Increment value +CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-1 -10 +1 10 +2 20 +3 30 +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); +UPDATE t SET c1 = -6 WHERE c1 = 2; +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-6 20 +-1 -10 +1 10 +3 30 +4 40 +5 50 +DROP TABLE t; +# Updating the partition table with a value that crosses the upper limits +# on both the positive and the negative side. +CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), +c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = 130 where c1 = 127; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +1 10 +2 20 +126 30 +127 40 +UPDATE t SET c1 = -140 where c1 = 126; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t ORDER BY c1 ASC; +c1 c2 +-128 30 +1 10 +2 20 +127 40 +DROP TABLE t; +############################################################################## diff --git a/mysql-test/suite/parts/t/partition_auto_increment_archive.test b/mysql-test/suite/parts/t/partition_auto_increment_archive.test index fb09557204f..e99a0b23b36 100644 --- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test +++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test @@ -30,6 +30,9 @@ let $skip_delete= 1; let $skip_truncate= 1; let $skip_update= 1; let $only_ai_pk= 1; +# Bug#45823 Assertion failure in file row/row0mysql.c line 1386 +# Archive does not handle negative autoincrement values correctly +let $skip_negative_auto_inc= 1; ##### Storage engine to be tested let $engine= 'Archive'; diff --git a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test index 64cd96c6173..f92dc33f263 100644 --- a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test +++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test @@ -25,6 +25,9 @@ #------------------------------------------------------------------------------# # Engine specific settings and requirements --source include/have_blackhole.inc +# Bug#45823 Assertion failure in file row/row0mysql.c line 1386 +# Blackhole does not handle negative autoincrement values correctly +let $skip_negative_auto_inc= 1; ##### Storage engine to be tested let $engine= 'Blackhole'; diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment.result b/mysql-test/suite/rpl/r/rpl_auto_increment.result index 2a4c3a09361..fdd94264041 100644 --- a/mysql-test/suite/rpl/r/rpl_auto_increment.result +++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result @@ -244,3 +244,71 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 drop table t1; +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; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb; +CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +SET SQL_MODE=''; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t2 VALUES(NULL); +SELECT * FROM t1; +id +1 +SELECT * FROM t2; +id +1 +INSERT INTO t1 VALUES(); +INSERT INTO t2 VALUES(); +SELECT * FROM t1; +id +1 +2 +SELECT * FROM t2; +id +1 +2 +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +id +1 +2 +3 +SELECT * FROM t2; +id +1 +2 +3 +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +id +0 +1 +2 +3 +SELECT * FROM t2; +id +0 +1 +2 +3 +INSERT INTO t1 VALUES(4); +INSERT INTO t2 VALUES(4); +FLUSH LOGS; +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +DROP TABLE t1; +DROP TABLE t2; +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +DROP TABLE t1; +DROP TABLE t2; +SET SQL_MODE=''; diff --git a/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result b/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result index 8339e77d3a0..6792a701577 100644 --- a/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result +++ b/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result @@ -4,21 +4,20 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -create database if not exists mysqltest1; -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP TABLE IF EXISTS mysqltest1.t1; -CREATE TABLE IF NOT EXISTS mysqltest1.t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=InnoDB; -CREATE TABLE IF NOT EXISTS mysqltest1.t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=InnoDB; -CREATE PROCEDURE mysqltest1.p1() +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=InnoDB; +CREATE PROCEDURE p1() BEGIN DECLARE done INT DEFAULT 0; DECLARE spa CHAR(16); DECLARE spb INT; DECLARE cur1 CURSOR FOR SELECT name, (YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) -FROM mysqltest1.t1; +FROM t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; SET AUTOCOMMIT=0; @@ -26,21 +25,20 @@ REPEAT FETCH cur1 INTO spa, spb; IF NOT done THEN START TRANSACTION; -INSERT INTO mysqltest1.t2 VALUES (spa,spb); +INSERT INTO t2 VALUES (spa,spb); COMMIT; END IF; UNTIL done END REPEAT; SET AUTOCOMMIT=1; CLOSE cur1; END| -CREATE PROCEDURE mysqltest1.p2() +CREATE PROCEDURE p2() BEGIN -INSERT INTO mysqltest1.t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); +INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); END| -CALL mysqltest1.p2(); -CALL mysqltest1.p1(); -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t1; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP DATABASE mysqltest1; +CALL p2(); +CALL p1(); +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP PROCEDURE p2; diff --git a/mysql-test/suite/rpl/t/rpl_do_grant.test b/mysql-test/suite/rpl/t/rpl_do_grant.test index 806de780086..fc793c03649 100644 --- a/mysql-test/suite/rpl/t/rpl_do_grant.test +++ b/mysql-test/suite/rpl/t/rpl_do_grant.test @@ -129,6 +129,9 @@ CREATE DATABASE bug42217_db; GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost' IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION; +-- sync_slave_with_master +-- connection master + connect (create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,); connect (create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,); diff --git a/mysql-test/suite/rpl/t/rpl_drop_temp.test b/mysql-test/suite/rpl/t/rpl_drop_temp.test index df162d3b244..7827e16e45f 100644 --- a/mysql-test/suite/rpl/t/rpl_drop_temp.test +++ b/mysql-test/suite/rpl/t/rpl_drop_temp.test @@ -23,6 +23,8 @@ disconnect con_temp; --source include/wait_until_disconnected.inc connection master; +-- let $wait_binlog_event= DROP +-- source include/wait_for_binlog_event.inc sync_slave_with_master; connection slave; diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_sp006.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_sp006.result index 482d43c8f10..047402f826f 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_sp006.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_sp006.result @@ -4,21 +4,20 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -create database if not exists mysqltest1; -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP TABLE IF EXISTS mysqltest1.t1; -CREATE TABLE IF NOT EXISTS mysqltest1.t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=NDBCLUSTER; -CREATE TABLE IF NOT EXISTS mysqltest1.t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=NDBCLUSTER; -CREATE PROCEDURE mysqltest1.p1() +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=NDBCLUSTER; +CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=NDBCLUSTER; +CREATE PROCEDURE p1() BEGIN DECLARE done INT DEFAULT 0; DECLARE spa CHAR(16); DECLARE spb INT; DECLARE cur1 CURSOR FOR SELECT name, (YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) -FROM mysqltest1.t1; +FROM t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; SET AUTOCOMMIT=0; @@ -26,21 +25,20 @@ REPEAT FETCH cur1 INTO spa, spb; IF NOT done THEN START TRANSACTION; -INSERT INTO mysqltest1.t2 VALUES (spa,spb); +INSERT INTO t2 VALUES (spa,spb); COMMIT; END IF; UNTIL done END REPEAT; SET AUTOCOMMIT=1; CLOSE cur1; END| -CREATE PROCEDURE mysqltest1.p2() +CREATE PROCEDURE p2() BEGIN -INSERT INTO mysqltest1.t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); +INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); END| -CALL mysqltest1.p2(); -CALL mysqltest1.p1(); -DROP PROCEDURE IF EXISTS mysqltest1.p1; -DROP PROCEDURE IF EXISTS mysqltest1.p2; -DROP TABLE IF EXISTS mysqltest1.t1; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP DATABASE mysqltest1; +CALL p2(); +CALL p1(); +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP PROCEDURE p2; diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 7139d95ab49..aad3d19455d 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1599,3 +1599,27 @@ INSERT INTO t1 VALUES (NULL, NULL),(NULL, NULL); FLUSH TABLE t1; SELECT * FROM t1 ORDER BY a; DROP TABLE t1; + +# +# BUG#29203 - archive tables have weird values in show table status +# +CREATE TABLE t1(a INT, b BLOB) ENGINE=archive; +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM + INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +INSERT INTO t1 VALUES(1, 'sampleblob1'),(2, 'sampleblob2'); +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM + INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DROP TABLE t1; + +# +# BUG#46961 - archive engine loses rows during self joining select! +# +SET @save_join_buffer_size= @@join_buffer_size; +SET @@join_buffer_size= 8228; +CREATE TABLE t1(a CHAR(255)) ENGINE=archive; +INSERT INTO t1 VALUES('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), + ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), + ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +SELECT COUNT(t1.a) FROM t1, t1 a, t1 b, t1 c, t1 d, t1 e; +DROP TABLE t1; +SET @@join_buffer_size= @save_join_buffer_size; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 75ad6c056c1..5ffa1b93929 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1198,6 +1198,23 @@ CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) DROP TABLE t1, t2; +--echo # +--echo # BUG#46384 - mysqld segfault when trying to create table with same +--echo # name as existing view +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); + +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); + +CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE v1 AS SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1,t2; --echo End of 5.0 tests diff --git a/mysql-test/t/ctype_gbk_binlog.test b/mysql-test/t/ctype_gbk_binlog.test index a8f653d1b1e..e4c1bee19af 100644 --- a/mysql-test/t/ctype_gbk_binlog.test +++ b/mysql-test/t/ctype_gbk_binlog.test @@ -1,6 +1,7 @@ -- source include/have_binlog_format_mixed_or_statement.inc -- source include/have_gbk.inc +RESET MASTER; SET NAMES gbk; --character_set gbk diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index a77d1136840..bf4c23562cf 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -573,4 +573,44 @@ SELECT DISTINCT a, b, d, c FROM t1; DROP TABLE t1; +--echo # +--echo # Bug #46159: simple query that never returns +--echo # + +# Set max_heap_table_size to the minimum value so that GROUP BY table in the +# SELECT query below gets converted to MyISAM +SET @old_max_heap_table_size = @@max_heap_table_size; +SET @@max_heap_table_size = 16384; + +# Set sort_buffer_size to the mininum value so that remove_duplicates() calls +# remove_dup_with_compare() +SET @old_sort_buffer_size = @@sort_buffer_size; +SET @@sort_buffer_size = 32804; + +CREATE TABLE t1(c1 int, c2 VARCHAR(20)); +INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); +# Now we just need to pad the table with random data so we have enough unique +# values to force conversion of the GROUP BY table to MyISAM +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; + +# First rows of the GROUP BY table that will be processed by +# remove_dup_with_compare() +SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; + +# The actual test case +SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; + +# Cleanup + +DROP TABLE t1; +SET @@sort_buffer_size = @old_sort_buffer_size; +SET @@max_heap_table_size = @old_max_heap_table_size; + --echo End of 5.1 tests diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 755e126baf2..18f1145a25d 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -135,6 +135,17 @@ EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a); DROP TABLE t1; +# +# Bug#45989 memory leak after explain encounters an error in the query +# +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); +--error ER_BAD_FIELD_ERROR +EXPLAIN SELECT DISTINCT 1 FROM t1, + (SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 + WHERE t1.a = d1.a; +DROP TABLE t1; # End of 5.0 tests. diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 7cb7f7f72d2..66b9eabd385 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1291,6 +1291,19 @@ INSERT INTO t1 VALUES ('aaaaaaaa'); SELECT LOAD_FILE(a) FROM t1; DROP TABLE t1; +# +# Bug#46815 CONCAT_WS returning wrong data +# +CREATE TABLE t1 (f2 VARCHAR(20)); +CREATE TABLE t2 (f2 VARCHAR(20)); + +INSERT INTO t1 VALUES ('MIN'),('MAX'); +INSERT INTO t2 VALUES ('LOAD'); + +SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; + +DROP TABLE t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt new file mode 100644 index 00000000000..272f91d629c --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt @@ -0,0 +1,2 @@ +--lower-case-table-names=2 +--tmpdir=$MYSQLTEST_VARDIR/tmp/MixedCase diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh new file mode 100644 index 00000000000..95c26e3aa02 --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh @@ -0,0 +1,6 @@ +# This test requires a non-lowercase tmpdir directory on a case-sensitive +# filesystem. + +d="$MYSQLTEST_VARDIR/tmp/MixedCase" +test -d "$d" || mkdir "$d" +rm -f "$d"/* diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb.test b/mysql-test/t/lowercase_mixed_tmpdir_innodb.test new file mode 100644 index 00000000000..e3b9b7b2a32 --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb.test @@ -0,0 +1,12 @@ +--source include/have_lowercase2.inc +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (id int) engine=InnoDB; +insert into t1 values (1); +create temporary table t2 engine=InnoDB select * from t1; +drop temporary table t2; +drop table t1; diff --git a/mysql-test/t/mysqlbinlog-cp932.test b/mysql-test/t/mysqlbinlog-cp932.test index a7055bfc8ca..2a210bea0e0 100644 --- a/mysql-test/t/mysqlbinlog-cp932.test +++ b/mysql-test/t/mysqlbinlog-cp932.test @@ -5,8 +5,9 @@ -- source include/have_cp932.inc -- source include/have_log_bin.inc +RESET MASTER; + # Bug#16217 (mysql client did not know how not switch its internal charset) -flush logs; create table t3 (f text character set utf8); create table t4 (f text character set cp932); --exec $MYSQL --default-character-set=utf8 test -e "insert into t3 values(_utf8'ソ')" @@ -14,7 +15,7 @@ create table t4 (f text character set cp932); flush logs; rename table t3 to t03, t4 to t04; let $MYSQLD_DATADIR= `select @@datadir`; ---exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000002 | $MYSQL --default-character-set=utf8 +--exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001 | $MYSQL --default-character-set=utf8 # original and recovered data must be equal select HEX(f) from t03; select HEX(f) from t3; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 2abbceffbb0..aba28b76f01 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -6,6 +6,23 @@ drop table if exists t1; --enable_warnings # +# Bug#47029: Crash when reorganize partition with subpartition +# +create table t1 (a int not null, + b datetime not null, + primary key (a,b)) +engine=innodb +partition by range (to_days(b)) +subpartition by hash (a) +subpartitions 2 +( partition p0 values less than (to_days('2009-01-01')), + partition p1 values less than (to_days('2009-02-01')), + partition p2 values less than (to_days('2009-03-01')), + partition p3 values less than maxvalue); +alter table t1 reorganize partition p1,p2 into +( partition p2 values less than (to_days('2009-03-01'))); +drop table t1; +# # Bug#40595: Non-matching rows not released with READ-COMMITTED on tables # with partitions CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB @@ -270,3 +287,15 @@ PARTITION BY RANGE (int_column) (PARTITION p1 VALUES LESS THAN (5)); show create table t1; drop table t1; + +# +# BUG#46483 - drop table of partitioned table may leave extraneous file +# Note: was only repeatable with InnoDB plugin +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB + PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); +CREATE INDEX i1 ON t1 (a); +DROP TABLE t1; +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# Before the fix it should show extra file like #sql-2405_2.par +--list_files $MYSQLD_DATADIR/test/ * diff --git a/mysql-test/t/ps_not_windows.test b/mysql-test/t/ps_not_windows.test index 6d85f737b32..0ab08b59f1e 100644 --- a/mysql-test/t/ps_not_windows.test +++ b/mysql-test/t/ps_not_windows.test @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# requires dynamic loading +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 5eeac457958..44c4556340e 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8242,6 +8242,28 @@ while ($tab_count) DROP PROCEDURE p1; DROP TABLE t1; + +--echo # +--echo # Bug #46629: Item_in_subselect::val_int(): Assertion `0' +--echo # on subquery inside a SP +--echo # +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT, b INT PRIMARY KEY); + +DELIMITER |; +CREATE PROCEDURE p1 () +BEGIN + SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); +END| +DELIMITER ;| +--error ER_BAD_FIELD_ERROR +CALL p1; +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1, t2; + + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test new file mode 100644 index 00000000000..ff4cdf3c439 --- /dev/null +++ b/mysql-test/t/subselect4.test @@ -0,0 +1,32 @@ +# General purpose bug fix tests go here : subselect.test too large + + +--echo # +--echo # Bug #46791: Assertion failed:(table->key_read==0),function unknown +--echo # function,file sql_base.cc +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,1),(2,2); +CREATE TABLE t3 LIKE t1; + +--echo # should have 1 impossible where and 2 dependent subqueries +EXPLAIN +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); + +--echo # should not crash the next statement +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); + +--echo # should not crash: the crash is caused by the previous statement +SELECT 1; + +DROP TABLE t1,t2,t3; + + +--echo End of 5.0 tests. diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index e9ae1a31079..7bf252040e5 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -436,4 +436,16 @@ SELECT * FROM t2 WHERE a = sequence(); DROP FUNCTION sequence; DROP TABLE t1,t2; +--echo # +--echo # Bug#46259: 5.0.83 -> 5.1.36, query doesn't work +--echo # +CREATE TABLE t1 ( a INT ); + +INSERT INTO t1 VALUES (1), (2), (3); + +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 ); +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); + +DROP TABLE t1; + --echo End of 5.0 tests. diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 12421170eba..176f320e390 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -225,4 +225,11 @@ insert into t2 values(@q); drop table t1, t2; +# +# Bug#42364 SHOW ERRORS returns empty resultset after dropping non existent table +# +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; +SHOW ERRORS; + --echo End of 5.0 tests |