Are these cron jobs isolated to a single cluster node? For example all of my database connections to Galera 3-node go through HAProxy that provides the failover. Without this I can reproduce a deadlock on a galera cluster very simply. Very similar with RDS clusters. On Fri, May 10, 2024 at 11:19 AM Scott Canaan via discuss < discuss@lists.mariadb.org> wrote:
It’s a table.
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*srcdco@rit.edu <srcdco@rit.edu>* | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
*From:* Rhys.Campbell@swisscom.com <Rhys.Campbell@swisscom.com> *Sent:* Friday, May 10, 2024 11:11 AM *To:* Scott Canaan <srcdco@rit.edu>; discuss@lists.mariadb.org *Subject:* AW: Deadlock Errors
An EXPLAIN on the SELECT might be a good place to start. Is rit_mdb_track_login_detail a table or a view?
Rhys
*Von:* Scott Canaan via discuss <discuss@lists.mariadb.org> *Gesendet:* Freitag, 10. Mai 2024 16:34 *An:* discuss@lists.mariadb.org *Betreff:* [MariaDB discuss] Deadlock Errors
*Be aware:* This is an external email.
We have a 3-node Galera cluster running MariaDB 10.5.23. A couple months ago the cluster had an issue and one of my coworkers handled it. We have several cron jobs that run against the databases to gather statistics and other information. One of those jobs runs every 15 minutes. Since the last issue, one SQL in that script has been throwing a deadlock error on two of the three nodes in the cluster:
ERROR 1213 (40001) at line 14: Deadlock found when trying to get lock; try restarting transaction
I’ve been monitoring and I don’t see the job running more than once at the same time on the same server. The SQL that it is failing on is:
insert into rit_mdb_track_logins
select report_date, user, count(*) logins
from rit_mdb_track_login_detail
group by report_date, user;
I’ve run this SQL manually and it works fine.
I’m not sure where to go from here.
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*srcdco@rit.edu <srcdco@rit.edu>* | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org