Convert Mysql from MyISAM to InnoDB errors and solutions _mysql

Source: Internet
Author: User
Tags wordpress database
Later in doing WordPress, the original did not know that WordPress used the InnoDB data engine, so in the original database built a database, the beginning did not find the problem, installation, import SQL, no problem, then did not think much. Until these days because you have to install more than one WordPress, with phpMyAdmin access to the database a little bit, the problem. Always a visit to the WordPress database, pop-up error prompts " Mysqld-nt.exe Application Error : the "0x005346c4" directive refers to "0x00786000" memory, which cannot be read ...", and then the MySQL service also stopped, landing can not, the entire database can not be accessed."

Figure 1 Setting the database type

Tangled up for a long time, also forget to see what article triggered the inspiration, anyway, then know that the original data engine is different from the trouble. The idea is to rerun the Settings Wizard and change the database type to support the InnoDB engine. Act now, set the database type in the wizard (that is, the DB usage in Figure 1), set it to the second item, and place the InnoDB data file settings in the InnoDB tablespace setting at D:\MySQL InnoDB Datafiles directory. But at the end of execute, there was always a problem with the start service, as shown in Figure 2 below.

Figure 2 Error in Start service
Want to go directly to services.msc inside start service, also not. Error message : " unable to start MySQL service on the local computer error 1067: Process aborted unexpectedly." "
In accordance with some of the online practices, uninstall the MySQL service, also did not restart on the reset, the problem as before; uninstall the service, reboot, then reset, then start the service successfully, but in the later applying security settings when there are new problems, as shown in Figure 3. Figure 2 where the error tip becomes "error 2003:can ' t connect to MySQL server ' localhost ' (10061)", indicating that MySQL has not been started or started, and the error has been turned off again.
Figure 3
Incidentally, in MySQL5 (the other version I don't know) each time the Setup Wizard runs, as soon as execute executes, it automatically generates a backup bak file My.ini The original MySQL settings file under the MySQL5 installation directory. And will be named after the modified time. So if you set a problem, 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 name, in the \data directory of the MYSQL5 installation directory.
First I found the following paragraph:
InnoDB:Error:log file. \ib_logfile0 is of different size 0 52428800 bytes
Innodb:than specified in the. cnf file 0 10485760 bytes!
101001 9:47:42 [ERROR] Default storage Engine (InnoDB) is not available
101001 9:47:42 [ERROR] Aborting

101001 9:47:42 [note] D:\mysql5\bin\mysqld-nt:shutdown complete

So to "MySQL5 installation directory \data" directory to delete ib_logfile0 log files, restart the MySQL service, or hint 10067 error. To view the Err file again.
101001 18:39:29 innodb:log file. \ib_logfile0 did not exist:new to be created
innodb:setting log file. \ib_logfile0 size to 109 MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100
InnoDB:Error:log file. \ib_logfile1 is of different size 0 52428800 bytes
Innodb:than specified in the. cnf file 0 114294784 bytes!
101001 18:39:30 [ERROR] Default storage Engine (InnoDB) is not available
101001 18:39:30 [ERROR] Aborting

101001 18:39:30 [note] D:\mysql5\bin\mysqld-nt:shutdown complete

As you can see, the IB_LOGFILE0 problem is solved, but the same problem is on the ib_logfile1. So dots, the ib_logfile1 deleted, and then restart the MySQL service, but found no use. Do you want to reboot the system? Indeed, the front two ib_logfile deleted, restart the system is done. The front may be due to just boot, so it is OK. actually, the system needs to be restarted . This is the following log
101001 19:19:24 innodb:log file. \ib_logfile0 did not exist:new to be created
innodb:setting log file. \ib_logfile0 size to 109 MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100
101001 19:19:26 innodb:log file. \ib_logfile1 did not exist:new to be created
innodb:setting log file. \ib_logfile1 size to 109 MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100
Innodb:cannot Initialize created log files because
Innodb:data files are corrupt, or new data files were
Innodb:created The database was started previous
Innodb:time But the database is not shut down
Innodb:normally after that.
101001 19:19:27 [ERROR] Default storage Engine (InnoDB) is not available
101001 19:19:27 [ERROR] Aborting

101001 19:19:27 [note] D:\mysql5\bin\mysqld-nt:shutdown complete

All right, now that's the last question. The default storage engine (InnoDB) is not available. Go to services.msc inside to start MySQL service, still error. But the log is showing
Innodb:no valid checkpoint found.
Innodb:if This error appears the are creating an InnoDB database,
Innodb:the problem May is that during a earlier attempt you managed
Innodb:to Create the InnoDB data files, but log file creation failed.
Innodb:if that's the case, please refer to
Innodb:http://dev.mysql.com/doc/mysql/en/error_creating_innodb.html
101001 20:45:09 [ERROR] Default storage Engine (InnoDB) is not available
101001 20:45:09 [ERROR] Aborting

101001 20:45:09 [note] D:\mysql5\bin\mysqld-nt:shutdown complete

So go online search for a moment, find hint no valid checkpoint found. solution: Restart MySQL after deleting all existing Ibdatan and Ib_logfilen files. so do it, and sure enough.
Innodb:the the specified data file D:\MySQL InnoDB datafiles\ibdata1 did not exist:
INNODB:A new database to is created!
101001 21:04:24 innodb:setting file D:\MySQL InnoDB datafiles\ibdata1 size to MB
Innodb:database physically writes the file full:wait ...
101001 21:04:25 innodb:log file. \ib_logfile0 did not exist:new to be created
innodb:setting log file. \ib_logfile0 size to 109 MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100
101001 21:04:27 innodb:log file. \ib_logfile1 did not exist:new to be created
innodb:setting log file. \ib_logfile1 size to 109 MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100
Innodb:doublewrite Buffer not found:creating new
Innodb:doublewrite Buffer Created
Innodb:creating FOREIGN KEY constraint system tables
Innodb:foreign key constraint system tables created
101001 21:04:31 innodb:started; Log sequence number 0 0
101001 21:04:32 [note] D:\mysql5\bin\mysqld-nt:ready for connections.
Version: ' 5.0.18-nt ' socket: ' port:3306 MySQL Community Edition (GPL)

The
struggled for a day and managed to convert MySQL from MyISAM to InnoDB engine. Also learned to read the error log file Err file. But I was thinking, if from the beginning to follow the above, do not know what will happen?
Go from Darkblue blog

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.