Better MySQL bootstrap setup for datomic's datomic_kvs table

datomic-mysql-bootstrap.sql

-- Optimized MYSQL schema for datomic
-- Unfortunately the bin/sql/mysql-*.sql bootstrapping files for datomic are not
-- very good, and can actually cause failures if not adjusted.
-- One symptom of this is the following error:

-- SQL Error (1071): Specified key was too long; max key length is 767 bytes.

-- Reported here: https://support.cognitect.com/entries/28462168-MySQL-Caveats

-- This is caused by the default collation for the `id` column possibly being
-- too long in bytes.
-- It is unknown what the range in `rev` and `map` may be, so we expand the
-- `map` charset to all possible unicode characters (utf8 collation only handles
-- BMP characters i.e. 3 utf8 bytes) and leave `rev` alone. Possibly it could
-- be either narrower or unsigned.

-- Also, we create different users for the transactor and peer with minimal
-- permissions. You don't have to use these but it's not a bad idea.

CREATE DATABASE IF NOT EXISTS datomic;

-- Create table. Charset and collations are important to reduce index size.

CREATE TABLE IF NOT EXISTS `datomic`.`datomic_kvs` (
  `id` VARCHAR(640) CHARACTER SET 'ascii' COLLATE 'ascii_bin' NOT NULL,
  `rev` INT,
  `map` TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin',
  `val` LONGBLOB,
  PRIMARY KEY (`id`)) ENGINE=INNODB;

-- You may also want to experiment with ROW_FORMAT=DYNAMIC which may handle
-- datomic's large blobs better.

-- Datomic users. Set a stronger password here or use SSL authentication.
-- Not sure if necessary to specify ssl+cert required, or if google cloud
-- mysql with "ssl required" takes care of it for all users.

-- Transactor user: read-write permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON datomic.* TO datomic_txr@'%' IDENTIFIED BY 'securepassword';
-- Peer user: read-only permissions
GRANT SELECT ON datomic.* TO datomic_peer@'%' IDENTIFIED BY 'securepassword';