[MDEV-8928] Queries on information_schema.TABLES with invalid SPIDER table causes problems Created: 2015-10-11  Updated: 2021-08-13  Resolved: 2021-08-13

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.0.21, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None


 Description    

SELECT * FROM mysql.spider_tables \G
 
*************************** 1. row ***************************
 
               db_name: x
 
            table_name: s
 
               link_id: 0
 
              priority: 1000000
 
                server: NULL
 
                scheme: NULL
 
                  host: 127.0.0.1
 
                  port: NULL
 
                socket: NULL
 
              username: root
 
              password: proot
 
                ssl_ca: NULL
 
            ssl_capath: NULL
 
              ssl_cert: NULL
 
            ssl_cipher: NULL
 
               ssl_key: NULL
 
ssl_verify_server_cert: 0
 
          default_file: NULL
 
         default_group: NULL
 
           tgt_db_name: NULL
 
        tgt_table_name: NULL
 
           link_status: 1
 
1 row in set (0.00 sec)
 

SHOW PROCESSLIST;
 
+----+------+-------------------+------+---------+------+----------------------+-----------------------------------------+----------+
 
| Id | User | Host              | db   | Command | Time | State                | Info                                    | Progress |
 
+----+------+-------------------+------+---------+------+----------------------+-----------------------------------------+----------+
 
| 32 | root | 172.17.42.1:35121 | test | Query   |    3 | After opening tables | SELECT * FROM information_schema.TABLES |    0.000 |
 
| 45 | root | 172.17.42.1:35145 | test | Query   |    0 | init                 | SHOW PROCESSLIST                        |    0.000 |
 
| 47 | root | 127.0.0.1:43968   | x    | Query   |    3 | After opening tables | show table status from `x` like 's'     |    0.000 |
 
+----+------+-------------------+------+---------+------+----------------------+-----------------------------------------+----------+

Connection 32 will wait forever or for a long time (not sure). I can KILL connection 47, but connection 32 will say that MySQL server has gone away.
Connection 47 will also block:

  • DROP TABLE x.s
  • FLUSH TABLES
  • maybe others...

The solution is not that hard (KILL 47; DROP TABLE x.s), but I think that SPIDER should detect the problem and return an error to the user. Or maybe SPIDER shouldn't open that connection at all.

If this is not a bug, please consider it as a feature request.

 Comments   
Comment by Federico Razzoli [ 2015-10-12 ]

Probably the test case shows a side problem: SPIDER does bad things, but after killing the SPIDER connection, the other one should be able to complete the query on i_s.TABLES (I think).

Comment by Elena Stepanova [ 2015-10-21 ] 

--exec $MYSQL < `find .. -name install_spider.sql`
 
 
 
CREATE DATABASE x;
 
USE x;
 
CREATE TABLE s (id INT) ENGINE=SPIDER COMMENT 'host "127.0.0.1", user "root"';
 
SHOW TABLE STATUS FROM x LIKE 's';

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-13 ]

Hi f_razzoli! I'm another Spider developer. I tried to reproduce the bug on 10.2 but I couldn't, and thus I close the issue. Please let me know if the problem is still there or I missed something.

Generated at Thu Feb 08 07:30:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.