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

[Greg Grotsky]

On Mon, Apr 14, 2008 at 2:08 AM, Nick Morrott <knowledgejunkie at gmail.com>
wrote:

> 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
>
>
Thanks Nick, sorry about the delay.  I tried the method you outline and I
get the following message the first time I try to load mythfrontend:

2008-04-15 06:22:15.710 Upgrading to MythMusic schema version 1006
2008-04-15 06:22:15.729 DB Error (Performing database upgrade):
Query was: RENAME TABLE smartplaylist TO music_smartplaylists;
Error was: Driver error was [2/1017]:
QMYSQL: Unable to execute query
Database error was:
Can't find file: './mythconverg/smartplaylist.frm' (errno: 2)

new version: 1006
2008-04-15 06:22:15.729 Couldn't upgrade database to new schema, exiting.
2008-04-15 06:22:15.730 Unable to initialize plugin 'mythmusic'.

And on subsequent loads I see this:

2008-04-15 06:23:03.749 Upgrading to MythMusic schema version 1006
2008-04-15 06:23:03.750 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-15 06:23:03.750 Couldn't upgrade database to new schema, exiting.
2008-04-15 06:23:03.750 Unable to initialize plugin 'mythmusic'.


-Greg
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20080415/e90a3508/attachment.htm