Gotham Alpha 12, Nightly -- Windows 8.1 64-bit -- MySql 5.5
Clean Library, on first run produced the above error at "Cleaning Paths" stage. After checking basic logs, I checked the collation settings in MyMusic44 db and discovered that it was set to latin1_swedish_cl, which surprised me because the previous db (32) I ensured was set to Utf8_general_cl. (I had a similar issue a while back on Frodo 12.2 and picked up on a thread I found where Jonathon Marshall had commented on in that regard, which had resolved it at that time for me as well). I don't understand why this didn't migrate when the Gotham (77) db upgrade occurred.
However, I ran an ALTER Database statement to update the db to Utf8, refreshed and closed out the session. Reran the Clean Library process again and this time passed the "Cleaning Path's" stage but stopped at "Cleaning Songs". At this point I went back and converted each table to Utf8 and tried again, same error at the same stage. Seeking more clarity, I ran the delete artist statement (same as in logs) from within HeidiSQL (Superb "Free" Interface BTW) which produced the same error as was in the logs, but a little more verbose for my benefit:
"SQL Error (1442): Can't update table 'album_artist' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
Here's an excerpt from the log, with full log link following:
00:46:59 T:9600 DEBUG: Mysql execute: CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512))
00:46:59 T:9600 DEBUG: Mysql execute: INSERT INTO songpaths select idPath,strPath from path where idPath in (select idPath from song)
00:47:02 T:9600 DEBUG: Mysql execute: drop table songpaths
00:47:02 T:9600 DEBUG: Mysql execute: delete from artist where idArtist not in (select idArtist from song_artist) and idArtist not in (select idArtist from album_artist)
00:47:02 T:9600 ERROR: SQL: Undefined MySQL error: Code (1442)
Query: delete from artist where idArtist not in (select idArtist from song_artist) and idArtist not in (select idArtist from album_artist)
00:47:02 T:9600 ERROR: Exception in CMusicDatabase::CleanupArtists() or was aborted
00:47:02 T:9600 DEBUG: Mysql rollback transaction
00:47:02 T:9600 DEBUG: CAnnouncementManager - Announcement: OnCleanFinished from xbmc
00:47:02 T:9600 DEBUG: GOT ANNOUNCEMENT, type: 32, from xbmc, message OnCleanFinished
00:47:02 T:9600 DEBUG: ------ Window Init (DialogOK.xml) ------
:--> Full Debug log (Verbose = SMB Library)
Note that the Log Uploader doesn't work for me either, as you'll see in the log - tried twice.
I hope this helps, I'm at a loss other than to say, the error suggests a potential logic conflict when calling the sql statement - but that's for better minds than me!
On a final note, I couldn't find a bug report for this in Trac, so at this stage of the game I'm assuming it's environmental to me??
Clean Library, on first run produced the above error at "Cleaning Paths" stage. After checking basic logs, I checked the collation settings in MyMusic44 db and discovered that it was set to latin1_swedish_cl, which surprised me because the previous db (32) I ensured was set to Utf8_general_cl. (I had a similar issue a while back on Frodo 12.2 and picked up on a thread I found where Jonathon Marshall had commented on in that regard, which had resolved it at that time for me as well). I don't understand why this didn't migrate when the Gotham (77) db upgrade occurred.
However, I ran an ALTER Database statement to update the db to Utf8, refreshed and closed out the session. Reran the Clean Library process again and this time passed the "Cleaning Path's" stage but stopped at "Cleaning Songs". At this point I went back and converted each table to Utf8 and tried again, same error at the same stage. Seeking more clarity, I ran the delete artist statement (same as in logs) from within HeidiSQL (Superb "Free" Interface BTW) which produced the same error as was in the logs, but a little more verbose for my benefit:
"SQL Error (1442): Can't update table 'album_artist' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
Here's an excerpt from the log, with full log link following:
00:46:59 T:9600 DEBUG: Mysql execute: CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512))
00:46:59 T:9600 DEBUG: Mysql execute: INSERT INTO songpaths select idPath,strPath from path where idPath in (select idPath from song)
00:47:02 T:9600 DEBUG: Mysql execute: drop table songpaths
00:47:02 T:9600 DEBUG: Mysql execute: delete from artist where idArtist not in (select idArtist from song_artist) and idArtist not in (select idArtist from album_artist)
00:47:02 T:9600 ERROR: SQL: Undefined MySQL error: Code (1442)
Query: delete from artist where idArtist not in (select idArtist from song_artist) and idArtist not in (select idArtist from album_artist)
00:47:02 T:9600 ERROR: Exception in CMusicDatabase::CleanupArtists() or was aborted
00:47:02 T:9600 DEBUG: Mysql rollback transaction
00:47:02 T:9600 DEBUG: CAnnouncementManager - Announcement: OnCleanFinished from xbmc
00:47:02 T:9600 DEBUG: GOT ANNOUNCEMENT, type: 32, from xbmc, message OnCleanFinished
00:47:02 T:9600 DEBUG: ------ Window Init (DialogOK.xml) ------
:--> Full Debug log (Verbose = SMB Library)
Note that the Log Uploader doesn't work for me either, as you'll see in the log - tried twice.
I hope this helps, I'm at a loss other than to say, the error suggests a potential logic conflict when calling the sql statement - but that's for better minds than me!
On a final note, I couldn't find a bug report for this in Trac, so at this stage of the game I'm assuming it's environmental to me??
