Reasons a query might fail

works in some contexts, but not others

[ergophobe]

I have a bothersome little problem and I don't really know where to start.

I have a very simple query:

DELETE FROM flags WHERE rec_id='1' AND user_id='1'

On the test server, the script runs fine.

On the shared host that it's supposed to run on, the query fails when run from the script. I have the script set up so that failed queries are echoed at the bottom of the page. So it echos the query.

The failed query looks correct (the above example is pasted directly from the error output) and when I copy it and paste it into PhpMyAdmin on the same server (the one it failed on) and it works fine as well.

I don't even know where to begin. Any ideas what could make the query work via the script or command line on one server, via PhpMyAdmin but not via the script on the second server?

shared server: MySQL 4.0.18-standard, PHP 4.3.5
test: MySQL 3.23.43, PHP 4.3.4

[coopster]

What are the MySQL error messages?

mysql_query($sql) 
 or exit('Error (' . mysql_errno() . '): ' . mysql_error());

[ergophobe]

Oooooh that's embarassing!

I forgot that I had set it up so that a user without admin prvilieges could not delete. So I sheepishly admit that the error is:

Error number 1142: delete command denied to user...

On the plus side, though, I did change my DB abstraction layer to facilitate debugging so now it returns the query, the error message and the error number upon failure.

[coopster]

Cool.

BTW, this is not embarrassing. Embarrassing is when you forget to turn your debug variable off after testing and moving the code into production :)

[ergophobe]

Funny you should mention that....

While you were typing that message, if finally occurred to me to set my debug flag to true in my local_settings.php file.

Then in my main settings file:

if(file_exists($_SERVER['DOCUMENT_ROOT'] . "/domain-name/local/local_settings.php")) {
include($_SERVER['DOCUMENT_ROOT'] . "/domain-name/local/local_settings.php");
}

if (!defined('DEBUG')) {
define('DEBUG', false);
}

- the "local" directory does not get uploaded to servers, but stays on my test server only.
- even if I make a mistake, the path would still be invalid, since it's in the /domain-name/ subdir

Of course, in this case, I had to briefly set the debug flag to true on a live server (though with only a couple of 'alpha' testers currently allowed to log in), since it was the only one that had the problem.