0

How can I determine whether a particular column in a MySQL table is ever SELECTed or explicitly UPDATEd or INSERTed or referenced in a WHERE clause? It'd be nice if I could determine the db user who is using the column, too.

This is on MySQL 5.0, but I could move that to 5.6 or 5.7.

Background: I'm maintaining a legacy app with an AUTOINCREMENT field ("id") that will foreseeably reach its maximum INT value before the app is retired.

I suspect, but do not know, that no one actually references this field in SQL at all. Ideally I'd "monitor" the column for a week or two and, if it appeared in no queries, I'd turn it into a BIGINT or drop it in favor of a natural key. If the column does appear in someones' queries, I'll need to track that person down and coordinate the change.

  • All you need to worry about for auto_incrementing are INSERTs. YOu can put in a TRIGGER to log this. Why would detecting SELECTs affect anything? – Vérace Jun 30 '16 at 21:24
  • @Vérace, is my edit clearer? I want to get rid of this column but only if I'm sure it won't break any actual use. – pilcrow Jun 30 '16 at 21:36
  • Still don't see what the problem is. Nobody's UPDATEing this column, so the auto_increment won't be affected. No need to change it to BIGINT - seems like you're looking for work which isn't there. – Vérace Jun 30 '16 at 21:55
  • @Vérace, people still INSERT into this table, and id is populated implicitly. Hence the possibility that the id field will hit its maximum unless I do something. Anything I do in DML to that column risks confounding a client that assumes that the id field exists and is an INT. – pilcrow Jun 30 '16 at 21:59
  • OK, why not just modify your column to a BIGINT? – Vérace Jun 30 '16 at 22:06
1

Mind the Gap

Let me give you a third option if the ids have a lot of gaps...

One significant cause of "running out of id values" is queries that leave gaps in AUTO_INCREMENT values. An INSERT statements where row is not inserted first allocates an auto-inc value, then fails to use it. INSERT IGNORE will do this silently.

Sometimes a useful workaround is to change the code such that it does not do the INSERT if it is not going to succeed. Normalization is perhaps a normal case for this. Here is a technique for batch normalization that avoids the problem.

(OK, this suggestion is not useful if you can't get to the source code. But it could be useful to other readers; it has saved me a few times.)

General log

By turning on the "general log" for a period of time, you can collect all queries. Then you can search for the table or "id", etc. It will be tedious. It will not be 100% safe, since some queries may not have been executed during that time.

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode did not exist until 5.5, so that is not an option.

(Again, I mention this for other readers.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.