<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On 21 January 2015 at 13:56, Stephen Worthington <span dir="ltr"><<a href="mailto:stephen_agent@jsw.gen.nz" target="_blank">stephen_agent@jsw.gen.nz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">On Wed, 21 Jan 2015 09:39:53 +1100, you wrote:<br>
<br>
>On 21 January 2015 at 04:11, Stephen Worthington <<a href="mailto:stephen_agent@jsw.gen.nz">stephen_agent@jsw.gen.nz</a>><br>
>wrote:<br>
><br>
>> mysql> select * from settings where value='BackendServerPort';<br>
>> +-------------------+------+----------+<br>
>> | value | data | hostname |<br>
>> +-------------------+------+----------+<br>
>> | BackendServerPort | 6543 | mypvr |<br>
>> | BackendServerPort | 6543 | mypvr |<br>
>> +-------------------+------+----------+<br>
>> 2 rows in set (0.00 sec)<br>
>><br>
>> That looks a bit strange as there are two identical rows in the table<br>
>> - I have no idea why my database would have that.<br>
><br>
><br>
>It may be that the value has some non-printing or whitespace in it from<br>
>some weird config in the past?<br>
><br>
>Try this:<br>
><br>
>SELECT CONCAT(':' , hostname, ':') FROM settings where<br>
>value='BackendServerPort';<br>
><br>
>This will place a colon immediately before and after the value, so you can<br>
>spot if there's any additional characters in there.<br>
<br>
A useful trick - but when I tried it on all three fields, the colons<br>
were directly before and after the data in the field. So then I<br>
checked the SQL in my most recent backup file, and there were indeed<br>
two rows with the same primary key of ('BackendServerPort', 'mypvr').<br>
So it looks like my settings table is a little corrupt. After a bit<br>
of googling to work out the right query, I tried this:<br>
<br>
mysql> select *, count(*) c from settings group by value,hostname<br>
having c > 1;<br>
+---------------------------------+------------------------------------+----------+---+<br>
| value | data | hostname | c |<br>
+---------------------------------+------------------------------------+----------+---+<br>
| BackendServerIP | 10.0.2.4 | mypvr | 2 |<br>
| BackendServerIP6 | ::1 | mypvr | 2 |<br>
| BackendServerPort | 6543 | mypvr | 2 |<br>
| BackendStatusPort | 6544 | mypvr | 2 |<br>
| DisableFirewireReset | 0 | mypvr | 2 |<br>
| JobAllowCommFlag | 1 | mypvr | 2 |<br>
| JobAllowTranscode | 1 | mypvr | 2 |<br>
| JobAllowUserJob1 | 1 | mypvr | 2 |<br>
| JobAllowUserJob2 | 1 | mypvr | 2 |<br>
| JobAllowUserJob3 | 1 | mypvr | 2 |<br>
| JobAllowUserJob4 | 1 | mypvr | 2 |<br>
| JobQueueCheckFrequency | 60 | mypvr | 2 |<br>
| JobQueueCPU | 0 | mypvr | 2 |<br>
| JobQueueMaxSimultaneousJobs | 4 | mypvr | 2 |<br>
| JobQueueWindowEnd | 23:59 | mypvr | 2 |<br>
| JobQueueWindowStart | 00:00 | mypvr | 2 |<br>
| MiscStatusScript | /usr/local/bin/misc_status_info.sh | mypvr | 2 |<br>
| MythFillFixProgramIDsHasRunOnce | 1 | mypvr | 2 |<br>
| SecurityPin | | mypvr | 2 |<br>
| TruncateDeletesSlowly | 0 | mypvr | 2 |<br>
+---------------------------------+------------------------------------+----------+---+<br>
20 rows in set (0.19 sec)<br>
<br>
And then this to make sure the data values were identical too:<br>
<br>
mysql> select *, count(*) c from settings group by value,hostname,data<br>
having c > 1;<br>
+---------------------------------+------------------------------------+----------+---+<br>
| value | data | hostname | c |<br>
+---------------------------------+------------------------------------+----------+---+<br>
| BackendServerIP | 10.0.2.4 | mypvr | 2 |<br>
| BackendServerIP6 | ::1 | mypvr | 2 |<br>
| BackendServerPort | 6543 | mypvr | 2 |<br>
| BackendStatusPort | 6544 | mypvr | 2 |<br>
| DisableFirewireReset | 0 | mypvr | 2 |<br>
| JobAllowCommFlag | 1 | mypvr | 2 |<br>
| JobAllowTranscode | 1 | mypvr | 2 |<br>
| JobAllowUserJob1 | 1 | mypvr | 2 |<br>
| JobAllowUserJob2 | 1 | mypvr | 2 |<br>
| JobAllowUserJob3 | 1 | mypvr | 2 |<br>
| JobAllowUserJob4 | 1 | mypvr | 2 |<br>
| JobQueueCheckFrequency | 60 | mypvr | 2 |<br>
| JobQueueCPU | 0 | mypvr | 2 |<br>
| JobQueueMaxSimultaneousJobs | 4 | mypvr | 2 |<br>
| JobQueueWindowEnd | 23:59 | mypvr | 2 |<br>
| JobQueueWindowStart | 00:00 | mypvr | 2 |<br>
| MiscStatusScript | /usr/local/bin/misc_status_info.sh | mypvr | 2 |<br>
| MythFillFixProgramIDsHasRunOnce | 1 | mypvr | 2 |<br>
| SecurityPin | | mypvr | 2 |<br>
| TruncateDeletesSlowly | 0 | mypvr | 2 |<br>
+---------------------------------+------------------------------------+----------+---+<br>
20 rows in set (0.09 sec)<br>
<br>
So I seem to somehow have acquired 20 rows with duplicate primary<br>
keys. Fortunately, all the data values are also duplicated, so it<br>
should not be causing me any problems as long as I never have to<br>
change one of those settings. Now I need to work out just how to fix<br>
this - my current thought is to dump the settings table, edit out all<br>
the duplicates from the SQL in the dump, then drop the table and<br>
restore it from the edited dump. Are there any better ideas?<br>
<br>
And does anyone have any idea how this problem might have occurred?<br>
_______________________________________________<br>
mythtv-users mailing list<br>
<a href="mailto:mythtv-users@mythtv.org">mythtv-users@mythtv.org</a><br>
<a href="http://lists.mythtv.org/mailman/listinfo/mythtv-users" target="_blank">http://lists.mythtv.org/mailman/listinfo/mythtv-users</a><br>
<a href="http://wiki.mythtv.org/Mailing_List_etiquette" target="_blank">http://wiki.mythtv.org/Mailing_List_etiquette</a><br>
MythTV Forums: <a href="https://forum.mythtv.org" target="_blank">https://forum.mythtv.org</a><br>
</blockquote></div><br></div><div class="gmail_extra"><div class="gmail_extra" style="font-size:13px">By definition, if you have duplicates, then it can't be a primary key, or even a unique key, but it can be an indexed key where duplicates are allowed!</div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px">I'm an Oracle DBA by trade, and Oracle has a ROWID pseudo-column available, which makes duplicate removal easy in this situation.</div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px">I don't believe that MySQL has this, so something along the lines you illustrated would do the job.<br></div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px">Since the rows in question are identical in every column, you can't use the usual method of selecting which rows to remove.</div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px">I found this link, which discusses detection and resolution of this:</div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px"><a href="http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/" target="_blank">http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/</a><br></div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px">They use a temporary table, which pretty much amounts to your method!</div><div class="gmail_extra" style="font-size:13px"><br></div><div class="gmail_extra" style="font-size:13px"><div dir="ltr">Regards</div><div>Mike Holden</div></div></div></div>