| Bug #68974 | NOT NULL columns prevent Memcached plugin from writing new records | ||
|---|---|---|---|
| Submitted: | 16 Apr 2013 16:31 | Modified: | 3 Jan 2014 7:50 |
| Reporter: | Aaron Morand | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Memcached | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 GA, 5.6.11 GA, 5.6.12 | OS: | Linux (CentOS 5.6, Xubuntu 12.04) |
| Assigned to: | CPU Architecture: | Any | |
[3 May 2013 11:53]
Erlend Dahl
I can't reproduce this behaviour on 5.6.11, nor on 5.6.11:
$ bin/mysql -e "SELECT VERSION();"
+--------------+
| VERSION() |
+--------------+
| 5.6.11-debug |
+--------------+
$ memccp --version
memcp v1.0
$ memccat --version
memcat v1.0
#
# Reset innodb memcached setup
#
$ bin/mysql -e 'DROP DATABASE innodb_memcache;'
$ bin/mysql -e 'DROP TABLE test.demo_test;';
$ cat share/innodb_memcached_config.sql | bin/mysql
#
$ bin/mysql -e "DELETE FROM innodb_memcache.containers WHERE name='aaa';"
$ bin/mysql -e 'CREATE TABLE test.demo_test1 (c1 VARCHAR(32), c2 VARCHAR(1024), c3 INT, c4 BIGINT UNSIGNED, c5 INT, c6 INT NOT NULL, primary key(c1)) ENGINE = INNODB;'
#
$ bin/mysql -e 'INSERT INTO innodb_memcache.containers VALUES ("aaa", "test", "demo_test1","c1", "c2", "c3", "c4", "c5", "PRIMARY");'
$ touch TEST1
$ memccp --verbose --debug --servers=localhost --expire=20 TEST1
op: set
source file: TEST1
length: 0
key: TEST1
flags: 0
expires: 32
Calling memcached_free()
$ memccat --verbose --debug --servers=localhost TEST1
key: TEST1
flags: 0length: 0
value:
$ bin/mysql -e "DELETE FROM innodb_memcache.containers WHERE name='aaa';"
$ bin/mysql -e "DROP TABLE test.demo_test1;"
#
$ bin/mysql -e 'CREATE TABLE test.demo_test2 (c1 VARCHAR(32), c2 VARCHAR(1024), c3 INT, c4 BIGINT UNSIGNED, c5 INT, c6 INT, primary key(c1)) ENGINE = INNODB;'
#
$ bin/mysql -e 'INSERT INTO innodb_memcache.containers VALUES ("aaa", "test", "demo_test2","c1", "c2", "c3", "c4", "c5", "PRIMARY");'
#
$ touch TEST2
$ memccp --verbose --debug --servers=localhost --expire=20 TEST2
op: set
source file: TEST2
length: 0
key: TEST2
flags: 0
expires: 32
Calling memcached_free()
$ memccat --verbose --debug --servers=localhost TEST2
key: TEST2
flags: 0length: 0
value:
#
$ bin/mysql -e "DROP TABLE test.demo_test2;"
#
[3 May 2013 11:54]
Erlend Dahl
"on 5.6.10, nor on 5.6.11", I mean. Is there any obvious difference in our setup?
[3 May 2013 12:55]
Aaron Morand
You have to restart MySQL after making changes to the innodb_memcache.containers table or they won't take effect.
[3 May 2013 13:24]
Erlend Dahl
I tried that, but to no avail. I will check with the implementor.
[3 May 2013 14:44]
Aaron Morand
I just re-verified my test case, here it is in complete detail.
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use innodb_memcache;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete from containers;
Query OK, 1 row affected (0.04 sec)
mysql> drop database test;
Query OK, 1 row affected (0.13 sec)
mysql> exit
Bye
$ /etc/init.d/mysql restart
Shutting down MySQL.... [ OK ]
Starting MySQL.... [ OK ]
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE demo_test (c1 VARCHAR(32),
-> c2 VARCHAR(1024),
-> c3 INT,
-> c4 BIGINT UNSIGNED,
-> c5 INT,
-> c6 INT NOT NULL,
-> primary key(c1))
-> ENGINE = INNODB;
Query OK, 0 rows affected (0.47 sec)
mysql> use innodb_memcache;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO containers VALUES ("aaa", "test", "demo_test",
-> "c1", "c2", "c3", "c4", "c5",
-> "PRIMARY");
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
$ /etc/init.d/mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL..... [ OK ]
$ memcat --verbose --debug --servers=localhost TEST1
memcat: TEST1 not found
$ touch TEST1
$ memcp --verbose --debug --servers=localhost --expire=20 TEST1
op: set
source file: TEST1
length: 0
key: TEST1
flags: 0
expires: 32
memcp: TEST1: memcache error NOT STORED
$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter table demo_test modify c6 int;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc demo_test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1 | varchar(32) | NO | PRI | | |
| c2 | varchar(1024) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
| c4 | bigint(20) unsigned | YES | | NULL | |
| c5 | int(11) | YES | | NULL | |
| c6 | int(11) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> exit
Bye
$ memcat --verbose --debug --servers=localhost TEST1
memcat: TEST1 not found
$ memcp --verbose --debug --servers=localhost --expire=20 TEST1
op: set
source file: TEST1
length: 0
key: TEST1
flags: 0
expires: 32
$
[13 May 2013 16:49]
Aaron Morand
I just verified this same behaviour on my Xubuntu 12.04 workstation with MySQL 5.6.11.
[24 Jul 2013 18:18]
MySQL Verification Team
Hello Aaron, Thank you for the report. Thanks, Umesh
[24 Jul 2013 18:18]
MySQL Verification Team
How to repeat:
--------------
drop table if exists test.demo_test;
drop database if exists innodb_memcache;
source share/innodb_memcached_config.sql;
uninstall plugin daemon_memcached;
install plugin daemon_memcached soname "libmemcached.so";
use test;
// earlier step create demo test but it doesn't have c6 columns..so as not to mess.. using IF NOT EXISTS
CREATE TABLE if not exists demo_test (c1 VARCHAR(32),c2 VARCHAR(1024),c3 INT,c4 BIGINT UNSIGNED,c5 INT,c6 INT NOT NULL,primary key(c1)) ENGINE = INNODB;
ALTER TABLE demo_test add column `c6` INT NOT NULL;
use innodb_memcache;
INSERT IGNORE INTO containers VALUES ("aaa", "test", "demo_test","c1", "c2", "c3", "c4", "c5", "PRIMARY");
uninstall plugin daemon_memcached;
install plugin daemon_memcached soname "libmemcached.so";
// Check from shell> prompt if everything okay
shell>memcat --verbose --debug --servers=localhost TEST1
shell>touch TEST1
shell>memcp --verbose --debug --servers=localhost --expire=20 TEST1
// on mysql> prompt
use test;
ALTER TABLE demo_test drop column `c6`;
// Check from shell> prompt if everything okay
shell>memcat --verbose --debug --servers=localhost TEST1
shell>touch TEST1
shell>memcp --verbose --debug --servers=localhost --expire=20 TEST1
[24 Jul 2013 18:19]
MySQL Verification Team
// MySQL prompt
ushastry@ushastry mysql-5.6.12-release]$ bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table if exists test.demo_test;
Query OK, 0 rows affected (0.08 sec)
mysql> drop database if exists innodb_memcache;
Query OK, 3 rows affected (0.11 sec)
mysql> source /home/ushastry/mybuilds/mysql-5.6.12-release/share/innodb_memcached_config.sql;
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.06 sec)
Query OK, 1 row affected (0.01 sec)
mysql> uninstall plugin daemon_memcached;
Query OK, 0 rows affected (2.00 sec)
mysql> install plugin daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE if not exists demo_test (c1 VARCHAR(32),c2 VARCHAR(1024),c3 INT,c4 BIGINT UNSIGNED,c5 INT,c6 INT NOT NULL,primary key(c1)) ENGINE = INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> ALTER TABLE demo_test add column `c6` INT NOT NULL;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> use innodb_memcache;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT IGNORE INTO containers VALUES ("aaa", "test", "demo_test","c1", "c2", "c3", "c4", "c5", "PRIMARY");
Query OK, 0 rows affected (0.01 sec)
mysql> uninstall plugin daemon_memcached;
Query OK, 0 rows affected (2.00 sec)
mysql> install plugin daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.00 sec)
mysql> use test;ALTER TABLE demo_test drop column `c6`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
// shell>
[ushastry@ushastry mysql-5.6.12-release]$ memcat --verbose --debug --servers=localhost TEST1
[ushastry@ushastry mysql-5.6.12-release]$ touch TEST1
[ushastry@ushastry mysql-5.6.12-release]$ memcp --verbose --debug --servers=localhost --expire=20 TEST1
op: set
source file: TEST1
length: 0
key: TEST1
flags: 0
expires: 20
memcp: TEST1: memcache error NOT STORED
[ushastry@ushastry mysql-5.6.12-release]$ memcat --verbose --debug --servers=localhost TEST1
[ushastry@ushastry mysql-5.6.12-release]$ touch TEST1
[ushastry@ushastry mysql-5.6.12-release]$ memcp --verbose --debug --servers=localhost --expire=20 TEST1
op: set
source file: TEST1
length: 0
key: TEST1
flags: 0
expires: 20
[ushastry@ushastry mysql-5.6.12-release]$
[13 Dec 2013 13:22]
Daniel Price
The NOT NULL issue has been addressed in the troubleshooting section of the InnoDB memcached documentation. Thank you for the bug report: http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-troubleshoot.html http://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-troubleshoot.html
[13 Dec 2013 14:32]
Aaron Morand
Ok, but this does not help when I want to have timestamp columns for telling when records were created and/or updated. For example : Create Table: CREATE TABLE `memstash` ( `k` varchar(255) NOT NULL, `v` longtext, `flags` int(11) DEFAULT NULL, `cas_column` bigint(20) unsigned DEFAULT NULL, `expires` int(10) unsigned DEFAULT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`k`), KEY `v` (`v`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; This would work as intended on any other InnoDB table. How do I make it work with the Memcached plugin?
[3 Jan 2014 7:50]
Erlend Dahl
We have discussed this internally. What you suggest is a reasonable feature request, though not necessarily easy to implement, since InnoDB has no knowledge about default values (it's a server layer constuct). In any case I have filed the FR separately as bug#71285 and I will close this report again.
Description: Having NOT NULL columns in an InnoDB table used by the Memcached plugin prevents memcached from setting new key/value pairs. How to repeat: 1) Create a table to be used with the Memcached plugin with a NOT NULL column : CREATE TABLE demo_test (c1 VARCHAR(32), c2 VARCHAR(1024), c3 INT, c4 BIGINT UNSIGNED, c5 INT, c6 INT NOT NULL, primary key(c1)) ENGINE = INNODB; OR Modify an existing table : ALTER TABLE demo_test add column `c6` INT NOT NULL; 2) Add the containers record use innodb_memcache; INSERT INTO containers VALUES ("aaa", "test", "demo_test", "c1", "c2", "c3", "c4", "c5", "PRIMARY"); 3) Restart MySQL to ensure everything is in place 4) Attempt get & set operations on keys $ memcat --verbose --debug --servers=localhost TEST1 memcat: TEST1 not found $ touch TEST1 $ memcp --verbose --debug --servers=localhost --expire=20 TEST1 op: set source file: TEST1 length: 0 key: TEST1 flags: 0 expires: 32 memcp: TEST1: memcache error NOT STORED $ memcat --verbose --debug --servers=localhost TEST1 memcat: TEST1 not found 5) Drop the NOT NULL column and repeat Step 4 ALTER TABLE demo_test drop column `c6`; $ memcat --verbose --debug --servers=localhost TEST1 memcat: TEST1 not found $ memcp --verbose --debug --servers=localhost --expire=20 TEST1 op: set source file: TEST1 length: 0 key: TEST1 flags: 0 expires: 32 $ memcat --verbose --debug --servers=localhost TEST1 key: TEST1 flags: 0 length: 0 value: Suggested fix: The default value for columns should not prevent the Memcached plugin from adding new records.