| Bug #75201 | strange error message while ddl on a table with memcached configured | ||
|---|---|---|---|
| Submitted: | 13 Dec 2014 12:57 | Modified: | 14 Dec 2014 7:28 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Memcached | Severity: | S3 (Non-critical) |
| Version: | 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | memcached | ||
[13 Dec 2014 13:04]
zhai weixiang
First ddl didn't clear #sql2-196d-434.ibd and lead to failure of the second ddl.
[14 Dec 2014 7:28]
MySQL Verification Team
Hello Zhai, Thank you for the report and test case. Thanks, Umesh
[14 Dec 2014 7:28]
MySQL Verification Team
// Build
[root@cluster-repo server]# md5sum mysql-5.7.5-m15-linux-glibc2.5-x86_64.tar.gz
b4c61a681b8a2d85527e7e2d2a595c84 mysql-5.7.5-m15-linux-glibc2.5-x86_64.tar.gz
// Cutsom
use test;
CREATE TABLE `t1` (
`pk` varchar(20) NOT NULL,
`val1` int(11) DEFAULT NULL,
`val2` int(11) DEFAULT NULL,
`c3` bigint(20) DEFAULT NULL,
`c4` bigint(20) DEFAULT NULL,
`c5` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into innodb_memcache.containers
(name,db_schema,db_table,key_columns,value_columns,flags,cas_column,
expire_time_column,unique_idx_name_on_key)
values
('tt1','test','t1','pk','val1,val2',
'c3','c4','c5','PRIMARY');
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.5 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.5-m15 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> CREATE TABLE `t1` (
-> `pk` varchar(20) NOT NULL,
-> `val1` int(11) DEFAULT NULL,
-> `val2` int(11) DEFAULT NULL,
-> `c3` bigint(20) DEFAULT NULL,
-> `c4` bigint(20) DEFAULT NULL,
-> `c5` bigint(20) DEFAULT NULL,
-> PRIMARY KEY (`pk`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into innodb_memcache.containers
-> (name,db_schema,db_table,key_columns,value_columns,flags,cas_column,
-> expire_time_column,unique_idx_name_on_key)
-> values
-> ('tt1','test','t1','pk','val1,val2',
-> 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.00 sec)
mysql> select * from innodb_memcache.containers where name like 'tt1';
+------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+
| tt1 | test | t1 | pk | val1,val2 | c3 | c4 | c5 | PRIMARY |
+------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values('pk1',1,2,2,4,0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-----+------+------+------+------+------+
| pk | val1 | val2 | c3 | c4 | c5 |
+-----+------+------+------+------+------+
| pk1 | 1 | 2 | 2 | 4 | 0 |
+-----+------+------+------+------+------+
1 row in set (0.00 sec)
// Telnet session
[root@cluster-repo ~]# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
..
get @@tt1
VALUE @@tt1 0 7
test/t1
END
get pk1
VALUE pk1 2 3
1|2
END
// CLI session
mysql> alter table t1 modify column val1 bigint;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify column val2 bigint;
ERROR 1050 (42S01): Table './test/#sql2-2b9e-9' already exists
mysql> show variables like 'datadir';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| datadir | /tmp/bug/ |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> \! ls -l /tmp/bug/test
total 316
-rw-rw---- 1 ushastry ushastry 65 Dec 15 18:00 db.opt
-rw-rw---- 1 ushastry ushastry 8660 Dec 15 18:00 demo_test.frm
-rw-rw---- 1 ushastry ushastry 98304 Dec 15 18:05 demo_test.ibd
-rw-rw---- 1 ushastry ushastry 98304 Dec 16 14:28 #sql2-2b9e-9.ibd
-rw-rw---- 1 ushastry ushastry 8694 Dec 16 14:29 t1.frm
-rw-rw---- 1 ushastry ushastry 98304 Dec 16 14:29 t1.ibd
Description: Configure the table bellow to use memcached root@test 08:49:52>show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `pk` varchar(20) NOT NULL, `val1` int(11) DEFAULT NULL, `val2` int(11) DEFAULT NULL, `c3` bigint(20) DEFAULT NULL, `c4` bigint(20) DEFAULT NULL, `c5` bigint(20) DEFAULT NULL, PRIMARY KEY (`pk`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@innodb_memcache 08:56:25>select * from containers where name like 'tt1'; +------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+ | name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key | +------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+ | tt1 | test | t1 | pk | val1|val2 | c3 | c4 | c5 | PRIMARY | +------+-----------+----------+-------------+---------------+-------+------------+--------------------+------------------------+ 1 row in set (0.00 sec) root@test 08:51:54>select * from t1; +-----+------+------+------+------+------+ | pk | val1 | val2 | c3 | c4 | c5 | +-----+------+------+------+------+------+ | pk1 | 1 | 2 | 2 | 4 | 0 | +-----+------+------+------+------+------+ 1 row in set (0.00 sec) then connect from telnet: $telnet 127.0.0.1 13407 Trying 127.0.0.1... Connected to 127.0.0.1. Escape character is '^]'. get @@tt1 VALUE @@tt1 0 7 test/t1 END get pk1 VALUE pk1 2 3 1|2 END go back to MySQL client: root@test 08:52:55>alter table t1 modify column val1 bigint; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 root@test 08:53:57>alter table t1 modify column val2 bigint; ERROR 1050 (42S01): Table './test/#sql2-196d-434' already exists So the second ddl is failed and first one succeed. How to repeat: Described above Suggested fix: I don't know