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 '';

  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, 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
• https://mariadb.com/kb/en/innodb-page-compression/