Description:
When trying to activate the memcached plugin, the innodb_memcached_config.sql configuration script will fail in the following cases:
- innodb_memcache schema already exists
- demo_test table already exists
- test values already exist
Running the innodb_memcached_config.sql script is a one-time operation but script should not be failing as there are cases where it may be ran more than once i.e.
- automation against a db node to ensure that plugin is activated or reactivate if initial activation was incomplete for any reason
- enable on a slave after activating on the master
How to repeat:
- Run the script more than once on the same host
- Run on a slave after running on its master
Suggested fix:
- The "create database innodb_memcache" should be replaced with "create database if not exists innodb_memcache"
- The "CREATE TABLE demo_test" should be replaced with "CREATE TABLE IF NOT EXISTS demo_test"
- The INSERT statements should be replaced with "INSERT IGNORE"
--- 5.7 2020-04-20 15:04:46.000000000 +0300
+++ 5.7.patch 2020-04-20 15:12:56.000000000 +0300
@@ -1,4 +1,4 @@
-create database innodb_memcache;
+create database if not exists innodb_memcache;
use innodb_memcache;
@@ -74,14 +74,14 @@
-- PRIMARY -> use primary key to search
-- ------------------------------------------------------------------------
-INSERT INTO containers VALUES ("aaa", "test", "demo_test",
+INSERT IGNORE INTO containers VALUES ("aaa", "test", "demo_test",
"c1", "c2", "c3", "c4", "c5", "PRIMARY");
-INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
+INSERT IGNORE INTO cache_policies VALUES("cache_policy", "innodb_only",
"innodb_only", "innodb_only", "innodb_only");
-INSERT INTO config_options VALUES("separator", "|");
-INSERT INTO config_options VALUES("table_map_delimiter", ".");
+INSERT IGNORE INTO config_options VALUES("separator", "|");
+INSERT IGNORE INTO config_options VALUES("table_map_delimiter", ".");
CREATE DATABASE IF NOT EXISTS test;
USE test
@@ -94,9 +94,9 @@
-- CAS (c4) is a 64 bits integer, per memcached define
-- Exp (c5) is again a 32 bits integer
-- ------------------------------------------------------------------------
-CREATE TABLE demo_test (c1 VARCHAR(32),
+CREATE TABLE IF NOT EXISTS demo_test (c1 VARCHAR(32),
c2 VARCHAR(1024),
c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
ENGINE = INNODB;
-INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);
+INSERT IGNORE INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);
Description: When trying to activate the memcached plugin, the innodb_memcached_config.sql configuration script will fail in the following cases: - innodb_memcache schema already exists - demo_test table already exists - test values already exist Running the innodb_memcached_config.sql script is a one-time operation but script should not be failing as there are cases where it may be ran more than once i.e. - automation against a db node to ensure that plugin is activated or reactivate if initial activation was incomplete for any reason - enable on a slave after activating on the master How to repeat: - Run the script more than once on the same host - Run on a slave after running on its master Suggested fix: - The "create database innodb_memcache" should be replaced with "create database if not exists innodb_memcache" - The "CREATE TABLE demo_test" should be replaced with "CREATE TABLE IF NOT EXISTS demo_test" - The INSERT statements should be replaced with "INSERT IGNORE" --- 5.7 2020-04-20 15:04:46.000000000 +0300 +++ 5.7.patch 2020-04-20 15:12:56.000000000 +0300 @@ -1,4 +1,4 @@ -create database innodb_memcache; +create database if not exists innodb_memcache; use innodb_memcache; @@ -74,14 +74,14 @@ -- PRIMARY -> use primary key to search -- ------------------------------------------------------------------------ -INSERT INTO containers VALUES ("aaa", "test", "demo_test", +INSERT IGNORE INTO containers VALUES ("aaa", "test", "demo_test", "c1", "c2", "c3", "c4", "c5", "PRIMARY"); -INSERT INTO cache_policies VALUES("cache_policy", "innodb_only", +INSERT IGNORE INTO cache_policies VALUES("cache_policy", "innodb_only", "innodb_only", "innodb_only", "innodb_only"); -INSERT INTO config_options VALUES("separator", "|"); -INSERT INTO config_options VALUES("table_map_delimiter", "."); +INSERT IGNORE INTO config_options VALUES("separator", "|"); +INSERT IGNORE INTO config_options VALUES("table_map_delimiter", "."); CREATE DATABASE IF NOT EXISTS test; USE test @@ -94,9 +94,9 @@ -- CAS (c4) is a 64 bits integer, per memcached define -- Exp (c5) is again a 32 bits integer -- ------------------------------------------------------------------------ -CREATE TABLE demo_test (c1 VARCHAR(32), +CREATE TABLE IF NOT EXISTS demo_test (c1 VARCHAR(32), c2 VARCHAR(1024), c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1)) ENGINE = INNODB; -INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0); +INSERT IGNORE INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);