[MDEV-12142] server goes down when creating CONNECT table Created: 2017-02-27  Updated: 2017-03-11  Resolved: 2017-03-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.17, 10.1
Fix Version/s: 10.1.22, 10.2.5, 10.0.31

Type: Bug Priority: Critical
Reporter: Takuya Aoki (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

Amazon Linux AMI release 2016.03

Attachments: Microsoft Word M_Zip.csv    

 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



 Comments   
Comment by Elena Stepanova [ 2017-03-03 ]

Thanks for the report and test case.

Also reproducible on the current 10.1 (88b5eedef2b).

Comment by Olivier Bertrand [ 2017-03-03 ]

There is a bug indeed causing a crash in case of wrong declaration. Here there are two problems:

  1. The field length must include evental quotes
  2. 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.

Comment by Takuya Aoki (Inactive) [ 2017-03-06 ]

Thank you, table was successfully created.
I also think erroneous cases will happen frequently.

Comment by Takuya Aoki (Inactive) [ 2017-03-08 ]

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.

Comment by Olivier Bertrand [ 2017-03-08 ]

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?

Comment by Takuya Aoki (Inactive) [ 2017-03-09 ]

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?

Comment by Olivier Bertrand [ 2017-03-09 ]

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.

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