MySQL Bugs: #69779: Export fails for Excel files containing

Bug #69779	Export fails for Excel files containing > 4000 characters of text per cell
Submitted:	18 Jul 2013 13:01	Modified:	26 Sep 2013 9:34
Reporter:	Rob Sherratt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	5.6	OS:	Windows (Excel 2013)
Assigned to:		CPU Architecture:	Any
Tags:	BLOB, Excel, export, fail, text

Description:
When using "Export Data" from Excel to create a MySQL table, if any of the user's excel spreadsheet cells contain > 4000 characters of text, the export fails with the following message: 

"MySQL Error 1118: 
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs" 

When there is > 4000 characters of text in a cell, the exporter sets the corresponding column datatype to VARCHAR(65535). This is a "bug" - because when exporting, the above error message will always happen, and the export fails. 

 

How to repeat:
When using "Export Data" from Excel to create a MySQL table, if any of the user's excel spreadsheet cells contain > 4000 characters of text, the export fails.

Suggested fix:
The MySQL for Excel plug-in needs to provide the capability to set the datataype of the columns to TEXT or BLOB instead of VARCHAR(65535).

Also when using the "Import MySQL Data, the Excel Plug-in needs to be modified so that TEXT and BLOB datatypes in a MySQL Table are read into Excel cells subject to Excel's cell size max of 32767 characters.

Thank you for the report.

Verified as described.

Fixed as of MySQL for Excel 1.1.2, and here's the changelog entry:

When "Export Data" from Microsoft Excel was used to create a MySQL table,
and any of the Excel spreadsheet cells contained over 4,000 characters of
text, the export failed with the following message: "MySQL Error 1118: Row
size too large. The maximum row size for the used table type, not counting
BLOBs, is 65535. This includes storage overhead, check the manual. You
have to change some columns to TEXT or BLOBs" 

Under this condition, the data type is now changed to TEXT instead 
of VARCHAR(65535).

Thank you for the bug report.

Thank you very much for fixing this problem!

Has this "solution" been tested for Import as well as Export from Excel files?  

In other words, thanks to this bug fix, we can now "Export" because large fields >4000 characters are exported as type "Text" to the MySQL data table.  

What happens when the same data is imported again from the MySQL data table to the Excel file with data types of "Text" containing >4000 characters?