mythconverg_backup.pl strange behavior when using IPV6 for database

[Peter Bennett]

If the database host in config.xml is specified in IPV6 format, for example

    <Host>fd3d:63f5:6a89:1::10</Host>

the mythconverg_backup.pl script

• fails to find the backup storage group, instead reporting that no backup directory is supplied
• If you specify a backup directory on the command line, it creates the backup but the file name does not include the schema version

File name when using ipv4

mythdbmst-1348-20170727143457.sql.gz

File name when using ipv6

mythdbmst-20170727143255.sql.gz

[sphery]

Perl's DBD::mysql requires a bracketed IPv6 address, if specifying an IPv6 address directly (rather than specifying a host name).

​https://metacpan.org/pod/DBD::mysql#host

If config.xml does not allow specifying a bracketed IPv6 address, and if we want mythconverg_backup.pl (and mythconverg_restore.pl) to support specifying a MySQL IPv6 address directly, then we will have to either detect an IPv6-looking address and apply brackets around it or, alternatively, we could try a connection with the specified DB host and then, if it fails, attempt another connection with a bracketed version of the specified DB host.

Note that all the behavior described above (not being able to get a backup directory automatically/requiring it to be specified and not putting a schema version in the file name) is standard behavior for the script when it is unable to connect to the database to retrieve the information. So the only issue is the inability to connect using an IPv6 address as a DB host name. I don't know whether config.xml allows (requires?/should require?) an IPv6 address to be specified in brackets, but if it is allowed, the current script version (1.0.12) and all previous ones should be able to pull the unspecified information from the database if the OP adds brackets to the config.xml <Host> entry, at least as a workaround until the script is updated to better handle non-bracketed IPv6 addresses.

[sphery]

So, it seems that MySQL programs (like mysqldump) won't allow a bracketed IPv6 address for their host specification, so bracketing the IPv6 address in config.xml won't work. The attached patch should bracket a not-already-bracketed IPv6 address for only the DBI connection using a quick-and-dirty test to see if it looks like an IPv6 address (looking for one or more colon characters in the value). I didn't want to pull in Net::IP or any other module just to check, so I thought this might be good enough (though I fully admit to not being a network address/host name expert, so please correct me if it's a bad test). Note that I did not actually test this patch with an IPv6-enabled MySQL server, so I'd appreciate your testing it.

If config.xml does allow specifying a bracketed IPv6 address, we would need to scrub out the brackets from the db_host so mysqldump will work. I'm guessing bracketed IPv6 addresses in config.xml are not allowed because I do not think that libmysqlclient allows bracketed IPv6 addresses (so the Qt MySQL drivers wouldn't connect).

Note, too, that mythconverg_restore.pl will need a similar update. I'm only uploading the patch for backup and the restore patch can be created once we've verified the approach. Thanks.

[sphery]

Potential patch

[Peter Bennett]

I worked on the IPV6 support for config.xml a few months ago and I had to leave the square brackets off the IPV6 address in the config.xml file to get everything to work.

Thanks for the patch. I will test it.

[Stuart Auchterlonie]

fyi, the patch looks reversed, so you'll need to use patch -R on it

[Bill Meek]

If DBBackupDirectory isn't specified (I'm letting the program get it from the DB) same issue, but fails earlier and no backup is made.

I get:

ERROR: DBBackupDirectory not specified, stopped at /usr/local/share/mythtv/mythconverg_backup.pl line 856.

Tried the patch + added verbose message and PrintError => 1 in check_database():

In check_database, before connect(), host=[fdf9:a66:2cd8:1::200]
DBI connect('database=mythconverg:host=[fdf9:a66:2cd8:1::200]','mythtv',...) failed: Unknown MySQL server host 'fdf9' (11) at /usr/local/share/mythtv/mythconverg_backup.pl line 715.

Perl v5.24.1, Ubuntu 17.04, MythTV v30-Pre-1

[Peter Bennett]

I am getting the same result. No matter what I try it is only using the first 4 digits of the IPV6 address. This is supposed to work, as per ​http://search.cpan.org/~michielb/DBD-mysql-4.043/lib/DBD/mysql.pm. I did also try changing the colon before host= to a semicolon in conformance with that page, but the result is the same. Perhaps we have an outdated mysql perl interface library somewhere. As per ​http://www.perlmonks.org/index.pl?node_id=890934 this was fixed in 2011.

Checking configuration.
DBI connect('database=mythdbmst:host=[fd3d:63f5:6a89:1::10]','mythmst',...) failed: Unknown MySQL server host 'fd3d' (0) at /home/peter/proj/github.com/bennettpeter/mythtv/mythtv/programs/scripts/database/mythconverg_backup.pl line 711.

[Jonatan Lindblad]

It seems the regular expression in mysql.pm might be a bit too simple. Switching the order of database and host allows the IPv6 address to be parsed correctly.

diff --git a/mythtv/programs/scripts/database/mythconverg_backup.pl b/mythtv/programs/scripts/database/mythconverg_backup.pl
index 6b3d0eb13c..efc3a58470 100755
--- a/mythtv/programs/scripts/database/mythconverg_backup.pl
+++ b/mythtv/programs/scripts/database/mythconverg_backup.pl
@@ -701,8 +701,8 @@ EOF
             my $have_database_libs = check_database_libs;
             return 0 if ($have_database_libs < 2);
             $dbh = DBI->connect("dbi:mysql:".
-                                "database=$mysql_conf{'db_name'}:".
-                                "host=$mysql_conf{'db_host'}",
+                                "host=$mysql_conf{'db_host'}:".
+                                "database=$mysql_conf{'db_name'}",
                                 "$mysql_conf{'db_user'}",
                                 "$mysql_conf{'db_pass'}",
                                 { PrintError => 0 });

[Peter Bennett]

Combining the two patches and applying them to mythconverg_backup.pl and mythconverg_restore.pl gives me the following patch. This works for both backup and restore with database using IPV6 address.

[Peter Bennett]

Combined patch for backup and restore

[Bill Meek]

Peter, the above patch works for me. I didn't actually restore my DB, but it did get to "sub is_database_empty" where a connection is used to get the list of tables.

backup works perfectly.

One suggestion, PrintError? = STDERR (unless you were just using it for testing too.) Personally, I prefer printing the failure, it shouldn't show up now.

Thanks Jonatan and Mike too.

[Peter Bennett]

According to the documentation, PrintError is a boolean, so I am setting it to 1 so that errors are printed.

​https://metacpan.org/pod/DBI#PrintError

I may be wrong as I am far from an expert on perl.

[Peter Bennett <pbennett@…>]

In 68392fd5b4358447da11be5b097a02aa3742cc58/mythtv:

Fixes #13077 IPV6 support for backup and restore scripts.

[Peter Bennett <pbennett@…>]

In 345f69c8332102d8284a9bfb87126f6f8eb127f1/mythtv:

Fixes #13077 IPV6 support for backup and restore scripts.

(cherry picked from commit 68392fd5b4358447da11be5b097a02aa3742cc58)