Hi Team
is there any tool to compare 2 database objects in memsql i am using dbeaver it has the option to have a simple compare but not showing all the options I need to check is there a table or procedure change in both schemes…
What kind of comparison do you want to do, exactly?
I expect that any tool that looks at information_schema information about tables and columns (basic mysql-compatible stuff) will work to find changes like adding a new column, but it would understand things like columnstore indexes in SingleStore, so it won’t be a complete solution.
There is nothing out there we know of that can compare a SingleStore schema for changes in stored procedures or functions.
MySQL workbench or TOAD might be able to compare basic objects, like tables, for changes.
yes like compare like table structure and procedure available from both scheme… like
Dev to Prod 2 schema comparing
Okay, I understand what you’re looking for.
is there any tool? to compare and to the schema
I wrote a schema compare/update utility for my company because I couldn’t find such a utility elsewhere. Unfortunately, I can’t share all of the code, but basically, after you enumerate your database objects, you can call SHOW CREATE [object] on the source and target databases, then compare the DDL.
For objects that exist in the source db but not the target, you can simply execute the CREATE statement DDL from the source on the target database. For objects that exist in the target db but not the source, you can execute a DROP statement on the target database.
For functions, views, and stored procedures, if there’s a difference in the DDL, you can simply drop and create the object in the target database (because there’s no data loss with those objects). For tables, CREATE (if exists in source but not target) and DROP (if exists in target but not source) are simple, but if there are differences requiring ALTER TABLE statements, that requires more logic.
Here are a few queries that might help you get started. I use these queries to enumerate the database objects:
public List<string> GetTables(string database)
{
string sql = String.Format("SELECT TABLE_NAME AS `Item` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME;", database);
return GetItemsFromQuery(sql);
}
public List<string> GetViews(string database)
{
// For some reason (perhaps because functions are referenced in some of our
// sprocs in JOIN statements) some of our functions are being returned in the
// query for views. So, strip out anything that says it's a view but has a function
// with a matching name.
string sql = String.Format("SELECT v.TABLE_NAME AS `Item` FROM INFORMATION_SCHEMA.VIEWS v WHERE v.TABLE_SCHEMA = '{0}' AND NOT EXISTS(SELECT f.ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES f WHERE v.TABLE_NAME = f.ROUTINE_NAME) ORDER BY v.TABLE_NAME;", database);
return GetItemsFromQuery(sql);
}
public List<string> GetStoredProcedures(string database)
{
string sql = String.Format("SELECT ROUTINE_NAME AS `Item` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME;", database);
return GetItemsFromQuery(sql);
}
public List<string> GetFunctions(string database)
{
string sql = String.Format("SELECT ROUTINE_NAME AS `Item` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME;", database);
return GetItemsFromQuery(sql);
}
Hope this helps. Best, Kevin
2 Likes
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.
This tool not only finds all of the differences but produces the exact CREATE syntax that is used by Singlestore.
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';