Database Tools

Post a reply

Visual Confirmation

To prevent automated access and spam, you are required to confirm that you are human. Please place a check mark next to all images of monkeys or apes. If you cannot see any images, please contact the Board Administrator.

Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:
BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON
Topic review
   

Expand view Topic review: Database Tools

ArtistsSongs(Artist) Check Values **ERROR**

Post by zephyr » Sat Feb 04, 2012 7:45 pm

Hi, I'm getting a few errors that aren't being fixed by running the clean script. Any ideas what the causes (and hopefully solutions) are?

Many thanks!

Code: Select all
ArtistsSongs(Artist) Check Values             **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM (SELECT Artists.ID AS IDArtist, Artists.Artist AS Artist, Songs.ID AS IDSong, Songs.Artist AS SongsArtist,  Songs.SongTitle, Songs.Album
    FROM Artists, ArtistsSongs, Songs
    WHERE ArtistsSongs.PersonType=1
    AND Artists.ID=ArtistsSongs.IDArtist
    AND ArtistsSongs.IDSong=Songs.ID)
    WHERE SongsArtist NOT LIKE '%'||REPLACE(REPLACE(Artist,'@','@@'),';',';;')||'%'
[IDARTIST] [ARTIST] [IDSONG] [SONGSARTIST] [SONGTITLE] [ALBUM]
[145178] [The Beach Boys] [1469850] [Audio Bullys] [Snake] [Ego War]
[145178] [The Beach Boys] [1469851] [Audio Bullys] [100 Million] [Ego War]
[145178] [The Beach Boys] [1469852] [Audio Bullys] [Way Too Long] [Ego War]
[145178] [The Beach Boys] [1469853] [Audio Bullys] [Turned Away] [Ego War]
[145178] [The Beach Boys] [1469854] [Audio Bullys] [Real Life] [Ego War]
[145178] [The Beach Boys] [1469855] [Audio Bullys] [We Don't Care] [Ego War]
[145178] [The Beach Boys] [1469856] [Audio Bullys] [Face In A Cloud] [Ego War]
[145178] [The Beach Boys] [1469857] [Audio Bullys] [The Tyson Shuffle] [Ego War]
[145178] [The Beach Boys] [1469858] [Audio Bullys] [The Things] [Ego War]
[145178] [The Beach Boys] [1469859] [Audio Bullys] [Veteran] [Ego War]
[145178] [The Beach Boys] [1469860] [Audio Bullys] [The Snow] [Ego War]
[145178] [The Beach Boys] [1469861] [Audio Bullys] [I Go To Your House] [Ego War]
[145178] [The Beach Boys] [1469862] [Audio Bullys] [Hit The Ceiling] [Ego War]
[145178] [The Beach Boys] [1469863] [Audio Bullys] [Ego War] [Ego War]
[152378] [Isolée] [1542179] [Isole´e] [Pictureloved] [We Are Monster]
[152378] [Isolée] [1542180] [Isole´e] [Schrapnell] [We Are Monster]
[152378] [Isolée] [1542181] [Isole´e] [Enrico] [We Are Monster]
[152378] [Isolée] [1542182] [Isole´e] [Madchen Mit Hase] [We Are Monster]
[152378] [Isolée] [1542183] [Isole´e] [My Hi-Matic] [We Are Monster]
[152378] [Isolée] [1542184] [Isole´e] [Do Re Mi] [We Are Monster]
---------------- 20 OF 24 Errors Listed------------

ArtistsSongs(Composer) Check Counts           OK
ArtistsSongs(Composer) Check Values           **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM (SELECT Artists.ID AS IDArtist, Artists.Artist AS Artist, Songs.ID AS IDSong, Songs.Author AS Composer,  Songs.AlbumArtist, Songs.SongTitle, Songs.Album
    FROM Artists, ArtistsSongs, Songs
    WHERE ArtistsSongs.PersonType=3
    AND Artists.ID=ArtistsSongs.IDArtist
    AND ArtistsSongs.IDSong=Songs.ID)
    WHERE Composer NOT LIKE '%'||REPLACE(REPLACE(Artist,'@','@@'),';',';;')||'%'
[IDARTIST] [ARTIST] [IDSONG] [COMPOSER] [ALBUMARTIST] [SONGTITLE] [ALBUM]
[184768] [Asa de A´guia] [1889120] [Asa de Águia] [Axe] [Abalou] [Axé Bahia 2004]
---------------- 1 OF 1 Errors Listed------------

ArtistsAlbums(Album) Check Count              OK
ArtistsAlbums(Album) Check EQUAL 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 <> Name
    AND AlbumArtist NOT LIKE '%;%'
[IDARTIST] [NAME] [IDALBUM] [ALBUMARTIST]
[145178] [The Beach Boys] [149353] [Audio Bullys]
[145178] [The Beach Boys] [149445] [Audio Bullys]
---------------- 2 OF 2 Errors Listed------------
 
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]
[145178] [The Beach Boys] [149353] [Audio Bullys]
[145178] [The Beach Boys] [149445] [Audio Bullys]
[152378] [Isolée] [158372] [Isole´e]
---------------- 3 OF 3 Errors Listed------------
 
Songs->Albums: Check Values                   **ERROR**
---------------------Details-----------------------------
SELECT Songs.ID AS IDSong, Songs.Album AS SongAlbum, Songs.IDAlbum AS IDAlbum, Albums.Album AS Album
    FROM Songs, Albums
    WHERE Songs.IDAlbum = Albums.ID
    AND TRIM(Songs.Album)<>Albums.Album
[IDSONG] [SONGALBUM] [IDALBUM] [ALBUM]
[1871678] [Beyond the Wall] [186947] [Beyond the Wall]
[1871683] [Beyond the Wall] [186947] [Beyond the Wall]
[1871684] [Beyond the Wall] [186947] [Beyond the Wall]
[1871685] [Beyond the Wall] [186947] [Beyond the Wall]
[1871686] [Beyond the Wall] [186947] [Beyond the Wall]
---------------- 5 OF 5 Errors Listed------------

Re: Database Tools

Post by martialartsguy » Thu Nov 17, 2011 12:09 am

Any chance of getting this updated for MM4? I've got the audit db tool to work with changes suggested by nyn, but no idea where the DB_Clean goes after installing the mmip.

Re: Database Tools

Post by MusicBringer » Sun Jul 24, 2011 4:21 pm

Nyn, yeah I dun it :D
Then ran the script and it worked. Hooray.
Thanks so much for helping me.

[Ooh err missus, it's me Five Hundredth post] 8)

Re: Database Tools

Post by nynaevelan » Sun Jul 24, 2011 12:49 pm

Change the DeviceFilters to DeviceTracks, you can do it with a find and replace in notepad to change it, just make sure when you save it it still has it's vbs extension and not txt.

Re: Database Tools

Post by MusicBringer » Sun Jul 24, 2011 10:41 am

Nyn, sorry I do not have the answer for you.
In fact I too am getting an error when using the clean up script with MM4.
Is there any chance you could tell me how to fix it so it works with MM4 - I'm not a coder so it's gotta be simple - alternatively might you sent me an updated version please.
Image
Image

Thanks,

Re: Database Tools

Post by nynaevelan » Thu Jun 30, 2011 12:06 pm

Owyn and company:

I need some help with an error which I am getting in MM4. First, in order to get the scripts to work in mm4 I had to change the name of the filters table in the scripts but after that everything is working as it should. Now this is my error:

Code: Select all
Database Statistics
Table                              Seq   Count   MaxID
------------------------------ ------- ------- -------
Albums                           11587    5764   11587
Artists                           5995    2974    5995
ArtistsAlbums                    11552    5758   11552
ArtistsSongs                    242081   20199  242081
Covers                          189657   19999  189657
DEISyncLog                      686748  686748  686748
DeviceCollectionConfig            5169     793    5169
DeviceTracks                    163247    3642  163247
Devices                             62      13      62
Folders                            111      46      95 ***ERROR***
FoldersHier                        434     203     373
Genres                              65      20      58
GenresSongs                     226339   19491  226339
ListsSongs                     1851642  211781 1851642
MediaConnections                   682     682     682
MediaServerFilters                  30      27      30
MediaServerPlaylists               223     193     223
PathProcessing                   19305       0         
Played                          378302  185257  378302
PlaylistSongs                    62116   15857   62116
Playlists                         3213     430    3213
Songs                            37973   19491   37973
SynchCollection                      4       0         
SynchPlaylist                      935     134     935
Views                               73      61      73


Anyone have any ideas how to fix something like this??

Nyn

Re: Database Tools

Post by Onweerwolf » Mon Apr 11, 2011 1:22 pm

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.

Re: Database Tools

Post by Bex » Sat Jan 22, 2011 5:17 pm

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!

Re: Database Tools

Post by Onweerwolf » Wed Jan 19, 2011 9:18 am

Bex wrote: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.

Re: Database Tools

Post by Bex » Wed Jan 19, 2011 9:13 am

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?

Re: Database Tools

Post by Onweerwolf » Wed Jan 19, 2011 9:09 am

Same result still, unfortunately.

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

Re: Database Tools

Post by Bex » Wed Jan 19, 2011 9:03 am

Then try this:

Code: Select all
DELETE FROM PlaylistSongs WHERE IDPLAYLISTSONG=25421

Re: Database Tools

Post by Onweerwolf » Tue Jan 18, 2011 7:39 am

Bex wrote:
Onweerwolf wrote:# 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.

Re: Database Tools

Post by Bex » Tue Jan 18, 2011 7:13 am

Onweerwolf wrote:# 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'

Re: Database Tools

Post by Onweerwolf » Sun Jan 16, 2011 6:28 pm

Bex wrote: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:

Bex wrote: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)


:(

Top