Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL Error : 1213 Deadlock #3905

Open
TanqBQZ opened this issue Mar 26, 2024 · 3 comments
Open

SQL Error : 1213 Deadlock #3905

TanqBQZ opened this issue Mar 26, 2024 · 3 comments

Comments

@TanqBQZ
Copy link

TanqBQZ commented Mar 26, 2024 •
edited

Description

Describe the bug

When adding a catalog or doing an "Add", multiple copies of the same songs are added.
This is due to the following SQL error :
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

On a folder containg 1000 songs, I got like 1300 deadlocks, multiple times (I reverted back to a snapshot and did the same operation)

Environment

Ampache Docker
Same issue from 6.0 to 6.3.1
On a fresh 6.0 install, I got a few deadlocks but manage to add my music collection by adding small folders at a time,
but right now I reorganized my folders and did a Clean followed by an Add and I get multiple deadlocks even on small folders (100 songs)

Settings

catalog_ignore_pattern = xxxxxxx
memory_cache = "true"
gather_song_art = "true"
show_song_art = "true"

Logs

You can see here that the same file is getting imported multiple times
There are different SQL queries resulting in a deadlock error

2024-03-26T20:08:30+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: UPDATE `album`, (SELECT COUNT(DISTINCT `album_disk`.`disk`) AS `disk_count`, `album_id` FROM `album_disk` WHERE `album_disk`.`album_id` = ? GROUP BY `album_disk`.`album_id`) AS `album_disk` SET `album`.`disk_count` = `album_disk`.`disk_count` WHERE `album`.`disk_count` != `album_disk`.`disk_count` AND `album`.`id` = `album_disk`.`album_id`; [13066]
2024-03-26T20:08:30+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
2024-03-26T20:08:30+00:00 [Tanq] (local.catalog) -> Imported song file: /current/3. Monstercat/Albums/2013 - [MC013] - VA - Monstercat 013 - Awakening/02. Tristam - Truth (Dabin Remix).flac
2024-03-26T20:08:30+00:00 [Tanq] (local.catalog) -> Imported song file: /current/3. Monstercat/Albums/2013 - [MC013] - VA - Monstercat 013 - Awakening/03. Vicetone - Heartbeat (Rameses B Remix) (Ft. Collin McLoughlin).flac
2024-03-26T20:08:31+00:00 [Tanq] (local.catalog) -> Imported song file: /current/3. Monstercat/Albums/2013 - [MC013] - VA - Monstercat 013 - Awakening/02. Tristam - Truth (Dabin Remix).flac
2024-03-26T20:08:31+00:00 [Tanq] (local.catalog) -> Imported song file: /current/3. Monstercat/Albums/2013 - [MC013] - VA - Monstercat 013 - Awakening/03. Vicetone - Heartbeat (Rameses B Remix) (Ft. Collin McLoughlin).flac
2024-03-26T20:08:31+00:00 [Tanq] (local.catalog) -> Imported song file: /current/3. Monstercat/Albums/2013 - [MC013] - VA - Monstercat 013 - Awakening/08. Project 46 - Catalyst.flac
2024-03-26T20:08:31+00:00 [Tanq] (Ampache\Repository\Model\Catalog) -> update_map song: {53169}

2024-03-26T20:08:38+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: UPDATE `album`, (SELECT COUNT(DISTINCT(`album_map`.`object_id`)) AS `artist_count`, `album_id` FROM `album_map` LEFT JOIN `album` ON `album`.`id` = `album_map`.`album_id` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `album_map`.`object_type` = 'song' AND `catalog`.`enabled` = '1' AND `album`.`id` = ? GROUP BY `album_id`) AS `album_map` SET `album`.`song_artist_count` = `album_map`.`artist_count` WHERE `album`.`song_artist_count` != `album_map`.`artist_count` AND `album`.`id` = `album_map`.`album_id`; [13066]
2024-03-26T20:08:38+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
2024-03-26T20:08:38+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: UPDATE `album`, (SELECT COUNT(DISTINCT(`album_map`.`object_id`)) AS `artist_count`, `album_id` FROM `album_map` LEFT JOIN `album` ON `album`.`id` = `album_map`.`album_id` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `album_map`.`object_type` = 'song' AND `catalog`.`enabled` = '1' AND `album`.`id` = ? GROUP BY `album_id`) AS `album_map` SET `album`.`song_artist_count` = `album_map`.`artist_count` WHERE `album`.`song_artist_count` != `album_map`.`artist_count` AND `album`.`id` = `album_map`.`album_id`; [13066]
2024-03-26T20:08:38+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

2024-03-26T20:09:52+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: UPDATE `album`, (SELECT SUM(`song`.`time`) AS `time`, `song`.`album` FROM `song` WHERE `album` = ? GROUP BY `song`.`album`) AS `song` SET `album`.`time` = `song`.`time` WHERE `album`.`id` = `song`.`album` AND ((`album`.`time` != `song`.`time`) OR (`album`.`time` IS NULL AND `song`.`time` > 0)); [13071]
2024-03-26T20:09:52+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

2024-03-26T20:10:24+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: UPDATE `album`, (SELECT COUNT(DISTINCT(`album_map`.`object_id`)) AS `artist_count`, `album_id` FROM `album_map` LEFT JOIN `album` ON `album`.`id` = `album_map`.`album_id` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `album_map`.`object_type` = 'song' AND `catalog`.`enabled` = '1' AND `album`.`id` = ? GROUP BY `album_id`) AS `album_map` SET `album`.`song_artist_count` = `album_map`.`artist_count` WHERE `album`.`song_artist_count` != `album_map`.`artist_count` AND `album`.`id` = `album_map`.`album_id`; [13072]
2024-03-26T20:10:24+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

2024-03-26T20:12:40+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query SQL: INSERT IGNORE INTO `catalog_map` (`catalog_id`, `object_type`, `object_id`) SELECT DISTINCT `song`.`catalog` AS `catalog_id`, 'artist' AS `map_type`, `artist_map`.`artist_id` AS `object_id` FROM `song` LEFT JOIN `artist_map` ON `song`.`id` = `artist_map`.`object_id` AND `artist_map`.`object_type` = 'song' WHERE `artist_map`.`object_type` IS NOT NULL UNION SELECT DISTINCT `album`.`catalog` AS `catalog_id`, 'artist' AS `map_type`, `artist_map`.`artist_id` AS `object_id` FROM `album` LEFT JOIN `artist_map` ON `album`.`id` = `artist_map`.`object_id` AND `artist_map`.`object_type` = 'album' WHERE `artist_map`.`object_type` IS NOT NULL UNION SELECT DISTINCT `song`.`catalog` AS `catalog_id`, 'song_artist' AS `map_type`, `artist_map`.`artist_id` AS `object_id` FROM `song` LEFT JOIN `artist_map` ON `song`.`id` = `artist_map`.`object_id` AND `artist_map`.`object_type` = 'song' WHERE `artist_map`.`object_type` IS NOT NULL UNION SELECT DISTINCT `album`.`catalog` AS `catalog_id`, 'album_artist' AS `map_type`, `artist_map`.`artist_id` AS `object_id` FROM `album` LEFT JOIN `artist_map` ON `album`.`id` = `artist_map`.`object_id` AND `artist_map`.`object_type` = 'album' WHERE `artist_map`.`object_type` IS NOT NULL GROUP BY `catalog`, `artist_map`.`object_type`, `artist_map`.`artist_id`; [14572,14572,14572,14572]
2024-03-26T20:12:40+00:00 [Tanq] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Here is the result of SHOW ENGINE INNODB STATUS :

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-26 23:37:49 0x7f612c4c76c0
*** (1) TRANSACTION:
TRANSACTION 3178765, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1128, 3 row lock(s)
MariaDB thread id 56, OS thread handle 140055331763904, query id 67672 localhost ampache Sending data
UPDATE `album`, (SELECT COUNT(DISTINCT(`album_map`.`object_id`)) AS `artist_count`, `album_id` FROM `album_map` LEFT JOIN `album` ON `album`.`id` = `album_map`.`album_id` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `album_map`.`object_type` = 'song' AND `catalog`.`enabled` = '1' AND `album`.`id` = '13273' GROUP BY `album_id`) AS `album_map` SET `album`.`song_artist_count` = `album_map`.`artist_count` WHERE `album`.`song_artist_count` != `album_map`.`artist_count` AND `album`.`id` = `album_map`.`album_id`
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178765 lock_mode X locks rec but not gap waiting
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;

*** CONFLICTING WITH:
RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178765 lock mode S locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;

RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178764 lock mode S locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;


*** (2) TRANSACTION:
TRANSACTION 3178764, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1128, 3 row lock(s)
MariaDB thread id 64, OS thread handle 140055330535104, query id 67675 localhost ampache Sending data
UPDATE `album`, (SELECT COUNT(DISTINCT(`album_map`.`object_id`)) AS `artist_count`, `album_id` FROM `album_map` LEFT JOIN `album` ON `album`.`id` = `album_map`.`album_id` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `album_map`.`object_type` = 'song' AND `catalog`.`enabled` = '1' AND `album`.`id` = '13273' GROUP BY `album_id`) AS `album_map` SET `album`.`song_artist_count` = `album_map`.`artist_count` WHERE `album`.`song_artist_count` != `album_map`.`artist_count` AND `album`.`id` = `album_map`.`album_id`
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178764 lock_mode X locks rec but not gap waiting
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;

*** CONFLICTING WITH:
RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178765 lock mode S locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;

RECORD LOCKS space id 77 page no 166 n bits 32 index PRIMARY of table `ampache`.`album` trx id 3178764 lock mode S locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
 0: len 4; hex 000033d9; asc   3 ;;
 1: len 6; hex 0000003080e9; asc    0  ;;
 2: len 7; hex 15000001400110; asc     @  ;;
 3: len 16; hex 446f676d6120526573697374616e6365; asc Dogma Resistance;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 4; hex 000007e3; asc     ;;
 7: len 4; hex 00000001; asc     ;;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 8; hex 000000000000039f; asc         ;;
 16: SQL NULL;
 17: len 4; hex 66034e3b; asc f N;;;
 18: len 4; hex 00000001; asc     ;;
 19: len 4; hex 00000000; asc     ;;
 20: len 2; hex 0005; asc   ;;
 21: len 2; hex 0000; asc   ;;
 22: len 2; hex 0001; asc   ;;
 23: len 8; hex 00000000000033d9; asc       3 ;;

*** WE ROLL BACK TRANSACTION (2)
@TanqBQZ
Copy link
Author

TanqBQZ commented Mar 28, 2024

Update : Like a comment in #3319 suggests, GUI and CLI catalog actions are not behaving the same way.
I did "run:updateCatalog -a" then "run:updateCatalog -g" and got absolutely 0 deadlocks.

@TanqBQZ
Copy link
Author

TanqBQZ commented Mar 31, 2024

I noticed that you can clean up the mess left by the deadlocks by running :
/var/www/bin/cli run:updateCatalog -t

@lachlan-00
Copy link
Member

I need a way to lock the tables while it's running i think. even if it's just to ensure the ui stops repeating itself like that

@SimonHova SimonHova mentioned this issue May 13, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@lachlan-00 @TanqBQZ