Miising FILE_FORMAT column in INNODB_SYS_TABLES
My MariaDB V10.4.12 actually began like years ago mysql v3.x or so. and was upgraded...and upgraded... For Nextcloud it was asked to convert to baracuda innodb format.
``` MariaDB [(none)]> SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%"; ERROR 1054 (42S22): Unknown column 'FILE_FORMAT' in 'field list' ``` MariaDB is v10.4, and the mysql_upgrade procedure has been run a few weeks ago. ``` MariaDB [information_schema]> describe INNODB_SYS_TABLES ; +---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(21) unsigned | NO | 0 | ||
| NAME | varchar(655) | NO | |||
| FLAG | int(11) | NO | 0 | ||
| N_COLS | int(11) | NO | 0 | ||
| SPACE | int(11) | NO | 0 | ||
| ROW_FORMAT | varchar(12) | YES | NULL | ||
| ZIP_PAGE_SIZE | int(11) unsigned | NO | 0 | ||
| SPACE_TYPE | varchar(10) | YES | NULL |
+---------------+---------------------+------+-----+---------+-------+ 8 rows in set (0.011 sec)
```
So there is no file format while tke KB shows it should be there. AFAIK all mysql_upgrade/mariadb-upgrade have been done.
How to get this table corrected?
Answer Answered by Marko Mäkelä in this comment.
INFORMATION_SCHEMA.INNODB_SYS_TABLES is a hard-coded view that is provided to the InnoDB internal data dictionary table SYS_TABLES that resides in the system tablespace. The column FILE_FORMAT does not have any direct counterpart in SYS_TABLES.
The configuration parameters innodb_file_format and innodb_large_prefix were deprecated in MariaDB 10.2.2 when the InnoDB changes from MySQL 5.7.9 were merged. The motivation for the deprecation and removal was that originally these parameters were introduced to supposedly allow downgrading to older versions than MySQL 5.5. To make matters worse, the default values of these parameters caused the files to remain in the backward-compatible format. Only MySQL 5.7 and MariaDB 10.2 would change the defaults to sane values and deprecate these useless parameters. (The predecessors of MySQL 5.5 were already EOL at that point, and besides, we do not really support downgrades.)
Those 2 deprecated parameters were removed in MariaDB 10.3, but they were put back later in MDEV-18399, because the sane defaults were explicitly specified in commonly used configuration files.
I do not think that it makes sense to carry the column in INNODB_SYS_TABLES.
Note: In MariaDB 10.3 and MariaDB 10.4, InnoDB file formats were extended by MDEV-11369 and MDEV-15562. You can disable those format changes by
SET GLOBAL innodb_instant_alter_column_allowed=never;
which was implemented in MDEV-20590.
