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

PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS_TABLES

Details

    Description

      Folks,

      The files from a partitioned table were removed from disk but the rows for each of the files are on the INFORMATION_SCHEMA.INNODB_SYS_TABLES. The customer wants to know how to fix that and he has attached the files to the ticket that helped us to verify what is going on.

      Please, let us know if you need any other information, cheers!

      Attachments

        Issue Links

          Activity

            I can only think of re-creating the fake files and then dropping the table. Like so (file operations of course should be done in a normal way, here they are done from MySQL client for the sake of simplicity):

            Initial table

            MariaDB [test]> use db1;
            Database changed
            MariaDB [db1]> create table t1 (i int) partition by hash(i) partitions 2;
            Query OK, 0 rows affected (0.52 sec)
            

            Remove the table files from the disk

            MariaDB [db1]> select @@datadir;
            +----------------------+
            | @@datadir            |
            +----------------------+
            | /data/bld/10.0/data/ |
            +----------------------+
            1 row in set (0.01 sec)
             
            MariaDB [db1]> system rm /data/bld/10.0/data/db1/t1*
             
            MariaDB [db1]> show tables;
            Empty set (0.01 sec)
            

            MariaDB [db1]> select * from  INFORMATION_SCHEMA.INNODB_SYS_TABLES;
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            | TABLE_ID | NAME                     | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            |       14 | SYS_DATAFILES            |    0 |      5 |     0 | Antelope    | Redundant  |             0 |
            |       11 | SYS_FOREIGN              |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       12 | SYS_FOREIGN_COLS         |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       13 | SYS_TABLESPACES          |    0 |      6 |     0 | Antelope    | Redundant  |             0 |
            |       18 | db1/t1#P#p0              |    1 |      4 |     4 | Antelope    | Compact    |             0 |
            |       19 | db1/t1#P#p1              |    1 |      4 |     5 | Antelope    | Compact    |             0 |
            |       17 | mysql/gtid_slave_pos     |    1 |      7 |     3 | Antelope    | Compact    |             0 |
            |       16 | mysql/innodb_index_stats |    1 |     11 |     2 | Antelope    | Compact    |             0 |
            |       15 | mysql/innodb_table_stats |    1 |      9 |     1 | Antelope    | Compact    |             0 |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            9 rows in set (0.00 sec)
            

            Create a fake table with the same partitions in a different schema (won't be able to do in the current schema)

            MariaDB [db1]> use db2;
            Database changed
             
            MariaDB [db2]> create table t1 (i int) partition by hash(i) partitions 2;
            Query OK, 0 rows affected (0.68 sec)
            

            Copy table files and then drop the fake table

            MariaDB [db2]> system cp /data/bld/10.0/data/db2/t1* /data/bld/10.0/data/db1/
            MariaDB [db2]> drop table t1;
            Query OK, 0 rows affected (0.25 sec)
            

            Problematic files are still there

            MariaDB [db2]> use db1;
            MariaDB [db1]> select * from  INFORMATION_SCHEMA.INNODB_SYS_TABLES;
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            | TABLE_ID | NAME                     | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            |       14 | SYS_DATAFILES            |    0 |      5 |     0 | Antelope    | Redundant  |             0 |
            |       11 | SYS_FOREIGN              |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       12 | SYS_FOREIGN_COLS         |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       13 | SYS_TABLESPACES          |    0 |      6 |     0 | Antelope    | Redundant  |             0 |
            |       18 | db1/t1#P#p0              |    1 |      4 |     4 | Antelope    | Compact    |             0 |
            |       19 | db1/t1#P#p1              |    1 |      4 |     5 | Antelope    | Compact    |             0 |
            |       17 | mysql/gtid_slave_pos     |    1 |      7 |     3 | Antelope    | Compact    |             0 |
            |       16 | mysql/innodb_index_stats |    1 |     11 |     2 | Antelope    | Compact    |             0 |
            |       15 | mysql/innodb_table_stats |    1 |      9 |     1 | Antelope    | Compact    |             0 |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            9 rows in set (0.00 sec)
            

            Now drop the bad table

            MariaDB [db1]> drop table t1;
            Query OK, 0 rows affected (0.25 sec)
             
            MariaDB [db1]> select * from  INFORMATION_SCHEMA.INNODB_SYS_TABLES;
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            | TABLE_ID | NAME                     | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            |       14 | SYS_DATAFILES            |    0 |      5 |     0 | Antelope    | Redundant  |             0 |
            |       11 | SYS_FOREIGN              |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       12 | SYS_FOREIGN_COLS         |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
            |       13 | SYS_TABLESPACES          |    0 |      6 |     0 | Antelope    | Redundant  |             0 |
            |       17 | mysql/gtid_slave_pos     |    1 |      7 |     3 | Antelope    | Compact    |             0 |
            |       16 | mysql/innodb_index_stats |    1 |     11 |     2 | Antelope    | Compact    |             0 |
            |       15 | mysql/innodb_table_stats |    1 |      9 |     1 | Antelope    | Compact    |             0 |
            +----------+--------------------------+------+--------+-------+-------------+------------+---------------+
            7 rows in set (0.01 sec)
            

            Of course, it's a hack and comes without any warranty. The right answer is "restore from the backup and never do it again".

            marko, do you have a better idea?

            elenst Elena Stepanova added a comment - I can only think of re-creating the fake files and then dropping the table. Like so (file operations of course should be done in a normal way, here they are done from MySQL client for the sake of simplicity): Initial table MariaDB [test]> use db1; Database changed MariaDB [db1]> create table t1 (i int ) partition by hash(i) partitions 2; Query OK, 0 rows affected (0.52 sec) Remove the table files from the disk MariaDB [db1]> select @@datadir; + ----------------------+ | @@datadir | + ----------------------+ | /data/bld/10.0/data/ | + ----------------------+ 1 row in set (0.01 sec)   MariaDB [db1]> system rm /data/bld/10.0/data/db1/t1*   MariaDB [db1]> show tables; Empty set (0.01 sec) MariaDB [db1]> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES; + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 | | 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 | | 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 | | 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 | | 18 | db1/t1#P#p0 | 1 | 4 | 4 | Antelope | Compact | 0 | | 19 | db1/t1#P#p1 | 1 | 4 | 5 | Antelope | Compact | 0 | | 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 | | 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 | | 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ 9 rows in set (0.00 sec) Create a fake table with the same partitions in a different schema (won't be able to do in the current schema) MariaDB [db1]> use db2; Database changed   MariaDB [db2]> create table t1 (i int ) partition by hash(i) partitions 2; Query OK, 0 rows affected (0.68 sec) Copy table files and then drop the fake table MariaDB [db2]> system cp /data/bld/10.0/data/db2/t1* /data/bld/10.0/data/db1/ MariaDB [db2]> drop table t1; Query OK, 0 rows affected (0.25 sec) Problematic files are still there MariaDB [db2]> use db1; MariaDB [db1]> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES; + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 | | 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 | | 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 | | 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 | | 18 | db1/t1#P#p0 | 1 | 4 | 4 | Antelope | Compact | 0 | | 19 | db1/t1#P#p1 | 1 | 4 | 5 | Antelope | Compact | 0 | | 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 | | 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 | | 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ 9 rows in set (0.00 sec) Now drop the bad table MariaDB [db1]> drop table t1; Query OK, 0 rows affected (0.25 sec)   MariaDB [db1]> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES; + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ | 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 | | 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 | | 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 | | 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 | | 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 | | 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 | | 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 | + ----------+--------------------------+------+--------+-------+-------------+------------+---------------+ 7 rows in set (0.01 sec) Of course, it's a hack and comes without any warranty. The right answer is "restore from the backup and never do it again". marko , do you have a better idea?

            elenst, that sounds about right.
            For what it is worth, I am reluctant to merge the contribution
            MDEV-11412 AliSQL: Support force drop table
            because I think that the correct approach would be to remove the InnoDB system tablespace (MDEV-11633), to make InnoDB data files self-contained, and to make storage engines rely on a crash-safe data dictionary that is maintained at the SQL layer.

            marko Marko Mäkelä added a comment - elenst , that sounds about right. For what it is worth, I am reluctant to merge the contribution MDEV-11412 AliSQL: Support force drop table because I think that the correct approach would be to remove the InnoDB system tablespace ( MDEV-11633 ), to make InnoDB data files self-contained, and to make storage engines rely on a crash-safe data dictionary that is maintained at the SQL layer.

            @elena – As the customer in question that technique has been tried and all variations. Alas, the description is far from complete and it's far more complex. To be brief, the original database system was Windows and then moved to Linux. This in itself caused interesting issues with file names and case sensitivity.

            There is no way we can find that, now that we are on Linux, a way to create the partition name (to drop) that has the lower case #p# that is found in the INFO_SYS tables .. Anything we do creates a new #P# thing . We worked literally 5 hours with a MariaDB tech to try various things to remove these and nothing worked, hence the ticket.

            back in the day when the MDEV bug was around that partition names ignore the lower-case- file setting thing (don't remember which MDEV but it's there) .. which also lead to this issue ..

            It still seems quite "absurd" that since these ghost partitions are not even needed (they were created over 2 years ago and deleted)..that there is not a simple way to just remove them from the offending "INFO SCHEMA" .. The only issue they are causing are 480+ warning lines upon MariaDB startup complaining they aren't there .. Pretty annoying ..

            Are you sure there aren't any little dev tools to hack the INFO SCHEMA information to remove these things?

            sjmcdowall Steven McDowall added a comment - @elena – As the customer in question that technique has been tried and all variations. Alas, the description is far from complete and it's far more complex. To be brief, the original database system was Windows and then moved to Linux. This in itself caused interesting issues with file names and case sensitivity. There is no way we can find that, now that we are on Linux, a way to create the partition name (to drop) that has the lower case #p# that is found in the INFO_SYS tables .. Anything we do creates a new #P# thing . We worked literally 5 hours with a MariaDB tech to try various things to remove these and nothing worked, hence the ticket. back in the day when the MDEV bug was around that partition names ignore the lower-case- file setting thing (don't remember which MDEV but it's there) .. which also lead to this issue .. It still seems quite "absurd" that since these ghost partitions are not even needed (they were created over 2 years ago and deleted)..that there is not a simple way to just remove them from the offending "INFO SCHEMA" .. The only issue they are causing are 480+ warning lines upon MariaDB startup complaining they aren't there .. Pretty annoying .. Are you sure there aren't any little dev tools to hack the INFO SCHEMA information to remove these things?

            "INFO SCHEMA" does not hold any information of its own, it only provides read access to metadata in the database. The problem is not in the information schema, it's in the discrepancy between the contents of the system tablespace and the rest of the data; and to get rid of it, you need to hack not the seemingly unimportant information schema, but the highly sensitive InnoDB system tablespace. I'm not aware of any reasonably safe ways to do so; I suppose if there were any, marko would have suggested them in his previous comment. We can ask him again. Probably the above-mentioned MDEV-11412 patch would have done that, but apparently it hasn't been merged into MariaDB, and even if it will ever be, it won't be 10.0.

            elenst Elena Stepanova added a comment - "INFO SCHEMA" does not hold any information of its own, it only provides read access to metadata in the database. The problem is not in the information schema, it's in the discrepancy between the contents of the system tablespace and the rest of the data; and to get rid of it, you need to hack not the seemingly unimportant information schema, but the highly sensitive InnoDB system tablespace. I'm not aware of any reasonably safe ways to do so; I suppose if there were any, marko would have suggested them in his previous comment. We can ask him again. Probably the above-mentioned MDEV-11412 patch would have done that, but apparently it hasn't been merged into MariaDB, and even if it will ever be, it won't be 10.0.

            MDEV-11633 will provide a way to "hack the INFO SCHEMA", or, more correctly, to get rid of these stale entries in the InnoDB data dictionary.

            MDEV-11412 will get rid of the data dictionary, so this situation will not be possible in the first place.

            So, I'm closing this issue. I'll be continued in one of the issues above.

            serg Sergei Golubchik added a comment - MDEV-11633 will provide a way to "hack the INFO SCHEMA", or, more correctly, to get rid of these stale entries in the InnoDB data dictionary. MDEV-11412 will get rid of the data dictionary, so this situation will not be possible in the first place. So, I'm closing this issue. I'll be continued in one of the issues above.

            People

              wagnerbianchi Wagner Bianchi (Inactive)
              wagnerbianchi Wagner Bianchi (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.