[mythtv-users] database upgrade error in upgrade from mythtv .15.to .16

[John Salch]

I had the same problem when I upgraded and then tried to start the 
front-end.  It appears the patch program is trying to insert a column on a 
table that already has the column in it.  You might try restarting the front 
end one more time to see if the problem occurs again.  If it does, you might 
want to follow what I did to fix it:

Here is what I did:

Saved all my data from the myth database using the following command:

mysqldump -u mythtv -pmythtv mythconverg -c > mythtv_backup.sql

Logged into mysql and removed the column from the database table:

Log in to mysql:
mysql -u root -p mythconverg

Remove the column:

ALTER TABLE recorded DROP COLUMN lastmodified;

Started up the front end to make sure it worked.  Then checked and couldn't 
see any recorded programs... this is because the column is missing from the 
table.

So, next truncate the recorded programs table from within mysql:

truncate table recorded;

or:

delete from recorded;

I can't remember which one worked.  After you clear the recorded programs 
you can verify by:

select count(*) from recorded;

That command should return back '0 records', meaning the table is clean.

Next you need to add back the column, using the command the patch was trying 
to run:

ALTER TABLE recorded ADD COLUMN lastmodified TIMESTAMP NOT NULL;

Then exit out of mysql.

Next you need to get out of the database dump file the records for your 
recorded programs, you can do that with something like the following:

grep recorded mythtv_backup.sql > fixrecorded.sql;

This will create the 'fixrecorded.sql' file that only has commands to 
replace your recorded table in mysql.  You mya need to edit this file as the 
CREATE TABLE statement might be at the top of it.  You don't want that line 
there, only the INSERT statements.

Once you have the 'fixrecorded.sql' file ready, execute the following 
command:

mysql -u root -p mythconverg < fixrecorded.sql

Next, you can start teh front end and it should work OK.

Cheers.

----- Original Message ----- 
From: "Jeff Johann" <jjohann at insightbb.com>
To: <mythtv-users at mythtv.org>
Sent: Monday, September 20, 2004 1:31 AM
Subject: [mythtv-users] database upgrade error in upgrade from mythtv .15.to 
.16


>I did an upgrade from a working mythtv .15 to .16 using the .atrpms
> repository and got the following error.  Any suggestions would be
> appreciated.
>
> jeff
>
>
> # mythbackend -v
> Session management error: Authentication Rejected, reason : None of the
> authentication protocols specified are supported and host-based
> authentication failed
> 2004-09-20 01:09:42 Upgrading to schema version 1051
> DB Error (Performing database upgrade):
> Query was:
> ALTER TABLE recorded ADD COLUMN lastmodified TIMESTAMP NOT NULL;
> Driver error was [2/-1]:
> QMYSQL3: Unable to execute query
> Database error was:
> Duplicate column name 'lastmodified'
>
>
>
>


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


_______________________________________________
mythtv-users mailing list
mythtv-users at mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users