Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.17, 10.1(EOL)
-
None
-
Amazon Linux AMI release 2016.03
Description
I tried to make a CONNECT table to a local CSV file.
The command fails and server goes down every time.
I will attach the csv for reproduction test.
I wanted to set the primary key to the row "zip_code", so the column def was needed beforehand or the command would error.
The CONNECT table works properly only when no column definition is set or all existing columns are defined.
I want to request that PK can be set even where not all columns are known.
MariaDB [(none)]> SET SESSION connect_xtrace=1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> show variables like 'connect_xtrace';
|
+----------------+-------+
|
| Variable_name | Value |
|
+----------------+-------+
|
| connect_xtrace | 1 |
|
+----------------+-------+
|
1 row in set (0.00 sec)
|
|
|
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]> CREATE OR REPLACE TABLE M_Zip (zip_code char(7) character set ascii collate ascii_bin NOT NULL, PRIMARY KEY(zip_code)) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='M_Zip.csv' HEADER=1 SEP_CHAR=',' QUOTED=3;
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [test]> SHOW CREATE TABLE M_Zip;
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 2
|
Current database: test
|
|
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| M_Zip | CREATE TABLE `M_Zip` (
|
`zip_code` char(7) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
PRIMARY KEY (`zip_code`)
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`=CSV `FILE_NAME`='M_Zip.csv' `HEADER`=1 `SEP_CHAR`=',' `QUOTED`=3 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.01 sec)
|
The report of my.err is below.
New CONNECT 0x7fb0e1add820, table: M_Zip
|
create: this=0x7fb0e1add820 thd=0x7fb0e3a6e008 xp=0x7fb0e1a7d080 g=0x7fb0e1a69000 sqlcom=1 name=M_Zip
|
xchk=(nil) createas=0
|
Getting created index 1 info
|
XTAB: making new TABLE M_Zip (null)
|
Getting created index 1 info
|
GetFileLength: fn=/data/mysql/./test/M_Zip.csv h=43
|
File length=19028
|
GetFileLength: fn=/data/mysql/./test/M_Zip.csv h=43
|
File length=19028
|
Estimating lines len=19028 ending=1/nEstimatedLength: Fields=0 Columns=(nil)
|
avglen=1 MaxSize19028
|
ColDB: am=34 colname=zip_code tabname=M_Zip num=0
|
cdp(1).Name=zip_code cp=(nil)
|
making new CSVCOL C1 zip_code at 0x7fb047800590
|
colp=0x7fb047800590
|
DOS OpenDB: tdbp=0x7fb047800478 tdb=R1 use=2 mode=10
|
PlugOpenFile: fname=/data/mysql/./test/M_Zip.csv ftype=rb
|
dbuserp=0x7fb0e182b020
|
fop=0x7fb0e182ee80
|
fp=0x7fb0478006b0
|
returning fop=0x7fb0e182ee80
|
File /data/mysql/./test/M_Zip.csv open Stream=0x7fb0e182ee80 mode=rb
|
SubAllocating a buffer of 16 bytes
|
OpenDos: R1 mode=10 To_Line=0x7fb047800718
|
File length=19028
|
ColDB: am=34 colname=zip_code tabname=M_Zip num=0
|
cdp(1).Name=zip_code cp=0x7fb047800590
|
colp=0x7fb047800590
|
File length=19028
|
Estimating lines len=19028 ending=1/nEstimatedLength: Fields=1 Columns=0x7fb047800590
|
avglen=1 MaxSize19028
|
XINDEX Make: n=19028
|
KCOL(0x7fb047813210) Init: col=zip_code n=19028 type=1 sm=1
|
AVB: mp=0x7fb047813350 type=1 nval=19028 len=7 check=1 blank=1
|
170227 19:45:22 mysqld_safe Number of processes running now: 0
|
170227 19:45:22 mysqld_safe mysqld restarted
|
Attachments
Activity
There is a bug indeed causing a crash in case of wrong declaration. Here there are two problems:
- The field length must include evental quotes
- The LRECL must be large enough to contain the largest file record, including eventual header
This cannot be calculated when all columns are not declared and must be specified.
Therefore the correct create table is:
CREATE OR REPLACE TABLE M_Zip (
|
zip_code char(9) character set ascii collate ascii_bin NOT NULL,
|
PRIMARY KEY(zip_code))
|
ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='M_Zip.csv'
|
HEADER=1 SEP_CHAR=',' LRECL=30 QUOTED=3;
|
Waiting for a fix for erroneous cases, this should work as a turnaround.
Thank you, table was successfully created.
I also think erroneous cases will happen frequently.
By the way, is it possible for the CREATE TABLE ENGINE=CONNECT to work like CREATE TABLE SELECT.
I mean can the table definition be created using the CSV file or MySQL table even when some columns are already defined.
It would be helpful to set PK (or INDEX ) or to set definition of important columns.
Yes, CREATE TABLE SELECT works for CONNECT tables.
However, I am not sure to understand your second question. Why not trying it and tell me what happen?
Sorry, my request was confusing.
In the example above the CSV file contains two columns "zip_code" and "jis_x0401x402".
When the "zip_code" is defined in the CREATE TABLE command, the "jis_x0401x402" column is not created automatically.
However when no columns are defined, both columns are read from the CSV file and created.
Can the command work so "jis_x0401x402" column (column not defined in command) is created automatically?
No. Retrieving column definitions is done using the MariaDB discovery feature. However, the handler assisted_discovery function_ is called from MariaDB only when no column definition is provided.
Meanwhile you can create your table giving no column definition, then make all index you want using create index or alter table.
Thanks for the report and test case.
Also reproducible on the current 10.1 (88b5eedef2b).