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

drop partition doesn't delete partition information in INNODB_SYS_TABLES and INNODB_SYS_tablespaces

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Incomplete
    • 10.1.12
    • N/A
    • Partitioning
    • 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

      Attachments

        Activity

          People

            Unassigned Unassigned
            steenb steen bartholdy
            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.