[mythtv-users] 0.20 to 0.21 upgrade problems: Duplicate entry error while restoring database and duplicate entry line error while upgrading schema?

Mon Oct 13 05:11:29 UTC 2008

Hi all,

I'm trying to upgrade from 0.20 to 0.21.

I had fairly old but super stable 0.20 on my system (FC6 BE/FE) and now I have moved to from Fedora to Ubuntu 8.04:

elveli at hippo:~$ mythbackend --version
Please include all output in bug reports.
MythTV Version   : 16838
MythTV Branch    : branches/release-0-21-fixes
Library API      : 0.21.20080304-1
Network Protocol : 40
Options compiled in:
 linux profile using_oss using_alsa using_arts using_jack using_backend using_dbox2 using_dvb using_firewire using_frontend using_hdhomerun using_iptv using_ivtv using_joystick_menu using_libfftw3 using_lirc using_opengl_vsync using_opengl_video using_v4l using_x11 using_xrandr using_xv using_xvmc using_xvmcw using_xvmc_vld using_glx_proc_addr_arb using_bindings_perl using_bindings_python using_opengl using_ffmpeg_threads using_libavc_5_3 using_live
elveli at hippo:~$

elveli at hippo:~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
elveli at hippo:~$

I have two issues with my upgrade

A) Duplicate entry error while restoring recordings file:
=========================================================

Backup restore works ok on new system:

elveli at hippo:~/mytti$ ls -ls restore.sql mythtv_backup.sql
132896 -rw-r--r-- 1 elveli elveli 135943023 2008-10-12 13:04 mythtv_backup.sql
107752 -rw-r--r-- 1 elveli elveli 110220860 2008-10-12 15:43 restore.sql
elveli at hippo:~/mytti$

root at hippo:/home/user/mytti# mysql -u mythtv mythconverg -pmythtv <mythtv_backup.sql

But restore.sql file that holds recording data does not work:

root at hippo:/home/user/mytti# mysql -u mythtv mythconverg -pmythtv <restore.sql
ERROR 1062 (23000) at line 1: Duplicate entry '26' for key 1

restore.sql was created using this script on a old 0.20 backend:

root at hippo:/home/user/mytti# cat tt.sh
grep "INSERT INTO \`record\` "          mythtv_backup.sql > restore.sql
grep "INSERT INTO \`recorded\` "        mythtv_backup.sql >> restore.sql
grep "INSERT INTO \`oldrecorded\` "     mythtv_backup.sql >> restore.sql
grep "INSERT INTO \`recordedprogram\` " mythtv_backup.sql >> restore.sql
grep "INSERT INTO \`recordedrating\` "  mythtv_backup.sql >> restore.sql
grep "INSERT INTO \`recordedmarkup\` "  mythtv_backup.sql >> restore.sql
grep "INSERT INTO \`recordedseek\` "    mythtv_backup.sql >> restore.sql

root at hippo:/home/user/mytti#

B) Schema upgrade (from 1160) fails:
=====================================

Now when I ran mythbackend on terminal window I get this:

user at hippo:~$ sudo mythbackend
user at hippo:~/mytti$ mythfrontend
2008-10-12 19:25:30.390 Using runtime prefix = /usr, libdir = /usr/lib
2008-10-12 19:25:31.163 XScreenSaver support enabled
2008-10-12 19:25:31.163 DPMS is active.
2008-10-12 19:25:31.164 Empty LocalHostName.
2008-10-12 19:25:31.165 Using localhost value of hippo
2008-10-12 19:25:31.181 New DB connection, total: 1
2008-10-12 19:25:31.186 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:31.230 Closing DB connection named 'DBManager0'
2008-10-12 19:25:31.313 Primary screen 0.
2008-10-12 19:25:31.315 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:31.320 Using screen 0, 1280x1024 at 0,0
2008-10-12 19:25:31.363 New DB connection, total: 2
2008-10-12 19:25:31.364 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:31.368 Unexpected DB Schema version.  Waiting to see if DB is being upgraded.
2008-10-12 19:25:36.380 Timed out waiting.
2008-10-12 19:25:36.380 This version of MythTV requires an updated database schema. Please run mythtv-setup or mythbackend to update your database.
user at hippo:~/mytti$ mythbackend
2008-10-12 19:25:45.447 Using runtime prefix = /usr, libdir = /usr/lib
2008-10-12 19:25:45.448 Empty LocalHostName.
2008-10-12 19:25:45.448 Using localhost value of hippo
2008-10-12 19:25:45.460 New DB connection, total: 1
2008-10-12 19:25:45.466 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:45.468 Closing DB connection named 'DBManager0'
2008-10-12 19:25:45.469 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:45.470 New DB connection, total: 2
2008-10-12 19:25:45.471 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:45.473 Current Schema Version: 1160
2008-10-12 19:25:45.484 New DB connection, total: 3
2008-10-12 19:25:45.485 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:25:45.489 Backing up database to file: /video/store/mythconverg-1160-20081012192545.sql
2008-10-12 19:26:03.896 Compressing database backup file.
2008-10-12 19:26:15.768 Database Backup filename: /video/store/mythconverg-1160-20081012192545.sql.gz
2008-10-12 19:26:15.768 Database Backup complete.

Warning: MythTV wants to upgrade your database schema, from 1160 to 1214.

If your system becomes unstable, a database backup is located in /video/store/mythconverg-1160-20081012192545.sql.gz

Shall I upgrade this database? [yes]
2008-10-12 19:26:24.461 Newest Schema Version : 1214
2008-10-12 19:26:24.464 Upgrading to schema version 1161
2008-10-12 19:26:24.474 Upgrading to schema version 1162
2008-10-12 19:26:24.481 Upgrading to schema version 1163
2008-10-12 19:26:24.519 New DB connection, total: 4
2008-10-12 19:26:24.522 Connected to database 'mythconverg' at host: localhost
2008-10-12 19:26:24.524 Upgrading to schema version 1164
2008-10-12 19:26:24.550 Upgrading to schema version 1165
2008-10-12 19:26:27.241 Upgrading to schema version 1166
2008-10-12 19:26:28.077 Upgrading to schema version 1167
2008-10-12 19:26:28.080 Upgrading to schema version 1168
2008-10-12 19:26:28.081 Upgrading to schema version 1169
2008-10-12 19:26:28.084 Upgrading to schema version 1170
2008-10-12 19:26:28.085 Upgrading to schema version 1171
2008-10-12 19:26:28.087 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-alpo-/var/lib/mythtv/recordings' for key 2

new version: 1171
2008-10-12 19:26:28.088 Database Schema upgrade FAILED, unlocking.
2008-10-12 19:26:28.088 Couldn't upgrade database to new schema
user at hippo:~/mytti$

Schema upgrade fails. Host alpo (see above "duplicate entry line") is my FE in my living room.

Now is there a simple way to clean/fix up this schema issue? PhpAdmin?
There must be many other duplicates now.. 

So a solution that can be run from CLI would be apprciated...

Are A) and B) related issues?

I'm faily novice with mysql...

I saw this kind of similar topic in the list:
http://www.gossamer-threads.com/lists/mythtv/users/349161

Any help would be appreciated.

thanks