Bug #95472 Importing data to Excel from MySQL causes invalid mapping of columns headers
Submitted: 22 May 2019 10:30 Modified: 22 May 2019 15:51
Reporter: Nikos Voutsinas Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.7 OS:Microsoft Windows (Windows 10)
Assigned to: CPU Architecture:Any

[22 May 2019 10:30] Nikos Voutsinas
Description:
In the process of importing a MySQL table in an Excel sheet, by using the MySQL for Excel 1.3.7, randomly in subsequent repetitions, the columns headers are sorted alphabetically resulting into an inconsistent mapping between the columns' headers and the columns' data.   

While this bug has rendered MySQL for Excel unusable for us, it is nasty, not because of its impact, but mostly because of its non deterministic behavior. 

This bug report seems to be another revision of the following:
https://bugs.mysql.com/bug.php?id=92226 (Can't repeat)
https://bugs.mysql.com/bug.php?id=92699 (Can't repeat)
https://bugs.mysql.com/bug.php?id=93174 (Duplicate)

At this time the bug has been confirmed, by using:
Client: MySQL for Excel 1.3.7 on the MS Excel 2016 and MS Windows 10
Server: MySQL Community Server 8.0.16 (from Oracle's repos), on Debian Stable

The only deviation from the defaults in the testing environment, was the use of the following configuration variable:
collation-server = utf8mb4_unicode_ci
 

How to repeat:
Use the following to create a sample schema:

DROP SCHEMA IF EXISTS `bug` ;
CREATE SCHEMA IF NOT EXISTS `bug` ;
USE `bug` ;
CREATE TABLE IF NOT EXISTS `bug`.`bug` (
  `B` VARCHAR(5) NOT NULL,
  `C` VARCHAR(5) NULL,
  `A` VARCHAR(5) NULL,
  PRIMARY KEY (`B`))
ENGINE = InnoDB;
INSERT INTO `bug`.`bug` (`B`, `C`, `A`) VALUES ('B1', 'C1', 'A1');
INSERT INTO `bug`.`bug` (`B`, `C`, `A`) VALUES ('B2', 'C2', 'A2');
INSERT INTO `bug`.`bug` (`B`, `C`, `A`) VALUES ('B3', 'C3', 'A3');
INSERT INTO `bug`.`bug` (`B`, `C`, `A`) VALUES ('B4', 'C4', 'A4');

The character settings of the database should be the following:

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%collatio%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_unicode_ci |
| collation_server              | utf8mb4_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

The version related info of the database should be the following:

mysql> SHOW VARIABLES LIKE '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.16                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| slave_type_conversions   |                              |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2        |
| version                  | 8.0.16                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Linux                        |
| version_compile_zlib     | 1.2.11                       |
+--------------------------+------------------------------+
11 rows in set (0.00 sec)

Repeat at least 10 to 15 times the following process:

1. Open the MS Excel 1.3.7, go to the "Data" tab and select the MySQL for Excel. Select the connection the leads you to the table: bug.bug
2. Proceed with the "Editing ...." option of the bug table
3. Note whether the order of the imported excel columns are wrong (A,B,C) or right (B,C,A)
4. Close the MS Excel, Restart the MySQL Server (systemctl restart mysql.service)

Suggested fix:
None
[22 May 2019 10:34] Nikos Voutsinas
Test schema, MySQL Server config files, Configuration info, MySQL for Excel screenshots

Attachment: mysql8_columns_sorting.tgz (application/x-compressed-tar, text), 93.74 KiB.

[22 May 2019 13:11] MySQL Verification Team
Thank you for the bug report.
[22 May 2019 15:51] Javier TreviƱo
Posted by developer:
 
This is a duplicate of MySQL Bug #93501.
This bug is already fixed and we are going to make a release soon along with other bug fixes in version 1.3.8.
This bug is very severe and affects a lot of users, we are finding a close release window.