[MDEV-30496] CONNECT engine does not handle TEXT columns Created: 2023-01-29  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.6.10
Fix Version/s: 10.6, 10.11

Type: Bug Priority: Major
Reporter: Gisbert W. Selke Assignee: Andrew Hutchings
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux


 Description   

On a remote server, I have this table definition:

CREATE TABLE `test`.`problem2` (
 
	`ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
 
	`message` TEXT NULL DEFAULT NULL COLLATE 'latin1_german1_ci'
 
)
 
COLLATE='latin1_german1_ci'
 
ENGINE=InnoDB

When I try to connect to this table using 

create table localtest.problem2_connect engine=CONNECT table_type=MYSQL
 
connection='mysql://me:xyzzy@myhost/test/problem2'

I get this error message:

Engine CONNECT failed to discover table `localtest`.`problem2_connect` with 'CREATE TABLE whatever (`ts` DATETIME DEFAULT CURRENT_TIMESTAMP,`message` VARCHAR(1024)) TABLE_TYPE='MYSQL' CONNECTION='mysql://me:xyzzy@myhost/test/problem2'' [for Statement "create table localtest.problem2_connect engine=CONNECT table_type=MYSQL connection='mysql://me:xyzzy@myhost/test/problem2'"]

Note that column message was of type TEXT in the original table but this was changed to VARCHAR(1024) in the discovered CONNECT table.

This is, as has been pointed out, documented behaviour and, as such, not a bug. I would like to make a feature request for full support of TEXT (and its variants) columns, in order to make CONNECT even more useful.



 Comments   
Comment by Anel Husakovic [ 2023-01-30 ]

Currently this is not a bug, but it is per specification.
There is limitation stated that some data types are not permitted.
There is also TEXT column, that is not permitted.
Regarding conversion from TEXT to VARCHAR(1024) is also expected per data type conversion.
For data type TEXT conversion is done based on connect_type_conv (default YES,where VARCHAR type is used) and connect_conv_size (default 1024), so that is the reason why VARCHAR(1024) is used.
I agree this may be a feature request and you may change it in MDEV description to feature, but for the current state it is not treated as an bug.

Comment by Gisbert W. Selke [ 2023-01-30 ]

Thank you for pointing out that this is a documented limitation. I have changed the description as suggested. Please, adjust priorities accordingly.

Comment by Gisbert W. Selke [ 2023-01-30 ]

My connect_type_conv variable has the value YES, indeed. I wonder, though, why I get the error message shown in my original description. From the documentation I understand that with my configuration, the conversion should silently be applied.

Generated at Thu Feb 08 10:16:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.