Bug #56908 Driver returns empty resultset for SQLForeignKeys
Submitted: 21 Sep 2010 21:04 Modified: 11 Oct 2011 7:55
Reporter: merino klimtong Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.27 OS:Windows (7)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: SQLForeignKeys

[21 Sep 2010 21:04] merino klimtong
Description:
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
	...
  PRIMARY KEY (`user_id`),
  KEY `users_FK00` (`age_id`),
	...
  CONSTRAINT `users_FK07` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`group_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

both of the following calls appear to work fine with the 5.1 driver but not the 3.5 driver, which returns an empty resultset. 

-------------------------------

OdbcTE32        1748-1a18	ENTER SQLForeignKeysW 
		HSTMT               0x023023A8
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x02304A70 [      -3] "users\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 

OdbcTE32        1748-1a18	EXIT  SQLForeignKeysW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x023023A8
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x02304A70 [      -3] "users\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 

Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
0 rows fetched from 14 columns.

--------------------------------------------

OdbcTE32        1748-1a18	ENTER SQLForeignKeysW 
		HSTMT               0x023023A8
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x02304A70 [      -3] "users\ 0"
		SWORD                       -3 

OdbcTE32        1748-1a18	EXIT  SQLForeignKeysW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x023023A8
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
		SWORD                        0 
		WCHAR *             0x02304A70 [      -3] "users\ 0"
		SWORD                       -3 

Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
0 rows fetched from 14 columns.

How to repeat:
I'm using mysql 5.1, and several databases created like the sample ddl above.  No matter what I do, the 3.5 driver returns an empty resultset for sqlforeignkeys.    I used odbctest.  I didn't test them all, but several of the other schema calls appear to work fine (sqlcolumns, sqlprimarykeys, etc.)
[22 Sep 2010 6:11] Tonci Grgin
Hi Merino and thanks for your report.

Can I please have a complete DDL and a description of DNS used so I can check.
[23 Sep 2010 3:22] merino klimtong
CREATE DATABASE  IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
-- MySQL dump 10.13  Distrib 5.1.40, for Win32 (ia32)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.1.50-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `fk_users` (`group_id`),
  CONSTRAINT `fk_users` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_groups`
--

DROP TABLE IF EXISTS `user_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_groups`
--

LOCK TABLES `user_groups` WRITE;
/*!40000 ALTER TABLE `user_groups` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_groups` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-09-22 20:15:18

----------------------------------------------

dsn for 3.5

DATABASE: test
Driver: C:\Windows\system32\myodbc3.dll

dsn for 5.1

DATABASE: test
Driver: C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll
PORT: 3306

The other metadata calls I tested appear to work for both; it's only the sqlforeignkeys that returns different results.
[11 Oct 2011 7:55] Bogdan Degtyariov
This is fixed in 3.51.28.
The new Connector/ODBC 3.51.28 is using Information_Schema just like 5.1 does.
Closing the bug report.