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

Temporary table name conflict between sessions

Details

    Description

      If we create a temporary table with the same name within a store procedure, and try to drop some columns in two different sessions running at the same moment, error " Unknown column" may pop.

      Although this is documented in

      https://mariadb.com/kb/en/mariadb/documentation/sql-commands/data-definition/create/create-table/

      "Temporary table names are specific to your session. They may conflict with other temporary tables from other sessions or shadow names of non-temporary tables."

      But this is different behavior with Mysql

      http://dev.mysql.com/doc/refman/5.5/en/create-table.html

      This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

      This is stopper if using Mariadb as a drop in replacement for Mysql, as this require rewrite all SPs with alter temporary statement.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          links to

          Web Link WL#4179: Stored programs: validation of stored program statements

          Activity

            Ascending order - Click to sort in descending order
            View 4 older comments
            elenst Elena Stepanova added a comment -

            Thanks for the data, I was able to reproduce it now. It's a conflict not between sessions, but between several executions of the stored procedures. It's easily repeatable in a single session:

            drop procedure if exists pr;
            		 
             
            		 
            --delimiter |
            		 
            create procedure pr(i int) begin
            		 
              drop table if exists t1;
            		 
              create table t1 (a int, b int);
            		 
              if (i = 1) then alter table t1 drop a;
            		 
              else alter table t1 drop b; 
              end if;
            		 
              select * from t1;
            		 
            end |
            		 
            --delimiter ;
            		 
             
            		 
            call pr(1);
            		 
            call pr(2);

            It used to be an upstream bug, fixed in 5.6.6 by the following revision:

            revno: 3857
            		 
            revision-id: alexander.nozdrin@oracle.com-20120516123641-tnvnhoaimesc8bj9
            		 
            parent: alexander.nozdrin@oracle.com-20120516110343-36sld7c7somcl3z8
            		 
            committer: Alexander Nozdrin <alexander.nozdrin@oracle.com>
            		 
            branch nick: trunk-stage.2
            		 
            timestamp: Wed 2012-05-16 16:36:41 +0400
            		 
            message:
            		 
              WL#4179: Stored programs: validation of stored program statements.
            		 
              
              Briefly, that's the implementation of WL#4179. The idea is to remember
            		 
              the original query for SQL-statement within a Stored Programs and re-parse
            		 
              it when meta-data has changed.

            elenst Elena Stepanova added a comment - Thanks for the data, I was able to reproduce it now. It's a conflict not between sessions, but between several executions of the stored procedures. It's easily repeatable in a single session: drop procedure if exists pr;   --delimiter | create procedure pr(i int ) begin drop table if exists t1; create table t1 (a int , b int ); if (i = 1) then alter table t1 drop a; else alter table t1 drop b; end if ; select * from t1; end | --delimiter ;   call pr(1); call pr(2); It used to be an upstream bug, fixed in 5.6.6 by the following revision: revno: 3857 revision-id: alexander.nozdrin@oracle.com-20120516123641-tnvnhoaimesc8bj9 parent: alexander.nozdrin@oracle.com-20120516110343-36sld7c7somcl3z8 committer: Alexander Nozdrin <alexander.nozdrin@oracle.com> branch nick: trunk-stage.2 timestamp: Wed 2012-05-16 16:36:41 +0400 message: WL#4179: Stored programs: validation of stored program statements. Briefly, that's the implementation of WL#4179. The idea is to remember the original query for SQL-statement within a Stored Programs and re-parse it when meta-data has changed.
            leomkkwan Leo Kwan added a comment -

            Thank you so much on spending time to reproduce this issue and find the root cause. Since there will be relatively long time before 10.1 become production ready. Are there any work around available on this issue so the server may not generate error or random crash?

            Thanks.

            leomkkwan Leo Kwan added a comment - Thank you so much on spending time to reproduce this issue and find the root cause. Since there will be relatively long time before 10.1 become production ready. Are there any work around available on this issue so the server may not generate error or random crash? Thanks.
            serg Sergei Golubchik added a comment -

            There are no crashes, as far as I understand, aren't there?

            serg Sergei Golubchik added a comment - There are no crashes, as far as I understand, aren't there?
            serg Sergei Golubchik added a comment -

            Okay, I will do a simple workaround for 5.5 and 10.0 branches.

            serg Sergei Golubchik added a comment - Okay, I will do a simple workaround for 5.5 and 10.0 branches.
            elenst Elena Stepanova added a comment -

            There are no crashes, as far as I understand, aren't there?

            I didn't observe any crashes while experimenting with it.

            elenst Elena Stepanova added a comment - There are no crashes, as far as I understand, aren't there? I didn't observe any crashes while experimenting with it.

            People

              serg Sergei Golubchik
              leomkkwan Leo Kwan
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.