Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6166

Store all of LOAD DATA INFILE's warnings

Details

    • Task
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • None

    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?

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-6167 Store all of LOAD DATA INFILE's warnings

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-13046 Feature Request: Need BADFILE option like in oracle - LOAD DATA INFILE

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            RalfNeubauer Ralf Neubauer added a comment -

            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.

            RalfNeubauer Ralf Neubauer added a comment - 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.
            Debjyoti Debjyoti Ghosh added a comment -

            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.

            Debjyoti Debjyoti Ghosh added a comment - 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.
            TheLinuxJedi Andrew Hutchings (Inactive) added a comment -

            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.

            TheLinuxJedi Andrew Hutchings (Inactive) added a comment - 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.
            RalfNeubauer Ralf Neubauer added a comment -

            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.

            RalfNeubauer Ralf Neubauer added a comment - 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.

            People

              Unassigned Unassigned
              RalfNeubauer Ralf Neubauer
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.