InnoDB errors and crashes with MyISAM database?

Question

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes. The config is mostly the default one that comes with Ubuntu 18.04 package, I just adjusted some myisam buffers and cache. (Now I also increased innodb pool size, hoping it will make those errors go away)

What may cause those errors and how I get rid of them?

Examples of errors:

2019-09-19T04:39:34.464347Z 5169718 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 22967494 OS file reads, 55308825 OS file writes, 1252 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

2019-09-20T08:27:30.209263Z 0 [ERROR] [FATAL] InnoDB: Page [page id: space=45, page number=4551] still fixed or dirty

2019-09-20 10:27:30 0x7f9350594740 InnoDB: Assertion failure in thread 140270684948288 in file ut0ut.cc line 910

Innodb tables:

+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | OPTIMIZER_TRACE           |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | engine_cost               |
| mysql              | gtid_executed             |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | innodb_index_stats        |
| mysql              | innodb_table_stats        |
| mysql              | plugin                    |
| mysql              | server_cost               |
| mysql              | servers                   |
| mysql              | slave_master_info         |
| mysql              | slave_relay_log_info      |
| mysql              | slave_worker_info         |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| sys                | sys_config                |
+--------------------+---------------------------+

Version

mysql> show global variables like '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.27                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.27-0ubuntu0.18.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

Answer 1

I have some annoying news for you. MySQL 5.7 has InnoDB enabled permanently. It's in the Docs:

Important

InnoDB cannot be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

The number of InnoDB tables have increase from version to version.

I mentioned this Apr 04, 2018 in my old post Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

MySQL SCHEMA FILES

The number of InnoDB tables in the MySQL schema also changes between major releases.

Back on Dec 09, 2017, I answered ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option and discussed when InnoDB tables starting being used in the MySQL schema.

In MySQL 5.6, it was 5 tables. In MySQL 5.7, it went to 19. In MySQL 8.0, all 31 tables are InnoDB

From my old post, I stated that MySQL 5.7 has 19 InnoDB Tables in the mysql schema.

In order to hunt down your InnoDB properly, run this

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

The 19 tables from the mysql schema should appear. If you have additional InnoDB tables, you will have to get rid of or rebuild those tables. If the 19 tables do not show up from this query, you must fix this.

I just noticed you said in your question : Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes

If you did not upgrade the system tables correctly, mysqld is probably looking for the mysql schema tables to be InnoDB.

Please read MySQL 5.7 Docs on mysql_upgrade and look at --upgrade-system-tables. It should convert the mysql schema tables from MyISAM to InnoDB.

Please do this in a test environment first beforehand.

UPDATE 2019-09-20 18:09 EDT

After a nice chat session, it turns out that there is a lot of temp tables being generated. All the temp tables were using InnoDB, consuming 482MB of the InnoDB Buffer pool. The InnoDB buffer Pool was at default (128M). Increase above 512M solved it.

I also recommended switching every to InnoDB.

Answer 2

Switch from MyISAM to InnoDB. This covers the differences and gotchas in conversion: http://mysql.rjweb.org/doc.php/myisam2innodb

As for the settings, see http://mysql.rjweb.org/doc.php/memory . That is summarized below:

If you have 64GB of RAM that is mostly used for MySQL, then these may be close to optimal:

• During the transition from MyISAM to InnoDB:

  key_buffer_size = 6G
  innodb_buffer_pool_size = 20G
  

• After conversion to InnoDB:

  key_buffer_size = 40M
  innodb_buffer_pool_size = 45G