[PATCH] Fix upgrade from an empty database

[Lawrence Rust <lvr@…>]

mythtv-setup fails when starting with an empty database, failing during the wizard upgrade step from the intiak database schema 1226.

2011-08-10 15:56:56.192100 C Newest MythTV Schema Version : 1280 2011-08-10 15:56:56.197098 N Inserting MythTV initial database information. 2011-08-10 15:56:56.197158 C Upgrading to MythTV schema version 1226 2011-08-10 15:56:56.197777 E DB Error (Performing database upgrade): Query was: ALTER DATABASE mythmaster DEFAULT CHARACTER SET utf8; Error was: Driver error was [2/1192]: QMYSQL: Unable to execute query Database error was: Can't execute the given command because you have active locked tables or an active transaction new version: 1226 2011-08-10 15:56:56.197802 E Database Schema upgrade FAILED, unlocking.

Steps to reproduce - Run this script:

#!/bin/sh -e
: ${MYTHCONFDIR:="$PWD/mythconf-test"}
: ${MYTHTVDIR:="$PWD/mythinstall"}
export MYTHCONFDIR
export MYTHTVDIR
hostname="server"
database="test"
admin_username="root"
admin_password="password"
mythtv_username="mythtv"
mythtv_password="mythtv"

newdb() {
    local db=$1 user=$2 pass=$3
    local SECURITY_INFO="--user='$admin_username'\
      ${admin_password:+-p$admin_password}"
    mysql --host=$hostname $SECURITY_INFO <<END
DROP DATABASE IF EXISTS ${db};
CREATE DATABASE IF NOT EXISTS ${db};
GRANT ALL ON ${db}.* TO ${user}@"%" IDENTIFIED BY "${pass}";
FLUSH PRIVILEGES;
END
}

echo "WARNING: About to re-initialise MythTV database $database"
read -p "Presss [Return] to continue or {Control-C] to abort" temp
newdb $database $mythtv_username $mythtv_password

rm -rf "$MYTHCONFDIR"
mkdir -p "$MYTHCONFDIR"
cat > "$MYTHCONFDIR/mysql.txt" <<END
DBHostName=${hostname}
DBUserName=${mythtv_username}
DBPassword=${mythtv_password}
DBName=${database}
DBType=QMYSQL3
END

$MYTHTVDIR/bin/mythtv-setup $@

There are 2 problems:

1. MythDB::GetSetting can't read the dbschemavar during initialisation because HaveValidDatabase returns false.

2. After calling DBUtil::lockSchema mysql requests fail during prepare with the error that LOCK TABLES wasn't called.

This fix allows the dbschemavar to be read by MythDB::GetSetting even if HaveValidDatabase returns false and disables "LOCK TABLE schemalock WRITE;" I don't (and hope never will) have the mysql knowledge to fix the table locking 'properly' so this is an expedient fix.

[stuartm]

Fixed by [8e783b75bf4] last night?

[Lawrence Rust <lvr@…>]

No, it isn't fixed:

2011-08-11 13:11:35.626483 I  No current database version?
2011-08-11 13:11:35.644966 C  MythTV database schema is old. Waiting to see if DB is being upgraded.
2011-08-11 13:11:36.653640 I  No current database version?
2011-08-11 13:11:36.653703 I  Database connection created: DBManager2
2011-08-11 13:11:36.653735 I  New DB connection, total: 2
2011-08-11 13:11:36.657363 I  Connected to database 'test' at host: mac
2011-08-11 13:11:36.677379 I  Database connection created: DBManager3
2011-08-11 13:11:36.677416 I  New DB connection, total: 3
2011-08-11 13:11:36.680978 I  Connected to database 'test' at host: mac
2011-08-11 13:11:37.735033 I  Current MythTV Schema Version (DBSchemaVer): 1226
2011-08-11 13:11:38.745884 I  Current MythTV Schema Version (DBSchemaVer): 1226
2011-08-11 13:11:39.756360 I  Current MythTV Schema Version (DBSchemaVer): 1226
2011-08-11 13:11:40.766854 I  Current MythTV Schema Version (DBSchemaVer): 1226
2011-08-11 13:11:40.767228 C  Timed out waiting.
2011-08-11 13:11:40.811345 E  SG(DB Backups): Unable to find any Storage Group Directories.  Using hardcoded default value of '/mnt/store'
2011-08-11 13:11:40.811404 E  SG(DB Backups): FindNextDirMostFree: '/mnt/store' does not exist!
2011-08-11 13:11:40.818387 E  Backing up database with script: '/home/lvr/Projects/myth/mythinstall/share/mythtv/mythconverg_backup.pl'
2011-08-11 13:11:41.997263 C  Database Backup complete.
2011-08-11 13:11:41.997574 C  Backed up database to file: '/tmp/test-1226-20110811131140.sql.gz'
2011-08-11 13:11:44.540146 C  Newest MythTV Schema Version : 1280
2011-08-11 13:11:44.552310 E  Unrecognized database schema version. Unable to upgrade database.
2011-08-11 13:11:44.552316 E  Database Schema upgrade FAILED, unlocking.
2011-08-11 13:11:44.552929 E  Couldn't upgrade database to new schema.

I believe that commit 8e783b75bf4 might have fixed the lockSchema issue but doesn't fix problem #1 (MythDB::GetSetting)

[Lawrence Rust <lvr@…>]

Having tried applying the patch just for point #1 the upgrade still fails so both elements of this patch are still required.

[Github]

Fixes #9983. Another location that needs a dedicated db connection. See [8e783b75bf4].

Branch: master Changeset: d986a922a5883b8b9ebb6e6415e385a3fcb99324