MySQL Bugs: #36670: Migrating Large Union Query (Views) SQL is truncated

Bug #36670	Migrating Large Union Query (Views) SQL is truncated
Submitted:	12 May 2008 15:53	Modified:	19 Oct 2009 10:01
Reporter:	Bryn Parrott	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.1.17	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, Migration Toolkit, Views

Description:
Task: Migrating a database from SQL Server 2005 to MySQL Community Edition.

When Migratioin Toolkit migrates Views, it will write out an SQL script that contains the SQL text of the views, but they are all commented out, and require manual intervention to ensure that the intent of the view is translated correctly.

Thats fine, however I have found that when a view consists of a large Union query with 5 select statements (each consisting of 17 lines of SQL) contained within, that the text of the SQL statement copied from the SQL Server instance is truncated. The truncation seems to occur anywhere, even in the middle of a word or quoted string.
I have several such views in the source DB. The view topmost in the file just had the last couple of lines chopped off. It gets progressively worse as I go down the file reviewing and fixing up the SQL. At first, the issue seemed to affect the 'FROM' clause in the select statement, and left the 'Where' clause following unaffected. That is, until the last one.
The bottom most view had approx 20 lines chopped off meaning that one whole segment of the union query went missing, and I had to type it in manually.

This problem does not affect the smaller views which are interspersed in the file, some in between the larger views.

How to repeat:
It will be a lot of work to repeat this.

1/. Create a large Union query in a SQL 2005 database.
Make sure this has lots of lines of code and several select statements within the Union.
Need to have several of these. The DB on which I found this issue has 20 such views.

2/. Attempt to use the Migration toolkit to transfer the views into a MySQL database 5.0.x Save the script file it produces.
Look at the sql produced (with comments) and confirm that each large view is truncated to some degree.

If you need my files to attempt to reproduce well then we may have problems, due to a) they contain real client sensitive data and b) the files are physically large.

Suggested fix:
looks like a buffer overflow condition, possibly when writing out the converted SQL script or possibly when sucking it in from SQL server.

Thank you for the bug report. Could you please provide a test case database so we can to attach it to our SQL Server?. Thanks in advance.

Not so easy,

Please read read my comments at the end of the report.

The SQL 2005 database is 40 M in size, so I cant email it to you, and also it contains data confidential to my client.  Short story: you are not getting this data.

test sql

Attachment: bug36670.sql (text/x-sql), 14.02 KiB.

Thank you for the feedback.

Verified as described using MySQL server for source database and attached SQL create script for view.

Sveta, the attached SQL does not reproduce the case which was reported. Instead it shows a server bug which prevents the migration from happening. See Bug #40277. Please verify again with data that shows this bug's problem. I'd be glad to use this data then to fix the bug. Thank you.

Verified as described.

It doesn't matter with source RDBMS you are using. When you want to migrate views then all views just are commented out.

Verified as described by using actual version.

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Migration Tool into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/