Re: [PHP-DB] AGONIZING Mysql Select DB issue.
As I mentioned your suggestion here does work, however while the example was simple, the application is very extensive and changing every SQL call is not possible. On 8/17/03 4:56 PM this was written: Try using the SQL to select which database. example, instead of: select * from table1 use: select * from database1.table1 if that works, and the php command doesn't that may mean the the mysql client lib is broken, although, I've been using it with mysql 4 and it seems to work fine. -Micah On Sunday 17 August 2003 1:49 pm, Thomas Deliduka wrote: I'm not making two connections, I'm making one and only one call to mysql_connect. Also, there is no way in that function as per the definition page of it (http://us3.php.net/mysql_connect) to have the database selected as per your example below. With my connection though, when I do: $dbh = Mysql_connect(blah, blah, blah) Mysql_select_db(db1) I do call: Mysql_query(query, $dbh); For some reason even though I am calling mysql_select_db(db1) it is latching onto the first available database it has access to (or not as the case/permissions may be) and chooses db2 instead. I don't know why, connecting to the MySQL 3.23 it selects the right database, connecting to the Mysql 4.x server it doesn't allow a selecting of the table even though I do the select function and it returns true as it was selected properly. On 8/16/03 12:23 AM this was written: If you are doing this: $dbh = mysql_connect(db1, blah blah blah); $dbh2 = mysql_connect(db2, blah blah blah); Then $r = mysql_query(select * from mytable); will use the db2 connection, because it is the most recent. However, if you do this: $r = mysql_query(select * from mytable, $dbh); it will use the first connection, as specified in the handle that is passed back by mysql_connect. mysql_query uses the most recent connection by default; you can override this action by specifying which DB handle to use for a given query. Replace $dbh with $dbh2 to select from tables on the second database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
I am not using $dbh and $dbh2 I'm only making ONE connection to the database. I AM using mysql_query($sql,$dbh) when I make the call. I am using mysql_select_db($dbname,$dbh) to do the connection to the database but it's not selecting the one that I want. It's choosing another one and I don't know why! My statement that the 3.23 server chooses the right one is with another website setup almost the same way. In fact, the code is the same for both but the main database server for each is different. The only difference between the two is that the one connecting to the 4.x server doesn't selected the database when the one connecting to the 3.23 server (another separate website) does select the right one. On 8/16/03 12:26 AM this was written: Oh yeah -- just swap the $dbh and $dbh2 variables on the other server, and your code will work both places (the only difference being $dbh is the server the code is on, and $dbh2 is the other server). I would use the handle in your mysql_query calls, rather than the mysql_select_db(); gets too weird. Just open two connections, one to each (if you must) and go from there. From the docs: mysql_select_db() sets the current active database on the server that's associated with the specified link identifier. If no link identifier is specified, the last opened link is assumed. If no link is open, the function will try to establish a link as if mysql_connect() was called without arguments, and use it. Every subsequent call to mysql_query() will be made on the active database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: The ONLY way I have been able to work around this issue is before I call the SQL query I have to add db1.mytable to every call to the table. I.e. Adding db1 to the beginnign. Then it works around the call to the wrong database. This cannot happen all the time. The application is very extensive and the code is shared between sites so I can't just do this. Does anyone have any clue why even though I'm calling mysql_select_db(db1) it still tries to gather data from db2? This only happens when I'm connecting from Server 2 to server 1 it does not happen any other time. Incidentally it doesn't happen when I'm connecting from server 2 to another server running mySQL 3.23.54 there is no problem at all. I'm thinking it's a permissions system or something funky with MySQL 4 but I don't know for sure. Any ideas? -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
Agreed, sounds like a pain. to keep two copies. But if you do it to both copies, and use the same code both places (connect via URL, not 'localhost' even if you are on the same machine) then it wouldn't be any extra trouble. The OS will realize that the URL is localhost and make that connection via a socket anyways. Something else to check; if you're using two logins, one for remote, one for local, verify that their database permissions are the same. -Micah On Monday 18 August 2003 8:11 am, you wrote: The worst thing is that The SAME CODE that is used to connect via 'localhost' on the server that holds the MYSQL installation works fine. Why the heck does it work locally but not via a server to server connection? I keep a code pool for several sites, I don't want to have to do a search/replace and keep two versions of my code on each server. That is a pain in the arse. On 8/18/03 11:05 AM this was written: Just do a global search/replace on the table names. *shrug* should do the trick. On Monday 18 August 2003 6:21 am, you wrote: As I mentioned your suggestion here does work, however while the example was simple, the application is very extensive and changing every SQL call is not possible. On 8/17/03 4:56 PM this was written: Try using the SQL to select which database. example, instead of: select * from table1 use: select * from database1.table1 if that works, and the php command doesn't that may mean the the mysql client lib is broken, although, I've been using it with mysql 4 and it seems to work fine. -Micah On Sunday 17 August 2003 1:49 pm, Thomas Deliduka wrote: I'm not making two connections, I'm making one and only one call to mysql_connect. Also, there is no way in that function as per the definition page of it (http://us3.php.net/mysql_connect) to have the database selected as per your example below. With my connection though, when I do: $dbh = Mysql_connect(blah, blah, blah) Mysql_select_db(db1) I do call: Mysql_query(query, $dbh); For some reason even though I am calling mysql_select_db(db1) it is latching onto the first available database it has access to (or not as the case/permissions may be) and chooses db2 instead. I don't know why, connecting to the MySQL 3.23 it selects the right database, connecting to the Mysql 4.x server it doesn't allow a selecting of the table even though I do the select function and it returns true as it was selected properly. On 8/16/03 12:23 AM this was written: If you are doing this: $dbh = mysql_connect(db1, blah blah blah); $dbh2 = mysql_connect(db2, blah blah blah); Then $r = mysql_query(select * from mytable); will use the db2 connection, because it is the most recent. However, if you do this: $r = mysql_query(select * from mytable, $dbh); it will use the first connection, as specified in the handle that is passed back by mysql_connect. mysql_query uses the most recent connection by default; you can override this action by specifying which DB handle to use for a given query. Replace $dbh with $dbh2 to select from tables on the second database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
Didn't work. On 8/18/03 11:51 AM this was written: Same login for remote and local but I wonder if I did create a remote-only user it will work better. On 8/18/03 11:33 AM this was written: Agreed, sounds like a pain. to keep two copies. But if you do it to both copies, and use the same code both places (connect via URL, not 'localhost' even if you are on the same machine) then it wouldn't be any extra trouble. The OS will realize that the URL is localhost and make that connection via a socket anyways. Something else to check; if you're using two logins, one for remote, one for local, verify that their database permissions are the same. -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
I'm not making two connections, I'm making one and only one call to mysql_connect. Also, there is no way in that function as per the definition page of it (http://us3.php.net/mysql_connect) to have the database selected as per your example below. With my connection though, when I do: $dbh = Mysql_connect(blah, blah, blah) Mysql_select_db(db1) I do call: Mysql_query(query, $dbh); For some reason even though I am calling mysql_select_db(db1) it is latching onto the first available database it has access to (or not as the case/permissions may be) and chooses db2 instead. I don't know why, connecting to the MySQL 3.23 it selects the right database, connecting to the Mysql 4.x server it doesn't allow a selecting of the table even though I do the select function and it returns true as it was selected properly. On 8/16/03 12:23 AM this was written: If you are doing this: $dbh = mysql_connect(db1, blah blah blah); $dbh2 = mysql_connect(db2, blah blah blah); Then $r = mysql_query(select * from mytable); will use the db2 connection, because it is the most recent. However, if you do this: $r = mysql_query(select * from mytable, $dbh); it will use the first connection, as specified in the handle that is passed back by mysql_connect. mysql_query uses the most recent connection by default; you can override this action by specifying which DB handle to use for a given query. Replace $dbh with $dbh2 to select from tables on the second database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
Try using the SQL to select which database. example, instead of: select * from table1 use: select * from database1.table1 if that works, and the php command doesn't that may mean the the mysql client lib is broken, although, I've been using it with mysql 4 and it seems to work fine. -Micah On Sunday 17 August 2003 1:49 pm, Thomas Deliduka wrote: I'm not making two connections, I'm making one and only one call to mysql_connect. Also, there is no way in that function as per the definition page of it (http://us3.php.net/mysql_connect) to have the database selected as per your example below. With my connection though, when I do: $dbh = Mysql_connect(blah, blah, blah) Mysql_select_db(db1) I do call: Mysql_query(query, $dbh); For some reason even though I am calling mysql_select_db(db1) it is latching onto the first available database it has access to (or not as the case/permissions may be) and chooses db2 instead. I don't know why, connecting to the MySQL 3.23 it selects the right database, connecting to the Mysql 4.x server it doesn't allow a selecting of the table even though I do the select function and it returns true as it was selected properly. On 8/16/03 12:23 AM this was written: If you are doing this: $dbh = mysql_connect(db1, blah blah blah); $dbh2 = mysql_connect(db2, blah blah blah); Then $r = mysql_query(select * from mytable); will use the db2 connection, because it is the most recent. However, if you do this: $r = mysql_query(select * from mytable, $dbh); it will use the first connection, as specified in the handle that is passed back by mysql_connect. mysql_query uses the most recent connection by default; you can override this action by specifying which DB handle to use for a given query. Replace $dbh with $dbh2 to select from tables on the second database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] AGONIZING Mysql Select DB issue.
Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 My HTTP is setup on Server 2 (and server 1 as well) I'm setting up a mirror on Server 2 and it connects to the DB on server 1. Privs are setup that user has DB access to server 1 from 2 and all that. I've been working on this for over 2 hours so I am sorry for being very clear. My connection string in PHP connects to server 1's DB and then I have a mysql_select_db() command to select the database. My debugging code shows that I'm selecting the right DB (let's call it db1) and it returns true as if it selected fine. However, whenever I do a query I get an error like Table 'db2.mytable' doesn't exist (Notice db2 not db1) (there are like 30 databases on here I'm just using these two as an example). It's latching onto the wrong database! Now I get this error if I enable Select_Priv in the User table, if I turn that off and rely only on the privs in the 'db' table I get select command denied to user: '[EMAIL PROTECTED]' for table 'mytable' because it's attempting to do a select on db2 not 1. I don't know why. The ONLY way I have been able to work around this issue is before I call the SQL query I have to add db1.mytable to every call to the table. I.e. Adding db1 to the beginnign. Then it works around the call to the wrong database. This cannot happen all the time. The application is very extensive and the code is shared between sites so I can't just do this. Does anyone have any clue why even though I'm calling mysql_select_db(db1) it still tries to gather data from db2? This only happens when I'm connecting from Server 2 to server 1 it does not happen any other time. Incidentally it doesn't happen when I'm connecting from server 2 to another server running mySQL 3.23.54 there is no problem at all. I'm thinking it's a permissions system or something funky with MySQL 4 but I don't know for sure. Any ideas? -- Thomas Deliduka IT Manager - Xenocast Street Smart Media Solutions http://www.xenocast.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AGONIZING Mysql Select DB issue.
If you are doing this: $dbh = mysql_connect(db1, blah blah blah); $dbh2 = mysql_connect(db2, blah blah blah); Then $r = mysql_query(select * from mytable); will use the db2 connection, because it is the most recent. However, if you do this: $r = mysql_query(select * from mytable, $dbh); it will use the first connection, as specified in the handle that is passed back by mysql_connect. mysql_query uses the most recent connection by default; you can override this action by specifying which DB handle to use for a given query. Replace $dbh with $dbh2 to select from tables on the second database. Peter On Fri, 15 Aug 2003, Thomas Deliduka wrote: Here's the stats: Two servers: Server 1, Mysql 4.0.12, PHP 4.3.2, apache 1.3.27 Server 2, Mysql 4.0.14, PHP 4.3.2, apache 1.3.27 --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
