Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#805 closed defect (fixed)

DB error (performing database upgrade) 1119 to 1120

Reported by: anonymous Owned by: bjm
Priority: minor Milestone: unknown
Component: mythtv Version:
Severity: medium Keywords:
Cc: Ticket locked: no

Description

Error upgrading schema from 1119 to 1120 after ADD COLUMN jump to playgroup

2005-12-13 16:02:26.247 Current Schema Version: 1119 2005-12-13 16:02:26.248 Newest Schema Version : 1120 2005-12-13 16:02:26.248 Setting Lock for Database Schema upgrade. If you see a long pause here it means the Schema is already locked and is being upgraded by another Myth process. 2005-12-13 16:02:26.249 New DB connection, total: 2 2005-12-13 16:02:26.250 Upgrading to schema version 1120 2005-12-13 16:02:26.251 DB Error (Performing database upgrade): Query was: ALTER TABLE playgroup ADD COLUMN jump INT NOT NULL DEFAULT 0; Error was: Driver error was [2/1060]: QMYSQL3: Unable to execute query Database error was: Duplicate column name 'jump'

new version: 1120 2005-12-13 16:02:26.251 Database Schema upgrade FAILED, unlocking. 2005-12-13 16:02:26.251 Couldn't upgrade database to new schema, exiting.

Change History (10)

comment:1 Changed 14 years ago by anonymous

I also noticed this at revision 8255

comment:2 Changed 14 years ago by bjm <bjm@…>

Unless you had added a column "jump" to your playgroup already, this must have run twice. The first failure would be significant.

$ mysql -u mythtv -pmythtv mythconverg
mysql> ALTER TABLE playgroup DROP COLUMN jump;

$ mythbackend -v database > /tmp/dbcheck.out

It may work this time but if it fails, attach the dbcheck.out file and the output from "mysqld --version".

comment:3 Changed 14 years ago by bjm <bjm@…>

Owner: changed from Isaac Richards to bjm

comment:4 Changed 14 years ago by anonymous

dupe of #705?

comment:5 Changed 14 years ago by a.petersen AT telstra.com

I saw the same issue, initial error from the backend log follows:

MySQL version is: mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)

2005-12-14 21:47:59.947 Upgrading to schema version 1120
2005-12-14 21:47:59.961 MSqlQuery: ALTER TABLE playgroup ADD COLUMN jump INT NOT NULL DEFAULT 0;
2005-12-14 21:47:59.962 MSqlQuery: UPDATE playgroup SET jump = (SELECT data FROM settings  WHERE value = 'JumpAmount' GROUP BY value) WHERE name = 'Default';
2005-12-14 21:47:59.963 DB Error (Performing database upgrade):
Query was: UPDATE playgroup SET jump = (SELECT data FROM settings  WHERE value = 'JumpAmount' GROUP BY value) WHERE name = 'Default';
Error was: Driver error was [2/1064]:
QMYSQL3: Unable to execute query
Database error was:
You have an error in your SQL syntax near 'SELECT data FROM settings  WHERE value = 'JumpAmount' GROUP BY value) WHERE name' at line 1

comment:6 Changed 14 years ago by danielk

I can confirm that the error seen by a.petersen is the error on the first run, and the error reported by anonymous is the error on subsequent runs. This is with mysql 4.0.24.

comment:7 Changed 14 years ago by tom@…

I had the exact same problem as a.peterson:

2005-12-14 11:45:43.970 Current Schema Version: 1119
2005-12-14 11:45:44.058 Newest Schema Version : 1120
2005-12-14 11:45:44.230 Setting Lock for Database Schema upgrade. If you see a long pause here it means the Schema is alread
y locked and is being upgraded by another Myth process.
2005-12-14 11:45:44.324 New DB connection, total: 2
2005-12-14 11:45:44.427 Upgrading to schema version 1120
2005-12-14 11:47:21.058 DB Error (Performing database upgrade):
Query was: UPDATE playgroup SET jump = (SELECT data FROM settings  WHERE value = 'JumpAmount' GROUP BY value) WHERE name = '
Default';
Error was: Driver error was [2/1064]:
QMYSQL3: Unable to execute query
Database error was:
You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax t
o use near 'SELECT data FROM settings  WHERE value = 'JumpAmount' GROUP BY

new version: 1120
2005-12-14 11:47:23.875 Database Schema upgrade FAILED, unlocking.
2005-12-14 11:47:25.282 Couldn't upgrade database to new schema

It added the 'jump' column to playgroup, but it doesn't appear to be able to execute the update command correctly. I manually updated playgroup, and manually bumped the DBSchemaVer to 1120 and now my backend actually starts.

Tom

PS Mysql version:

mysqld Ver 4.0.22-log for pc-linux on i386 (Source distribution)

comment:8 Changed 14 years ago by bjm

Resolution: fixed
Status: newclosed

(In [8260]) Disable the Default playgroup initialization. Closes #805

Comment out the subqueries that don't work all versions of mysql.

comment:9 Changed 14 years ago by mythdev@…

I think some mysql versions don't support update ... select ? I'm not sure what the group by was trying to do - the subselect would have returned multiple values if they existed anyway?

Following is one approach - that doesn't try to do anything that the group by might have done.

Index: mythtv/libs/libmythtv/dbcheck.cpp
===================================================================
--- mythtv/libs/libmythtv/dbcheck.cpp   (revision 8257)
+++ mythtv/libs/libmythtv/dbcheck.cpp   (working copy)
@@ -1957,12 +1957,12 @@
     {
         const QString updates[] = {
 "ALTER TABLE playgroup ADD COLUMN jump INT NOT NULL DEFAULT 0;",
-"UPDATE playgroup SET jump = (SELECT data FROM settings"
-"  WHERE value = 'JumpAmount' GROUP BY value) WHERE name = 'Default';",
-"UPDATE playgroup SET skipahead = (SELECT data FROM settings"
-"  WHERE value = 'FastForwardAmount' GROUP BY value) WHERE name = 'Default';",
-"UPDATE playgroup SET skipback = (SELECT data FROM settings"
-"  WHERE value = 'RewindAmount' GROUP BY value) WHERE name = 'Default';",
+"UPDATE playgroup, settings set playgroup.jump = settings.data"
+" WHERE settings.value = 'JumpAmount' AND playgroup.name = 'Default';",
+"UPDATE playgroup, settings set playgroup.skipahead = settings.data"
+" WHERE settings.value = 'FastForwardAmount' AND playgroup.name = 'Default';",
+"UPDATE playgroup, settings set playgroup.skipback = settings.data"
+" WHERE settings.value = 'RewindAmount' AND playgroup.name = 'Default';",
 ""
 };
         if (!performActualUpdate(updates, "1120", dbver))
  

comment:10 Changed 14 years ago by bjm

(In [8264]) Update playgroup Default without using subqueries as suggested by mythdev at penyball.cix.co.uk .

References #805

Note: See TracTickets for help on using tickets.