Update to 4.0.0: Corrupt index while trying to convert to utf8mb4

Support & Bugs
#1

Hello,

After upgrade to matomo4 along with other several issues when I try to convert to utf8mb4 using “console core:convert-to-utf8mb4” I got the following error:

SQLSTATE[HY000]: General error: 1034 Index for table ‘matomo_log_hsr_event’ is corrupt; try to repair it.

I have tried to repair the table doing the following:

  • repair table stament : fails because is an innodb table.
  • mysql dump and rebuild the table: the error keeps happening after that.
  • optimize table: It fails due the corrupted index.
  • Change storage engine to myisaam, repair and convert back to innodb: The repair says OK but when go back to innodb and run the convert command the index still corrupted.
  • mysql dump change to utf8mb4 manually in the ddl and execute: the indexes still corrupted.

As you can see I have tried everything I could find in the net, ¿Does anyone know how to fix this?

Thank you in advance.
Best regards

#2

Are you using MariaDB 10.0/1 or MySQL 5.5? This could probably be that bug: https://jira.mariadb.org/browse/MDEV-18063 - we don’t necessarily expect a bug in the DB engine as a first option, but worth a try to update anyway.

#3

Hello Peterbo,

Thank for you for your answer.

My MariaDB version is 10.5.6-1 :frowning:.

#4

When you check the table:

MariaDB> check table matomo_log_hsr_event;

Which Index is reported corrupt?

You can try to drop

MariaDB> ALTER table matomo_log_hsr_event drop index [INDEX NAME];

and recreate that index. But, depending of table size, this could take some time:

MariaDB> ALTER table matomo_log_hsr_event add [INDEX DATA AND ATTRIBUTES];
#5

I have upgraded mariadb to 10.5.8 to see if this fixed the issues, but it doesn’t.
It doesn’t say which index is corrupted:

MariaDB [matomo]> ALTER TABLE matomo.matomo_log_hsr_event ENGINE=innodb;
ERROR 1034 (HY000): Index for table 'matomo_log_hsr_event' is corrupt; try to repair it
MariaDB [matomo]> check table matomo_log_hsr_event; 
+-----------------------------+-------+----------+----------+
| Table                       | Op    | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------+
| matomo.matomo_log_hsr_event | check | status   | OK       |
+-----------------------------+-------+----------+----------+
1 row in set (19.226 sec)

MariaDB [matomo]> check table matomo_log_hsr_event extended;
+-----------------------------+-------+----------+----------+
| Table                       | Op    | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------+
| matomo.matomo_log_hsr_event | check | status   | OK       |
+-----------------------------+-------+----------+----------+
1 row in set (19.002 sec)

I can list the indexes but nothing else:

+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| matomo_log_hsr_event |          0 | PRIMARY   |            1 | idhsrevent  | A         |     8675484 |     NULL | NULL   |      | BTREE      |         |               |
| matomo_log_hsr_event |          1 | idloghsr  |            1 | idloghsr    | A         |       27281 |     NULL | NULL   |      | BTREE      |         |               |
| matomo_log_hsr_event |          1 | idhsrblob |            1 | idhsrblob   | A         |      192788 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

I dont mind to recreate all the indexes, but I don’t have the DDLs

#6

A little update @peterbo

I have finally found which index is producing the problems, its the idhsrblob, for some reason when I delete it, I can change the encoding of the table, but if I recreate it with create index and then try to change the encoding it fails with corruption again…

Can it be some value of this field?

Any thoughs about what could I do to fix this?

ty in advance guys

#7

Good question - is there anything more verbose in the error-log? Apart from that, make sure that ROW_FORMAT is dynamic (I don’t think that this is the reason, but at the moment, I don’t really have a better idea).

#8

The only message that appears is “[Warning] InnoDB: Retry attempts for writing partial data failed”

[EDIT]I have the feeling that the issue maybe related to the size of the tmp folder of mariadb, I gonna resize it for a higher value and see if can be this

#9

Finally!

Problem solved, the issue was because my tmpfs temp folder created for mariadb only had 256mb, after raise its size to 512 it was able to end the operation.

2 Likes