Hi Erland, to help us target this fix, what kind of symptoms would this present to the end user?

The consequence when this happens is that SqueezeCenter will no longer be able to store statistics (ratings, playcounts and last played time) so it survives rescan. You don't get any errors in the log, the statistics just disappears when you perform a rescan.

For people with a lot of ratings this is a major issue, for people that doesn't care about the statistics it's a minor issue. The result in the database is that the contents of the tracks_persistent table will be increased with the number of tracks in the tracks table for each rescan, this might after some time result in a huge tracks_persistent table and probably affect performance a bit. After three rescans, you will have tree rows in tracks_persistent with the same url but only one with a value in the track column.

However, there need to be a very special situation for this problem to happen which might make it less critical.

The problem is related to this bug report which was never solved:
https://bugs-archive.lyrion.org/show_bug.cgi?id=4387

The situation to reproduce this is:
1. You need a 6.5.0 installation (6.5.0 is important, if you start with a later 6.5.x release it will work).
2. The 6.5.0 installation needs to be upgraded to 7.2 (either directly or by going through the upgrade of each 6.5.x and 7.x release until you reach 7.2)

It is important that the same Cache/MySQL directory is used in both step 1 and step 2. If you start step 2 with an empty Cache/MySQL directory you won't see the problem. I'm not sure, but I think the Cache/MySQL directory might have been moved between 6.5.x and 7.x and in that case you will have a empty Cache/MySQL directory in step 2 and everything works.

The real problem is that the database upgrade mentioned in bug #4387 doesn't work the same if you start with an empty database compared to if you start with a database with contents. The ALTER TABLE directives in schema_3_up.sql only affects new columns in the tables if the table contains information, if the table is empty it also affects existing columns.

So people that upgraded directly from 6.3.x to 6.5.1(or later) won't see this problem, because then the incorrect upgrade script (schema_3_up.sql) will execute on an empty database and work correctly.

The reason I got the problem in my setup is that I've configured SqueezeCenter to use an external MySQL instance instead of the bundled one. The result is that my database is never recreated during upgrades, the tables are just upgraded with the upgrad script, it has been upgraded from 6.5.0 through all intermediate releases to 7.2. 

A workaround for people that get this problem is to shutdown SqueezeCenter, manually delete the Cache/MySQL directory and startup SqueezeCenter. This this case the mysql upgrade scripts will execute on an empty database and you will get the correct result. This will of course result in that they lose all their statistics, but at least future statistics will survive rescans.

Finally, please note that I'm not really sure that the problem is the tracks_persistent table, it might be all the other tables that have incorrect collate. So someone that really knows which collate the tables are supposed to have should probably investigate if the tracks_persistent or all the other tables should be corrected.

In a configuration that is affected by this bug, it will look like this:
- tracks_persistent: Table collate "utf8_unicode_ci", url column collate "utf8_unicode_ci"
- tracks: No collate shown, which means that it uses the database default collate which is set to "utf8_general_ci" both on the table and the url column.

In a clean 7.2 database started from a completely new database, you will have:
- tracks_persistent: Table collate "utf8_unicode_ci", url column collate "utf8_unicode_ci"
- tracks: Table collate none (which means utf8_general_ci), url column collate "utf8_unicode_ci".

I use the "show create table tracks" and "show create table tracks_persistent" SQL commands to view the collate settings. The table can have one collate and the columns within it can have another collate. The important thing to make sure it works is that the column collate settings matches between the tables, if they don't you can't join with these columns.

Fixed in 7.3 change 23456.

I'm afraid this change 23456 just moves the problem to another situation.

To solve this I believe you will need to correct the problem that have existed since schema_4_up.sql was created. Read description in comment #2 and this bug report for more information:
https://bugs-archive.lyrion.org/show_bug.cgi?id=4387

You will need to do the same conversion for ALL tables in the database to get the correct collate, for example like:
alter table tracks convert to character set utf8 collate
utf8_general_ci;

The tracks, table is just a sample, this has to be done for ALL tables with text columns.

This is now critical to solve, because at the moment after the last change, every user will be affected at all times because an newly created database now has inconsistent collates. It's very easy to reproduce, just delete the Cache directory and restart SqueezeCenter.
The ONLY users that get correct collates at the moment are those that have upgraded from 6.5.0 without deleting the MySQL database.

I'll revert your patch so it won't affect anyone.  Please provide a correct patch, or I'll look at this later.

(In reply to comment #6)
> I'll revert your patch so it won't affect anyone.  Please provide a correct
> patch, or I'll look at this later.
> 

I'm guessing that the idea is that collate on both columns and tables should be set to utf8_general_ci ?

Created attachment 4117 [details]
Patch to change table and column collates to utf8_general_ci

This patch changes all table and column collates to utf8_general_ci.

The downgrade script converts the database to a working layout with table collate set to utf8_general_ci and column collate to utf8_unicode_ci. This is the same as a clean newly created 7.x database looked before this patch. 

IMPORTANT!
If the table collates are really supposed to be different than the column collates, the upgrade script in this patch will not do the correct thing. I can't see any reason why they should differ and in that case the upgrade script works correctly. 
If they really should differ, the downgrade script in this patch will do a correct upgrade (yes I know it sounds a bit strange, but it really does).

A database upgraded from 6.5.0 will be converted to the same collate settings as a clean newly created database with the downgrade script. It's not possible to convert it to the same as before the upgrade in this patch was run, to do that we need to have two downgrade scripts, one for newly created databases and one for 6.5.0 upgraded databases.
This also means that for a 6.5.0 upgraded database which is affected by this bug, the downgrade script does NOT downgrade to a non working situation, it downgrades the database to the same situation as a newly created 7.x database which works correctly already today.

I've verified this patch in two different Ubuntu setups:
1. A setup upgraded from 6.5.0 which was affected by this bug report
2. A setup of a newly installed 7.x release which was not affected by this bug report.

I've not verified this patch on Windows or Mac, but I can't see any reason why it wouldn't work correctly on those platforms too.

Fixed in change 23791.

This bug has been fixed in the 7.3.0 release version of SqueezeCenter!

Please download the new version from http://www.slimdevices.com/su_downloads.html if you haven't already.  

If you are still experiencing this problem, feel free to reopen the bug with your new comments and we'll have another look.

Reduce number of active targets for SC