[SA 7.1.228 Pro] - Schema Compare (MySQL)

[SA 7.1.228 Pro] - Schema Compare (MySQL)

The results of the schema comparison contained a strange character string.

What is this? Garbage got into retrieved code?

EDIT: that above was for tables. Stored procedure codes (apart from the keywords CREATE DEFINER... PROCEDURE schema_procname) entirely consist of similarly encoded "strings" (both the parameter list and the body).

The link to the screenshot seems to be broken

That's stranget. It works here. Could you try copy-pasting it into your browser, please?

http://s26.postimg.org/wx6k9ac8n/screenshot_2014_11_20_at_15_26_10.png

Thank you, I will let them know.

It looks like a problem with identifying column data type of the Comments column or reading it's context. Please execute the following "Tables" query I copied from SA settings and check if the output results appear normal. Please let us know if you spot something unusual

SELECT
t.TABLE_SCHEMA AS `Schema name`,
t.TABLE_NAME AS `Table name`,
'Table' AS `Target type`,
t.ENGINE AS `Engine`,
t.AUTO_INCREMENT AS `Auto increment`,
IF(t.AVG_ROW_LENGTH > 0, t.AVG_ROW_LENGTH, '') AS `Avg row length`,
t.TABLE_COMMENT as `Comment`,
IF(t.TABLE_COLLATION <> s.DEFAULT_COLLATION_NAME, t.TABLE_COLLATION, '') AS `Collation`,
IF(t.`CHECKSUM` IS NOT NULL, '1 ', '') AS `Checksum`,
UPPER(t.`ROW_FORMAT`) AS `Row format`,
IF(t.TABLE_TYPE = 'TEMPORARY', ' TEMPORARY ', '') AS `Temporary`,
t.CREATE_OPTIONS as `Check options`
FROM
information_schema.TABLES t
JOIN
information_schema.SCHEMATA s ON t.TABLE_SCHEMA = s.SCHEMA_NAME
WHERE
t.TABLE_TYPE IN ('BASE TABLE', 'TEMPORARY')
ORDER BY t.TABLE_NAME

Does the query posted in the previous message return binary data?

I'm sorry, I couldn't reply earlier, had no access to the servers until now.

And yes, I can see binary results in Awg row length. All the rest seems to be pretty normal.

Please make a correction in SA settings that will fix that issue.

Options - Schema Compare - MySQL - Schema Compare Queries for MySQL - Table - Query text

Code:

SELECT
t.TABLE_SCHEMA AS `Schema name`,
t.TABLE_NAME AS `Table name`,
'Table' AS `Target type`,
t.ENGINE AS `Engine`,
t.AUTO_INCREMENT AS `Auto increment`,
IF(t.AVG_ROW_LENGTH > 0, t.AVG_ROW_LENGTH, NULL) AS `Avg row length`,
t.TABLE_COMMENT as `Comment`,
IF(t.TABLE_COLLATION <> s.DEFAULT_COLLATION_NAME, t.TABLE_COLLATION, NULL) AS `Collation`,
IF(t.`CHECKSUM` IS NOT NULL, '1 ', NULL) AS `Checksum`,
UPPER(t.`ROW_FORMAT`) AS `Row format`,
IF(t.TABLE_TYPE = 'TEMPORARY', ' TEMPORARY ', NULL) AS `Temporary`,
t.CREATE_OPTIONS as `Check options`
FROM
information_schema.TABLES t
JOIN
information_schema.SCHEMATA s ON t.TABLE_SCHEMA = s.SCHEMA_NAME
WHERE
t.TABLE_TYPE IN ('BASE TABLE', 'TEMPORARY')
AND ('$SCHEMA_NAME$' = '' OR t.TABLE_SCHEMA = '$SCHEMA_NAME$')
ORDER BY t.TABLE_NAME

Options - Schema Compare - MySQL - Schema Compare Templates for MySQL - Table (CREATE) - Template text

Code:

CREATE $TEMPORARY$TABLE `$SCHEMA_NAME$`.`$OBJECT_NAME$`
(
$,\n\t|COLUMN=COLUMN DEFINITION,PRIMARY KEY=PRIMARY KEY DEFINITION,UNIQUE KEY=UNIQUE KEY DEFINITION,FOREIGN KEY=FOREIGN KEY DEFINITION$
)$ENGINE {ENGINE}$$
AUTO_INCREMENT = {AUTO INCREMENT}$$
AVG_ROW_LENGTH = {AVG ROW LENGTH}$$
COMMENT = '{COMMENT}'$$
COLLATE = {COLLATION}$$
CHECKSUM = {CHECKSUM}$$
ROW_FORMAT = {ROW FORMAT}$$
{TEMPORARY}$$
{CREATE OPTIONS}$;

$\n\n|DML TRIGGER=TRIGGER (CREATE)$

Not completely sure if this this fix is compatible with 7.1.228, I couldn't test the fix, my test system is already running a more recent build, but I think it's worse trying. The fix was implemented in 7.1.237 internal build, which is currently considered a candidate for a new public beta release, slated for later this week.

After applying modifications I still get the garbage instead of normal code but this time on target server only. By the way, there aren't any indications which is which, it shows MySQL for both Source and Target and nothing else useful that could help me identify the server itself. Granted that they are of different versions (5.1.40 and 5.6.15) I guess the issue might be version dependent.

	Quote:
	By the way, there aren't any indications which is which, it shows MySQL for both Source and Target

We are working on fixing this issue.