The primary keys in a table are identifiers. Usually, they are unique numeric values used to identify the rows in a table.
Let’s take an example. There is a table of students. This table contains columns, like ID, name, last name, email, address, etc. In this, the ID will be the primary key and the ID value for each student should be unique.
It is to be noted that the ID is auto-incremental, which means that the value is generated automatically and incrementally.
The following example shows the code to create a table with a primary key.
CREATE TABLE students (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
email VARCHAR(255) NOT NULL,
address VARCHAR(200) NOT NULL,
PRIMARY KEY (id)
);
The primary key contains an index that is used when you search for information using the select statements to retrieve data from a table.
Sometimes, the index can become corrupt, resulting in the MySQL error 1712. The complete error message may look like this one:
ERROR 1712 (HY000): Index PRIMARY is corrupted while doing an online DDL operation.
Causes for the MySQL Error 1712
Here are some possible reasons that may result in this error:
- Server Damage: Sudden power outages can cause damage to the server. Also, the server can get damaged due to overheating problems. Make sure that your server with the MySQL database has proper ventilation to avoid heating problems or you can buy a cooling system. You can also install a software that can send alerts when the server temperature is high.
- Hard Drive Issues: Hard drive starts creating issue when it becomes old. Make sure to replace your hard drive after some years.
- Malicious Software: Sometimes, malicious software can cause problems in the hard drive or create file system issues, causing corruption in index. Make sure you have anti-malware software or anti-virus software installed on your system to prevent such problems.
How to Fix the Error 1712 in MySQL?
If you get this error, the best solution is to repair the index. You can use the REPAIR TABLE statement to fix this problem.
In the command prompt, login to MySQL:
Mysql -u yourUserName -p yourPassword
Here, -u is used for the user’s name and -p is used to specify the password.
You can use the REPAIR TABLE command like this:
REPAIR TABLE ‘students’;
This command will repair the table, named students.
The following example shows how to repair only the indexes using the QUICK option. This option repairs only the index and not the data file.
REPAIR TABLE students QUICK;
There is another option that can be useful in some cases. The EXTENDED option repairs not only the indexes, but also try to fix errors with the data.
REPAIR TABLE students EXTENDED;
There is another option - USE_FRM. This is used if the index file is corrupt. The index is usually stored in a .MYI file. If it is corrupt, you can use the USE_FRM option to recreate the index using the data dictionary.
REPAIR TABLE students USE_FRM;
An Alternate Solution
If the REPAIR TABLE statement fails, then you can use a third-party software to repair your corrupt index in MySQL. One such software is Stellar Repair for MySQL. This software is available for Windows and Linux (CentOS Red Hat and Ubuntu). However, the functionality is basically the same.
To repair the MySQL database, you need to first stop the MySQL Service. In the below example, we are stopping the MySQL80 Service.
net stop MySQL80
If you do not stop the service, Stellar Repair for MySQL will show a message saying, the file is in use.
Once stopped, you can copy the folder with the data. The data is commonly stored at the location similar to this one:
C:\ProgramData\MySQL\MySQL Server 8.0\Data
Now, install Stellar Repair for MySQL and open it.
Press the Browse button and look for the copy of the data folder.
Select the database that you want to repair. If you want to repair all the databases, select the Databases List option and press the Repair button.
Once repaired, you can save the data in files or restore the database. You can save each table in Excel, CSV, or HTML files.
Conclusion
The error 1712 in MySQL usually occurs due to corrupt primary key index. In this article, we have discussed different options to repair corrupt index using the REPAIR TABLE command. We have also mentioned a third-party MySQL repair tool that can be used to repair indexes in case the REPAIR TABLE fails.
Technical Marketer at Stellar Information Technology Private Limited. He makes Tech concepts easy to understand with his strong grip on Technology.
7 min read
 in SQL Server-1448.jpg)
