SQLite WAL in 7.6.1 - please test

[andyg]

I've made a rather large change in 7.6.1 today: SQLite now runs in WAL mode (see here for the gory technical details):

* Server and standalone scanner now share the same set of 3 database files (squeezebox.db, squeezebox-persistent.db, and ArtworkCache.db). No more copying back and forth to "-scanner" files, etc.
* "Database is locked" errors while scanning should go away.
* locking_mode=EXCLUSIVE has been removed.
* You can see and use changes in the database while the scanner is running, the way it used to be.

Also, I found a bug in the persistent table during this work, the 'added' column was storing the file's modification timestamp instead of the time when the file was originally added. I had to fix this in order to get the scanner to work right for new files. Hopefully this will not cause any issues with plugins.

[erland]

Also, I found a bug in the persistent table during this work, the 'added' column was storing the file's modification timestamp instead of the time when the file was originally added. I had to fix this in order to get the scanner to work right for new files. Hopefully this will not cause any issues with plugins.

I suspect I'm the only one using it (in TrackStat) and I copy it to the separate TrackStat tables initially and I think I really want to be the time when the file was added, so I think your change should be fine.

However, earlier when I looked at the LMS schema it looked like it also had a "added_time" column in the "tracks" table besides the "tracks_persistent.added" column, is that intentional or just a mistake ?
I posted a thread about it earlier but never got any response:
http://forums.slimdevices.com/showthread.php?t=87579

[frank1969]

I've made a rather large change in 7.6.1 today

When You're talking about 7.6.1 these days it's "automatically"in 7.7.0 (LMS), too?

[slate]

When You're talking about 7.6.1 these days it's "automatically"in 7.7.0 (LMS), too?

It is merged to 7.7 http://svn.slimdevices.com/slim/7.7/?view=log

[JJZolx]

Mmmm... Not good. I just ran a full rescan. Note the numbers shown below. It apparently attempted to scan 32,712 files, which is the correct number of Flac files in my library, but only ended up with 16,372.

Total Tracks: 16,372
Total Albums: 1,659
Total Artists: 703
Total Genres: 32
Total Playing Time: 1228:19:22

Music Scan Details
Discovering files: D:\slim\65\music   (1 of 1)   Complete  00:00:00
Discovering files: E:\Flac   (36548 of 36548)   Complete  00:00:31
Scanning new files: E:\Flac   (32712 of 32712)   Complete  00:10:24
Discovering playlists: D:\slim\65\playlists   (5 of 5)   Complete  00:00:00
Scanning new playlists: D:\slim\65\playlists   (4 of 4)   Complete  00:00:02
Pre-caching Artwork   (1736 of 1736)   Complete  00:05:03

The server has finished scanning your music collection.
Total Time: 00:16:00 (Monday, August 8, 2011 / 12:51 PM)

Here's what the library stats should look like:

Total Tracks: 32,712
Total Albums: 2,711
Total Artists: 898
Total Genres: 36
Total Playing Time: 2449:19:39

Nothing in the scanner.log. I'll enable debugging and try again.

[JJZolx]

Exact same library stats after the second run. No errors in the logs. There are only 16,362 'Handling new track' entries in the scanner.log. Looking at the database I see 16,362 files scanned in order, then 10 files added to the end, which I presume are files that were skipped and then added when the playlists were scanned.

Looking at the logs and the database, it appears that either the first or last N files from an album/folder are being skipped. I don't see any albums where random tracks were skipped.

Since the album count also comes up short, there must also be entire folders being skipped.

[11-08-08 13:15:50.8398] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Vangelis/Blade%20Runner/09%20Tales%20Of%20The%20Future.flac
[11-08-08 13:15:50.8442] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Vangelis/Blade%20Runner/10%20Damask%20Rose.flac
[11-08-08 13:15:50.8575] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Vangelis/Blade%20Runner/11%20Blade%20Runner%20(End%20Titles).flac
[11-08-08 13:15:50.8668] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Vangelis/Blade%20Runner/12%20Tears%20In%20Rain.flac
[11-08-08 13:15:52.5405] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Velvet%20Underground/The%20Velvet%20Underground/06%20Beginning%20To%20See%20The%20Light.flac
[11-08-08 13:15:52.5570] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Velvet%20Underground/The%20Velvet%20Underground/07%20I%27m%20Set%20Free.flac
[11-08-08 13:15:52.5633] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Velvet%20Underground/The%20Velvet%20Underground/08%20That%27s%20The%20Story%20Of%20My%20Life.flac
[11-08-08 13:15:52.5743] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Velvet%20Underground/The%20Velvet%20Underground/09%20The%20Murder%20Mystery.flac
[11-08-08 13:15:52.5790] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Velvet%20Underground/The%20Velvet%20Underground/10%20After%20Hours.flac

[11-08-08 13:15:47.6621] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Hymns%20to%20the%20Silence/Disc%202/07%20Green%20Mansions.flac
[11-08-08 13:15:47.6715] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Hymns%20to%20the%20Silence/Disc%202/08%20Pagan%20Streams.flac
[11-08-08 13:15:48.7654] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Moondance/07%20These%20Dreams%20Of%20You.flac
[11-08-08 13:15:48.7848] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Moondance/08%20Brand%20New%20Day.flac
[11-08-08 13:15:48.7994] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Moondance/09%20Everyone.flac
[11-08-08 13:15:48.8089] Slim::Utils::Scanner::Local::new (744) Handling new track file:///E:/Flac/Van%20Morrison/Moondance/10%20Glad%20Tidings.flac

[JJZolx]

Here's something odd looking. For the main squeeebox.db database file there are two sets of -shm and -wal files. One set using a name derived from a Window 8.3 type filename and one set from the full file name.

I just wiped the cache clean to launch a new full scan. Previously the -shm and -wal files for the ArtworkCache.db were also using 8.3 type filenames.

[reniera]

scan worked fine for me (22k tracks) both clear/scan or search new files. I saw no change. More or less the same elaspsed time.

[sbellon]

On Mon, 08 Aug, reniera wrote:

> scan worked fine for me (22k tracks) both clear/scan or search new
> files. I saw no change. More or less the same elaspsed time.

Worked for me as well. Same reported data as before. Time went down
from 2 minutes 10 seconds to 1 minute 50 seconds on my i3 powered amd64
Debian GNU/Linux machine for my 9500 tracks library.

--
Stefan Bellon

[JJZolx]

Is it working for anyone on Windows?

I have a feeling that as long as scanner.pl uses Windows 8.3 filenames, things aren't going to work, although I have no idea whether that had anything to do with the missing tracks problem I reported previously.

After I cleared the cache, the initial scan never completed. Looks like the transaction may never have been successfully committed, as the .db-wal file was 40+ MB, but squeezebox.db was very small. The server reports no library configured. It appears that if you begin with an empty cache, then SBS creates the databases, and the .db-shm and .db-wal files for them use full filenames. Then when the external scanner is launched the 8.3 filename versions of .db-shm and .db-wal are created and used during the full scan.