I have global autocommits off but I was wondering if it were possible to set autocommit on for PHPMyAdmin during login without doing so manually. Turning off autocommit makes the PHPMyAdmin GUI not work properly (clicking 'delete' on a row won't actually delete it since there isn't a commit). Since PHPMyAdmin creates a new session on each page load, setting autocommit on doesn't do anything since it immediately gets 'wiped out'. I have to manually type in queries followed by a commit.
2 Answers
You could set a trigger to watch for a session coming in from phpmyadmin or a specific user. That trigger could fire once the session is created and enable autocommit. Since MySQL 5.7 still doesn't support all the DDL command you could use init_connect to drive the procedure when a user connects.
Here is a document outlining the init_connect operation. Logon Init_connect operation
-
3Can you explain how we can define a trigger that is activated when a session starts? Commented Mar 28, 2017 at 18:39
-
1My mistake. Looks like MySQL 5.7 still doesn't support some DDL commands. However, this document does look promising for having init_connect drive the trigger like functionality. I wouldn't know the performance impact of this kind of operation. Logon/Startup trigger– SpaceManCommented Apr 4, 2017 at 13:20
-
1I thought you might be referring to that. Add it in the answer! Commented Apr 4, 2017 at 13:34
It seems PhpMyAdmin does not have a config option for autocommit.
Anyway, I have found a very simple work-around modifying the code.
Edit the database interface file inside the "libraries" folder.
Look at the "postConnect" function, it seems to be executed after every connect.
At the end of the funcion, add a line that invokes "SET AUTOCOMMIT = 1;".
You can take the line that executes "SET NAMES" as an example.
Now every time PhpMyAdmin opens a connection, it sets autocommit on it.
This trick worked for me with PhpMyAdmin version 3.3.2 and 4.4.15, but I suppose it can work on every version.
