MySQL Bugs: #85814: mysqldbexport exclude does not work

Bug #85814	mysqldbexport exclude does not work
Submitted:	5 Apr 2017 15:13	Modified:	11 Apr 2017 13:32
Reporter:	Dan Teok	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	5.7.17	OS:	Ubuntu (16.04)
Assigned to:		CPU Architecture:	Any
Tags:	-X, exclude, mysqldbexport

Description:
Tested on Ubuntu 16.04 on MySQL Server 5.7.17
Created schema dtsandbox.
Created 5 tables:
1. get_tmp
2. mb7rcpp
3. nbimrep
4. npbrcpp
5. test_box

Goal here is to dump all tables except getm9p_tmp, and test_box.

Attempt #1
----------
$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude="dtsandbox.get_tmp" dtsandbox

Result: All 5 tables were exported. Expected only 4.

Attempt #2
----------
$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude="get_tmp" dtsandbox

Result: All 5 tables were exported. Expected only 4.
Note: it did not make a difference if double-quotes were used or not in exclude.

Attempt #3 
----------
$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude="dtsandbox.get_tmp" --exclude="dtsandbox.test_box" dtsandbox

Result: All 5 tables were exported. Expected only 3.

Attempt #4
----------
$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude=".*_(tmp|box).*" dtsandbox

Result: All 5 tables were exported. Expected only 3.

How to repeat:
Create new schema and create some tables.
Install mysql-utilities (sudo apt install mysql-utilities)

Run the above mysqldbexport from command line above.

Suggested fix:
The fix should really be just as it is described in MySQL documentation -- https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbexport.html

By using the exclude option, specified tables *must be excluded from the export.

Where possible, provide an example with and without use of regex.

"getm9p_tmp" should be read as "get_tmp"

Hello Dan Teok,

Thank you for the report.
I'm not seeing the issue with MySQL Utilities GA version. Could you please confirm the exact version of MySQL Utilities that you are using? If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

=====
### 5.7.17

root@localhost [db1]> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| get_tmp       |
| mb7rcpp       |
| nbimrep       |
| npbrcpp       |
| test_box      |
+---------------+
5 rows in set (0.00 sec)

[umshastr@hod03]/export/umesh/server/binaries/1.6.5:  PYTHONPATH="./lib/python2.7/site-packages/" bin/mysqldbexport --version
MySQL Utilities mysqldbexport version 1.6.5
License type: Commercial

[umshastr@hod03]/export/umesh/server/binaries/1.6.5:  PYTHONPATH="./lib/python2.7/site-packages/" bin/mysqldbexport --server=root@localhost --export=both --verbose  --exclude=db1.get_tmp --exclude=db1.test_box db1
# Source on localhost: ... connected.
# Exporting metadata from `db1`
DROP DATABASE IF EXISTS `db1`;
CREATE DATABASE `db1`;
USE `db1`;
# TABLE: `db1`.`mb7rcpp`
CREATE TABLE `mb7rcpp` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: `db1`.`nbimrep`
CREATE TABLE `nbimrep` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: `db1`.`npbrcpp`
CREATE TABLE `npbrcpp` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.
USE `db1`;
# Exporting data from `db1`
# Data for table `db1`.`mb7rcpp`:
INSERT INTO `db1`.`mb7rcpp` VALUES (1);
INSERT INTO `db1`.`mb7rcpp` VALUES (2);
# Data for table `db1`.`nbimrep`:
INSERT INTO `db1`.`nbimrep` VALUES (1);
INSERT INTO `db1`.`nbimrep` VALUES (2);
# Data for table `db1`.`npbrcpp`:
INSERT INTO `db1`.`npbrcpp` VALUES (1);
INSERT INTO `db1`.`npbrcpp` VALUES (2);
#...done.

Thanks,
Umesh

$ mysqldbexport --version
MySQL Utilities mysqldbexport version 1.6.1
License type: GPLv2

$

Looks like you are on a more recent version. How do I update this?

I asked in my just now on how to install the recent version of MySQL Utilities.
I have downloaded mysql-utilities_1.6.5-1ubuntu16.04_all.deb from https://dev.mysql.com/downloads/file/?id=468270.

$ dpkg -i mysql-utilities_1.6.5-1ubuntu16.04_all.deb
(Reading database ... 112890 files and directories currently installed.)
Preparing to unpack mysql-utilities_1.6.5-1ubuntu16.04_all.deb ...
Unpacking mysql-utilities (1.6.5-1ubuntu16.04) over (1.6.1-2) ...
dpkg: error processing archive mysql-utilities_1.6.5-1ubuntu16.04_all.deb (--install):
 trying to overwrite '/usr/lib/python2.7/dist-packages/mysql/__init__.py', which is also in package python-mysql.connector 2.0.4-1
dpkg-deb: error: subprocess paste was killed by signal (Broken pipe)
postrm script for mysql-utilities
found python2.7
/usr/lib/python2.7/dist-packages
Errors were encountered while processing:
 mysql-utilities_1.6.5-1ubuntu16.04_all.deb

I have also tried with gdebi.

$ gdebi gdebi mysql-utilities_1.6.5-1ubuntu16.04_all.deb
Reading package lists... Done
Building dependency tree
Reading state information... Done
Reading state information... Done
This package is uninstallable
Dependency is not satisfiable: mysql-connector-python (>= 2.0.0)|mysql-connector-python-commercial (>= 2.0.0)

I can't seem to install it. Any ideas? Please help.

Alright. I believe I got this working

$ dpkg --force-all -i downloadedPackage.deb
$ mysqldbexport --version
MySQL Utilities mysqldbexport version 1.6.5
License type: GPLv2

If you can please help with the next question.
What is the correct syntax to exclude table objects by regex?
Take for example to exclude all tables with underscored tablenames.