Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Incomplete
-
10.1.12
-
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 doingalter 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