[MDEV-18130] drop partition doesn't delete partition information in INNODB_SYS_TABLES and INNODB_SYS_tablespaces Created: 2019-01-03  Updated: 2019-04-15  Resolved: 2019-04-15

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.1.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: steen bartholdy Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: innodb, need_feedback
Environment:

Red Hat Enterprise Linux Server release 7.4 (Maipo)


 Description   

Updated with :
I have checked, this isnt a general problem.
I have instances where this looks as a bug as described below, but I also have instances where drop partitions cleans up as expected.
So, a procedure to cleanup this situation is also fine,
When drop partition doesnt cleanup it looks like this :

we rarely shutdown and startup our databases, dont know how long this has been an issue :

drop partition doesn't delete partition information in

  • INFORMATION_SCHEMA.INNODB_SYS_TABLES
  • INFORMATION_SCHEMA.INNODB_SYS_tablespaces
    but information_schema.partitions is maintained ( records deleted ) when doing 

    alter table integration_error drop partition P_ERROR_ID_LT_16HK00

complications :
during startup for each partition found in INNODB_SYS_TABLES and INNODB_SYS_tablespaces but not found in information_schema.partitions and not found in filesystem.
We are getting this error :

2018-12-20 12:50:34 140559259134144 [ERROR] InnoDB: Could not find a valid tablespace file for 'error/integration_error#P#P_ERROR_ID_LT_16HK00'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2018-12-20 12:50:34 140559259134144 [ERROR] InnoDB: Tablespace open failed for '"error"."integration_error" /* Partition "P_ERROR_ID_LT_16HK00" */', ignored.
 
2018-12-20 12:50:34 7fd680b5e8c0  InnoDB: Operating system error number 2 in a file operation.

its not possible to drop the partitions :

MariaDB [error]> alter table integration_error drop partition P_ERROR_ID_LT_16HK00;
 
--------------
 
alter table integration_error drop partition P_ERROR_ID_LT_16HK00
 
--------------
 
 
 
ERROR 1507 (HY000): Error in list of partitions to DROP
 
MariaDB [error]>

I Can't see any consequences other than error messages, but would like to cleanup innodb sys information.
a procedure for cleanup or recreate is also fine.

Information :
list files :

-rw-rw----. 1 mysql mysql 1073741824 Jul 17 00:29 integration_error#P#P_ERROR_ID_LT_52HK00.ibd
 
-rw-rw----. 1 mysql mysql 1916796928 Jul 26 09:49 integration_error#P#P_ERROR_ID_LT_53HK00.ibd
 
-rw-rw----. 1 mysql mysql 1916796928 Aug  4 17:53 integration_error#P#P_ERROR_ID_LT_54HK00.ibd
 
-rw-rw----. 1 mysql mysql 9651093504 Sep 16 20:33 integration_error#P#P_ERROR_ID_LT_59HK00.ibd
 
-rw-rw----. 1 mysql mysql     131072 Oct 22 08:24 integration_error#P#P_ERROR_ID_LT_79HK00.ibd
 
-rw-rw----. 1 mysql mysql 9676259328 Oct 28 09:13 integration_error#P#P_ERROR_ID_LT_64HK00.ibd
 
-rw-rw----. 1 mysql mysql 9684647936 Dec 10 23:53 integration_error#P#P_ERROR_ID_LT_69HK00.ibd
 
-rw-rw----. 1 mysql mysql      26172 Dec 21 11:35 integration_error.frm
 
-rw-rw----. 1 mysql mysql        256 Dec 21 11:35 integration_error.par
 
-rw-rw----. 1 mysql mysql     131072 Dec 21 11:35 integration_error#P#P_ERROR_ID_LT_84HK00.ibd
 
-rw-rw----. 1 mysql mysql 1698693120 Jan  3 15:20 integration_error#P#P_ERROR_ID_LT_74HK00.ibd
 
[root@spcidb01 error]#

list partitions from information_schema.partitions 

select table_name, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_METHOD
 
from information_schema.partitions where PARTITION_NAME is not null
 
order by table_name, PARTITION_NAME,PARTITION_DESCRIPTION
 
--------------
 
 
 
+-------------------+----------------------+----------------------+-----------------------+------------------+
 
| table_name        | PARTITION_NAME       | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | PARTITION_METHOD |
 
+-------------------+----------------------+----------------------+-----------------------+------------------+
 
| integration_error | P_ERROR_ID_LT_52HK00 |  ID                  | 5200000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_53HK00 |  ID                  | 5300000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_54HK00 |  ID                  | 5400000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_59HK00 |  ID                  | 5900000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_64HK00 |  ID                  | 6400000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_69HK00 |  ID                  | 6900000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_74HK00 |  ID                  | 7400000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_79HK00 |  ID                  | 7900000               | RANGE            |
 
| integration_error | P_ERROR_ID_LT_84HK00 |  ID                  | 8400000               | RANGE            |
 
+-------------------+----------------------+----------------------+-----------------------+------------------+
 
9 rows in set, 3 warnings (0.02 sec)

List what innodb thinks exist :

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 |
 
|      115 | error/integration_error#P#P_ERROR_ID_LT_16HK00 |   33 |     22 |   101 | Barracuda   | Dynamic    |             0 |
 
|      116 | error/integration_error#P#P_ERROR_ID_LT_17HK00 |   33 |     22 |   102 | Barracuda   | Dynamic    |             0 |
 
|      117 | error/integration_error#P#P_ERROR_ID_LT_18HK00 |   33 |     22 |   103 | Barracuda   | Dynamic    |             0 |
 
|      118 | error/integration_error#P#P_ERROR_ID_LT_19HK00 |   33 |     22 |   104 | Barracuda   | Dynamic    |             0 |
 
|      119 | error/integration_error#P#P_ERROR_ID_LT_20HK00 |   33 |     22 |   105 | Barracuda   | Dynamic    |             0 |
 
|      120 | error/integration_error#P#P_ERROR_ID_LT_21HK00 |   33 |     22 |   106 | Barracuda   | Dynamic    |             0 |
 
|      121 | error/integration_error#P#P_ERROR_ID_LT_22HK00 |   33 |     22 |   107 | Barracuda   | Dynamic    |             0 |
 
|      122 | error/integration_error#P#P_ERROR_ID_LT_23HK00 |   33 |     22 |   108 | Barracuda   | Dynamic    |             0 |
 
|      123 | error/integration_error#P#P_ERROR_ID_LT_24HK00 |   33 |     22 |   109 | Barracuda   | Dynamic    |             0 |
 
|      124 | error/integration_error#P#P_ERROR_ID_LT_25HK00 |   33 |     22 |   110 | Barracuda   | Dynamic    |             0 |
 
|      125 | error/integration_error#P#P_ERROR_ID_LT_26HK00 |   33 |     22 |   111 | Barracuda   | Dynamic    |             0 |
 
|      126 | error/integration_error#P#P_ERROR_ID_LT_27HK00 |   33 |     22 |   112 | Barracuda   | Dynamic    |             0 |
 
|      127 | error/integration_error#P#P_ERROR_ID_LT_28HK00 |   33 |     22 |   113 | Barracuda   | Dynamic    |             0 |
 
|      128 | error/integration_error#P#P_ERROR_ID_LT_29HK00 |   33 |     22 |   114 | Barracuda   | Dynamic    |             0 |
 
|      129 | error/integration_error#P#P_ERROR_ID_LT_30HK00 |   33 |     22 |   115 | Barracuda   | Dynamic    |             0 |
 
|      130 | error/integration_error#P#P_ERROR_ID_LT_31HK00 |   33 |     22 |   116 | Barracuda   | Dynamic    |             0 |
 
|      131 | error/integration_error#P#P_ERROR_ID_LT_32HK00 |   33 |     22 |   117 | Barracuda   | Dynamic    |             0 |
 
|      132 | error/integration_error#P#P_ERROR_ID_LT_33HK00 |   33 |     22 |   118 | Barracuda   | Dynamic    |             0 |
 
|      133 | error/integration_error#P#P_ERROR_ID_LT_34HK00 |   33 |     22 |   119 | Barracuda   | Dynamic    |             0 |
 
|      134 | error/integration_error#P#P_ERROR_ID_LT_35HK00 |   33 |     22 |   120 | Barracuda   | Dynamic    |             0 |
 
|      135 | error/integration_error#P#P_ERROR_ID_LT_36HK00 |   33 |     22 |   121 | Barracuda   | Dynamic    |             0 |
 
|      136 | error/integration_error#P#P_ERROR_ID_LT_37HK00 |   33 |     22 |   122 | Barracuda   | Dynamic    |             0 |
 
|      137 | error/integration_error#P#P_ERROR_ID_LT_38HK00 |   33 |     22 |   123 | Barracuda   | Dynamic    |             0 |
 
|      138 | error/integration_error#P#P_ERROR_ID_LT_39HK00 |   33 |     22 |   124 | Barracuda   | Dynamic    |             0 |
 
|      151 | error/integration_error#P#P_ERROR_ID_LT_52HK00 |   33 |     22 |   137 | Barracuda   | Dynamic    |             0 |
 
|      152 | error/integration_error#P#P_ERROR_ID_LT_53HK00 |   33 |     22 |   138 | Barracuda   | Dynamic    |             0 |
 
|      153 | error/integration_error#P#P_ERROR_ID_LT_54HK00 |   33 |     22 |   139 | Barracuda   | Dynamic    |             0 |
 
|      155 | error/integration_error#P#P_ERROR_ID_LT_59HK00 |   33 |     22 |   141 | Barracuda   | Dynamic    |             0 |
 
|      156 | error/integration_error#P#P_ERROR_ID_LT_64HK00 |   33 |     22 |   142 | Barracuda   | Dynamic    |             0 |
 
|      157 | error/integration_error#P#P_ERROR_ID_LT_69HK00 |   33 |     22 |   143 | Barracuda   | Dynamic    |             0 |
 
|      158 | error/integration_error#P#P_ERROR_ID_LT_74HK00 |   33 |     22 |   144 | Barracuda   | Dynamic    |             0 |
 
|      159 | error/integration_error#P#P_ERROR_ID_LT_79HK00 |   33 |     22 |   145 | Barracuda   | Dynamic    |             0 |
 
|      160 | error/integration_error#P#P_ERROR_ID_LT_84HK00 |   33 |     22 |   146 | Barracuda   | Dynamic    |             0 |
 
|      108 | fd_dba/part_gen_table_list                     |    1 |     10 |    94 | Antelope    | Compact    |             0 |
 
|      154 | fd_dba/part_gen_table_list_20180425            |    1 |     10 |   140 | Antelope    | Compact    |             0 |
 
|       71 | percona_schema/backups                         |    1 |     12 |    57 | Antelope    | Compact    |             0 |
 
|       87 | sys/sys_config                                 |    1 |      7 |    73 | Antelope    | Compact    |             0 |
 
+----------+------------------------------------------------+------+--------+-------+-------------+------------+---------------+
 
41 rows in set (0.00 sec)

and tablespaces innodb thinks exists :

select * from INFORMATION_SCHEMA.INNODB_SYS_tablespaces
 
--------------
 
 
 
+-------+------------------------------------------------+------+-------------+----------------------+-----------+---------------+
 
| SPACE | NAME                                           | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
 
+-------+------------------------------------------------+------+-------------+----------------------+-----------+---------------+
 
|    57 | percona_schema/backups                         |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
 
|    73 | sys/sys_config                                 |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
 
|    94 | fd_dba/part_gen_table_list                     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
 
|   101 | error/integration_error#P#P_ERROR_ID_LT_16HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   102 | error/integration_error#P#P_ERROR_ID_LT_17HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   103 | error/integration_error#P#P_ERROR_ID_LT_18HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   104 | error/integration_error#P#P_ERROR_ID_LT_19HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   105 | error/integration_error#P#P_ERROR_ID_LT_20HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   106 | error/integration_error#P#P_ERROR_ID_LT_21HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   107 | error/integration_error#P#P_ERROR_ID_LT_22HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   108 | error/integration_error#P#P_ERROR_ID_LT_23HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   109 | error/integration_error#P#P_ERROR_ID_LT_24HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   110 | error/integration_error#P#P_ERROR_ID_LT_25HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   111 | error/integration_error#P#P_ERROR_ID_LT_26HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   112 | error/integration_error#P#P_ERROR_ID_LT_27HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   113 | error/integration_error#P#P_ERROR_ID_LT_28HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   114 | error/integration_error#P#P_ERROR_ID_LT_29HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   115 | error/integration_error#P#P_ERROR_ID_LT_30HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   116 | error/integration_error#P#P_ERROR_ID_LT_31HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   117 | error/integration_error#P#P_ERROR_ID_LT_32HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   118 | error/integration_error#P#P_ERROR_ID_LT_33HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   119 | error/integration_error#P#P_ERROR_ID_LT_34HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   120 | error/integration_error#P#P_ERROR_ID_LT_35HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   121 | error/integration_error#P#P_ERROR_ID_LT_36HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   122 | error/integration_error#P#P_ERROR_ID_LT_37HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   123 | error/integration_error#P#P_ERROR_ID_LT_38HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   124 | error/integration_error#P#P_ERROR_ID_LT_39HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   137 | error/integration_error#P#P_ERROR_ID_LT_52HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   138 | error/integration_error#P#P_ERROR_ID_LT_53HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   139 | error/integration_error#P#P_ERROR_ID_LT_54HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   140 | fd_dba/part_gen_table_list_20180425            |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
 
|   141 | error/integration_error#P#P_ERROR_ID_LT_59HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   142 | error/integration_error#P#P_ERROR_ID_LT_64HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   143 | error/integration_error#P#P_ERROR_ID_LT_69HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   144 | error/integration_error#P#P_ERROR_ID_LT_74HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   145 | error/integration_error#P#P_ERROR_ID_LT_79HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
|   146 | error/integration_error#P#P_ERROR_ID_LT_84HK00 |   33 | Barracuda   | Dynamic              |     16384 |             0 |
 
+-------+------------------------------------------------+------+-------------+----------------------+-----------+---------------+
 
37 rows in set (0.00 sec)

Kind regards
Steen

 Comments   
Comment by Elena Stepanova [ 2019-01-22 ]

steenb,

Do your other instances, where the problem doesn't occur, run the same 10.1.12?
The version is quite old (almost 3 years old), there have been plenty of changes since then. They might be not reflected in JIRA as fixed bugs, if they were inherited from upstream.

I wonder if it would be possible for you to upgrade one of the instances where the problem does occur, and see if it goes away.

Comment by steen bartholdy [ 2019-01-22 ]

Hi Elena
Thanks for input.
we are looking into upgrading.
since 10.1 to 10.3 isnt directly supported we expect to upgrade to 10.2.highest which is installed from redhat ( so we use redhat as software stack builder and responsible )
Is that upgrade sufficient ?
I could also use a procedure for "recreating the innodb sys tables" from the information that is on the filesystem, that would also solve my issue.
Kind regards
Steen

Comment by Elena Stepanova [ 2019-03-11 ]

Since we don't know whether this particular problem was indeed fixed, we can't say for sure whether it will be sufficient to upgrade to any version; but in regard to InnoDB-related problems, I expect the upgrade to the latest 10.2 to be a good start, with all likelihood if it was fixed at all, it was fixed in 10.2.

Generated at Thu Feb 08 08:41:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.