[mythtv-users] How do I completely reset mythmusic database structure?

[Nick Morrott]

On 14/04/2008, Greg Grotsky <spikeygg at gmail.com> wrote:
> So I upgraded to SVN 16946 a couple weeks ago and ever since my frontend has
> been complaining about:
>
> 2008-04-13 21:45:18.339 Upgrading to MythMusic schema version 1006
> 2008-04-13 21:45:18.340 DB Error (Performing database upgrade):
>  Query was: CREATE TABLE music_albums (    album_id int(11) unsigned NOT
> NULL auto_increment PRIMARY KEY,    artist_id int(11) unsigned NOT NULL
> default '0',    album_name varchar(255) NOT NULL default '',    year
> smallint(6) NOT NULL default '0',    compilation tinyint(1) unsigned NOT
> NULL default '0',    INDEX idx_album_name(album_name));
>  Error was: Driver error was [2/1050]:
> QMYSQL: Unable to execute query
> Database error was:
> Table 'music_albums' already exists
>
> new version: 1006
> 2008-04-13 21:45:18.341 Couldn't upgrade database to new schema, exiting.
>  2008-04-13 21:45:18.341 Unable to initialize plugin 'mythmusic'.
>
>
> I've gone into the database and tried to randomly remove the tables and
> columns it complains about but I always end up getting into some endless
> loop and eventually restoring the whole mythmusic database structure (which
> is broken).  My mythmusic used to work but now it won't even come up!  How
> do I drop all the mythmusic stuff so that it can rebuild itself and be happy
> again?

Looking at the mythmusic DB tables listing
(http://svn.mythtv.org/trac/browser/trunk/mythplugins/mythmusic/mythmusic/dbcheck.cpp)
you could try this bruteforce method (although it might not work - so
take backup of the DB beforehand).

Close mythfrontend, shutdown mythbackend and edit the mythconverg DB:

mysql> UPDATE `settings` SET 'MusicDBSchemaVer' = '';

mysql> DROP TABLE IF EXISTS musicmetadata;
mysql> DROP TABLE IF EXISTS musicplaylist;
mysql> DROP TABLE IF EXISTS music_albumart;
mysql> DROP TABLE IF EXISTS music_albums;
mysql> DROP TABLE IF EXISTS music_artists;
mysql> DROP TABLE IF EXISTS music_directories;
mysql> DROP TABLE IF EXISTS music_genres;
mysql> DROP TABLE IF EXISTS music_playlists;
mysql> DROP TABLE IF EXISTS music_smartplaylists;
mysql> DROP TABLE IF EXISTS music_smartplaylist_categories;
mysql> DROP TABLE IF EXISTS music_smartplaylist_items;
mysql> DROP TABLE IF EXISTS music_songs;
mysql> DROP TABLE IF EXISTS music_stats;
mysql> DROP TABLE IF EXISTS smartplaylistcategory;
mysql> DROP TABLE IF EXISTS smartplaylist;
mysql> DROP TABLE IF EXISTS smartplaylistitem;


This should clear out all mythmusic-related tables and reset the music
DB version so that a future mythmusic DB update run should start from
the very beginning, recreating all required tables in order.

Let me know how it goes,
Nick

-- 
Nick Morrott

MythTV Official wiki:
http://mythtv.org/wiki/
MythTV users list archive:
http://www.gossamer-threads.com/lists/mythtv/users

"An investment in knowledge always pays the best interest." - Benjamin Franklin