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)
|