Ran into several DB errors when updating via the GUI:

[NatureMuncher]

          Ran into several DB errors when updating via the GUI:

Update failed. Please check the logs for further information.

    Version: 6.0.0 Build: 052
        Add unique constraint `playlist_track_UN` on `playlist_data` table
    Version: 6.0.0 Build: 053
        Remove Flattr plugin (Service has been discontinued)

logs:

2024-01-25T13:17:28+00:00 [ampache] (Ampache\Module\System\Dba) -> Database connection...
2024-01-25T13:17:28+00:00 [ampache] (Ampache\Module\Application\ApplicationRunner) -> Found handler "Ampache\Module\Application\Update\ShowAction" for action "show"
2024-01-25T13:17:30+00:00 [ampache] (Ampache\Module\System\Dba) -> Database connection...
2024-01-25T13:17:30+00:00 [ampache] (Ampache\Module\Application\ApplicationRunner) -> Found handler "Ampache\Module\Application\Update\UpdateAction" for action "update"
2024-01-25T13:17:30+00:00 [ampache] (Ampache\Module\System\Update\UpdateRunner) -> Migration starting
2024-01-25T13:17:30+00:00 [ampache] (log.lib) -> [Runtime Error] Attempt to read property "id" on string in file /var/www/html/ampache/src/Repository/Model/Playlist.php(594)
2024-01-25T13:17:31+00:00 [ampache] (log.lib) -> [Runtime Error] Attempt to read property "id" on string in file /var/www/html/ampache/src/Repository/Model/Playlist.php(594)
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` DROP KEY `playlist_track_UN`; []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP INDEX `playlist_track_UN`; check that it exists
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` DROP KEY `playlist_track_UN`; []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP INDEX `playlist_track_UN`; check that it exists
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` DROP KEY `playlist_track_UN`; []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP INDEX `playlist_track_UN`; check that it exists
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` DROP KEY `playlist_track_UN`; []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP INDEX `playlist_track_UN`; check that it exists
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` ADD CONSTRAINT `playlist_track_UN` UNIQUE KEY (`playlist`,`track`); []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-24' for key 'playlist_track_UN'
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` ADD CONSTRAINT `playlist_track_UN` UNIQUE KEY (`playlist`,`track`); []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-24' for key 'playlist_track_UN'
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` ADD CONSTRAINT `playlist_track_UN` UNIQUE KEY (`playlist`,`track`); []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-24' for key 'playlist_track_UN'
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `playlist_data` ADD CONSTRAINT `playlist_track_UN` UNIQUE KEY (`playlist`,`track`); []
2024-01-25T13:17:31+00:00 [ampache] (Ampache\Module\System\Dba) -> Error_query MSG: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-24' for key 'playlist_track_UN'

Originally posted by @NatureMuncher in #3778 (comment)

[lachlan-00]

Id on string might mean you have data for a playlist that doesn't exist. I'll add more checks on this one the constraint is important to have

[lachlan-00]

i've put another check in to delete bad data. i'll do some more checks when i'm back at my desk tomorrow
7f38e23

[NatureMuncher]

Hi @lachlan-00 , I did perform a manual update but still get similar errors. I ran this to see if I had items outside of a known playlist:

MariaDB [ampache]> select id, playlist from playlist_data where playlist not in (10,11,12,13,15,16,17);
Empty set (0.008 sec)

My playlists seem complete:

MariaDB [ampache]> select * from playlist;
+----+--------------+------+--------+------------+-------------+---------------+----------+
| id | name         | user | type   | date       | last_update | last_duration | username |
+----+--------------+------+--------+------------+-------------+---------------+----------+
| 10 | user1        |   -1 | public | 1669781329 |  1705785636 |         12868 | System   |
| 11 | user         |    1 | public | 1673279916 |  1705630062 |         22326 | ampache  |
| 12 | me           |    1 | public | 1675697595 |  1705630819 |         22196 | ampache  |
| 13 | user2        |    1 | public | 1678551538 |  1704498370 |         30380 | ampache  |
| 15 | user3        |   -1 | public | 1684529200 |  1705629793 |         26857 | System   |
| 16 | user4        |    2 | public | 1693495352 |  1695393648 |          6144 | user     |
| 17 | user5        |    1 | public | 1699024392 |  1699024392 |          4862 | ampache  |
+----+--------------+------+--------+------------+-------------+---------------+----------+
7 rows in set (0.001 sec)

Not sure what I need to delete to get it working again.
Thank you!

[lachlan-00]

if that's clean then it's your track orders which need work

Duplicate entry '10-24'

means playlist 10 already has mutliple track 24's. this is supposed to already be fixed when i first did the update so there might be something not correct in that part. i'll have a quick look again to see if the sorting needs work.

I call the regenerate function to update the id's but it might not work all the time with duplicates maybe

[lachlan-00]

This query will tell us what's non-unique

SELECT * FROM `playlist_data` GROUP BY `playlist`, `track` HAVING count(`track`) > 1;

This should be empty to get the update

[NatureMuncher]

That looks like it worked!

MariaDB [ampache]> SELECT * FROM `playlist_data` GROUP BY `playlist`, `track` HAVING count(`track`) > 1;
+-----+----------+-----------+-------------+-------+
| id  | playlist | object_id | object_type | track |
+-----+----------+-----------+-------------+-------+
| 778 |       10 |     22836 | song        |    24 |
+-----+----------+-----------+-------------+-------+
1 row in set (0.003 sec)

MariaDB [ampache]> delete from playlist_data where id=778;
Query OK, 1 row affected (0.016 sec)

MariaDB [ampache]> SELECT * FROM `playlist_data` GROUP BY `playlist`, `track` HAVING count(`track`) > 1;
Empty set (0.001 sec)

Thank you!

[lachlan-00]

i'll see if i can do a bit more tweaking on that as well. ab2161f does a different sort now which might be better