MariaDB 10.2 InnoDB internal data dictionary problem

Question

I've recently upgraded my MariaDB installation on a Windows Server 2012 to a stable release 10.2.6. However I'm having a problem with one particular table.

Each time MariaDB starts, it logs the following:

2017-05-23 1:28:53 4180 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1

2017-05-23 1:28:53 4180 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

I've tried REPAIR on this table, but it says the table does not exist in the engine.

If I try to drop this table, I get

2017-05-23 1:58:07 5076 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1 2017-05-23 1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

2017-05-23 1:58:07 5076 [ERROR] InnoDB: incorrect flags in SYS_TABLES

2017-05-23 1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename does not exist in the InnoDB internal data dictionary though MariaDB is trying to drop it. Have you copied the .frm file of the table to the MariaDB database directory from another database? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

Afterwards, MariaDB thinks this table does not exist, though InnoDB doesn't think so. When I try to rename a newly created table to the same name, I get:

2017-05-23 2:03:30 5076 [ERROR] InnoDB: Possible reasons:

2017-05-23 2:03:30 5076 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.

2017-05-23 2:03:30 5076 [ERROR] InnoDB: (2) Table dbname.tablename exists in the InnoDB internal data dictionary though MySQL is trying to rename table dbname1.tablename to it. Have you deleted the .frm file and not used DROP TABLE?

2017-05-23 2:03:30 5076 [Note] InnoDB: Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

2017-05-23 2:03:30 5076 [ERROR] InnoDB: If table dbname.tablename is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

One of the solutions on the Internet is to recreate the whole database, which is not an option for me due to a long downtime.

So far I've ended up creating a table with the same structure using Aria engine. But how can I get it working with InnoDB?

Any tools out there to work with that InnoDB data dictionary?

Additionally, trying to SELECT from information_schema.INNODB_SYS_TABLES crashes the whole server.

Answer 1

Unfortunately, MariaDB 10.2 changed the data format in a way which breaks compatibility with 10.1 - issue MDEV-12873 - so although the upgrade will "succeed", you no longer have any access to your data.

Apparently 10.2.7 will fix this bug; in the mean time, it looks as if you just have to revert to your most recent backup. (To add insult to injury, the failed "upgrade" also prevents direct reversion, so you need to dig out a full backup and restore from that.) It's pretty troubling for me that this managed to slip in to a "stable release".