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

Spider: ERROR 12710 (HY000): Invalid information from remote table when using MariaDB 10.5 local and MariaDB 10.6 remote

Details

    Description

      When using Spider storage engine on MariaDB 10.5 to connect to a MariaDB 10.6 remote server, if the spider table in your query has an index and is used as part of the query you will get 

      ERROR 12710 (HY000): Invalid information from remote table 'employees.dept_emp'

      If you run the following command you get the same error

      show index from emp_spider.dept_emp;
      		 
      ERROR 12710 (HY000): Invalid information from remote table 'employees.dept_emp'

      This issue easily reproducible.

      1. Create a 10.6 server and load the following schema onto it https://github.com/datacharmer/test_db
      2. Create a user to use for spider access
      3. Then build a 10.5 server
      4. Load the emp_spider_repro.sql schema onto the 10.5 server
        1. Be sure use the correct details for creating the server for Spider engine to use
      5. Run the following commands on the 10.5 server

      show index from emp_spider.dept_emp;
      		 
      select count(*) from emp_spider.employees;
      		 
      select count(*) from emp_spider.employees where emp_no between 1 and 40000;

      The first and last command will return with an error but the second command will succeed

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-7317 Make an index ignorable to the optimizer

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

          Activity

            Ascending order - Click to sort in descending order
            ycp Yuchen Pei added a comment - - edited

            Below is a minimal testcase.

            Start a 10.6 server with

            mtr --start-and-exit && ./client/mysql -uroot -S./mysql-test/var/tmp/mysqld.1.sock test
            		 
            {shell}
            		 
             
            		 
            and note the socket. Then run these queries
            		 
             
            		 
            {code:sql}
            		 
            create table t (c int, key (c));
            		 
            create table t1 (c int); # not needed for bug reprod, but just for comparison

            create an mtr testcase in a 10.5 build and call mtr on it:

            install soname 'ha_spider';
            		 
            create table t (c int, key (c)) ENGINE=Spider
            		 
            # adjust the socket accordingly
            		 
            COMMENT='socket "/home/ycp/source/mariadb-server/10.6/build/mysql-test/var/tmp/mysqld.1.sock", user "root"';
            		 
            insert into t values (42), (3);
            		 
            select count(c) from t;             # ok
            		 
            --error 12710
            		 
            select count(c) from t where c < 4; # fail
            		 
            # not needed for bug reprod, but just for comparison
            		 
            create table t1 (c int) ENGINE=Spider
            		 
            # adjust the socket accordingly
            		 
            COMMENT='socket "/home/ycp/source/mariadb-server/10.6/build/mysql-test/var/tmp/mysqld.1.sock", user "root"';
            		 
            select count(c) from t1 where c < 4; # ok

            And updating the check on num_fields in {{ spider_db_mbase_result::fetch_table_cardinality()}} fixes it.

            ycp Yuchen Pei added a comment - - edited Below is a minimal testcase. Start a 10.6 server with mtr --start-and-exit && ./client/mysql -uroot -S./mysql-test/var/tmp/mysqld.1.sock test {shell}   and note the socket. Then run these queries   {code:sql} create table t (c int, key (c)); create table t1 (c int); # not needed for bug reprod, but just for comparison create an mtr testcase in a 10.5 build and call mtr on it: install soname 'ha_spider' ; create table t (c int , key (c)) ENGINE=Spider # adjust the socket accordingly COMMENT= 'socket "/home/ycp/source/mariadb-server/10.6/build/mysql-test/var/tmp/mysqld.1.sock", user "root"' ; insert into t values (42), (3); select count (c) from t; # ok --error 12710 select count (c) from t where c < 4; # fail # not needed for bug reprod, but just for comparison create table t1 (c int ) ENGINE=Spider # adjust the socket accordingly COMMENT= 'socket "/home/ycp/source/mariadb-server/10.6/build/mysql-test/var/tmp/mysqld.1.sock", user "root"' ; select count (c) from t1 where c < 4; # ok And updating the check on num_fields in {{ spider_db_mbase_result::fetch_table_cardinality()}} fixes it.
            ycp Yuchen Pei added a comment -

            Hi holyfoot, ptal thanks:

            bb-10.4-mdev-33777 bb-10.4-ycp 5466f5b18d0cfcd50acd4f3a5333b2ded9ba9c1b
            		 
            MDEV-33777 Spider: Correct checks for show index column numbers
            		 
             
            		 
            It was updated for 10.6+ in MDEV-7317. Because a lower version spider
            		 
            node may connect to a higher version data node, we need to change this
            		 
            for 10.4 and 10.5 as well.

            ycp Yuchen Pei added a comment - Hi holyfoot , ptal thanks: bb-10.4-mdev-33777 bb-10.4-ycp 5466f5b18d0cfcd50acd4f3a5333b2ded9ba9c1b MDEV-33777 Spider: Correct checks for show index column numbers   It was updated for 10.6+ in MDEV-7317. Because a lower version spider node may connect to a higher version data node, we need to change this for 10.4 and 10.5 as well.
            holyfoot Alexey Botchkov added a comment -

            ok to push.

            holyfoot Alexey Botchkov added a comment - ok to push.
            ycp Yuchen Pei added a comment -

            thanks for the review - pushed 051a1fa0e9fb61a3347d7ca8c41ddc8d95261729 to 10.4

            ycp Yuchen Pei added a comment - thanks for the review - pushed 051a1fa0e9fb61a3347d7ca8c41ddc8d95261729 to 10.4

            People

              ycp Yuchen Pei
              kyle.hutchinson Kyle Hutchinson
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.