[mythtv-users] find_orphans and error?

[Stephen Worthington]

On Wed, 21 Jan 2015 09:39:53 +1100, you wrote:

>On 21 January 2015 at 04:11, Stephen Worthington <stephen_agent at jsw.gen.nz>
>wrote:
>
>> mysql> select * from settings where value='BackendServerPort';
>> +-------------------+------+----------+
>> | value             | data | hostname |
>> +-------------------+------+----------+
>> | BackendServerPort | 6543 | mypvr    |
>> | BackendServerPort | 6543 | mypvr    |
>> +-------------------+------+----------+
>> 2 rows in set (0.00 sec)
>>
>> That looks a bit strange as there are two identical rows in the table
>> - I have no idea why my database would have that.
>
>
>It may be that the value has some non-printing or whitespace in it from
>some weird config in the past?
>
>Try this:
>
>SELECT CONCAT(':' , hostname, ':') FROM settings where
>value='BackendServerPort';
>
>This will place a colon immediately before and after the value, so you can
>spot if there's any additional characters in there.

A useful trick - but when I tried it on all three fields, the colons
were directly before and after the data in the field.  So then I
checked the SQL in my most recent backup file, and there were indeed
two rows with the same primary key of ('BackendServerPort', 'mypvr').
So it looks like my settings table is a little corrupt.  After a bit
of googling to work out the right query, I tried this:

mysql> select *, count(*) c from settings group by value,hostname
having c > 1;
+---------------------------------+------------------------------------+----------+---+
| value                           | data                               | hostname | c |
+---------------------------------+------------------------------------+----------+---+
| BackendServerIP                 | 10.0.2.4                           | mypvr    | 2 |
| BackendServerIP6                | ::1                                | mypvr    | 2 |
| BackendServerPort               | 6543                               | mypvr    | 2 |
| BackendStatusPort               | 6544                               | mypvr    | 2 |
| DisableFirewireReset            | 0                                  | mypvr    | 2 |
| JobAllowCommFlag                | 1                                  | mypvr    | 2 |
| JobAllowTranscode               | 1                                  | mypvr    | 2 |
| JobAllowUserJob1                | 1                                  | mypvr    | 2 |
| JobAllowUserJob2                | 1                                  | mypvr    | 2 |
| JobAllowUserJob3                | 1                                  | mypvr    | 2 |
| JobAllowUserJob4                | 1                                  | mypvr    | 2 |
| JobQueueCheckFrequency          | 60                                 | mypvr    | 2 |
| JobQueueCPU                     | 0                                  | mypvr    | 2 |
| JobQueueMaxSimultaneousJobs     | 4                                  | mypvr    | 2 |
| JobQueueWindowEnd               | 23:59                              | mypvr    | 2 |
| JobQueueWindowStart             | 00:00                              | mypvr    | 2 |
| MiscStatusScript                | /usr/local/bin/misc_status_info.sh | mypvr    | 2 |
| MythFillFixProgramIDsHasRunOnce | 1                                  | mypvr    | 2 |
| SecurityPin                     |                                    | mypvr    | 2 |
| TruncateDeletesSlowly           | 0                                  | mypvr    | 2 |
+---------------------------------+------------------------------------+----------+---+
20 rows in set (0.19 sec)

And then this to make sure the data values were identical too:

mysql> select *, count(*) c from settings group by value,hostname,data
having c > 1;
+---------------------------------+------------------------------------+----------+---+
| value                           | data                               | hostname | c |
+---------------------------------+------------------------------------+----------+---+
| BackendServerIP                 | 10.0.2.4                           | mypvr    | 2 |
| BackendServerIP6                | ::1                                | mypvr    | 2 |
| BackendServerPort               | 6543                               | mypvr    | 2 |
| BackendStatusPort               | 6544                               | mypvr    | 2 |
| DisableFirewireReset            | 0                                  | mypvr    | 2 |
| JobAllowCommFlag                | 1                                  | mypvr    | 2 |
| JobAllowTranscode               | 1                                  | mypvr    | 2 |
| JobAllowUserJob1                | 1                                  | mypvr    | 2 |
| JobAllowUserJob2                | 1                                  | mypvr    | 2 |
| JobAllowUserJob3                | 1                                  | mypvr    | 2 |
| JobAllowUserJob4                | 1                                  | mypvr    | 2 |
| JobQueueCheckFrequency          | 60                                 | mypvr    | 2 |
| JobQueueCPU                     | 0                                  | mypvr    | 2 |
| JobQueueMaxSimultaneousJobs     | 4                                  | mypvr    | 2 |
| JobQueueWindowEnd               | 23:59                              | mypvr    | 2 |
| JobQueueWindowStart             | 00:00                              | mypvr    | 2 |
| MiscStatusScript                | /usr/local/bin/misc_status_info.sh | mypvr    | 2 |
| MythFillFixProgramIDsHasRunOnce | 1                                  | mypvr    | 2 |
| SecurityPin                     |                                    | mypvr    | 2 |
| TruncateDeletesSlowly           | 0                                  | mypvr    | 2 |
+---------------------------------+------------------------------------+----------+---+
20 rows in set (0.09 sec)

So I seem to somehow have acquired 20 rows with duplicate primary
keys.  Fortunately, all the data values are also duplicated, so it
should not be causing me any problems as long as I never have to
change one of those settings.  Now I need to work out just how to fix
this - my current thought is to dump the settings table, edit out all
the duplicates from the SQL in the dump, then drop the table and
restore it from the edited dump.  Are there any better ideas?

And does anyone have any idea how this problem might have occurred?