| 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: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51.06 | OS: | Windows (Windows 2000 SP4) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | ODBC5-RC | ||
[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]
MySQL Verification Team
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
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