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 < [email protected]> 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 > > *[email protected] <[email protected]>* | 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:* [email protected] <[email protected]> > *Sent:* Friday, May 10, 2024 11:11 AM > *To:* Scott Canaan <[email protected]>; [email protected] > *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 <[email protected]> > *Gesendet:* Freitag, 10. Mai 2024 16:34 > *An:* [email protected] > *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 > > *[email protected] <[email protected]>* | 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 -- [email protected] > To unsubscribe send an email to [email protected] >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
