mysql和mariadb支持insert delayed的问题

最新推荐文章于 2024-03-12 22:37:55 发布

weixin_33724046

最新推荐文章于 2024-03-12 22:37:55 发布

阅读量167

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/bugutian/p/8005055.html

版权

分析一个开源项目，往数据库里添加日志，为了避免写入日志信息影响正常业务，日志的插入方式采用了insert delayed的方式。

打印其数据库语句，复制到mysql执行报如下错误：

ERROR 1616 (HY000): DELAYED option not supported for table

经过查找资料发现是有的mysql的存储引擎不支持此方式：

INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. If you execute INSERT DELAYED with another storage engine, you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported for table 'tab_name'

mysql存储引擎的查看方式：

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

修改方式：

在配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 一句

参考地址：

https://mariadb.com/kb/en/library/insert-delayed/

http://www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html

https://www.cnblogs.com/Baronboy/p/6037913.html

转载于:https://www.cnblogs.com/bugutian/p/8005055.html