Bug #76517 Column mapping not saving/loading correctly (among other issues)
Submitted: 28 Mar 2015 10:27 Modified: 26 Oct 2015 20:20
Reporter: Gordon Heck Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.4 OS:Windows (7)
Assigned to: CPU Architecture:Any
Tags: MAPPING, out of memory

[28 Mar 2015 10:27] Gordon Heck
Description:
Windows 7
MySql 5.6
MySQL for Excel 1.3.4
Excel 2007

When I am attempting to append data to an existing table:
1) The automatic mapping fails to find the DateTime column.

2) The automatic mapping selects the first column in the spreadsheet for EVERY column in the MySQL table.

3) For *some* columns, but not all for some reason, I get an improper warning stating "appending data is not suitable for the mapped target column's data type".  The MySQL table column is a double, and the data in the spreadsheet column are all decimals (cell format: Number with 5 decimal places).  All of the data is correct, there is no text.  (I have checked this numerous times for all 40,000 rows I am trying to append.)

4) After manually mapping all of the columns, I click on "Store Mapping" to save the work, it asks me to pick a name, I click "OK", and the mapping instantly reverts back to what it wanted to do for the automatic mapping (see bug 2).

5) After mapping all 200 of the columns (again), I click "Append".  It runs for a long time, I sit paranoid that something is going to go wrong, and after about 20 minutes, a message box pops up stating something along the lines of there is not enough contiguous memory to build the SQL.... (and it cuts off).  This happens when I have 5+GB of free RAM in my system.

The log in AppData/.../MySQL for Excel contains the stack trace:
MySQLForExcel Error: 1 : Application Exception on MySqlScriptDialog.CreateOriginalStatementsList - Exception of type 'System.OutOfMemoryException' was thrown.
   at System.Globalization.TextInfo.InternalChangeCaseString(IntPtr handle, IntPtr handleOrigin, String localeName, String str, Boolean isToUpper)
   at System.Globalization.TextInfo.ToLower(String str)
   at System.String.ToLower(CultureInfo culture)
   at MySQL.ForExcel.Classes.MySqlDataColumn.get_IsBool()
   at MySQL.ForExcel.Classes.DataTypeUtilities.GetInsertingValueForColumnType(Object rawValue, MySqlDataColumn againstTypeColumn, Boolean escapeStringForTextTypes)
   at MySQL.ForExcel.Classes.DataTypeUtilities.GetStringValueForColumn(Object rawValue, MySqlDataColumn againstTypeColumn, Boolean& valueIsNull)
   at MySQL.ForExcel.Classes.MySqlDataRow.GetSqlForAddedRow()
   at MySQL.ForExcel.Classes.MySqlDataRow.GetSql(String& setVariablesSql)
   at MySQL.ForExcel.Classes.MySqlStatement.get_SqlQuery()
   at MySQL.ForExcel.Forms.MySqlScriptDialog.CreateOriginalStatementsList()
    DateTime=2015-03-28T07:33:45.9125755Z
    Timestamp=175708384895
    Callstack=   at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
   at System.Environment.get_StackTrace()
   at System.Diagnostics.TraceEventCache.get_Callstack()
   at System.Diagnostics.TraceListener.WriteFooter(TraceEventCache eventCache)
   at System.Diagnostics.TraceListener.TraceEvent(TraceEventCache eventCache, String source, TraceEventType eventType, Int32 id, String message)
   at System.Diagnostics.TraceSource.TraceEvent(TraceEventType eventType, Int32 id, String message)
   at MySQL.Utility.Classes.MySqlSourceTrace.WriteToLog(String message, SourceLevels messageType, Int32 messageId)
   at MySQL.Utility.Classes.MySqlSourceTrace.WriteAppErrorToLog(Exception exception, SourceLevels messageType, Boolean useInnerException)
   at MySQL.ForExcel.Forms.MySqlScriptDialog.CreateOriginalStatementsList()
   at MySQL.ForExcel.Forms.MySqlScriptDialog..ctor(MySqlDataTable mySqlTable, Boolean refreshRowsDataAfterPush)
   at MySQL.ForExcel.Classes.MySqlDataTable.PushData(Boolean showMySqlScriptDialog)
   at MySQL.ForExcel.Forms.AppendDataForm.AppendData()
   at MySQL.ForExcel.Forms.AppendDataForm.AppendDataForm_FormClosing(Object sender, FormClosingEventArgs e)
   at System.Windows.Forms.Form.OnFormClosing(FormClosingEventArgs e)

How to repeat:
Create table.
Link to DB, etc.
Click "Append...".

1,2,3) Select "Automatic Mapping".
4) Drag columns to map. Select "Store Mapping".
5) Select "Append"....wait.
[28 Mar 2015 11:26] Gordon Heck
And, when I attempt to append only a portion of the spreadsheet, it hangs up with no error message, although I think the same "out of memory" exception is being thrown in the log file.
[28 Mar 2015 12:11] Gordon Heck
On the saving of the column mapping: Based on the settings.config file, it *appears* that the config file has the proper column mapping.  All of the spreadsheet columns appear in the correct order under "SourceColumns", all of the DB columns appear in the correct order under "TargetColumns", and the DB is a perfect mirroring of the spreadsheet, the SourceIndex are sequential from 0 to the number of columns - 1.

However, even with this config file in place, the DateTime column still isn't automatically detected, and the first spreadsheet column (ID) is mapped for every DB column, which triggers an Application Exception when the ID value exceeds the max value for a TinyInt column.

I also get this strange error in the log file:

MySQLForExcel Information: 1 : Deleting automatically saved connection information entries with non-existent Excel Workbooks.
    DateTime=2015-03-28T11:59:36.8241961Z
    Timestamp=19628064037

No worksheets have been deleted from this workbook since I downloaded MySQL.
[28 Mar 2015 12:45] Gordon Heck
Uninstalled 1.3.4 and installed 1.3.3, and the column mapping worked fine, even without loading the stored mapping.  However, Excel still hangs with no warning message.  You can click the buttons, though.
[29 May 2015 22:03] Javier Treviño
Thanks for your bug report.
Please visit the following page to see the rules for creating  bugs:
http://bugs.mysql.com/how-to-report.php

This bug report contains more than 1 issue, individual bug reports need to be created for separate issues.

In order to reproduce the problem faster please attach to the bug report a SQL script to recreate the DB table you are working with and an Excel workbook with data to reproduce the out of memory problem.
[11 Aug 2015 23:28] Eric Williams
I had the identical issue on a Win 7 Pro machine.  mappings wouldn't save and it would take the first column (invoice date) and map it to 50%+ of the columns; all but one of which was incorrect.

I'm also using 1.3.4 but will install 1.3.3 until bug is fixed.
[12 Aug 2015 19:49] David Berg
I, too, have the same mapping bug with 1.3.4. It's really annoying, especially for wide or many tables. However, 1.3.4 was the first version I installed, so I don't have access to 1.3.3 to fall back to.
[27 Aug 2015 16:40] Javier Treviño
Eric, David,
To expedite a fix for this, could you supply a SQL script that creates a MySQL table with data and steps to reproduce the problem you are seeing?

Thank you.
[29 Aug 2015 21:40] David Berg
Javier, 
Here is a simple test to replicate the problem.
I uploaded a ZIP that contains three files to /support/incoming/MySQL-Bug-76517. 
The first file is a one row Excel file that will be exported to MySQL as demonstrated in the image export.png. 
 - Open the MySQL for Excel Tool
 - Press Export Excel Data to New Table
 - Table Name is "Test".
 - All other options are allowed to default.
 - Press Export Data.

Next, I will append the same one-row Excel file to the Test table as demonstrated in the image append.png.
 - Select the Database Object "test".
 - Press Append Excel Data to Table
 - Notice that the source column mapping for each destination column is "Column 1."
 - The columns must be remapped manually to achieve the desired alignment of source and destination columns.
 - I manually remapped the columns to append Row 2 to the table, as you can observe.
 - Then I appended a third row, allowing the incorrect mapping to default, and, as you can observe, the value of Column 1 in the source Excel file was inserted into every column in the destination MySQL table.
[4 Sep 2015 22:28] Javier Treviño
David,
Thanks for taking the time to create the ZIP file and upload it.
But unfortunately I don't see it in the FTP site.
Can you please add it again?

Thank you.
[5 Sep 2015 7:30] David Berg
Mapping when exporting an Excel table to a database.

Attachment: MySQL Bug 76517_export.PNG (image/png, text), 140.19 KiB.

[5 Sep 2015 7:31] David Berg
Mapping error when appending to a table using a stored map.

Attachment: MySQL Bug 76517_append.PNG (image/png, text), 138.06 KiB.

[5 Sep 2015 18:23] David Berg
One-row Excel file to be exported/appended to database table.

Attachment: MySQL Bug 76517.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, text), 13.35 KiB.

[26 Oct 2015 20:20] Philip Olson
Fixed as of the upcoming MySQL for Excel 1.2.5 release, and here's the
changelog entry:

The Append Data dialog was incorrectly mapping columns when only a single
row of Excel data was selected to append.

Also, if a single row of Excel data is selected with First Row Contains
Column Names
enabled, a warning is now displayed stating that no rows are available to
complete the append operation.

Thank you for the bug report.