2

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                   |
+-------------------------+-------------------------+
5
  • 1
    the exact same message, given from MySQL was given here: stackoverflow.com/questions/53308726/mysql-crashes-often
    – Luuk
    Sep 20, 2019 at 17:42
  • @Luuk I've seen that post, but in my case, there is no InnoDB tables in the database. As I said, I only have MyISAM tables, so I don't understand why I have InnoDB related errors.
    – st.
    Sep 20, 2019 at 18:06
  • What is the output of show engines; ? if it shows InnoDB, it is loading the engine, …., and probably it is, otherwise, what are causing those errors?
    – Luuk
    Sep 20, 2019 at 19:06
  • @Luuk yes, it shows InnoDB as first line. But why is it used and by who? I understand "mysql" system database is using InnoDB, but it's small, default 128M innodb buffer should be sufficient. Is it possible that my myisam database uses InnoDB somehow? I just checked with "show table status", all tables are myisam.
    – st.
    Sep 20, 2019 at 19:17
  • How much RAM do you have?
    – Rick James
    Oct 5, 2019 at 0:24

2 Answers 2

2

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.

13
  • I ran the query you provided, it shows tables from mysql,sys and information_schema. My database isn't here as expected, as I said I only have myisam tables. I didn't upgrade in-place but imported a mysqldump file. The question is, as I said before, why I need increase innodb buffer size if nobody except system db (small) uses innodb? Does mysql uses innodb even for myisam internally somehow?
    – st.
    Sep 20, 2019 at 20:22
  • The query I just gave you should only show InnoDB tables. Please post the output in the question. Sep 20, 2019 at 20:25
  • Please run SELECT table_name FROM information_schema.tables WHERE engine='InnoDB' and table_schema='mysql'; Do you get any row coming back ??? Sep 20, 2019 at 20:28
  • I added the output to the question
    – st.
    Sep 20, 2019 at 20:29
  • OK I see the 19 InnoDB tables from the mysql schema. Sep 20, 2019 at 20:29
0

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
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.