[mythtv-users] Are these SQL errors something to worry about?
Jeffrey J. Kosowsky
mythtv at kosowsky.org
Tue Jan 12 18:02:55 UTC 2010
David Liana wrote at about 09:31:40 -0500 on Tuesday, January 12, 2010:
> On Tue, Jan 12, 2010 at 5:24 AM, Jeffrey J. Kosowsky <mythtv at kosowsky.org>wrote:
> > I seem to get the following types of errors when watching LiveTV or
> > recording shows:
> > 2010-01-12 04:55:39.561 DB Error (aspect ratio change insert):
> > Query was:
> > INSERT INTO recordedmarkup (chanid, starttime, mark, type, data) VALUES (
> > ?, ?, ?, ?, ?);
> > Bindings were:
> > :CHANID=2071, :DATA=0, :MARK=15, :STARTTIME=2010-01-12T04:55:38, :TYPE=12
> > No error type from QSqlError? Strange...
> > 2010-01-12 04:55:39.595 Error preparing query: INSERT INTO recordedmarkup
> > (chanid, starttime, mark, type, data) VALUES ( :CHANID, :STARTTIME,
> > :MARK, :TYPE, :DATA);
> > 2010-01-12 04:55:39.619 Driver error was [2/1054]:
> > QMYSQL3: Unable to prepare statement
> > Database error was:
> > Unknown column 'data' in 'field list'
> > Originally, I feared that maybe they were caused by my recent editing
> > of the sql database to copy over frontend profiles or maybe due to
> > incompatibilities between revisions on my Linux vs. Windows
> > frontends. But looking back in my logs, the error *precede* either of
> > those situations.
> > But they *all* seem to have occurred since upgrading from 0.21 to
> > 0.22. It makes me wonder whether the database was updated properly.
> > Looking back, I may have done the following:
> > - Sourced in new blank database:
> > mysql -u root -p < /usr/share/doc/mythtv-docs-0.22/database/mc.sql
> > - Ran mythtv-setup and mythfrontend on that blank database to
> > customize my setup
> > - Sourced in my program data tables (record, recorded, oldrecorded,
> > recordedprogram, recordedrating, recordedmarkup, recordedseek) from
> > my latest dump of the old Version 0.21
> > mysql -u mythtv -pmythtv mythconverg < myth-0.21-programdata.sql
> > Having learned a little sql over the past few days, I now realize that
> > the program-related tables may not have been compatible and that
> > sourcing the old dump first erased the 0.22 version of the program
> > data tables and then replaced it with the 0.21 version of the tables.
> > - Is this likely to be the source of my problems?
> > - Whether or not that was the source of the problem, what can I do to
> > fix things now?
> > - Will this lead to corruption, data loss, or issues down the road?
> > - Are there any other changed/added/deleted fields in the above tables
> > in moving from 0.21 to 0.22 that I need to fix?
> I think I got these errors when I dumped my 0.21 database and (improperly)
> loaded it over a 0.22 database. But, that was back in November.
> Did you try doing a proper dump/restore with the correct versions?
For future reference, I was able to fix it all pretty painlessly by
doing the following:
0. Stop mythbackend
1. Fully backup the current mixed/corrupted 0.21-0.22 mythconverg
database [ call it: mixed.full.sql]
2. Use mysqldump to selectively dump just the non-upgraded (i.e. old
schema) tables from my current mixed/corrupted 0.21-0.22
mythconverg database [call it: ver21.partial.sql]
[Note these table are non-corrupted in the ver21 context.]
3. Delete current mixed/corrupted mythconverg database and create a
blank new mythconverg database
4. Source a full old dump of the old 0.21 version (do not run myth yet)
into the blank mythconverg database
5. Over that source the ver21.partial.sql database
You now have a consistent ver21 database with the current v21
tables taken from your latest mixed/corrupted version
6. Run mythtv-setup or mythbackend to upgrade the schema
7. Use mysqldump to selectively dump the now upgraded tables that you
had selectively dumped before from the mixed/corrupted version.
[call it: ver22.partial.sql]
8. Restore your mixed/corrupted database (mixed.full.sql)
9. Source back in ver22.partial.sql on top of it
10. Restart mythbackend
Now everything is version 22 and works.
Note the above should apply to fix any mixed schema corruption where
some tables are in one schema and other tables are in another schema.
More information about the mythtv-users