Why is there a SET AUTOCOMMIT=0 in the SQL of a database export

[Mattie112]

The first 4 lines of a database export with default settings in phpMyAdmin are:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

The last 4 lines contain:

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I see that the AUTOCOMMIT is disabled but never enabled. I have noticed because we imported a SQL (generated by phpMyAdmin) and in the same session also executed some INSERT statements. These rows where not visible in the database (because they where never committed).

In the MySQL docs I read this:

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

This states that a SET AUTOCOMMIT = 0 automatically when starting a transaction. Is there a reason why phpMyAdmin disables the autocommit and then never enables it? Is it possible to remove the SET AUTOCOMMIT=0 or add a SET AUTOCOMMIT=1 on the last line of the export?

[williamdes]

Seems like a bug, thank you for reporting it !

[ibennetch]

I also can't think of a reason why this would be the intended behavior. Thanks for reporting this!

[kartik1000]

@williamdes I am willing to work on this issue :)

[williamdes]

@kartik1000 I assigned it to you for now

[kartik1000]

What should I prefer removing SET AUTO COMMIT = 0 or Adding SET AUTO COMMIT = 1? @williamdes

[Mattie112]

I would suggest removing the SET AUTOCOMMIT = 0 is it is already done according to the MySQL docs.
https://dev.mysql.com/doc/refman/8.0/en/commit.html

[kartik1000]

ok @Mattie112

[Mattie112]

And just to confirm:

The docs state this for 8.0, 5.7, 5.6 and 5.5

The MariaDB docks also state this:
(https://mariadb.com/kb/en/start-transaction/)

To disable autocommit mode for a single series of statements, use the START TRANSACTION statement.