Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15700

Spider ignores change to server object

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            jacob-mathew Jacob Mathew (Inactive)
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration