Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12142

server goes down when creating CONNECT table

Details

    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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report and test case.

          Also reproducible on the current 10.1 (88b5eedef2b).

          elenst Elena Stepanova added a comment - Thanks for the report and test case. Also reproducible on the current 10.1 (88b5eedef2b).
          bertrandop Olivier Bertrand added a comment -

          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.

          bertrandop Olivier Bertrand added a comment - 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.
          takuya Takuya Aoki (Inactive) added a comment - - edited

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

          takuya Takuya Aoki (Inactive) added a comment - - edited Thank you, table was successfully created. I also think erroneous cases will happen frequently.
          takuya Takuya Aoki (Inactive) added a comment -

          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.

          takuya Takuya Aoki (Inactive) added a comment - 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.
          bertrandop Olivier Bertrand added a comment -

          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?

          bertrandop Olivier Bertrand added a comment - 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?
          takuya Takuya Aoki (Inactive) added a comment -

          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?

          takuya Takuya Aoki (Inactive) added a comment - 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?
          bertrandop Olivier Bertrand added a comment -

          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.

          bertrandop Olivier Bertrand added a comment - 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 .

          People

            bertrandop Olivier Bertrand
            takuya Takuya Aoki (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.