MySQL Bugs: #28334: myODBC 3.51.15 caused MS Query / Excel behaviour to break

Bug #28334	myODBC 3.51.15 caused MS Query / Excel behaviour to break
Submitted:	9 May 2007 15:23	Modified:	3 Sep 2007 13:45
Reporter:	B Kingston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.15	OS:	Windows (XP Pro 2002, SP2)
Assigned to:		CPU Architecture:	Any
Tags:	criteria, Excel, MS Query, ODBC, sql bind

Description:
An Excel spreadsheet used an embedded query (built in MS Query using SQL) to populate a workbook in Excel. The odbc data source (System DSN) is to a Linux MySQL server, version 4.0.23-standard.

Attempts to refresh the Excel sheet on a new Windows server resulted in some fields missing in the output result set displayed in Excel.
Also, the "criteria" and "table" View options in MSQuery were not available for use.
As well as this, attempts to edit the SQL query text resulted in syntax errors being generated in MSQuery.

The query is as shown here:
---
SELECT CM_Job_0.JobName, CM_Job_0.StartTime, CM_Job_0.EndTime, CM_Job_0.NumberES, CM_Job_0.InputTraces, CM_Job_0.JobStatus, CM_Job_0.ELP, CM_Project_0.ProjectName, CM_ES_0.NodeName, CM_ES_0.ELP, CM_ES_0.DataMB FROM stats.CM_ES CM_ES_0, stats.CM_Job CM_Job_0, stats.CM_Project CM_Project_0 WHERE CM_ES_0.Job_ID = CM_Job_0.Job_ID AND CM_ES_0.Project_ID = CM_Project_0.Project_ID AND CM_Job_0.Project_ID = CM_Project_0.Project_ID AND ((CM_Project_0.ProjectName=?) AND (CM_Job_0.JobName Like concat(?,"%"))) ORDER BY CM_Job_0.JobName, CM_Job_0.StartTime
---

The use of the question mark to receive user input values caused the syntax errors.

It was noticed that an "SQLBind" error appeared, saying that some parameters could not be bound. This would explain why 3 of the output fields were missing in Excel.

Removing the use of the wildcards (question marks) in the query allowed the query to run without a problem. However, the criteria and table views were still not available in MSQuery.

How to repeat:
Spreadsheet was tested on a machine using ODBC 3.51.12 and worked ok.
3.51.15 was installed, and problem repeated (SQLBind error in MSQuery).

3.51.15 uninstalled (Add/Remove programs in Windows Control Panel), and 3.51.12 installed again.
Spreadsheet now updates ok again, and MSQuery options are as expected.

Suggested actions to repeat:
In MSExcel - Use the Data menu to create a new database query from a MyODBC data source, and in MSExcel, build a query that prompts the user for input.
Test with 3.51.12 and then with 3.51.14 or 3.51.15.

Suggested fix:
Find cause of bind errors - presumably introduced in 3.51.13 or .14

Hi Ben and thanks for your interesting report. Can you please attach following info:
 - DDL statements to create tables in question
 - a script to add few records to tables
 - ODBC trace at the time of error (ODBC Data Sources/ Tracing/ Machine wide tracing)
 - General query log from MySQL server at the time of problem so we can examine what actually got to it

Also, synopsis and Version show 3.51.14 and you talk about 3.51.15. Please correct this.

SQL.log from ODBC trace while updating Excel sheet. Shows dropped fields near EOF

Attachment: SQLLog.zip (application/x-zip-compressed, text), 4.40 KiB.

Have added trace log. This was generated while refreshing the Excel sheet using the embedded query. Version 3.51.15 was installed before running this update.

Results displayed in the Excel sheet have 3 columns missing. SQL trace log appears to show drop of fields near end of the log.

I did not open MSQuery while generating this trace log - simply opened Excel file, and refreshed sheet that has embedded query.

I don't have the opportunity to turn on the MySQL general query log at the moment, but I know through use of SHOW FULL PROCESSLIST that the query received is complete and as expected, and that using version 3.51.12 of the MyODBC driver works ok.

Thanks,
Ben

No Ben, that is not column being dropped... Next time, add limit (like LIMIT 5) to your SELECT clause so that we avoid SQLFetch repeating throughout entire log.
What about:
 - DDL statements to create tables in question
 - a script to add few records to tables

Can you also try catching PREPARE and SELECT part in SHOW PROCESSLIST output since you don't have access to log.

If you can provide me with DDL statements to create tables in question and a script to add few records to each of tables needed for sheet to work
and accompanying XLS sheet I can test myself. So far, all I know is that ODBC trace looks fine.

Tonci; sorry - I had written all of this DDl before, but lost it when going to upload the trace log.

Using MyODBC 3.51.15, I cannot actually create the SQL query I need to, because in MSQuery, having pasted the SQL and attempting to 'OK', I get the message "Parameters are not allowed in queries that can't be displayed graphically", and as a result, the query cannot be saved. The original way that we ran the query was using an existing sheet that was built while using 3.51.12, and to attempt to run it. That resulted in the missing output columns.

So - part of this problem is the MSQuery complaints about the query syntax. 

Tables and example rows:

CREATE TABLE `CM_Project` (
  `Project_ID` int(9) unsigned NOT NULL auto_increment,
  `ProjectName` varchar(32) binary NOT NULL default '',
  PRIMARY KEY  (`Project_ID`)
) TYPE=MyISAM

Example row: (1639, 'ztrain')

CREATE TABLE `CM_Job` (
  `Job_ID` int(9) unsigned NOT NULL auto_increment,
  `Project_ID` int(9) unsigned NOT NULL default '0',
  `Cube_ID` int(9) unsigned NOT NULL default '0',
  `Site_ID` int(9) unsigned NOT NULL default '0',
  `CMNode` varchar(32) binary NOT NULL default '',
  `CMPort` mediumint(7) unsigned NOT NULL default '0',
  `CMHome` varchar(32) binary NOT NULL default '',
  `JobName` varchar(64) binary NOT NULL default '',
  `JobNumber` mediumint(7) unsigned NOT NULL default '0',
  `StartTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `EndTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `Memory` double unsigned default NULL,
  `CPU` double unsigned default NULL,
  `ELP` double unsigned default NULL,
  `NumberES` smallint(4) unsigned NOT NULL default '0',
  `InputTraces` int(9) unsigned default NULL,
  `DataType_ID` int(9) unsigned NOT NULL default '0',
  `JobStatus` set('OK','ABORTED','KILLED','ERROR') NOT NULL default '',
  PRIMARY KEY  (`Job_ID`),
  UNIQUE KEY `Project_ID` (`Project_ID`,`Site_ID`,`CMNode`,`CMPort`,`CMHome`,`JobName`,`JobNumber`,`StartTime`,`EndTime`),
  KEY `StartTime_index` (`StartTime`),
  KEY `EndTime_index` (`EndTime`)
) TYPE=MyISAM

Example row:
(8942879, 1639, 0, 8, 'jmigb000', 3337, '/cm/production/r3.10', '_ICE_9_ZgtVdq', 786, '2007-05-02 09:58:37', '2007-05-02 10:30:28', 0.8, 1899.3, 1910.2, 1, 679200, 1820, 'OK')

CREATE TABLE `CM_ES` (
  `ES_ID` int(9) unsigned NOT NULL auto_increment,
  `Node_ID` int(9) unsigned NOT NULL default '0',
  `NodeType_ID` int(9) unsigned NOT NULL default '0',
  `Job_ID` int(9) unsigned NOT NULL default '0',
  `Project_ID` int(9) unsigned NOT NULL default '0',
  `Cube_ID` int(9) unsigned NOT NULL default '0',
  `Site_ID` int(9) unsigned NOT NULL default '0',
  `ESseq` smallint(4) unsigned NOT NULL default '0',
  `PID` mediumint(7) unsigned NOT NULL default '0',
  `Task` varchar(32) binary NOT NULL default '',
  `NodeName` varchar(32) binary NOT NULL default '',
  `Memory` double unsigned default NULL,
  `DataMB` double unsigned default NULL,
  `Traces` int(9) unsigned default NULL,
  `CPU` double unsigned default NULL,
  `ELP` double unsigned default NULL,
  `UserCPU` double unsigned default NULL,
  `SysCPU` double unsigned default NULL,
  `ShellCPU` double unsigned default NULL,
  `ShellELP` double unsigned default NULL,
  `MinorFault` int(9) unsigned default NULL,
  `MajorFault` int(9) unsigned default NULL,
  `Swap` int(9) unsigned default NULL,
  PRIMARY KEY  (`ES_ID`),
  KEY `Job_ID` (`Job_ID`)
) TYPE=MyISAM

Example row:
(31522024, 0, 0, 8942879, 1639, 0, 8, 0, 14197, '0x1', 'jmigb041', 0.8, 7842.78, 679200, 1899.3, 1910.22, 131.25, 1768.05, 24.50097, 32.091696, 1279, 1, 0)

Ben, can you please attach XLS in question. If it contains sensitive data you can do it privately so that only I can access it.

Excel file added.
SQL cannot be edited and saved unless user prompts are changed: (set ? in Project statement to '1476exm' and ? in Jobname prefix to '26j1a06_pstm'). 
When done, MSQuery correctly pulls in the results, showing all columns. When asking MSQuery to return that data to Excel, 3 columns are lost. (JobName, ProjectName, NodeName).

Thanks Ben. Will try to get to the bottom of this...

No problem, I hope you can find something.
I was surprised to find that reverting to version 12 solved the problem - I was expecting this to be an application issue, not the driver. 

We have seen the problem with versions 14 and 15, but my extra tests used version 15.

The fact that MSQuery fails to sent all results back to Excel would suggest there's a binding issue with knowing where to place the columns. I guess that the ODBC driver must be in use at this time, and something happens to break the binding of some columns to the Excel spreadsheet.

Good luck,
Ben

Ben, tough one :)
 - Your DSN is malformed, Port is set to 0 as well as options. You should see docs on MyODBC options
 - The error you mention is pretty notorious and not connected to this or that server/driver

What I suspect, and I'll have to ask for consult on this, is:
 - New MyODBC driver correctly (as opposed to what 3.51.12 did) discovers parameters in your query but it is not able to retrieve parameters info (nor is it able to prepare statement) as PS were introduced in 4.1 (lot's of SQL_NO_DATA in logs). Please do replace "?" with cell designator like "[A1]" as shown in following pages and get back to me with results.

http://www.dbforums.com/printthread.php?threadid=983817&perpage=4
http://www.tek-tips.com/viewthread.cfm?qid=1143813&page=1
http://www.mrexcel.com/archive2/57700/66890.htm

Additional points of concern:
 - How does one change Query / DSN settings from within Excel?
 - Changing rows in Excel sheet do send query through MyODBC 3.51.15 to Linux server with MySQL 4.1.xx server, I checked in logs (replaced "m" with "r") so basically, sheet works for me.
SELECT CM_Job_0.JobName, CM_Job_0.StartTime, CM_Job_0.EndTime, CM_Job_0.NumberES, CM_Job_0.InputTraces, CM_Job_0.JobStatus, CM_Job_0.ELP, CM_Project_0.ProjectName, CM_ES_0.NodeName, CM_ES_0.ELP, CM_ES_0.DataMB
FROM stats.CM_ES CM_ES_0, stats.CM_Job CM_Job_0, stats.CM_Project CM_Project_0
WHERE CM_ES_0.Job_ID = CM_Job_0.Job_ID AND CM_ES_0.Project_ID = CM_Project_0.Project_ID AND CM_Job_0.Project_ID = CM_Project_0.Project_ID
AND ((CM_Project_0.ProjectName='1476exr') AND (CM_Job_0.JobName Like concat('26j1a05_pstr',"%")))
ORDER BY CM_Job_0.JobName, CM_Job_0.StartTime

"- DSN malformed": ODBC settings state that port should default to 3306, but setting it manually to 3306 also appears to make no difference.

I have edited the SQL in MSQuery to use hardwired values (for project name and job prefix), yet I still see columns missing in the data returned to Excel, so using references to the parent workbook to get values is unlikely to make any difference - we have just tested the supply of actual parameter values within the query itself, so the output data going missing definitely appears to be occurring "downstream" of the SQL query itself, although this doesn't tell us that the actual cause wasn't upstream, if you get me. :)

You ask how to change the DSN name in Excel. When a new database query is created (Data - Import External Data - New Database Query), you select the Data Source form the offered list. I cannot find a way to later change or edit the Data Source associated with the query.. perhaps it's buried in Excel somewhere, but I'm afraid I don't know where.

Ben,

"- DSN malformed": ODBC settings state that port should default to 3306, but
setting it manually to 3306 also appears to make no difference.

Didn't think it would help but rather used it as an example of what's seems to be wrong with sheet in question...

>I have edited the SQL in MSQuery to use hardwired values (for project name and
job prefix), yet I still see columns missing in the data returned to Excel, so
using references to the parent workbook to get values is unlikely to make any
difference - we have just tested the supply of actual parameter values within
the query itself, so the output data going missing definitely appears to be
occurring "downstream" of the SQL query itself, although this doesn't tell us
that the actual cause wasn't upstream, if you get me. :)

I do :) I still suspect that 3.51.12 was not working properly and that 3.51.15 does. Can you upgrade to at least MySQL server 4.1.x and test? I believe you'll get correct results.

>You ask how to change the DSN name in Excel. When a new database query is
created (Data - Import External Data - New Database Query), you select the Data
Source form the offered list. I cannot find a way to later change or edit the
Data Source associated with the query.. perhaps it's buried in Excel somewhere,
but I'm afraid I don't know where.

Heh, me neither...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Ben, any news?

Tonci;
no, I have not revisited this, since reverting to the older version got us running again, and I'm afraid time doesn't allow me to continue diagnostic/investigative work on this.

If you wish to close/cancel this bug and leave it in the archives to be reopened if someone else encounters the problem, then please do so.
Best regards,
Ben

Ben, sorry to hear that you're unable to work on this any more. Is it possible for to to, at least, try with 3.51.19 and let me know how it went?

Tonci:
I have just tested version 19 of the driver, and it appears to FIX the bug we had been seeing: the query no longer loses data columns on returning the data to the sheet in Excel.

I checked again to make sure:
- uninstalled version 19 of the driver,
- installed version 12 of the driver, tested: OK.
- Installed version 15 of the driver, tested: lost columns in Excel result set.
- (re)Installed version 19 of the driver, tested: OK.

So, something got fixed between 15 and 19, and 19 looks good. Many thanks,
Ben

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Ben, I'm glad things work now and thanks for your feedback. Our ODBC team doubled their efforts to fix as many issues as possible in reasonable time. You can see the result.

Thanks for your interest in MySQL.