Add functionality to verify if a transaction is committed or not after failure

Add functionality to Verify if a transaction is committed or not after failure
Short name: Transaction_verifier (trx_verfier)

Problem:
If the connection to MariaDB server breaks after an application has
scheduled a commit (auto-commit or manual) but before the application
has got a response, there is no easy way for the application to know if
the commit succeeded or not.

The task is to provide a way to do this without any additional
network packages between application and server when things are
working normally.

A connection break can happen in many different ways:

• The network link goes down between the application and server
• The network link goes down between the application and MaxScale
• The network link goes down between the MaxScale and MariaDB server.
• MaxScale dies and restarts
• MariaDB server dies and restarts.
• MariaDB server dies and there is a failover to slave that takes over.

The official MariaDB connectors will handle all bookkeeping needed
to without any new application code. MaxScale will also be enhanced to
support the above

Solution:
For the application there would be new api call,
mariadb_trx_status(), to be used after reconnecting to the server after
a network or server failure.

mariadb_trx_status() will respond with 3 columns. First is state, which is one of MARIADB_TRX_COMMITTED,
MARIADB_TRX_ABORTED, MARIADB_TRX_IN_PROGRESS, MARIADB_TRX_UNKNOWN. The other 2 are connect_id and query_id in case of MARIADB_TRX_IN_PROGRESS.
MARIADB_TRX_UNKNOWN is for the case where a wrong transaction id is used or
if the transaction is very old and MariaDB has not longer any
information about it. This will give the application information of what to do
(inform user all went well, retry or kill the old query that is in progress).
Note that mariadb_trx_status() should do an automatic reconnect a few times if needed. The reason for the reconnect is that this command is usually to be done after a server goes done and we should wait for the server to start responding.

If one uses an old connector, one can instead use the following SQL
commands:

select trx_id();   -- Returns an trx_id "connection_id:commit_id"

select trx_status(trx_id, optional_gtid);  -- Returns 0-3 

When using an old MariaDB server that does not support trx_id, one can
emulate it in an application by creating an trx table:

create table trx(xid bigint auto_increment primary key) engine=innodb;

insert into trx values();

You can retrieve the inserted value with the mysql_insert_id() function that
is supported by all MariaDB/MySQL connectors. Alternatively you can use
insert into trx values() returning xid;.

To check if the commit succeeded, one just has to check that insert_id is
stored in the trx table. If it is, then the commit succeeded.

The disadvantage of the above approach is one extra round trip to get the value
and one has to occasionally truncate the trx table for old values.

Implementation:

When connecting to the MariaDB server with a connector that supports the
automatic transaction id (Flag MARIADB_CLIENT_TRANSACTION_VERIFY), the
server will in the connect package send a unique connection id to the
client (generated by uuid_short()) which the connector will remember.
The connector will also handle an commit counter that will be increased
for each commit (auto-commit or manual) sent to the server.

The trx_id value is a combination of the connect id and the commit
counter separated by ':' . As both the server and client knows the
current commit counter value there is no extra communication needed to
keep things in sync.

On commit the server will send the current gtid (replication id) to
the client for each commit. The connector will remember this value
for usage with the mariadb_trx_status() command.

The trx_id will be stored in the binary log on commit as 2
numbers. The trx_status() function will search the binary log for the
given xid_id value. If the status exist, then we know that the
transaction was committed on the current MariaDB (master or slave).

To speed up the search for the trx in the binary log, one can specify
the last known gtid to trx_status(). This is done automatically done
by the connector when using the connector mariadb_trx_status() function.

Changes in connector:

• The connector must remember the connection_id when connecting to the MariaDB server and set the commit counter to 1.
• On ok packets that does not have SERVER_STATUS_IN_TRANS flag set, which signals a commit happened, increment the commit counter.
• It connection fails during a statement, it should store the connection id, commit counter and last gtid. This is to be used for the next mariadb_trx_status() call. Any query call will reset the flags
• Implement a few new functions:
  • mariadb_trx_id()
  • mariadb_gtid();
  • mariadb_trx_status()
    • Which executes 'select trx_status("connection_id:commit_counter", gtid)' or optionally COM_TRX_STATUS. Note that trx_status() uses the same string that one gets from mariadb_trx_id() which is a ombination of connect_id and commit_counter.
  • mariadb_trx_kill(), which executes 'kill transaction "connection_id:commit_counter"¨
• mysql_options(connection, MYSQL_TRANSACTION_VERIFY, &on_off)
  • on_off is a bool variable that is set to 0 (disable) or 1 (enable)

Server changes:
Implement new functions (enabled if MARIADB_CLIENT_TRANSACTION_VERIFY is set for the connection)

• Sending connection_id on connect
• Sending gtid id as part of commit package
• trx_id()
• trx_status(trx_id, optional_gtid);
• Scan the binary log for the trx_id.
• * On a new master from a recent fail over should wait until the slave is up to date with the old master.
• Implement user variables (in THD):
  • @@trx_connect_id ; Generated by uuid_short() during connect
  • @@trx_commit_id ; Set to 1 at connect and incremented for each commit
    **Store the above variables in binary log on commit (together with GTID)
• Add new kill command: KILL TRANSACTION "connect_id:commit_id".
  • The kill will only go through if the user and host on the original connection and new connection are the same and the trx_id matches.

Changes in clients

• The mariadb client should be updated to call mariab_trx_status() on connection failure and inform the user of the status of the last transaction. In case of 'MARIADB_TRX_IN_PROGRESS' there should be an option to kill the transaction.

Changes in MaxScale:

• Remember the CONNECTION_ID and COMMIT_COUNTER for each connection
• In case of CHANGE USER, update the new user with his old connection_id
• and commit counter.
• Optionally:
  • Detect COM_TRX_STATUS calls and if the calls matches what MaxScale remembers then it can inform the client that the commit succeeded without asking the server.

Limitations

• One must have binary logging enabled for this feature to work.

Version:

• If implemented for 10.11 then MARIADB_CLIENT_TRANSACTION_VERIFY will be disabled by default. One can enable it in the configuration file for client and server if needed.
• In 13.2 and above it should be enabled by default.