Mailing List Archive: MythTV: Users

Error upgrading DB scheme from .20.2 to 21

Index | Next | Previous | View Threaded

brent at brentnorris

May 20, 2008, 1:39 PM

Post #1 of 4 (318 views)
Permalink

Error upgrading DB scheme from .20.2 to 21

When mythbackend attempts to upgrade the DB on my friends mythtv install
it fails with the following error:

2008-05-20 15:18:04.443 DB Error (Performing database upgrade):
Query was: INSERT storagegroup (groupname, hostname, dirname) SELECT
DISTINCT 'Default', hostname, data FROM settings WHERE value =
'RecordFilePrefix' AND hostname IS NOT NULL AND hostname
<> '';
Error was: Driver error was [2/1062]:
QMYSQL3: Unable to execute query
Database error was:
Duplicate entry 'Default-mythback-/storage' for key 2

new version: 1171
2008-05-20 15:18:04.445 Database Schema upgrade FAILED, unlocking.
2008-05-20 15:18:04.446 Couldn't upgrade database to new schema

Logging into mysql and running the query results in:

mysql> select DISTINCT 'Default', 'mythback', '/storage' from settings;
+---------+----------+----------+
| Default | mythback | /storage |
+---------+----------+----------+
| Default | mythback | /storage |
+---------+----------+----------+
1 row in set (0.00 sec)

It looks to me like this duplicate entry is the problem, what do i need
to do to get rid of it, so that the upgrade can complete?

Thanks

Brent
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mythtv at mikeholden

May 20, 2008, 2:03 PM

Post #2 of 4 (303 views)
Permalink

Re: Error upgrading DB scheme from .20.2 to 21 [In reply to]

Brent Norris wrote:
> When mythbackend attempts to upgrade the DB on my friends mythtv install
> it fails with the following error:
>
> 2008-05-20 15:18:04.443 DB Error (Performing database upgrade):
> Query was: INSERT storagegroup (groupname, hostname, dirname) SELECT
> DISTINCT 'Default', hostname, data FROM settings WHERE value =
> 'RecordFilePrefix' AND hostname IS NOT NULL AND hostname
> <> '';
> Error was: Driver error was [2/1062]:
> QMYSQL3: Unable to execute query
> Database error was:
> Duplicate entry 'Default-mythback-/storage' for key 2
>
> new version: 1171
> 2008-05-20 15:18:04.445 Database Schema upgrade FAILED, unlocking.
> 2008-05-20 15:18:04.446 Couldn't upgrade database to new schema
>
>
> Logging into mysql and running the query results in:
>
> mysql> select DISTINCT 'Default', 'mythback', '/storage' from settings;
> +---------+----------+----------+
> | Default | mythback | /storage |
> +---------+----------+----------+
> | Default | mythback | /storage |
> +---------+----------+----------+
> 1 row in set (0.00 sec)
>
> It looks to me like this duplicate entry is the problem, what do i need
> to do to get rid of it, so that the upgrade can complete?
>
> Thanks
>
> Brent
> _______________________________________________
> mythtv-users mailing list
> mythtv-users[at]mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

Sorry, but that query doesn't prove anything. All you're asking it do do
is output those 3 literal strings to the screen, not actually pull any
data from the table at all:

mysql> select DISTINCT 'tom', 'dick', 'harry' from settings;
+-----+------+-------+
| tom | dick | harry |
+-----+------+-------+
| tom | dick | harry |
+-----+------+-------+

What you should be running is tthe query you posted earlier, which is:

SELECT DISTINCT 'Default', hostname, data FROM settings WHERE
value = 'RecordFilePrefix' AND hostname IS NOT NULL AND
hostname <> '';

mysql> SELECT DISTINCT 'Default', hostname, data FROM settings
WHERE value = 'RecordFilePrefix' AND hostname IS NOT NULL
AND hostname <> '';
+---------+-------------+----------------------------+
| Default | hostname | data |
+---------+-------------+----------------------------+
| Default | OLDHOSTNAME | /var/lib/mythtv/recordings |
+---------+-------------+----------------------------+

--
Mike Holden

http://www.by-ang.com - the place to shop for all manner of hand crafted
items, including Jewellery, Greetings Cards and Gifts

_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

brent at brentnorris

May 20, 2008, 3:39 PM

Post #3 of 4 (296 views)
Permalink

Re: Error upgrading DB scheme from .20.2 to 21 [In reply to]

Mike Holden wrote:
> What you should be running is tthe query you posted earlier, which is:
>
> SELECT DISTINCT 'Default', hostname, data FROM settings WHERE
> value = 'RecordFilePrefix' AND hostname IS NOT NULL AND
> hostname <> '';
>
> mysql> SELECT DISTINCT 'Default', hostname, data FROM settings
> WHERE value = 'RecordFilePrefix' AND hostname IS NOT NULL
> AND hostname <> '';
> +---------+-------------+----------------------------+
> | Default | hostname | data |
> +---------+-------------+----------------------------+
> | Default | OLDHOSTNAME | /var/lib/mythtv/recordings |
> +---------+-------------+----------------------------+
>

Whoops sorry:

mysql> SELECT DISTINCT 'Default', hostname, data FROM settings WHERE
-> value = 'RecordFilePrefix' AND hostname IS NOT NULL
AND
-> hostname <> '';
+---------+----------+----------+
| Default | hostname | data |
+---------+----------+----------+
| Default | mythback | /storage |
+---------+----------+----------+
1 row in set (0.01 sec)

So this result makes even less sense to me when related to the error
that is in the log file:

ERROR 1062 (23000): Duplicate entry 'Default-mythback-/storage' for key 2

since it doesn't seem like there is a duplicate entry.

Brent
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mythtv at mikeholden

May 20, 2008, 4:04 PM

Post #4 of 4 (296 views)
Permalink

Re: Error upgrading DB scheme from .20.2 to 21 [In reply to]

You don't currently have a duplicate entry, but if you try to insert a new
record with the same key value as an existing record, then the insert
fails because otherwise you WILL have a duplicate.

The error is telling you that you are trying to insert a record that
already exists, and thus the insert fails.

You could maybe try deleting the existing record and letting the upgrade
re-create it perhaps. Make sure you back up your database first before
manual intervention like this though!

Something like:

DELETE FROM settings
WHERE value = 'RecordFilePrefix'
AND hostname IS NOT NULL
AND hostname <> '';
--
Mike Holden

http://www.by-ang.com - the place to shop for all manner of hand crafted
items, including Jewellery, Greetings Cards and Gifts

Brent Norris wrote:
> Mike Holden wrote:
>> What you should be running is tthe query you posted earlier, which is:
>>
>> SELECT DISTINCT 'Default', hostname, data FROM settings WHERE
>> value = 'RecordFilePrefix' AND hostname IS NOT NULL AND
>> hostname <> '';
>>
>> mysql> SELECT DISTINCT 'Default', hostname, data FROM settings
>> WHERE value = 'RecordFilePrefix' AND hostname IS NOT NULL
>> AND hostname <> '';
>> +---------+-------------+----------------------------+
>> | Default | hostname | data |
>> +---------+-------------+----------------------------+
>> | Default | OLDHOSTNAME | /var/lib/mythtv/recordings |
>> +---------+-------------+----------------------------+
>>
>
> Whoops sorry:
>
> mysql> SELECT DISTINCT 'Default', hostname, data FROM settings
> WHERE
> -> value = 'RecordFilePrefix' AND hostname IS NOT NULL
> AND
> -> hostname <> '';
> +---------+----------+----------+
> | Default | hostname | data |
> +---------+----------+----------+
> | Default | mythback | /storage |
> +---------+----------+----------+
> 1 row in set (0.01 sec)
>
> So this result makes even less sense to me when related to the error
> that is in the log file:
>
> ERROR 1062 (23000): Duplicate entry 'Default-mythback-/storage' for key 2
>
> since it doesn't seem like there is a duplicate entry.
>
> Brent
> _______________________________________________
> mythtv-users mailing list
> mythtv-users[at]mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com