| Bug #80272 | MySQL Workbench Import Export JSON Error | ||
|---|---|---|---|
| Submitted: | 4 Feb 2016 22:45 | Modified: | 13 May 2016 3:18 |
| Reporter: | Bradley Booms | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Workbench | Severity: | S3 (Non-critical) |
| Version: | 6.3.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Feb 2016 22:46]
Bradley Booms
A sample that will fail import and would not be exported properly.
Attachment: import.json (application/json, text), 46 bytes.
[4 Feb 2016 23:12]
Bradley Booms
Added pull request on github. https://github.com/mysql/mysql-workbench/pull/5
[5 Feb 2016 5:26]
MySQL Verification Team
Hello Bradley Booms, Thank you for the report and contribution. In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team. Thanks, Umesh
[5 Feb 2016 5:27]
MySQL Verification Team
// On Win7, with WB 6.3.6
// Export
Starting...
Prepare Export...
Prepare Export done
Export data to file....
Traceback (most recent call last):
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\workbench\wizard_progress_page_widget.py", line 192, in thread_work
self.func()
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_export_wizard.py", line 259, in start_export
retval = self.module.start(self.stop)
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_import_export_be.py", line 272, in start
ret = self.start_export()
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_import_export_be.py", line 577, in start_export
rset = self._editor.executeManagementQuery(query, 1)
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 'key,value FROM `test`.`tjason`' at line 1", 1064)
ERROR: Export data to 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 'key,value FROM `test`.`tjason`' at line 1", 1064)
Failed
// Import
Starting...
Prepare Import...
Prepare Import done
Import data file....
- Truncate table
Traceback (most recent call last):
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\workbench\wizard_progress_page_widget.py", line 192, in thread_work
self.func()
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_import_wizard.py", line 126, in start_import
retval = self.module.start(self.stop)
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_import_export_be.py", line 270, in start
ret = self.start_import()
File "D:\Workbench\MySQL Workbench 6.3.6 CE (winx64)\modules\sqlide_power_import_export_be.py", line 644, in start_import
self._editor.executeManagementCommand(query, 1)
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 'key,value) 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 'key,value) VALUES(?,?)' at line 1", 1064)
Failed
[5 Feb 2016 22:09]
Bradley Booms
Unfortunately I am unable to sign the Oracle Contribution Agreement and have had to close my PR. Please let me know if I can help resolve this issue in another way.
[13 May 2016 3:18]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry: Import and export to JSON format failed if the table had column names that were keywords such as key or value. Thank you for the bug report.
Description: Import and export to json format fails if the table has column names that are keywords such as key or value. How to repeat: Create a table with columns named key and value and try to export and import a json file. Suggested fix: sqlide_power_import_export_be.py is missing the `` (quotation marks) around column names in the json_module class. The lines below included the required fixes. Line 569 (get_query) return """SELECT %s FROM %s %s""" % (",".join(["`%s`" % value['name'] for value in self._columns]), self._table_w_prefix, limit) Line 640 (start_import) query = """PREPARE stmt FROM 'INSERT INTO %s (%s) VALUES(%s)'""" % (self._table_w_prefix, ",".join(["`%s`" % col for col in dest_col_order]), ",".join(["?" for i in dest_col_order]))