Jump to content

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


stevieontario

Recommended Posts

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,

 

 

 

 

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.