Mysql Non-Transactional Database Only (Only MyISAM is supported), nontransactional

Mysql Non-Transactional Database Only (Only MyISAM is supported), nontransactional

Later, I was working on WordPress. I didn't know that WordPress was using the InnoDB Data Engine at the beginning, so I built a database in the original database. I didn't find any problems at the beginning, it's okay to import the SQL statement. I didn't think much about it 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.

Figure 1 set the 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 in the Wizard (that is, the database usage in Figure 1) to the second item, in the subsequent InnoDB Tablespace Settings, place the InnoDB data file Settings in the D: \ MySQL InnoDB Datafiles directory. But at the end of execute, there is always a problem with Start Service, such as 2.

Figure 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. Figure 2 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.

Figure 3

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?