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

EITS data is lost after failed attempt to CREATE OR REPLACE table

Details

    Description

      Quite possibly it is intentional and/or unavoidable, but if so, it should probably reflected in MTR tests, and I couldn't see it.

      create table t (a int primary key);
      		 
      insert into t values (1),(2);
      		 
      analyze table t persistent for all;
      		 
      select count(*) from mysql.table_stats where table_name = 't';
      		 
      select count(*) from mysql.index_stats where table_name = 't';
      		 
      select count(*) from mysql.column_stats where table_name = 't';
      		 
       
      		 
      --error ER_DUP_ENTRY
      		 
      create or replace table t (b int primary key) as select 1 as b union all select 1;
      		 
      select * from t;
      		 
       
      		 
      select count(*) from mysql.table_stats where table_name = 't';
      		 
      select count(*) from mysql.index_stats where table_name = 't';
      		 
      select count(*) from mysql.column_stats where table_name = 't';
      		 
       
      		 
      drop table t;

      bb-main-monty 2931f8e3a7f4871ef2a82262dc78d5162837b738

      		 
      create or replace table t (b int primary key) as select 1 as b union all select 1;
      		 
      ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
      		 
      select * from t;
      		 
      a
      		 
      1
      		 
      2
      		 
      select count(*) from mysql.table_stats where table_name = 't';
      		 
      count(*)
      		 
      0
      		 
      select count(*) from mysql.index_stats where table_name = 't';
      		 
      count(*)
      		 
      0
      		 
      select count(*) from mysql.column_stats where table_name = 't';
      		 
      count(*)
      		 
      0

      So, CREATE OR REPLACE is atomic as expected, the table and its contents remain intact, but the statistical data is gone.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25292 Atomic CREATE OR REPLACE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Also, InnoDB persistent statistics in the same case gets a new "last update" date 

            create table t (a int primary key) engine=InnoDB;
            		 
            insert into t values (1),(2);
            		 
            select table_name, index_name, last_update from mysql.innodb_index_stats where table_name = 't';
            		 
            table_name	index_name	last_update
            		 
            t	PRIMARY	2025-04-07 00:28:43
            		 
            t	PRIMARY	2025-04-07 00:28:43
            		 
            t	PRIMARY	2025-04-07 00:28:43
            		 
            select table_name, last_update from mysql.innodb_table_stats where table_name = 't';
            		 
            table_name	last_update
            		 
            t	2025-04-07 00:28:43
            		 
            create or replace table t (b int primary key) engine=InnoDB as select 1 as b union all select 1;
            		 
            ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
            		 
            select * from t;
            		 
            a
            		 
            1
            		 
            2
            		 
            select table_name, index_name, last_update from mysql.innodb_index_stats where table_name = 't';
            		 
            table_name	index_name	last_update
            		 
            t	PRIMARY	2025-04-07 00:28:45
            		 
            t	PRIMARY	2025-04-07 00:28:45
            		 
            t	PRIMARY	2025-04-07 00:28:45
            		 
            select table_name, last_update from mysql.innodb_table_stats where table_name = 't';
            		 
            table_name	last_update
            		 
            t	2025-04-07 00:28:45

            elenst Elena Stepanova added a comment - Also, InnoDB persistent statistics in the same case gets a new "last update" date create table t (a int primary key) engine=InnoDB; insert into t values (1),(2); select table_name, index_name, last_update from mysql.innodb_index_stats where table_name = 't'; table_name index_name last_update t PRIMARY 2025-04-07 00:28:43 t PRIMARY 2025-04-07 00:28:43 t PRIMARY 2025-04-07 00:28:43 select table_name, last_update from mysql.innodb_table_stats where table_name = 't'; table_name last_update t 2025-04-07 00:28:43 create or replace table t (b int primary key) engine=InnoDB as select 1 as b union all select 1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' select * from t; a 1 2 select table_name, index_name, last_update from mysql.innodb_index_stats where table_name = 't'; table_name index_name last_update t PRIMARY 2025-04-07 00:28:45 t PRIMARY 2025-04-07 00:28:45 t PRIMARY 2025-04-07 00:28:45 select table_name, last_update from mysql.innodb_table_stats where table_name = 't'; table_name last_update t 2025-04-07 00:28:45
            elenst Elena Stepanova added a comment -

            userstat statistics also gets reset.

            elenst Elena Stepanova added a comment - userstat statistics also gets reset.

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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