Fix “Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT” Error

The error “Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT” occurs when the database crashes unexpectedly. This may happen due to a sudden power outage or any other reason. The complete error message may look like:

ERROR (2000) [InnoDB] InnoDB: Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT for tablespace 023

ERROR (2000) [InnoDB] Plugin initialization aborted with error Data structure corruption

Note: InnoDB: Starting shutdown…

The FILE_CREATE, FILE_DELETE, and FILE_Modify are operations that should be logged to ensure data consistency. This error indicates that some data files are missing or inaccessible. This is a critical error as you will not be able to initialize the database.

When you do a checkpoint in the database, you may face problems. The error specifies the table space is used for this error message. The table space is a logical unit of storage used by MariaDB with InnoDB storage engine.

Why does this error occur?

The “Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT” error usually occurs when the database crashes unexpectedly. The main reason for this is sudden power outage. When power outage occurs, it can interrupt the transactional operations. For example, you’re creating a data file for a table but the operation is interrupted due to power outage.

This error can also occur when doing database upgrades.

How to Resolve the Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT” Error?

As the error occurs when the database crashes, the first thing you can do is to try to restore the database from the last known good backup. To recover the database from the last good backup when the service is stopped, you can use the below command:

mysql -h localhost -u root -p stellardb <  C:\backup\database.sql

Here, -h is used to establish the host, -u is used to define the MariaDB user name, and -p is used to provide the password. stellardb is the database name and C:\backup\database.sql is the path of backup.

If you do not have backup, then you can take the help of a specialized software to recover the data from MariaDB database. One such specialized software is Stellar Repair for MySQL. It can recover all the objects from the database, including primary keys, foreign keys, and auto-increment properties. This software works perfectly with MySQL and MariaDB. The software supports all MariaDB versions up to MariaDB 11.3.2.

Steps to use the Stellar Repair for MySQL Software

Step 1: Download Stellar Repair for MySQL Software

Step 2: Then, install the software. Once installed, stop the MySQL service. For this, open the services.msc and stop the MariaDB service (for Windows). For Linux, run the sudo systemctl stop mariadb command.

Step 3: Once the services are stopped, launch Stellar Repair for MySQL.

Step 4: Press Browse to select the Data Folder with the database you want to repair.

Step 5: Then, press the Repair button.

The software will start repairing the database. When the database is repaired, select the objects you want to restore. You can save the selected objects in a new database file or export them to Excel, CSV, or HTML file.

How to prevent this problem?

To avoid the power outage or server crash problem, take the following measures:

• Make sure to have an Uninterruptible Power Supply (UPS) to avoid an abrupt outage of power, thus preventing MariaDB crash.
• Fluctuation in voltage can cause power outage. So, provide a Power Conditioning System. This can help you to keep the voltage stable.
• Make sure to control the temperature of your servers to avoid overheating.

Conclusion

The “Missing FILE_CREATE, FILE_DELETE, or FILE_MODIFY before FILE_CHECKPOINT” error message can occur due to crashing of database. Usually, the problem is caused by power outage. To avoid this problem, make sure to have a UPS and a Power Conditioning System. It is also suggested to create backups and check the backups regularly. Backups can help you to recover data in such situations. If you do not have a backup, then use software like Stellar Repair for MySQL. This software can repair MariaDB and MySQL databases and recover all the data.