[mythtv-users] RRe: find_orphans and error?

[Keith Pyle]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20150121/866f68c3/attachment.html>