SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.261 Pro] - Bug: Schema comparison

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.261 Pro] - Bug: Schema comparison
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.1.261 Pro] - Bug: Schema comparison Reply with quote
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

Thu Apr 06, 2017 7:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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
Thu Apr 13, 2017 10:15 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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).
Fri Apr 14, 2017 1:05 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Fri Apr 14, 2017 3:29 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I'm not sure why they're defined as longblob. Perhaps to support encryption in the future.
Fri Apr 14, 2017 3:30 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Sat Apr 15, 2017 3:20 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.