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.
Best Answer
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_INCREMENTvalues. AnINSERTstatements where row is not inserted first allocates an auto-inc value, then fails to use it.INSERT IGNOREwill do this silently.Sometimes a useful workaround is to change the code such that it does not do the
INSERTif 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_modedid not exist until 5.5, so that is not an option.(Again, I mention this for other readers.)