I posted this in the other thread too, but thought will post it here and it may help someone. The below tool not only finds all of the differences but produces the exact ALTER / CREATE syntax that is used by Singlestore, including the sharding, indexes, table types.
The best tool I found for this, believe it or not, is the perl mysqldiff utility (part of MySQL::Diff module). This is not to be confused with a similarly named MySQL utility. I couldnât really find anything else that does a good job.
Check this out:
(python) [ec2-user@prod-jump-1_10-1-1-5 db]$ mysqldiff --keep-old-tables -h memsql-prod -u proof -p $PASSWORD_DB_PT trading_hist trading
## mysqldiff 0.60
##
## Run on Tue Apr 6 17:47:13 2021
## Options: user=proof_post_trade, debug=0, keep-old-tables, host=memsql-prod.prooftrading.com
##
## --- db: trading_hist ('host=memsql-prod' 'user=proof')
## +++ db: trading ('host=memsql-prod' 'user=proof')
ALTER TABLE Venue ADD COLUMN auctionCollarBps smallint(6) DEFAULT NULL;
ALTER TABLE ClientConnection ADD COLUMN senderSubId varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE ClientConnection ADD COLUMN targetSubId varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;
CREATE TABLE KillSwitch (
sbeClassName varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
sbeVersion int(11) DEFAULT NULL,
sessionId smallint(6) DEFAULT NULL,
sequenceTime timestamp(6) NULL DEFAULT NULL,
sequenceTime_nanos bigint(20) DEFAULT NULL,
sequence bigint(20) DEFAULT NULL,
sourceType smallint(6) DEFAULT NULL,
sourceInstance smallint(6) DEFAULT NULL,
sourceId int(11) DEFAULT NULL,
sourceSequence bigint(20) DEFAULT NULL,
active varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
checkString varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
note varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
KEY sequenceTime_nanos (sequenceTime_nanos) USING CLUSTERED COLUMNSTORE
, SHARD KEY ()
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES';