MySQL Bugs: #32243: ODBC

Bug #32243	ODBC - call failed
Submitted:	9 Nov 2007 19:10	Modified:	14 Nov 2007 16:45
Reporter:	Jerry Schwartz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.01	OS:	Windows (Vista 32bit)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC Access

Description:
I have a working ODBC system data source. Using Access 2007, I open a database that has three tables linked to the ODVC data source (along with many others linked to Access databases). I try to run the following query:

SELECT prod.prod_num, concat("|",group_concat(topic.topic_code SEPARATOR "|"),"|") AS topic_list
FROM prod JOIN prod_topic
  JOIN topic
WHERE prod.prod_id = prod_topic.prod_id
  AND prod_topic.topic_id = topic.topic_id
GROUP BY prod.prod_num;

All of the tables in this query are linked to the ODBC data source. The query fails with the message "ODBC-connection to 'sourcename' failed. The query will fail every time I try to run it.

If I open one of the linked tables, the table opens properly without an error. If I then try the query again, it will work.

How to repeat:
Sorry, I can repeat it myself but I don't know what the secret is.

Trace

Attachment: SQL.LOG (text/plain), 272.77 KiB.

Thank you for the bug report. Could you please provide the dump file of
the tables involved and the server version you are using (it could be
private if you wish, just use the Files tab to attach and select private).
Thanks in advance.

The log says:

[MySQL][ODBC 5.1 Driver]Access denied for user 'readonly'@'adsl-76-245-68-102.dsl.wlfrct.sbcglobal.net' (using password: YES)

Are you really sure, that there is a user "readonly" with the correct grants for host "adsl-76-245-68-102.dsl.wlfrct.sbcglobal.net"?

Do your password has special signs that are not between 0-9, a-z or A-Z?

Yes, I am certain that the user "readonly" exists and has the proper permissions. The password does not have any funky characters in it.

Remember that the work-around is to open one of the tables in browse mode. That works just fine. After opening one of the linked tables, I can then run the query without a whimper.

Jerry what I would like to see attached here is general query log from server (start server with --log or just ad "log" to my.ini file). Also, a table structure might prove interesting. Office2007 and Vista a rather new (read: no SP yet) so we might expect all kind of troubles with it (type Excel2007 bug in google for example).

Hi Jerry,

I can't reproduce your problem.

I used MS Office 2007 and tried it either with a local MySQL server and with a server, that runs under Linux using the network.

I'll attach my test SQL statements. Also I'll add the screenshots, that I made.

I suppose, that your problem occurs because of not enough rights to the database or network.

Or didn't you found the pass through button in Access?

Look to my screenshots. Without pushing this button, the concat() fails because Access wants to use it's own concat function. 

I'll set this bug to "can't repeat" but when you agree with me I want to set it to "not a bug".

Many thanks for trusting Mysql.

Susanne

P.S.:
There were some weird stuff at your SQL, but nothing, that could be the reason for your problem.

1) select ... from a join b join c where ...; 
better here: 
select ... from a, b, c where ...; 

2) your tables have had: id varchar(15) not null default ''; primary key(id)
This will occur an error, when you insert the default value twice.
Also I found columns, with default NULL and unique indexes, that's not a very nice kind of programming.

the SQL that I used for testing

Attachment: bug32243.sql (text/x-sql), 1.69 KiB.

the screenshots without linking tables

Attachment: bug32243_snapshots_without_linking_tables.tar.gz (application/gzip, text), 485.06 KiB.

screenshots with linking tables

Attachment: bug32243_snapshots_with_linking_tables.tar.gz (application/gzip, text), 329.70 KiB.

screenshots after linking tables

Attachment: bug32243_snapshots_with_linking_tables_part2.tar.gz (application/gzip, text), 278.11 KiB.

Here is the general query log for the time period surrounding the ODBC calls from MS Access 2007. If I read this right, the first attempted connection failed and the second attempt succeeded. The only difference from the client side was that the first attempt was generated by a query, the second was generated by a table browse (I presume that was the SELECT `topic`.`topic_id` FROM `topic` query).

The query that failed the first time, but succeeded once the connection was open, was 

SELECT prod.prod_num, concat("|",group_concat(topic.topic_code SEPARATOR "|"),"|") AS topic_list
FROM prod JOIN prod_topic
  JOIN topic
WHERE prod.prod_id = prod_topic.prod_id
  AND prod_topic.topic_id = topic.topic_id
GROUP BY prod.prod_num;

       Table: topic
Create Table: CREATE TABLE `topic` (
  `topic_id` varchar(15) NOT NULL default '',
  `topic_code` varchar(15) default NULL,
  `topic_name` varchar(255) default NULL,
  `topic_desc` text,
  `topic_added` datetime default NULL,
  `topic_updated` datetime default NULL,
  PRIMARY KEY  (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

       Table: prod_topic
Create Table: CREATE TABLE `prod_topic` (
  `prod_topic_id` varchar(15) NOT NULL default '',
  `prod_id` varchar(15) default NULL,
  `topic_id` varchar(15) default NULL,
  `prod_topic_added` datetime default NULL,
  PRIMARY KEY  (`prod_topic_id`),
  KEY `prod_id` (`prod_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

       Table: prod
Create Table: CREATE TABLE `prod` (
  `prod_id` varchar(15) NOT NULL default '',
  `prod_num` mediumint(6) unsigned default NULL,
  `prod_title` varchar(255) default NULL,
  `prod_type` varchar(2) default NULL,
  `prod_discont` tinyint(1) default NULL,
  `prod_ready` tinyint(1) default NULL,
  `pub_id` varchar(15) default NULL,
  `prod_published` date default NULL,
  `prod_pub_prod_id` varchar(255) default NULL,
  `prod_pub_acct_id` varchar(2) default NULL,
  `prod_pub_doi` date default NULL,
  `prod_pub_resp` date default NULL,
  `prod_pub_prod_url` varchar(255) default NULL,
  `prod_rel_freq` smallint(3) default NULL,
  `prod_content_info` varchar(255) default NULL,
  `prod_info_type` varchar(5) default NULL,
  `prod_language` varchar(50) default NULL,
  `prod_broch_doc` varchar(255) default NULL,
  `prod_samp_doc` varchar(255) default NULL,
  `prod_samp_pgs` varchar(255) default NULL,
  `prod_exec_summ` varchar(255) default NULL,
  `prod_toc_doc` varchar(255) default NULL,
  `prod_e_title_tag` varchar(255) default NULL,
  `prod_meta_tags` varchar(255) default NULL,
  `prod_keywords` varchar(255) default NULL,
  `prod_comments` text,
  `prod_if_sample_pdf` varchar(255) default NULL COMMENT 'URL for sample PDF on
the-infoshop.com',
  `prod_stop_date` date default NULL,
  `prod_hide_web` tinyint(1) default NULL,
  `prod_changed` tinyint(1) default NULL,
  `prod_export` tinyint(1) default NULL,
  `prod_export_pending` tinyint(1) default NULL,
  `prod_scoop_changed` tinyint(1) default NULL COMMENT 'If true, then product needs to be considered for Scoop export',
  `prod_on_scoop` tinyint(1) default NULL COMMENT 'If true, then this product is on the Scoop site',
  `prod_added` datetime default NULL,
  `prod_updated` datetime default NULL,
  PRIMARY KEY  (`prod_id`),
  UNIQUE KEY `prod_num` (`prod_num`),
  UNIQUE KEY `prod_num_2` (`prod_num`),
  KEY `prod_pub_prod_id` (`prod_pub_prod_id`),
  KEY `pub_id` (`pub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

General Query Log showing connection failure

Attachment: host.log (text/plain), 7.79 KiB.

I understand your observation about all of those UNIQUE columns having NULL as a default. I also understand your concern about the primary key having a default value of " ". I consider the use of a random string as an ID field to be rather bizarre, since an auto-increment field would have been so much easier to deal with. I inherited this whole system, which is rather convoluted, and there are a lot of things I would have done differently. I'm afraid to make too many changes for fear of unexpected consequences, especially since we have no test environment.

Jerry,

and you are sure, the Pass-Through button was pressed, when you tried it the first time?

Did you try executing the query before opening the table? I wasn't sure from your screen shots exactly what the order was. 

Also, did you close and re-open Access after linking the tables but before running the query? I didn't think to mention it before, but linking or relinking the tables will also make the query work - until you close and re-open Access.

I started Access and then tried first the query. It works. Then I linked the tables and tried it again. It worked too.

Also, when I used another host for the database, I closed Access and opened it before using it.