MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL 5.0 migration bug

without comments

At present, you can’t use the MySQL Workbench migration tool to migrate MySQL 5.0 to MySQL 5.5, as documented in Bug 66861. The only documentation reference that I could find that references the mysql.proc table. Since the physical definition of the mysql.proc table changes across the MySQL 5.0, 5.1, and 5.6 releases, I modified my documentation Bug 66886 to suggest providing online documentation (as a feature request) for the mysql, information_schema, and performance_schema tables across all releases.

The actual definition of the mysql.proc table for MySQL 5.0.91 holds 16 columns not 20 columns as presently expected by the MySQL Workbench migration tool, and is summarized below:

Field Type Null Key
db char(64) NO PRI
name char(64) NO PRI
type enum(‘FUNCTION’,’PROCEDURE’) NO PRI
specific_name char(64) NO  
language enum(‘SQL’) NO SQL
sql_data_access enum(‘CONTAINS_SQL’,…) NO CONTAINS_SQL
is_deterministic enum(‘YES’,’NO’) NO NO
security_type enum(‘INVOKER’,’DEFINER’) NO DEFINER
param_list blob NO  
returns char(64) NO  
body longblob NO  
definer char(77) NO  
created timestamp NO CURRENT_TIMESTAMP
modified timestamp NO 0000-00-00 00:00:00
sql_mode set(‘REAL_AS_FLOAT’,…) NO  
comment char(64) NO  

I found out about the issue through a comment on my blog from Marc, who was trying to migrate his production instance. I hope this provides a heads-up to anybody else attempting to migrate a MySQL 5.0 database to a MySQL 5.5. The good news is that the MySQL Workbench team appears to be actively working the issue.

Written by maclochlainn

September 20th, 2012 at 12:47 am