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 jplindst - why is WSREP involved in this HANDLER trx?

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6268 SPIDER table with no COMMENT clause causes queries to wait forever

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30012 Triple thread hang in states 'NULL', 'NULL' and 'closing tables' on INSERT

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-30576 Include spider error codes in extra/perror

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29605 SIGSEGV in spider_db_ping on INSERT

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29606 Assertion `transaction->implicit_xid.is_null()' failed in THD::init_for_queries and Assertion `transaction.is_empty()' failed in prepare_new_connection_state

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29676 Dual thread hang in 'closing tables' and 'Waiting for table metadata lock' on Spider CREATE OR REPLACE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29800 ERROR 1062 (23000): Duplicate entry 'test-t-0' for key 'PRIMARY' on CoR for Spider table, DDL_LOG: Got error 1032 or 12524 when trying to execute action for entry 1/2/3/4/5/6/7 of type 'rename table'

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29854 SIGSEGV in spider_string::length on LOCK TABLES

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30011 SIGSEGV in spider_db_mbase::fin_loop_check on I_S SELECT

          • Major - Major loss of function.
          • Open
          split to

          Task - A task that needs to be done. MDEV-30578 Multiple connections in spider when executing invalid init_command on spider table

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30579 Confusing WSREP warning on HANDLER READ FIRST for spider table (possibly caused by zero-length self-referencing)

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-30580 Investigate whether self-/multi- referencing spider tables cause problems

          • Major - Major loss of function.
          • Open

          Activity

            People

              ycp Yuchen Pei
              Roel Roel Van de Paar
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.