Database Tools

[nynaevelan]

After you ran the Audit script did you use the Clean DB Errors script?? That one should fix all the errors.

[Onweerwolf]

Yes, of course.

[nynaevelan]

Sorry I don't have any other suggestions other than sending Bex a PM, he's a sql genius maybe he will have some thoughts on how to fix it. I would suggest Owyn also but I have not seen any posts from him on the board lately.

[Bex]

What do you get if you run this query in SQL-Viewer?

Code: Select all

SELECT *
    FROM PlaylistSongs
    WHERE IDPlayList<>-1
    AND IDPlayList NOT IN (SELECT IDPlaylist
    FROM Playlists)

[Onweerwolf]

# IDPLAYLISTSONG IDPLAYLIST IDSONG SONGORDER
01 25421 Genesis 6873 Trespass

[Bex]

It should go away id you run this SQL in SQL-Viewer:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPlaylist NOT IN (SELECT IDPlaylist FROM Playlists)

[Onweerwolf]

It should go away id you run this SQL in SQL-Viewer:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPlaylist NOT IN (SELECT IDPlaylist FROM Playlists)

I've tried this but it still gives the same result when I run this one:

What do you get if you run this query in SQL-Viewer?

Code: Select all

SELECT *
    FROM PlaylistSongs
    WHERE IDPlayList<>-1
    AND IDPlayList NOT IN (SELECT IDPlaylist
    FROM Playlists)

[Bex]

# IDPLAYLISTSONG IDPLAYLIST IDSONG SONGORDER
01 25421 Genesis 6873 Trespass

The offending playlist seems to have an id=Genesis, which shouldn't be possible. But SQLite actually accepts strings in numeric fields and vice versa.
Try this one:
DELETE FROM PlaylistSongs WHERE IDPlaylist='Genesis'

[Onweerwolf]

# IDPLAYLISTSONG IDPLAYLIST IDSONG SONGORDER
01 25421 Genesis 6873 Trespass

The offending playlist seems to have an id=Genesis, which shouldn't be possible. But SQLite actually accepts strings in numeric fields and vice versa.
Try this one:
DELETE FROM PlaylistSongs WHERE IDPlaylist='Genesis'

That did not change anything as far as I can see. It still gives

# IDPLAYLISTSONG IDPLAYLIST IDSONG SONGORDER
01 25421 Genesis 6873 Trespass

after that.

[Bex]

Then try this:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPLAYLISTSONG=25421

[Onweerwolf]

Same result still, unfortunately.

(I do appreciate the help though, thank you! )

[Bex]

Can you zip and uplaod your db to e.g. mediafire and then PM me the link so I can see what's going on with your DB?

[Onweerwolf]

Can you zip and uplaod your db to e.g. mediafire and then PM me the link so I can see what's going on with your DB?

Yep, I'm rarring a recent DB back-up as we speak and will send you a pm after i've uploaded it.

[Bex]

The bad thing is that your db seems to be corrupted, but the good thing is that I managed to fix it. The key seems to first delete the offending record and then do a "Optimize Database (Complete)". At first it didn't work but after three times or so it actually did work.

So run this statement:

Code: Select all

DELETE FROM PlaylistSongs WHERE IDPLAYLISTSONG=25421

and do a "Optimize Database (Complete)". Repeat until it works.

If nothing works I can send you your db!

[Onweerwolf]

Hi Bex!

Sorry it took me such a long time to get back to you. I've done what you said in your last post and indeed the error is now fixed and the optimized database function finally makes it to the end as well. Wohoo! Thanks for helping me out! I appreciate it.

Now in the Audit Report there is 1 error left, which is this one:

Code: Select all

ArtistsAlbums(Album) Check LIKE Values        **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM (SELECT Artists.ID AS IDArtist, Artists.Artist AS Name, Albums.ID As IDAlbum, Albums.Artist AS AlbumArtist
    FROM Artists, ArtistsAlbums, Albums
    WHERE ArtistsAlbums.IDArtist=Artists.ID
    AND ArtistsAlbums.IDAlbum=Albums.ID)
    WHERE AlbumArtist NOT LIKE '%'||REPLACE(REPLACE(Name,'@','@@'),';',';;')||'%'
[IDARTIST] [NAME] [IDALBUM] [ALBUMARTIST]
[6253] [Æthenor] [8222] [Aethenor]
---------------- 1 OF 1 Errors Listed------------

Now i'm assuming the Æ character is responsible here, correct?

Is there a way to fix this or is this just a a MM bug/limitation/etc.?

I'm pretty sure this is not causing any problems in how MM functions but i'd still be nice to have my DB fully error free of course.