I want to practice rolling back commands, savepoints and more on phpMyAdmin but I couldn't find a way to turn off autocommit in phpMyadmin. Currently I use the command SET autocommit = 0;.

I want to know how, instead of typing this command before each query, if there is another way to perform this task?

  • Is your development/experimental machine Linux or Windows (or something else)? – Vérace Jan 11 '20 at 9:44
  • @ Vérace Windows – Ransaka Ravihara Jan 11 '20 at 9:54
  • Switch to the mysql commandline tool. It gives you direct control. – Rick James Jan 11 '20 at 16:47

You can do this in 3 different ways:

The best answer to this question depends on the context of your request.

If you are doing this from time to time on a dev/test machine (and I certainly hope you're not doing it on an important/production one!), you can then afford to make global changes and you could:

  • Edit the database interface file inside the "libraries" folder as described here and put the line SET AUTOCOMMIT = 1; in the postConnect function. This will affect every connection made from that instance of phpMyAdmin - you may or may not want this, depending on whether you also use that machine's phpMyAdmin for running stuff on prod?

  • Put the line autocommit = 0 to set the variable in your my.cnf/my.ini. This now means that every connection to that server will automatically commit (described here). Again, you may or may not want to do this.

  • Finally, if this is something that you'll want to do from time to time - i.e. experiment at lunchtime/whatever, you can issue a BEGIN [WORK] before you issue your SQL statements! (ibid - aliases of START TRANSACTION - see here).

    This has the advantage of not affecting the configuration of either phyMyAdmin or the MySQL server, BUT has the disadvantage of the dev (i.e. you) having to remember to put it in each time. NOT advisable on a production machine, but means that you can use your db tool on both work systems and your experimental ones without worry!

The choice is yours!

  • Thank you so much!! – Ransaka Ravihara Jan 11 '20 at 10:51
  • Glad you found it helpful! – Vérace Jan 11 '20 at 10:52

Log into phpMyAdmin, select the database and then click the appropriate table in the left navigation area. In the main pane you will see the list columns etc. At the top is a navigation area, as shown in the screenshot below, with "Browse", "Structure" etc. Click the "Operations" button.Then select table operations and change Storage Engine as Inno_db that works for me! Thanks!

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.