MySQL Specific Information
Common Issues
When testing with the mysql commandline client, use -A to connect. This avoids an issue where the client is itself using a deprecated feature the MySQL protocol that can trigger issues in a proxy environment.
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. Here is an example configuration for the my.cnf that can help avoid any such issues:
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4
In addition to this however, it is also important to set the character set of the database itself to utfmb4:
ALTER DATABASE <dbname> DEFAULT COLLATE utf8_unicode_ci;
If all of these are not in alignment, then various character encoding issues may occur.
One further impact--if a database is set to utf8 instead of utf8mb4, most functions may appear to work properly, BUT it can impact performance. If an index is done on a column that is coded in utf8, but the query is received in utf8mb4, then the index may not be used as part of the optimization. This can be a very subtle and difficult issue to track down, but using "explain" on the query will show the difference in the execution plan with and without heimdall, but it will not say why the index is not used.
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 dev.mysql.com for more information.
sql_mode
If a custom sql mode is needed, it can be set in the data source properties with the key of "sessionVariables" and a value of “sql_mode='whatever'“.
MariaDB
Due to changes in the version string and how the Oracle 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:
- dbTriggerTracking=true
- dbChanges=select ts,name from heimdall.table_change where ts > @ts
- dbTimeMs=select get_time_ms()
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