CODEWITHSUNDEEP
mysqldatabaserefactoringrefactoring-databases
TheStoryCoder
TheStoryCoder

Reputation: 3640

Is there a way to log read/write access to a specific column in MySQL?

Is there any way in MySQL to log any query that accesses a certain column in a table?

Purpose: I need to do this for refactoring purposes - I have to change the structure of the database but because I was not smart enough at the time to use unique searchable column names it is now very difficult for me to locate ALL the places in my PHP code where I deal with that column. So if I could start logging all queries that access that column over time I could have a complete overview of exactly which queries use that column and therefore be able to modify my PHP code (assuming that all possible queries had been run some time in that period).

Upvotes: -1

Views: 251

Answers (1)

user2246674
user2246674

Reputation: 7719

While I wrote my misgivings below, this question relates to "auditing" and "logging".

Basic statement logging can achieved using the General Query Log and more advanced auditing can be performed by the Enterprise Audit plugin. Triggers are not appropriate for logging queries but have been known to implement update/insert change log trails.

I think that trying to find the usage programmatically is misguided. Refactoring is not an "ongoing" process - it's done once (per refactor goal) and life moves on. Tests (e.g. unit/integration) and code-coverage can tell the results of refactoring, but generally work best with a formalized DAL/API.

Instead, this is a good time to sit down with the code-base and carefully analyze all database access calls. Even a terrible column name like id is relatively easy to grep through files for if merely trying to find certain occurrences. Chances are it'll only require an hour or two to "feel comfortable" about the manual changes. Now, if only such changes could be tested ..

It would also be prudent to refactor all access out into named methods with well-defined contracts, which could later be pushed up into a proper DAL, even if they are currently left at the call-sites. This separation of concerns is the basis underpinning testability and future refactoring.

Upvotes: 0

Related Questions