Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#10636 closed Bug Report - General (Works for me)

upgrade from 11.04 to 12.04 - Mythweb recorded programs broken

Reported by: camski@… Owned by: sphery
Priority: minor Milestone: unknown
Component: MythTV - General Version: Unspecified
Severity: medium Keywords:
Cc: Ticket locked: no

Description

!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.

Attachments (2)

Mythweb - Mysql error - recorded programs.txt (9.7 KB) - added by Raymond Wagner 7 years ago.
Desc recordedmarkup.png (30.2 KB) - added by camski@… 7 years ago.
PNG of DESC recordedmarkup

Download all attachments as: .zip

Change History (8)

Changed 7 years ago by Raymond Wagner

comment:1 Changed 7 years ago by Raymond Wagner

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

DESC recordedmarkup;

Changed 7 years ago by camski@…

Attachment: Desc recordedmarkup.png added

PNG of DESC recordedmarkup

comment:2 Changed 7 years ago by 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),
Last edited 7 years ago by Raymond Wagner (previous) (diff)

comment:3 Changed 7 years ago by Raymond Wagner

Component: Plugin - MythWebMythTV - General
Owner: changed from Rob Smith to sphery
Status: newassigned

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?

comment:4 Changed 7 years ago by Raymond Wagner

Status: assignedinfoneeded
Version: 0.25-fixesUnspecified

comment:5 Changed 7 years ago by sphery

Resolution: Works for me
Status: infoneededclosed

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.

comment:6 Changed 7 years ago by 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.

Note: See TracTickets for help on using tickets.