Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.1.18
-
None
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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. |
The table is created as follows
{code:sql} 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; {code} When reading the following line of data {noformat} 20161102 10:24:41,luovm1,root,localhost,5,17,QUERY,,'load data infile \'discounts.csv\'',1046 {noformat} There is the following error {noformat} > select * from audit_log limit 20; ERROR 1296 (HY000): Got error 122 'Missing field 9 in audit_log1 line 20' from CONNECT {noformat} Any insights will be appreciated. |
| Labels | need_feedback |
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?
| Labels | need_feedback |
It sounds reasonable to me, I'll leave it to the Connect engine developer bertrandop to decide.
| Assignee | Olivier Bertrand [ bertrandop ] |
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.
| issue.field.resolutiondate | 2016-11-05 11:53:13.0 | 2016-11-05 11:53:13.669 |
| Fix Version/s | 10.0.29 [ 22312 ] | |
| Fix Version/s | 10.1.19 [ 22111 ] | |
| Fix Version/s | 10.2.3 [ 22115 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Fix Version/s | 10.1.20 [ 22112 ] | |
| Fix Version/s | 10.1.19 [ 22111 ] |
| Workflow | MariaDB v3 [ 78208 ] | MariaDB v4 [ 151187 ] |
Apparently, single quotes should be escaped like '' rather than \'.
At least the following note suggests so:
https://mariadb.com/kb/en/mariadb/connect-csv-and-fmt-table-types/