Opened 2 years ago

Closed 2 years ago

Last modified 16 months ago

#13077 closed Bug Report - General (fixed)

mythconverg_backup.pl strange behavior when using IPV6 for database

Reported by: Peter Bennett Owned by: Peter Bennett
Priority: minor Milestone: 29.1
Component: Contributed Scripts & Apps Version: v29-fixes
Severity: medium Keywords:
Cc: Ticket locked: no

Description

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

Attachments (3)

13077-mythconverg_backup-bracket_ipv6_address_for_dbd.patch (1.2 KB) - added by sphery 2 years ago.
Potential patch
reverse_database_host_order.patch (904 bytes) - added by Jonatan Lindblad 2 years ago.
20170804_backup_restore.patch (3.1 KB) - added by Peter Bennett 2 years ago.
Combined patch for backup and restore

Download all attachments as: .zip

Change History (17)

comment:1 Changed 2 years ago by Peter Bennett

Owner: changed from sphery to Peter Bennett
Status: newassigned

comment:2 Changed 2 years ago by 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.

comment:3 Changed 2 years ago by 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.

Changed 2 years ago by sphery

Potential patch

comment:4 Changed 2 years ago by 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.

comment:5 Changed 2 years ago by Stuart Auchterlonie

Milestone: needs_triage29.1

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

comment:6 Changed 2 years ago by 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

comment:7 Changed 2 years ago by 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.

comment:8 Changed 2 years ago by 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 });

Changed 2 years ago by Jonatan Lindblad

comment:9 Changed 2 years ago by 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.

Changed 2 years ago by Peter Bennett

Combined patch for backup and restore

comment:10 Changed 2 years ago by 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.

comment:11 Changed 2 years ago by 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.

comment:12 Changed 2 years ago by Peter Bennett <pbennett@…>

Resolution: fixed
Status: assignedclosed

In 68392fd5b4358447da11be5b097a02aa3742cc58/mythtv:

Fixes #13077 IPV6 support for backup and restore scripts.

comment:13 Changed 2 years ago by Peter Bennett <pbennett@…>

In 345f69c8332102d8284a9bfb87126f6f8eb127f1/mythtv:

Fixes #13077 IPV6 support for backup and restore scripts.

(cherry picked from commit 68392fd5b4358447da11be5b097a02aa3742cc58)

comment:14 Changed 16 months ago by Peter Bennett

Owner: changed from Peter Bennett to Peter Bennett
Note: See TracTickets for help on using tickets.