#35993 [Bgs->Opn]: mysql_query doesnt honor database resource id

ID: 35993 User updated by: andreas at fink dot org Reported By: andreas at fink dot org -Status: Bogus +Status: Open Bug Type: MySQL related Operating System: MacOS X & Linux PHP Version: 5.1.2 New Comment: Gosh. Please CHECK what I provided before putting it to bogous again. THIS IS DEFINITIVELY A BUG. It just doesnt occur in all scenarios. Your code works here too but mine doesn't and its not obvious why not. It apparently depends what you do with Mysql and what user you use. Test EXACTLY THIS and not with user "root" or anything else: 1st. rename table testdata to testdata2 in database test2 2nd: <?php $db1_handle=mysql_pconnect("127.0.0.1","test","test"); mysql_select_db("test1",$db1_handle); $db2_handle=mysql_pconnect("127.0.0.1","test","test", true); mysql_select_db("test2",$db2_handle); $query = "show tables"; $result1 = mysql_query($query,$db1_handle); while ($line = mysql_fetch_row ( $result1 ) ) { var_dump($line); } echo "---------------------------\n"; $query = "show tables"; $result2 = mysql_query($query,$db2_handle); while ($line = mysql_fetch_row ( $result2 ) ) { var_dump($line); } ?> results in: vpn5:~ afink$ php test2.php array(1) { [0]=> string(8) "testdata" } --------------------------- array(1) { [0]=> string(8) "testdata" } The SECOND one however MUST BE "testdata2" So its doing it at the WRONG DB. mysql> use test1 Database changed mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | testdata | +-----------------+ 1 row in set (0.01 sec) mysql> use test2 Database changed mysql> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | testdata2 | +-----------------+ 1 row in set (0.00 sec) mysql> Previous Comments: ------------------------------------------------------------------------ [2006-01-13 17:58:28] tony2001@php.net <?php $db1_handle=mysql_connect("127.0.0.1","root","", true); mysql_select_db("test",$db1_handle); $db2_handle=mysql_connect("127.0.0.1","root","", true); mysql_select_db("mysql",$db2_handle); $query = "show tables"; $result1 = mysql_query($query,$db1_handle); while ($line = mysql_fetch_row ( $result1 ) ) { var_dump($line); } echo "---------------------------\n"; $query = "show tables"; $result2 = mysql_query($query,$db2_handle); while ($line = mysql_fetch_row ( $result2 ) ) { var_dump($line); } ?> This code works perfectly here. ------------------------------------------------------------------------ [2006-01-13 15:14:21] andreas at fink dot org Tried the suggestion in two ways: $db1_handle=mysql_connect("127.0.0.1","test","test",1); mysql_select_db("test1",$db1_handle); $db2_handle=mysql_connect("127.0.0.1","test","test",1); mysql_select_db("test2",$db2_handle); result: This query was executed on db1_handle:This is DB named test1 This query was executed on db2_handle:This is DB named test1 so still wrong if I use mysql_connect instead I get This query was executed on db1_handle:This is DB named test1 Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /Users/afink/test.php on line 14 This query was executed on db2_handle: So it is still a bug. ------------------------------------------------------------------------ [2006-01-13 12:43:19] tony2001@php.net $db1_handle=mysql_pconnect("127.0.0.1","test","test"); $db2_handle=mysql_pconnect("127.0.0.1","test","test"); These two calls effectively return THE SAME connection identifier (because connect details are the same). Use mysql_connect() with 4th parameter set to TRUE to force creation of new connection. No bug here. ------------------------------------------------------------------------ [2006-01-13 12:14:49] andreas at fink dot org used configure statement: export CC="gcc" export CFLAGS="-DBIND_8_COMPAT=1 -DEAPI -O3 -fno-omit-frame-pointer" ./configure --mandir=/usr/share/man --with-mysql=/usr/local/mysql \ --enable-dba --enable-track-vars --with-sockets \ -enable-libxml --enable-calendar --enable-ftp \ --with-apxs=/usr/sbin/apxs --enable-cli --disable-cgi \ --disable-dependency-tracking ------------------------------------------------------------------------ [2006-01-13 12:08:46] andreas at fink dot org Description: ------------ When you use multiple connections to mysql databases, you should use a resource id in your query which you got back at the time of connection. However this doesnt work anymore. The queries always go to one database. Reproduce code: --------------- mysqladmin create test1 mysqladmin create test2 mysql << --EOF-- use test1 CREATE TABLE \testdata\ (\id\ int(20) NOT NULL auto_increment,\data\ varchar(255) NOT NULL default '', PRIMARY KEY (\id\) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; grant all on test1.* to 'test'@'localhost' identified by 'test'; insert into test1.testdata(id,data) values (1,'This is DB named test1'); use test2 CREATE TABLE \testdata\ (\id\ int(20) NOT NULL auto_increment,\data\ varchar(255) NOT NULL default '', PRIMARY KEY (\id\) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; grant all on test2.* to 'test'@'localhost' identified by 'test'; insert into test2.testdata(id,data) values (1,'This is DB named test2'); --EOF-- now launch php on this: <? $db1_handle=mysql_pconnect("127.0.0.1","test","test"); mysql_select_db("test1",$db1_handle); $db2_handle=mysql_pconnect("127.0.0.1","test","test"); mysql_select_db("test2",$db2_handle); $query = "select data from testdata where id=1"; $result1 = mysql_query($query,$db1_handle); $line = mysql_fetch_row ( $result1 ); echo "This query was executed on db1_handle:" .$line[0] ."\n"; $query = "select data from testdata where id=1"; $result2 = mysql_query($query,$db2_handle); $line = mysql_fetch_row ( $result2 ); echo "This query was executed on db2_handle:" .$line[0] ."\n"; ?> Expected result: ---------------- This query was executed on db1_handle:This is DB named test1 This query was executed on db2_handle:This is DB named test2 Actual result: -------------- On Linux i386: This query was executed on db1_handle:This is DB named test2 This query was executed on db2_handle:This is DB named test2 On MacOS X PPC: This query was executed on db1_handle:This is DB named test1 This query was executed on db2_handle:This is DB named test1 Interesting enough that i386 and ppc are exactly reversed. This might hint to a location which is endian sensitive. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=35993&edit=1