[MDEV-12414] ENUM column type is not supported by CONNECT engine with tabtype=MySQL Created: 2017-03-31  Updated: 2020-08-25  Resolved: 2017-03-31

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.23
Fix Version/s: 10.2.5, 10.3.0, 10.1.25, 10.0.31

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by Olivier Bertrand [ 2017-03-31 ]

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

Comment by Valerii Kravchuk [ 2017-06-09 ]

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?

Comment by Valerii Kravchuk [ 2017-07-05 ]

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

Generated at Thu Feb 08 07:57:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.