事象内容
Spider データベース で テーブルを確認すると、テーブルが存在しないといった下記エラーが発生する。
ERROR 1932 (42S02): Table ‘example_db.USER’ doesn’t exist in engine
[root@spider-1 lib]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db 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 MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 29 Server version: 10.3.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [example_db]> MariaDB [example_db]> SELECT * FROM USER; ERROR 1932 (42S02): Table 'example_db.USER' doesn't exist in engine MariaDB [example_db]> MariaDB [example_db]> SELECT * FROM USER; ERROR 1932 (42S02): Table 'example_db.USER' doesn't exist in engine MariaDB [example_db]> quit Bye [root@spider-1 lib]#
/var/lib/mysql1/spider-1.novalocal.err のログを確認すると、テーブルのオープンが失敗しているといった下記エラーが出力されている。
[ERROR] Failed to open table example_db/USER#P#p1.
[root@spider-1 ~]# tail -F /var/lib/mysql1/spider-1.novalocal.err 2018-11-23 11:51:09 0 [Note] Plugin 'FEEDBACK' is disabled. 2018-11-23 11:51:09 0 [Note] Server socket created on IP: '::'. 2018-11-23 11:51:09 0 [Note] Reading of all Master_info entries succeded 2018-11-23 11:51:09 0 [Note] Added new Master_info '' to hash table 2018-11-23 11:51:09 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.3.10-MariaDB' socket: '/var/lib/mysql1/mysql.sock' port: 3306 MariaDB Server 2018-11-23 11:54:07 29 [ERROR] Failed to open table example_db/USER#P#p1.
水平分散（シャーディング）しているデータノードのデータベースではテーブルは正常に参照できている。
■ データノード（ mysql2 ）でのデータベースのテーブル参照
[root@spider-1 lib]# mysql -uroot --socket=/var/lib/mysql3/mysql.sock example_db 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 MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [example_db]> MariaDB [example_db]> SELECT * FROM USER; +---------+--------+---------+-------+------+ | user_id | name | profile | level | exp | +---------+--------+---------+-------+------+ | 1 | NAME01 | PROF01 | 1 | 101 | | 3 | NAME03 | PROF03 | 3 | 103 | | 5 | NAME05 | PROF05 | 5 | 105 | | 7 | NAME07 | PROF07 | 7 | 107 | | 9 | NAME09 | PROF09 | 9 | 109 | | 11 | NAME11 | PROF11 | 11 | 111 | | 13 | NAME13 | PROF13 | 13 | 113 | | 15 | NAME15 | PROF15 | 15 | 115 | | 17 | NAME17 | PROF17 | 17 | 117 | | 19 | NAME19 | PROF19 | 19 | 119 | +---------+--------+---------+-------+------+ 10 rows in set (0.036 sec) MariaDB [example_db]> quit Bye [root@spider-1 lib]#
■ データノード（ mysql3 ）でのデータベースのテーブル参照
[root@spider-1 lib]# mysql -uroot --socket=/var/lib/mysql2/mysql.sock example_db 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 MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [example_db]> MariaDB [example_db]> SELECT * FROM USER; +---------+--------+---------+-------+------+ | user_id | name | profile | level | exp | +---------+--------+---------+-------+------+ | 2 | NAME02 | PROF02 | 2 | 102 | | 4 | NAME04 | PROF04 | 4 | 104 | | 6 | NAME06 | PROF06 | 6 | 106 | | 8 | NAME08 | PROF08 | 8 | 108 | | 10 | NAME10 | PROF10 | 10 | 110 | | 12 | NAME12 | PROF12 | 12 | 112 | | 14 | NAME14 | PROF14 | 14 | 114 | | 16 | NAME16 | PROF16 | 16 | 116 | | 18 | NAME18 | PROF18 | 18 | 118 | | 20 | NAME10 | PROF20 | 20 | 120 | +---------+--------+---------+-------+------+ 10 rows in set (0.009 sec) MariaDB [example_db]> MariaDB [example_db]> quit Bye [root@spider-1 lib]#
解決方法
Spider ノードのデータベースを一旦削除し、新規で再作成しなおすことで解決されます。データベースのデータ自体は、データノード（ mysql2、mysql3 ）に存在しているため、Spider ノードのデータベースを削除し再作成してもデータは消去されません。
MariaDB の多重起動を停止する。
[root@spider-1 ~]# mysqld_multi stop [root@spider-1 ~]# [root@spider-1 ~]# mysqld_multi report Reporting MariaDB servers MariaDB server from group: mysqld1 is not running MariaDB server from group: mysqld2 is not running MariaDB server from group: mysqld3 is not running [root@spider-1 ~]#
Spider ノードのデータベースディレクトリを削除する。
[root@spider-1 ~]# rm -rf /var/lib/mysql1 [root@spider-1 ~]#
Spider ノードのデータベースの初期化をする。
[root@spider-1 ~]# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql Installing MariaDB/MySQL system tables in '/var/lib/mysql1' ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/bin/mysqladmin' -u root password 'new-password' '/usr/bin/mysqladmin' -u root -h spider-1.novalocal password 'new-password' Alternatively you can run: '/usr/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql1' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: [root@spider-1 ~]#
Spider ノードのデータベースのアクセス権と所有権を mysql ユーザーに変更する。
[root@spider-1 ~]# chown -R mysql:mysql /var/lib/mysql1 [root@spider-1 ~]#
MariaDB の多重起動を起動する。
[root@spider-1 ~]# mysqld_multi start
[root@spider-1 ~]# mysqld_multi report Reporting MariaDB servers MariaDB server from group: mysqld1 is running MariaDB server from group: mysqld2 is running MariaDB server from group: mysqld3 is running [root@spider-1 ~]#
Spider ノードのデータベースに接続する。
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Spider ノードのデータベースに対して、Spider ストレージエンジンの定義をインストールする。
MariaDB [(none)]> source /usr/share/mysql/install_spider.sql Query OK, 0 rows affected (0.035 sec) Query OK, 0 rows affected (0.017 sec) Query OK, 0 rows affected (0.013 sec) Query OK, 0 rows affected (0.013 sec) Query OK, 0 rows affected (0.013 sec) Query OK, 0 rows affected (0.011 sec) Query OK, 0 rows affected (0.010 sec) Query OK, 0 rows affected (0.012 sec) Query OK, 0 rows affected (0.010 sec) Query OK, 0 rows affected, 1 warning (0.006 sec) Query OK, 0 rows affected, 1 warning (0.002 sec) Query OK, 0 rows affected (0.012 sec) Query OK, 0 rows affected (0.021 sec) Query OK, 23 rows affected (0.131 sec) Query OK, 0 rows affected (0.010 sec) Query OK, 0 rows affected (0.008 sec) Query OK, 0 rows affected, 1 warning (0.002 sec) Query OK, 0 rows affected (0.011 sec) Empty set (0.011 sec) Empty set (0.011 sec) Empty set (0.045 sec) Empty set (0.045 sec) Empty set (0.064 sec) Empty set (0.064 sec) Empty set (0.064 sec) Empty set (0.064 sec) Empty set (0.064 sec) Query OK, 0 rows affected (0.067 sec) Query OK, 0 rows affected (0.009 sec) MariaDB [(none)]>
サーバーのストレージエンジンに関するステータス情報を表示し、Spider ストレージエンジンがサポートされている（YES）ことが確認する。
MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | SPIDER | YES | Spider storage engine | YES | YES | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.021 sec) MariaDB [(none)]>
Spider で水平分散（シャーディング）する外部データラッパ（ mysql2、mysql3 ）を作成する。
MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3307); Query OK, 0 rows affected (0.016 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3308); Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]>
外部データラップが作成されていることを確認する。
MariaDB [(none)]> SELECT * FROM mysql.servers; +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | mysqld2 | 127.0.0.1 | | spider | spider | 3307 | | mysql | | | mysqld3 | 127.0.0.1 | | spider | spider | 3308 | | mysql | | +-------------+-----------+----+----------+----------+------+--------+---------+-------+ 2 rows in set (0.021 sec) MariaDB [(none)]> quit Bye [root@spider-1 ~]#
Spider ノードで データベースを作成する。
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;" [root@spider-1 ~]#
Spider ノードで作成したデータベース（ example_db ）にアクセスし、USERテーブルを user_id のハッシュにより mysql2 と mysql3 の2つのサーバーにシャーディングするための設定をする。
[root@spider-1 lib]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [example_db]>
MariaDB [example_db]> CREATE TABLE `USER` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`level` int(10) unsigned DEFAULT NULL,
`exp` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE = SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(user_id) (
PARTITION p1 comment 'server "mysqld2", table "USER"',
PARTITION p2 comment 'server "mysqld3", table "USER"'
);
Query OK, 0 rows affected (0.056 sec)
MariaDB [example_db]> テーブル参照できるようになりました。
MariaDB [example_db]> SELECT * FROM USER;
+---------+--------+---------+-------+------+
| user_id | name | profile | level | exp |
+---------+--------+---------+-------+------+
| 2 | NAME02 | PROF02 | 2 | 102 |
| 4 | NAME04 | PROF04 | 4 | 104 |
| 6 | NAME06 | PROF06 | 6 | 106 |
| 8 | NAME08 | PROF08 | 8 | 108 |
| 10 | NAME10 | PROF10 | 10 | 110 |
| 12 | NAME12 | PROF12 | 12 | 112 |
| 14 | NAME14 | PROF14 | 14 | 114 |
| 16 | NAME16 | PROF16 | 16 | 116 |
| 18 | NAME18 | PROF18 | 18 | 118 |
| 20 | NAME10 | PROF20 | 20 | 120 |
| 1 | NAME01 | PROF01 | 1 | 101 |
| 3 | NAME03 | PROF03 | 3 | 103 |
| 5 | NAME05 | PROF05 | 5 | 105 |
| 7 | NAME07 | PROF07 | 7 | 107 |
| 9 | NAME09 | PROF09 | 9 | 109 |
| 11 | NAME11 | PROF11 | 11 | 111 |
| 13 | NAME13 | PROF13 | 13 | 113 |
| 15 | NAME15 | PROF15 | 15 | 115 |
| 17 | NAME17 | PROF17 | 17 | 117 |
| 19 | NAME19 | PROF19 | 19 | 119 |
+---------+--------+---------+-------+------+
20 rows in set (0.151 sec)
MariaDB [example_db]>