php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #46611 multi_query() does not handle ALTER TABLE... ADD CONSTRAINT...
Submitted: 2008-11-19 10:23 UTC Modified: 2008-11-24 10:35 UTC
From: fhardy at noparking dot net Assigned: andrey (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5.2.6 OS: FreeBSD 7.1-PRERELEASE
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: fhardy at noparking dot net
New email:
PHP Version: OS:

 

 [2008-11-19 10:23 UTC] 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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-11-19 10:57 UTC] 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 11:23 UTC] fhardy at noparking dot net
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.
 [2008-11-19 17:55 UTC] andrey@php.net
 Hi,
I have tried both 5.2.7RC4 and 5.3-dev (with libmysql and mysqlnd) and can reproduce the problem. I have tracked the C/S traffic and there is an error in the middle of the multi-statement, which cancels the whole statement. I have added two var_dumps() to your example, like :

$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('127.0.0.1', 'root', 'root', 'db');

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()) {
				var_dump($result);
				$result->free();
			} else {
				var_dump(mysqli_error($mysqli));
			}
		} while ($mysqli->next_result());
		var_dump(mysqli_error($mysqli));
	}

	$mysqli->close();
}

and the output is :
andrey@winnie:~/dev/tmp/php5.2-200811191530$ ./php ../../vanilla/php5_3/a.php
string(0) ""
string(0) ""
string(54) "Can't create table 'db.bank_transactions' (errno: 150)"
andrey@winnie:~/dev/tmp/php5.2-200811191530$ ./php -v
PHP 5.2.7RC4-dev (cli) (built: Nov 19 2008 18:49:58)
Copyright (c) 1997-2008 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies

andrey@winnie:~/dev/tmp/php5.2-200811191530$ /work/mysql-server/mysql-5.1-binprot/extra/perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

----------
So I don't know why from the CLI the problem doesn't happen, it needs further analyse to see why the environment is different. Also probably needs a test case in C using libmysql, which will probably exhibit the same problems.
 [2008-11-24 10:35 UTC] andrey@php.net
A MySQL server bug 
http://bugs.mysql.com/bug.php?id=40877

Sorry to close it as bogus, it's not client related, thus not mysqli.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 18:01:29 2024 UTC