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

TEXT type not supported by connect

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.0.15
    • 10.0.16
    • None
    • CentOS release 6.5 (X86_64)

    Description

      Looking at below, in the Connection Engine Limitations part it is said that "Note: TEXT is allowed".

      https://mariadb.com/kb/en/mariadb/documentation/storage-engines/connect/connect-table-types/connect-table-types-mysql-table-type-accessing-mysqlmariadb-tables/#data-types

      I found that a CONNECT table can not be created to a remote MariaDB table with TEXT type.
      CONNECT should at least support all data types that are provided by MariaDB itself.
      (I understand there can be a limit when working with other DBs.)

      Error (Code 1105): Column memo unsupported type text

      server1

      --------------
      SHOW VARIABLES LIKE "%CHAR%"
      --------------
       
      +--------------------------+----------------------------+
      | Variable_name            | Value                      |
      +--------------------------+----------------------------+
      | character_set_client     | utf8                       |
      | character_set_connection | utf8                       |
      | character_set_database   | utf8                       |
      | character_set_filesystem | binary                     |
      | character_set_results    | utf8                       |
      | character_set_server     | utf8                       |
      | character_set_system     | utf8                       |
      | character_sets_dir       | /usr/share/mysql/charsets/ |
      +--------------------------+----------------------------+
      8 rows in set (0.04 sec)
       
      --------------
      CREATE DATABASE IF NOT EXISTS test
      --------------
       
      Query OK, 1 row affected, 1 warning (0.00 sec)
       
      Note (Code 1007): Can't create database 'test'; database exists
      --------------
      DROP TABLE IF EXISTS table_coupon_varchar
      --------------
       
      Query OK, 0 rows affected (0.01 sec)
       
      --------------
      CREATE TABLE table_coupon_varchar (
        coupon_no varchar(10) NOT NULL DEFAULT '',
        memo varchar(30) DEFAULT NULL,
        discount double DEFAULT NULL,
        PRIMARY KEY (coupon_no)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      --------------
       
      Query OK, 0 rows affected (3.84 sec)
       
      --------------
      INSERT INTO table_coupon_varchar VALUES ('1','ABC',250),('2','DE F',200),('3','GHI',300)
      --------------
       
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM table_coupon_varchar
      --------------
       
      +-----------+------------+----------+
      | coupon_no | memo       | discount |
      +-----------+------------+----------+
      | 1         | ABC     |      250 |
      | 2         | DE F    |      200 |
      | 3         | GHI     |      300 |
      +-----------+------------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      DROP TABLE IF EXISTS table_coupon_text
      --------------
       
      Query OK, 0 rows affected (0.02 sec)
       
      --------------
      CREATE TABLE table_coupon_text (
        coupon_no varchar(10) NOT NULL DEFAULT '',
        memo text DEFAULT NULL,
        discount double DEFAULT NULL,
        PRIMARY KEY (coupon_no)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      --------------
       
      Query OK, 0 rows affected (0.28 sec)
       
      --------------
      INSERT INTO table_coupon_text VALUES ('1','ABC',250),('2','DE F',200),('3','GHI',300)
      --------------
       
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM table_coupon_text
      --------------
       
      +-----------+------------+----------+
      | coupon_no | memo       | discount |
      +-----------+------------+----------+
      | 1         | ABC     |      250 |
      | 2         | DE F    |      200 |
      | 3         | GHI     |      300 |
      +-----------+------------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      DROP TABLE IF EXISTS table_coupon_mediumtext
      --------------
       
      Query OK, 0 rows affected (0.05 sec)
       
      --------------
      CREATE TABLE table_coupon_mediumtext (
        coupon_no varchar(10) NOT NULL DEFAULT '',
        memo mediumtext DEFAULT NULL,
        discount double DEFAULT NULL,
        PRIMARY KEY (coupon_no)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      --------------
       
      Query OK, 0 rows affected (0.43 sec)
       
      --------------
      INSERT INTO table_coupon_mediumtext VALUES ('1','ABC',250),('2','DE F',200),('3','GHI',300)
      --------------
       
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM table_coupon_mediumtext
      --------------
       
      +-----------+------------+----------+
      | coupon_no | memo       | discount |
      +-----------+------------+----------+
      | 1         | ABC     |      250 |
      | 2         | DE F    |      200 |
      | 3         | GHI     |      300 |
      +-----------+------------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      GRANT ALL ON test.* TO 'root'@'10.0.1.33'
      --------------
       
      Query OK, 0 rows affected (0.32 sec)
       
      Bye

      server2

      --------------
      SHOW VARIABLES LIKE "%CHAR%"
      --------------
       
      +--------------------------+----------------------------+
      | Variable_name            | Value                      |
      +--------------------------+----------------------------+
      | character_set_client     | utf8                       |
      | character_set_connection | utf8                       |
      | character_set_database   | utf8                       |
      | character_set_filesystem | binary                     |
      | character_set_results    | utf8                       |
      | character_set_server     | utf8                       |
      | character_set_system     | utf8                       |
      | character_sets_dir       | /usr/share/mysql/charsets/ |
      +--------------------------+----------------------------+
      8 rows in set (0.00 sec)
       
      --------------
      CREATE DATABASE IF NOT EXISTS test
      --------------
       
      Query OK, 1 row affected, 1 warning (0.00 sec)
       
      Note (Code 1007): Can't create database 'test'; database exists
      --------------
      CREATE OR REPLACE TABLE maria_table_coupon_varchar
      ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon_varchar' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
      --------------
       
      Query OK, 0 rows affected (0.09 sec)
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+------+----------+
      | coupon_no | memo | discount |
      +-----------+------+----------+
      | 1         | ???  |      250 |
      | 2         | ?? ? |      200 |
      | 3         | ???  |      300 |
      +-----------+------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      CREATE OR REPLACE TABLE maria_table_coupon_text
      ENGINE=CONNECT DEFAULT CHARSET=BINARY TABLE_TYPE='MYSQL' TABNAME='table_coupon_text' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
      --------------
       
      Error (Code 1105): Column memo unsupported type text
      Error (Code 1030): Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
      --------------
      SELECT * FROM maria_table_coupon_text
      --------------
       
      --------------
      CREATE OR REPLACE TABLE maria_table_coupon_mediumtext
      ENGINE=CONNECT DEFAULT CHARSET=BINARY TABLE_TYPE='MYSQL' TABNAME='table_coupon_mediumtext' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
      --------------
       
      Error (Code 1105): Column memo unsupported type mediumtext
      Error (Code 1030): Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
      --------------
      SELECT * FROM maria_table_coupon_mediumtext
      --------------
       
      Bye

      I made a test case, it can be used with the below command.

      server1

      mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test2_script_server1.sql;" > /root/test2_script_server1.log

      server2

      mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test2_script_server2.sql;" > /root/test2_script_server2.log

      Attachments

        1. test2_script_server2.sql
          0.8 kB
          Takuya Aoki
        2. test2_script_server2.log
          2 kB
          Takuya Aoki
        3. test2_script_server1.sql
          1 kB
          Takuya Aoki
        4. test2_script_server1.log
          4 kB
          Takuya Aoki

        Activity

          People

            bertrandop Olivier Bertrand
            takuya Takuya Aoki (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.