Upgrading from mysql 5.1 to 5.7, Sequential run of DDL queries via a perl script, gets stuck with reason "Waiting for table metadata lock" -

i have perl script uses dbd::mysql , performs operations create tablesand performs insert, update, alter, delete select queries on them. data being loaded here xml file. im here trying upgrade mysql 5.1 mysql 5.7. , facing issue, loading gets stuck while using script.

when checked show processlist output below:

mysql> show full processlist; +----+------+-----------+---------+---------+------+---------------------------------+----------------------------+ | id | user | host      | db      | command | time | state                           | info                       | +----+------+-----------+---------+---------+------+---------------------------------+----------------------------+ | 14 | test | localhost | regress | sleep   |   17 |                                 | null                       | | 15 | test | localhost | regress | query   |   16 | waiting table metadata lock | optimize table test.source | | 16 | test | localhost | null    | query   |    0 | starting                        | show full processlist      | +----+------+-----------+---------+---------+------+---------------------------------+----------------------------+ 

i have not faced issue while using mysql 5.1.

here every query running, before query run, autocommit set 0 apart insert , delete not performing commit after running query.

i know if bug latest mysql, or change have make in code perform manual commit after select query run runs different session in script.(ref comment : https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html).

i have tried killing process sleeping different mysql session, , script went further. tried put commit statements after select statements , without killing process went fine, still getting stuck while running different sequence of above set of queries - know placing commit statements - right?

is there variable or parameter have set - needs changed when upgraded mysql 5.7?

is there workaround above issue?

how find sleeping thread created?

is there way identify live running query in mysql?

updating question: there way functionality of myisam db engine in 5.1 after upgrading mysql 5.7 perl scripts.

using parameters default-storage-engine , disabled-storage-engines in my.cnf set : default-storage-engine=myisam disabled-storage-engines=innodb

tried load xml data script.

autocommit variables status:
mysql 5.7 - select @@autocommit

mysql> select @@autocommit;   +--------------+   | @@autocommit |   +--------------+   |            1 |   +--------------+   

perl dbh autocommit via script when connecting db still set unset as: $self->{dbh}->{autocommit}=0;

the table locking issue still persists.

snip of engine status:

mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | engine             | support | comment                                                        | transactions | xa   | savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | mrg_myisam         | yes     | collection of identical myisam tables                          | no           | no   | no         | | csv                | yes     | csv storage engine                                             | no           | no   | no         | | performance_schema | yes     | performance schema                                             | no           | no   | no         | | blackhole          | yes     | /dev/null storage engine (anything write disappears) | no           | no   | no         | | innodb             | yes     | supports transactions, row-level locking, , foreign keys     | yes          | yes  | yes        | | myisam             | default | myisam storage engine                                          | no           | no   | no         | | archive            | yes     | archive storage engine                                         | no           | no   | no         | | memory             | yes     | hash based, stored in memory, useful temporary tables      | no           | no   | no         | | federated          | no      | federated mysql storage engine                                 | null         | null | null       | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 

is there way can disable transaction management on mysql5.7? let me know if need set other flag above requested functionality.?

thanks in advance.