[MDEV-6166] Store all of LOAD DATA INFILE's warnings Created: 2014-04-24  Updated: 2023-03-06

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Ralf Neubauer Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: load_data

Issue Links:
Duplicate
duplicates MDEV-6167 Store all of LOAD DATA INFILE's warnings Closed
Relates
relates to MDEV-13046 Feature Request: Need BADFILE option... Open

 Description   

When we do bulk imports, we need the full list of warnings to:

  • log the data modifications done by MariaDB
  • get statistics about the data quality.

The data is from external sources and often has quality problems, but we want to load it into the database as far as possible with sane types, we don't want to impose strict format checks. But we want to know what got lost.

And we need the speed of LOAD DATA INFILE.

Of course we could try to mimic MariaDB's type and range checks etc., but that is too error prone, the target is too big and moving, so we can only do some basic checks.

show warnings or get diagnostic can only show up to 65535 warnings, and because there can be many warnings per row, we don't know in advance, if we can load the whole file at once or just blocks of 100 lines or so (which is much slower).

So I implemented an algorithm:-
1) block_size = all lines of file
2) load block_size lines
3) if @@warning_count <= 65535 go to next block, else delete block from table, determine new, smaller, block_size using an estimation of warnings per line, goto 2)
...

Why can't LOAD DATA INFILE just log the warnings into a table with a predefined structure or stop loading the file an return the file position when max_error_count warnings occured?

 Comments   
Comment by Ralf Neubauer [ 2014-04-24 ]

There is another thing: We parse the messages for information like affected columns, line numbers, and incriminating values, the rest of the messages (possibly together with the column) is nice as some kind of message class for counting similar messages. Of course this depends on the message language... And not every message contains affected column and line number.

Even with GET DIAGNOSTICS one has to parse the message, and it seems to be too slow to query millions of messages.

Comment by Debjyoti Ghosh [ 2023-02-26 ]

Hi, the issue seems interesting, I would like to work on it, please suggest me on how to get started with it, I have build the server code and have tried to implement the LOAD DATA INFILE statement for imports to mimic the issue.

Comment by Andrew Hutchings [ 2023-03-06 ]

Storing the warnings is not a problem. The problem is actually telling the client the warning count. This is done using two bytes in the protocol layer. Increasing this cause compatibility issues. Changing this will require some thought and design work.

Comment by Ralf Neubauer [ 2023-03-06 ]

Hi,

I partly understand this, but if it would be possible to set max_error_count to a value above 65535, it would – as it always does – just change the behaviour of SHOW WARNINGS, SHOW ERRORS and SHOW COUNT WARNINGS – the two bytes in the protocol could just be capped to 65535 as they always have been – and they can already differ from SHOW COUNT WARNINGS depending on the value of error_count (just in the other direction). warning_count could behave either way, it is not limited to two bytes, I assume.

If a new way of returning the warnings would be introduced, like a table in INFORMATION_SCHEMA or a table you have to create yourself whose name you can set (e.g. LOAD DATA LOCAL INFILE 'file_name' INTO TABLE tbl_name LOG ERRORS INTO TABLE warning_tbl_name or with a session variable error_log_table) then that would be completely independent of said bytes in the protocol and warning_count – the only decision to make would be if SHOW WARNINGS etc. would return an empty result if you redirected the warnings and errors to an explicitly set table.

Mit freundlichen Grüßen

Ralf Neubauer
Referent

Forschungsbereich Arzneimittelinformationssysteme und Analysen
Wissenschaftliches Institut der AOK
AOK-Bundesverband
 
Tel.: 030 34646-2583
Fax: 030 34646-332583
E-Mail: ralf.neubauer@wido.bv.aok.de
 
AOK-Bundesverband
Rosenthaler Straße 31
10178 Berlin
www.wido.de

AOK - Die Gesundheitskasse. Gesundheit in besten Händen.

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