Mysql doesn’t work, I get the following error:
[ERROR] InnoDB: Failed to find tablespace for table '"database"."table"' in the cache.
Attempting to load the tablespace with space id 1290.
asked Jul 27, 2016 at 9:05
1
I faced a similar issue today while copying a datadir from one server to another, the answer I m gonna post might not be a direct one.
I have mistakenly added the innodb_force_recovery option in my configuration file thats the reason for the error, innodb_force_recovery value was set to 5.
Post removing the innod_force_recover option I got rid of this issue.
answered Feb 21, 2019 at 11:06
One of our database table tblpages seems to be corrupted. When I select the table, its says
show full fields from `favc`.`tblpages`
Table 'tblpages' doesn't exist
So I thought repairing it will be possible. I run a repair on the table and got this unsuccessful message.
tblpages repair Warning InnoDB: Tablespace is missing for table 'tblpages'
tblpages repair Error Table 'tblpages' doesn't exist
tblpages repair status Operation failed
I don’t have any latest copy of this table.
Please help with any advise you may have. Thanks!
asked Jan 15, 2014 at 20:56
2
«Tablespace is missing for table XXXX”
There are broadly three ways in which this error can happen:
- Table files have the wrong ownership/permissions
- The table file is misplaced
- The data file is corrupted or deleted
in my situation was: 1 — wrong permission, after hard copy/
other solutions can be found here: https://bobcares.com/blog/mysql-tablespace-is-missing-for-table/
answered Aug 3, 2019 at 15:55
in my case I could simply fix the table-space-missing problem by:
cd /var/lib/mysql/psa/
cp -a ModuleSettings.ibd.bak ModuleSettings.ibd
psa.ModuleSettings was the table which gave me «#1812 table space is missing»-erros.
Warning: It’s possible you lose some newer data.
answered Feb 6, 2017 at 20:02
3
In this error case, first check if the tablespaces i.e the files that have the table data exists in the path
- In my case i had the tablespaces exist
$ cd {path_to_mysql_installation}/mysql/data/{db_name}/ MyTable1.ibd MyTable2.ibd
- Check Permission for the files
$ ls -l -rw-r-----@ 1 vivek 884470207 114688 Apr 10 08:40 MyTable1.ibd -rw-r-----@ 1 vivek 884470207 114688 Apr 10 08:40 MyTable2.ibd
(My permissions got modified, not sure how)
- Reset the permissions to the whole data directory
$ chmod -R 755 ../../data
Now tried accessing the data from mysql console and from java application, it worked perfectly fine.
Please note i’m answering only the permission problem, for file correction or missing check out the solutions here .
Cheers!
answered Apr 15, 2021 at 8:59
VivekVivek
1112 bronze badges
In my case I could fix it with
ALTER TABLE database.tablename IMPORT TABLESPACE
Where database is the database and tablename obviously the name of the table that had the missing Tablespace.
answered Oct 21, 2021 at 9:59
Last night, my MySQL server crashed and now I have several databases, which data I’m unable to recover.
According to errorlog, the coruptions in tablespaces happend quite a while ago (few months), but no one notticed that and yesterday, when MySQL crashed (for completely different reason), it just failed to start with following error:
2015-06-09 23:09:23 14642 [Note] InnoDB: Starting crash recovery.
2015-06-09 23:09:23 14642 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-06-09 23:09:24 14642 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 0.
2015-06-09 23:09:24 7f648369a7e0 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2015-06-09 23:09:24 14642 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation
When the db was started with innodb-force-recovery set to 6, server started, with tons of those messages in error log:
2015-06-09 23:16:50 16659 [ERROR] InnoDB: Failed to find tablespace for table '"db1234"."tbl456"' in the cache. Attempting to load the tablespace with space id 275772.
I managed to dump majority of databases and import them to clean data directory. But some dbs have corrupted tablespace and I can’t dump them (or even open any table in them).
So now i have several (about a hundred) databases, which can’t be loaded into live MySQL server. I have their data files (both .frm and .ibd files) but they are somehow corrupted and MySQL can’t open them:
2015-06-10 18:37:18 3965 [Warning] InnoDB: Cannot open table db123/tbl456 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
I tried to copy data files into another MySQL instance, but with no success, i even tried to create fresh .frm files (but i don’t know the structure of the tables), stop the server, replace them with the right ones, start the server and open them — again with no success.
Sadly, i don’t have backup of those databases, because they were corrupted long time ago and the backup dumps failed to save any data.
So now i have the data in innodb data files but can’t load them into live server. Is there any way how to save them?
I found this toolkit: https://www.percona.com/software/mysql-innodb-data-recovery-tools, but i’m not sure if it is possible for me to use it, its quite complicated and i dont see how to recover a hundred of databases, which structure is unknown to me..
Server uses MySQL 5.6.17, compiled from source.
Here at Bobcares, our Support Engineers monitor & maintain servers of web hosts, digital marketers and other online businesses.
A vast majority of these servers use MySQL as the database server, and a common error seen in them is:
1812 Tablespace is missing for table XXXXWhat is MySQL error “Tablespace is missing for table”?
MySQL stores all data in files with the extensions .ibd (for InnoDB tables) and .MYD (for MyISAM tables).
These files are the “space” in which table data is stored. Unsurprisingly, these files are referred to as “tablespaces”.
We’ve seen many cases where these files go missing. Usually it happens due to errors in server migrations, disk issues or even administration errors.
When MySQL is unable to access a file to query a table, it shows the error:
1812 Tablespace is missing for table XXXX
How to fix error “1812 Tablespace is missing for table XXXX”
There are broadly three ways in which this error can happen:
- Table files have the wrong ownership/permissions
- The table file is misplaced
- The data file is corrupted or deleted
When we see this error, we take a quick look at the database folder for the table’s data file (with the extension .ibd).
If it is present there, we check the permissions and fix it if it’s wrong. In many cases we’ve found this to be the issue.
However, if the file is not present there, we’ll then look for backups or try to get it from the source server (in case of migrations).
Now, we’ve seen two ways in which backups are stored:
- .ibd files – This is the original format of InnoDB files. This is easy to restore.
- .sql dump files – This is the database stored as SQL queries. These are harder to restore.
If the backups are stored as .ibd files, we copy the table files into the database folder and set the right permissions. Everything usually works fine from that point.
On the other hand, if it’s in the SQL format, we drop the current database, and restore the full database from backup. This method will create fresh entries in the System tables, and build proper linkages.
Special case : Fixing corrupted system table
Loosely related to this error is a situation when the table files are present in the database folder, but MySQL refuses to see it.
This happens when the system table is restored from a backup that doesn’t contain the table information, or the system table space is corrupted in some way.
The best way to solve this is to restore the database with an SQL dump.
However, if there’s no SQL dump, we’ve to create the database and import the database files (aka tablespace).
The steps loosely follow this sequence:
- Take a backup of all
.ibdand.frmfiles. - Create the database and tables using the SQL queries from the web app installation script.
- Delete the newly created files using the DISCARD statement. Eg.
ALTER TABLE newdb.table1 DISCARD TABLESPACE; - Then copy all the
.ibdand.frmfiles from backup to the database folder, and assignmysql:mysqlownership. - Ask MySQL to accept the new files using the IMPORT statement. Eg.
ALTER TABLE newdb.table1 IMPORT TABLESPACE;
This should get the database back online.
Of course, there could complications in table creation and import. If you need help getting this done right, click here to talk to our MySQL experts. We are online 24/7 and can help you in a few minutes.
Conclusion
“1812 Tablespace is missing for table” is a common error in MySQL servers that usually comes up after a server migration, hard disk error, or a new app setup. Today we’ve seen why this error happens and what do here at Bobcares to fix it.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
SEE SERVER ADMIN PLANS
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
#1 10.10.2016 00:51:07
- Elimay
- Участник
- Зарегистрирован: 10.10.2016
- Сообщений: 4
не поднимается БД после сбоя
Не запускается сервер бд. ОС debian + MariaDb. Выдает
Код:
Код:
Код:
Неактивен
#2 10.10.2016 16:03:09
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6740
Re: не поднимается БД после сбоя
Удалять ib_logfile — плохо, если база остановлена нештатно. Попробуйте поднять force_recovery до максимального уровня, когда стартует (например, 6 должно завестись, но может и раньше). И после этого dump/restore.
Неактивен
#3 10.10.2016 16:21:41
- Elimay
- Участник
- Зарегистрирован: 10.10.2016
- Сообщений: 4
Re: не поднимается БД после сбоя
paulus написал:
Удалять ib_logfile — плохо, если база остановлена нештатно. Попробуйте поднять force_recovery до максимального уровня, когда стартует (например, 6 должно завестись, но может и раньше). И после этого dump/restore.
ib_logfile удалялось лишь в качестве экперимента и само собой с откатом, бывали случаи в практике что помогало. По force_recovery поднимал и до 6, безрезультатно. Попробовал переставить MariaDB. В итоге завелась на force_recovery=6. Делаю бэкап, но полет не стабильный, периодически теряет соединение и не все дампится. Приходится дампить кусками…
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `attachments` at row: 33338330
Неактивен
#4 10.10.2016 18:31:36
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6740
Re: не поднимается БД после сбоя
А что в логе при этом? По идее, с шестеркой не должно падать и должно отдавать пустые данные, если страница не читается.
Неактивен
#5 10.10.2016 22:07:47
- Elimay
- Участник
- Зарегистрирован: 10.10.2016
- Сообщений: 4
Re: не поднимается БД после сбоя
paulus написал:
А что в логе при этом? По идее, с шестеркой не должно падать и должно отдавать пустые данные, если страница не читается.
Oct 11 06:21:08 server-db mysqld: 161011 6:21:08 [ERROR] InnoDB: Failed to find tablespace for table ‘»imas».»attachments»‘ in the cache. Attempting to load the tablespace with space id 376.
Oct 11 06:23:53 server-db mysqld: 2016-10-11 06:23:53 7f6e706f1700 InnoDB: Assertion failure in thread 140112309458688 in file btr0pcur.cc line 445
Oct 11 06:23:53 server-db mysqld: InnoDB: Failing assertion: page_is_comp(next_page) == page_is_comp(page)
Oct 11 06:23:53 server-db mysqld: InnoDB: We intentionally generate a memory trap.
Oct 11 06:23:53 server-db mysqld: InnoDB: Submit a detailed bug report to <a href=«http://bugs.mysql.com»>http://bugs.mysql.com</a>.
Неактивен
#6 12.10.2016 01:05:54
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6740
Re: не поднимается БД после сбоя
Ого. А остальные таблички, кроме этой, дампятся? Или на многих такая штука? Я бы попробовал сдампить все, кроме этой, а потом эту вручную — сколько получится.
Неактивен
#7 12.10.2016 08:36:23
- Elimay
- Участник
- Зарегистрирован: 10.10.2016
- Сообщений: 4
Re: не поднимается БД после сбоя
на нескольких, но их содержание было не интересно. Как оказалось сбой в этой таблице происходил почти в самом конце, если потери и есть то микроскопические в сравнении с размером таблицы. Но столкнулся с проблемой что дробнуть БД не получилось опять же из-за этой таблицы, все время слетал мускл. Пришлось решать проблему физическим удалением из /var/lib/mysql
Неактивен
#8 12.10.2016 22:37:39
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6740
Re: не поднимается БД после сбоя
При таких сбоях все равно придется все перезаливать, т.к. InnoDB неконсистентен, так что всё правильно сделали.
Неактивен
This post comes from at the MySQL Performance Blog.
A few years ago Yves Trudeau and Aleksandr Kuzminsky wrote posts about different ways for recovering orphaned .ibd files:
- Connecting orphaned .ibd files
- A recovery trivia or how to recover from a lost ibdata1 file
Today I want to show you how to do that in an easier and quicker way. In my example I’ll restore a “payment.ibd” file (payment table) from Sakila DB on a server with MySQL 5.5 (but with help from MySQL 5.6 and sandbox).
In my case the OS is CentOS. So I needed to:
- install mysqlsandbox(check instructions there)
- download latest Percona Server 5.6:
wget http://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/release-5.6.14-62.0/483/binary/linux/i686/Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Create sandbox:
make_sandbox Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Test it:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select @@versionG" *************************** 1. row *************************** @@version: 5.6.14-rel62.0
It works!
Check datadir and if the innodb_file_per_table option enabled (this is requirement):
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like 'datadir'" +---------------+---------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------+ | datadir | /home/mixa/sandboxes/msb_5_6_14/data/ | +---------------+---------------------------------------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like '%per_table'" +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+
If it’s not enabled then you’ll need to enable it
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "SET GLOBAL innodb_file_per_table=1"
Create empty payment table on 5.6 sandbox
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 test < payment_table.sql
Payment_table.sql – is file with “SHOW CREATE TABLE” statement for payment table. The table structure should be the same.
cat payment_table.sql CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`) ) ENGINE=InnoDB
Stop sandbox
./sandboxes/msb_5_6_14/stop
Replace .ibd file (in my case the correct copy of it is located in my homedir)
cp ~/payment.ibd ~/sandboxes/msb_5_6_14/data/test/ -f
Make sure permissions are ok for .ibd file
sudo chmod 660 ~/sandboxes/msb_5_6_14/data/test/payment.ibd sudo chown : ~/sandboxes/msb_5_6_14/data/test/payment.ibd
Start sandbox
./sandboxes/msb_5_6_14/start
Currently if you’ll try to select something from the table you’ll get an error:
Select from table
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 1146 (42S02) at line 1: Table 'test.payment' doesn't exist
Error log
2013-11-02 14:36:34 b7eff990 InnoDB: Error: table 'test/payment' InnoDB: in InnoDB data dictionary has tablespace id 7, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name test/payment and id 10, though. Have InnoDB: you deleted or moved .ibd files? ... ... 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Failed to find tablespace for table '"test"."payment"' in the cache. Attempting to load the tablespace with space id 7. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: In file './test/payment.ibd', tablespace id and flags are 10 and 0, but in the InnoDB data dictionary they are 7 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/payment'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: cannot calculate statistics for table "test"."payment" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
How to Fix it? In 5.6 tablespacemanagement is very improved so the only thing needed is “ALTER TABLE .. DISCARD TABLESPACE” and “ALTER TABLE .. IMPORT TABLESPACE”.
Please check also limitations: Tablespace Copying Limitations
Look at example:
Discard tablespace
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment discard tablespace; show warnings;" +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1812 | InnoDB: Tablespace is missing for table 'test/payment' | | Warning | 1812 | InnoDB: Tablespace is missing for table 'payment' | +---------+------+--------------------------------------------------------+
Import tablespace
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment import tablespace; show warnings" +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/payment.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
That’s it, data recovered, payment table accessible on 5.6 sandbox.
Now check if data exists in payment table on sandbox:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment"
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select * from test.payment limit 1G"
*************************** 1. row ***************************
payment_id: 1
customer_id: 1
staff_id: 1
rental_id: 76
amount: 2.99
payment_date: 0000-00-09 03:49:32
last_update: 2028-02-08 12:32:35
Exists.
So dump it from sandbox and restore on 5.5:
Dump from 5.6
mysqldump -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 --add-drop-table test payment > ~/payment_dump.sql
Restore to 5.5
mysql -u user -p < ~/payment_dump.sql
Check if data exists on 5.5
mysql -u root -e "select * from test.payment limit 3;" +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | 1 | 1 | 1 | 76 | 2.99 | 0000-00-09 03:49:32 | 2028-02-08 12:32:35 | | 2 | 1 | 1 | 573 | 0.99 | 0000-00-09 03:49:32 | 0000-00-00 00:00:00 | | 3 | 1 | 1 | 1185 | 5.99 | 0000-00-09 03:49:37 | 0000-00-00 00:00:00 | +------------+-------------+----------+-----------+--------+---------------------+---------------------+
During my work with this case I got into a situation in which the drop table payment on 5.5 wasn’t possible because payment.idb there wasn’t correct – so the server crashed each time I tried to access to this table. The workaround is:
— stop server
— rm .ibd file
— start server
— drop table as usually by DROP TABLE command
-
Type:
Bug
-
Priority:
Major
-
Resolution:
Incomplete
-
Affects Version/s:
10.1.13
-
Fix Version/s:
N/A
Major
Aug 25 11:26:50 gpsdata-01 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] /usr/sbin/mysqld (mysqld 10.1.13-MariaDB-1~trusty) starting as process 3816 ...
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Using mutexes to ref count buffer pool pages
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: The InnoDB memory heap is disabled
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Memory barrier is not used
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Compressed tables use zlib 1.2.8
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Using Linux native AIO
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Using SSE crc32 instructions
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Initializing buffer pool, size = 256.0M
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Completed initialization of buffer pool
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [Note] InnoDB: Highest supported file format is Barracuda.
|
Aug 25 11:26:50 gpsdata-01 mysqld: InnoDB: Transaction 3748540 was in the XA prepared state.
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [ERROR] InnoDB: Failed to find tablespace for table '"gps"."events"' in the cache. Attempting to load the tablespace with space id 72.
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [ERROR] InnoDB: Failed to find tablespace for table '"gps"."positions"' in the cache. Attempting to load the tablespace with space id 56.
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [ERROR] InnoDB: Failed to find tablespace for table '"gps"."user_device"' in the cache. Attempting to load the tablespace with space id 54.
|
Aug 25 11:26:50 gpsdata-01 mysqld: 2016-08-25 11:26:50 140205837375424 [ERROR] InnoDB: Failed to find tablespace for table '"gps"."device_geofence"' in the cache. Attempting to load the tablespace with space id 83.
|
Aug 25 11:26:50 gpsdata-01 mysqld: InnoDB: 1 transaction(s) which must be rolled back or cleaned up
|
- relates to
-
MDEV-14717
RENAME TABLE in InnoDB is not crash-safe
-
- Closed
-
Let’s show you what means
“That’s specifics of InnoDB data storage.”
Here is the what this specific case will teach you:
- basic debug with strace
- MySQL could hangs in a infinite loop during a shutdown (could be seen with strace)
- InnoDB files could get corrupted without a hardware issue, but with a kill -KILL or probably it was corrupted before the shutdown and the kill?
- list the opened files (and the IDs = file descriptors) of a mysql process
- start a mysql slave without a starting the replication on start with “skip-slave-start”
- Remove a corrupted innodb file, which causes database (mysql process) crash leaving your with no database at all – replace a innodb file, start the server, then drop the innodb table with sql command and recreate it to continue using healthy table
- 2 rows (4 INTEGER columns) could eat up a lot of space – 12G and probably infinite! === “That’s specifics of InnoDB data storage.”
This table file is 12 Gigabytes in size!!!MariaDB [mysql]> select * from gtid_slave_pos; +-----------+----------+-----------+-------------+ | domain_id | sub_id | server_id | seq_no | +-----------+----------+-----------+-------------+ | 0 | 16983943 | 101 | 45790450502 | | 0 | 16983944 | 101 | 45790450503 | +-----------+----------+-----------+-------------+ 2 rows in set (0.00 sec)
Here is presented a specific case with replication, but in your case you may not use replication, your problem table could be another (and your mariadb/mysql server crashes on start up or selecting from a specific table or on shutdown?), so find the problem table and remove it, here we show you how to do it! BACKUP the mysql datadir BEFORE any intervention!
Here we have a situation: a mariadb (mysql) server running as slave to a really busy master server, so you could expect 10 000 update/insert/delete queries. Everything was working till the time we wanted to shutdown the mysql process, which occurred to be impossible.
STEP 1) We tried everything from “systemctl stop mysql” to kill -TERM multiple times
5 hour the mysql process was running with 2000 opened file descriptors to multiple table files. The strace showed this:
[pid 14824] <... io_getevents resumed> []{0, 500000000}) = 0
[pid 14815] <... io_getevents resumed> []{0, 500000000}) = 0
[pid 14824] io_getevents(139723876253696, 1, 256, <unfinished ...>
[pid 14815] io_getevents(139723876356096, 1, 256, <unfinished ...>
[pid 14825] <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out)
[pid 14825] futex(0x55d16885deb0, FUTEX_WAKE_PRIVATE, 1) = 0
[pid 14825] futex(0x55d16885dedc, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 3576261, {1525268239, 312230000}, ffffffff <unfinished ...>
[pid 14852] <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out)
[pid 14852] futex(0x55d16885dd30, FUTEX_WAKE_PRIVATE, 1) = 0
[pid 14852] futex(0x55d16885dd5c, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 2775717, {1525268240, 308225000}, ffffffff <unfinished ...>
[pid 14825] <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out)
[pid 14825] futex(0x55d16885deb0, FUTEX_WAKE_PRIVATE, 1) = 0
[pid 14825] futex(0x55d16885dedc, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 3576263, {1525268240, 304889000}, ffffffff <unfinished ...>
[pid 14862] <... nanosleep resumed> NULL) = 0
[pid 14862] nanosleep({1, 0}, <unfinished ...>
[pid 14821] <... io_getevents resumed> []{0, 500000000}) = 0
[pid 14821] io_getevents(139723876315136, 1, 256, <unfinished ...>
[pid 14820] <... io_getevents resumed> []{0, 500000000}) = 0
And this has been repeating many times for hours without any disk activity on flushing any IO…so no use to wait for something, which apparently won’t finish at all.
STEP 2) So a
kill -9
was used to stop the mysql process. What could go wrong??? InnoDB is awesome and cannot corrupt if the hardware is OK, right? Yeahhh right…
When the start command was executed, the mysql process started, the innodb engine recovery completed successfully and after 5 minutes without listening socket and heavy IO reading there is the segmentation fault crash and YOU have no database….
So here is one of the crashes taken from the log:
2018-05-02 19:51:54 139990018914496 [ERROR] Plugin 'innodb' already installed 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Uses event mutexes 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Compressed tables use zlib 1.2.8 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Using Linux native AIO 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Number of pools: 1 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Using SSE2 crc32 instructions 2018-05-02 19:51:54 139990018914496 [Note] InnoDB: Initializing buffer pool, total size = 64G, instances = 8, chunk size = 128M 2018-05-02 19:51:56 139990018914496 [Note] InnoDB: Completed initialization of buffer pool 2018-05-02 19:51:56 139913709942528 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2018-05-02 19:51:56 139990018914496 [Note] InnoDB: Highest supported file format is Barracuda. 2018-05-02 19:51:56 139990018914496 [Note] InnoDB: Starting crash recovery from checkpoint LSN=311205983427362 2018-05-02 19:51:57 139990018914496 [Note] InnoDB: Last binlog file '/var/log/mysql-binlog/mysql-bin.227009', position 38590879 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: 128 out of 128 rollback segments are active. 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: Creating shared tablespace for temporary tables 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: Waiting for purge to start 2018-05-02 19:52:12 139990018914496 [Note] InnoDB: 5.7.21 started; log sequence number 311205983427371 2018-05-02 19:52:12 139915446597376 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2018-05-02 19:52:12 139990018914496 [Note] Plugin 'FEEDBACK' is disabled. 2018-05-02 19:52:12 139990018914496 [Note] Recovering after a crash using tc.log 2018-05-02 19:52:12 139990018914496 [Note] Starting crash recovery... 2018-05-02 19:52:12 139990018914496 [Note] Crash recovery finished. 2018-05-02 19:52:12 139990018914496 [Note] Server socket created on IP: '0.0.0.0'. 2018-05-02 19:52:12 139990018914496 [Warning] 'user' entry 'root@srvdns2' ignored in --skip-name-resolve mode. 2018-05-02 19:52:12 139990018914496 [Warning] 'proxies_priv' entry '@% root@srvdns1' ignored in --skip-name-resolve mode. 2018-05-02 19:53:52 139915446597376 [Note] InnoDB: Buffer pool(s) load completed at 180502 19:53:52 180502 19:57:12 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.2.13-MariaDB-10.2.13+maria~xenial key_buffer_size=2147483648 read_buffer_size=262144 max_used_connections=0 max_threads=10002 thread_count=6 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6871600 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f40700009a8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f515c4fecf8 thread_stack 0x49000 *** buffer overflow detected ***: /usr/sbin/mysqld terminated ======= Backtrace: ========= /lib/x86_64-linux-gnu/libc.so.6(+0x777e5)[0x7f51f54eb7e5] /lib/x86_64-linux-gnu/libc.so.6(__fortify_fail+0x5c)[0x7f51f558d15c] /lib/x86_64-linux-gnu/libc.so.6(+0x117160)[0x7f51f558b160] /lib/x86_64-linux-gnu/libc.so.6(+0x1190a7)[0x7f51f558d0a7] /usr/sbin/mysqld(my_addr_resolve+0xde)[0x55a9e9f1832e] /usr/sbin/mysqld(my_print_stacktrace+0x1e2)[0x55a9e9eff2e2] /usr/sbin/mysqld(handle_fatal_signal+0x345)[0x55a9e9999b95] /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f51f5eda390] /lib/x86_64-linux-gnu/libc.so.6(+0x9f849)[0x7f51f5513849] /usr/sbin/mysqld(insert_dynamic+0x2a)[0x55a9e9ed3a4a] /usr/sbin/mysqld(_Z25rpl_load_gtid_slave_stateP3THD+0x424)[0x55a9e98c43a4] /usr/sbin/mysqld(handle_slave_background+0xe7)[0x55a9e97834e7] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f51f5ed06ba] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f51f557b41d] ======= Memory map: ======== 55a9e933e000-55a9ea423000 r-xp 00000000 08:03 148565 /usr/sbin/mysqld 55a9ea622000-55a9ea6f4000 r--p 010e4000 08:03 148565 /usr/sbin/mysqld 55a9ea6f4000-55a9ea7aa000 rw-p 011b6000 08:03 148565 /usr/sbin/mysqld 55a9ea7aa000-55a9eb03d000 rw-p 00000000 00:00 0 55a9eba46000-55aa4e67b000 rw-p 00000000 00:00 0 [heap]
STEP 3) So the default way of repairing the InnoDB is to use
innodb_force_recovery
in your my.cnf configuration file:
[mysqld] innodb_force_recovery=1
But again and again crashes even enabling all the options of innodb_force_recovery=1,2,3,4,5 and last 6. But when using “innodb_force_recovery=4” and 5 and 6 we have some strange additional error:
2018-05-02 21:43:34 139667439187712 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`gtid_slave_pos` in the cache. Attempting to load the tablespace with space id 63584 2018-05-02 21:43:34 139667439187712 [Warning] InnoDB: Allocated tablespace ID 63584 for mysql/gtid_slave_pos, old maximum was 0
The innodb_force_recovery did not help we still cannot start our MySQL database, but there were two things:
- No errors were reported by the InnoDB Engine – “InnoDB: Buffer pool(s) load completed” and Crash recovery always finished without errors
- The MySQL starts successfully, but not listening socket and then after 3~5 minutes of extensive IO reading from the disk by the mysql process it crashes
It was like something big was loading just on the start and in fact needed on the very beginning, so what start immediately after successful load of the mysql process?
REPLICATION
Remember this is a slave!
STEP 4) So trying to prevent the start of the replication on start of the mysql process with:
[mysqld] skip-slave-start
STEP 5) strace and file descritor to find the offender table file.
The skip-slave-start option DID the trick – NO Segmentation fault
the mysql processes immediately after successful engines load began to listen on sockets. So the big offender was probably something connected with the replication! A big table used in the replication? How to find it, ok remove the “skip-slave-start”, then start the mysql process and wait for the time the IO read kicks in, then strace the mysql process (find the ID of the process with ps – the first number of the following command is the ID of the mysql process):
srv@local ~ # ps axuf|grep mysql|grep -v grep
mysql 6969 239 8.3 78561320 10983848 ? Ssl 22:53 108:59 /usr/sbin/mysqld
srv@local ~ # strace -f -p 6969
strace -f -p 6969
strace: Process 6969 attached with 27 threads
[pid 6996] rt_sigtimedwait([HUP QUIT ALRM TERM TSTP], NULL, NULL, 8 <unfinished ...>
[pid 6994] futex(0x55941cb5d1ec, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid 6995] futex(0x7f93c6de1d24, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid 6993] restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
[pid 6992] futex(0x55941cb5d0ec, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid 6991] futex(0x55941cb5cd6c, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid 6990] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6997] mremap(0x7f9298b76000, 2210828288, 2210836480, MREMAP_MAYMOVE <unfinished ...>
[pid 6989] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6988] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6986] futex(0x55947cfe941c, FUTEX_WAIT_PRIVATE, 33, NULL <unfinished ...>
[pid 6985] futex(0x55947cfe941c, FUTEX_WAIT_PRIVATE, 31, NULL <unfinished ...>
[pid 6984] futex(0x55947cfe941c, FUTEX_WAIT_PRIVATE, 32, NULL <unfinished ...>
[pid 6997] <... mremap resumed> ) = 0x7f9298b76000
[pid 6984] <... futex resumed> ) = -1 EAGAIN (Resource temporarily unavailable)
[pid 6983] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6984] futex(0x55947cfe941c, FUTEX_WAIT_PRIVATE, 33, NULL <unfinished ...>
[pid 6982] io_getevents(140347217702912, 1, 256, <unfinished ...>
[pid 6981] io_getevents(140347217723392, 1, 256, <unfinished ...>
[pid 6985] <... futex resumed> ) = -1 EAGAIN (Resource temporarily unavailable)
[pid 6980] io_getevents(140347217743872, 1, 256, <unfinished ...>
[pid 6979] io_getevents(140347217764352, 1, 256, <unfinished ...>
[pid 6978] io_getevents(140347217825792, 1, 256, <unfinished ...>
[pid 6977] io_getevents(140347217846272, 1, 256, <unfinished ...>
[pid 6985] futex(0x55947cfe941c, FUTEX_WAIT_PRIVATE, 33, NULL <unfinished ...>
[pid 6976] io_getevents(140347217866752, 1, 256, <unfinished ...>
[pid 6975] io_getevents(140347219357696, 1, 256, <unfinished ...>
[pid 6974] io_getevents(140347217784832, 1, 256, <unfinished ...>
[pid 6973] io_getevents(140347217805312, 1, 256, <unfinished ...>
[pid 6971] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6970] restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
[pid 6969] futex(0x55941a4be944, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
[pid 6997] pread64(38, "201L32136553153531553156130307o303365]E277"..., 16384, 6228590592) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210836480, 2210844672, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "210271277343531565315353157130307o343362~E277"..., 16384, 6228639744) = 16384
[pid 6997] pread64(38, "30517303k5315753156531510130307o377224gE277"..., 16384, 6228656128) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210844672, 2210852864, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "217351V~531510531575315n130307p3431363E277"..., 16384, 6228672512) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210852864, 2210861056, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "3673213302325315n5315105315v130307p>316360E277"..., 16384, 6228705280) = 16384
[pid 6997] pread64(38, "374v2021775315v5315n5315r130307p\alE277"..., 16384, 6228721664) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210861056, 2210869248, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "s261A5315r5315v531516130307p2121AE277"..., 16384, 6228754432) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210869248, 2210877440, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "214t72445315165315r531521130307p242H37E277"..., 16384, 6228770816) = 16384
[pid 6997] pread64(38, "<n272"531521531516531524130307p311i313E277"..., 16384, 6228819968) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210877440, 2210885632, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] mremap(0x7f9298b76000, 2210885632, 2210893824, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "n|In531524531521531525130307p360EqE277"..., 16384, 6228869120) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210893824, 2210902016, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "270206326207531525531524531527130307q1a251E277"..., 16384, 6228885504) = 16384
[pid 6997] pread64(38, "{l226S531527531525531531130307q&205!E277"..., 16384, 6228918272) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210902016, 2210910208, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "201265]&531531531527531532130307qK365212E277"..., 16384, 6228951040) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210910208, 2210918400, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "352?236327531532531531531533130307qob:E277"..., 16384, 6228967424) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210918400, 2210926592, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] mremap(0x7f9298b76000, 2210926592, 2210934784, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "3c33301531533531532531536130307q2236%E277"..., 16384, 6228983808) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210934784, 2210942976, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "Zi343177531536531533531537130307q32527LE277"..., 16384, 6229032960) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210942976, 2210951168, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "v5h%5315375315365315!130307rf325364E277"..., 16384, 6229049344) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210951168, 2210959360, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "06Yn5315!5315375315"130307sCX@E277"..., 16384, 6229082112) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210959360, 2210967552, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "203243K2035315"5315!5315$130307t;234302E277"..., 16384, 6229098496) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210967552, 2210975744, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "9264332j5315$5315"5315%130307177370#371E277"..., 16384, 6229131264) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210975744, 2210983936, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, ":200+3375315%5315$5315&13030720020U-E277"..., 16384, 6229147648) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210983936, 2210992128, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "35-th5315&5315%5315(130307200+333CE277"..., 16384, 6229164032) = 16384
[pid 6997] pread64(38, "246Y3053515315(5315&5315)130307200[324305E277"..., 16384, 6229196800) = 16384
[pid 6997] mremap(0x7f9298b76000, 2210992128, 2211000320, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "340(23505315)5315(5315+130307200214L365E277"..., 16384, 6229213184) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211000320, 2211008512, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "A37437235315+5315)5315,13030720025230373E277"..., 16384, 6229245952) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211008512, 2211016704, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "iW2743655315,5315+5315.130307200332266256E277"..., 16384, 6229262336) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211016704, 2211024896, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "343c3403475315.5315,5315/130307200356272YE277"..., 16384, 6229295104) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211024896, 2211033088, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "}3532i5315/5315.531501303072015226256E277"..., 16384, 6229311488) = 16384
[pid 6997] pread64(38, "322237206377531505315/53151130307201257241272E277"..., 16384, 6229327872) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211033088, 2211041280, MREMAP_MAYMOVE) = 0x7f9298b76000
[pid 6997] pread64(38, "/25021!53151531505315213030720332372E277"..., 16384, 6229344256) = 16384
[pid 6997] mremap(0x7f9298b76000, 2211041280, 2211049472, MREMAP_MAYMOVE) = 0x7f9298b76000
You see multiple lines (thousands) with:
[pid 6997] pread64(38, "/25021!53151531505315213030720332372E277"..., 16384, 6229344256) = 16384 [pid 6997] mremap(0x7f9298b76000, 2211041280, 2211049472, MREMAP_MAYMOVE) = 0x7f9298b76000
The pread64 – reads from file descriptor with ID=38, then you can list all file descriptors for the mysql process ID with:
srv@local mysql # ls -altr /proc/6969/fd total 0 dr-xr-xr-x 9 mysql mysql 0 May 2 21:32 .. l-wx------ 1 root root 64 May 2 21:35 2 -> /var/log/mysql/error.log dr-x------ 2 root root 0 May 2 21:35 . lrwx------ 1 root root 64 May 2 21:35 9 -> /tmp/ibgyw4AI (deleted) lrwx------ 1 root root 64 May 2 21:35 8 -> /tmp/ibuaprWP (deleted) lrwx------ 1 root root 64 May 2 21:35 7 -> /tmp/ibfXwsBW (deleted) lrwx------ 1 root root 64 May 2 21:35 6 -> /var/lib/mysql/ibdata1 lrwx------ 1 root root 64 May 2 21:35 5 -> /var/lib/mysql/aria_log.00000001 lr-x------ 1 root root 64 May 2 21:35 4 -> /var/lib/mysql lrwx------ 1 root root 64 May 2 21:35 38 -> /var/lib/mysql/mysql/gtid_slave_pos.ibd lrwx------ 1 root root 64 May 2 21:35 37 -> /var/lib/mysql/mysql/event.MYD lrwx------ 1 root root 64 May 2 21:35 36 -> /var/lib/mysql/mysql/event.MYI lrwx------ 1 root root 64 May 2 21:35 35 -> /var/lib/mysql/mysql/procs_priv.MYD lrwx------ 1 root root 64 May 2 21:35 34 -> /var/lib/mysql/mysql/procs_priv.MYI lrwx------ 1 root root 64 May 2 21:35 33 -> /var/lib/mysql/mysql/columns_priv.MYD lrwx------ 1 root root 64 May 2 21:35 32 -> /var/lib/mysql/mysql/columns_priv.MYI lrwx------ 1 root root 64 May 2 21:35 31 -> /var/lib/mysql/mysql/tables_priv.MYD lrwx------ 1 root root 64 May 2 21:35 30 -> /var/lib/mysql/mysql/tables_priv.MYI lrwx------ 1 root root 64 May 2 21:35 3 -> /var/lib/mysql/aria_log_control lrwx------ 1 root root 64 May 2 21:35 29 -> /var/lib/mysql/mysql/roles_mapping.MYD lrwx------ 1 root root 64 May 2 21:35 28 -> /var/lib/mysql/mysql/roles_mapping.MYI lrwx------ 1 root root 64 May 2 21:35 27 -> /var/lib/mysql/mysql/proxies_priv.MYD lrwx------ 1 root root 64 May 2 21:35 26 -> /var/lib/mysql/mysql/proxies_priv.MYI lrwx------ 1 root root 64 May 2 21:35 25 -> /var/lib/mysql/mysql/host.MYD lrwx------ 1 root root 64 May 2 21:35 24 -> /var/lib/mysql/mysql/host.MYI lrwx------ 1 root root 64 May 2 21:35 23 -> /var/lib/mysql/mysql/db.MYD lrwx------ 1 root root 64 May 2 21:35 22 -> /var/lib/mysql/mysql/db.MYI lrwx------ 1 root root 64 May 2 21:35 21 -> /var/lib/mysql/mysql/user.MYD lrwx------ 1 root root 64 May 2 21:35 20 -> /var/lib/mysql/mysql/user.MYI lrwx------ 1 root root 64 May 2 21:35 19 -> socket:[49519] lrwx------ 1 root root 64 May 2 21:35 18 -> socket:[49518] lrwx------ 1 root root 64 May 2 21:35 17 -> /var/lib/mysql/mysql/servers.MYD lrwx------ 1 root root 64 May 2 21:35 16 -> /var/lib/mysql/mysql/servers.MYI lrwx------ 1 root root 64 May 2 21:35 15 -> /var/lib/mysql/tc.log lrwx------ 1 root root 64 May 2 21:35 13 -> /tmp/ib281FZH (deleted) lrwx------ 1 root root 64 May 2 21:35 12 -> /var/lib/mysql/ibtmp1 lrwx------ 1 root root 64 May 2 21:35 11 -> /var/lib/mysql/ib_logfile1 lrwx------ 1 root root 64 May 2 21:35 10 -> /var/lib/mysql/ib_logfile0 l-wx------ 1 root root 64 May 2 21:35 1 -> /var/log/mysql/error.log lrwx------ 1 root root 64 May 2 21:35 0 -> /dev/pts/3
And again file descriptor with ID=38 is:
/var/lib/mysql/mysql/gtid_slave_pos.ibd
And when you check the size – 12G…..12G for this table? Why? What is used for?
STEP 6) And from the manual:
The mysql.gtid_slave_pos table is used in replication by slave servers to keep track of their current position (the global transaction ID of the last transaction applied). Using the table allows the slave to maintain a consistent value for the gtid_slave_pos system variable across server restarts. See Global Transaction ID. You should never attempt to modify the table directly. If you do need to change the global gtid_slave_pos value, use SET GLOBAL gtid_slave_pos = ... instead. The table is updated with the new position as part of each transaction committed during replication. This makes it preferable that the table is using the same storage engine as the tables otherwise being modified in the transaction, since otherwise a multi-engine transaction is needed that can reduce performance.
Hmm “The table is updated with the new position as part of each transaction committed during replication” and 12G table – could it track minillions of transactions? Probably not, in fact this table has only 2-3-4 rows at a given time!!! But the size is 12G, well as we said this is:
“That’s specifics of InnoDB data storage. Did you try to run OPTIMIZE TABLE mysql.gtid_slave_pos? It should allow to reclaim the disk space.” (taken from: https://jira.mariadb.org/browse/MDEV-12318).
STEP 7) Remove a corrupted innodb file, which causes database (mysql process) crash leaving your with no database at all and then recreate the table
Restart your mysql server with “skip-slave-start” again to be able to start it (look above).
So a table probably with couple of rows takes 12G and MariaDB is using it for the replication on start up, but the replication position is kept on another place “master.info”, can we delete this offender file “gtid_slave_pos.ibd”? Yes we can, move the file out of its place (mysql datadir) and then create a second table with:
MariaDB [mysql]> use mysql
MariaDB [mysql]> CREATE TABLE `gtid_slave_pos1` (
-> `domain_id` int(10) unsigned NOT NULL,
-> `sub_id` bigint(20) unsigned NOT NULL,
-> `server_id` int(10) unsigned NOT NULL,
-> `seq_no` bigint(20) unsigned NOT NULL,
-> PRIMARY KEY (`domain_id`,`sub_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';
Query OK, 0 rows affected (0.01 sec)
Copy gtid_slave_pos1.ibd to gtid_slave_pos.ibd and restart the mysql process, it will report an error for a table mysql.gtid_slave_pos but you will be able to drop the table and then create it with the same name: “gtid_slave_pos” (you could drop the temporary one “gtid_slave_pos1”)
MariaDB [mysql]> use mysql
MariaDB [mysql]> DROP TABLE `gtid_slave_pos`;
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> CREATE TABLE `gtid_slave_pos` (
-> `domain_id` int(10) unsigned NOT NULL,
-> `sub_id` bigint(20) unsigned NOT NULL,
-> `server_id` int(10) unsigned NOT NULL,
-> `seq_no` bigint(20) unsigned NOT NULL,
-> PRIMARY KEY (`domain_id`,`sub_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> DROP TABLE `gtid_slave_pos1`;
Query OK, 0 rows affected (0.01 sec)
The create table statement is taken from another server probably it is a good idea to do it yourself, login on a healthy mysql server and issue: “show create table mysql.gtid_slave_pos;”.
So now you have a healthy mysql.gtid_slave_posto be used for your replication. Restart your mysql server removing “skip-slave-start” from your configuration file and here it is the replication is OK and running:
MariaDB [(none)]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.10
Master_User: replusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.071301
Read_Master_Log_Pos: 64980976
Relay_Log_File: mysqld-relay-bin.230012
Relay_Log_Pos: 80704376
Relay_Master_Log_File: mysql-bin.090129
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 80704077
Relay_Log_Space: 113209377078
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 30944
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for room in worker thread event queue
1 row in set (0.00 sec)