[MDEV-15700] Spider ignores change to server object Created: 2018-03-27  Updated: 2018-05-22  Resolved: 2018-05-22

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Storage Engine - Spider
Affects Version/s: 10.2.14
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Jacob Mathew (Inactive)
Resolution: Fixed Votes: 0
Labels: spider


 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)



 Comments   
Comment by Jacob Mathew (Inactive) [ 2018-05-11 ]

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]> SELECT * FROM spider_tab;
 
ERROR 1429 (HY000): Unable to connect to foreign data source: srv1
 
MariaDB [spider_test]> FLUSH TABLES;
 
Query OK, 0 rows affected (0.020 sec)
 
 
 
MariaDB [spider_test]> SELECT * FROM spider_tab;
 
Empty set (3.020 sec)
 
 
 
MariaDB [spider_test]>

Comment by Jacob Mathew (Inactive) [ 2018-05-11 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-05-22 ]

Kenneth Dyer has documented the need for using FLUSH TABLES after changing a server definition: Unable-to-Connect-Errors

Generated at Thu Feb 08 08:23:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.