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

#sql-backup tables are visible upon CREATE OR REPLACE and get stuck in S3 storage

Details

    Description

      Run in S3-enabled environment.
      The test case is non-deterministic by nature, although it hits the problem for me every time.
      I only tried with MinIO, with AWS timing may need to be adjusted, depending on how slowly it is.
      Don't put it into the regression suite, create a deterministic one instead.

      In the test case, one connection does CREATE OR REPLACE of an existing S3 table. Another connection is monitoring visible tables. As soon as #sql-backup-... table becomes visible (which is already strange), the monitoring connection crash-restarts the server. After recovery, the temporary table remains permanently in S3.

      # Because it's not easy to drop the garbage tables which are the essense
      # of the report, I re-create the bucket to force-remove the contents.
      # This assumes that I run the test with --mysqld=--s3-bucket=mtr,
      # and MinIO server is already running. Naturally it doesn't belong
      # to the eventual test case for the regression suite
       
      --exec mc alias set local http://127.0.0.1:9000 minio minioadmin
      --exec mc rb --force local/mtr || true
      --exec mc mb local/mtr
       
      show tables;
       
      # This is to clean up S3 in a "normal" situation, when there is no garbage
      drop table if exists t;
       
      --connect (con1,localhost,root,,test)
      CREATE TABLE t (a INT);
      ALTER TABLE t ENGINE=S3;
      --send
        CREATE OR REPLACE TABLE t (b INT);
       
      --connection default
      --let $i= 100
      while ($i)
      {
        --let $tbl= `show tables like '%sql-backup%'`
        if ($tbl != '')
        {  
           --echo #
           --echo # Found a backup table $tbl
           --echo #
           show tables;
           --let $shutdown_timeout= 0
           --source include/restart_mysqld.inc
           --echo #
           --echo # The table is still there
           --echo #
           show tables;
           --echo #
           --echo # .. but cannot be dropped
           --echo #
           --eval drop table if exists `$tbl`
           show tables;
           --let $i= 1
        }
        --dec $i
      }
       
      # Cleanup
      DROP TABLE t;
      

      bb-10.11-midenok 6df00b8559

      connection default;
      #
      # Found a backup table #sql-backup-12beea-5-t
      #
      show tables;
      Tables_in_test
      #sql-backup-12beea-5-t
      t
      # restart
      #
      # The table is still there
      #
      show tables;
      Tables_in_test
      #sql-backup-12beea-5-t
      t
      #
      # .. but cannot be dropped
      #
      drop table if exists `#sql-backup-12beea-5-t`;
      Warnings:
      Note	1051	Unknown table 'test.#sql-backup-12beea-5-t'
      show tables;
      Tables_in_test
      #sql-backup-12beea-5-t
      t
      

      Couldn't reproduce before MDEV-25292.

      Attachments

        Issue Links

          Activity

            Because it's not easy to drop the garbage tables

            Just FYI, this may or may not work, s3.basic uses the below to cleanup the mess:

            --source create_database.inc
            ...
            --replace_result $database database
            ...
            --source drop_database.inc
            

            midenok Aleksey Midenkov added a comment - Because it's not easy to drop the garbage tables Just FYI, this may or may not work, s3.basic uses the below to cleanup the mess: --source create_database.inc ... --replace_result $database database ... --source drop_database.inc
            elenst Elena Stepanova added a comment - - edited

            It doesn't work in this case, I tried that. That is, the S3 trick would seem to work because it creates a unique database every time, so on the new invocation of the test the newly created database would always be empty. But it wouldn't actually clean up at the end, the garbage would still be there. It's easy to see if we add this in the "Cleanup" section of the test case, instead of drop table:

            drop database test;
            create database test;
            show tables in test;
            

            The result is

            drop database test;
            Warnings:
            Note	1051	Unknown table 'test.#sql-backup-223f1f-5-t'
            create database test;
            show tables in test;
            Tables_in_test
            #sql-backup-223f1f-5-t
            

            elenst Elena Stepanova added a comment - - edited It doesn't work in this case, I tried that. That is, the S3 trick would seem to work because it creates a unique database every time, so on the new invocation of the test the newly created database would always be empty. But it wouldn't actually clean up at the end, the garbage would still be there. It's easy to see if we add this in the "Cleanup" section of the test case, instead of drop table : drop database test; create database test; show tables in test; The result is drop database test; Warnings: Note 1051 Unknown table 'test.#sql-backup-223f1f-5-t' create database test; show tables in test; Tables_in_test #sql-backup-223f1f-5-t

            Please review bb-10.11-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.11-midenok

            OK to push after adding comment to the method

            sanja Oleksandr Byelkin added a comment - OK to push after adding comment to the method

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.