MySQL Bugs: #92226: Importing data to Excel from MySQL causes data to be misplaced during sorting

Bug #92226	Importing data to Excel from MySQL causes data to be misplaced during sorting
Submitted:	29 Aug 2018 12:01	Modified:	23 Sep 2018 17:17
Reporter:	Samuel Kekäläinen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.3.7	OS:	Windows (Office 365)
Assigned to:		CPU Architecture:	Any

Description:
When first exporting data from MySQL and then importing the same data into the same sheet, the original data and headers are mismatched. I have at the current moment not tested whether it is caused by the importing from any MySQL database (which seems to be correct to me), or whether it has to be data exported from Excel.

How to repeat:
1. Make the following example table (as pictured)

https://ibb.co/nOvanp

2. Export table through "Export Excel Data to New Table" in MySQL for Excel (picking the column B as a primary key to avoid type errors)

https://ibb.co/b8DZf9

3. Import the table back into the same Excel file by right-clicking on the table and picking Import Selected and Related Tables, or alternatively just look at the preview

4. The resulting table/preview should have mismatched headers/data, like pictured (note how the headers are sorted in alphabetical order, whilst the data isn't)

https://ibb.co/mWgFnp

Suggested fix:
Have the data follow along when the headers are sorted (or alternatively don't have the headers sorted automatically at all. To be frank, not being able to sort the order of the columns would be a nuisance even without this bug).

Hi Samuel,
I tried to verify the bug, but in my test I get the correct column order.
You can see in the following screenshots the flow I did, which is follow your steps to create the data in Excel, then export it to a MySQL table, after that I verified the data importing through a usual Import Data operation and then through the one you pointed out, right-clicking the table and doing an Import Selected and Related Tables. Both imports reflect the correct column headers order:

https://ibb.co/cyoRCK
https://ibb.co/cUOMez
https://ibb.co/j4gk6e
https://ibb.co/eo5Zzz
https://ibb.co/fEjSKz

So, I need to know more about your setup (Server configuration, Excel version, etc), since I don't get the same results.

Hello Javier,

Curious, it seems that whilst for me it automatically sorts the headers, whilst for you, it does not. 

In either case, I'm using Windows 10 Home 64-bit, Excel 2016 32-bit (Microsoft Office 365 ProPlus subscription). Locale of Windows is English (UK), but I use a Finnish keyboard and formatting. I'm also using a 7th generation i5 processor.

As far as server goes, I use my computer as a local server. So, "Local instance MySQL Router". Can't remember whether it was there from the start or whether I had to go through a wizard to create it, but it is most likely not going to be all that far from the standard (possibly changing one or two things depending on recommendations from a tutorial). If you need more detailed information I'm afraid I need more guidance on what exactly and/or how I should get that information, as I simply don't know MySQL well enough to know what information you might be looking for.

Regards,
Samuel

FYI, I can also confirm the same behavior of MySQL for Excel 1.3.7. Initial loading from a worksheet to a MySQL table completed successfully and I was even able to alter the data at the MySQL table via the same excel worksheet (ie the source worksheet).

However when at later time, I tried to load the data from the MySQL table using the "Edit" operation, the columns were loaded correctly but the headers of the columns were ordered alphabetically.

That was on a Windows 7 32bit machine, using an MS Office 2010 (32bit).

I checked this with a colleague, on his laptop using MS Office 2007. We used for the test the same MySQL table on the same MySQL server, which is btw, a MySQL 8.0.12. The weird thing is that there is nothing special on the MySQL server side, besides a different collation (utf8mb4_unicode_ci) than the default.

MySQL Workbench Model that was used to reproduce the problem

Attachment: bug92226.mwb (application/vnd.mysql-workbench-model, text), 9.34 KiB.

SHOW VARIABLES output

Attachment: bug92226.show_variables.out (application/octet-stream, text), 264.20 KiB.

It seems that the guilty part is the utf8mb4_unicode collation. At least one way to reproduce the problem is to complete the use-case shown initially by Samuel, with a specification of a schema having: 'DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'. That was confirmed on a MySQL 8.0.12 for Linux as provided by Oracle's repo.

Please find attached, a)the MySQL Workbench Model that was used to reproduce the problem b)the output of the SHOW VARIABLES from the MySQL server