I have written this to the PHP-DB list and couldn't get any answers. I have gone through all the documentation I could find. I have been everywhere and cannot find an answer to this problem! Perhaps someone on this list can lend me some insight?!
We can't tell. You don't show any of your queries.
Also, you use "server" is ambiguous fashion. It seems to mean web server in some cases, and MySQL server in other cases. So it's not really clear what your setup is. We might guess correctly, but you're more likely to get a better response if you re-read your message from the point of view of someone who knows nothing of your setup to see if such a person would have an idea of what you're trying to say. Where that is not true, please try to add clarifying details.
I think, at heart, the problem is with MySQL permissions or some way 4.0 handles connections across a network, however I just cannot figure it out.
I'm using PHP 4.3.2+MySQL 4.0.14 on server 1 Server 1, however, is connecting to Server 2 which has MySQL 4.0.12
I have permissions on the 4.0.12 server setup like: user: localhost user1 all main privs are 'N' except for "File_priv" 192.168.1.% user1 all main privs are 'N' except for "File_priv" (internal SAN).
db: % user11 my_database all main privs set to "Y" (select, insert,update, delete, create, drop, references, index, alter)
If I use the command-line interface from server 1 to server 2 it works perfectly fine.
I'm using PHP connection to do code like:
$conn = mysql_connect("192.168.1.2","user1","pass"); mysql_select_db("my_database",$conn);
mysql_select_db returns true.
My queries are like: mysql_query($sql,$conn); So I'm using the proper connection.
The webpage mysql_error outputs "select command denied to user: '[EMAIL PROTECTED]' for table 'table_name'"
For every table. And I wonder what the heck is this for because the select priv is on for that table in the 'db' table.
So, what I did was change the 'user' table with select_priv to "Y" which basically make this user a super-user for every table. After doing this I get these errors:
Table 'another_database.table_name' doesn't exist.
I didn't select another_database as my table! I selected my_database as my table! Why the heck is it selecting the wrong table?!
So, I added a ton of bebugging code. I confirmed that just before calling mysql_select_db() I had the right table in the variable. I confirmed I'm only making one database connection, not two. I confirmed that this user has no permissions for another_database with that user/pass. I confirmed the same Reference ID for $conn. Is being used in every case.
I know the reason I'm getting the initial "select command denied" error is because it's still selecting another_database for the table and I don't have permissions for that unless I add it for super-user like I did.
A work around (which will not work once I get this site in production) is to do a search/replace on the SQL strings and add "my_database." to every database table. Once the SQL command becomes "select xxx from my_database.table_name" it works fine but this is not what I can do in the end. What the heck could be the problem?! Why doesn't PHP select the right table?!
This problem happens with persistant or non-persistant connections.
Oh, and another site using the same code and the same PHP install (same server1) but connecting to a 3.23.xx database works perfectly fine. Just to throw a wrench in the works.
I've been through all of the mysql docs can't find a thing. Do you guys know where I'm going wrong?
--
Thomas Deliduka IT Manager ------------------------- Xenocast Street Smart Media Solutions http://www.xenocast.com/
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
