[MDEV-19462] Deadlock with vBulletin server and replication Created: 2019-05-13  Updated: 2023-04-17  Resolved: 2023-04-17

Status: Closed
Project: MariaDB Server
Component/s: Platform Debian, Replication, Server
Affects Version/s: 10.3.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Thibaut Perrin Assignee: Andrei Elkin
Resolution: Cannot Reproduce Votes: 0
Labels: regression
Environment:

Debian 9 Stretch.
Server is a Supermicro at Scaleway (online.net)

Issue Links:
Relates
relates to MDEV-17603 Allow statement-based replication for... Closed

 Description   

I have a vBulletin server running (v4.2.5), with PHP 7.1. They were running on the 10.1 version from the apt repositories from Debian (mariadb-server-core-10.1/stable 10.1.38-0+deb9u1).

I decided to upgrade the MariaDB using the MariaDB apt repo, which gave me an upgrade to 10.3.14.

The database is replicated onto another server with the same OS / MariaDB version from stratch (Debian 9 / MariaDB 10.3).

Replication works fine : 

MariaDB [(none)]> show slave status \G
 
*************************** 1. row ***************************
 
                Slave_IO_State: Waiting for master to send event
 
                   Master_User: replication_user
 
                 Connect_Retry: 10
 
               Master_Log_File: master1-bin.000319
 
               Read_Master_Log_Pos: 56225430
 
                 Relay_Log_Pos: 56223609
 
            Relay_Master_Log_File: master1-bin.000319
 
              Slave_IO_Running: Yes
 
             Slave_SQL_Running: Yes

Now, whenever someone opens multiple threads in a very short time range, they will likely be facing a "Database error". As an admin, I can see what the error is, and I can guarantee that this wasn't present when the database used was MariaDB 10.1.

I opened a thread on the vBulletin support, but they told me there shouldn't be any issues on this type from the version I'm using, and they don't test every MariaDB version so they can't confirm the problem.

Is any help possible here ?

Here is the error I'm getting when it occurs : 

Invalid SQL:
 
REPLACE INTO forum_ipdata
 
    (rectype, userid, contentid, contenttypeid, dateline, ip, altip)
 
VALUES
 
    ('view', 13574, 121872, 2, 1556868134, '82.64.xxx.xxx', '82.64.xxx.xxx');
 
 
 
MySQL Error   : Deadlock found when trying to get lock; try restarting transaction
 
Error Number  : 1213
 
Request Date  : Friday, May 3rd 2019 @ 09:22:14 AM
 
Error Date    : Friday, May 3rd 2019 @ 09:22:15 AM
 
Script        : http://mysuperforum/threads/123456-any-thread
 
Referrer      : 
IP Address    : xx.xx.xx.xx
 
Username      : Wobak
 
Classname     : vB_Database_MySQLi
 
MySQL Version : 10.3.14-MariaDB-1:10.3.14+maria~stretch-log
 
Server        :  xx.xx.xxx.xx
 
User Agent    :

(I masked the IPs)

Tables are running in InnoDB for almost all the tables (the issue also occured when they were in MyISAM).

Could this be due to replication ?
Is there anything I can provide to help fix this ?

Thanks,



 Comments   
Comment by Thibaut Perrin [ 2019-05-14 ]

Thank you for that link. I updated the database to 10.3.15, it seems that the issue is gone indeed.

I'll monitor for 3-4 days, and if it doesn't reappear, I'll close this.

Thanks,

Comment by Andrei Elkin [ 2019-05-15 ]

Wobak, thanks for reporting! One suggestion for any new replication report though please try making it
clear where an error occurs (I can only guess that the slave is meant in this case). And since it's a deadlock show processlist is necessary. Cheers.

Comment by Thibaut Perrin [ 2019-05-15 ]

Hi,

As the error is instantaneous and disappears with a page reload, the show processlist doesn't show anything useful : 

MariaDB [(none)]> show processlist
 
    -> ;
 
+------+-------------+-----------+------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
 
| Id   | User        | Host      | db   | Command   | Time  | State                                                                       | Info             | Progress |
 
+------+-------------+-----------+------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
 
|    2 | system user |           | NULL | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
 
|    1 | system user |           | NULL | Daemon    |  NULL | InnoDB purge coordinator                                                    | NULL             |    0.000 |
 
|    3 | system user |           | NULL | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
 
|    4 | system user |           | NULL | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
 
|    5 | system user |           | NULL | Daemon    |  NULL | InnoDB shutdown handler                                                     | NULL             |    0.000 |
 
|    9 | system user |           | NULL | Slave_IO  | 62152 | Waiting for master to send event                                            | NULL             |    0.000 |
 
|   10 | system user |           | NULL | Slave_SQL |     0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
 
| 1051 | root        | localhost | NULL | Query     |     0 | Init                                                                        | show processlist |    0.000 |
 
+------+-------------+-----------+------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
 
8 rows in set (0.000 sec)

Also, unfortunately, the error still exists even when master and slave are running 10.3.15

Comment by Andrei Elkin [ 2019-10-15 ]

Wobak Thank you for the report! What was the slave configuration - do you still have its cnf file and a show-global-variables log?

Comment by Thibaut Perrin [ 2019-10-18 ]

Hi Andrei,

The issue seems to be gone since I updated both Master and Slave to 10.3.18-MariaDB

  1. mysql --version
    mysql Ver 15.1 Distrib 10.3.18-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Do you still need that info ?

Comment by Andrei Elkin [ 2019-10-19 ]

Wobak, salute. Not this does not look urgent as apparently you moved away from reproducing context. Hopefully it was something that got fixed already.Nevertheless, could you tell us whether the slave was in parallel mode (slave_parallel_threads > 0)?

Cheers.

Comment by Thibaut Perrin [ 2019-10-19 ]

MariaDB [(none)]> show global variables where variable_name like 'slave_%';
----------------------------------------------+

Variable_name Value

----------------------------------------------+

slave_compressed_protocol OFF
slave_ddl_exec_mode IDEMPOTENT
slave_domain_parallel_threads 0
slave_exec_mode STRICT
slave_load_tmpdir /tmp
slave_max_allowed_packet 1073741824
slave_net_timeout 60
slave_parallel_max_queued 131072
slave_parallel_mode conservative
slave_parallel_threads 0
slave_parallel_workers 0
Comment by Andrei Elkin [ 2019-10-20 ]

Thanks, Wobak! It was not parallel. I suggest we close it. Feel free to express any other opinion or act to close.

Generated at Thu Feb 08 08:51:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.