Created
January 22, 2015 17:49
-
-
Save favila/ecdcd6c4269ff2bd1bb3 to your computer and use it in GitHub Desktop.
Better MySQL bootstrap setup for datomic's datomic_kvs table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment