[mythtv-users] Mythtv database requirements??

[Stephen Worthington]

On Mon, 30 Mar 2020 07:31:26 -0400, you wrote:

>
>On 3/29/20 8:54 PM, Stephen Worthington wrote:
>> On Sun, 29 Mar 2020 20:31:25 -0400, you wrote:
>>
>>> On Sun, Mar 29, 2020 at 10:44 AM James Abernathy <jfabernathy at gmail.com>
>>> wrote:
>>>
>>>>
>>>> On Sun, Mar 29, 2020 at 9:29 AM Stephen Worthington <
>>>> stephen_agent at jsw.gen.nz> wrote:
>>>>
>>>>> On Sun, 29 Mar 2020 07:21:17 -0400, you wrote:
>>>>>
>>>>>> So I know mythtv uses MySQL which has supposedly transitioned to mariadb
>>>>>> over time, but it's basically the same mysql commands. I think???
>>>>>>
>>>>>> So to compare what I have on 2 systems I found some puzzling results, at
>>>>>> least to me.
>>>>>>
>>>>>> _System 1_ is Xubuntu 18.04.4 installed recently and mythtv v31
>>>>>> installed today.
>>>>>>
>>>>>> It has mysql-client-5.7, mysql-client-core-5.7, mysql-server-5.7,
>>>>>> mysql-server-core-5.7 installed.
>>>>>>
>>>>>> if has no packages by the name mariadb, or maria installed.
>>>>>>
>>>>>> _System 2_ is an Ubuntu 18.04 Server which started on mythtv 29, moved
>>>>>> to 30, and not 31.
>>>>>>
>>>>>> It does NOT have mysql-client-5.7, mysql-client-core-5.7,
>>>>>> mysql-server-5.7, mysql-server-core-5.7 installed.
>>>>>>
>>>>>> It has mysql-common installed.
>>>>>>
>>>>>> It has mariadb-client-10.0, mariadb-client-core-10.0,
>>>>>> mariadb-server-10.0, mariadb-server-core-10.0, and mariadb-common
>>>>> installed.
>>>>>> Not sure why I see the difference and wonder if this is the reason some
>>>>>> of the database scripts that run during mythtv updates fail for syntax
>>>>>> reasons??
>>>>>>
>>>>>> Jim A
>>>>> Unless something has changed recently, Ubuntu installs MySQL by
>>>>> default, so when you install MythTV, it sees MySQL present and is
>>>>> happy.  If you install MythTV on a Ubuntu system with neither MySQL
>>>>> nor MariaDB installed, the MythTV packages specify MySQL first, so
>>>>> MySQL gets installed.  So the only way you get MythTV running with
>>>>> MariaDB on Ubuntu is if you have manually chosen to either install
>>>>> MariaDB before you installed MythTV (recommended), or have later
>>>>> installed MariaDB which will automatically uninstall MySQL.  If you do
>>>>> the latter, you normally get a message that the database versions are
>>>>> out of step and MariaDB is unable to convert the mythconverg database.
>>>>> So you have to reinstall MySQL, do a backup of mythconverg, install
>>>>> MariaDB again, create a new mythconverg database and set up its access
>>>>> credentials, then restore your backed up copy of mythconverg.  A very
>>>>> tedious process I have done more than once, hence why I recommend
>>>>> installing MariaDB before installing MythTV if at all possible.
>>>>>
>>>>> When MariaDB is installed, mysql-common remains installed as there are
>>>>> other packages that require it.  It only really creates a three config
>>>>> files under /etc/mysql plus documentation files and /usr/share/lintian
>>>>> files, so it is not a worry.
>>>>>
>>>>> My first thought is to wait for Bill to come up with a solution for
>>>> updating a system with the mariadb version that Ubuntu 18.04 uses. I plan
>>>> on staying on Ubuntu 18.04 LTS until 2023 unless the Covid-19 kills me
>>>> first.
>>>>
>>>> BTW, I can't remember how mariadb got installed because none of my install
>>>> instructions have that written down.
>>>>
>>>> However, I'm a guy who likes to stay on standard software so I don't get
>>>> caught by normal assumptions.  All my mythtv databases, backups and
>>>> recordings, etc are on a set of 2 RAID mirrors, i.e. 4 drives. I've read
>>>> that If you create a new Xubuntu 18.04 install and it will see the 2 RAID
>>>> mirrors and know how to use them. I'd just have to add the mounts in
>>>> /etc/fstab.  Then I could restore the backed up mythconverg and go from
>>>> there.
>>>>
>>>> Obviously, I have both RAIDs backed up to external e-SATA drive so if the
>>>> shit hits the fan I can rebuild.  I even have a clonezilla image of my boot
>>>> drive /dev/sda.
>>>>
>>>> Thoughts on doing this to get back to standard??
>>>> Jim A
>>>>
>>> I just went through this exercise.  As part of my social distancing, I just
>>> migrated my MythTV backend from a physical EL6 machine to an Ubuntu 18 LTS
>>> VM on Unraid.  The EL6 machine was running MariaDB, yet when I built the
>>> Ubuntu machine, as Stephen stated, the package manager dragged in MySQL 5.7
>>> I think, rather than MariaDB 10, which is what I was expecting to see.
>>> Anyway, when I tried to import the mythconverg dump file from the old
>>> machine, it failed on the points Bill made.
>>>
>>> To get around this, did the following on the new VM:  1) uninstall mythtv,
>>> 2) uninstall mysql, 3) install mariadb, then 4) reinstall mythtv.
>>>
>>> Relatively easy process.  After that, the mythconverg dump file was
>>> successfully imported and all is well with the world.
>
>I appreciate all this education. So the surprising part, if I understand 
>what has been said here, is that a database backup using 
>mythconverg_backup_jsw.pl from a system running MariaDB 10.1 cannot be 
>successfully restored on a new system that has MySQL 5.7?
>
>I would have thought that while there are some command structure 
>difference between the 2 the backup and restore would have been one of 
>the simpler functions that would have worked.
>
>BTW, it looks like Bill has a fix for my update issue.
>
>Jim A

No, there is no problem with restoring a backup.  The backup files are
SQL statements, so they will run on anything that will accept the SQL
being used.  The problem is when you try to change from MySQL to
MariaDB and get MariaDB to just do a binary fix to convert the MySQL
tables into MariaDB tables.  It finds no path it can use to do that
conversion due to a mismatch of MySQL version against MariaDB version.
So it leaves the database in MySQL format and tells you it is unable
to convert it.  It seems to be a problem with the point versions.  I
suspect that if you were coming from MySQL 5.7.0 and converting to
MariaDB 10.0.0, it would work.  But from say MySQL 5.7.1 to say
MariaDB 10.0.4, it does not work as no-one has created code for any
binary format changes in both programs to match up between them.  So
it is a pain but not a show stopper problem - just do a backup, change
to MariaDB and do a restore.  But you do need to remember to do the
backup before you change the database engine!