1

I ran into the same database trouble that another user described here and I managed to get mysqld started by setting innodb_force_recovery = 6, which allowed me to perform a backup using mysqldump -u<username> -p -f --all-databases > all.sql. However, this backup resulted in a set of error messages:

mysqldump: Error 1034: Index for table <table1> is corrupt; try to repair it when dumping table <table1> at row: 0
mysqldump: Error 1034: Index for table <table2> is corrupt; try to repair it when dumping table <table2> at row: 6
mysqldump: Error 1034: Index for table <table3> is corrupt; try to repair it when dumping table <table3> at row: 0
mysqldump: Error 1034: Index for table <table4> is corrupt; try to repair it when dumping table <table4> at row: 0
mysqldump: Error 1034: Index for table <table5> is corrupt; try to repair it when dumping table <table5> at row: 0
mysqldump: Error 1034: Index for table <table6> is corrupt; try to repair it when dumping table <table6> at row: 0
mysqldump: Error 1034: Index for table <table7> is corrupt; try to repair it when dumping table <table7> at row: 0

Does the "try to repair it" mean that mysqldump did, in fact, try to repair it (and succeeded), or does it mean that I should try to repair it as a next action, and if so, what commands do I need to issue now? Do I need to re-run mysqldump?

10.6.12-MariaDB-0ubuntu0.22.04.1

I just did SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES; to get a list, and the list shows different engines (Aria, Memory, InnoDB, MyISAM) for different tables, but with regard to the tables 1-7 referenced above, they are all InnoDB.

Table Create Table
table1 CREATE TABLE `table1` (
`config_name` varchar(255) NOT NULL DEFAULT '',
`config_value` mediumtext NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin

They do not have the same schema.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Robert K S
  • 13
  • 1
  • 3

1 Answers1

0

"I should try to repair it as a next action" <- this one. mysqldump/mariadb-dump doesn't attempt to repair anything, it maintains focused on dumping.

There are ER_NOT_KEYFILE errors generated in InnoDB under some conditions so its not an exclusive Aria/MyISAM error.

"REPAIR TABLE" isn't implemented on InnoDB, try dropping the indexes on these tables and re-creating the indexes.

Consider adding --routines/ triggers to your mysqldump. It might be worth trying to use this dump as a restore mechanism, but copy (when shutdown) the current datadir first.

danblack
  • 8,258
  • 2
  • 12
  • 28