Log queries executed on a UI tool like phpMyAdmin

Question

I am trying to populate a history table in my database whenever information_schema.PROCESSLIST gets updated. I tried:

CREATE TRIGGER populate_history 
AFTER INSERT ON information_schema.PROCESSLIST
FOR EACH ROW 
  INSERT INTO myDatabase.history_changes (host, db, query)
  VALUES (new.HOST, new.DB,new.INFO);

But I am getting

Access denied for user 'root'@'localhost' to database 'information_schema'

I have already looked into this answer but it wasn't of any help.

Can someone point me what I am doing wrong?

UPDATE : As suggested by @zozo, I am going to explain my end goal -

I want to log everything done MANUALLY by a user in the database. Our customers have access to our production database and sometimes they manually edit a row in some table which cause errors on the app. I know our main emphasis should be not allowing them the production db access, but somehow thats not possible currently and I need something to log what they do manually with the tables.

The architecture we use:

We are using AWS RDS MySQL instance. We are using PHP and nodejs to connect to it.(PHP majorly). The codebase is on a separate AWS instance.

The customers use phpMyAdmin to edit records

AFAIK `information_schema` is not a "real" database, but rather some kind of "views", you shouldn't be able to create triggers on them. — Matteo Tassinari, Apr 11 '23 at 12:34
no it doesn't. yes information_schema has views, but isn't there any way this can be done? — Vishal Dubey, Apr 11 '23 at 12:37
It actually **does** answer your question, as the first answer clearly states **you can only read the contents of tables**, this implies you can't do anything else, included creating triggers. In fact, you cannot have triggers on views. — Matteo Tassinari, Apr 11 '23 at 12:40
@VishalDubey Cheers. The error may be a bit missleading so I'm gonna ignore that and assume access is actually right (just to confirm though can you select anything from information schema?). Now getting back on it the actual issue... it seem like you want to log the queries? Do I get that right? If I get it write... why not just use the logs instead for it (https://stackoverflow.com/questions/303994/log-all-queries-in-mysql)? What is the actually end goal (because the way you are approaching will not work). — zozo, Apr 11 '23 at 12:42
@zozo I want to log everything done MANUALLY by a user in the database. Our customers have access to our production database and sometimes they manually edit a row in some table which cause errors on the app. I know our main emphasis should be not allowing them the production db access, but somehow thats not possible currently and I need something to log what they do manually with the tables. I hope I explained well. Thanks — Vishal Dubey, Apr 11 '23 at 12:46
Ugh... that's tough. Reading from process list would not help anyway in that case (since everything ends up in there). I am not aware of any way to differentiate between the manual and "not manual" case except checking the connection... in logs (like here: https://serverfault.com/questions/477162/mysql-logs-of-user-activity-by-ip-address). What I would do (I am ignoring the "production access" thingy). I would either create a special user and check for this, either check connection ip. But the answer wildly differ on how you are using your systems. — zozo, Apr 11 '23 at 13:03
If you can describe the architecture completely I may be able to suggest some actual solutions (also you should edit your question to reflect that). As it stands now... what I can tell for sure is the the approach will not work. :) — zozo, Apr 11 '23 at 13:08
@zozo Thanks a ton. So we are using AWS RDS MySQL instance. We are using PHP and nodejs to connect to it.(PHP majorly). The codebase is on a separate AWS instance. I am updating the question shortly — Vishal Dubey, Apr 11 '23 at 13:12
Tyvm. And what do you mean by manually? Do you have a specific screen in your app where you say "input query here"? Or you have a direct mysql connection for the users (cli or some client)? — zozo, Apr 11 '23 at 13:15
If is the first one I can suggest a fairly simple trick, consisting in always appending a comment to the query (like -- this is a manually executed query; you can even include the user here). If is a client, or phpmyadmin, or cli is a bit more complicated. You will need to make different users for app and for the users that use phpmyadmin (can be a single one for those, but needs to be different from the app). And then log those. Or, if you have a different pma server than the app, you can base it on the ip of the machines (but those can change and you need to take that into account). — zozo, Apr 11 '23 at 13:19
And then if you want those to get into db, just parse the logs and save them (you can use a heartbeat system or a cron for this). — zozo, Apr 11 '23 at 13:21
I like that idea. Will need to make some tweaks in the existing system. Thank you. FYI - I just edited the question — Vishal Dubey, Apr 11 '23 at 13:25

score 1 · Accepted Answer · answered Apr 11 '23 at 13:37

Based on the edits, and the discussion in comments, I can suggest the following solution:

You will need to make different users for app and for the users that use phpmyadmin (can be a single one for those, but needs to be different from the app). And then log those.

Or, if you have a different pma server than the app, you can base it on the ip of the machines (but those can change and you need to take that into account).

And then if you want those to get into db, just parse the logs and save them (you can use a heartbeat system or a cron for this).

score 1 · Answer 2 · answered Apr 11 '23 at 19:25

phpMyAdmin does have some query logging built in which may be worth exploring. I do not use PMA so I have no idea how good/configurable it is.

As an alternative to writing the general_log to a file, you can direct MySQL's log_output to a database table and turn on the general_log:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 1;

Note: changing log_output to table applies to both the General Query Log and Slow Query Log.

You can check the structure of the table used with:

SHOW CREATE TABLE mysql.general_log;

By default it is created with ENGINE=CSV and I would suggest changing the engine to MyISAM (the only alternative):

SET @old_log_state = @@GLOBAL.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

Depending on your traffic, the general log can grow very quickly and if you plan to have it running all the time you will need to create some maintenance tasks, probably as scheduled events, to stop the table growing out of control. Purge all but the logs you are interested in, frequently.

Another way of tackling this might be to use a proxy (like ProxySQL) as the middle man between PMA and your MySQL server. Then you could implement filtering in the proxy to only log the queries you are interested in.

Log queries executed on a UI tool like phpMyAdmin

2 Answers2