Description:
When trying to migrate a MySQL database to a new MySQL schema using MySQL Workbench the process fails when trying to copy the data from a table that has 257 columns. The create statement is executed OK, but the copy from the existing MySQL table step fails. You can see from the log file below that the copy statement is truncated in the middle of a column name (after 2048 characters in the temporay file containing the list of columns etc. to be copied):-
Starting...
Prepare information for data copy...
Prepare information for data copy done
Determine number of rows to copy....
Counting number of rows in tables...
wbcopytables.exe --count-only --passwords-from-stdin --mysql-source=admin@ho-appmysql01:3306 --table-file=c:\users\anita\appdata\local\temp\tmp3hqlrf
--table `human_resources` `trainingevaluationstats`
10:42:53 [INF][ copytable]: Connecting to MySQL server at ho-appmysql01:3306 with user admin
10:42:53 [INF][ copytable]: Connection to MySQL opened
6 total rows in 1 tables need to be copied:
- `human_resources`.`trainingevaluationstats`: 6
Determine number of rows to copy finished
Copy data to target RDBMS....
Migrating data...
wbcopytables.exe --mysql-source=admin@ho-appmysql01:3306 --target=admin@ho-appmysql01:3306 --progress --passwords-from-stdin --log-level=debug3 --thread-count=1 --table-file=c:\users\anita\appdata\local\temp\tmptupxxe
\users\anita\appdata\local\temp\tmptupxxe
ERROR: `human_resources_ajm`.`trainingevaluationstats`:mysql_stmt_prepare: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`' at line 1
`human_resources_ajm`.`trainingevaluationstats`:Finished copying 0 rows in 0m00s
42:54 [INF][ copytable]: --table `human_resources` `trainingevaluationstats` `human_resources_ajm` `trainingevaluationstats` `Directorate`, `CourseTitle`, `Evaluations`, `LO1Y`, `LO1N`, `LO2Y`, `LO2N`, `LO3Y`, `LO3N`, `NOEVAL`, `K11`, `K12`, `K13`, `K14`, `K15`, `K16`, `K11P`, `K12P`, `K13P`, `K14P`, `K15P`, `K16P`, `K17P`, `K21`, `K22`, `K23`, `K24`, `K25`, `K26`, `K21P`, `K22P`, `K23P`, `K24P`, `K25P`, `K26P`, `K27P`, `K31`, `K32`, `K33`, `K34`, `K35`, `K36`, `K31P`, `K32P`, `K33P`, `K34P`, `K35P`, `K36P`, `K37P`, `K41`, `K42`, `K43`, `K44`, `K45`, `K46`, `K41P`, `K42P`, `K43P`, `K44P`, `K45P`, `K46P`, `K47P`, `K51`, `K52`, `K53`, `K54`, `K55`, `K56`, `K51P`, `K52P`, `K53P`, `K54P`, `K55P`, `K56P`, `K57P`, `K61`, `K62`, `K63`, `K64`, `K65`, `K66`, `K61P`, `K62P`, `K63P`, `K64P`, `K65P`, `K66P`, `K67P`, `K71`, `K72`, `K73`, `K74`, `K75`, `K76`, `K71P`, `K72P`, `K73P`, `K74P`, `K75P`, `K76P`, `K77P`, `K81`, `K82`, `K83`, `K84`, `K85`, `K86`, `K81P`, `K82P`, `K83P`, `K84P`, `K85P`, `K86P`, `K87P`, `K91`, `K92`, `K93`, `K94`, `K95`, `K96`, `K91P`, `K92P`, `K93P`, `K94P`, `K95P`, `K96P`, `K97P`, `R11`, `R12`, `R13`, `R14`, `R15`, `R16`, `R11P`, `R12P`, `R13P`, `R14P`, `R15P`, `R16P`, `R17P`, `R21`, `R22`, `R23`, `R24`, `R25`, `R26`, `R21P`, `R22P`, `R23P`, `R24P`, `R25P`, `R26P`, `R27P`, `R31`, `R32`, `R33`, `R34`, `R35`, `R36`, `R31P`, `R32P`, `R33P`, `R34P`, `R35P`, `R36P`, `R37P`, `R41`, `R42`, `R43`, `R44`, `R45`, `R46`, `R41P`, `R42P`, `R43P`, `R44P`, `R45P`, `R46P`, `R47P`, `R51`, `R52`, `R53`, `R54`, `R55`, `R56`, `R51P`, `R52P`, `R53P`, `R54P`, `R55P`, `R56P`, `R57P`, `R61`, `R62`, `R63`, `R64`, `R65`, `R66`, `R61P`, `R62P`, `R63P`, `R64P`, `R65P`, `R66P`, `R67P`, `R71`, `R72`, `R73`, `R74`, `R75`, `R76`, `R71P`, `R72P`, `R73P`, `R74P`, `R75P`, `R76P`, `R77P`, `R81`, `R82`, `R83`, `R84`, `R85`, `R86`, `R81P`, `R82P`, `R83P`, `R84P`, `R85P`, `R86P`, `R87P`, `R91`, `R92`, `R93`, `R94`, `R95`, `R96`, `R91P`, `R92P`, `R93P`, `R94P`, `R95P`, `R96P`, `R97P`, `R101`, `R102`, `R103`, `R104`, `R105`, `R106`, `R101P`, `R102P`, `R103P`, `R104P`, `R10
42:54 [INF][ copytable]: Connecting to MySQL server at ho-appmysql01:3306 with user admin
42:54 [INF][ copytable]: Connection to MySQL opened
42:54 [DB1][ copytable]: Detected server version=5.1.49-community
42:54 [DB1][ copytable]: Detected max_allowed_packet=1048576
42:54 [DB1][ copytable]: Retrieving trigger list
42:54 [INF][ copytable]: Connecting to MySQL server at ho-appmysql01:3306 with user admin
42:54 [INF][ copytable]: Connection to MySQL opened
42:54 [INF][ copytable]: Connecting to MySQL server at ho-appmysql01:3306 with user admin
42:54 [INF][ copytable]: Connection to MySQL opened
42:54 [DB1][ copytable]: Detected server version=5.1.49-community
42:54 [DB1][ copytable]: Detected max_allowed_packet=1048576
42:54 [INF][ copytable]: Re-enabling triggers for schema 'human_resources_ajm'
42:54 [DB1][ copytable]: Retrieving trigger definitions
42:54 [INF][ copytable]: No triggers found for 'human_resources_ajm'
Copy helper has finished
Data copy results:
- `human_resources_ajm`.`trainingevaluationstats` has FAILED (0 of 6 rows copied)
0 tables of 1 were fully copied
Copy data to target RDBMS finished
Tasks finished with warnings and/or errors, view the logs for details
Finished performing tasks.
How to repeat:
Create a MySQL table where the number of columns and length of the column names etc. are over 2048 characters when the temporay file is created by MySQL Workbench.
Use the database migration option to copy this table to another MySQL database.
Suggested fix:
Increase this limit, or use an alternative method to copy the table data as the create statement has already been executed successfully.
