LinHES Forums
http://forum.linhes.org/

duplicate entry for key 1 SOLVED!
http://forum.linhes.org/viewtopic.php?f=6&t=18734		 Page 1 of 1
Author:  cahlfors [ Sun Aug 10, 2008 12:19 pm ]
Post subject:  duplicate entry for key 1 SOLVED!
I'm seeing the following in the backend log:
Code:
2008-08-10 19:59:31.530 Started recording: Sport Disasters "Del 1 s�song 3": channel 1006 on cardid 13, sourceid 1
2008-08-10 19:59:32.680 DB Error (Error in JobQueue::StartJob()):
Query was:
INSERT INTO jobqueue (chanid, starttime, inserttime, type, status, statustime, hostname, args, comment, flags) VALUES ('1006', '2008-08-10T20:00:00', now(), 2, 1, now(), '', '', '', 2);
Driver error was [2/1062]:
QMYSQL3: Unable to execute query
Database error was:
Duplicate entry '2147483647' for key 1

It seems that this has very much to do with the fact that commercial flagging no longer works - the jobs don't show up in the queue.

What can I do to correct the situation and get commflagging working again? I've tried the DB repair procedure, but there aren't any errors.

Thanks,
/Chris
Author:  tjc [ Sun Aug 10, 2008 7:54 pm ]
Post subject: 
For starters you can try purging your jobqueue table....
Author:  cahlfors [ Fri Oct 10, 2008 10:09 am ]
Post subject: 
Took me a while to figure out how to do that. Elsewhere you have suggested using WebMin and that sure is powerful!
It looks like I have a missing index for the jobqueue table. All additional records will try to get written with the same primary key, hence the error (duplicate key). Removing the record (purging the table) makes commflagging start working again - once only.

Should I just move ahead and do my upgrade to R5.5 and hope that fixes the index problem or do I need to do something first?

Thanks
/Chris
Author:  tjc [ Fri Oct 10, 2008 7:08 pm ]
Post subject: 
Just to be safe I'd probably drop that table and recreate it using the SQL commands from the/usr/local/share/knoppmyth/KnoppMyth.sql file.

NOTE! Do _not_ run that whole thing as is!

You'll want to extract just the bits related to the jobqueue table.
Code:
DROP TABLE IF EXISTS `jobqueue`;
CREATE TABLE `jobqueue` (
  `id` int(11) NOT NULL auto_increment,
  `chanid` int(10) NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `inserttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `type` int(11) NOT NULL default '0',
  `cmds` int(11) NOT NULL default '0',
  `flags` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `statustime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `hostname` varchar(255) NOT NULL default '',
  `args` blob NOT NULL,
  `comment` varchar(128) NOT NULL default '',
  `schedruntime` datetime NOT NULL default '2007-01-01 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `chanid` (`chanid`,`starttime`,`type`,`inserttime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and pipe that into mysql.
Author:  cahlfors [ Sat Oct 11, 2008 12:42 am ]
Post subject:  How to recreate the jobqueue table
It worked like a snap - thanks!

First I thought that the KnoppMyth.sql must be something that executes during installation, so why not just do the upgrade - that should take care of it. Then I realized that if it's part of the installation, I might as well do it now. If I ruin something, it will be recreated in the upgrade anyway.

To document the procedure in case someone else has a similar problem:
As root:
I copied the /usr/local/share/knoppmyth/KnoppMyth.sql into /usr/local/share/knoppmyth/jobqueue.sql, then edited the jobqueue.sql file to strip it from everything except the part you quoted above. Since I am still on R5F27, it's somewhat different.
Code:
-- Table structure for table `jobqueue`
--

DROP TABLE IF EXISTS `jobqueue`;
CREATE TABLE `jobqueue` (
  `id` int(11) NOT NULL auto_increment,
  `chanid` int(10) NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `inserttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `type` int(11) NOT NULL default '0',
  `cmds` int(11) NOT NULL default '0',
  `flags` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `statustime` timestamp(14) NOT NULL,
  `hostname` varchar(255) NOT NULL default '',
  `args` blob NOT NULL,
  `comment` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `chanid` (`chanid`,`starttime`,`type`,`inserttime`)
) TYPE=MyISAM;

--
-- Dumping data for table `jobqueue`
--

Then, still as root:
Code:
mysql -u root mythconverg < jobqueue.sql

The command took less than a second and after that I could just start queuing up the commflagging jobs. :D

Cheers,
/Chris
Author:  tjc [ Sat Oct 11, 2008 10:40 am ]
Post subject:  Re: How to recreate the jobqueue table
cahlfors wrote:
First I thought that the KnoppMyth.sql must be something that executes during installation, so why not just do the upgrade - that should take care of it. Then I realized that if it's part of the installation, I might as well do it now. If I ruin something, it will be recreated in the upgrade anyway.

Well, it's a bit more complicated than that because of how the DB restore works, which is why I recommended doing this before you started the upgrade. During an upgrade the following things happen:

    1) An empty db is created (this is what is used by an install).
    2) The db part of the restore drops and recreates the tables and loads the data from your backup.
    3) When the MythTV app (usually the backend) comes up it realizes that the DB schema is "old", upgrades the schema, and inserts default values for any new settings.

Between step 2 and 3 is where I was anticipating trouble. If you restore a table that is in a funky state, the upgrade done by MythTV can have problems. This happened to a number of folks who had their DB partially upgraded by running a newer version of the frontend against it, and then manually repaired.
Page 1 of 1 All times are UTC - 6 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/