Converting MySQL from MyISAM to InnoDB errors and solutions bitsCN.com was later working on WordPress. at the beginning, I still did not know that WordPress was using the InnoDB data engine, so I built a database in the original database, and I didn't find any problems at the beginning. it was okay to install and import SQL statements. I didn't think much at the time. The problem is that one more WordPress is installed and phpmyadmin is used to access the database a little more. The error message"
Mysqld-nt.exe application error
: "0x0050000c4" refers to the "0x00786000" memory, which cannot be read...", And then the MySQL service stopped, login failed, and the whole database could not be accessed.
Set Database type
After a long struggle, I forgot what articles I saw triggered the inspiration. later I realized that it was a disaster caused by different data engines. So I thought of re-running the Settings Wizard and changing the database type to support the InnoDB engine. Set the database type (that is, database usage) in The Wizard and set it to the second item. in The InnoDB Tablespace Settings section, set the InnoDB data file to D: /MySQL InnoDB Datafiles directory. But at the end of execute, there is always a problem with Start Service, such as 2.
If you want to Start the service directly in services. msc, you cannot Start the service. The error message is:"
Mysql service cannot be started on the local computer. error 1067: The process is aborted unexpectedly."According to some practices on the internet, if the MySQL service is uninstalled and it is re-set without restarting, the problem is as follows. if the service is uninstalled and re-set after restart, the service is successfully started, but there are new problems in the following Applying Security Settings, such as 3. The ERROR message is "ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)", indicating that MySQL is not started or started, the error is disabled again. By the way, in MySQL5 (I don't know about other versions), every time you run the setup wizard, you only need to execute the execute command, will automatically generate an original MySQL setting file in the installation directory of mysql5. ini. And it will be named after the modification time. Therefore, if an error occurs, you can recover from the backup file.
Later, I found the idea in the error log. The error log is the. err file named after your computer in the/Data directory of MySQL5 installation directory. First, I found the following section: InnoDB: Error: log file./ib_logfile0 is of different size 0 52428800 bytesInnoDB: than specified in the. cnf file 0 10485760 bytes! 101001 9:47:42 [ERROR] Default storage engine (InnoDB) is not available101001 9:47:42 [ERROR] Aborting
101001 9:47:42 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
Therefore, the ib_logfile0 log file is deleted in the "MySQL5 installation directory/Data" directory, and the MySQL service is restarted. the error 10067 is returned. Check the err file again. 101001 18:39:29 InnoDB: Log file. /ib_logfile0 did not exist: new to be createdInnoDB: Setting log file. /ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait... innoDB: Progress in MB: 100 InnoDB: Error: log file. /ib_logfile1 is of different size 0 52428800 bytesInnoDB: than specified in. cnf file 0 114294784 bytes! 101001 18:39:30 [ERROR] Default storage engine (InnoDB) is not available101001 18:39:30 [ERROR] Aborting
101001 18:39:30 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
We can see that the ib_logfile0 problem is solved, but the same problem lies in ib_logfile1. Therefore, I drew a gourd, deleted ib_logfile1, and restarted the MySQL service, but it was useless. Is it necessary to restart the system. If you delete the first two ib_logfiles, restart the system. The reason may be that you just started the instance.
Actually, the system needs to be restarted.. The Log is as follows: 101001 19:19:24 InnoDB: Log file. /ib_logfile0 did not exist: new to be createdInnoDB: Setting log file. /ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait... innoDB: Progress in MB: 100101001 19:19:26 InnoDB: Log file. /ib_logfile1 did not exist: new to be createdInnoDB: Setting log file. /ib_logfile1 size to 109 MBInnoDB: Database physically writes the file full: wait... innoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files becauseInnoDB: data files are stored UPT, or new data files wereInnoDB: created when the database was started previusinnodb: time but the database was not shut downInnoDB: normally after that.101001 19:19:27 [ERROR] Default storage engine (InnoDB) is not available101001 19:19:27 [ERROR] Aborting
101001 19:19:27 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
Now there is only one last Problem. Default storage engine (InnoDB) is not available. Go to services. msc to start the MySQL service, or an error occurs. However, the log shows InnoDB: No valid checkpoint found. innoDB: If this error appears when you are creating an InnoDB database, InnoDB: the problem may be that during an earlier attempt you managedInnoDB: to create the InnoDB data files, but log file creation failed. innoDB: If that is the case, please refer toInnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html101001 20:45:09 [ERROR] Default storage engine (InnoDB) is not available101001 20:45:09 [ERROR] Aborting
101001 20:45:09 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
So I searched the internet and found the solution to the prompt "No valid checkpoint found.": delete all existing ibdataN and ib_logfileN files and restart Mysql. As a result, it is enough. InnoDB: The first specified data file D:/MySQL InnoDB Datafiles/ibdata1 did not exist: InnoDB: a new database to be created! 101001 21:04:24 InnoDB: Setting file D:/MySQL InnoDB Datafiles/ibdata1 size to 10 MBInnoDB: Database physically writes the file full: wait... 101001 21:04:25 InnoDB: Log file. /ib_logfile0 did not exist: new to be createdInnoDB: Setting log file. /ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait... innoDB: Progress in MB: 100101001 21:04:27 InnoDB: Log file. /ib_logfile1 did not exist: new to be createdInnoDB: Setting log file. /ib_logfile1 size to 109 MBInnoDB: Database physically writes the file full: wait... innoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating newInnoDB: Doublewrite buffer createdInnoDB: Creating foreign key constraint system tablesInnoDB: Foreign key constraint system tables limit 21:04:31 InnoDB: Started; log sequence number 0 0101001 21:04:32 [Note] D:/MySQL5/bin/mysqld-nt: ready for connections. version: '5. 0.18-nt 'socket: ''port: 3306 MySQL Community Edition (GPL)
After one day of struggle, I finally successfully converted MySQL from MyISAM to The InnoDB engine. I also learned to read the error log file err. But I just thought about it. if I had followed the above steps from the very beginning, I don't know what would happen?
