Database objects are identified by name, check is case-insensitive, so for example table myTable vs MyTable is considered as the same table.
All objects except Events and Sequences are built by reading object properties from different system metadata tables and views. Application supports the most commonly used object attributes. However in many cases it is only the subset of MySQL/MariaDB specifications, and the application does not support some MySQL/MariaDB language features. Such features are not recognized and if an object has changes in any non-supported attribute then such change is ignored. In case of object merge these attributes can be lost in the target database. Below you will find the information about supported/unsupported features.
The Standard version also allows you to compare results of SHOW CREATE TABLE results, this can be useful if you want to check how a database server generates table scripts. There is an appropriate button in the Table structure diff tab. For any other object (FUNCTION, etc.) you can compare SHOW CREATE .. results using the Query result diff tab.
All objects are compared as their text presentation, that's the text you observe if you open an object in a Text diff tab. In the Standard you can set up text diff options to ignore some general text-related changes like case-insensitive or ignore-whitespace. Application also allows additional custom text normalization to skip some changes that may look false-positive. For example, you can specify to ignore optional function parenthesis, to make the function call CURRENT_DATE to be considered as unchanged compared to the CURRENT_DATE() function call (with vs without parenthesis).
Here is the high-level presentation of supported/unsupported table definition attributes, based on MySQL and MariaDB CREATE TABLE specifications. Unsupported ones are shown as crossed out:
CREATE TABLE tbl_name
(create_definition,...)
[table_options]
[partition_options]
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
| period_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
[AUTO_INCREMENT] [ZEROFILL]
[{WITH|WITHOUT} SYSTEM VERSIONING]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[REF_SYSTEM_ID = value]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name] [GENERATED ALWAYS] AS (expr)
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[COMMENT 'string']
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
Note that this definition excludes [UNIQUE [KEY]] [[PRIMARY] KEY] of column_definition because scripts for constraints are generated as parts of create_definition.
Supported table_options are COMMENT, CHARSET, COLLATE, ENGINE, and ROW_FORMAT. If CHARSET, COLLATE, and ENGINE match the database defaults, these options are excluded from the UI and script generation (because it is not possible to determine whether these options were explicitly specified during CREATE TABLE or not).For Views application recognizes only select_statement and ignores ALGORITHM, DEFINER, SQL SECURITY and CHECK OPTION options. Database engine does not store view SELECT statement formatting, application uses its own SQL indentation logic to display view text.
Application ignores DEFINER statements for these object types.
For Functions and Stored procedures the application supports these characteristics, based on the MySQL CREATE PROCEDURE and CREATE FUNCTION specification. Unsupported ones are shown as crossed out:
characteristic: {
COMMENT 'string'
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| LANGUAGE { SQL | JAVASCRIPT }
}
Characteristics are part of the object text, so a change of any of them makes the object changed. Default values (NOT DETERMINISTIC, CONTAINS SQL and SQL SECURITY DEFINER) are excluded from the object text.
If only COMMENT, SQL SECURITY or the data access characteristic (CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA) is different, the object is merged with ALTER PROCEDURE or ALTER FUNCTION. In all other cases, for example if DETERMINISTIC or the body is different, the object is dropped and created again.
MariaDB 12.0 and newer allow a trigger to react only to changes in some columns: UPDATE OF column_name, .... The application supports this for MariaDB 12.2 and newer. MariaDB 12.0 and 12.1 do not show the list of these columns in the system metadata (the INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS view was added in 12.2), so for these two versions the application can not read the column list and shows such a trigger as a plain UPDATE trigger.
Event definitions are retrieved using SHOW CREATE EVENT statements, followed by some adjustments and formatting. DEFINER clause is excluded, to be unified with all other object types which do not support it.
Sequence definitions are retrieved using SHOW CREATE SEQUENCE statements with some additional formatting.
To apply some changes, the application re-creates an object: it drops the object in the target database and creates it again. After that, some things that belonged to the object may no longer be there. This can include dependent objects and settings that are turned off in Settings, and features that the application does not support.
When a merge script re-creates objects, the application shows a warning in the Execute script dialog. If your database has something that the application does not read, please save it before the merge and add it back afterwards.