Duplicate entry 'Default' for key 'recgroup'

[neilbrown66]

Hi all

When upgrading mythbunutu from .27 to .28 i keep getting the error 'Duplicate entry 'Default' for key 'recgroup'' when the database tries to upgrade

I am running ubuntu 14-04
for the full output please see below

B Error (Performing database upgrade):
Query was: INSERT INTO recgroups ( recgroup, special ) VALUES ( 'Default', '1' )

Error was: Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error was:
Duplicate entry 'Default' for key 'recgroup'

Thanks in advance

[bill6502]

There should have been something in the log that said the upgrade was
for DBSchemaver 1321. recgroups looks like a new table at that time (
or it's very old 'cause it's not in dbcheck.cpp.)

Maybe you had a previous aborted attempt to upgrade and the
table was created.

You can reload an old backup of your database and try again. Or, if
comfortable with SQL, do: DROP TABLE recgroups;.

[neilbrown66]

Hi Bill
Many thanks for your help on this, i have been testing the update path on a virtual machine. So i have rolled it back to a previous snapshot and checked in the database and cannot see the table recgroups. This is the output below when I first run mythbackend

neil@neil-media3:~$ mythbackend
2017-08-29 22:03:33.635059 C mythbackend version: fixes/0.28 [v0.28.1-38-geef6a48] www.mythtv.org
2017-08-29 22:03:33.635085 C Qt version: compile: 5.2.1, runtime: 5.2.1
2017-08-29 22:03:33.635097 N Enabled verbose msgs: general
2017-08-29 22:03:33.635112 N Setting Log Level to LOG_INFO
2017-08-29 22:03:33.650013 I Added logging to the console
2017-08-29 22:03:33.650590 I Setup Interrupt handler
2017-08-29 22:03:33.650602 I Setup Terminated handler
2017-08-29 22:03:33.650618 I Setup Segmentation fault handler
2017-08-29 22:03:33.650626 I Setup Aborted handler
2017-08-29 22:03:33.650640 I Setup Bus error handler
2017-08-29 22:03:33.650649 I Setup Floating point exception handler
2017-08-29 22:03:33.650663 I Setup Illegal instruction handler
2017-08-29 22:03:33.650686 I Setup Real-time signal 0 handler
2017-08-29 22:03:33.650698 I Setup Hangup handler
2017-08-29 22:03:33.650962 N Using runtime prefix = /usr
2017-08-29 22:03:33.650977 N Using configuration directory = /home/neil/.mythtv
2017-08-29 22:03:33.651056 I Assumed character encoding: en_GB.UTF-8
2017-08-29 22:03:33.651561 N Empty LocalHostName.
2017-08-29 22:03:33.651568 I Using localhost value of neil-media3
2017-08-29 22:03:33.675561 N Setting QT default locale to en_US
2017-08-29 22:03:33.675575 I Current locale en_US
2017-08-29 22:03:33.675632 N Reading locale defaults from /usr/share/mythtv//locales/en_us.xml
2017-08-29 22:03:33.694597 I Loading en_gb translation for module mythfrontend
2017-08-29 22:03:33.703434 I Current MythTV Schema Version (DBSchemaVer): 1320
2017-08-29 22:03:33.720728 E Backing up database with script: '/usr/share/mythtv/mythconverg_backup.pl'
2017-08-29 22:03:33.721998 I Starting process manager
2017-08-29 22:03:33.729120 I Starting process signal handler
2017-08-29 22:03:33.732483 I Starting IO manager (read)
2017-08-29 22:03:33.729024 I Starting IO manager (write)

Warning: MythTV wants to upgrade your database,
for the TV schema, from 1320 to 1344.

Database Host: localhost
Database Name: mythconverg

If your system becomes unstable, a database backup is located in /var/lib/mythtv/db_backups/mythconverg-1320-20170829210333.sql.gz


Shall I upgrade this database? [yes] 2017-08-29 22:03:55.550424 C Database Backup complete.
2017-08-29 22:03:55.550669 C Backed up database to file: '/var/lib/mythtv/db_backups/mythconverg-1320-20170829210333.sql.gz'

2017-08-29 22:04:02.812692 C Upgrading to MythTV schema version 1321
2017-08-29 22:04:02.814446 E DB Error (Performing database upgrade):
Query was: INSERT INTO recgroups ( recgroup, special ) VALUES ( 'Default', '1' );
Error was: Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error was:
Duplicate entry 'Default' for key 'recgroup'

2017-08-29 22:04:02.814494 E Database schema upgrade failed.
2017-08-29 22:04:02.815598 E Couldn't upgrade database to new schema



2017-08-29 22:04:02.814494 E Database schema upgrade failed.
2017-08-29 22:04:02.815598 E Couldn't upgrade database to new schema

If then drop the recgroups table from the database i get the following

2017-08-29 22:10:35.253143 C mythbackend version: fixes/0.28 [v0.28.1-38-geef6a48] www.mythtv.org
2017-08-29 22:10:35.253179 C Qt version: compile: 5.2.1, runtime: 5.2.1
2017-08-29 22:10:35.253191 N Enabled verbose msgs: general
2017-08-29 22:10:35.253206 N Setting Log Level to LOG_INFO
2017-08-29 22:10:35.268297 I Added logging to the console
2017-08-29 22:10:35.268930 I Setup Interrupt handler
2017-08-29 22:10:35.268942 I Setup Terminated handler
2017-08-29 22:10:35.268958 I Setup Segmentation fault handler
2017-08-29 22:10:35.268967 I Setup Aborted handler
2017-08-29 22:10:35.268982 I Setup Bus error handler
2017-08-29 22:10:35.268991 I Setup Floating point exception handler
2017-08-29 22:10:35.269006 I Setup Illegal instruction handler
2017-08-29 22:10:35.269037 I Setup Real-time signal 0 handler
2017-08-29 22:10:35.269049 I Setup Hangup handler
2017-08-29 22:10:35.269390 N Using runtime prefix = /usr
2017-08-29 22:10:35.269404 N Using configuration directory = /home/neil/.mythtv
2017-08-29 22:10:35.269542 I Assumed character encoding: en_GB.UTF-8
2017-08-29 22:10:35.270320 N Empty LocalHostName.
2017-08-29 22:10:35.270329 I Using localhost value of neil-media3
2017-08-29 22:10:35.296738 N Setting QT default locale to en_US
2017-08-29 22:10:35.296753 I Current locale en_US
2017-08-29 22:10:35.296816 N Reading locale defaults from /usr/share/mythtv//locales/en_us.xml
2017-08-29 22:10:35.316405 I Loading en_gb translation for module mythfrontend
2017-08-29 22:10:35.327152 I Current MythTV Schema Version (DBSchemaVer): 1320
2017-08-29 22:10:35.344693 E Backing up database with script: '/usr/share/mythtv/mythconverg_backup.pl'
2017-08-29 22:10:35.345321 I Starting process manager
2017-08-29 22:10:35.346712 I Starting IO manager (read)
2017-08-29 22:10:35.350285 I Starting IO manager (write)
2017-08-29 22:10:35.350289 I Starting process signal handler

Warning: MythTV wants to upgrade your database,
for the TV schema, from 1320 to 1344.

Database Host: localhost
Database Name: mythconverg

If your system becomes unstable, a database backup is located in /var/lib/mythtv/db_backups/mythconverg-1320-20170829211035.sql.gz


Shall I upgrade this database? [yes] 2017-08-29 22:10:56.982550 C Database Backup complete.
2017-08-29 22:10:56.983432 C Backed up database to file: '/var/lib/mythtv/db_backups/mythconverg-1320-20170829211035.sql.gz'

2017-08-29 22:10:59.288049 C Upgrading to MythTV schema version 1321
2017-08-29 22:10:59.295423 E DB Error (Performing database upgrade):
Query was: DELETE FROM recgrouppassword WHERE password = '';
Error was: Driver error was [2/1146]:
QMYSQL: Unable to execute query
Database error was:
Table 'mythconverg.recgrouppassword' doesn't exist

2017-08-29 22:10:59.295466 E Database schema upgrade failed.
2017-08-29 22:10:59.296210 E Couldn't upgrade database to new schema
neil@neil-media3:~$ ^C
neil@neil-media3:~$

Not sure if this helps

[bill6502]

It does help, but makes me question the health of your DB, or, my not
knowing about VMs. If recgroups isn't in the DB, then you shouldn't
need to drop the table. I can't answer the question of which DB the
VM is using (the host's or the VMs.) Looking at /home/neil/.mythtv/config.xml
would tell you which host/port etc. is being used.

recgrouppassword is older than dirt ;). But obviously missing. You might see
if it's in the hosts DB (as opposed to the VMs.)

[neilbrown66]

Hi again and thanks for your reply

Maybe i should have given you a bit of history. I tried to upgrade my machine some time ago and it went horribly wrong, thankfully i had imaged the OS partition prior to updating. I successfully recovered and rather than compromise the machine i have been replicating the issue on a VM in order to try an upgrade route. My physical machine is running running 12.04.5 and mythtv .25.

I have basically built a the virtual machine from scratch using an ubunutu distro and restored the database from my physical and gone through the process of renaming in the database and changing IP addresses etc. The full route i have taken is as attached

The initial database installs and upgrades just fine, however when i try and upgrade to .28 i keep getting this error about tables. I have checked on my physical machine and cannot see 'recgroup' or 'recgrouppassword' tables in my database.

Looking at my config.xml below

<Configuration>
<Database>
<PingHost>1</PingHost>
<Host>localhost</Host>
<UserName>mythtv</UserName>
<Password>dLer110E</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
<WakeOnLAN>
<Enabled>0</Enabled>
<SQLReconnectWaitTime>0</SQLReconnectWaitTime>
<SQLConnectRetry>5</SQLConnectRetry>
<Command>echo 'WOLsqlServerCommand not set'</Command>
</WakeOnLAN>
<UPnP>
<UDN>
<MediaRenderer>951de3e4-b3bb-4e8b-9c2e-45b18f457a69</MediaRenderer>
</UDN>
</UPnP>

This may or may not help again

[bill6502]

Seems to be a tough one. localhost, I assume, is relative to the VM,
so the proper DB is being used. I was concerned about a stale IP
address pointing back physical machine.

Hopefully just a typo above, the 1st table of concern is recgroups,
not recgroup.

I still can't understand why there would be a: 'Default' for key 'recgroups' failure
if that table doesn't exist, e.g. DESC recgroups; should say that the
table doesn't exist. Or, at least SELECT * FROM recgroups; would
be empty.

Now for the hard part, recgrouppassword is created during the initial DB
setup. But it's then modified twice (in schemas 1252 and 1262.) How it
got removed even in the physical DB is another mystery. I'll send you
something in a private message regarding that.

[neilbrown66]

Hi Bill
Thanks for your reply again. Now that i think about it i may still have the original ISO that i built the physical with, i will have a go at finding this and installing it on a VM to see if i can see the missing tables, as you say there may well be more. This way i should be able to back them up and restore them to the other machines. I am on holiday now for a week so wont have chance to do anything further with this until my return. So i will post again then.

By the way is there a site that tells me which tables are changed with the database schema revisions?

[bill6502]

Hi,

The link in the PM I sent you is the only way to tell what tables
change.