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

Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user

    XMLWordPrintable

    Details

      Description

      INSTALL PLUGIN Spider SONAME 'ha_spider.so';
      CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';
      CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD1');
      CREATE TABLE t (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
      SET GLOBAL init_connect="dummy";
      SELECT c FROM t;
      HANDLER t OPEN;
      HANDLER t READ FIRST;
      

      Produces some interesting results:

      10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

      10.11.0-opt>source in.sql
      Query OK, 0 rows affected, 1 warning (0.030 sec)
       
      Query OK, 0 rows affected (0.007 sec)
       
      Query OK, 0 rows affected (0.001 sec)
       
      Query OK, 0 rows affected (0.410 sec)
       
      Query OK, 0 rows affected (0.000 sec)
       
      ERROR 12701 (HY000) at line 6 in file: 'in.sql': Remote MariaDB server has gone away
      Query OK, 0 rows affected (0.000 sec)
       
      ERROR 1184 (08S01) at line 8 in file: 'in.sql': Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      10.11.0-opt>
      

      Or when not sourced/directly executed at the CLI:

      10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

      ...
      10.11.0-opt>SELECT c FROM t;
      ERROR 12701 (HY000): Remote MariaDB server has gone away
      10.11.0-opt>HANDLER t OPEN;
      Query OK, 0 rows affected (0.000 sec)
       
      10.11.0-opt>HANDLER t READ FIRST;
      ERROR 1184 (08S01): Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      10.11.0-opt>
      

      Error log:

      10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

      2022-09-21 11:16:45 0 [Note] /test/MD190922-mariadb-10.11.0-linux-x86_64-opt/bin/mysqld: ready for connections.
      Version: '10.11.0-MariaDB'  socket: '/test/MD190922-mariadb-10.11.0-linux-x86_64-opt/socket.sock'  port: 11824  MariaDB Server
      2022-09-21 11:16:50 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 25 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 26 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 27 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 28 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 29 [Warning] Aborted connection 29 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 29 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 30 [Warning] Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
      2022-09-21 11:16:50 30 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
      2022-09-21 11:16:50 4 [ERROR] mysql_ha_read: Got error 1184 when reading table 't'
      2022-09-21 11:16:50 4 [Warning] WSREP: handlerton rollback failed, thd 4 226 conf 0 SQL HANDLER t READ FIRST
      

      While the eventual outcome looks understandable (init_connect command failed) the in-between outcomes seem to require some work;
      1. The Remote MariaDB server has gone away is odd given the server has not gone away (and is reachable). This is likely due to #2, ref next item.
      2. Self-referencing should not be allowed? (Spider t table referencing to itself)
      3. The various 'Aborted connections'. This too is likely due to #2? Also see #6 in this regard.
      4. 'init_connect' should likely not be allowed for the spider connection?
      5. The final errors (mysql_ha_read/ WSREP) are likely due to #4.
      6. It is odd that there are 5 attempts? If this is a hardcoded number of retries within Spider it may be best to improve this code to a single attempt with appropriate return values if it fails etc.?
      7. More as a question to Jan Lindström - why is WSREP involved in this HANDLER trx?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              Roel Roel Van de Paar
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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