#46611 [Fbk->Opn]: multi_query() does not handle ALTER TABLE... ADD CONSTRAINT...
| From: | fhardy at noparking dot net | Date: | Wed, 19 Nov 2008 11:23:39 +0000 |
| Subject: | #46611 [Fbk->Opn]: multi_query() does not handle ALTER TABLE... ADD CONSTRAINT... | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-131102@lists.php.net to get a copy of this message | ||
ID: 46611
User updated by: fhardy at noparking dot net
Reported By: fhardy at noparking dot net
-Status: Feedback
+Status: Open
Bug Type: MySQLi related
Operating System: FreeBSD 7.1-PRERELEASE
PHP Version: 5.2.6
New Comment:
Table and constraint creation are ok with cli mysql client.
Table and constraint creation are ok with phpmyadmin with mysql php's
extension (i known that mysql php's extension has not multi_query()
equivalent method).
Removing "alter table... add constraint" from sql in php script resolve
the problem.
Execute several queries on mysql 5.1 RC server with
mysqli::multi_queries() without any "alter table... add constraint" is
ok.
In conclusion, All work fine between this mysql 5.1 RC version and
php's mysqli extension, except this.
So, I think that it must be interesting to check mysqli php's extension
compatibility with mysql 5.1, even if mysql version is a RC.
Previous Comments:
------------------------------------------------------------------------
[2008-11-19 10:57:52] jani@php.net
How is this _PHP_ bug? Since all that changed is mysql version (to a
_release candidate_!) I find it funny you report this here..
------------------------------------------------------------------------
[2008-11-19 10:23:59] fhardy at noparking dot net
Description:
------------
Using mysqli::multi_query() in order to create database table in innodb
format with foreign key failed with mysql 5.1 RC.
All is fine with mysql 5.0.67
Reproduce code:
---------------
<?php
$sql = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
DROP TABLE IF EXISTS
bank_transactions;
CREATE TABLE bank_transactions (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
client_id int(10) unsigned NOT NULL,
PRIMARY KEY (id),
KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
ALTER TABLE bank_transactions ADD CONSTRAINT
bank_transactions_ibfk_1 FOREIGN KEY (client_id) REFERENCES
clients (id) ON UPDATE CASCADE;';
$mysqli = new mysqli('myhost', 'myuser', 'mypassword',
'mydatabase');
if ($mysqli->connect_error) {
printf('Connect failed: %s\n', mysqli_connect_error());
} else {
if (!$mysqli->multi_query($sql)) {
printf('Unable to execute sql');
} else {
do {
if ($result = $mysqli->store_result()) {
$result->free();
}
} while ($mysqli->next_result());
}
$mysqli->close();
}
?>
Expected result:
----------------
Database "mydatabase" must contain two tables, clients and
bank_transactions, and one constraint between this tables.
Actual result:
--------------
I have an empty database and no error message from mysqli object.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=46611&edit=1