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?!
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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
