mysql建表时报错ERROR 1286 (42000) Unknown storage engine 'MyISAM'
来源:互联网 发布:智慧办公软件下载 编辑:程序博客网 时间:2024/06/07 06:19
一.问题描述
mysql版本为:mariadb 10.1.11。
在建myisam表时,报错ERROR 1286 (42000): Unknown storage engine 'MyISAM'
dba@192.168.64.30:3306 >create table inno_myisam_test (`id` int(11) NOT NULL AUTO_INCREMENT,
-> a int(2),b varchar(10),PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
ERROR 1286 (42000): Unknown storage engine 'MyISAM'
二.问题分析
检查mysql与enforce相关的参数:
dba@192.168.64.30:3306>show variables like 'enforce%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| enforce_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
enforce_storage_engine参数为InnoDB,表示所有表建立时的engine只能为Innodb。若有避开这一限制,还有什么办法?
三.问题解决
在会话级更改参数为myisam,如下:
dba@192.168.64.30:3306>set session enforce_storage_engine='myisam';
Query OK, 0 rows affected (0.00 sec)
dba@192.168.64.30:3306>show variables like 'enforce%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| enforce_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)
再建表,正常完成。
dba@192.168.64.30:3306: zeng 10:53:26>create table inno_myisam_test (`id` int(11) NOT NULL AUTO_INCREMENT,
-> a int(2),b varchar(10),PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
查看表定义:
dba@192.168.64.30:3306 zeng 10:53:51>show create table inno_myisam_test\G
*************************** 1. row ***************************
Table: inno_myisam_test
Create Table: CREATE TABLE `inno_myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(2) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
登出mysql,重新登入:
对表插入数据正常:
dba@192.168.64.30:3306zeng 11:22:01>insert into inno_myisam_test(b) values('zengxuewen');
Query OK, 1 row affected (0.00 sec)
查询表的记录正常:
dba@192.168.64.30:3306zeng 11:23:01>select * from inno_myisam_test;
+----+------+------------+
| id | a | b |
+----+------+------------+
| 1 | NULL | zengxuewen |
+----+------+------------+
1 row in set (0.00 sec)
再查看会话级参数设置为InnoDB
dba@192.168.64.30:3306 : zeng 11:23:19>show variables like 'enforce%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| enforce_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
参考mariadb对参数的说明:
https://mariadb.com/kb/en/mariadb/server-system-variables/#enforce_storage_engine
enforce_storage_engine
Description: Force the use of a particular storage engine for new tables. Used to avoid unwanted creation of tables using another engine. For example, setting to InnoDB will prevent any MyISAM tables from being created. If another engine is specified in a CREATE TABLE statement, the outcome depends on whether the NO_ENGINE_SUBSTITUTION sql_mode has been set or not. If set (the default from MariaDB 10.1.7), the query will fail, while if not set, a warning will be returned and the table created according to the engine specified by this variable. The variable has a session scope, but is only modifiable by a user with the SUPER privilege.
Commandline: None
Scope: Session
Dynamic: Yes
Data Type: string
Default Value: none
Introduced: MariaDB 10.1.4
四.问题延伸
若用导出导入方法(含mysqldump、mydumper/myloader)将含有myisam引擎的表迁移到强制innodb的mysql库中,会遇到同样报错问题,需要将表的定义sql文件更改为innodb:
sed -i "s/)ENGINE=MyISAM;/)ENGINE=InnoDB;/g" *schema.sql
- mysql建表时报错ERROR 1286 (42000) Unknown storage engine 'MyISAM'
- MySQL error : unknown storage engine InnoDB
- 查询innodb表时报错:ERROR 1286 (42000): Unknown table engine 'InnoDB'
- Error Code: 1286 - Unknown storage engine 'InnoDB'
- ndbcluster引擎表同步到innodb引擎报错Error 'Unknown storage engine 'ndbcluster'
- mysql报错unknown/unsupported storage engine: InnoDB
- 关于mysql报错: Unknown storage engine 'InnoDB'
- MySQL备份/还原 Unknown storage engine 'InnoDB'
- mysql之Unknown storage engine 'InnoDB'
- mysql数据库操作时报错:Unknown table engine 'InnoDB"解决方法
- ERROR 1286 (42000): Unknown table engine 'InnoDB'
- ERROR 1286 (42000): Unknown table engine ‘InnoDB’
- 1286 Unknown storage engine 'InnoDB'的解决办法
- Unknown storage engine 'InnoDB'
- Syntax error or access violation: 1286 Unknown storage engine 'InnoDB' 解决办法
- mysql报错:1030 - Got error 28 from storage engine
- mysql 报错:Got error 30 from storage engine ( 1030 )
- mysql显示报错“Got error 28 from storage engine”
- C/C++的堆栈,内存分配
- 基本界面组件~TextView~EditText~Button
- 银行家算法
- Python学习笔记(3)-操作列表
- 『sklearn学习』沃德结构层次聚类的浣熊脸图像的演示
- mysql建表时报错ERROR 1286 (42000) Unknown storage engine 'MyISAM'
- 数据分页显示
- PERL各个符号代表的意思
- 【暮冬之战】考研政治
- java设计模式1
- Spring 、SpringMVC、Mybatis、MySQL、Maven、Tomcat搭建JavaWeb项目流程---学习笔记(二)新建Maven项目
- STM32 USART1 USART2 UASRT3 UART4 UART5串口通信测试程序
- JFinal Web开发学习(九)后台添加前台显示博客
- 评价代码的维度