"INSERT command denied to user" -- two connections to two DBs

[stevieontario]

Morning Freaks,

 

PHP Version 5.2.15

MySql server version 5.1.54-log

MySQL client version: 5.1.54

 

I'm trying to run a query (from php) which involves two DBs and (I believe) two connections. I cannot get past this error when I launch the script:

 

A fatal MySQL error occured.

Query: insert into db2.table1 (sourceid2, carname, fuel, output, capability, capabilityfactor, generated, hour) values ('1193', 'BMW', 'GASOLINE', '799', '805', '0.992547', '2010-12-20 20:30:17', '20')

Error: (1142) INSERT command denied to user 'user2'@'localhost' for table 'table1'

 

Here's the code:

$db_hostname = "localhost";
$db_name = "db1";
$db_username = "user1";
$db_password = "password1";
$cxn = mysql_connect($db_hostname,$db_username,$db_password, true) or die ("Could not connect: " . mysql_error());
		mysql_select_db($db_name, $cxn);
$db_name2 = "db2";
$db_username2 = "user2";
$db_password2 = "password2";
$cxn2 = mysql_connect($db_hostname,$db_username2,$db_password2, true) or die ("Could not connect: " . mysql_error());
		mysql_select_db($db_name2, $cxn2);		
	$query1 = "SELECT sourceid as id2, carname, fuel, output, capability, capabilityfactor, generated, version as hour
			FROM performance, sourceinfo
			where
			performance.sourceid=sourceinfo.id
			and
			fuel like 'gasoline%'";


		$result = mysql_query($query1, $cxn)	
			or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query1 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

		while ( $row = mysql_fetch_assoc($result)) {

		extract($row);

	$query2 = "insert into db2.table1 (id2, carname, fuel, output, capability, capabilityfactor, generated, hour)
		values ('$id2', '$carname', '$fuel', '$output', '$capability', '$capabilityfactor', '$generated', '$hour')";
		$result1 = mysql_query($query2, $cxn2)
		or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query2 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
	}

 

I set the fourth parameters in the mysql_connect functions because that's what "sources" -- books and online info -- said  to do in the case of  multiple connections.

 

I also checked with my host -- they tell me I have all the privileges to those DBs/tables.

 

Any thoughts on how/why I'm screwing this up?

 

Thanks,

 

 

 

 

[Pikachu2000]

I'd say you need to call your host again. Clearly, the error message indicates that the user does not have all privileges.

[stevieontario]

Pikachu2000, thanks. When I contacted the host they took my username and password, and put data into the table just to test. I saw what they inserted, and assumed that proves that the user has all the privileges.

 

And now as I tried the same thing from the host's phpMyAdmin, I am still being denied access.

 

I'll check again. Again, thanks,

[awjudd]

I agree with Pikachu here ... If the host was able to add the information into your table, then they are using a different username and password combination since INSERT is not allowed for your user.

 

~judda

[Pikachu2000]

What happens if you set up a script with a connection only to the problem database, and try inserting a record?

[stevieontario]

thanks Juddster. Well there are definitely user access issues -- legitimate or not is hard to say at this time.

 

I thought that as the account owner I had global access privileges, but that's obviously not the case. Weird, because I can, from the host's cPanel, create new DBs and grant access. But then I can't insert to that table, even from the host's phpMyAdmin.

 

Pika, don't know if that answers your 2nd question. From phpMyAdmin, I clicked on the relevant DB then tried an insert query. Access denied but this time to the global user (who I thought was me).

[fenway]

Dump the grants.

[stevieontario]

fenway, thanks. But what do you mean, dump the grants? I checked out on "the net" and couldn't find anything that didn't involve transferring DBs from one host to another.

[fenway]

Well, it seems like you were having trouble with DB access -that's related to the grants - so "SHOW GRANTS" to see what you've been granted.

[stevieontario]

okay, so by "dump" you meant "display." I thought you meant get rid of them!

 

It's purely bizarre. From the top of the tree (i.e., at the localhost level), it says I have all privileges to the DB in question.

 

But from the level of the DB in question, I can't even do the SHOW GRANTS -- access denied!

[Pikachu2000]

What do you mean by 'at the localhost level'?

[stevieontario]

Pika -- when I first login to the host's cPanel and load phpMyAdmin, localhost is the first item that appears over the tabs ("Databases", "SQL", "Status", etc.) in the main window.

[stevieontario]

okay, I just managed to get the script to stop giving me that error: I put the full name of db2 into the query $query2.

 

So, instead of

$query2 = "insert into db2.table1 (field1, field2, etc.)

 

I put

$query2 = "insert into mainuser_db2.table1 (field1, field2, etc.)

 

and presto -- data is inserted, no fatal error.

 

I don't know why that worked, since I did not give the full DB name of db1 for the SELECT query ($query1). I thought that since I used the same $db_hostname for both connections I wouldn't have to get particular about the DB names.

 

[Pikachu2000]

That was sort of where I was going with this. I was starting to think it was a db selection issue, but I was about to suggest using the mysqli extension instead of the mysql extension, so each connection would then carry its own database selection argument.

[stevieontario]

Well, every time I decide to avoid learning critical subjects (like MySQL access rights), those same issues catch me down the road. Thanks everybody for your help, and I'll read up/practice the mysqli thing.

 

Steve