upgrade from 11.04 to 12.04 - Mythweb recorded programs broken

[camski@…]

!NoTrans: SQL Error: Unknown column 'recordedmarkup.data' in 'field list' #1054!!

Created a new PC with 12.04, backup database from working 11.04 (1264) and hit this problem. Everything working except Mythweb recorded programs, can play via frontend and new recordings working fine. Backend has updated schema to 1299.

Upgraded old working system with ubuntu 11.04 with myth 0.24 to 0.25 and the same problem. Just Recorded programs shows error #1054 on both systems.

Have done database checks and repairs, tried multiple backup databases and looked at mysql and cannot find the issue.

[Raymond Wagner]

If you access the database through a mysql client, does your recordedmarkup table actually not have a 'data' field?

DESC recordedmarkup;

[camski@…]

PNG of DESC recordedmarkup

[camski@…]

Below is the output from DESC recordedmarkup, PNG attachment too and also the beginning of phpMyAdmin SQL Dump

mysql> DESC recordedmarkup;
+-----------+-----------------------+------+-----+---------------------+-------+
| Field     | Type                  | Null | Key | Default             | Extra |
+-----------+-----------------------+------+-----+---------------------+-------+
| chanid    | int(10) unsigned      | NO   | PRI | 0                   |       |
| starttime | datetime              | NO   | PRI | 0000-00-00 00:00:00 |       |
| mark      | mediumint(8) unsigned | NO   | PRI | 0                   |       |
| offset    | varchar(32)           | YES  |     | NULL                |       |
| type      | tinyint(4)            | NO   | PRI | 0                   |       |
+-----------+-----------------------+------+-----+---------------------+-------+
5 rows in set (0.00 sec)

------------------------------------------------------------------

 -- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 22, 2012 at 08:06 PM
-- Server version: 5.5.22
-- PHP Version: 5.3.10-1ubuntu3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `mythconverg`
--

-- --------------------------------------------------------

--
-- Table structure for table `recordedmarkup`
--

CREATE TABLE IF NOT EXISTS `recordedmarkup` (
  `chanid` int(10) unsigned NOT NULL DEFAULT '0',
  `starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mark` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `offset` varchar(32) DEFAULT NULL,
  `type` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`chanid`,`starttime`,`type`,`mark`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `recordedmarkup`
--

INSERT INTO `recordedmarkup` (`chanid`, `starttime`, `mark`, `offset`, `type`) VALUES
(1007, '2010-10-03 23:20:00', 0, NULL, -3),
(1003, '2010-10-30 17:25:00', 0, NULL, -3),
(1003, '2010-10-16 17:25:00', 0, NULL, -3),
(1003, '2010-10-16 17:25:00', 0, NULL, 4),
(1003, '2010-10-16 17:25:00', 8182, NULL, 5),
(1003, '2010-10-16 19:25:00', 0, NULL, -3),
(1003, '2010-10-16 19:25:00', 98985, NULL, 4),
(1003, '2010-10-16 19:25:00', 104869, NULL, 5),
(1003, '2010-10-16 21:15:00', 0, NULL, -3),
(1003, '2010-10-21 19:25:00', 0, NULL, 4),
(1030, '2011-10-20 19:55:00', 78656, NULL, 5),
(1030, '2011-10-20 19:55:00', 74149, NULL, 4),

[Raymond Wagner]

It appears your schema is broken, and you missed an update years back, during the transition from 0.21 to 0.22.

​http://code.mythtv.org/trac/browser/mythtv/mythtv/libs/libmythtv/dbcheck.cpp?rev=e12af866cda3e65a7cc556aec8e18d92015023d5#L4463

Have you ever attempted to manually walk the schema forward? Perhaps to work around a failed update?

[sphery]

Yes, there doesn't seem to be any possible way that your database could have been upgraded through proper procedures and not gotten a recordedmarkup.data column. I'm guessing there were issues with your database caused by character encoding in the pre-0.22 days, so you had problems upgrading the schema and some corruption occurred during the process.

I recommend doing a partial database restore ( http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup ) since you have a corrupt schema. This will throw away most of your data (keeping only the not-re-creatable data) and ensure you have a correct schema. Since your recordedmarkup table structure is corrupt, you should probably also run mythcommflag on all your recordings after re-configuring everything. If you have any questions about doing the partial restore, please post questions to the mythtv-users mailing list.

[camski@…]

Thanks for the advice, tried a few partial restores that were not successful so I manually added data to the SQL table, all is working fine at the minute so hopefully this will not come back and bite me later. Thanks again.