MySQL Bugs: #77016: Can not refresh queries

Bug #77016	Can not refresh queries
Submitted:	12 May 2015 11:37	Modified:	23 Oct 2015 0:29
Reporter:	scott mankowitz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.3.4	OS:	Other (Windows Server 2008)
Assigned to:		CPU Architecture:	Any

Description:
I set up mysql for excel on Windows Server 2008 attached to a MySQL server running on localhost. Initially, I was able to get all queries (from stored procedures and views) to run normally. I saved the excel file. When I came back 2 weeks later, I am not able to refresh the data. I get the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

The connection is to localhost:3306, user root, wampmysqld

How to repeat:
1. Create new excel file
2. Import data using Mysql for Excel
3. Close file
4. Re-open file
5. Refresh data

Please try version 1.3.4. Thanks.

http://dev.mysql.com/downloads/windows/excel/

Upgraded to 1.3.4 (please note that mysql installer only upgrades to 1.3.3), but problem persists. Same error message.

Message error

Attachment: refresh_data_message.png (image/png, text), 92.75 KiB.

Thank you for the feedback.

does it make a difference if the file is saved as .xlsx or .xlsm?

Posted by developer:
 
I think it makes no difference at all, but you could try and post the result of your test.

I tried both (xlsx and xlsm). It worked in neither. Are there other settings I should change?

Hi Scott,
This problem seems to be caused because the Excel table (ListObject) that you are trying to refresh in an Excel workbook was created by an Import Data operation done in MySQL for Excel. The connection information needed by MySQL for Excel to connect to the MySQL Server is not being found in disk.

Don't know the reason for the data to be missing from disk, we will display a friendlier message when this happens.

Can you please verify if the root cause of this issue you are seeing is related to the problem described on this other bug report?

http://bugs.mysql.com/bug.php?id=73467

Maybe the connection information was not saved because you got the warning about macro-free workbooks and clicked No?  If it is related then I am pretty sure this problem will be "fixed" on that bug, BUT the "link" to the connection information is already broken so you would need to delete the ListObject and import it again so that when the Workbook is saved the connection information is linked correctly.

Posted by developer:
 
Added a validation to the cases when the connection information of an Excel table (ListObject) being refreshed is not found, if that ListObject was likely created by MySQL for Excel then a friendly message is displayed stating the reason for this problem and a solution for it. When this happens the ListObject being refreshed has already a broken link to its related information to connect back to the MySQL server and fetch data again, so that ListObject needs to be deleted and re-created. The root issue for this problem may be the one reported in MySQL Bug #73467, Bug #19358961.

Most likely this other bug is the root cause for this:
http://bugs.mysql.com/bug.php?id=76475

Posted by developer:
 
Correction, the root cause for this problem is the one reported in MySQL Bug #76475.

Great! Is there a workaround?

No workaround for the root cause until it is fixed, you can subscribe to the MySQL bug I mentioned in my last comment.

Posted by developer:
 
Added the changelog entry for this bug to MySQL Bug #73467 -- this is fixed as of the upcoming MySQL for Excel 1.3.5 release. Here is that changelog entry:

Connection information created after the last save was cleared
from memory when an Excel Workbook was closed, thus causing the
connection information to not write to disk. Now the connection
cleanup happens after a Workbench is saved, thereby saving the
new connection information.

Thank you for the bug report.