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

problem with 10.2.11 server crashing when executing stored procedure

Details

    • 10.1.31, 10.2.13

    Description

      Our solution follows a model which maintains most of the database manipulation logic in stored procedures.

      When I upgraded from 10.2.9 to 10.2.11 I immediately got a failure in existing established code. This code also continues to function on 10.1.28 without problem.

      I have prepared a database dump and a script which create a repeatable error.

      to avoid shipping all of the code in the database I cut down the number of procedures shipped to a minimum, and the problem is now occurring in a different procedure so it looks like a general error rather than something that is specific to a partiiuclar error in our code.

      I dont want to load up the database dump onto a public web site so can I provide that separately when this bug is picked up.

      I have attached below the server.cnf file, the error log associated with the current repeatable crash plus a couple of other error logs from previous tests, plus the script to generate this problem.

      To recreate:

      • create a database ipswichdb;
      • create a user ipswichdba and essentially grant them super user administrator status to the database.
      • mysql -u ipswichdb -p <ip_2018_01_02_togo.sql
      • mysql -u ipswichdba -p <ip_error_gen.sql

      the error files below I generated by using first deleting my current error log and then doing a systemctl restart mariadb after loading the database and before running the test script.

      Also I actually run the test script in Heidi, but I get the same effect whether its hedi or a more complex java app that I am running that normally triggers the problem.

      Using the 2.1.2 java connector

      Attachments

        1. bug_error_2.log
          233 kB
        2. bug_error.log
          355 kB
        3. error_mariadb.log
          13 kB
        4. ip_error_gen.sql
          0.1 kB
        5. server.cnf
          2 kB
        6. stacktrace.txt
          2 kB

        Issue Links

          Activity

            The large value of thd->select_number is not garbage. It comes from sp_head::execute in sp_head.cc:

              /*
                 Normally the counter is not reset between parsing and first execution,
                 but it is possible in case of error to have parsing on one CALL and
                 first execution (where VIEW will be parsed and added). So we store the
                 counter after parsing and restore it before execution just to avoid
                 repeating SELECT numbers.
             
                 Other problem is that it can be more SELECTs parsed in case of fixing
                 error causes previous interruption of the SP. So it is save not just
                 assign old value but add it.
               */
              thd->select_number+= m_select_number;
            

            This was introduced in https://github.com/MariaDB/server/commit/0f43279cc472d82859960f5057e0ec98428459ee .

            EXPLAIN data structure code expects select_number values to be continuous and small.

            psergei Sergei Petrunia added a comment - The large value of thd->select_number is not garbage. It comes from sp_head::execute in sp_head.cc: /* Normally the counter is not reset between parsing and first execution, but it is possible in case of error to have parsing on one CALL and first execution (where VIEW will be parsed and added). So we store the counter after parsing and restore it before execution just to avoid repeating SELECT numbers.   Other problem is that it can be more SELECTs parsed in case of fixing error causes previous interruption of the SP. So it is save not just assign old value but add it. */ thd->select_number+= m_select_number; This was introduced in https://github.com/MariaDB/server/commit/0f43279cc472d82859960f5057e0ec98428459ee . EXPLAIN data structure code expects select_number values to be continuous and small.

            revision-id: 26dcadf8af745edebbeff44d831380f34d54c3e3 (mariadb-10.1.30-26-g26dcadf8af7)
            parent(s): 524221e7a34d42214e82dd868348b453f2ef1591
            author: Oleksandr Byelkin
            committer: Oleksandr Byelkin
            timestamp: 2018-01-26 17:49:36 +0100
            message:

            MDEV-14857: problem with 10.2.11 server crashing when executing stored procedure

            Counter for select numbering made stored with the statement (before was global)
            So now it does have always accurate value which does not depend on
            interruption of statement prepare by errors like lack of table in
            a view definition.

            —

            sanja Oleksandr Byelkin added a comment - revision-id: 26dcadf8af745edebbeff44d831380f34d54c3e3 (mariadb-10.1.30-26-g26dcadf8af7) parent(s): 524221e7a34d42214e82dd868348b453f2ef1591 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-01-26 17:49:36 +0100 message: MDEV-14857 : problem with 10.2.11 server crashing when executing stored procedure Counter for select numbering made stored with the statement (before was global) So now it does have always accurate value which does not depend on interruption of statement prepare by errors like lack of table in a view definition. —

            revision-id: a0ea3a5822486fa1702d6615f1be30684c3d412f (mariadb-10.1.30-26-ga0ea3a58224)
            parent(s): 524221e7a34d42214e82dd868348b453f2ef1591
            author: Oleksandr Byelkin
            committer: Oleksandr Byelkin
            timestamp: 2018-01-27 11:38:38 +0100
            message:

            MDEV-14857: problem with 10.2.11 server crashing when executing stored procedure

            Counter for select numbering made stored with the statement (before was global)
            So now it does have always accurate value which does not depend on
            interruption of statement prepare by errors like lack of table in
            a view definition.

            —

            sanja Oleksandr Byelkin added a comment - revision-id: a0ea3a5822486fa1702d6615f1be30684c3d412f (mariadb-10.1.30-26-ga0ea3a58224) parent(s): 524221e7a34d42214e82dd868348b453f2ef1591 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-01-27 11:38:38 +0100 message: MDEV-14857 : problem with 10.2.11 server crashing when executing stored procedure Counter for select numbering made stored with the statement (before was global) So now it does have always accurate value which does not depend on interruption of statement prepare by errors like lack of table in a view definition. —
            psergei Sergei Petrunia added a comment - Review comments https://lists.launchpad.net/maria-developers/msg11098.html

            Very interesting to see the development of this fix.

            We have been having persistent problems over the last year when we referenced VIEWS in queries within an EXISTS() statement in stored procedures.

            Hopefully this fix will get to the bottom of this fundamental issue.

            Many thanks.

            nick.denning@diegesis.co.uk Nicholas Denning added a comment - Very interesting to see the development of this fix. We have been having persistent problems over the last year when we referenced VIEWS in queries within an EXISTS() statement in stored procedures. Hopefully this fix will get to the bottom of this fundamental issue. Many thanks.

            People

              sanja Oleksandr Byelkin
              nick.denning@diegesis.co.uk Nicholas Denning
              Votes:
              6 Vote for this issue
              Watchers:
              17 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.