Bug #877 MS Access/ ADO conn.OpenSchema error with more than 15 foreign key constraints
Submitted: 19 Jul 2003 5:03 Modified: 29 Jul 2004 1:26
Reporter: Ronald van Raaij Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.06 OS:Microsoft Windows (Windows 2000 SP4)
Assigned to: Assigned Account CPU Architecture:Any
Tags: ODBC5-RC

[19 Jul 2003 5:03] Ronald van Raaij
Description:
Using conn.OpenSchema on a MySQL 4.013 Server generates the following error:
The instruction at "0x0aa2610c" referenced memory at "0x6f6e49" the memory could not be "read". 

The full statement is:
Set rstSchema = New ADODB.Recordset
rstSchema.CursorLocation = adUseClient
Set rstSchema = conn.OpenSchema(adSchemaForeignKeys, Array(Empty, Empty, strTableName, Empty, Empty, Empty))

The error only occurs when the statement is executed on a table with a self referencing Join.

Configuration:

Server and client on the same machine
Connection string:
    conn.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & Trim(Svr) & ";PORT=3306;DATABASE=danwerk;OPTION=18435", UID, Pwd

OS: Win2K SP4
Server 4.0.13
ODBC 3.51.06 (debug library)
MS Access 2000 SP3

I had this bug before, on 4.0.12 but strangely enough it disappeared when switching to the debug library. It has come back with installing 4.0.13 Server.

Table Schema:

use danwerk;

/* ============================================================ */
/*   Table: Organisaties                                        */
/* ============================================================ */
create table Organisaties
(
    OgeID               MEDIUMINT UNSIGNED    not null  AUTO_INCREMENT,
    GebruiktAdresVan    MEDIUMINT UNSIGNED    null      ,
    Naam                VARCHAR(80)           not null  ,
    Status              INT                   null      
        default 'O',
    Adresseringsnaam    VARCHAR(55)           null      ,
    Bedrijfsonderdeel   VARCHAR(255)          null      ,
    Afdeling            VARCHAR(255)          null      ,
    EersteKontaktDatum  DATETIME              null      
        default 'NOW()',
    UserName            INTEGER UNSIGNED      null      ,
    ReviewDatum         DATETIME              null      
        default 'DateAdd("m";6;Date())',
    Actief              BIT                   not null  ,
    NVP                 BIT                   not null  ,
    Gewijzigd           DATETIME              null      ,
    GewijzigdDoor       INTEGER UNSIGNED      null      ,
    SvrUpdate           TIMESTAMP             null      ,
    LocalID             MEDIUMINT             null      
        default NULL,
    constraint pkOrganisaties primary key (OgeID)
)
TYPE= InnoDB;

/* ============================================================ */
/*   Index: fkAdrVOgeID                                         */
/* ============================================================ */
create index fkAdrVOgeID on Organisaties (GebruiktAdresVan);

/* ============================================================ */
/*   Index: Naam                                                */
/* ============================================================ */
create index Naam on Organisaties (Naam);

/* ============================================================ */
/*   Index: Status                                              */
/* ============================================================ */
create index Status on Organisaties (Status);

alter table Organisaties
    add constraint fkConstrOrganisatiesOrganisati foreign key  (GebruiktAdresVan)
       references Organisaties (OgeID);

How to repeat:
See a under description
[21 Jul 2003 11:43] Ronald van Raaij
I have been reviewing and testing some things today, and have some conclusions:

It probably has nothing to do with the self referencing join, but more with the number of foreign keys a table is involved in. This particular table's primary key is a foreign key in 15 other tables including itself. 
I tried reverting to 4.0.12 but that did not help anything.
So while writing this I got the idea to try adding foreign key constraints one by one. And presto, everything worked until adding the 15th then the crash occured again.

So, it may be an bounds error in ADO or in the ODBC driver.

Ronald
[21 Jul 2003 12:02] Ronald van Raaij
See recently added comment. Just changing the synopsis
[28 Jul 2003 11:46] Venu Anuganti
Hi !!

Thanks for the bug report. As your bug report states you are using a debug version of myodbc3.dll. Can you please revert that to a normal 'release' version and see if you can reproduce the problem ?

Mean while I will also cross check what is happing. For sure driver doesn't impose any restriction of number of keys. But it looks like some memeory overrun during the internal result set mapping; 

My guess is that, it shouldn't happen with any release version of DLLs.

Thanks
Venu
[29 Jul 2003 2:32] Ronald van Raaij
Venu,

Tried with the normal release driver (again), then the error becomes:

The instruction at "0x09ab486b" referenced memory at "0x0ff1e10f4" the memory could not be "written"

So something changes, but it does happen.

Ronald
[6 Oct 2003 1:36] Venu Anuganti
Hi !!

I tried to reproduce this, but it just works fine with 3.51.06. I tried this with 4.0.16.

Here is the test case and its output..
Private Sub FKey_Click()
Dim conn As adodb.Connection
Dim rs As adodb.Recordset
Dim fld As adodb.Field
Dim count As Integer

'Connect to MySQL Server
Set conn = New adodb.Connection
conn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=venu;PASSWORD=venu;OPTION=3;"
conn.Open
conn.Execute "DROP TABLE IF EXISTS Organisaties"
conn.Execute "create table Organisaties" _
                  & " (" _
                  & " OgeID               MEDIUMINT UNSIGNED    not null  AUTO_INCREMENT," _
                  & " GebruiktAdresVan    MEDIUMINT UNSIGNED    null      ," _
                  & " Naam                VARCHAR(80)           not null  ," _
                  & " Status              INT                   null" _
                  & " Default 'O'," _
                  & " Adresseringsnaam    VARCHAR(55)           null      ," _
                  & " Bedrijfsonderdeel   VARCHAR(255)          null      ," _
                  & " Afdeling            VARCHAR(255)          null      ," _
                  & " EersteKontaktDatum  DATETIME              null" _
                  & " Default 'NOW()'," _
                  & " UserName            INTEGER UNSIGNED      null      ," _
                  & " ReviewDatum         DATETIME              null" _
                  & " Default 'DateAdd(m;6;Date())'," _
                  & " Actief              BIT                   not null  ," _
                  & " NVP                 BIT                   not null  ," _
                  & " Gewijzigd           DATETIME              null      ," _
                  & " GewijzigdDoor       INTEGER UNSIGNED      null      ," _
                  & " SvrUpdate           TIMESTAMP             null      ," _
                  & " LocalID             MEDIUMINT             null" _
                  & " default NULL, constraint pkOrganisaties primary key (OgeID)" _
                  & " ) TYPE= InnoDB"
conn.Execute "create index fkAdrVOgeID on Organisaties (GebruiktAdresVan)"
conn.Execute "create index Naam on Organisaties (Naam)"
conn.Execute "create index Status on Organisaties (Status)"
conn.Execute "alter table Organisaties add constraint fkConstrOrganisatiesOrganisati foreign key (GebruiktAdresVan) References Organisaties(OgeID)"

Set rs = New adodb.Recordset

rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
Set rs = conn.OpenSchema(adSchemaForeignKeys, Array(Empty, Empty, "Organisaties", Empty, Empty, Empty))

count = 0
rs.MoveFirst
 'Do
    Debug.Print String(50, "-") & "Processing ADO Result Set " & String(50, "-")
    For Each fld In rs.Fields
        Debug.Print fld.Name,
    Next
    Debug.Print

    Do Until rs.EOF
    
    count = count + 1
    For Each fld In rs.Fields
      Debug.Print fld.Value,
    Next
    rs.MoveNext
    Debug.Print
    Loop
'Loop Until rs.EOF = False
rs.Close
conn.Close
Debug.Print " total fkeys: " & count
Debug.Assert count = 1
End Sub

--------------------------------------------------Processing ADO Result Set --------------------------------------------------
PK_TABLE_CATALOG            PK_TABLE_SCHEMA             PK_TABLE_NAME PK_COLUMN_NAME              PK_COLUMN_GUID              PK_COLUMN_PROPID            FK_TABLE_CATALOG            FK_TABLE_SCHEMA             FK_TABLE_NAME FK_COLUMN_NAME              FK_COLUMN_GUID              FK_COLUMN_PROPID            ORDINAL       UPDATE_RULE   DELETE_RULE   PK_NAME       FK_NAME       DEFERRABILITY 
test                        organisaties  OgeID         Null          Null          test                        organisaties  GebruiktAdresVan            Null          Null           1            NO ACTION     NO ACTION     NULL          NULL           3            

total fkeys: 1
[6 Oct 2003 3:55] Ronald van Raaij
Venu,

you have only 1 foreign key created. You need to create the entire database, (the script has already been added) the issue is not with the table itself, but with the amount of keys that refer *to* that table.

Ronald van Raaij
[1 Feb 2004 11:09] Ronald van Raaij
Additional:
The ADOX statement:

Set rstSchema = conn.OpenSchema(adSchemaForeignKeys, Array(Empty, Empty,
strTableName, Empty, Empty, Empty))

requests from the server an array with all the tables the "strTableName" is a foreign key in.

In the database I have 14 tables in which the Primary key of the table Organisaties ("OgeID") is a foreign key. As soon as I add another table that has a foreign key that points to Organisaties, the crash occurs. If I remove the foreign key (with "Drop constraint") the error goes away.
[5 Feb 2004 12:34] Miguel Solorzano
Are you tested this issue like Venu said using the release version ?

Because also I guess that Venus is right, I found some problems with
the debug version when testing it inserting  BLOB with > 16 MB. I resolved
the problem compiling the debug version with SAFEMALLOC.
[7 Feb 2004 17:06] Ronald van Raaij
Dear People,
sometimes it seems too much information is confusing. I admit I made a mistake in decsribing the bug in the first instance. However, the anomaly is different: If I use the release version, the bug occurs with 13 foreign keys, if I use the debug versiom the bug occurs with 14 foreign keys. Do not ask ME why I am just the messenger.

Ronald
[29 Jul 2004 1:26] Timothy Smith
I believe this is the same bug as #4519; please follow the progress of that bug for details on the resolution of this problem.

Thank you!

Timothy