InnoDB

This article is a Draft. Help us to complete it.

wikipedia:InnoDB is a storage engine for the database management system MySQL and MariaDB. Is the default engine since MariaDB 10.2 (2016).

InnoDB supports:

• Both SQL and XA transactions
• Tablespaces
• Foreign keys
• Full text search indexes, since MySQL 5.6 (February 2013) and MariaDB 10.0)
• Spatial operations, following the OpenGIS standard
• Virtual columns, in MariaDB
• Page compression

Activities

• Show database engine:

  show table status like '<table name>';

Related commands

• mysqldump --single-transaction
• mariadbcheck, can checked InnoDB tables with CHECK TABLE, but not repaired them with REPAIR TABLE
• mariabackup
• show table status
• SHOW PROCESSLIST;
• show table status where name = "YOUR_TABLE_NAME" (Includes engine type)

SELECT TABLE_NAME,
      ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

Errors

/var/log/mysql/error.log (MariaDB)
2022-12-15 19:18:32 0 [ERROR] InnoDB: preallocating 12582912 bytes for file ./ibtmp1 failed with error 28
2022-12-15 19:18:32 0 [ERROR] InnoDB: Could not set the file size of './ibtmp1'. Probably out of disk space
2022-12-15 19:18:32 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
2022-12-15 19:18:32 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-12-15 19:18:37 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-12-15 19:18:37 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-12-15 19:18:37 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-12-15 19:18:37 0 [ERROR] Aborting
2022-12-15 19:21:06 35 [ERROR] mysqld: Table './xxxxx/searchindex' is marked as crashed and should be repaired

Activities

1. Read InnoDB Recovery Modes https://mariadb.com/kb/en/innodb-recovery-modes/

See also

• InnoDB, InnoDB purge worker, mysqldump --single-transaction
• MariaDB engines: MyISAM, Aria, InnoDB, XtraDB, show engines;
• MariaDB: mariadbcheck, mariabackup, mysqldump, MyRocks, show (MariaDB) MariaDB Changelog, 50-server.cnf, MariaDB engines, .ibd, .myd, .myi, /var/log/mysql/error.log, /var/log/mysql/mariadb-slow.log, MariaDB files: .frm, .ibd, /etc/mysql/mariadb.conf.d/50-server.cnf, replication, MariaDB slow queries, show databases;, Aria, MyISAM, MariaDB SkySQL, binary log, 3306, /etc/mysql/

https://mariadb.com/kb/en/innodb-page-compression/