Add CREATE TABLE statement functionality for pt-heartbeat --create-table

[Agustín]

It would be nice to have the ability to add a custom CREATE TABLE statement to the --create-table argument.

Right now, the only way to do this is manually editing the code in the following section:

"""
Create the heartbeat L<"--table"> if it does not exist.

This option causes the table specified by L<"--database"> and L<"--table"> to
be created with the following MAGIC_create_heartbeat table definition:

CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);
"""

In the manual pages, engine=memory is suggested, but there's no way of implementing it in an automated way:

"""
You must either manually create the heartbeat table on the master or use --create-table. See --create-table for the proper heartbeat table structure. The MEMORY storage engine is suggested, but not required of course, for MySQL.
"""

Some options discussed (with DanielN):

- Add to pt-heartbeat's config file (Daniel mentioned potential issues with multiline parsing).
- Add to one file that only has the CREATE TABLE statement, like --create-table /path/to/file/with/createtable.statement

Note that the file should also contain the INSERT INTO statement to be used to insert the first row to that table.

The user should make sure that the CREATE TABLE and the INSERT INTO statement work together well, in my opinion.

[Agustín (agustin-gallego)]

Another option, different from the request above, would be to add a --create-table-engine option so one can at least choose the engine used for the table (in the lines of using the Memory engine, as suggested in the docs).

[Frank Cizmich (frank-cizmich)]

I'm inclined to implementing the --create-table-engine option, as suggested by Agustin in #1
Since the main reason to modify it, as of now, would only be changing the engine.
The other options seem a bit overkill.

[Agustín (agustin-gallego)]

Sounds good, Frank. I agree that this will work fine for this case, and that implementing the other options would be somewhat overkill.

Bryan Welch proposed the change in comment #1 (credit where it is due :))

Agustín.

[Carlos Salguero (carlos-salguero)]

Added a new --create-table-engine cli param.
This fix is already merged and it will be released in 2.2.19.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-695