Description:
Three options are given at the bottom of this page (at least when MS SQL Server is the source database and MySQL the target, and presumably for others as well):
• Keep schemata as they are: Catalog.Schema.Table -> Schema.Table
• Only one schema: Catalog.Schema.Table -> Catalog.Table
• Only one schema, keep current schema names as a prefix: Catalog.Schema.Table -> Catalog.Schema_Table
These three options do not suffice for many situations, most especially including migrating directly to a shared hosted MySQL server using a user account with full database owner (DBOwner) but no system administrator (SysAdmin) privileges.
In particular, since in MySQL 5.x (and presumably before) the term “SCHEMA” is simply a synonym for “DATABASE” rather than allowing multiple schemata per database (a feature that really should be implemented in MySQL 6, but that’s another subject for another entry), the “DROP SCHEMA IF EXISTS” and “CREATE SCHEMA IF NOT EXISTS” MySQL statements created for the migration are exactly the same as “DROP DATABASE IF EXISTS” and “CREATE DATABASE IF NOT EXISTS” in every respect, and which, for obvious reasons, are only available to full-fledged SysAdmins, not mere DBOwners.
Most shared hosting servers only grant users DBOwner-level privileges, not SysAdmin, again for obvious reasons. To actually create or delete a database, the user must use the hosting control panel (e.g. CPanel), which executes the commands behind the scenes using SysAdmin privileges. Of course, this simply won’t do for the scripts produced by MySQL Workbench Migration.
While those can be commented out in the later stages, there are other issues as well. Each object (Table, etc.) is written as fully qualified in the “CREATE TABLE …” statements, e.g. “CREATE TABLE `mySchema`.`myTable`…” if the first of option is chosen, or “CREATE TABLE `mySourceDBname`.`myTable`…” if the second option is chosen. If the database on the destination that was created by the CPanel (or whatever) isn’t exactly the same name as either the source database or schema, then those statements will fail! And often, the person doing the migration has no ability or privileges to rename either database or the source schema!
The only feasible workarounds that I know of involve installing one’s own local copy of either the source or target databases (used for staging) or both, complete with installing the whole database engine(s) on the local or other in-house machine, so that the user does indeed have full SysAdmin privileges and can rename either the source database or schema, or the target MySQL database (=schema), to match.
Granted, a database developer who does this often should indeed already have this sort of thing set up for development and staging, but for a casual user who just wants to migrate a database one time and hand it off to another developer, this forces him or her jump through excessive hoops to get the job done.
How to repeat:
(N/A)
Suggested fix:
How about adding a fourth and fifth option?
• Ignore catalog and schemata entirely: Catalog.Schema.Table -> Table (useful if Target is shared hosting or for other restricted privilege situations)
• Ignore catalog, use schema names as a prefix: Catalog.Schema.Table -> Schema_Table (ditto)
Selecting either of those options would prevent the DROP and CREATE SCHEMA statements from even being generated. All CREATE TABLE (and VIEW and other object) statements would simply have the Table (or View or whatever) name (with or without schemata-as-prefix depending on which option), unqualified by a database name (it would simply use the database name given when the Target was set up earlier in the wizard). So, in the above example, the generated CREATE statement for Table “myTable” would simply read “CREATE TABLE `myTable` …” or “CREATE TABLE `mySchema_myTable` …”, respectively (this assumes that the proper Target database already exists and has already been established as the default schema).
The remainder of the wizard would work as before.
