[MDEV-6187] CONNECT storage engine should be more verbose Created: 2014-04-29  Updated: 2014-05-18  Resolved: 2014-05-09

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: 10.0.12

Type: Bug Priority: Major
Reporter: Antonio Fernandes Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine
Environment:

CentOS 6.4 x86_64


 Description   

When adding a connect table from multiple files, it prints an error that it could be improved:

create table test (
 
	Filename VARCHAR(100) NOT NULL default '' special=FILEID,
 
	a varchar(25) not null default '',
 
	b varchar(80) not null default '',
 
	c varchar(10) not null default '',
 
	d varchar(10) not null default '',
 
	e varchar(10) not null default '',
 
	f varchar(10) not null default '',
 
	g varchar(10) not null default '',
 
	h varchar(10) not null default '',
 
	i varchar(10) not null default ''
 
)
 
engine=CONNECT
 
table_type=CSV
 
file_name='/tmp/FOLDER/PREC_*.txt'
 
multiple=1,
 
sep_char='|';

As soon I try to get some data, it prints an error with no clue to what relates to:

SELECT * FROM `test` LIMIT 1000;
 
/* SQL Error (1296): Got error 3 'Invalid null offset value for a CSV table' from CONNECT */

If I try do add the table with just one file, it works OK.

I suspect there's some file without data in proper format..

Could you add some more verbosity in ha_connect::GetTDB as to which filename relates to?

Best regards,
António Fernandes

 Comments   
Comment by Olivier Bertrand [ 2014-05-09 ]

This was a bug caused by the special column FILEID that was given an offset of 0 wrongly tested later. Now this test skips special columns.

Offset of CSV table columns are the rank of the corresponding field in the file starting at 1. For instance if you create a CSV table on the file:

id,errno,msg
 
25,215,error

and you are willing to skip the errno column, you should create it as:

create table t1 (
 
id int not null,
 
msg char(12) flag=3)
 
engine=connect table_type=CSV file_name='foo.csv' header=1;

Normally, CONNECT affects offsets by default starting at 1 and therefore an offset of 0 is invalid.

Comment by Antonio Fernandes [ 2014-05-15 ]

Hi Olivier,

I've tested with 10.0.11 and the "table crash" problem is now OK.

I would use this ticket to suggest also a change of behavior: when a data source has some records "scanned" (read) that don't comply with the layout, it would be better to throw a warning instead of error (or this behavior could be set by a variable):

SELECT * FROM `db`.`test` LIMIT 1000;
 
/* SQL Error (1296): Got error 122 'Missing field 9 in test line 1 (/tmp/file1.txt)' from CONNECT */
 
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0,000 sec. */

That way, we could still get some data from some ill formated datasources.

What do you think?

Regards,
António

Comment by Olivier Bertrand [ 2014-05-18 ]

This issue is already addressed by the MAXERR and ACCEPT create table options.

Comment by Antonio Fernandes [ 2014-05-18 ]

Sorry Olivier
Didn't find that within docs (https://mariadb.com/kb/en/creating-and-dropping-connect-tables/)... I'm browsing through the code right now.

Best regards,
António

Comment by Olivier Bertrand [ 2014-05-18 ]

Antonio,
See Bad record error processing
in (https://mariadb.com/kb/en/connect-table-types-data-files/)
after the FMT table type description.
Regards,
Olivier

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