mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-03-16 13:28:41 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/storage/spider/mysql-test/spider/t/spider_fixes.test
Lena Startseva 9854fb6fa7 MDEV-31003: Second execution for ps-protocol 
This patch adds for "--ps-protocol" second execution
of queries "SELECT".
Also in this patch it is added ability to disable/enable
(--disable_ps2_protocol/--enable_ps2_protocol) second
execution for "--ps-prototocol" in testcases.
2023-07-26 17:15:00 +07:00

1425 lines
31 KiB
Text
Raw Permalink Blame History

# This test tests for Spider's bug fixes
source include/have_log_bin.inc;
--disable_warnings
--disable_query_log
--disable_result_log
--source test_init.inc
--source slave_test_init.inc
--enable_result_log
--enable_query_log
--echo
--echo drop and create databases
--connection master_1
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
if ($USE_REPLICATION)
{
--connection slave1_1
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
}
if ($USE_CHILD_GROUP2)
{
--connection child2_1
DROP DATABASE IF EXISTS auto_test_remote;
CREATE DATABASE auto_test_remote;
USE auto_test_remote;
--connection child2_2
DROP DATABASE IF EXISTS auto_test_remote2;
CREATE DATABASE auto_test_remote2;
USE auto_test_remote2;
}
--enable_warnings
--echo
--echo test select 1
--connection master_1
SELECT 1;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
SELECT 1;
--connection child2_2
SELECT 1;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo create table and insert
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_DROP_TABLES;
echo CHILD2_1_CREATE_TABLES;
}
--disable_warnings
eval $CHILD2_1_DROP_TABLES;
--enable_warnings
eval $CHILD2_1_CREATE_TABLES;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS tb_l;
--enable_warnings
--disable_query_log
echo CREATE TABLE tb_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE2 MASTER_1_CHARSET2;
eval CREATE TABLE tb_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) $MASTER_1_ENGINE2 $MASTER_1_CHARSET2;
--enable_query_log
INSERT INTO tb_l (a, b, c) VALUES
(1, 'a', '2008-08-01 10:21:39'),
(2, 'b', '2000-01-01 00:00:00'),
(3, 'e', '2007-06-04 20:03:11'),
(4, 'd', '2003-11-30 05:01:03'),
(5, 'c', '2001-12-31 23:59:59');
--disable_warnings
DROP TABLE IF EXISTS ta_l;
--enable_warnings
--disable_query_log
echo CREATE TABLE ta_l (
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
eval CREATE TABLE ta_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
--enable_query_log
INSERT INTO ta_l SELECT a, b, c FROM tb_l;
--echo
--echo 2.13
--echo select table with "order by desc" and "<"
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l
WHERE a < 5 ORDER BY a DESC LIMIT 3;
--enable_ps2_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo select table with "order by desc" and "<="
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l
WHERE a <= 5 ORDER BY a DESC LIMIT 3;
--enable_ps2_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo 2.14
--echo update table with range scan and split_read
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
UPDATE ta_l SET c = '2000-02-02 00:00:00' WHERE a > 1;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
--echo
--echo 2.15
--echo select table with range scan
TRUNCATE TABLE ta_l;
--disable_warnings
DROP TABLE IF EXISTS ta_l;
--enable_warnings
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_DROP_TABLES6;
echo CHILD2_1_CREATE_TABLES6;
}
--disable_warnings
eval $CHILD2_1_DROP_TABLES6;
--enable_warnings
eval $CHILD2_1_CREATE_TABLES6;
--enable_query_log
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_result_log
}
}
--connection master_1
--disable_query_log
echo CREATE TABLE ta_l (
a int(11) NOT NULL DEFAULT '0',
b char(1) DEFAULT NULL,
c datetime DEFAULT NULL,
PRIMARY KEY (a, b, c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1;
eval CREATE TABLE ta_l (
a int(11) NOT NULL DEFAULT '0',
b char(1) DEFAULT NULL,
c datetime DEFAULT NULL,
PRIMARY KEY (a, b, c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT5_2_1;
--enable_query_log
INSERT INTO ta_l SELECT a, b, c FROM tb_l;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b >= 'b'
AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b > 'b'
AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a >= 4 AND b = 'd'
AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a > 4 AND b = 'c'
AND c = '2001-12-31 23:59:59';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b <= 'd'
AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b < 'e'
AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a <= 4 AND b = 'b'
AND c = '2000-01-01 00:00:00';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a < 4 AND b = 'b'
AND c = '2000-01-01 00:00:00';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b >= 'b'
AND b <= 'd' AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b > 'b'
AND b < 'e' AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a <= 4 AND a >= 1
AND b >= 'b' AND c = '2003-11-30 05:01:03';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a < 4 AND a > 1
AND b >= 'b' AND c = '2000-01-01 00:00:00';
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo 2.16
--echo auto_increment insert with trigger
if ($HAVE_TRIGGER)
{
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_result_log
}
--disable_query_log
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
echo CHILD2_1_DROP_TABLES4;
echo CHILD2_1_CREATE_TABLES4;
}
--disable_warnings
eval $CHILD2_1_DROP_TABLES4;
--enable_warnings
eval $CHILD2_1_CREATE_TABLES4;
--enable_query_log
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_result_log
}
}
--connection master_1
--disable_query_log
echo CREATE TABLE ta_l_auto_inc (
a INT AUTO_INCREMENT,
b CHAR(1) DEFAULT 'c',
c DATETIME DEFAULT '1999-10-10 10:10:10',
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT3_2_1;
eval CREATE TABLE ta_l_auto_inc (
a INT AUTO_INCREMENT,
b CHAR(1) DEFAULT 'c',
c DATETIME DEFAULT '1999-10-10 10:10:10',
PRIMARY KEY(a)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT3_2_1;
echo CREATE TABLE tc_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE2 MASTER_1_CHARSET2;
eval CREATE TABLE tc_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) $MASTER_1_ENGINE2 $MASTER_1_CHARSET2;
--enable_query_log
--eval CREATE TRIGGER ins_ta_l_auto_inc AFTER INSERT ON ta_l_auto_inc FOR EACH ROW BEGIN INSERT INTO tc_l (a, b, c) VALUES (NEW.a, NEW.b, NEW.c); END;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
eval $CHILD2_1_SELECT_TABLES;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
INSERT INTO ta_l_auto_inc (a, b, c) VALUES
(NULL, 's', '2008-12-31 20:59:59');
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %';
}
eval $CHILD2_1_SELECT_TABLES4;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM tc_l ORDER BY a;
}
--echo
--echo 2.17
--echo engine-condition-pushdown with "or" and joining
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l WHERE a = 1 OR a IN (SELECT a FROM tb_l);
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo 2.23
--echo index merge
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_result_log
}
--disable_query_log
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
echo CHILD2_1_DROP_TABLES5;
echo CHILD2_1_CREATE_TABLES5;
}
--disable_warnings
eval $CHILD2_1_DROP_TABLES5;
--enable_warnings
eval $CHILD2_1_CREATE_TABLES5;
--enable_query_log
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_result_log
}
}
--connection master_1
--disable_query_log
echo CREATE TABLE ta_l_int (
a INT AUTO_INCREMENT,
b INT DEFAULT 10,
c INT DEFAULT 11,
PRIMARY KEY(a),
KEY idx1(b),
KEY idx2(c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1;
eval CREATE TABLE ta_l_int (
a INT AUTO_INCREMENT,
b INT DEFAULT 10,
c INT DEFAULT 11,
PRIMARY KEY(a),
KEY idx1(b),
KEY idx2(c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT4_2_1;
--enable_query_log
INSERT INTO ta_l_int (a, b, c) VALUES (1, 2, 3);
INSERT INTO ta_l_int (a, b, c) SELECT a + 1, b + 1, c + 1 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 2, b + 2, c + 2 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 4, b + 4, c + 4 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 8, b + 8, c + 8 FROM ta_l_int;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l_int force index(primary, idx1, idx2)
WHERE a = 5 OR b = 5 OR c = 5 ORDER BY a;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo 2.24
--echo index scan update without PK
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS ta_l_int;
--enable_warnings
--disable_query_log
echo CREATE TABLE ta_l_int (
a INT NOT NULL,
b INT DEFAULT 10,
c INT DEFAULT 11,
KEY idx1(b),
KEY idx2(c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1;
if ($MASTER_1_NEEDPK)
{
--error ER_REQUIRES_PRIMARY_KEY
eval CREATE TABLE ta_l_int (
a INT NOT NULL,
b INT DEFAULT 10,
c INT DEFAULT 11,
KEY idx1(b),
KEY idx2(c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT4_2_1;
eval CREATE TABLE ta_l_int (
a INT NOT NULL,
b INT DEFAULT 10,
c INT DEFAULT 11,
PRIMARY KEY(a),
KEY idx1(b),
KEY idx2(c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT4_2_1;
}
if (!$MASTER_1_NEEDPK)
{
eval CREATE TABLE ta_l_int (
a INT NOT NULL,
b INT DEFAULT 10,
c INT DEFAULT 11,
KEY idx1(b),
KEY idx2(c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT4_2_1;
}
--enable_query_log
SELECT a, b, c FROM ta_l_int ORDER BY a;
INSERT INTO ta_l_int (a, b, c) VALUES (0, 2, 3);
INSERT INTO ta_l_int (a, b, c) VALUES (18, 2, 3);
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
UPDATE ta_l_int SET c = 4 WHERE b = 2;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
SELECT argument FROM mysql.general_log WHERE argument LIKE '%update %';
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SELECT a, b, c FROM ta_l_int ORDER BY a;
--echo
--echo 2.25
--echo direct order limit
--connection master_1
eval $MASTER_1_CHECK_DIRECT_ORDER_LIMIT_STATUS;
--disable_ps2_protocol
SELECT a, b, c FROM ta_l_int ORDER BY a LIMIT 3;
--enable_ps2_protocol
eval $MASTER_1_CHECK_DIRECT_ORDER_LIMIT_STATUS;
--echo
--echo 2.26
--echo lock tables
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_DROP_LOCK_TABLES1;
echo CHILD2_1_DROP_LOCK_TABLES2;
echo CHILD2_1_CREATE_LOCK_TABLES1;
echo CHILD2_1_CREATE_LOCK_TABLES2;
}
--disable_warnings
eval $CHILD2_1_DROP_LOCK_TABLES1;
eval $CHILD2_1_DROP_LOCK_TABLES2;
--enable_warnings
eval $CHILD2_1_CREATE_LOCK_TABLES1;
eval $CHILD2_1_CREATE_LOCK_TABLES2;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
--connection child2_2
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_2_DROP_LOCK_TABLES1;
echo CHILD2_2_DROP_LOCK_TABLES2;
echo CHILD2_2_CREATE_LOCK_TABLES1;
echo CHILD2_2_CREATE_LOCK_TABLES2;
}
--disable_warnings
eval $CHILD2_2_DROP_LOCK_TABLES1;
eval $CHILD2_2_DROP_LOCK_TABLES2;
--enable_warnings
eval $CHILD2_2_CREATE_LOCK_TABLES1;
eval $CHILD2_2_CREATE_LOCK_TABLES2;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings
--disable_query_log
echo CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_LOCK1;
eval CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_LOCK1;
echo CREATE TABLE t2 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_LOCK2;
eval CREATE TABLE t2 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_LOCK2;
--enable_query_log
LOCK TABLES t1 READ, t2 READ;
UNLOCK TABLES;
--echo
--echo auto_increment
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_DROP_INCREMENT_TABLES1;
echo CHILD2_1_CREATE_INCREMENT_TABLES1;
echo CHILD2_1_AUTO_INCREMENT_INCREMENT2;
echo CHILD2_1_AUTO_INCREMENT_OFFSET2;
}
--disable_warnings
eval $CHILD2_1_DROP_INCREMENT_TABLES1;
--enable_warnings
eval $CHILD2_1_CREATE_INCREMENT_TABLES1;
eval $CHILD2_1_AUTO_INCREMENT_INCREMENT2;
eval $CHILD2_1_AUTO_INCREMENT_OFFSET2;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
if ($USE_REPLICATION)
{
save_master_pos;
--connection slave1_1
sync_with_master;
--connection master_1
--disable_query_log
SET SESSION sql_log_bin= 0;
--enable_query_log
}
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--disable_query_log
echo CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_INCREMENT1_1;
echo MASTER_1_AUTO_INCREMENT_INCREMENT2;
echo MASTER_1_AUTO_INCREMENT_OFFSET2;
eval CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_INCREMENT1_1;
eval $MASTER_1_AUTO_INCREMENT_INCREMENT2;
eval $MASTER_1_AUTO_INCREMENT_OFFSET2;
if ($USE_REPLICATION)
{
SET SESSION sql_log_bin= 1;
--connection slave1_1
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
echo CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_1;
eval CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) $SLAVE1_1_ENGINE $SLAVE1_1_CHARSET $SLAVE1_1_COMMENT_INCREMENT1_1;
--connection master_1
}
--enable_query_log
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 () VALUES (),(),(),();
SELECT LAST_INSERT_ID();
SELECT id FROM t1 ORDER BY id;
SET INSERT_ID=5000;
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 (id) VALUES (10000);
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
INSERT INTO t1 (id) VALUES (1000);
SELECT LAST_INSERT_ID();
SELECT MAX(id) FROM t1;
if ($USE_REPLICATION)
{
save_master_pos;
--connection slave1_1
sync_with_master;
SELECT id FROM t1 ORDER BY id;
--connection master_1
--disable_query_log
SET SESSION sql_log_bin= 0;
--enable_query_log
}
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %';
}
eval $CHILD2_1_SELECT_INCREMENT_TABLES1;
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_AUTO_INCREMENT_INCREMENT1;
echo CHILD2_1_AUTO_INCREMENT_OFFSET1;
}
eval $CHILD2_1_AUTO_INCREMENT_INCREMENT1;
eval $CHILD2_1_AUTO_INCREMENT_OFFSET1;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo read only
let $MASTER_1_ENGINE_IS_SPIDER=
`SELECT IF('$MASTER_1_ENGINE_TYPE' = 'Spider' OR
'$MASTER_1_HIDDEN_ENGINE_TYPE' = 'Spider', 1, 0)`;
if ($MASTER_1_ENGINE_IS_SPIDER)
{
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--disable_query_log
echo CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_READONLY1_1;
eval CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_READONLY1_1;
--let $MIN_VAL= `SELECT MIN(id) FROM t1`
--enable_query_log
SELECT id FROM t1 ORDER BY id;
--error 12518
INSERT INTO t1 (id) VALUES (1);
--error 12518
eval UPDATE t1 SET id = 4 WHERE id = $MIN_VAL;
--error 12518
eval DELETE FROM t1 WHERE id = $MIN_VAL;
--error 12518
DELETE FROM t1;
--error 12518
TRUNCATE t1;
}
if (!$MASTER_1_ENGINE_IS_SPIDER)
{
--echo skipped
}
--echo
--echo 2.27
--echo error mode
if ($MASTER_1_ENGINE_IS_SPIDER)
{
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--disable_query_log
echo CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_ERROR_MODE1_1;
eval CREATE TABLE t1 (
id int(11) NOT NULL,
PRIMARY KEY (id)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_ERROR_MODE1_1;
--enable_query_log
SELECT id FROM t1 ORDER BY id;
INSERT INTO t1 (id) VALUES (1);
DELETE FROM t1;
TRUNCATE t1;
}
if (!$MASTER_1_ENGINE_IS_SPIDER)
{
--echo skipped
}
--echo
--echo 3.0
--echo is null
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_result_log
}
--disable_query_log
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
echo CHILD2_1_DROP_TEXT_KEY_TABLES1;
echo CHILD2_1_CREATE_TEXT_KEY_TABLES1;
}
--disable_warnings
eval $CHILD2_1_DROP_TEXT_KEY_TABLES1;
--enable_warnings
eval $CHILD2_1_CREATE_TEXT_KEY_TABLES1;
--enable_query_log
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_result_log
}
}
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--disable_query_log
echo CREATE TABLE t1 (
a VARCHAR(255),
b VARCHAR(255),
c VARCHAR(255),
KEY idx1(a,b),
KEY idx2(b),
PRIMARY KEY(c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_TEXT_KEY1_1;
eval CREATE TABLE t1 (
a VARCHAR(255),
b VARCHAR(255),
c VARCHAR(255),
KEY idx1(a,b),
KEY idx2(b),
PRIMARY KEY(c)
) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_TEXT_KEY1_1;
--enable_query_log
insert into t1 values (null, null, '2048');
insert into t1 values ('1', '1', '1');
insert into t1 select a + 1, b + 1, c + 1 from t1;
insert into t1 select a + 2, b + 2, c + 2 from t1;
insert into t1 select a + 4, b + 4, c + 4 from t1;
insert into t1 select a + 8, b + 8, c + 8 from t1;
insert into t1 select a + 16, b + 16, c + 16 from t1;
insert into t1 select a + 32, b + 32, c + 32 from t1;
insert into t1 select a + 64, b + 64, c + 64 from t1;
insert into t1 select a + 128, b + 128, c + 128 from t1;
insert into t1 select a + 256, b + 256, c + 256 from t1;
insert into t1 select a + 512, b + 512, c + 512 from t1;
flush tables;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
select a from t1 where a is null order by a limit 30;
select b from t1 where b is null order by b limit 30;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
eval $CHILD2_1_SELECT_TEXT_PK_TABLES1;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo direct_order_limit
--connection master_1
TRUNCATE TABLE t1;
insert into t1 values ('1', '1', '1');
insert into t1 select a + 1, b + 1, c + 1 from t1;
insert into t1 select a + 2, b + 2, c + 2 from t1;
insert into t1 select a + 4, b + 4, c + 4 from t1;
insert into t1 select a + 8, b + 8, c + 8 from t1;
insert into t1 select a + 16, b + 16, c + 16 from t1;
insert into t1 select a, b + 32, c + 32 from t1;
insert into t1 select a, b + 64, c + 64 from t1;
insert into t1 select a, b + 128, c + 128 from t1;
flush tables;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
select a, b, c from t1 where a = '10' and b <> '100' order by c desc limit 5;
select a, c from t1 where a = '10' order by b desc limit 5;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
}
eval $CHILD2_1_SELECT_TEXT_PK_TABLES1;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo deinit
--disable_warnings
--connection master_1
DROP DATABASE IF EXISTS auto_test_local;
if ($USE_REPLICATION)
{
--connection slave1_1
DROP DATABASE IF EXISTS auto_test_local;
}
if ($USE_CHILD_GROUP2)
{
--connection child2_1
DROP DATABASE IF EXISTS auto_test_remote;
--connection child2_2
DROP DATABASE IF EXISTS auto_test_remote2;
}
--disable_query_log
--disable_result_log
--source slave_test_deinit.inc
--source test_deinit.inc
--enable_result_log
--enable_query_log
--enable_warnings
--echo
--echo end of test
Reference in a new issue View git blame Copy permalink