0

Possible Duplicate:
Retrieve (or simulate) full query from PDO prepared statement
Get Last Executed Query in PHP PDO
PDO Debugging - View Query AFTER Bind?

Using the PDOException class, I can get various info relating to a SQL error. One part lacking is the actual SQL query which caused the error. This is helpful in troubleshooting integrity constraint violation errors. Even if the full query with inserted parameters is not available, the prepared query along with the array passed by execute() would suffice as I can recreate the full query. Note that I currently do not use bindParam(), but pass all data using execute().

How can I view the actual query that caused the error? Thank you

Error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (website/accounts, CONSTRAINT fk_accounts_zipcodes1 FOREIGN KEY (zipcodes_id) REFERENCES zipcodes (id) ON DELETE NO ACTION ON UPDATE NO ACTION)
File Name: /var/www/main/components/com_upload/models/contacts.phpLine: 520
Time of Error: Monday October 15, 2012, 7:11:49 PDT

SQL which caused error:

INSERT INTO accounts (id, sites_id, name, address, cities_id, zipcodes_id, phone, fax, date_created, date_modified, record_status, vertical_markets_id,priority_id,roles_id) VALUES (:id,123,:name,:address,:cities_id,:zipcodes_id,:phone,:fax,NOW(),NOW(), "active" ,:vertical_markets_id,:priority_id,:roles_id)

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • 2
    Show your code and the error please – Cfreak Oct 15 '12 at 14:01
  • 1
    Read this: http://stackoverflow.com/questions/7716785/get-last-executed-query-in-php-pdo – Matt Humphrey Oct 15 '12 at 14:01
  • Also read this: http://stackoverflow.com/questions/1786322/in-php-with-pdo-how-to-check-the-final-sql-parametrized-query –  Oct 15 '12 at 14:03
  • @Matt and Zlatan. Matt's recommended thread states that it is possible by extending PDOStatement. Zlatan's post on the other hand states that it is not possible, and one must use mysql logs as suggested by Dainis. – user1032531 Oct 15 '12 at 14:22
  • Are you sure you're passing a zipcodes_id? It seems that would be why it would fail. – Cfreak Oct 15 '12 at 14:37
  • @Cfreak. Zipcodes allowed null. Problem was I was passing it an empty string and not null. I figured it out, but it took adding all this test script. I am more looking for a general strategy to trouble shoot, and not this particular problem. Looks like either a wrapper or using the mysql logs might work, but I don't yet know for sure. Thanks – user1032531 Oct 15 '12 at 14:42

1 Answers1

1

The easiest way, to make sure that you get the actual query is activate the mysql log and see what you get as output.

On Windows this can be done adding two lines to your my.ini file:

general_log = 1
general_log_file = "C:\path\to\log\mysql.log"

On Linux you need to modify the my.cnf file:

general_log = 1
general_log_file = /path/to/log/mysql.log
Peon
  • 7,902
  • 7
  • 59
  • 100
  • I added the following to my.cnf and restarted mysql and apache, but mysql.log still doesn't exist. general_log = 1 general_log_file = /var/log/mysql.log – user1032531 Oct 15 '12 at 14:32
  • Dunno how it happens on `Linux`, but for my Windows machine I had to restart Windows itself. It refused to load after `apache` and `mysql` restart. + check for file/directory permission too. – Peon Oct 15 '12 at 14:55