Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.9
-
Windows 10
Description
When I load a CSV with a lot of large text columns, a CONNECT table is created and I can query the table. Some rows however, are returning the following error:
SQL Error (1296): Got error 122 'Field 22 too long for my_column line 1292 of my_file.csv' from CONNECT
|
HeidiSQL, SQL command line and PDO are all giving the same error. The error log shows:
rnd_next CONNECT: Field 22 too long for my_column line 1292 of my_file.csv
|
This is my create table statement:
create or replace table my_table |
engine=connect |
table_type=csv
|
file_name='C:\\my_folder\\my_file.csv' |
header=1
|
sep_char=',' |
quoted=1
|
Column my_column is auto created as varchar(3329). But when I scan the CSV file, the row on line 1292 contains a text string with a length of 6632.
Adding the column length explicitly solves the issue. I prefer however to let MariaDB create the column definitions as this simplyfies the upload process.
What can I do? Are there any parameters that I can change? Could this be Windows related? Any help is appreciated.
Thanks,
Peter
Attachments
Activity
The reason for this is that the CSV parser in CONNECT engine only reads a maximum 4096 bytes for a row's data by default, which means that when determining row length that row would have been truncated. If it was quoted you would have got an "Unbalanced quote" error.
To resolve this you need to add the lrecl parameter to your CREATE TABLE and set it to something higher than your maximum row length. Something like:
create or replace table my_table |
engine=connect |
table_type=csv
|
file_name='C:\\my_folder\\my_file.csv' |
header=1
|
sep_char=',' |
quoted=1
|
lrecl=8192
|
I will update the documentation for this accordingly.
Updated the following document to add note about lrecl:
https://mariadb.com/kb/en/connect-csv-and-fmt-table-types/