0

I'm not sure if this is a duplicate of another question, but I have a small PHP file that calls some SQL INSERT and DELETE for an image tagging system. Most of the time both insertions and deletes work, but on some occasions the insertions don't work.

Is there a way to view why the SQL statements failed to execute, something similar to when you use SQL functions in Python or Java, and if it fails, it tells you why (example: duplicate key insertion, unterminated quote etc...)?

j0k
  • 22,600
  • 28
  • 79
  • 90
Raymond
  • 737
  • 2
  • 12
  • 23

3 Answers3

3

There are two things I can think of off the top of my head, and one thing that I stole from amitchhajer:

  • pg_last_error will tell you the last error in your session. This is awesome for obvious reasons, and you're going to want to log the error to a text file on disk in case the issue is something like the DB going down. If you try to store the error in the DB, you might have some HILARIOUS* hi-jinks in the process of figuring out why.

  • Log every query to this text file, even the successful ones. Find out if the issue affects identical operations (an issue with your DB or connection, again) or certain queries every time (issue with your app.)

  • If you have access to the guts of your server (or your shared hosting is good,) enable and examine the database's query log. This won't help if there's a network issue between the app and server, though.

But if I had to guess, I would imagine that when the app fails it's getting weird input. Nine times out of ten the input isn't getting escaped properly or - since you're using PHP, which murders variables as a matter of routine during type conversions - it's being set to FALSE or NULL or something and the system is generating a broken query like INSERT INTO wizards (hats, cloaks, spell_count) VALUES ('Wizard Hat', 'Robes', );

*not actually hilarious

Community
  • 1
  • 1
Winfield Trail
  • 5,535
  • 2
  • 27
  • 43
2

Start monitoring your SQL queries by starting the log. There you can look what all queries are fired and errors if any.

This tutorial to start the logger will help.

amitchhajer
  • 12,492
  • 6
  • 40
  • 53
0

Depending on which API your PHP file uses (let's hope it's PDO ;) you could check for errors in your current transaction with s.th. like

$naughtyPdoStatement->execute();
if ($naughtyPdoStatement->errorCode() != '00000')
    DebuggerOfChoice::log( implode (' ', $naughtyPdoStatement->errorInfo() );

When using the legacy-APIs there's equivalents like mysql_errno, mysql_error, pg_last_error, etc... which should enable to do the same. DebuggerOfChoice::Log of course can be whatever log function you'd like to utilise

Philzen
  • 3,945
  • 30
  • 46