[mythtv-users] videometadata restore error and schema

[Brad DerManouelian]

On Jan 28, 2009, at 10:11 AM, Captain Krypto wrote:

> Hi all,
>
> I am a dumb dumb and accidently dropped my videometadata table from  
> mythconverg.
>
> I was trying to restore it from a recent backup and I get the  
> following error when running the command:
>
> mysql -u mythtv -pmythtv mythconverg < videometadata.sql
>
> ERROR 1064 (42000) at line 3: You have an error in your SQL syntax;  
> check the manual that corresponds to your MySQL server version for  
> the right syntax to use near 'LOCK TABLES `videometadata` WRITE' at  
> line 3
>
> With the code in question being:
>
> -- Table structure for table `videometadata`
> DROP TABLE IF EXISTS `videometadata`;
> CREATE TABLE `videometadata` (
> -- Dumping data for table `videometadata`
> LOCK TABLES `videometadata` WRITE;
> /*!40000 ALTER TABLE `videometadata` DISABLE KEYS */;
> INSERT INTO `videometadata` (`intid`, [...]
>
> Does anyone know what the issue with that is?

Yeah, you're completely missing the table definition. Mine looks like  
this (but don't go by it because I'm running trunk and might have a  
different schema for this table):
CREATE TABLE `videometadata` (
   `intid` int(10) unsigned NOT NULL auto_increment,
   `title` varchar(128) NOT NULL,
   `director` varchar(128) NOT NULL,
   `plot` text,
   `rating` varchar(128) NOT NULL,
   `inetref` varchar(255) NOT NULL,
   `year` int(10) unsigned NOT NULL,
   `userrating` float NOT NULL,
   `length` int(10) unsigned NOT NULL,
   `showlevel` int(10) unsigned NOT NULL,
   `filename` text NOT NULL,
   `coverfile` text NOT NULL,
   `childid` int(11) NOT NULL default '-1',
   `browse` tinyint(1) NOT NULL default '1',
   `playcommand` varchar(255) default NULL,
   `category` int(10) unsigned NOT NULL default '0',
   `trailer` text,
   PRIMARY KEY  (`intid`),
   KEY `director` (`director`),
   KEY `title` (`title`),
   KEY `title_2` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=272 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `videometadata`
--

LOCK TABLES `videometadata` WRITE;
...

<snip commands used>

> Does anyone have the videometadata schema handy?

Do you have another backup or are you just keeping one?
The wiki has the schema listed, but I would check to see when it was  
updated to see if it's correct for your version.
http://www.mythtv.org/wiki/Videometadata_table

In fact, you can get all the database schema information from here:
http://www.mythtv.org/wiki/Database_Schema

-Brad