Merge lp:~guillaume-simon/mysql-utilities/mysql-utilities into lp:mysql-utilities

[Guillaume Simon]

When using mysqldiff or mysqlindexcheck with DB users having read access to many DBs objects (e.g tables), mysqldiff and others takes ages to execute (30+ secs). It can lead to DB timeouts issue when the value of interactive_timeout is too low, such "crashing" the execution of mysqldiff/mysqlindexcheck.

The issue comes in database.py/get_db_objects() where useless but resources intensive LEFT JOINS are performed when the method is executed with obj_type=_TABLE and columns="names" arguments.

The proposed patch just skipped those LEFT JOINS in this case, leading to a normal execution of mysqldiff even with many DB objects (back to a few seconds).

Unmerged revisions

402. By Guillaume Simon on 2014-01-31

Optimization fix for users having accesses to many DBs and Tables

401. By Nelson Goncalves <email address hidden> on 2013-11-26

Release-1.3.6 testing

This patch fixes the result files for the following MUT tests:
rpl_admin_gtid_demote_master_repo_file,
rpl_admin_gtid_demote_master_repo_file_req_pass and
rpl_admin_gtid_demote_master_repo_table

400. By Israel Gomez on 2013-11-26

Release-1.3.6 preparation

Added changes to CHANGES.txt file.

399. By Nelson Goncalves <email address hidden> on 2013-11-26

Release-1.3.6 testing

This patch fixes an issue with mysqlfrm on Windows and it also fixes
the frm_reader_default MUT test.

398. By Israel Gomez on 2013-11-26

Release-1.3.6 testing

More changes to mac OSX package name, this patch removes the python
version from the name.

397. By Nelson Goncalves <email address hidden> on 2013-11-25

Release-1.3.6 testing

This patch fixes the following MUT tests:
audit_log_admin, audit_log_grep, check_rpl_parameters, check_gtid_version
check_rpl_errors, export_gtid, audit_log_admin_errors, clone_server_errors
connection_values, copy_db_errors and frm_reader_errors.

396. By Israel Gomez on 2013-11-25

Release-1.3.6 testing

This patch fixes the mac OSX package name, which was missing the
platform name and platform version. Also removes the '-1' from the
commercial msi package name.

v2

395. By Paulo Jesus on 2013-11-25

BUG#14725390 : multithreaded copy, export, import

This patch fixes regression issues introduced in failover and rpl_admin by the
previous patch for this bug. More specifically, error handling for replication
commands has been corrected and a commit is issued before enabling/disabling
the binary logging in order to close any active transaction (that would lead
to an error when executing the command). Other small adjustments were also
made to few rpl_admin test to run properly.

394. By Paulo Jesus on 2013-11-22

BUG#14725390 : multithreaded copy, export, import

The mysqldbexport, mysqldbimport and mysqldbcopy utilities are single
threaded, not taking advantages of concurrent executions to export, import and
copy multiple databases or tables (on multi-core systems). This patch adds
multiprocessing capabilities to those utilities in order to improve their
performance. Other optimizations were also made to avoid repeated processing
(e.g., remove duplicated privileges check) and reuse created object, reducing
the waste of CPU time.

Follows the summary of multiprocessing changes and performance improvement for
each utility.

mysqldbexport:

- Multiprocessing by database for non POSIX systems (Windows) and by table for
  POSIX systems, using the new --multiprocess option.
- New --output-file option was added to store the export results instead of
  using the standard output which can be very slow (up to 10x slower). Now,
  export results are always sent to a file (even if temporary) and only sent
  to the stdout at the end if needed.

mysqldbimport:

- Multiprocessing by file (independently of the operating systems), using the
  new --multiprocess option.
- Perform only a single COMMIT at the end of importing each file, by default.
  A new --autocommit option was added to allow each statement to be committed
  upon its execution.
- Improve the bulk insert feature (support SQL statements). If the
  --bulk-insert option is enabled, then all INSERT statements are parsed and
  their data aggregated (if possible) in order to be executed as a single
  query.
- A new --max-bulk-insert option was added to control the maximum size of the
  bulk insert, in order to avoid broken pipe errors. When the max number of
  insert is reached a new bulk is created.

mysqldbcopy:
- Multiprocessing by database for non POSIX systems (Windows) and by table for
  POSIX systems, using the new --multiprocess option.
- Remove/disable previous thread feature.

Some additional fixes and improvements were also made to the code. For
example: fix position of database lock for export; improve output of database
export with the --file-per-table option, fix execution of SET
FOREIGN_KEY_CHECKS to import data ensuring that statements are executed in the
same order they appear in the imported files; improve method exec_query() and
call method with appropriate options to avoid unnecessary commits; move
get_copy_lock() to dbexport module to remove circular import issue.

MUT tests were added and existing one changed for regression.

393. By Israel Gomez on 2013-11-22

BUG#17634676 : Add missing option to show license type on Utilities

This patch adds a new option to each MySQL Utility to show the license
type. The parameter '--license' can now be used to display the release
version, license type and full copyright message. In addition, the
'--version' option has been modified to display the release version
and the license type, without the copyright. The '--help' option will
also display the license type.

Tests were updated.

v2

=== modified file 'mysql/utilities/common/database.py'
--- mysql/utilities/common/database.py	2013-11-22 23:04:19 +0000
+++ mysql/utilities/common/database.py	2014-01-31 10:14:13 +0000
@@ -1275,24 +1275,36 @@
                 KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
             """
             minimal_pos_to_quote = (0, 1, 4, 11, 12, 13, 16, 17, 18, 19)
-            _OBJECT_QUERY = """
-            FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
-                TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
-                TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
-            LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON
-                TABLES.TABLE_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
-                AND
-                TABLES.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME
-            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
-                TABLES.TABLE_SCHEMA = KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
-                AND
-                TABLES.TABLE_NAME = KEY_COLUMN_USAGE.TABLE_NAME
-            WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
-            GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
-                     COLUMNS.ORDINAL_POSITION
-            ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
-                     COLUMNS.ORDINAL_POSITION
-            """
+            if columns == "names":
+                _OBJECT_QUERY = """
+                FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
+                    TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
+                    TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
+                WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
+                GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
+                         COLUMNS.ORDINAL_POSITION
+                ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
+                         COLUMNS.ORDINAL_POSITION
+                """
+            else:
+                _OBJECT_QUERY = """
+                FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
+                    TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
+                    TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
+                LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON
+                    TABLES.TABLE_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
+                    AND
+                    TABLES.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME
+                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
+                    TABLES.TABLE_SCHEMA = KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
+                    AND
+                    TABLES.TABLE_NAME = KEY_COLUMN_USAGE.TABLE_NAME
+                WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
+                GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
+                         COLUMNS.ORDINAL_POSITION
+                ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
+                         COLUMNS.ORDINAL_POSITION
+                """
             exclude_param = "TABLES.TABLE_NAME"
 
         elif obj_type == _VIEW: