Description
Joonas Kylmälä
2021-02-15 11:23:19 UTC
I did some testing and I confirm that the txn is not needed here. use Koha::Database; my $schema = Koha::Database->new->schema; $schema->txn_do(sub { $schema->txn_do( sub { my $dbh = $schema->storage->dbh; say "getting locks"; $dbh->do(q|LOCK TABLE message_queue READ|); $dbh->do(q|LOCK TABLE message_queue WRITE|); say "locked!"; sleep 10; say "unlocking"; $dbh->do(q|UNLOCK TABLES|); say "unlocked!"; }); }); Start several parallel processes. Remove the nested txn_do, try again. Created attachment 116906 [details] [review] Bug 27707: (bug 26639 follow-up) Fix renewals when RenewalSendNotice is set Since bug 26639 we have auto savepoint enabled and the LOCK TABLE query in C4::Circulation::SendCirculationAlert is not correctly handled. From the MySQL doc that is copied few lines before, "LOCK TABLE will commit any transactions", but here we don't have a savepoint and the release for a non-existent savepoint will throw a DBI exception. This patch removes the unecessary transaction and prevent the following error when a renewal is done: > DBIx::Class::Storage::DBI::mysql::_exec_svp_release(): DBI Exception: DBD::mysql::db do failed: SAVEPOINT savepoint_0 does not exist [for Statement "RELEASE SAVEPOINT savepoint_0"] at /usr/share/koha/lib/C4/Circulation.pm line 3590 Test plan: 1. Enable RenewalSendNotice 2. Add some email address to patron and select the email box from the message preference "Item checkout and renewal" in order to receive renewal emails. 3. Check 1 item out to a patron 4. Renew it Created attachment 116909 [details] [review] Bug 27707: (bug 26639 follow-up) Fix renewals when RenewalSendNotice is set Since bug 26639 we have auto savepoint enabled and the LOCK TABLE query in C4::Circulation::SendCirculationAlert is not correctly handled. From the MySQL doc that is copied few lines before, "LOCK TABLE will commit any transactions", but here we don't have a savepoint and the release for a non-existent savepoint will throw a DBI exception. This patch removes the unecessary transaction and prevent the following error when a renewal is done: > DBIx::Class::Storage::DBI::mysql::_exec_svp_release(): DBI Exception: DBD::mysql::db do failed: SAVEPOINT savepoint_0 does not exist [for Statement "RELEASE SAVEPOINT savepoint_0"] at /usr/share/koha/lib/C4/Circulation.pm line 3590 Test plan: 1. Enable RenewalSendNotice 2. Add some email address to patron and select the email box from the message preference "Item checkout and renewal" in order to receive renewal emails. 3. Check 1 item out to a patron 4. Renew it Signed-off-by: Andrew Nugged <nugged@gmail.com> I found the MySQL documentation where the LOCK Tables behaviour with transactions is explained: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html Created attachment 116910 [details] [review] Bug 27707: (bug 26639 follow-up) Fix renewals when RenewalSendNotice is set Since bug 26639 we have auto savepoint enabled and the LOCK TABLE query in C4::Circulation::SendCirculationAlert is not correctly handled. From the MySQL doc that is copied few lines before, "LOCK TABLE will commit any transactions", but here we don't have a savepoint and the release for a non-existent savepoint will throw a DBI exception. This patch removes the unecessary transaction and prevent the following error when a renewal is done: > DBIx::Class::Storage::DBI::mysql::_exec_svp_release(): DBI Exception: DBD::mysql::db do failed: SAVEPOINT savepoint_0 does not exist [for Statement "RELEASE SAVEPOINT savepoint_0"] at /usr/share/koha/lib/C4/Circulation.pm line 3590 Test plan: 1. Enable RenewalSendNotice 2. Add some email address to patron and select the email box from the message preference "Item checkout and renewal" in order to receive renewal emails. 3. Check 1 item out to a patron 4. Renew it Signed-off-by: Andrew Nugged <nugged@gmail.com> Signed-off-by: Joonas Kylmälä <joonas.kylmala@helsinki.fi> @RMaints: you all need this one. Pushed to master for 21.05, thanks to everybody involved! Pushed to 20.11.x for 20.11.03 Testing this for backport on 20.05, I am able to renew without a problem with RenewalSendNotice on and the patch not applied. I get a warning in the plack-intranet-error.log: [2021/02/22 20:41:57] [WARN] DBD::mysql::db do failed: SAVEPOINT savepoint_0 does not exist [for Statement "RELEASE SAVEPOINT savepoint_0"] at /usr/share/perl5/DBIx/Class/Storage/DBI/mysql.pm line 152. That warning persists after I've applied the patch. Again, I get a successful renewal and renewal notice regardless of whether or not this patch is present. I'm inclined to skip backport, since I can't recreate the issue. Pushed to 20.05.x for 20.05.09 As mentioned, I was unable to reproduce this error on my Koha Testing Docker running MariaDB 10.1.48. However, the patch applied fine and didn't break anything. I'm backporting on the assumption that this may impact 20.05 users with a different database version. Backported: Pushed to 19.11.x branch for 19.11.15 Still problems with renewals, now the error is just: Waiting for table metadata lock | LOCK TABLE message_queue READ (In reply to Joonas Kylmälä from comment #12) > Still problems with renewals, now the error is just: > > Waiting for table metadata lock | LOCK TABLE message_queue READ The locked table seems to have been caused by Bug 28230 so continuing discussion relating to this there. |