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

connect storage engine unable to read escape character in quote from CSV

Details

    Description

      The table is created as follows

      create table audit_log (
      		 
          logtime char(17),  
          serverhost varchar(128), 
          username varchar(128), 
          host varchar(128), 
          connectionid int unsigned,  
          queryid int unsigned,  
          operation varchar(128), 
          databasename varchar(128), 
          object varchar(512), 
          retcode int unsigned
      		 
      )  
          engine=CONNECT table_type=CSV 
          file_name='/home/mysql/test/server_audit.log' 
          data_charset='utf8' QCHAR='''' quoted=1;

      When reading the following line of data

       20161102 10:24:41,luovm1,root,localhost,5,17,QUERY,,'load data infile \'discounts.csv\'',1046

      There is the following error

      > select * from audit_log limit 20;
      		 
      ERROR 1296 (HY000): Got error 122 'Missing field 9 in audit_log1 line 20' from CONNECT

      Any insights will be appreciated.

      Attachments

        Activity

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

          Apparently, single quotes should be escaped like '' rather than \'.
          At least the following note suggests so:

          The fields between quotes are read and the quotes discarded. On writing, fields will be quoted only if they contain the separator character or begin with the quoting character. If they contain the quoting character, it will be doubled.

          https://mariadb.com/kb/en/mariadb/connect-csv-and-fmt-table-types/

          elenst Elena Stepanova added a comment - Apparently, single quotes should be escaped like '' rather than \' . At least the following note suggests so: The fields between quotes are read and the quotes discarded. On writing, fields will be quoted only if they contain the separator character or begin with the quoting character. If they contain the quoting character, it will be doubled . https://mariadb.com/kb/en/mariadb/connect-csv-and-fmt-table-types/
          bamboowind Daniel Luo added a comment -

          Elena, thank you for formatting the publishing.
          I`ll publish in correct format.

          In a real CSV file single quotes could be escaped by backslash.
          Actually the example CSV data is the log genrated by the MariaDB audit plugin.
          We`d like to read iin the log with the connect storage engine.

          Do you think it`s possible that support for escape by backslash will be added
          in the futurre release of MariaDB?

          bamboowind Daniel Luo added a comment - Elena, thank you for formatting the publishing. I`ll publish in correct format. In a real CSV file single quotes could be escaped by backslash. Actually the example CSV data is the log genrated by the MariaDB audit plugin. We`d like to read iin the log with the connect storage engine. Do you think it`s possible that support for escape by backslash will be added in the futurre release of MariaDB?
          elenst Elena Stepanova added a comment -

          It sounds reasonable to me, I'll leave it to the Connect engine developer bertrandop to decide.

          elenst Elena Stepanova added a comment - It sounds reasonable to me, I'll leave it to the Connect engine developer bertrandop to decide.
          bertrandop Olivier Bertrand added a comment - - edited

          Ok, I have added the possibility to escape the quoted character by backslash even it is not what is recommended for CSV files.
          Note that this does not imply that the backslash is recognized as an escape character except for the quoted character. For instance the field:

          "My name is \\Trinita\\ !"

          will be displayed as:

          My name is \\Trinita\\ !

          And the field:

          "My name is \Trinita\"

          Will result in error, the ending quote being mistaken as an internal quote.

          bertrandop Olivier Bertrand added a comment - - edited Ok, I have added the possibility to escape the quoted character by backslash even it is not what is recommended for CSV files. Note that this does not imply that the backslash is recognized as an escape character except for the quoted character. For instance the field: "My name is \\Trinita\\ !" will be displayed as: My name is \\Trinita\\ ! And the field: "My name is \Trinita\" Will result in error, the ending quote being mistaken as an internal quote.

          People

            bertrandop Olivier Bertrand
            bamboowind Daniel Luo
            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.