[MDEV-7488] TEXT type not supported by connect Created: 2015-01-22  Updated: 2015-02-06  Resolved: 2015-01-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.15
Fix Version/s: 10.0.16

Type: Bug Priority: Major
Reporter: Takuya Aoki (Inactive) Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS release 6.5 (X86_64)


Attachments: Text File test2_script_server1.log     File test2_script_server1.sql     Text File test2_script_server2.log     File test2_script_server2.sql    

 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','ＡＢＣ',250),('2','ＤＥ Ｆ',200),('3','ＧＨＩ',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         | ＡＢＣ     |      250 |
 
| 2         | ＤＥ Ｆ    |      200 |
 
| 3         | ＧＨＩ     |      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','ＡＢＣ',250),('2','ＤＥ Ｆ',200),('3','ＧＨＩ',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         | ＡＢＣ     |      250 |
 
| 2         | ＤＥ Ｆ    |      200 |
 
| 3         | ＧＨＩ     |      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','ＡＢＣ',250),('2','ＤＥ Ｆ',200),('3','ＧＨＩ',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         | ＡＢＣ     |      250 |
 
| 2         | ＤＥ Ｆ    |      200 |
 
| 3         | ＧＨＩ     |      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



 Comments   
Comment by Olivier Bertrand [ 2015-01-23 ]

Indeed, the documentation on CONNECT data types was not updated and is incomplete. Look at:
https://mariadb.com/kb/en/mariadb/documentation/storage-engines/connect/connect-data-types/
At the bottom of it, under Data Type Conversion it says:
In the case of TEXT columns, the handling depends on the values given to the connect_type_conv and connect_conv_size system variables.
Missing on this web page is the description of these system variables. Here it is

If the value of connect_type_conv is:

NO No conversion. TYPE_ERROR is returned causing a “not supported” message.
YES The column is internally converted to TYPE_STRING corresponding to a column declared as VARCHAR, n being the value of connect_conv_size.
SKIP No conversion. When column declaration is provided via Discovery (meaning the CONNECT table is created without column description) this column is not generated.

Note: connect_type_conv and connect_conv_size are global variables that are normally read only except when using a debug compiled server.

The connect_type_conv defaults to NO explaining your error. Therefore setting the connect_type_conv global variable to YES and the connect_conv_size to whatever is needed (it defaults to 65500) should provide the required conversion.

Comment by Takuya Aoki (Inactive) [ 2015-01-27 ]

In build 10.0.16 build-7290, connect_type_conv is a read only variable.
Do you mean it will be changed so I can change connect_type_conv like below (or in my configuration file)?
Actually I testd in version 10.0.13 and it wasn't a read only variable then.

MariaDB [(none)]> SET GLOBAL connect_type_conv=1;
 
ERROR 1238 (HY000): Variable 'connect_type_conv' is a read only variable

Comment by Olivier Bertrand [ 2015-01-27 ]

To change a global variable, it must be done in the my.cnf file or as a parameter when starting mysqld.
In my understanding, a global variable applies for all users connected simultaneously. This is why I declared read_only the variables that a user does not want to be changed during his session by another user.

Comment by Takuya Aoki (Inactive) [ 2015-01-27 ]

I wrote down the below in my.cnf file (/etc/my.cnf.d/connect.cnf) but it was not reflected.
This is simular to MDEV-6690 where connect_work_size in the my.cnf file did not work.

connect_type_conv=YES

I also tried specifying it as a parameter but it does not work.

[root@kc1060 my.cnf.d]# mysql -u root --connect_type_conv=1
 
mysql: unknown variable 'connect_type_conv=1'
 
[root@kc1060 my.cnf.d]# mysql -u root --connect_type_conv=YES
 
mysql: unknown variable 'connect_type_conv=YES'
 
[root@kc1060 my.cnf.d]# mysql -u root --connect_timeout=20
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 6
 
Server version: 10.0.16-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]>

Comment by Takuya Aoki (Inactive) [ 2015-02-05 ]

This bug needs to be reopened if there is no way of changing connect_type_conv to YES (It is a read only variable in 10.0.16).

Comment by Olivier Bertrand [ 2015-02-05 ]

The problem with cnf files is that it is extremely difficult to know where is the one effectively used, especially on Windows where I was unable to see where it is! Also, you must enter the specification in the right section, here the [mysqld] section I guess.

Concerning specifying it as a parameter, it is not to be done in the mysql command. It must be done in the mysqld command when starting the server. I did it and it works.

Now an open question is: should connect_type_conv be a global parameter (read only to avoid to act on simultaneously connected users) or a session parameter? I am not really decided on that and if you think it should be a session parameter I will make it session. As a matter of fact, it seems reasonable to me.

Comment by Takuya Aoki (Inactive) [ 2015-02-06 ]

As you say, it worked as a mysqld option.

[root@kc0022 ~]# /etc/init.d/mysql restart --connect_type_conv=1
 
Shutting down MySQL.. SUCCESS!
 
Starting MySQL. SUCCESS!

my.cnf file also worked this time setting it under the [mariadb] section.

 [mariadb]
 
connect_type_conv=YES

I think it's better for CONNECT System Variables to be session dependent, since it works like a table option.
Only if simultaneously connected users setting the parameter differently isn't a problem of course.

Comment by Olivier Bertrand [ 2015-02-06 ]

Ok, connect_type_conv and connect_conv_size will be session variables in the next release.

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