[mythtv-users] RRe: find_orphans and error?

[Stephen Worthington]

On Wed, 21 Jan 2015 11:40:59 -0600, you wrote:

>On 01/21/15 05:31, Stephen Worthington wrote:
>> 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?
>
>How about:
>
>    delete from settings where value = 'BackendServerPort' and hostname
>    = 'mypvr' limit 1;
>
>
>This will only delete one value, regardless of how many match the where 
>clause.
>
>You would have to do this manually for each value, or you could generate 
>the commands:
>
>    mysql -BN -h mypvr mythconverg -e 'select value,hostname from
>    settings group by value,hostname having count(value) > 1' | perl -n
>    -e '($value,$host) = split; print "delete from settings where value
>    = \"$value\" and hostname = \"$host\" limit1;\n"'
>
>
>Review the output and if you like the result, then feed the SQL 
>statements to a mysql command.
>
>Keith

Thank you, that has done the trick.  I do not speak Perl, but it is
obviously very useful for this sort of thing.