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

Hide specific warnings from SHOW WARNINGS

Details

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

    Description

      Hi,

      in [1], I presented a use-case where one could want to "ignore" specific SQL warnings. The use-case is as follow: doing an "INSERT IGNORE" which could result in "Duplicate entries" that are not important.

      [1]: http://jfg-mysql.blogspot.com/2017/01/do-not-ignore-warnings-in-mysql-mariadb.html

      In this use-case, not looking at warnings is bad as another warning could be produced. Filtering the output of SHOW WARNINGS is not satisfactory as it needs a round-trip to the server and extra work in the application. The best way to achieve this would be to not report (hide) warnings for code 1062. Having a generic way to do this is the object of this feature request.

      The specific way this is implemented does not matter to me. I would be ok with an optimizer hint and/or with a session variable (and probably a corresponding global variable). In both cases, the hint and variable could specify a comma separated list of warnings numbers that should be hidden. I have a preference for the optimizer hint as this does not need a round-trip to the server but I will leave the details to the implementer.

      Hidden warnings should not be reported by SHOW WARNINGS, by the “warning_count” system variable, and by SHOW COUNT(*) WARNINGS. Moreover, after executing a statement, the number of warnings reported should be zero is all warnings produced are hidden.

      If we hide warnings, we might also want a way to show them. Maybe a SHOW ALL WARNINGS, a “all_warning_count” system variable, and a SHOW COUNT(*) ALL WARNINGS could also be implemented. For SHOW ALL WARNINGS, if the number of warnings produced is higher than max_error_count, we probably want to drop the warnings that are hidden.

      Many thanks,

      JFG

      Attachments

        Activity

          jeanfrancois.gagne Jean-François Gagné added a comment - Upstream Bug#84557: http://bugs.mysql.com/bug.php?id=84557
          serg Sergei Golubchik added a comment - - edited

          Assorted thoughts:

          • SHOW ALL WARNINGS is not needed, one can do SET STATEMENT hide_warnings='' SHOW WARNINGS
          • Alternatively, instead of a variable, we can implement SHOW WARNINGS ... WHERE (not unlike other SHOW ... WHERE statements) and let the user filter by error number, warning text, whatever.
          • Or create an information_schema table for that, it could have more columns — all that diagnostics area has.
            • it would also allow to use joins, e.g. one could create a table with all warning numbers that should be hidden — arguably easier to maintain than a variable, if there're many warnings to hide.
            • and one could create a couple of views with exactly sets of warnings she wanted to see
          • on the other hand, only the server variable could make the after-statement status to count only important warnings, ignoring hidden ones.
          serg Sergei Golubchik added a comment - - edited Assorted thoughts: SHOW ALL WARNINGS is not needed, one can do SET STATEMENT hide_warnings='' SHOW WARNINGS Alternatively, instead of a variable, we can implement SHOW WARNINGS ... WHERE (not unlike other SHOW ... WHERE statements) and let the user filter by error number, warning text, whatever. Or create an information_schema table for that, it could have more columns — all that diagnostics area has. it would also allow to use joins, e.g. one could create a table with all warning numbers that should be hidden — arguably easier to maintain than a variable, if there're many warnings to hide. and one could create a couple of views with exactly sets of warnings she wanted to see on the other hand, only the server variable could make the after-statement status to count only important warnings, ignoring hidden ones.

          People

            Unassigned Unassigned
            jeanfrancois.gagne Jean-François Gagné
            Votes:
            3 Vote for this issue
            Watchers:
            7 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.