0

Another little bit which I am missing (as a beginner) I have a Backup script between two DB and as a final step I should optimise the real DB to reduce the size of the overhead. I have a code which works when I use only one DB but I need to use two:

$actconn = mysql_connect($dbhost, $actdbuser, $actdbpass) or die ('act Error connecting to mysql');
$bckconn = mysql_connect($dbhost, $bckdbuser, $bckdbpass) or die (' back Error connecting to mysql');

$ressql="SHOW TABLE STATUS FROM $actdbname WHERE Data_free / Data_length > 0.1 AND Data_free >  102400";

- this should tell that bckuser is ignored, I believe... 

 $res = mysql_query( $ressql, $actconn);

 echo mysql_error(); 
while($optrow = mysql_fetch_assoc($res)) {
  mysql_query('OPTIMIZE TABLE ' . $optrow['Name'], $actconn);
}

actconn value: Resource id #1

bckconn value: Resource id #2

mysql_error says:

SELECT command denied to user 'bckuser'@'localhost' for table 'actual table'

any idea what I do wrong?

UPDATE: bckuser should not do anything with it as actdbuser is the root of that DB, but some reason SHOW TABLE won't recognise $actconn...

UPDATE2: I tried just the opt again and the only thing is changed is the $actdbname='db-name'; when working and $actdbname='db-name' with the spec apostrophe;

please note this is on sharedhost so I can't change the DB name.

thanks @Corbin interesting idea, I tried and didn't work, but how could I debug it? I can't even debug it properly some reason...

TryHarder
  • 740
  • 1
  • 6
  • 22
  • 1
    The error is quite explicit.., you should check permission of the user `bckuser`, it can't do `SELECT`. – j0k Aug 06 '12 at 07:48
  • thanks, please see the update part of it. bckuser should not touch this at all and actdbuser is root on that table – TryHarder Aug 06 '12 at 07:53
  • Hrmm, can you try passing true as a fourth parameter to the mysql_connect call? I suspect that PHP is deciding to silently reuse the first connection. – Corbin Aug 06 '12 at 08:14
  • thanks @Corbin interesting idea, I tried and didn't work, but how could I debug it? I can't even debug it properly some reason... how could I check that? – TryHarder Aug 06 '12 at 08:29
  • Hrmm, am not sure how to go about debugging that. What it looks like to me is that instead of creating a second connection, PHP is re-logging in on the first connection. That would explain why you're getting an error about the wrong user. I guess the only way to be vaguely sure this is happening would be to make 100% sure that the right information is going into both connections and then determining what user each connection is running as. Are you certain that $actdbuser contains what you think it does? – Corbin Aug 06 '12 at 08:31
  • @Corbin, thanks well please find my UPDATE 2 in a minute - but somewhere here is the problem... – TryHarder Aug 06 '12 at 08:53

1 Answers1

1

You need to have a proper grant permissions to user 'bckuser'@'localhost' for executing SHOW TABLE STATUS query.

Unfortunately in MySQL 5.5 there is no separate grant option for this operation. Alternatively you can grant ALL to this user or use root user login for doing this operation. see Grants Manual

Omesh
  • 24,338
  • 6
  • 37
  • 49