Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.14
Description
If you create a server object in MariaDB, and then create a Spider table that depends on that server, and then you have recreate the server for whatever reason, Spider continues to use the old definition.
e.g. if I do the following on the remote server:
CREATE DATABASE spider_test;
|
|
GRANT ALL PRIVILEGES ON spider_test.* TO spider_test@'%' IDENTIFIED BY 'password';
|
|
USE spider_test;
|
|
CREATE TABLE local_tab (
|
id int auto_increment primary key,
|
str varchar(50)
|
) ENGINE=InnoDB;
|
And then the following on the Spider server:
CREATE SERVER srv1
|
FOREIGN DATA WRAPPER mysql
|
OPTIONS(
|
HOST 'hostdoesntexist',
|
DATABASE 'spider_test',
|
USER 'spider_test',
|
PASSWORD 'password'
|
);
|
|
CREATE DATABASE spider_test;
|
|
USE spider_test;
|
|
CREATE TABLE spider_tab (
|
id int auto_increment primary key,
|
str varchar(50)
|
) ENGINE=Spider COMMENT='wrapper "mysql", srv "srv1", table "local_tab"';
|
This is obviously a bad server definition, so querying this table will fail:
MariaDB [spider_test]> SELECT * FROM spider_tab;
|
ERROR 1429 (HY000): Unable to connect to foreign data source: srv1
|
So the intuitive fix would be to drop the server object and recreate it with the fixed parameters:
DROP SERVER srv1;
|
|
|
CREATE SERVER srv1
|
FOREIGN DATA WRAPPER mysql
|
OPTIONS(
|
HOST '172.30.0.249',
|
DATABASE 'spider_test',
|
USER 'spider_test',
|
PASSWORD 'password'
|
);
|
However, Spider still seems to use the old definition of the server object:
MariaDB [spider_test]> SELECT * FROM spider_tab;
|
ERROR 1429 (HY000): Unable to connect to foreign data source: srv1
|
Dropping the table and recreating it with the same definition allows the table to work:
MariaDB [spider_test]> SELECT * FROM spider_tab;
|
ERROR 1429 (HY000): Unable to connect to foreign data source: srv1
|
MariaDB [spider_test]> SHOW CREATE TABLE spider_tab\G
|
*************************** 1. row ***************************
|
Table: spider_tab
|
Create Table: CREATE TABLE `spider_tab` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`str` varchar(50) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", srv "srv1", table "local_tab"'
|
1 row in set, 2 warnings (0.00 sec)
|
|
|
MariaDB [spider_test]> DROP TABLE spider_tab;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [spider_test]> CREATE TABLE `spider_tab` (
|
-> `id` int(11) NOT NULL AUTO_INCREMENT,
|
-> `str` varchar(50) DEFAULT NULL,
|
-> PRIMARY KEY (`id`)
|
-> ) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", srv "srv1", table "local_tab"';
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [spider_test]> SELECT * FROM spider_tab;
|
Empty set (0.01 sec)
|
Attachments
Activity
The need for FLUSH TABLES after changing a server definition needs to be documented in a way that it is easy to find by anyone that is setting up a Spider cluster. If it is already documented, then I haven't found it yet.
Reassigning the bug to Kenneth Dyer who is documenting Spider in MariaDB Server 10.3. Kenneth, you can assign it to me for review when you're done. Thanks.
Kenneth Dyer has documented the need for using FLUSH TABLES after changing a server definition: Unable-to-Connect-Errors
When a server definition is dropped and recreated, this must be followed by FLUSH TABLES before again attempting to access the table. The FLUSH TABLES results in Spider closing the old server definition. On the next access to the table, Spider will reopen the server definition, this time getting the new server definition:
MariaDB [spider_test]> FLUSH TABLES;MariaDB [spider_test]>