MysqlNon-TransactionalDatabaseOnly (only MyISAM is supported) _ MySQL

Source: Internet
Author: User
According to the official recommendation of discuz, the selected Non-TransactionalDatabaseOnly only supports MyISAM. In fact, the default installation is also quite good. later, I was working on WordPress, I didn't know at first that WordPress was using the InnoDB data engine, so I built a database in the original database. I didn't find any problems at first. I installed and imported SQL, no problem. 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.

Start service error

You cannot start the service directly in services. msc. 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 persists. 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 MySQL5 installation directory \ Data directory.

First, find the following section:

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


Therefore, the ib_logfile0 log file is deleted in the "MySQL5 installation directory \ Data", 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 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


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 logs are as follows:

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 created UPT, or new data files were

InnoDB: created when the database was started previous

InnoDB: time but the database was 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


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, logs are displayed.

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 managed

InnoDB: to create the InnoDB data files, but log file creation failed.

InnoDB: If that is the case, please refer

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 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 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)


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?

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.