I have a database upgrade script which takes the current database version, passes it through a giant switch statement which does not have any breaks so all subsequent queries are run, then updates the database number to the latest version.
我有一个数据库升级脚本，它采用当前的数据库版本，通过一个巨大的switch语句传递它没有任何中断，以便运行所有后续查询，然后将数据库号更新为最新版本。
Disclaimer: I know there are probably better ways to do this (like just a bunch of if($version>1507) checks, so I don't have to have cases for "empty" versions) but I haven't had the time to refactor and test. I will, eventually, but I really don't think this problem is related.
免责声明：我知道可能有更好的方法来做到这一点（比如一堆if（$ version> 1507）检查，所以我不必有“空”版本的案例）但我没有时间重构和测试。我最终会，但我真的不认为这个问题是相关的。
Here is a sample of the upgrades script, including the line(s) that are giving me problems:
以下是升级脚本的示例，包括给我提出问题的行：
case 1508:
addNotification("Updating 1508: Adding feature specific tables for wake-up calls, virtual dispatcher and screen pop, to allow better usage and billing tracking", "success");
dbQuery("CREATE TABLE IF NOT EXISTS `feature_wakeup_calls` ( `feature_wakeup_call_id` int(12) NOT NULL AUTO_INCREMENT, `employee_id` int(12) NOT NULL, `date` date NOT NULL, `confirmed` datetime DEFAULT NULL, `employee_sms_id` int(12) DEFAULT NULL, `employee_call_id` int(12) DEFAULT NULL, `authority_call_id` int(12) DEFAULT NULL COMMENT 'call record to the authority', `balance_id` int(12) NOT NULL COMMENT 'references balance table', PRIMARY KEY (`feature_wakeup_call_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
dbQuery("CREATE TABLE IF NOT EXISTS `feature_virtual_dispatcher` ( `feature_virtual_dispatcher_id` int(12) NOT NULL AUTO_INCREMENT, `inbound_call_id` int(12) NOT NULL, `outbound_call_id` int(12) DEFAULT NULL, `balance_id` int(12) NOT NULL, PRIMARY KEY (`feature_virtual_dispatcher_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
dbQuery("CREATE TABLE IF NOT EXISTS `feature_screen_pop_endpoints` ( `feature_screen_pop_endpoint_id` int(12) NOT NULL AUTO_INCREMENT, `mac_address` varchar(50) NOT NULL, `created_by` int(12) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`feature_screen_pop_endpoint_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
dbQuery("CREATE TABLE IF NOT EXISTS `feature_screen_pop` ( `feature_screen_pop_id` int(12) NOT NULL AUTO_INCREMENT, `mac` varchar(200) DEFAULT NULL, `phone_number` int(12) NOT NULL, `format` varchar(200) DEFAULT NULL COMMENT 'ex: xml, html, etc', `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`feature_screen_pop_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
case 1509:
case 1510:
case 1511:
addNotification("Updating 1511: New columns to the phone companion endpoints table", "success");
dbQuery("ALTER TABLE `feature_screen_pop_endpoints` ADD `name` VARCHAR(200) NULL AFTER `feature_screen_pop_endpoint_id`;");
dbQuery("ALTER TABLE `feature_screen_pop_endpoints` ADD `extension` VARCHAR(50) NULL DEFAULT NULL AFTER `mac_address`, ADD `manufacturer` ENUM('Polycom','Astra','Mitel/YeaLink','Grandstream') NULL DEFAULT NULL AFTER `extension`, ADD `model` VARCHAR(50) NULL DEFAULT NULL AFTER `manufacturer`;");
The problem I'm having is that the dbQuery call immediately after 1511 fails. If I comment it out, the next one fails. Here is the dbQuery function:
我遇到的问题是1511之后立即调用dbQuery失败。如果我发表评论，那么下一个就会失败。这是dbQuery函数：
function dbQuery($query, $returnId = false, $connection = null) {
$close = true; // by default, close the connection once done
if($connection == null) $connection = connect();
else $close = false; // don't close the connection if it was passed to us
if($returnId == false) {
$result = mysqli_query($connection, $query);
if($result == false && !stringContains($query, "notification_log")) { // prevent infinite loops
addNotification("Query Failed: ".$query." with error: ".mysqli_error($connection));
}
if($close) mysqli_close($connection);
return $result;
}
else {
$result = mysqli_query($connection, $query);
if($result == false) {
if($close) mysqli_close($connection);
return $result;
}
else {
$result = mysqli_insert_id($connection);
if($close) mysqli_close($connection);
return $result;
}
}
}
Notice the Query Failed part. My log says:
请注意查询失败部分。我的日志说：
Query Failed: ALTER TABLE `feature_screen_pop_endpoints` ADD `name` VARCHAR(200) NULL AFTER `feature_screen_pop_endpoint_id` with error: Duplicate column name 'name'
If I comment out just that one line and start completely over (new database and everything) I get a similar error on the next line:
如果我只注释掉那一行并完全开始（新数据库和所有内容），我会在下一行得到类似的错误：
Query Failed: ALTER TABLE `feature_screen_pop_endpoints` ADD `extension` VARCHAR(50) NULL DEFAULT NULL AFTER `mac_address`, ADD `manufacturer` ENUM('Polycom','Astra','Mitel/YeaLink','Grandstream') NULL DEFAULT NULL AFTER `extension`, ADD `model` VARCHAR(50) NULL DEFAULT NULL AFTER `manufacturer` with error: Duplicate column name 'extension'
Of course I checked to see if anywhere else created these tables (they did not, if I comment out the lines in 1508 they never exist) and there are similar dbQuery calls which are performing ALTER TABLE table_name ADD operations flawlessly. For example, just a few rows above there is this:
当然，我查看是否有其他地方创建了这些表（他们没有，如果我在1508中注释掉它们从未存在过的行），并且有类似的dbQuery调用正在执行ALTER TABLE table_name ADD操作完美无瑕。例如，上面只有几行是这样的：
case 1502:
addNotification("Updating 1502: More data for the balance table", "success");
dbQuery("ALTER TABLE `balance` ADD `extra` VARCHAR(500) NULL AFTER `method`, ADD `unique` VARCHAR(200) NULL AFTER `extra`;");
I have tested repeatedly, so I know it is something about these lines (the 1511 set), but I have run into this problem before and I had to re-baseline the entire database, bypassing the first ~1300 cases in the switch. While that would work here it is treating a symptom and doesn't scale.
我已经反复测试过，所以我知道这些是关于这些线路的（1511套装），但我之前遇到过这个问题，我不得不重新启动整个数据库，绕过交换机中的前1300个案例。虽然这可以在这里起作用，但它正在治疗症状并且不能扩展。
Anyone have any clue about what might be wrong?
任何人都有任何关于可能出错的线索？
1 个解决方案
#1
-2
I've got the same problem. 
我有同样的问题。
And I found the browser always requested 'favicon.ico' after my php script. And It's Request URL is {my_domain}/favicon.ico.
我发现浏览器总是在我的php脚本之后请求'favicon.ico'。它的请求网址是{my_domain} /favicon.ico。
So It's Obviously the 'favicon.ico' request cause running the script again.
所以显然'favicon.ico'请求导致再次运行脚本。
#1
-2
I've got the same problem.
我有同样的问题。
And I found the browser always requested 'favicon.ico' after my php script. And It's Request URL is {my_domain}/favicon.ico.
我发现浏览器总是在我的php脚本之后请求'favicon.ico'。它的请求网址是{my_domain} /favicon.ico。
So It's Obviously the 'favicon.ico' request cause running the script again.
所以显然'favicon.ico'请求导致再次运行脚本。