mysql Non-Transactional Database Only(只支持MyISAM)_MySQL
后来在做WordPress，一开始还不知道原来WordPress用的是InnoDB数据引擎，于是在原来的数据库里面就建了一个数据库,一开始也没发觉问题，安装，导入sql，都没问题，当时也没多想。直到这几天因为又要装多一个WordPress，用phpmyadmin访问数据库多一点，问题来了。老是一访问WordPress的数据库，就弹出错误提示“Mysqld-nt.exe应用程序错误：“0x005346c4”指令引用的“0x00786000”内存，该内存不能为读...”，然后就MySQL服务也停了，登陆不了，整个数据库都访问不了。
图1 设置数据库类型
纠结了挺久，也忘了是看到什么文章触发了灵感，反正后来才知道原来是数据引擎不同惹的祸。于是就想到重新运行次设置向导，把数据库类型改成支持InnoDB引擎的。马上行动，在向导里设置数据库类型（也就是图1的database usage）那里，设置成第二项，还在后面的InnoDB Tablespace Settings里把InnoDB数据文件设置放在D:\MySQL InnoDB Datafiles目录里。但是在最后execute执行的时候，却老是在Start Service那里出问题，如下图2。
图2 Start service出错
想直接到services.msc里面启动服务，也不行。错误提示内容为：“在 本地计算机 无法启动mysql服务 错误1067：进程意外中止。”
按照网上的某些做法，卸载了MySQL服务，也没重启就重新设置，问题如故；把服务卸载了，重启后再重新设置，则是启动服务成功，但是在后面的Applying Security Settings的时候有新的问题，如下图3。图2那里的错误提示则变成“ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)"， 说明MySQL还是没有启动，或者启动过，出错又关了。
图3
顺便提一下，在MySQL5里（其他版本的我不知道）每运行一次设置向导，只要按了execute执行，都会在MySQL5的安装目录下自动生成一个原来的MySQL的设置文件my.ini的备份bak文件。并且将以修改的时间命名。因此如果设置出了问题，可以从备份文件恢复。
后来还是在错误日志里找到思路。错误日志就是放在 MySQL5安装目录\Data 目录里的那个以你的计算机名命名的.err文件。
首先我找到以下一段：
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
于是就到“MySQL5安装目录\Data ”目录里把ib_logfile0日志文件删掉，重启MySQL服务，还是提示10067错误。再去查看err文件。
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
可以看到，ib_logfile0的问题解决了，但是同样的问题却出在ib_logfile1上面了。于是依样画葫芦，把ib_logfile1删了，再重启MySQL服务，却发现没用。难道是还要重启系统。果真，把前面两个ib_logfile删了，重启系统就搞定了。前面的可能是因为刚开机，所以也行。实际上是需要重启系统的。这时的日志如下
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 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
好了，现在只剩下最后一个问题了Default storage engine (InnoDB) is not available。去services.msc里面启动MySQL服务，还是出错。但是日志却是显示
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 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
于是去网上搜索了一下，发现提示No valid checkpoint found.的解决办法：删除所有现有的ibdataN和ib_logfileN文件后，重启Mysql。于是照做，果然行了。
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)
奋斗了一天，总算成功地把MySQL从MyISAM转换成InnoDB引擎了。也学会了读懂错误日志文件err文件了。不过我就在想了，如果从一开始就按照上面的做，不知道会怎么样呢？
转自 DarkBlue blog
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1359
52
mysql whether to change table lock table
Apr 08, 2025 pm 05:06 PM
When MySQL modifys table structure, metadata locks are usually used, which may cause the table to be locked. To reduce the impact of locks, the following measures can be taken: 1. Keep tables available with online DDL; 2. Perform complex modifications in batches; 3. Operate during small or off-peak periods; 4. Use PT-OSC tools to achieve finer control.
RDS MySQL integration with Redshift zero ETL
Apr 08, 2025 pm 07:06 PM
Data Integration Simplification: AmazonRDSMySQL and Redshift's zero ETL integration Efficient data integration is at the heart of a data-driven organization. Traditional ETL (extract, convert, load) processes are complex and time-consuming, especially when integrating databases (such as AmazonRDSMySQL) with data warehouses (such as Redshift). However, AWS provides zero ETL integration solutions that have completely changed this situation, providing a simplified, near-real-time solution for data migration from RDSMySQL to Redshift. This article will dive into RDSMySQL zero ETL integration with Redshift, explaining how it works and the advantages it brings to data engineers and developers.
Query optimization in MySQL is essential for improving database performance, especially when dealing with large data sets
Apr 08, 2025 pm 07:12 PM
1. Use the correct index to speed up data retrieval by reducing the amount of data scanned select*frommployeeswherelast_name='smith'; if you look up a column of a table multiple times, create an index for that column. If you or your app needs data from multiple columns according to the criteria, create a composite index 2. Avoid select * only those required columns, if you select all unwanted columns, this will only consume more server memory and cause the server to slow down at high load or frequency times For example, your table contains columns such as created_at and updated_at and timestamps, and then avoid selecting * because they do not require inefficient query se
The primary key of mysql can be null
Apr 08, 2025 pm 03:03 PM
The MySQL primary key cannot be empty because the primary key is a key attribute that uniquely identifies each row in the database. If the primary key can be empty, the record cannot be uniquely identifies, which will lead to data confusion. When using self-incremental integer columns or UUIDs as primary keys, you should consider factors such as efficiency and space occupancy and choose an appropriate solution.
Can mysql handle multiple connections
Apr 08, 2025 pm 03:51 PM
MySQL can handle multiple concurrent connections and use multi-threading/multi-processing to assign independent execution environments to each client request to ensure that they are not disturbed. However, the number of concurrent connections is affected by system resources, MySQL configuration, query performance, storage engine and network environment. Optimization requires consideration of many factors such as code level (writing efficient SQL), configuration level (adjusting max_connections), hardware level (improving server configuration).
Can mysql run on android
Apr 08, 2025 pm 05:03 PM
MySQL cannot run directly on Android, but it can be implemented indirectly by using the following methods: using the lightweight database SQLite, which is built on the Android system, does not require a separate server, and has a small resource usage, which is very suitable for mobile device applications. Remotely connect to the MySQL server and connect to the MySQL database on the remote server through the network for data reading and writing, but there are disadvantages such as strong network dependencies, security issues and server costs.
Can mysql return json
Apr 08, 2025 pm 03:09 PM
MySQL can return JSON data. The JSON_EXTRACT function extracts field values. For complex queries, you can consider using the WHERE clause to filter JSON data, but pay attention to its performance impact. MySQL's support for JSON is constantly increasing, and it is recommended to pay attention to the latest version and features.
Do mysql need to pay
Apr 08, 2025 pm 05:36 PM
MySQL has a free community version and a paid enterprise version. The community version can be used and modified for free, but the support is limited and is suitable for applications with low stability requirements and strong technical capabilities. The Enterprise Edition provides comprehensive commercial support for applications that require a stable, reliable, high-performance database and willing to pay for support. Factors considered when choosing a version include application criticality, budgeting, and technical skills. There is no perfect option, only the most suitable option, and you need to choose carefully according to the specific situation.
