MySQL Specific Information

Common Issues

One of the most common issues with MySQL is the character-set being used by the database. As JDBC attempts to negotiate with the server, if the server is set to anything other than UTF8MB4 (along with the tables) this can cause issues with certain characters not being handled properly.

Several MySQL specific behaviors are included and detected by Heimdall, such as:

SELECT SQL_CALC_FOUND_ROWS: queries with this pattern will be treated as updates.

Using the "getBoolean()" method on a string type will look at the first character and match per the logic used in the MySQL driver, i.e. any string starting with "y" will test as "yes" or true. Same with "t".

Known Incompatibility: There is a rare but known issue when using non-prepared statements when inserting binary data using an insert command. In this configuration, the insert uses inline binary data in what is otherwise a UTF-8 string. When received, the entire string is processed as UTF-8, and results in the binary data being modified when transmitted from the proxy to the database. This problem is NOT observed if using true server-side prepared statements for the insert. If you observe issues where binary data appears corrupted, and you believe you are using a prepared statement, please check if your driver is implicitly converting prepared statements into "client" side prepared statements, as most DO do this for performance reasons. For example, with the MySQL JDBC driver, you will need to add the option "useServerPrepStmts=true". Please see mysql.com for more information.

MariaDB

Due to changes in the version string and how the MySQL driver interacts with the MySQL server, when using MariaDB, it is advised to set it to use a version string such as "5.7.19-10.2.22-MariaDB". The first part is the MySQL compatibility version--if this string is higher than either 5.6.19, or 8.0.3, then this may trigger an error about an invalid counter of "transaction_isolation". Please see the MariaDB documentation for information on how to change this setting.

Trigger Invalidation with MySQL

Connection properties:

Script to configure MySQL for Trigger expiry:

#!/bin/bash

database=tpch
host=127.0.0.1
port=3306
user=root
password=secret
command="mysql -N --force --host=$host --port=$port --user=$user --password=$password"

(
# setup the heimdall table for all Heimdall activities
echo "DROP DATABASE IF EXISTS heimdall;
CREATE DATABASE heimdall;
USE heimdall;

DELIMITER \$\$

#----------code for MySQL (including pre-5.6 versions) to get the current time to the ms level---------
DROP FUNCTION IF EXISTS get_time_ms\$\$
CREATE DEFINER=root@localhost FUNCTION get_time_ms() 
  RETURNS BIGINT
  READS SQL DATA
  NOT DETERMINISTIC
BEGIN
 DECLARE time_ms BIGINT;
 SELECT conv( 
  concat(
   substring(uid,16,3), 
   substring(uid,10,4), 
   substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000)
  into time_ms FROM (select uuid() uid) as alias;
 RETURN time_ms;
END\$\$

#---------code for trigger based expiry--------
CREATE TABLE table_change (
   ts BIGINT DEFAULT 0,
   name VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin,
   updates INT DEFAULT 0,
   inserts INT DEFAULT 0,
   deletes INT DEFAULT 0,
   PRIMARY KEY (name)) ENGINE = MEMORY\$\$

DROP PROCEDURE IF EXISTS changes\$\$
CREATE DEFINER=root@localhost PROCEDURE changes(time BIGINT)
BEGIN
    SELECT ts,name,updates,inserts,deletes from table_change where ts >= time;
END\$\$

DROP PROCEDURE IF EXISTS track_changes\$\$
CREATE DEFINER=root@localhost PROCEDURE track_changes(table_name VARCHAR(64), type VARCHAR(16))
BEGIN
    CASE type
    WHEN 'update' THEN INSERT INTO heimdall.table_change VALUES (get_time_ms(), table_name, 1, 0, 0) ON DUPLICATE KEY 
        UPDATE updates=updates+1,ts=get_time_ms();
    WHEN 'insert' THEN INSERT INTO heimdall.table_change VALUES (get_time_ms(), table_name, 0, 1, 0) ON DUPLICATE KEY 
        UPDATE inserts=inserts+1,ts=get_time_ms();
    WHEN 'delete' THEN INSERT INTO heimdall.table_change VALUES (get_time_ms(), table_name, 0, 0, 1) ON DUPLICATE KEY 
        UPDATE deletes=deletes+1,ts=get_time_ms();
    END CASE;
END\$\$
USE $database\$\$
DROP USER 'heimdall'@'%'\$\$
CREATE USER 'heimdall'@'%' IDENTIFIED BY 'heimdalldemo'\$\$
GRANT EXECUTE on heimdall.* to 'heimdall'@'%'\$\$
GRANT SELECT on tpch.* to 'heimdall'@'%'\$\$
FLUSH PRIVILEGES\$\$
"

echo "show tables" | $command $database | while read table; do

if [ "$table" != "table_change" -a "n$table" != "n" ]; then
    echo "INSERT INTO heimdall.table_change (ts,name) values (UNIX_TIMESTAMP()*1000,'${database}.${table}')\$\$"

        echo "DROP TRIGGER IF EXISTS ${database}.${table}_update\$\$
        CREATE TRIGGER ${database}.${table}_update AFTER UPDATE ON ${database}.${table} FOR EACH ROW 
            CALL heimdall.track_changes('${database}.${table}', 'update')\$\$"

        echo "DROP TRIGGER IF EXISTS ${database}.${table}_insert\$\$
            CREATE TRIGGER ${database}.${table}_insert AFTER INSERT ON ${database}.${table} FOR EACH ROW 
            CALL heimdall.track_changes('${database}.${table}', 'insert')\$\$"

        echo "DROP TRIGGER IF EXISTS ${database}.${table}_delete\$\$
            CREATE TRIGGER ${database}.${table}_delete AFTER DELETE ON ${database}.${table} FOR EACH ROW 
            CALL track_changes('${database}.${table}', 'delete')\$\$"
fi

done )| $command