[MDEV-11234] connect storage engine unable to read escape character in quote from CSV Created: 2016-11-04  Updated: 2016-11-07  Resolved: 2016-11-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.18
Fix Version/s: 10.0.29, 10.1.20, 10.2.3

Type: Bug Priority: Minor
Reporter: Daniel Luo Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7.2


 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.

 Comments   
Comment by Elena Stepanova [ 2016-11-04 ]

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/

Comment by Daniel Luo [ 2016-11-05 ]

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?

Comment by Elena Stepanova [ 2016-11-05 ]

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

Comment by Olivier Bertrand [ 2016-11-05 ]

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.

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