Member Avatar for riahc3

I have a bunch of insert querys running and some of them fail with a duplciate entry.

I want to see a log where it says "Error: Duplicate key" and that tells me the EXACT INSERT query that fails so I can see what keys are duplicated.

Thanks

Edited by pritaeas because: Restored. Didn't notice the different forum.
  • 3 Contributors
  • 4 Replies
  • 286 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short answer: enable server-side tracing (the general query log) to capture the exact INSERT text, or use an audit plugin to capture SQL + server status. Application-level logging (what suggested) is the smallest change if you control the client; otherwise enable the server log briefly and correlate by connection id. If you only want to silence duplicates, ’s INSERT IGNORE or your REPLACE choice will avoid the error but hide the root cause.

Enable the general query log to TABLE or FILE at runtime, run the workload, then search the log for the failing INSERTs. Example runtime commands (turn off when done):

SET GLOBAL log_output = 'TABLE';   -- or 'FILE'
SET GLOBAL general_log = 'ON';
-- later, to stop:
SET GLOBAL general_log = 'OFF';

Then find INSERTs or filter by connection id:

SELECT event_time, thread_id, argument
FROM mysql.general_log
WHERE argument LIKE 'INSERT %'
ORDER BY event_time DESC
LIMIT 200;

Get the connection id from the client (SELECT CONNECTION_ID()) and match thread_id in mysql.general_log to tie an error you saw in the app to the exact SQL the server received. General log usage, destinations and caveats are documented by MySQL. (dev.mysql.com)

For production or longer-term auditing, use MySQL Enterprise Audit or Percona’s audit plugin — they record the SQL and the command/status so you can query for status = 1062 (duplicate-key) directly. That avoids turning on the noisy general log and supports structured queries/rotation, but may require different server builds or licenses. (dev.mysql.com)

Notes and cautions: general logging can be heavy on I/O and CPU — enable only for short windows and rotate/trim logs. The server will (by default) rewrite passwords in the general log; prepared statements are logged with parameters substituted (at extra cost). REPLACE deletes+inserts (with side effects on auto-increment and FK behavior) — see the manual for REPLACE semantics before relying on it. ()

If immediate debugging is the goal, combine quick app-level error logging (error code + CONNECTION_ID() + bound values) with a short general_log capture — that gives both the "Error: Duplicate key (1062)" and the exact SQL that caused it.

Recommended Answers

All 4 Replies

Member Avatar for rch1231

Hello,

Try INSERT IGNORE in place of INSERT and it will ignore the duplicate key errors.

Member Avatar for Member #120589 
mysql_query($sql); //or use mysqli equivalents
if (mysql_errno() == 1062) {
    echo "Duplicate key error: <pre><code>$sql</code></pre>";
}
Member Avatar for riahc3

I went ahead with REPLACE but thank you

And this isnt a PHP question.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.