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

CONNECT storage engine should be more verbose

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.10
    • 10.0.12
    • None
    • CentOS 6.4 x86_64

    Description

      When adding a connect table from multiple files, it prints an error that it could be improved:

      create table test (
      		 
      	Filename VARCHAR(100) NOT NULL default '' special=FILEID,
      		 
      	a varchar(25) not null default '',
      		 
      	b varchar(80) not null default '',
      		 
      	c varchar(10) not null default '',
      		 
      	d varchar(10) not null default '',
      		 
      	e varchar(10) not null default '',
      		 
      	f varchar(10) not null default '',
      		 
      	g varchar(10) not null default '',
      		 
      	h varchar(10) not null default '',
      		 
      	i varchar(10) not null default ''
      		 
      )
      		 
      engine=CONNECT
      		 
      table_type=CSV
      		 
      file_name='/tmp/FOLDER/PREC_*.txt'
      		 
      multiple=1,
      		 
      sep_char='|';

      As soon I try to get some data, it prints an error with no clue to what relates to:

      SELECT * FROM `test` LIMIT 1000;
      		 
      /* SQL Error (1296): Got error 3 'Invalid null offset value for a CSV table' from CONNECT */

      If I try do add the table with just one file, it works OK.

      I suspect there's some file without data in proper format..

      Could you add some more verbosity in ha_connect::GetTDB as to which filename relates to?

      Best regards,
      António Fernandes

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bertrandop Olivier Bertrand added a comment -

          This was a bug caused by the special column FILEID that was given an offset of 0 wrongly tested later. Now this test skips special columns.

          Offset of CSV table columns are the rank of the corresponding field in the file starting at 1. For instance if you create a CSV table on the file:

          id,errno,msg
          		 
          25,215,error

          and you are willing to skip the errno column, you should create it as:

          create table t1 (
          		 
          id int not null,
          		 
          msg char(12) flag=3)
          		 
          engine=connect table_type=CSV file_name='foo.csv' header=1;

          Normally, CONNECT affects offsets by default starting at 1 and therefore an offset of 0 is invalid.

          bertrandop Olivier Bertrand added a comment - This was a bug caused by the special column FILEID that was given an offset of 0 wrongly tested later. Now this test skips special columns. Offset of CSV table columns are the rank of the corresponding field in the file starting at 1. For instance if you create a CSV table on the file: id,errno,msg 25,215,error and you are willing to skip the errno column, you should create it as: create table t1 ( id int not null , msg char (12) flag=3) engine= connect table_type=CSV file_name= 'foo.csv' header=1; Normally, CONNECT affects offsets by default starting at 1 and therefore an offset of 0 is invalid.
          ampf Antonio Fernandes added a comment -

          Hi Olivier,

          I've tested with 10.0.11 and the "table crash" problem is now OK.

          I would use this ticket to suggest also a change of behavior: when a data source has some records "scanned" (read) that don't comply with the layout, it would be better to throw a warning instead of error (or this behavior could be set by a variable):

          SELECT * FROM `db`.`test` LIMIT 1000;
          		 
          /* SQL Error (1296): Got error 122 'Missing field 9 in test line 1 (/tmp/file1.txt)' from CONNECT */
          		 
          /* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0,000 sec. */

          That way, we could still get some data from some ill formated datasources.

          What do you think?

          Regards,
          António

          ampf Antonio Fernandes added a comment - Hi Olivier, I've tested with 10.0.11 and the "table crash" problem is now OK. I would use this ticket to suggest also a change of behavior: when a data source has some records "scanned" (read) that don't comply with the layout, it would be better to throw a warning instead of error (or this behavior could be set by a variable): SELECT * FROM `db`.`test` LIMIT 1000; /* SQL Error (1296): Got error 122 'Missing field 9 in test line 1 (/tmp/file1.txt)' from CONNECT */ /* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0,000 sec. */ That way, we could still get some data from some ill formated datasources. What do you think? Regards, António
          bertrandop Olivier Bertrand added a comment -

          This issue is already addressed by the MAXERR and ACCEPT create table options.

          bertrandop Olivier Bertrand added a comment - This issue is already addressed by the MAXERR and ACCEPT create table options.
          ampf Antonio Fernandes added a comment -

          Sorry Olivier
          Didn't find that within docs (https://mariadb.com/kb/en/creating-and-dropping-connect-tables/)... I'm browsing through the code right now.

          Best regards,
          António

          ampf Antonio Fernandes added a comment - Sorry Olivier Didn't find that within docs ( https://mariadb.com/kb/en/creating-and-dropping-connect-tables/ )... I'm browsing through the code right now. Best regards, António
          bertrandop Olivier Bertrand added a comment -

          Antonio,
          See Bad record error processing
          in (https://mariadb.com/kb/en/connect-table-types-data-files/)
          after the FMT table type description.
          Regards,
          Olivier

          bertrandop Olivier Bertrand added a comment - Antonio, See Bad record error processing in ( https://mariadb.com/kb/en/connect-table-types-data-files/ ) after the FMT table type description. Regards, Olivier

          People

            bertrandop Olivier Bertrand
            ampf Antonio Fernandes
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.