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

Drop statement not droped in specific circumstances

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.7.7
    • N/A
    • OTHER
    • None
    • Windows 11 22H2
      P41 hynix ssd

    Description

      Below sample statements make not expected error that duplicated records.
      The sample expect 'view > newly made custom temp table > main table'
      but, as the result, duplicated error occured on the main table
      which means, the temp table never dropped clean.

      on windows 11,
      using 'innodb_flush_method=unbuffered'

      -------------------------------
      [Error statement]
      CREATE DEFINER=`root`@`localhost` PROCEDURE `procdure_temp`()
      LANGUAGE SQL
      NOT DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
      BEGIN

      *+ drop TABLE if EXISTS _temp_table;+*
      START TRANSACTION;
      create table if not exists _temp_table SELECT * from table_target LIMIT 1;

      INSERT INTO
      _temp_table
      SELECT
      *
      FROM
      view_for_target_table
      ;
      COMMIT;

      START TRANSACTION;
      delete from table_target;
      INSERT INTO
      table_target
      SELECT
      *
      FROM
      _temp_table;
      commit;
      drop TABLE if exists _temp_table;
      END

      ------------------------
      [avoid error]
      CREATE DEFINER=`root`@`localhost` PROCEDURE `procdure_temp`()
      LANGUAGE SQL
      NOT DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
      BEGIN
      START TRANSACTION;
      create table if not exists _temp_table SELECT * from table_target LIMIT 1;

      • delete from _temp_table;*

      INSERT INTO
      _temp_table
      SELECT
      *
      FROM
      view_for_target_table
      ;
      COMMIT;

      START TRANSACTION;
      delete from table_target;
      INSERT INTO
      table_target
      SELECT
      *
      FROM
      _temp_table;
      commit;
      drop TABLE if exists _temp_table;
      END

      Attachments

        Issue Links

          Activity

            If you need temporary table, why don't you CREATE TEMPORARY TABLE?
            Otherwise, the table you created can be shared in multiple sessions, and this is not what you want, and this can possibly be the reason you get your duplicates.

            wlad Vladislav Vaintroub added a comment - If you need temporary table, why don't you CREATE TEMPORARY TABLE? Otherwise, the table you created can be shared in multiple sessions, and this is not what you want, and this can possibly be the reason you get your duplicates.
            npdmailing@gmail.com Mitchell Lee added a comment -

            I'm FULLY aware what you r talking about.
            This issue is not a kinda conflcition between different thread/connection. I fully aware of it.
            The point is
            the drop not working at the statements point.

            npdmailing@gmail.com Mitchell Lee added a comment - I'm FULLY aware what you r talking about. This issue is not a kinda conflcition between different thread/connection. I fully aware of it. The point is the drop not working at the statements point.
            npdmailing@gmail.com Mitchell Lee added a comment -

            and the CREATE TEMPORARY TABLE usage condition u guys posted is
            'a session related issue of temp table and not garantee to be dropped'
            and etcetera, etcetera, etcetera the so much of restriction and unreliablity
            for under given situation which not meet MariaDB statemtent,
            want an own logic which reilable and confident.
            So, hope to focus on the issue not the workaround.

            npdmailing@gmail.com Mitchell Lee added a comment - and the CREATE TEMPORARY TABLE usage condition u guys posted is 'a session related issue of temp table and not garantee to be dropped' and etcetera, etcetera, etcetera the so much of restriction and unreliablity for under given situation which not meet MariaDB statemtent, want an own logic which reilable and confident. So, hope to focus on the issue not the workaround.

            Ok could you then, please create a standalone reproducible case, put it into an .sql file so it can run from the client, and we'll happily look into it.

            wlad Vladislav Vaintroub added a comment - Ok could you then, please create a standalone reproducible case, put it into an .sql file so it can run from the client, and we'll happily look into it.
            npdmailing@gmail.com Mitchell Lee added a comment -

            1. If call the CREATE TEMPORARY TABLE statemtent in one single connection in a infinite loop(of course same temp table name as the specification). Your team garantee that drop cleanly? None of logical exception expected?

            2. If call the CREATE TEMPORARY TABLE statement by each different connection(but might be a same thread sometime since the user uncontrollerble behavior) each 0.5 ~1.0 second. Your team garantee that drop cleanly? None of logical exception expected?

            npdmailing@gmail.com Mitchell Lee added a comment - 1. If call the CREATE TEMPORARY TABLE statemtent in one single connection in a infinite loop(of course same temp table name as the specification). Your team garantee that drop cleanly? None of logical exception expected? 2. If call the CREATE TEMPORARY TABLE statement by each different connection(but might be a same thread sometime since the user uncontrollerble behavior) each 0.5 ~1.0 second. Your team garantee that drop cleanly? None of logical exception expected?
            npdmailing@gmail.com Mitchell Lee added a comment -

            After upgrade to 10.7.8 and observing for a while,
            it seems fixed by MDEV-30052 since the WINDOW clause in all over the db.
            actuallky say, not fixed, but not occured since no call-stack breaking.
            So I suggest to close for now.

            npdmailing@gmail.com Mitchell Lee added a comment - After upgrade to 10.7.8 and observing for a while, it seems fixed by MDEV-30052 since the WINDOW clause in all over the db. actuallky say, not fixed, but not occured since no call-stack breaking. So I suggest to close for now.

            People

              Unassigned Unassigned
              npdmailing@gmail.com Mitchell Lee
              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.