MySQL的InnoDB引擎#1067与#187错误

InnoDB是通用存储引擎，可兼顾高可靠性和高性能。在 MySQL 5.7 中，InnoDB是默认的 MySQL 存储引擎。除非您配置了其他默认存储引擎，否则发出不带ENGINE=子句的CREATE TABLE语句将创建InnoDBtable。

查询MySQL支持的存储引擎

通过MySQL的客户端查询所支持的存储引擎，以及当前正在使用的引擎，如下图所示。MySQL各存储引擎的区别请查阅在线官方文档库

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.06 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

innodb_flush_method的作用

InnoDB是 MySQL 的事务安全存储引擎，具有提交，回滚和崩溃恢复功能，用于保护用户的数据。这些功能需要OS和磁盘系统的支持，否则无法工作。有些OS或者磁盘子系统为了提高性能，对写操作进行延迟或者重新排序，在Flash写数据到磁盘前是否返回等，与InnoDB的默认设置不同，造成MySQL启动失败。

异常1067和187问题的起因

启动时，MySQL报“错误1067：进程终止”，在Windows日志的应用程序里报“OS error 187”。错误的起因是MySQL的读写、操作系统的Buffer、Flash到磁盘之间的冲突引起。三者之间的关系将在后续补充完成【TODO】，请关注。通过控制台的方式启动MySQL查看更多详细的过程信息，如下所示：

D:\mysql-5.6.49-winx64\bin>.\mysqld.exe --console
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
[Note] .\mysqld.exe (mysqld 5.6.49) starting as process 32620 ...
[Note] Plugin 'FEDERATED' is disabled.
[Note] InnoDB: Using atomics to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.11
[Note] InnoDB: Not using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 128.0M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Operating system error number 87 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
[ERROR] InnoDB: File .\ib_logfile0: 'aio read' returned OS error 187. Cannot continue operation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

解决办法之一

MySQL默认innodb_flush_method=async_unbuffered，该选项不支持在 Windows 的 4K 扇区硬盘上运行 MySQL 服务器，解决方法是使用innodb_flush_method=normal。