| Bug #42088 | Query with complex LEFT JOIN on MySQL ODBC workspace crashes Access | ||
|---|---|---|---|
| Submitted: | 13 Jan 2009 16:09 | Modified: | 15 Jan 2009 11:13 |
| Reporter: | Chris Wilson | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1 | OS: | Windows (XP Service Pack 2, Access 2003) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | microsoft access odbc workspace left join query crash | ||
[13 Jan 2009 16:35]
Tonci Grgin
Hi Chris and thanks for your report. I think it is *not* our fault. I just tested your query on this environment: W2K8 x64 server, MySQL server 5.1.30GA Wx64, c/ODBC 5.1.5Wx64, ODBCTE32W x64 W2K8 x64 server, MySQL server 5.1.30GA Wx64, c/ODBC 5.1.5Wx32, ODBCTE32W x32 SELECT * FROM (test AS this_year LEFT JOIN test AS next_year ON next_year.schoolyear=this_year.schoolyear+1) Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN '5-1-05-local'. SQLExecDirect: In: hstmt = 0x0000000000659FB0, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: 0 rows affected by INSERT/UPDATE/DELETE or other statement. SQLExecDirect: In: hstmt = 0x0000000000659FB0, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "SchoolYear", "SchoolYear" 0 rows fetched from 2 columns. ODBCTE is default *Microsoft* ODBC client (you get one either from MS downloads or by purchasing VS) and as error *is* reported in JET engine I suggest you take this to Microsoft.
[13 Jan 2009 16:38]
Tonci Grgin
And with years from 1999-2009, using 32 bit versions of driver and odbcte: Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN '5-1-5-localhost-32'. SQLExecDirect: In: hstmt = 0x00842C40, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "SchoolYear", "SchoolYear" 1999, 2000 2000, 2001 2001, 2002 2002, 2003 2003, 2004 2004, 2005 2005, 2006 2006, 2007 2007, 2008 2008, 2009 2009, <Null> 11 rows fetched from 2 columns.
[13 Jan 2009 16:40]
Tonci Grgin
Chris, before calling MS, maybe you can test this query with MSSQL server. It is my guess it would crash too (meaning MSSQL server, not Access) as they tend to make same restrictions in all of their software.
[13 Jan 2009 16:44]
Chris Wilson
Hi Tonci, Thanks for looking into this, but I found it difficult to understand the information that you provided. Were you able to reproduce the crash? Did you do so using Microsoft's ODBC driver (ODBCTE32W) rather than MyODBC? Did you use the VBA code at all? From your description it looks like you were able to get results from the query rather than a crash, so I suspect that the problem is not being reproduced. Cheers, Chris.
[13 Jan 2009 16:53]
Chris Wilson
Hi Tonci, You're right, it does crash with SQL Server Express. Sorry for not testing that first. I will try to report it to Microsoft but not sure if I want to take the risk of paying for per-incident support. Is there any other way to raise a crashing bug with them? Cheers, Chris.
[15 Jan 2009 10:59]
Tonci Grgin
Hi Chris. I know it doesn't comfort you it crashes MSSQL server too but that's the story I see a lot around. Just a few words of explanation before closing the report: o ODBCte is *generic* Microsoft ODBC *client*. Thus I did used MySQL c/ODBC otherwise odbcte would not work :) I use it because it is de-facto the only ODBC client fully complying all ODBC standards. If something works there, no matter against which server, then it is signal that used driver behaves correctly. On the other hand, it does not accept scripts and such, only SQL commands connected driver understands and standard ODBC calls. For example, if we made correct SQLTables() in c/ODBC, calling it from odbcte with c/ODBC driver should work, and so on. Closing this report as it appears to be Microsoft problem.
[15 Jan 2009 11:03]
Tonci Grgin
Sorry, I know nothing of Microsoft support. Never had anything to do with them. I just learned how to live with their beta SW over the years... But I guess that's common case too.
[15 Jan 2009 11:09]
Chris Wilson
Hi Tonci, I understand that this is not a bug in MySQL, but I disagree with the testing methodology. Issuing a query from ODBCte32 to myODBC driver may not crash, which may mean that myODBC implements the ODBC standard properly. But that is not the issue that I reported. If Access crashes when retrieving data from myODBC, and it can be worked around by changing the myODBC driver, then in my opinion there is a case for doing so, as users may blame myODBC for the crash even though it's really Access' fault, and it's much easier to change myODBC than Access. Cheers, Chris.
[15 Jan 2009 11:13]
Chris Wilson
PS: thanks for your help in investigating the issue even though it turned out not to be a MySQL bug. Cheers, Chris.
Description: The code below causes Access to crash. The crash appears to happen in JET libraries, but this may be due to invalid data (e.g. capabilities) being returned to JET by the MyODBC driver. I don't have a support contract with Microsoft or a desire to pay for incident support for them to resolve this without confirming that it's not a bug in MyODBC first. Dr. MINGW gives the following crash dump information: MSACCESS.EXE caused an Access Violation at location 1b039b3b in module msjet40.dll Reading from location 074eb898. Registers: eax=074eb870 ebx=06e6a790 ecx=06e6bd94 edx=0013eba0 esi=06e6bd20 edi=06e6bd30 eip=1b039b3b esp=0013e9c8 ebp=0013e9c8 iopl=0 nv up ei pl nz na po nc cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000206 Call stack: 1B039B3B msjet40.dll:1B039B3B Ordinal118 1B0E3F0E msjet40.dll:1B0E3F0E Ordinal311 1B0E3CDA msjet40.dll:1B0E3CDA Ordinal311 1B040EA5 msjet40.dll:1B040EA5 Ordinal109 1B040EA5 msjet40.dll:1B040EA5 Ordinal109 1B040E21 msjet40.dll:1B040E21 Ordinal109 1B02E61A msjet40.dll:1B02E61A Ordinal1101 1B037053 msjet40.dll:1B037053 Ordinal1101 1B027111 msjet40.dll:1B027111 Ordinal908 I am unable to copy and paste the text from the Windows crash dump, although I could attach a minidump file if that would help. How to repeat: Create a new database. Create a new module within it. Place the following VBA code into that module and run it using the Run command. sub Test() Dim ConnectString As String ConnectString = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=zambia;UID=camfed;PWD=CAMFED;OPTION=35" Set odbcws = DBEngine.CreateWorkspace("", "", "", dbUseODBC) 'Set Conn = odbcws.OpenConnection("", _ dbDriverNoPrompt, False, "ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=zambia;UID=camfed;PWD=CAMFED;OPTION=35") Dim conn As DAO.Database Set conn = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt, False, ConnectString) conn.OpenRecordset "SELECT 1" On Error Resume Next CurrentDb.TableDefs.Delete "test" conn.TableDefs.Delete "test" On Error GoTo 0 Dim def As New DAO.TableDef def.Name = "test" Dim field As DAO.field Set field = def.CreateField("SchoolYear", dbInteger) Dim index As DAO.index Set index = def.CreateIndex("SchoolYear") index.Fields.Append index.CreateField("SchoolYear") def.Fields.Append field conn.TableDefs.Append def Set def = New DAO.TableDef def.Name = "test" def.Connect = ConnectString def.SourceTableName = "test" CurrentDb.TableDefs.Append def ' conn.Execute "CREATE TABLE test (SchoolYear INTEGER)" conn.OpenRecordset "SELECT * " & _ "FROM (test AS this_year " & _ "LEFT JOIN test AS next_year " & _ "ON next_year.schoolyear=this_year.schoolyear+1);" end sub The essential point seems to be the LEFT JOIN condition. Removing the "+1" from the condition makes it a simpler join which does not cause a crash. Suggested fix: Use a debugger to verify that Access did not call MyODBC with invalid parameters or receive an invalid response from MyODBC. Escalate to Microsoft.