Bug #84136 Error refreshing imported data with a connection where the password wasn't saved
Submitted: 9 Dec 2016 2:04 Modified: 4 May 2017 23:41
Reporter: Javier Treviño Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S3 (Non-critical)
Version:1.3.6 OS:Any
Assigned to: CPU Architecture:Any

[9 Dec 2016 2:04] Javier Treviño
Description:
When data is imported from MySQL into an Excel worksheet, having the advanced option of "Create an Excel table for the imported MySQL data." checked and the Workbook is saved, the next time the Workbook is opened MySQL for Excel will attempt to refresh the imported data.  If the connection used to do the import does not have a password stored securely, the refresh operation cannot establish the connection without the password and just fails instead of prompting the user for the password.

The only way to fix this behavior is to edit the connection that was used and store a password, which may not be a suitable workaround for everyone.

How to repeat:
1. Open MySQL for Excel, create a connection to a MySQL Server where the password is NOT specified.
2. Double-click the connection, MySQL for Excel will prompt for the password.
3. Select any schema.
4. Select any table with data, and click Import MySQL Data.
5. Click on Advanced Options, make sure the "Create an Excel table for the imported MySQL data." option is checked.
6. Click Import.
7. Save the Workbook to any location and close Excel.
8. Open the saved Workbook.
9. Note an error stating "Unable to retrieve data from from query:...." is shown, with a detail like Access denied for user xx@yy (using password: NO)
10. Click OK.
11. Enable MySQL for Excel from the Data ribbon.
12. Note the same error message in step 9 is displayed again.

Suggested fix:
1. MySQL for Excel must ask for a connection password when trying to refresh the data.
2. The error dialog has a repeated "from" that must be fixed.
3. Although it is standard behavior that MySQL for Excel refreshes MySQL imported data when a Workbook is opened and even if the MySQL for Excel sidebar is not active, it would be nice to have a global option to disable that behavior in case users prefer the data is only refreshed if the MySQL for Excel sidebar is activated.
[9 Dec 2016 20:48] Javier Treviño
Posted by developer:
 
Added code to check the status of MySQL connections before attempting to refresh imported MySQL data using those connections, if connections are not active an attempt to connect is done.
If the connections do not have a password specified the connection is attempted without password, if that fails then a dialog is displayed asking for the password before attempting to open the connection again.

Added 2 new options to the Globa Options dialog to either "Restore imported MySQL data in Excel Tables when opening an Excel workbook" or "Restore imported MySQL data in Excel Tables when showing the
Add-In's sidebar".
[4 May 2017 16:52] Rafael Antonio Bedoy Torres
Posted by developer:
 
Fixed on 1.3.7 build 2
[4 May 2017 23:41] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.7 release, and here's the changelog entry:

Inactive connections and unsaved passwords caused the refresh action to generate errors for worksheets with imported MySQL data in Excel tables.

Thank you for the bug report.