[MDEV-26730] SQL Error (1296): Got error 122 'Field 22 too long for my_column line 1292 of my_file.csv' from CONNECT Created: 2021-09-30  Updated: 2022-08-26  Resolved: 2022-08-26

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.5.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Peter Schulz Assignee: Andrew Hutchings
Resolution: Fixed Votes: 0
Labels: Documentation
Environment:

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

 Comments   
Comment by Andrew Hutchings [ 2022-08-26 ]

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.

Comment by Andrew Hutchings [ 2022-08-26 ]

Updated the following document to add note about lrecl:

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

Generated at Thu Feb 08 09:47:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.