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

ALTER TABLE ENGINE doesn't copy data from SPIDER tables

Details

    Description

      If I use the CONNECT engine to federate data from another table, and then issue something like the following on the CONNECT table:

      ALTER TABLE tbl1 ENGINE=InnoDB

      When converting from CONNECT to InnoDB, both the schema and table contents will be copied.

      If I instead use the SPIDER engine to federate the access, only the schema definition is modified with the previous SQL statement, but no data is copied into the local table, instead the table remain empty.

      My particular intent is to archive legacy data, moving it from one physical server to another after it has aged out.

      I would simply use the CONNECT engine itself, but it fails to support SETs, ENUMs, and NULLable indexes.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24389 CREATE TABLE ... ENGINE=S3 fails with (errno: 131 "Command not supported by the engine")

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 9 older comments
            ycp Yuchen Pei added a comment -

            ralf.gebhardt, sure - here's how to do it.

            Suppose we have a table src on a remote server, and we want to copy its content to an innodb table dst on the local server using spider.

            Let's say the src table has the following schema: (c INT, d DATE, PRIMARY KEY(c)).

            First, create the dst table with the same schema if it does not already exist:

            CREATE TABLE dst (c INT, d DATE, PRIMARY KEY(c)) ENGINE=INNODB;

            Then, create a spider table t with both src and dst as HA data nodes. Also ensure spider_same_server_link because t and src are on the same server (assuming the src is on SERVER s1 and the local server is defined to be SERVER s2):

            SET spider_same_server_link=1;
            		 
            CREATE TABLE t (c INT, d DATE, PRIMARY KEY(c)) ENGINE=SPIDER COMMENT='table "src dst", srv "s1 s2"';

            Finally, copy data from src to dst using spider_copy_tables:

            SELECT spider_copy_tables('t', '0', '1');

            I've also added a testcase which I'll push as a "fix" for this issue once it is reviewed.

            ycp Yuchen Pei added a comment - ralf.gebhardt , sure - here's how to do it. Suppose we have a table src on a remote server, and we want to copy its content to an innodb table dst on the local server using spider. Let's say the src table has the following schema: (c INT, d DATE, PRIMARY KEY(c)) . First, create the dst table with the same schema if it does not already exist: CREATE TABLE dst (c INT , d DATE , PRIMARY KEY (c)) ENGINE=INNODB; Then, create a spider table t with both src and dst as HA data nodes. Also ensure spider_same_server_link because t and src are on the same server (assuming the src is on SERVER s1 and the local server is defined to be SERVER s2): SET spider_same_server_link=1; CREATE TABLE t (c INT , d DATE , PRIMARY KEY (c)) ENGINE=SPIDER COMMENT= 'table "src dst", srv "s1 s2"' ; Finally, copy data from src to dst using spider_copy_tables : SELECT spider_copy_tables( 't' , '0' , '1' ); I've also added a testcase which I'll push as a "fix" for this issue once it is reviewed.
            ycp Yuchen Pei added a comment -

            Hi holyfoot, ptal at the new testcase, thanks:

            3b8bfbb8319 bb-10.5-mdev-30649 MDEV-30649 Adding a spider testcase showing copying from a remote to a local table

            ycp Yuchen Pei added a comment - Hi holyfoot , ptal at the new testcase, thanks: 3b8bfbb8319 bb-10.5-mdev-30649 MDEV-30649 Adding a spider testcase showing copying from a remote to a local table
            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 5be859d52ccefcd8d63ff5b00167383e4d9837df to 10.5

            ycp Yuchen Pei added a comment - thanks for the review - pushed 5be859d52ccefcd8d63ff5b00167383e4d9837df to 10.5
            ycp Yuchen Pei added a comment -

            As previously mentioned, pushed a testcase showcasing the copy data

            ycp Yuchen Pei added a comment - As previously mentioned, pushed a testcase showcasing the copy data

            People

              ycp Yuchen Pei
              darkain Vincent Milum Jr
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.