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

Feature request: add STATEMENT_DIGEST_TEXT

Details

    Description

      STATEMENT_DIGEST_TEXT is a very handy function in MySQL 8 which can be used programmatically and quickly check thousands of SQL statements for validity, without actually executing them, and returning an error on CLI-only statements:

      MS 10.8 (Debug)

      8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('SELECT 1')G
       
      *************************** 1. row ***************************
      STATEMENT_DIGEST_TEXT('SELECT 1'): SELECT ?
      1 row in set (0.00 sec)
       
      8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('INCORRECT QUERY')G
      ERROR 3676 (HY000): Could not parse argument to digest function: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INCORRECT QUERY' at line 1".
      8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('HELP')G
      ERROR 3676 (HY000): Could not parse argument to digest function: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1".
      

      The same function does not exist in MariaDB:

      MD 10.7.0 d552e092c9f3e20da078d1b62b976f629f73d3a4 (Debug)

      10.7.0-dbg>SELECT STATEMENT_DIGEST_TEXT('SELECT 1');
      ERROR 1305 (42000): FUNCTION test.STATEMENT_DIGEST_TEXT does not exist
      

      So whilst the MySQL 8 function can be used to check SQL statements for validity (via yes/no error), MariaDB specific syntax will not be recognized and thus using MySQL 8 for SQL verification is not very helpful.

      Implementing the same would help for testing.

      Attachments

        Issue Links

          Activity

            Roel, can you use PREPARE instead?

            MariaDB [test]> PREPARE p FROM 'SELECT 1';
            Query OK, 0 rows affected (0.002 sec)
            Statement prepared
            MariaDB [test]> PREPARE p FROM 'INCORRECT QUERY';
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INCORRECT QUERY' at line 1
            MariaDB [test]> PREPARE p FROM 'HELP';
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
            

            serg Sergei Golubchik added a comment - Roel , can you use PREPARE instead? MariaDB [test]> PREPARE p FROM 'SELECT 1'; Query OK, 0 rows affected (0.002 sec) Statement prepared MariaDB [test]> PREPARE p FROM 'INCORRECT QUERY'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INCORRECT QUERY' at line 1 MariaDB [test]> PREPARE p FROM 'HELP'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

            serg Fantastic, thank you very much. This seems to work better, and easier.

            Roel Roel Van de Paar added a comment - serg Fantastic, thank you very much. This seems to work better, and easier.

            Perhaps, when/if we implement this feature, this is also an easy way to work it into the code, by using a temporary prepare statement?

            Roel Roel Van de Paar added a comment - Perhaps, when/if we implement this feature, this is also an easy way to work it into the code, by using a temporary prepare statement?
            Roel Roel Van de Paar added a comment - - edited

            Notes to self

            sed "s|'|\\\'|g;s|^|PREPARE p FROM '|;s|$|';|" spider_se_2.sql > test.sql
            

            And test_pquery (~/start) was updated to log errors.

            One issue is that PREPARE will still crash on single-line crashing queries like the one in MDEV-19569. It would be hard to filter all such queries from the source files.

            Next; writing scripting to automate the lot & restart instance when crashes are observed (line-by-line execution and immediate classification into buckets).

            Roel Roel Van de Paar added a comment - - edited Notes to self sed "s|'|\\\'|g;s|^|PREPARE p FROM '|;s|$|';|" spider_se_2.sql > test .sql And test_pquery ( ~/start ) was updated to log errors. One issue is that PREPARE will still crash on single-line crashing queries like the one in MDEV-19569 . It would be hard to filter all such queries from the source files. Next; writing scripting to automate the lot & restart instance when crashes are observed (line-by-line execution and immediate classification into buckets).
            danblack Daniel Black added a comment -

            maxscale has the feature if its portable.

            danblack Daniel Black added a comment - maxscale has the feature if its portable.
            Weijun Huang Weijun Huang added a comment -

            Does this still need to be completed? If so, I want to work on it.

            Weijun Huang Weijun Huang added a comment - Does this still need to be completed? If so, I want to work on it.
            danblack Daniel Black added a comment -

            Also has some good application in assisting tooling to normalize like queries with different constants. So sure Weijun Huang, love to see an implementation.

            danblack Daniel Black added a comment - Also has some good application in assisting tooling to normalize like queries with different constants. So sure Weijun Huang , love to see an implementation.

            Thank you Weijun Huang, I too am looking forward to your work.

            Roel Roel Van de Paar added a comment - Thank you Weijun Huang , I too am looking forward to your work.

            People

              Unassigned Unassigned
              Roel Roel Van de Paar
              Votes:
              2 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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