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

ENUM column type is not supported by CONNECT engine with tabtype=MySQL

Details

    Description

      I know that it's a documented limitation (https://mariadb.com/kb/en/mariadb/connect-table-types-mysql-table-type-accessing-mysqlmariadb-tables/#data-types), but I was informed that recent CONNECT code should support ENUM columns. It does not seem to be the case based on my test:

      [openxs@fc23 maria10.1]$ bin/mysql -uroot test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 2
      Server version: 10.1.23-MariaDB Source distribution
       
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> create table tenu(id int primary key, c1 enum('a','b'));
      Query OK, 0 rows affected (0.38 sec)
       
      MariaDB [test]> insert into tenu values(1,'a'), (2,'b');
      Query OK, 2 rows affected (0.04 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from tenu;
      +----+------+
      | id | c1   |
      +----+------+
      |  1 | a    |
      |  2 | b    |
      +----+------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> install soname 'ha_connect.so';
      Query OK, 0 rows affected (0.13 sec)
       
      MariaDB [test]> show engines;
      +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
      | Engine             | Support | Comment                                                                                          | Transactions | XA   | Savepoints |
      +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
      | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                            | NO           | NO   | NO         |
      | CSV                | YES     | CSV storage engine                                                                               | NO           | NO   | NO         |
      | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                        | NO           | NO   | NO         |
      | MyISAM             | YES     | MyISAM storage engine                                                                            | NO           | NO   | NO         |
      | CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats                               | NO           | NO   | NO         |
      | SEQUENCE           | YES     | Generated tables filled with sequential values                                                   | YES          | NO   | YES        |
      | Aria               | YES     | Crash-safe tables with MyISAM heritage                                                           | NO           | NO   | NO         |
      | PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                               | NO           | NO   | NO         |
      | InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
      +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
      9 rows in set (0.00 sec)
       
      MariaDB [test]> show create table tenu\G
      *************************** 1. row ***************************
             Table: tenu
      Create Table: CREATE TABLE `tenu` (
        `id` int(11) NOT NULL,
        `c1` enum('a','b') DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> create table connect_enu engine=connect table_type=mysql dbname=test tabname=tenu option_list='user=root,host=127.0.0.1,port=3306';
      ERROR 1105 (HY000): Column c1 unsupported type enum('a','b')
      

      CONNECT says the following about its version in the log:

      Version: '10.1.23-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
      2017-03-31  9:50:35 139758460771072 [Note] CONNECT: Version 1.05.0003 February 27, 2017
      

      MariaDB was built from the following GitHub version:

      [openxs@fc23 server]$ git log -1
      commit ba298b1f022aa5e1247d07a97325c0a09bdd1dc2
      Merge: 716188f c51fc67
      Author: Marko Mцєkelцє <marko.makela@mariadb.com>
      Date:   Fri Mar 24 18:20:09 2017 +0200
       
          Merge 10.0 into 10.1
      

      Please, either confirm that ENUM is still not supported, or fix whatever problem prevents from having the latest CONNECT code in the main tree.

      Attachments

        Activity

          This was pushed onto the CONNECT repository. I hope it will be included in all the next MariaDB versions.

          bertrandop Olivier Bertrand added a comment - This was pushed onto the CONNECT repository. I hope it will be included in all the next MariaDB versions.

          Sorry, but this still fails in 10.1.24:

          [openxs@fc23 maria10.1]$ bin/mysql --socket=/tmp/mariadb.sock -uroot Welcome to the MariaDB monitor. Commands end with ; or \g.
          Your MariaDB connection id is 2
          Server version: 10.1.24-MariaDB Source distribution

          Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

          MariaDB [(none)]> show tables;
          ERROR 1046 (3D000): No database selected
          MariaDB [(none)]> use test
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A

          Database changed
          MariaDB [test]> show tables;
          ----------------

          Tables_in_test

          ----------------

          t1

          ----------------
          1 row in set (0.00 sec)

          MariaDB [test]> exit
          Bye
          [openxs@fc23 maria10.1]$ bin/mysql --socket=/tmp/mariadb.sock -uroot test
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A

          Welcome to the MariaDB monitor. Commands end with ; or \g.
          Your MariaDB connection id is 3
          Server version: 10.1.24-MariaDB Source distribution

          Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

          MariaDB [test]> create table tenu(id int primary key, c1 enum('a','b'));
          Query OK, 0 rows affected (0.61 sec)

          MariaDB [test]> insert into tenu values(1,'a'), (2,'b');
          Query OK, 2 rows affected (0.04 sec)
          Records: 2 Duplicates: 0 Warnings: 0

          MariaDB [test]> select * from tenu;
          --------+

          id c1

          --------+

          1 a
          2 b

          --------+
          2 rows in set (0.00 sec)

          MariaDB [test]> install soname 'ha_connect.so';
          Query OK, 0 rows affected (0.00 sec)

          MariaDB [test]> create table connect_enu engine=connect table_type=mysql dbname=test tabname=tenu option_list='user=root,host=127.0.0.1,port=3306';
          ERROR 1105 (HY000): Column c1 unsupported type enum('a','b')

          I wonder why the bug is closed?

          valerii Valerii Kravchuk added a comment - Sorry, but this still fails in 10.1.24: [openxs@fc23 maria10.1] $ bin/mysql --socket=/tmp/mariadb.sock -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.24-MariaDB Source distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)] > show tables; ERROR 1046 (3D000): No database selected MariaDB [(none)] > use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test] > show tables; ---------------- Tables_in_test ---------------- t1 ---------------- 1 row in set (0.00 sec) MariaDB [test] > exit Bye [openxs@fc23 maria10.1] $ bin/mysql --socket=/tmp/mariadb.sock -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.24-MariaDB Source distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test] > create table tenu(id int primary key, c1 enum('a','b')); Query OK, 0 rows affected (0.61 sec) MariaDB [test] > insert into tenu values(1,'a'), (2,'b'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test] > select * from tenu; --- -----+ id c1 --- -----+ 1 a 2 b --- -----+ 2 rows in set (0.00 sec) MariaDB [test] > install soname 'ha_connect.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > create table connect_enu engine=connect table_type=mysql dbname=test tabname=tenu option_list='user=root,host=127.0.0.1,port=3306'; ERROR 1105 (HY000): Column c1 unsupported type enum('a','b') I wonder why the bug is closed?

          This seems to be fixed in recent code (I assume 10.1.25 also):

          [openxs@fc23 maria10.1]$ bin/mysql --socket=/tmp/mysql.sock -uroot test
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A
           
          Welcome to the MariaDB monitor.  Commands end with ; or \g.
          Your MariaDB connection id is 2
          Server version: 10.1.26-MariaDB Source distribution
           
          Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          MariaDB [test]> create table tenu(id int primary key, c1 enum('a','b'));
          ERROR 1050 (42S01): Table 'tenu' already exists
          MariaDB [test]> show create table tenu\G
          *************************** 1. row ***************************
                 Table: tenu
          Create Table: CREATE TABLE `tenu` (
            `id` int(11) NOT NULL,
            `c1` enum('a','b') DEFAULT NULL,
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          1 row in set (0.00 sec)
           
          MariaDB [test]> select * from tenu;
          +----+------+
          | id | c1   |
          +----+------+
          |  1 | a    |
          |  2 | b    |
          +----+------+
          2 rows in set (0.02 sec)
           
          MariaDB [test]> install soname 'ha_connect.so';
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> show engines;
          +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
          | Engine             | Support | Comment                                                                                          | Transactions | XA   | Savepoints |
          +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
          | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                            | NO           | NO   | NO         |
          | CSV                | YES     | CSV storage engine                                                                               | NO           | NO   | NO         |
          | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                        | NO           | NO   | NO         |
          | MyISAM             | YES     | MyISAM storage engine                                                                            | NO           | NO   | NO         |
          | CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats                               | NO           | NO   | NO         |
          | SEQUENCE           | YES     | Generated tables filled with sequential values                                                   | YES          | NO   | YES        |
          | Aria               | YES     | Crash-safe tables with MyISAM heritage                                                           | NO           | NO   | NO         |
          | PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                               | NO           | NO   | NO         |
          | InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
          +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
          9 rows in set (0.00 sec)
           
          MariaDB [test]> drop table if exists connect_enu;
          Query OK, 0 rows affected, 1 warning (0.04 sec)
           
          MariaDB [test]> create table connect_enu engine=connect table_type=mysql dbname=test tabname=tenu option_list='user=root,host=127.0.0.1,port=3306';
          Query OK, 0 rows affected (0.03 sec)
           
          MariaDB [test]> select * from connect_enu;                                      
          +----+------+
          | id | c1   |
          +----+------+
          |  1 | a    |
          |  2 | b    |
          +----+------+
          2 rows in set (0.00 sec)
          

          The following CONNECT version is used based on the error log:

          2017-07-05 10:18:21 139793498352960 [Note] CONNECT: Version 1.06.0001 April 17, 2017

          valerii Valerii Kravchuk added a comment - This seems to be fixed in recent code (I assume 10.1.25 also): [openxs@fc23 maria10.1]$ bin/mysql --socket=/tmp/mysql.sock -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.26-MariaDB Source distribution   Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [test]> create table tenu(id int primary key, c1 enum('a','b')); ERROR 1050 (42S01): Table 'tenu' already exists MariaDB [test]> show create table tenu\G *************************** 1. row *************************** Table: tenu Create Table: CREATE TABLE `tenu` ( `id` int(11) NOT NULL, `c1` enum('a','b') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)   MariaDB [test]> select * from tenu; +----+------+ | id | c1 | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.02 sec)   MariaDB [test]> install soname 'ha_connect.so'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> show engines; +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+ | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | +--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)   MariaDB [test]> drop table if exists connect_enu; Query OK, 0 rows affected, 1 warning (0.04 sec)   MariaDB [test]> create table connect_enu engine=connect table_type=mysql dbname=test tabname=tenu option_list='user=root,host=127.0.0.1,port=3306'; Query OK, 0 rows affected (0.03 sec)   MariaDB [test]> select * from connect_enu; +----+------+ | id | c1 | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) The following CONNECT version is used based on the error log: 2017-07-05 10:18:21 139793498352960 [Note] CONNECT: Version 1.06.0001 April 17, 2017

          People

            bertrandop Olivier Bertrand
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.