1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
|
--echo # Case 1. Test with CREATE TABLES
CREATE TABLE t1(c1 VARCHAR(32),
c2 VARCHAR(1024),
c3 TINYBLOB,
c4 BLOB,
c5 MEDIUMBLOB,
c6 LONGBLOB,
c7 TEXT,
c8 INT,
c9 FLOAT,
c10 DOUBLE,
primary key(c1),
KEY idx1(c4(20)),
UNIQUE KEY idx2(c6(50)))
CHARSET latin1 ENGINE = INNODB;
perl;
use strict;
use warnings;
my $dest_dir = $ENV{'MYSQLTEST_VARDIR'};
open(DATA, ">$dest_dir/tmp/create_table.sql") or die "Couldn't open file
$dest_dir/tmp/create_table.sql, $!";
print DATA "CREATE TABLE t2(";
my $cols = 999;
for (my $i=1; $i <= $cols; $i++) {
if ($i == 1) {
print DATA "c1 INT PRIMARY KEY";
} else {
print DATA "c$i INT";
}
if ($i != $cols) {
print DATA ", ";
}
}
print DATA ");";
close DATA;
EOF
--echo # Create table t2 with large schema (huge number of columns)
--exec $MYSQL -uroot test <$MYSQLTEST_VARDIR/tmp/create_table.sql
--echo # Create table with small schema
CREATE TABLE t3(c1 VARCHAR(32),
c2 VARCHAR(1024),
PRIMARY KEY(c1))
ENGINE = INNODB;
--echo # Create tables with constraints
CREATE TABLE t4 (c1 INT PRIMARY KEY,
c2 BLOB)
ENGINE = INNODB;
CREATE TABLE t5 (c1 INT PRIMARY KEY,
c2 INT,
FOREIGN KEY (c2) REFERENCES t4(c1) ON UPDATE CASCADE)
ENGINE = INNODB;
--echo # Create table with spatial index
CREATE TABLE t7(c1 INT PRIMARY KEY,
c2 INT,
c3 CHAR(255),
c4 GEOMETRY NOT NULL SRID 0,
SPATIAL INDEX(c4))
ENGINE = INNODB;
# Tables must exist before plugin can be started!
--source ../include/load_daemon_memcached_expecting_success.inc
let TABLE_NAME = t1;
let MEMCACHED_TABLE_MAPPING = desc_t1;
--source ../include/sdi_insert_get_row.inc
let TABLE_NAME = t2;
let MEMCACHED_TABLE_MAPPING = desc_t2;
--source ../include/sdi_insert_get_row.inc
let TABLE_NAME = t3;
let MEMCACHED_TABLE_MAPPING = desc_t3;
--source ../include/sdi_insert_get_row.inc
let TABLE_NAME = t4;
let MEMCACHED_TABLE_MAPPING = desc_t4;
--source ../include/sdi_insert_get_row.inc
let TABLE_NAME = t5;
let MEMCACHED_TABLE_MAPPING = desc_t5;
--source ../include/sdi_insert_get_row.inc
let TABLE_NAME = t7;
let MEMCACHED_TABLE_MAPPING = desc_t7;
--source ../include/sdi_insert_get_row.inc
--echo # 2. Test with alter table
# Alter columns
ALTER TABLE t1 ADD COLUMN c11 INT;
let TABLE_NAME = t1;
let MEMCACHED_TABLE_MAPPING = desc_t1;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t3 ADD COLUMN c3 INT;
let TABLE_NAME = t3;
let MEMCACHED_TABLE_MAPPING = desc_t3;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t2 DROP COLUMN c100;
ALTER TABLE t2 DROP COLUMN c101;
ALTER TABLE t2 DROP COLUMN c102;
ALTER TABLE t2 DROP COLUMN c103;
ALTER TABLE t2 DROP COLUMN c104;
let TABLE_NAME = t2;
let MEMCACHED_TABLE_MAPPING = desc_t2;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t7 MODIFY c2 BIGINT NOT NULL;
let TABLE_NAME = t7;
let MEMCACHED_TABLE_MAPPING = desc_t7;
--source ../include/sdi_insert_get_row.inc
# Alter constraints
ALTER TABLE t3 ADD FOREIGN KEY(c3) REFERENCES t2(c1);
SHOW CREATE TABLE t3;
let TABLE_NAME = t3;
let MEMCACHED_TABLE_MAPPING = desc_t3;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t5 DROP FOREIGN KEY t5_ibfk_1;
let TABLE_NAME = t5;
let MEMCACHED_TABLE_MAPPING = desc_t5;
--source ../include/sdi_insert_get_row.inc
# Alter indexes
ALTER TABLE t7 ADD INDEX idx3(c2,c3(50));
let TABLE_NAME = t7;
let MEMCACHED_TABLE_MAPPING = desc_t7;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c1, ADD c1 BIGINT PRIMARY
KEY;
let TABLE_NAME = t1;
let MEMCACHED_TABLE_MAPPING = desc_t1;
--source ../include/sdi_insert_get_row.inc
# Alter Fulltext Indexes
ALTER TABLE t1 ADD FULLTEXT INDEX(c2), ALGORITHM=INPLACE;
let TABLE_NAME = t1;
let MEMCACHED_TABLE_MAPPING = desc_t1;
--source ../include/sdi_insert_get_row.inc
ALTER TABLE t1 DROP INDEX c2;
let TABLE_NAME = t1;
let MEMCACHED_TABLE_MAPPING = desc_t1;
--source ../include/sdi_insert_get_row.inc
# Test with SDI after a TRUNCATE TABLE operation
TRUNCATE TABLE t7;
let TABLE_NAME = t7;
let MEMCACHED_TABLE_MAPPING = desc_t7;
--source ../include/sdi_insert_get_row.inc
DROP TABLE t1, t3, t2, t4, t5, t7;
# Stop plugin before innodb_memcached configuration
UNINSTALL PLUGIN daemon_memcached;
--remove_file $MYSQLTEST_VARDIR/tmp/create_table.sql
|