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.
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.