mysql multi_query 间歇性失败
function cpanel_populate_database($dbname)
{
// populate database
$sql = file_get_contents(dirname(__FILE__) . '/PHP-Point-Of-Sale/database/database.sql');
$mysqli->multi_query($sql);
$mysqli->close();
}
sql 文件是从 phpMyAdmin 直接导出的,大约 95% 的时间运行没有问题,并且所有表都已创建并插入了数据。 (我正在从头开始创建数据库)
另外 5% 只创建第一个表或有时创建前 4 个表,但没有创建其他表(有 30 个表)。
我决定不使用 multi_query,因为它看起来有问题,并通过在分号后的每一行上仅使用 mysql_query 来查看是否发生错误。有人遇到过这样的问题吗?
function cpanel_populate_database($dbname)
{
// populate database
$sql = file_get_contents(dirname(__FILE__) . '/PHP-Point-Of-Sale/database/database.sql');
$mysqli->multi_query($sql);
$mysqli->close();
}
The sql file is a direct export from phpMyAdmin and about 95% of the time runs without issue and all the tables are created and data is inserted. (I am creating a database from scratch)
The other 5% only the first table or sometimes the first 4 tables are created, but none of the other tables are created (there are 30 tables).
I have decided to NOT use multi_query because it seems buggy and see if the the bug occurs by using just mysql_query on each line after semi-colon. Has anyone ran into issue's like this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
快速有效
Fast and effective
在将
multi_query
与可以创建或更改表的查询一起使用时,我也遇到过类似的问题。特别是,我倾向于收到似乎与外键相关的 InnoDB 1005 错误;这就像 MySQL 在继续下一条语句之前没有完全完成一条语句,因此外键缺乏适当的引用。在一个系统中,我将有问题的语句拆分到它们自己的文件中。在另一个例子中,我确实单独运行了每个命令,并以分号分隔:
I've seen similar issues when using
multi_query
with queries that can create or alter tables. In particular, I tend to get InnoDB 1005 errors that seem to be related to foreign keys; it's like MySQL doesn't completely finish one statement before moving on to the next, so the foreign keys lack a proper referent.In one system, I split the problematic statements into their own files. In another, I have indeed run each command separately, splitting on semicolons:
我从来没有使用过多重查询。当我需要类似的东西时,我转向 mysqli。另外,如果您不需要查询的任何结果,将脚本传递给 mysql_query 也可以。如果导出的顺序不正确,与外键和其他表的要求表发生冲突,您也会收到这些错误。
I have never resorted to multi-query. When I needed something like that, I moved over to mysqli. Also, if you do not need any results from the query, passing the script to mysql_query will also work. You'll also get those errors if there are exports in an incorrect order that clash with require tables for foreign keys and others.
我认为将 SQL 文件分解为单个查询的方法是一个好主意。即使它只是为了比较目的(看看它是否解决了问题)。
另外,我不确定你的文件有多大 - 但我遇到过一些文件非常大的情况,将其分成几批就可以完成工作。
I think the approach of breaking the SQL file to single-queries would be a good idea. Even if its just for comparison purposes (to see if it solves the issue).
Also, I'm not sure how big is your file - but I've had a couple of cases where the file was incredibly big and splitting it into batches did the job.