Opened 18 years ago
Closed 18 years ago
Last modified 18 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 18 years ago by
comment:2 Changed 18 years ago by
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 18 years ago by
Owner: | changed from Isaac Richards to bjm |
---|
comment:5 Changed 18 years ago by
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 18 years ago by
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 18 years ago by
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 18 years ago by
Resolution: | → fixed |
---|---|
Status: | new → closed |
comment:9 Changed 18 years ago by
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))
I also noticed this at revision 8255