[SA 9.1.261 Pro] - Bug: Schema comparison

[SA 9.1.261 Pro] - Bug: Schema comparison

When comparing MySQL schemas using the factory default settings I get strange results for stored procedures (see screenshot):

It looks like the characters of contents of the body and parameters columns are turned into their corresponding ascii code?

Adding some casts to the original code seems to fix this issue:

Code:

SELECT
db AS `Schema name`,
name AS `Function name`,
CASE TYPE
WHEN 'FUNCTION' THEN 'Function'
ELSE 'Procedure'
END,
CONCAT('''',REPLACE(`DEFINER`, '@', '''@'''),'''') AS `Definer`,
cast(param_list as char) AS `Param list`,
returns AS `Returns`,
REPLACE(sql_data_access, '_', ' ') AS `SQL data access`,
SECURITY_TYPE AS `Security type`,
comment AS `Comment`,
cast(body as char) AS `Body`
FROM
mysql.proc
WHERE
('$SCHEMA_NAME$' = '' OR db = '$SCHEMA_NAME$')
ORDER BY name

Thank you for reporting this. Please make a minor change in the options.

In Schema Compare -> MySQL ->Schema Compare Queries for MySQL -> Procedures and Functions, replace the following

param_list -> cast(param_list as char)
returns -> cast(returns as char)
body -> cast(body as char)

That should fix the display of the procedure code and parameters

So Returns needs that too? Any hints on what causes that? I'm not sure this casting is helping readability or performance (in case it affects it in any way).

The 3 columns in question are defined in the source table as longblob that's why their context is displayed in binary format if not explicitly casted as char. Were they defined as longtext that wouldn't be needed.

I'm not sure why they're defined as longblob. Perhaps to support encryption in the future.

That would eggsplain (again) this one but I recall plain varchar columns ending in that hex format when eggsposed (sorry) to CONCAT function. Also, the same query shows plain text when run in command line myqsl client.