[mythtv-users] find_orphans and error?
Stephen Worthington
stephen_agent at jsw.gen.nz
Wed Jan 21 04:06:46 UTC 2015
On Wed, 21 Jan 2015 14:29:55 +1100, you wrote:
>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
This is what the regular backup dump of the settings table has for its
definition:
--
-- Table structure for table `settings`
--
DROP TABLE IF EXISTS `settings`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `settings` (
`value` varchar(128) NOT NULL DEFAULT '',
`data` varchar(16000) NOT NULL DEFAULT '',
`hostname` varchar(64) DEFAULT NULL,
KEY `value` (`value`,`hostname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
Since that is used to recreate the table on restore, it should be
correct. Now, I am not exactly a MySQL expert, so I read the MySQL
manual for the CREATE TABLE command:
http://dev.mysql.com/doc/refman/5.5/en/create-table.html
and from that, it seems that the KEY definition for the settings table
does not define it as a primary key, and hence duplicates are allowed.
It is likely not set as a primary key as a primary key does not allow
any of its fields to be NULL, and the hostname field is often NULL and
defaults to NULL. Unfortunately, that then allows duplicate rows to
be created somehow.
So I am going to have a read of that page you pointed me at to see if
there is a good way of getting rid of mine.
More information about the mythtv-users
mailing list