Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 10.0.9
-
Fix Version/s: 10.0.11
-
Component/s: None
-
Labels:
-
Environment:any
Description
Apparently it is not possibly to create a MYSQL table with a blob column?
mysql 10.0.9-MariaDB (root) [test] db1> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql 10.0.9-MariaDB (root) [test] db1> create table xt1 (id int unsigned, b blob) engine=connect table_type=mysql tabname=t1;
ERROR 1105 (HY000): Unsupported type for column b
I didn't see any documentation about this.
And creating a PROXY table causes it to try using the wrong datatype
and fail:
mysql 10.0.9-MariaDB (root) [test] db1> create table xt1 engine=connect table_type=proxy tabname=t1; ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`xt1` with 'CREATE TABLE whatever (`id` INT(10) UNSIGNED NOT NULL,`b` VARCHAR(65535)) TABLE_TYPE='proxy' TABNAME='t1''
Should this be supported? If not, obviously, it must be clearly documented at https://mariadb.com/kb/en/connect-data-types/
Currently, that page seems to suggest that blob might be supported when it says TYPE_STRING should be used for "char, varchar, text, blob".
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Olivier, thanks for replying. Here are my thoughts.
My view is that silent conversions and truncations should never be performed. If someone wants to do something crazy and explicitly define something in a way that will truncate their data, we should warn them, but it's probably OK to let them proceed. Explicitly defining a MYSQL table to use a datatype different from the source might qualify. But this should never happen automatically.
On top of that, though, BLOB should not turn into a VARCHAR; it should be at the very least VARBINARY. Maybe that's a change that could be made now to reduce potential problems for binary data being treated as string data?
CONNECT now support global variables (for instance connect_xtrace replaces the use of the connect.ini entry)
I can add a variable specifying whether a conversion from TEXT to VARCHAR should be done and how. For instance:
NO: no conversion. BLOB and TEXT will be rejected (this would be the default)
YES: TEXT -> VARCHAR. BLOB -> ERROR (until a BIN type be added to CONNECT)
SKIP: when using discovery, the incompatible data type columns will be skipped.
In all cases, warnings shall be issued. Remains the issue of what VARCHAR length should be given for TEXT columns.
What do you think about this?
I think that is an interesting idea. I like it.
What is holding back CONNECT from having a binary data type? In MariaDB, VARBINARY(...) is equivalent to VARCHAR(...) CHARACTER SET BINARY. (VARCHAR with BINARY character set is converted to VARBINARY.) Maybe CONNECT could simply do something like that? It looks like CONNECT doesn't worry much about character sets anyway (maybe I am wrong), so perhaps this would be pretty straightforward?
OK, I'll work on it.
About BIN data type, it's just the burden of implementing it.
Two new system variables are added for CONNECT:
connect_type_conv: enum (no, yes,or skip) defaults to no.
connect_conv_size: integer defaults to 8192.
In the case of TEXT columns, the handling depends on the values given to the connect_type_conv and connect_conv_size system variables. 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: BLOB is currently not converted until a TYPE_BIN type is added to CONNECT. However, the SKIP option also applies to BLOB columns.
The current documentation does not say anything explicitly about the TEXT/BLOB types.
It must and will be updated to take care of this.
Meanwhile, this is the current handling provided by CONNECT:
1) For all file based types, TEXT and BLOB are not supported.
2) A "conversion" is done for the MYSQL table type (and MYSQL based PROXY, when the target able of a PROXY table is a MYSQL table) This means that in the CONNECT table a VARCHAR column must match the target table TEXT/BLOB column. This is what Discovery does. During READ or WRITE the text is exchanged between the CONNECT table column and the target column with eventual truncation (no warning)
3) The error above comes from the length value that was set for the VARCHAR column in the discovery process. According to some MySQL documentation the max length is 65535. However, this is not true and depends on many reducing factors: the record max size, the space needed by other columns, and the collation (when specifying a multi-bytes character set)
4) A similar problem occurs for ODBC tables.
What can be done? Perhaps making Discovery refuse to do the conversion or set the length to a smaller value (but which one?)
Waiting for, the solution is to manually specify the columns in the CREATE TABLE statement. For instance, the above example can be solved doing:
CREATE TABLE xt1(
id INT(10) UNSIGNED NOT NULL,
b VARCHAR(65528)
) ENGINE=connect TABLE_TYPE=proxy TABNAME=t1;
The issue is that, the way it is implemented, CONNECT cannot directly pass the text from the target table column to the CONNECT column but must internally pass by a CONNECT data type.
In CONNECT internals, there no limitation to the STRING type but a CONNECT engine table is in
fact a MariaDB table this is what causes the limitation.