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

Spider ignores change to server object

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

          Descending order - Click to sort in ascending order
          jacob-mathew Jacob Mathew (Inactive) added a comment - - edited

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

          jacob-mathew Jacob Mathew (Inactive) added a comment - - edited Kenneth Dyer has documented the need for using FLUSH TABLES after changing a server definition: Unable-to-Connect-Errors
          jacob-mathew Jacob Mathew (Inactive) added a comment -

          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.

          jacob-mathew Jacob Mathew (Inactive) added a comment - 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.
          jacob-mathew Jacob Mathew (Inactive) added a comment -

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

          jacob-mathew Jacob Mathew (Inactive) added a comment - 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]>

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.