Help with Database Import (Incorrect Databse Schema Error)

[mattlach]

Hey all,

So I finally got around to upgrading from .27 on 14.04 today on my dedicated master backend today

I did a fresh database backup before starting, then did a clean install of 18.04 LTS Server Edition, installing LXDE as my desktop.

I added the PPA for MythTV 30, and, once I got all my mounts and networks set up went to restore the database.

Everything SEEMED to work well, but now I am stuck.

If I try to launch the MythTV Backend Setup app on the backend it goes straight to asking if I'd like to start the MythTV backend. If I say yes, it then asks if I want to run a mythfilldatabase.

Here I ran into my first issue. It asked for host and logon information for the database. I provided the host (localhost) database (mythconverg) username (M<ythtv) and new password from /etc/mythtv/config.xml, and everything seemed to work, but then I get the following error message:

Code: Select all

I Current MythTV Schema Version (DBSchemaVer): 1317
W Not Allowed to upgrade the database.
E Incorrect Database Schema

Does anyone know what is going on? I was under the impression I could upgrade straight from .27 to 30?

Any help greatly appreciated.

--Matt

[mattlach]

Some more information about how I got here, if it is relevant.

After the clean install when I went to import the database it told me it could not import the database because it was not empty.

Then I retried the database import with the --drop_database --create_database switches. This appeared to complete successfully, but now I have this issue.

[mattlach]

Here is the output if I try to run mythtv-setup manually:

Code: Select all

myth@mythtv:~$ mythtv-setup
mysql: [Warning] Using a password on the command line interface can be insecure.
Gtk-Message: 06:19:22.068: GtkDialog mapped without a transient parent. This is discouraged.
/usr/bin/mythtv-setup: 68: /usr/bin/mythtv-setup: gksu: not found
Gtk-Message: 06:19:38.300: GtkDialog mapped without a transient parent. This is discouraged.
myth@mythtv:~$ mythbackend 
2019-08-13 06:20:00.436340 C  mythbackend version:  [v30.0] www.mythtv.org
2019-08-13 06:20:00.436356 C  Qt version: compile: 5.9.5, runtime: 5.9.5
2019-08-13 06:20:00.436360 N  Enabled verbose msgs:  general
2019-08-13 06:20:00.436368 N  Setting Log Level to LOG_INFO
2019-08-13 06:20:00.446807 I  Added logging to the console
2019-08-13 06:20:00.446986 I  Setup Interrupt handler
2019-08-13 06:20:00.447001 I  Setup Terminated handler
2019-08-13 06:20:00.447010 I  Setup Segmentation fault handler
2019-08-13 06:20:00.447015 I  Setup Aborted handler
2019-08-13 06:20:00.447021 I  Setup Bus error handler
2019-08-13 06:20:00.447026 I  Setup Floating point exception handler
2019-08-13 06:20:00.447039 I  Setup Illegal instruction handler
2019-08-13 06:20:00.447044 I  Setup Real-time signal 0 handler
2019-08-13 06:20:00.447052 I  Setup Hangup handler
2019-08-13 06:20:00.447157 N  Using runtime prefix = /usr
2019-08-13 06:20:00.447161 N  Using configuration directory = /home/myth/.mythtv
2019-08-13 06:20:00.447380 I  Assumed character encoding: C
2019-08-13 06:20:00.447384 W  This application expects to be running a locale that specifies a UTF-8 codeset, and many features may behave improperly with your current language settings. Please set the LC_ALL or LC_CTYPE, and LANG variable(s) in the environment in which this program is executed to include a UTF-8 codeset (such as 'en_US.UTF-8').
2019-08-13 06:20:00.447637 I  Using a profile name of: 'mythtv' (Usually the same as this host's name.)
2019-08-13 06:20:00.447675 I  Start up testing connections. DB localhost, BE , attempt 0, status dbAwake, Delay: 2000

This version of MythTV requires an updated database. (schema is 33 versions behind)

Please run mythtv-setup or mythbackend to update your database.

Database Host: localhost
Database Name: mythconverg

2019-08-13 06:20:01.455215 N  Setting QT default locale to en_US
2019-08-13 06:20:01.455225 I  Current locale en_US
2019-08-13 06:20:01.455262 N  Reading locale defaults from /usr/share/mythtv//locales/en_us.xml
2019-08-13 06:20:01.459950 I  Loading en_us translation for module mythfrontend
2019-08-13 06:20:01.462597 I  Current MythTV Schema Version (DBSchemaVer): 1317
2019-08-13 06:20:01.462608 W  Not allowed to upgrade the database.
2019-08-13 06:20:01.463610 E  Couldn't upgrade database to new schema on slave backend.

The setup never loads. Instead I get an immediate question if I want to start the Mythtv backend. Hitting yes or no makes no difference. It never appears to load.

The next question asks if I want to run mythtfilldatabase, if I select yes, I get the output in console above.

I'm not sure why it is talking about a "slave database" I've never had one of those. It doesn't mention the "slave database" if I try to start the backend setup from the GUI start menu, only if I do it from the console.

Again, any suggestions anyone might have to send me down the right path are greatly appreciated!

[MikeB2013]

As a guess, you might have hit the problem discussed in mythtv trac ticket https://code.mythtv.org/trac/ticket/13256
I provided a patch to fix the issue a little while ago, I presume it is waiting for review by mythtv developers, it not strictly a mythtv issue, as it relates to packaging, so it could well be a lower priority.

In the meantime, there is a fix using a terminal session (don't use the menu option to run mythtv-setup app)

1. Add current user to mythtv group (this should fix the loop issue you are seeing) and then reboot for it to take effect, this only needs to be done once.

Code: Select all

sudo adduser $USER mythtv
sudo reboot

For later versions of *ubuntu e.g. 16.04, 18.04 which use systemd, your previous 14.04 used upstart, you need to control mythtv-setup and mythbackend using terminal commands:

To stop mythbackend

Code: Select all

sudo systemctl stop mythtv-backend

To start mythbackend

Code: Select all

sudo systemctl start mythtv-backend

To check mythbackend status (running, failed etc)

Code: Select all

systemctl status mythtv-backend

So to use mythtv-setup you need to :

Code: Select all

sudo systemctl stop mythtv-backend
mythtv-setup

when finished in mythtv-setup, say no to the prompts (start mythbackend, run mythfilldatabase) when exiting mythtv-setup

Code: Select all

sudo systemctl daemon-reload
sudo systemctl start mythtv-backend

Note the "sudo systemctl daemon-reload" is not normally necessary but will be the first time on a new installation as systemd will have "failed" mythbackend as it will not have been configured (it will try and start too many times). The command does no harm if run when not needed.

Finally mythtv 30 has a new user interface for setup options in both mythfrontend and mythtv-setup, which is different from what you are used to in mythtv 0.27.

EDIT
see Host Address Backend Setup (v29), it applies to 30 as well, https://www.mythtv.org/wiki/Setup_General

Mike

[mattlach]

As a guess, you might have hit the problem discussed in mythtv trac ticket https://code.mythtv.org/trac/ticket/13256
I provided a patch to fix the issue a little while ago, I presume it is waiting for review by mythtv developers, it not strictly a mythtv issue, as it relates to packaging, so it could well be a lower priority.

In the meantime, there is a fix using a terminal session (don't use the menu option to run mythtv-setup app)

1. Add current user to mythtv group (this should fix the loop issue you are seeing) and then reboot for it to take effect, this only needs to be done once.

Code: Select all

sudo adduser $USER mythtv
sudo reboot

Thanks, I saw this in one of your earlier posts, so I manually added my user to the mythtv group (using a sligfhtly different syntax, "usermog -aG <usermname>" but that shouldn't make a difference.

Now granted, I did this AFTER importing the database, so maybe that messed something up? I recall the database wouldn't impoirt from my main user account, so I had to su into the mythtv account in order to import it.

Maybe I'll try to go back and reimport the database. The lovely part about running this ontop of ZFS is that I snapshotted every step along the way, so I can easily revert to the pre-database import state and try again.

For later versions of *ubuntu e.g. 16.04, 18.04 which use systemd, your previous 14.04 used upstart, you need to control mythtv-setup and mythbackend using terminal commands:

To stop mythbackend

Code: Select all

sudo systemctl stop mythtv-backend

To start mythbackend

Code: Select all

sudo systemctl start mythtv-backend

To check mythbackend status (running, failed etc)

Code: Select all

systemctl status mythtv-backend

So to use mythtv-setup you need to :

Code: Select all

sudo systemctl stop mythtv-backend
mythtv-setup

when finished in mythtv-setup, say no to the prompts (start mythbackend, run mythfilldatabase) when exiting mythtv-setup

Code: Select all

sudo systemctl daemon-reload
sudo systemctl start mythtv-backend

Note the "sudo systemctl daemon-reload" is not normally necessary but will be the first time on a new installation as systemd will have "failed" mythbackend as it will not have been configured (it will try and start too many times). The command does no harm if run when not needed.

These should be very useful in the future, but I don't think they are the source of my problems right now. "ps -A" does not show mythbackend running, so there is nothing to kill.

The odd part to me is myth-setup does not launch. It errors out at some point, but it fails silently without an apparent error message, and thus goes directly to the "do you want to launch the backend" which also fails, and "do you want to run mythfilldatabase" which fails because the schema has not been updated, because mythtv-setup never runs, so the conversion process never happens.

I wish I could figure out why myth-setup never launches when called from either the command line or from the gui menu. The console window gives no indication of why other than this:

Code: Select all

Gtk-Message: 06:19:22.068: GtkDialog mapped without a transient parent. This is discouraged.
/usr/bin/mythtv-setup: 68: /usr/bin/mythtv-setup: gksu: not found
Gtk-Message: 06:19:38.300: GtkDialog mapped without a transient parent. This is discouraged.

I also recall seeing it complain about a UTF8 warning at some point, but I am not sure what the problem there may be.

The error about gksu is odd, considering gksu has been deprecated and removed for some time now...

I wonder if it is bevcause myth-setup cannot connect to mysql?

The first time I tried to run mythsetup and it prompted me for the connection information I entered "localhost" as the host. I have read that doing this causes connection problems, so I switched it to my IP (by editing /etc/mythtv/config.xml) and this made things worse. Not only does myth-setup not launch, but now mythfilldatabase complains of not being able to connect to host, which it didn't before.

Side note, is editing /etc/mythtv/config.xml the right place to make these changes? There also appears to be an identical configuration file in /home/mythtv/.mythtv/config.xml. Or is there a different way all together to tell mythtv-setup and the backend how to connect to the database?

Finally mythtv 30 has a new user interface for setup options in both mythfrontend and mythtv-setup, which is different from what you are used to in mythtv 0.27.

EDIT
see Host Address Backend Setup (v29), it applies to 30 as well, https://www.mythtv.org/wiki/Setup_General

Mike

Thanks, I'll keep this in mind when I get there. I did read a little bit about it in the release notes before starting.

At this point I'll cross this bridge when I get there. For now I can't even get into mythtv-setup and my database to convert to the current schema.


Any ideas, thoughts, prayers ( :p ) appreciated.

I'm pretty close to another in the long line of "why can't we have a normal TV like everyone else" conversation with the significant other, if this doesn't go up ASAP :p

[MikeB2013]

You mentioned using real ip address, rather than localhost, mysql needs to be configured for this, it maybe but is worth checking.
The file /etc/mysql/mysql.conf.d/mythtv.cnf
should be as follows:

Code: Select all

[mysqld]
bind-address=::
max_connections=100

If bind-address has # character at the start of the line, this has to be removed and mysql restarted:
sudo systemctl restart mysql.service
and check that mysql is running by sudo systemctl status mysql.service

You can check mysql access to mythconverg database as follows, the password is the one from /etc/mythtv/config.xml
for localhost
mysql -umythtv -p mythconverg

for ip address change <ip address> to your actual ip address
mysql -umythtv -p mythconverg -h<ip address>

[mattlach]

You mentioned using real ip address, rather than localhost, mysql needs to be configured for this, it maybe but is worth checking.
The file /etc/mysql/mysql.conf.d/mythtv.cnf
should be as follows:

Code: Select all

[mysqld]
bind-address=::
max_connections=100

If bind-address has # character at the start of the line, this has to be removed and mysql restarted:
sudo systemctl restart mysql.service
and check that mysql is running by sudo systemctl status mysql.service

You can check mysql access to mythconverg database as follows, the password is the one from /etc/mythtv/config.xml
for localhost
mysql -umythtv -p mythconverg

for ip address change <ip address> to your actual ip address
mysql -umythtv -p mythconverg -h<ip address>

Thank you again. Your help has been invaluable every time I've had questions.

So, mysql definitely appears to be running:

Code: Select all

myth@mythtv:~$ sudo systemctl status mysql.service
[sudo] password for myth: 
* mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2019-08-13 17:02:03 UTC; 2min 9s ago
  Process: 391 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS
  Process: 314 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 402 (mysqld)
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mysql.service
           `-402 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Aug 13 17:02:03 mythtv systemd[1]: Starting MySQL Community Server...
Aug 13 17:02:03 mythtv systemd[1]: Started MySQL Community Server.

So, we can eliminate that as the issue.

I'm not sure how to interpret the mysql output though. Does this mean I successfully connected to the database or not?

Code: Select all

myth@mythtv:~$ mysql -u mythtv -p mythconverg
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

I'm starting to wonder if there is something wrong with my LXDE installation, and myth-setup for some reason can't open a graphical instance.

I can't test any of the GUI stuff from work though, only whatever I can do via ssh. That will ahve to wait until I get home. I also can't really mess with the IP address thing right now, so that will have to wait as well, but am I right to assume that going by IP address instead of localhost is the correct approach, or am I going down the wrong track there?

Any further thoughts or Ideas you may have are must welcome!

[mattlach]

A thought.

Does mythtv-setup dump log information anywhere, or is there a way to launch it with a verbose switch, so I can see why it is not launching, beyond the little it spits out in the console window upon (attempted) launch?

The fact that it fails so silently is kind of frustrating.

[bill6502]

Just answered that here: viewtopic.php?f=29&t=3286&p=15909#p15909

You may want different additional logging (but start without the -v... switch.) -v help tells you all of
the options.

[mattlach]

Just answered that here: viewtopic.php?f=29&t=3286&p=15909#p15909

You may want different additional logging (but start without the -v... switch.) -v help tells you all of
the options.

Thank you. I'll give that a shot as soon as I get home. Hopefully it will lead me straight to the issue.

[bill6502]

A slave backend can't update the DB.

See:

mythtv-setup-> General->Host Address Backend Setup->This server is the Master Backend

Make sure it's checked.

[mattlach]

A slave backend can't update the DB.

See:

mythtv-setup-> General->Host Address Backend Setup->This server is the Master Backend

Make sure it's checked.

Huh. Is this one of the setup changes since .27?

I'll be sure to make sure it is, once I can actually get into mythtv setup :p

[mattlach]

Alright.

So I decided to roll back to my clean install of 18.04 LTS and try again, just to make sure I didn't make any stupid mistakes while I was tired yesterday, and to see if I can fix my UTF8 errors before installing anything, as I understand once you start installing some packages, it may be too late to change the locale.

Steps:
1.) Rollback to clean 18.04 LTS server install

2.) Change locale in /etc/default/locale from LANG="C" to LANG="en_US.UTF-8"

3.) Install LXDE package and dependencies

4.) Add myth PPA and install mythtv-backend-master package

5.) Add user to mythtv group

6.) configure all my drive mounts to where they were on the old install when I backed up the database

7.) restore database

8.) See if I can get into mythtv-setup

9.) If not, continue troubleshooting.

Wish me luck!

[mattlach]

Progress update

1.) Rollback to clean 18.04 LTS server install

Done. So easy. I love ZFS.

2.) Change locale in /etc/default/locale from LANG="C" to LANG="en_US.UTF-8"

Turns out there is more to it than just editing the locale file. This explains it well.

3.) Install LXDE package and dependencies

As simple as "sudo apt install lxde"

4.) Add myth PPA and install mythtv-backend-master package

Piece of cake.

sudo add-apt-repository ppa:mythbuntu/30
sudo apt-get update
sudo apt install mythtv-backend-master

(Though I find it amusing that it is still called the mythbuntu PPA considering how long that project has been dead now)

5.) Add user to mythtv group

sudo usermod -aG mythtv <username>

log off, and log back on for it to take effect

6.) configure all my drive mounts to where they were on the old install when I backed up the database

This part is a pain...

...but it's done. All mounts linked, and permissions corrected with the new uid and gid the mythtv user received.

7.) restore database

The moment of truth...

Code: Select all

myth@mythtv:~$ /usr/share/mythtv/mythconverg_restore.pl --directory /mnt/dbbackup/ --filename mythconverg-1317-20190812192356.sql
DBI connect('host=:database=mythconverg','',...) failed: Access denied for user 'myth'@'localhost' (using password: NO) at /usr/share/mythtv/mythconverg_restore.pl line 933.

Unable to connect to database.
           database: mythconverg
               host: 
           username: 
To see the password used, please re-run the script with the --verbose
argument.

Please check your configuration files to verify the database connection
information is correct.  The files that are used to retrieve connection
information are prefixed with "parsing" in the "Parsing configuration files"
section of the --verbose output.

Also note that any [client] or [mysql] password specified in the MySQL options
file (/etc/my.cnf or /etc/mysql/my.cnf or ~/.my.cnf) will take precedence over
the password specified in the MythTV configuration files.

ERROR: Unable to connect to database.

First attempt is a fail. Despite adding my login user "myth" to the "mythtv" group, it will not allow me to restore the database as that user. Time to su to mythtv and retry.

Code: Select all

mythtv@mythtv:/home/myth$ /usr/share/mythtv/mythconverg_restore.pl --directory /mnt/dbbackup/ --filename mythconverg-1317-20190812192356.sql

ERROR: Unable to do a full restore. The database contains data.

Second attempt is a fail. Somehow my brand spanking new database contains data

Now we add drop and create commands and try again.

Code: Select all

mythtv@mythtv:/home/myth$ /usr/share/mythtv/mythconverg_restore.pl --drop_database --create_database --directory /mnt/dbbackup/ --filename mythconverg-1317-20190812192356.sql

Successfully restored backup.

After a bit of a wait... Success!

8.) See if I can get into mythtv-setup

Here is where I got stuck last time. Fingers crossed.

1.) Log in to GUI

2.) open console

3.) sudo systemctl stop mythtv-backend (just in case)

4.) mythtv-setup.real --logpath /tmp

*fingers crossed*

config1.png (57.97 KiB) Viewed 3967 times

Wooo! This didn't happen before. It certainly does look different than .27

I'm hoping this is just because I need to update the password to the one found in /etc/mythtv/config.xml

Be back in a bit!

[mattlach]

Alright, we are in business!

config2.png (64.19 KiB) Viewed 3965 times

I am elated!

Thanks for all your help guys! Hopefully I am OK on my own from here on out. (famous last words, I know)