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
