Protect db_handle methods against "MySQL server has gone away"

Created by: muffato

Use case

The issue was raised by @MatBarba . In ENSCORESW-2913 he managed to identify that a Python Runnable that just sleeps would raise the following error after 30 min:

DBD::mysql::db primary_key failed: MySQL server has gone away at (...)/modules/Bio/EnsEMBL/Hive/DBSQL/BaseAdaptor.pm line 244, <$CHILD_RDR> line 6.

30 min is indeed the timeout parameter of the server he was using.

Description of the changes

The primary_key "dbh" method (and a few others) were indeed called on database handles without checking if the handle is still valid, and without any eval {} protection, even though such protection is available via the AUTOLOAD in DBSQL::DBConnection. The fix is to move those calls from the database handle to the database connection (new "best practice").

prepare is slightly more tricky to fix since there is already a prepare in DBConnection. In this case, I have introduced a fake DBConnection method named protected_prepare that calls prepare on the database handle with the "MySQL server has gone away" protection, but without disconnecting from the database even if disconnect_when_inactive is set. Indeed, prepare is most often called on SELECT statements, which are immediately followed by execute and fetch, both of which expect the database connection to still be open

Comments

I have added a few comments about pitfalls of the current implementation

Notes

The change in has_write_access will probably clash with the changes that have been accepted upstream. I can easily fix it.