[mythtv-users] find_orphans and error?
Mike Holden
mikeholden99+mythtv at gmail.com
Wed Jan 21 03:29:55 UTC 2015
On 21 January 2015 at 13:56, Stephen Worthington <stephen_agent at jsw.gen.nz>
wrote:
> 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?
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
>
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!
I'm an Oracle DBA by trade, and Oracle has a ROWID pseudo-column available,
which makes duplicate removal easy in this situation.
I don't believe that MySQL has this, so something along the lines you
illustrated would do the job.
Since the rows in question are identical in every column, you can't use the
usual method of selecting which rows to remove.
I found this link, which discusses detection and resolution of this:
http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/
They use a temporary table, which pretty much amounts to your method!
Regards
Mike Holden
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20150121/8868d22b/attachment.html>
More information about the mythtv-users
mailing list