MySQL 表引擎发生意外变化？
我一直在通过复制和添加表到 MySQL 数据库。从文件粘贴到 mysql 命令行应用程序中。我的过程是从文件中复制 CREATE TABLE 语句并将其粘贴到我的开发数据库中，然后将其粘贴到另一台计算机上的测试数据库中。我是唯一使用测试数据库的人。我的 CREATE TABLE 语句指定 Engine=InnoDB。测试数据库启用了InnoDB。
最近另一位开发人员发现测试数据库上的表都是MyISAM表。我检查了我的文件，它仍然显示“Engine=InnoDB”。我检查了我的开发数据库，那里的表是 Engine=InnoDB 。
为了证明测试数据库InnoDB没有问题我只是ALTER TABLEd将所有表上的引擎设置为InnoDB。
我希望我刚刚复印了一份&粘贴或其他愚蠢的错误。但似乎很奇怪，所有测试表都是MyISAM...当然我不能与我的错误保持一致。所有备份和已使用 mysqldump 完成恢复。
有没有办法不经意地改变引擎？
I've been adding tables to MySQL databases by copying & pasting from a file into the mysql command-line app. My procedure is to copy the CREATE TABLE statement from the file and paste it into my development database and then also paste it into the test database on another machine. I'm the only one using the test database. My CREATE TABLE statements specify Engine=InnoDB. The test database has InnoDB enabled.
Recently another developer discovered that the tables on the test database are all MyISAM tables. I checked my file, it still says "Engine=InnoDB" for everything. I checked my development database, the tables are Engine=InnoDB there.
To prove that the test database has no problem with InnoDB I just ALTER TABLEd to set the engine to be InnoDB on all tables.
I hope that I've just made a copy & paste or other silly mistake. But it does seem weird that all the test tables are MyISAM...surely I can't be that consistent with my mistakes. All backups & restores have been done with mysqldump.
Is there a way of inadvertently changing engines?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为这是众所周知的 MySQL 陷阱的另一个例子；我自己也遇到过几次这个问题。
基本上问题是，如果 InnoDB 引擎由于某种原因（通常是配置问题）无法启动，那么 MySQL 会默默地回退到 MyISAM 引擎。即使你的声明说：
那么，如果 InnoDB 不活动，MySQL 也会很乐意创建一个 MyISAM 表，甚至不会警告你。即使您后来发现并修复了 InnoDB 问题，问题期间创建的所有表仍然是 MyISAM。再见数据完整性！ :)
您可以运行
SHOW ENGINES来查看哪些引擎处于活动状态。有关更多详细信息，请参阅此 MySQL 错误报告。I think this is another instance of a widely known MySQL pitfall; I have hit this problem a few times myself.
Basically the problem is that, if the InnoDB engine fails to start for whatever reason (usually configuration problems) -- then MySQL silently falls back to the MyISAM engine. Even if your statement says:
then, if InnoDB isn't active, MySQL will happily create a MyISAM table without even warning you. Even if you later discover and fix the InnoDB problem, all tables created during the problem remain MyISAM. Bye-bye data integrity! :)
You can run
SHOW ENGINESto see which engines are active. See this MySQL bug report for more details.