Bug #109613 MySQL Workbench import/export
Submitted: 13 Jan 2023 3:29 Modified: 13 Jan 2023 12:12
Reporter: Bill Karwin (Candidate Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[13 Jan 2023 3:29] Bill Karwin
Description:
MySQL Workbench import and export wizards don't handle special identifiers, because they generate SQL statements without delimiting identifiers. Table names and column names that don't match default identifier rules cause the generated SQL to fail.

How to repeat:
Create a table with column identifiers that do not need to be delimited:

mysql> create table mytable_plain ( id serial primary key, x varchar(10), y varchar(10), z varchar(10));

Use MySQL Workbench to perform Import or Export Wizards for the `mytable_plain` table. These wizards work without error. It doesn't matter what data is imported, I tested this input CSV file:

1,abc,def,xyz

Create a second table with column identifiers that need to be delimited, such as a reserved keyword, a non-English name that includes an apostrophe character, or other special characters such as whitespace.

mysql> create table mytable_special (id serial primary key, `key` varchar(10), `Région d'élection` varchar(10), `white space` varchar(10));

Try to perform the Import Wizard using the same CSV input file. It fails with the following output:

Starting...
Prepare Import...
Prepare Import done
Import data file....
- Prepare Import

Traceback (most recent call last):
  File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 197, in thread_work
    self.func()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_wizard.py", line 131, in start_import
    retval = self.module.start(self.stop)
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 300, in start
    ret = self.start_import()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 430, in start_import
    self._editor.executeManagementCommand(query, 1)
grt.DBError: ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'élection`,`white space`) VALUES(?,?,?)'' at line 1", 1064)
ERROR: Import data file: ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'élection`,`white space`) VALUES(?,?,?)'' at line 1", 1064)
Failed

This shows that the apostrophe in the column named `Région d'élection` causes a syntax error.

Also try to perform the Export Wizard. It fails with the following output:

Starting...
Prepare Export...
Prepare Export done
Export data to file....
- Begin Export

Traceback (most recent call last):
  File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 197, in thread_work
    self.func()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_export_wizard.py", line 273, in start_export
    retval = self.module.start(self.stop)
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 302, in start
    ret = self.start_export()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 367, in start_export
    output.writerow([value['name'] for value in self._columns])
UnicodeEncodeError: 'ascii' codec can't encode character '\xe9' in position 13: ordinal not in range(128)
ERROR: Export data to file: 'ascii' codec can't encode character '\xe9' in position 13: ordinal not in range(128)
Failed

Suggested fix:
Use back-ticks around identifiers during import and export, like other MySQL tools such as mysqldump and mysqlimport do.
[13 Jan 2023 3:30] Bill Karwin
I believe this the same issue has been reported in past bugs #76991, #80272, and #85341, but those bugs were closed without any fix applied to the MySQL Workbench product.
[13 Jan 2023 3:48] Bill Karwin
I have noticed that I made a mistake in my interpretation of the error. Back-ticks are used in the generated SQL. Despite that, in the case of the French column name with an apostrophe, there are still errors for both Import and Export.
[13 Jan 2023 12:12] MySQL Verification Team
Hello Bill Karwin,

Thank you for the bug report.
Imho this is duplicate of Bug #95700, please see Bug #95700.

Regards,
Ashwini Patil