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

CONNECT engine does not handle TEXT columns

Details

    Description

      On a remote server, I have this table definition:

      CREATE TABLE `test`.`problem2` (
      		 
      	`ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
      		 
      	`message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
      		 
      )
      		 
      COLLATE='latin1_german1_ci'
      		 
      ENGINE=InnoDB

      When I try to connect to this table using 

      create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
      		 
      connection='mysql://me:xyzzy@myhost/test/problem2'

      I get this error message:

      Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]

      Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.

      This is, as has been pointed out, documented behaviour and, as such, not a bug. I would like to make a feature request for full support of TEXT (and its variants) columns, in order to make CONNECT even more useful.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          gwselke Gisbert W. Selke created issue -
          gwselke Gisbert W. Selke made changes -
          Field Original Value New Value
          Comment [ Here's how to reproduce:
          On the remote server, create a table like this:
          {code:sql}
          CREATE TABLE remotetebl ( a int, b int, c int, d int, key idx1 (a) );
          {code}
          Populate this table with 100 million random records.
          On the local host, create a CONNECTed table tbl_connect to remotetbl.
          On the local host, run {{SELECT count( * ) FROM tbl_connect}}.
          On the remote host run {{SHOW FULL PROCESSLISt}}.
          See what command was started from your local host. ]
          anel Anel Husakovic made changes -
          Description On a remote server, I have this table definition:
          {code:sql}
          CREATE TABLE `test`.`problem2` (
          `ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
          )
          COLLATE='latin1_german1_ci'
          ENGINE=InnoDB
          {code}

          When I try to connect to this table using
          {code:sql}
          create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
          connection='mysql://me:xyzzy@myhost/test/problem2'
          {code}
          I get this error message:
          Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]

          Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.           		On a remote server, I have this table definition:
          {code:sql}
          CREATE TABLE `test`.`problem2` (
          `ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
          )
          COLLATE='latin1_german1_ci'
          ENGINE=InnoDB
          {code}

          When I try to connect to this table using
          {code:sql}
          create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
          connection='mysql://me:xyzzy@myhost/test/problem2'
          {code}
          I get this error message:
          {code:sql}
          Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]
          {code}
          Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.
          gwselke Gisbert W. Selke made changes -
          Description On a remote server, I have this table definition:
          {code:sql}
          CREATE TABLE `test`.`problem2` (
          `ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
          )
          COLLATE='latin1_german1_ci'
          ENGINE=InnoDB
          {code}

          When I try to connect to this table using
          {code:sql}
          create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
          connection='mysql://me:xyzzy@myhost/test/problem2'
          {code}
          I get this error message:
          {code:sql}
          Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]
          {code}
          Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.           		On a remote server, I have this table definition:
          {code:sql}
          CREATE TABLE `test`.`problem2` (
          `ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
          )
          COLLATE='latin1_german1_ci'
          ENGINE=InnoDB
          {code}

          When I try to connect to this table using
          {code:sql}
          create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
          connection='mysql://me:xyzzy@myhost/test/problem2'
          {code}
          I get this error message:
          {code:sql}
          Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]
          {code}
          Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.

          This is, as has been pointed out, documented behaviour and, as such, not a bug. I would like to make a feature request for full support of TEXT (and its variants) columns, in order to make CONNECT even more useful.
          serg Sergei Golubchik made changes -
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.8 [ 26121 ]
          Fix Version/s 10.9 [ 26905 ]
          Fix Version/s 10.10 [ 27530 ]
          Fix Version/s 10.11 [ 27614 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.8 [ 26121 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.9 [ 26905 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.10 [ 27530 ]
          danblack Daniel Black made changes -
          Assignee Andrew Hutchings [ JIRAUSER52179 ]

          People

            Unassigned Unassigned
            gwselke Gisbert W. Selke
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.