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<mailto: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.
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<mailto: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.
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<mailto: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<mailto:discuss@lists.mariadb.org>> Gesendet: Freitag, 10. Mai 2024 16:34 An: discuss@lists.mariadb.org<mailto: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<mailto: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.
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
No, and that’s what I’ve been thinking all along is that the job runs on all 3 nodes. The only one that doesn’t complain about a deadlock is the primary node. What I don’t know is how it got changed the last time the cluster had an issue. I am looking at commenting out the job on the other 2 nodes. 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<mailto: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: Jeff Dyke <jeff.dyke@gmail.com> Sent: Friday, May 10, 2024 11:50 AM To: Scott Canaan <srcdco@rit.edu> Cc: Rhys.Campbell@swisscom.com; discuss@lists.mariadb.org Subject: Re: [MariaDB discuss] Re: Deadlock Errors 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<mailto: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<mailto: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<mailto:Rhys.Campbell@swisscom.com> <Rhys.Campbell@swisscom.com<mailto:Rhys.Campbell@swisscom.com>> Sent: Friday, May 10, 2024 11:11 AM To: Scott Canaan <srcdco@rit.edu<mailto:srcdco@rit.edu>>; discuss@lists.mariadb.org<mailto: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<mailto:discuss@lists.mariadb.org>> Gesendet: Freitag, 10. Mai 2024 16:34 An: discuss@lists.mariadb.org<mailto: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<mailto: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<mailto:discuss@lists.mariadb.org> To unsubscribe send an email to discuss-leave@lists.mariadb.org<mailto:discuss-leave@lists.mariadb.org>
I would 100% back that assumption. That single row is going to make it to all three nodes, and if required, perhaps stagger the jobs in time. if they run in concert, definitely going to happen. On Fri, May 10, 2024 at 1:54 PM Scott Canaan <srcdco@rit.edu> wrote:
No, and that’s what I’ve been thinking all along is that the job runs on all 3 nodes. The only one that doesn’t complain about a deadlock is the primary node.
What I don’t know is how it got changed the last time the cluster had an issue. I am looking at commenting out the job on the other 2 nodes.
*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:* Jeff Dyke <jeff.dyke@gmail.com> *Sent:* Friday, May 10, 2024 11:50 AM *To:* Scott Canaan <srcdco@rit.edu> *Cc:* Rhys.Campbell@swisscom.com; discuss@lists.mariadb.org *Subject:* Re: [MariaDB discuss] Re: Deadlock Errors
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
participants (3)
-
Jeff Dyke -
Rhys.Campbell＠swisscom.com -
Scott Canaan