Bugzilla – Bug 15243
player table has NULL deviceid and model
Last modified: 2010-04-08 17:25:43 UTC
The players table shows between 150 - 200 players created each day with no deviceid and model fields (both are NULL). Here's a look at the scope of the issue: DATE( created ) Total Players w/ Unknown Type 2009-12-03 59 2009-12-02 182 2009-12-01 164 2009-11-30 167 2009-11-29 192 2009-11-28 184 2009-11-27 198 2009-11-26 154 2009-11-25 177 2009-11-24 125 2009-11-23 116 2009-11-22 139 2009-11-21 196 2009-11-20 122 ... The issue seems to have gotten much worse since 2009-10-28: 2009-10-03 254 2009-10-02 249 2009-10-01 240 2009-09-30 217 2009-09-29 174 <--- note the big spike in unknown player types 2009-09-28 46 2009-09-27 15 2009-09-26 30 2009-09-25 18 2009-09-24 20 2009-09-23 18 2009-09-22 14
The controllers table has less devices connecting than the players table, so it's not a viable workaround. Changing severity to blocker as this blocks sell-thru reporting for EMEA and AMR.
Blocking what? :-) Aren't these devices which don't actually connect to mysb.com but are announced through SBS?
No, check the 'authorized flag'. It's from both places. This is blocking connections reporting data. Emea can't reforcast until this is fixed. Feel free to have a conversation with Michel Landman or Remco if you want a sense of why this is a priority and urgently needed.
== Auto-comment from SVN commit #7801 to the network repo by michael == == https://svn.slimdevices.com/network?view=revision&revision=7801 == Bug: 15243 Description: store deviceid and model on registration
Matthew - what's the query you're using?
I'll spend some time on this. Good news after first investigation: it's very likely not babies or fab4s showing up as NULL devices, but some of the old. There is some schema in the faulty records which reminds me of Duets (temporary SSID Duet is using to set up the receiver being used as the mac address)
== Auto-comment from SVN commit #7803 to the network repo by michael == == https://svn.slimdevices.com/network?view=revision&revision=7803 == Bug: 15243 Description: use raw SQL for the player registration
Some more data: investigating the rows with deviceid = null, grouped by the significant part of the MAC address gives the following for november: | 11 | 00:04:20:17 | 16 | | 11 | 00:04:20:16 | 28 | | 11 | squeezebox | 426 | | 11 | 00:04:20:1b | 1148 | | 11 | 00:04:20:1a | 1239 | and december (that's the top5 for each of them): | 12 | 00:04:20:16 | 14 | | 12 | 00:04:20:17 | 22 | | 12 | squeezebox | 306 | | 12 | 00:04:20:1a | 707 | | 12 | 00:04:20:1b | 821 | According to David's list https://svn.slimdevices.com/repos/test_machine/trunk/uuid/MAC_RANGES.TXT this give us the following: 16-19: receivers 1a-1d: controllers squeezebox: most likely receivers which got incorrectly registered Which leads me to the conclusion that by far the biggest number is irrelevant as for player sales (because they're Controllers). And that something in the 7.4 firmware changed the way Controllers are registered on mysb.com. My previous change should hopefully fix the issue where receivers get incorrectly registered (mac/uuic/parent were mixed up - the middle case of the above where the mac address incorrectly is set to "squeezebox XYZ"). Andy - could you please roll out my change to production, so we can see whether the stats improve?
== Auto-comment from SVN commit #7820 to the network repo by michael == == https://svn.slimdevices.com/network?view=revision&revision=7820 == Bug: 15243 Description: add some debugging in case of suspicious player data
blocker = 7.5/p1
== Auto-comment from SVN commit #7821 to the network repo by michael == == https://svn.slimdevices.com/network?view=revision&revision=7821 == Bug: 15243 Description: backport change 7820 to production
We've narrowed the null device issue down to some bug in LUA's json encoding, where undefined values would get dropped instead of being encoded as "value: null". This particular issue being tracked in bug 15329. In those cases the player_register would be missing uuid, which would lead to shifting left of the remaining items. Which resulted in the mac being stored in the uuid field, the player name in mac, and an empty player name: [Fri Dec 18 22:09:38 2009] bad data in player_register: [ "", ["playerRegister", "00:04:20:26:9b:83", "Squeezebox Radio"], ] Should be: ["playerRegister", "abcdefg...", "00:04:20:26:9b:83", "Squeezebox Radio"]
Index: Comet.pm =================================================================== --- Comet.pm (revision 7819) +++ Comet.pm (working copy) @@ -716,6 +716,14 @@ $self->manager( deliver_events => [ $event ] ); return; } + + # sometimes Lua's json drops empty elements, causing everything to shift to the left + if ( !$name && $uuid && $uuid =~ /^00:04:20/ && $mac && $mac !~ /^00:04:20/ ) { + $name = $mac; + $mac = $uuid; + $uuid = ''; + }
== Auto-comment from SVN commit #7823 to the network repo by michael == == https://svn.slimdevices.com/network?view=revision&revision=7823 == Bug: 15243 Description: in case we get a playerRegister request without the UUID (see bug 15329), shift name/mac back into their fields. Workaround until bug 15329 is fixed.
Matthew - all the changes we did here shouldn't hold you off using the stats already. From what I can say today, you should just ignore the deviceid == null records in the players table. They most likely fall into one of the following categories: - record is an invalid dupe of another (valid) record, due to the issue discussed here and in bug 15329 - device is a Controller which has never connected in its role as a player - device is an iPod/iPhone running iPeng - device is some kind of software (SqueezePlay, SqueezeSlave) or legacy player (SliMP3)
Michael, This is excellent news. I'll set up some MySQL VIEWs for Remco & the EMEA team.
Hold on, hold on... don't rush :-). I'd still like to keep this bug open as we're trying to eliminate at least the first category (invalid dupes).
No longer a blocker
Changing priorities due to management guidance.
That should be fixed now
This bug has been marked fixed in a released version of Squeezebox Server or the accompanying firmware or mysqueezebox.com release. If you are still seeing this issue, please let us know!