MySQL Bugs: #44199: Unable to INSERT columns into view using ODBC 5.1.6/ADO

Bug #44199	Unable to INSERT columns into view using ODBC 5.1.6/ADO
Submitted:	10 Apr 2009 0:11	Modified:	10 Apr 2009 22:29
Reporter:	John Kounis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am unable to insert columns into a view from Microsoft Access using ODBC connector 5.1.6. However, it works fine with ODBC connector 3.51.

How to repeat:
To repeat, first create a test table and a test view:
mysql> create table tblTEST1( fld1 varchar(50), primary key (fld1));
Query OK, 0 rows affected (0.01 sec)

mysql> create view viewTEST1 as SELECT fld1 AS viewFld1 FROM tblTEST1;
Query OK, 0 rows affected (0.01 sec)

Then run the following visual basic programs.

The first test using connector 5.1.6 results in the error "[MySQL][ODBC 5.1 Driver][mysqld-5.0.67-0ubuntu6]Unknown column 'fld1' in 'field list'":

  Set cnn = New ADODB.Connection
 cnnMySQL.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;"
  Set rst = New ADODB.Recordset
  rst.Open "viewTEST1", cnn, adOpenForwardOnly, adLockOptimistic
  rst.AddNew
  rst!viewFld1 = "TEST"
  rst.Update                ' This line causes an error
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing

The following code works just fine:

  Set cnn = New ADODB.Connection
 cnnMySQL.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;"
  Set rst = New ADODB.Recordset
  rst.Open "viewTEST1", cnn, adOpenForwardOnly, adLockOptimistic
  rst.AddNew
  rst!viewFld1 = "TEST"
  rst.Update                ' This line causes an error
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing

Hi John and thanks for your report.

I have two problems on my side:
  o I'm on W2K8SE x64
  o Don't have Access right now

Writing test case in VS2005 against remote MySQL server 5.1.31 on OpenSolaris x64 box works as expected:
  o Test case:
    Dim cnn As ADODB.Connection
    Dim strConnectString As String

    strConnectString = "Provider=MSDASQL.1;Persist Security Info=True;DSN=5-1-5-x64-on-opensol"

    cnn = New ADODB.Connection
    cnn.CommandTimeout = 1800
    cnn.ConnectionTimeout = 1800
    cnn.Open(strConnectString)
    cnn.Execute("drop view if exists vbug44199")
    cnn.Execute("drop table if exists bug44199")
    cnn.Execute("create table bug44199(Line1 VARCHAR(30) NOT NULL PRIMARY KEY)")
    cnn.Execute("create view vbug44199 AS select Line1 AS ViewLine1 from bug44199")

    Dim rs As ADODB.Recordset
    rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cnn
        .LockType = ADODB.LockTypeEnum.adLockOptimistic
        .CursorType = ADODB.CursorTypeEnum.adOpenDynamic
        .CursorLocation = ADODB.CursorLocationEnum.adUseClient
        .Open("SELECT * FROM vbug44199")
        .AddNew()
        rs.Fields(0).Value = "Test bug 44199"
        .Update()
        .Close()
    End With
    
    Dim rs1 As ADODB.Recordset
    rs1 = New ADODB.Recordset
    With rs1
        .ActiveConnection = cnn
        .LockType = ADODB.LockTypeEnum.adLockOptimistic
        .CursorType = ADODB.CursorTypeEnum.adOpenDynamic
        .CursorLocation = ADODB.CursorLocationEnum.adUseClient
        .Open("SELECT * FROM vbug44199")
        .Update("ViewLine1", "New name bug#44199")
        .Update()
        .Close()
    End With
    cnn.Close()
    cnn = Nothing

and the log:
tonci01@opensol:~# tail /var/lib/mysql/opensol.log 
		  130 Query	SELECT @@tx_isolation
		  130 Query	drop view if exists vbug44199
		  130 Query	drop table if exists bug44199
		  130 Query	create table bug44199(Line1 VARCHAR(30) NOT NULL PRIMARY KEY)
		  130 Query	create view vbug44199 AS select Line1 AS ViewLine1 from bug44199
		  130 Query	SELECT * FROM vbug44199
		  130 Query	INSERT INTO `test`.`vbug44199` (`ViewLine1`) VALUES ('Test bug 44199')
		  130 Query	SELECT * FROM vbug44199
		  130 Query	UPDATE `test`.`vbug44199` SET `ViewLine1`='New name bug#44199' WHERE `ViewLine1`='Test bug 44199'
090410 10:14:57  130 Quit	
tonci01@opensol:~# 

I have also tested this:
            .AddNew()
            rs.Fields(0).Value = "Test bug 44199"
            .Update()
            .AddNew()
            rs.Fields("ViewLine1").Value = "Test bug 44199-take 2"
            .Update()
and it works proving rs.Fields structure has been populated correctly. What might be happening at your side is that server is not returning enough metadata. You can check this in BugsDB, look for views and Org_table (you can obtain this info by starting cmd line client with -T option in MySQL server 5.0 or with --column-type-info in 5.1+).

I tested with c/ODBC 5.1.5GA and latest snapshot, 5.1.6r823. ADODB used is 7.0.3300.0.

What you can do now is to attach ODBC traces from both c/ODBC 3.51 and 5.1 so I can see what Access messed up. A snip from general query log from MySQL server would be nice too.

Ok, found 32bit XPPro box with Office2003Pro.

In "Tables" I linked view in question and checked fetch/insert works.
In "Forms" I created form showing linked view with wizard.
  Added button and following code:
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=**; PORT=3306; DATABASE=test;OPTION=3;User=**;Password=**;"
  Set rst = New ADODB.Recordset
  rst.Open "vbug44199", cnn, adOpenForwardOnly, adLockOptimistic
  rst.AddNew
  rst!ViewLine1 = "TEST"
  rst.Update
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

Everything works as expected.

I tried your code and it works fine on my machine. The difference is that my code uses a server-side cursor (rs.CursorLocation = adUseServer), whereas your code uses a client-side cursor (adUseClient).

The bug only appears if rs.CursorLocation is set to "adUseServer"

I'll get the ODBC logs for you and attach them in my next post.

SQL 5.1.6 bug (with error at the end)

Attachment: SQL51.LOG (application/force-download, text), 194.48 KiB.

SQL 3.51 log (no errors)

Attachment: SQL351.LOG (application/force-download, text), 190.51 KiB.

For further information, I am using:

MS Access 2003 (11.8166.8202) SP3
ADO version 2.81.1132.0 (NOTE: You mentioned a completely different version number for ADO, so I'm not sure which number you used. This is the version number of msado15.dll on my machine)
ODBC Connector: 5.1.6r823
MySQL version: 5.0.67-0ubuntu6

This is working correctly w/5.1.30 and server cursor:
2057 Query	INSERT INTO viewTEST1 ( viewFld1) VALUES(_latin1'TEST')

I will try w/5.0 tomorrow.