Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.15
-
Fix Version/s: 10.0.16
-
Component/s: Storage Engine - Connect
-
Labels:None
-
Environment:CentOS release 6.5 (X86_64)
Description
Looking at below, in the Connection Engine Limitations part it is said that "Note: TEXT is allowed".
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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.
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)]>
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).
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.
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.
Ok, connect_type_conv and connect_conv_size will be session variables in the next release.
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.
, n being the value of connect_conv_size.
YES The column is internally converted to TYPE_STRING corresponding to a column declared as VARCHAR
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.