Bug #25611 | ODBC 3.51.12 does not alow updates on views containing text fields | ||
---|---|---|---|
Submitted: | 13 Jan 2007 19:20 | Modified: | 2 Feb 2007 8:30 |
Reporter: | Louis Breda van | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | Connector / ODBC | Severity: | S1 (Critical) |
Version: | 3.51.12 | OS: | Any (VISTA RC2 64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | ODBC, text, UPDATE, VIEW |
[13 Jan 2007 19:20]
Louis Breda van
[25 Jan 2007 9:27]
Tonci Grgin
Hi Louis and thanks for your report. > Problem-1: Create a table containing a longtext, try to access that table via odbc There is no suitable (according to MS Access) field to be used as record identifier (PK) thus Access refuses updates. I think ADO behaves the same. It is, in my opinion, MS problem and should be reported to them. Please, also check on Bug#9863. You will find an explanation there why view with limit is not updateable. > Problem-2: - Create a table containing two text fields e.g. one somwhere in the middle and some near the end. ... I have no problems modifying LONGTEXT/other fields in MS Access. Can you provide me with sample test case demonstrating this behavior? Tested on MySQL server 5.0.34BK on WinXP Pro SP2 localhost (this is not a server problem!) MyODBC 3.51.13, 08.01.2007 create table testbug25611_1 (LText longtext); create table testbug25611_1_1 (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,LText longtext); create table testbug25611_2 ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, LText1 longtext, RowIDTxt1 INT UNSIGNED NOT NULL, LText2 longtext); INSERT INTO testbug25611_1 VALUES ("1-0123456789"), ("2-01234567890123456789"), ("3-012345678901234567890123456789"), ("4-0123456789012345678901234567890123456789"), ("5-01234567890123456789012345678901234567890123456789"), ("6-012345678901234567890123456789012345678901234567890123456789"), ("7-0123456789012345678901234567890123456789012345678901234567890123456789"), ("8-01234567890123456789012345678901234567890123456789012345678901234567890123456789"); INSERT INTO testbug25611_1_1(LText) VALUES ("1-0123456789"), ("2-01234567890123456789"), ("3-012345678901234567890123456789"), ("4-0123456789012345678901234567890123456789"), ("5-01234567890123456789012345678901234567890123456789"), ("6-012345678901234567890123456789012345678901234567890123456789"), ("7-0123456789012345678901234567890123456789012345678901234567890123456789"), ("8-01234567890123456789012345678901234567890123456789012345678901234567890123456789"); INSERT INTO testbug25611_2(LText1,RowIDTxt1,LText2) VALUES ("1-1-0123456789",11,"1-2-0123456789"), ("2-1-01234567890123456789",22,"2-2-01234567890123456789"), ("3-1-012345678901234567890123456789",33,"3-2-012345678901234567890123456789"), ("4-1-0123456789012345678901234567890123456789",44,"4-2-0123456789012345678901234567890123456789"), ("5-1-01234567890123456789012345678901234567890123456789",55,"5-2-01234567890123456789012345678901234567890123456789"), ("6-1-012345678901234567890123456789012345678901234567890123456789",66,"6-2-012345678901234567890123456789012345678901234567890123456789"), ("7-1-0123456789012345678901234567890123456789012345678901234567890123456789",77,"7-2-0123456789012345678901234567890123456789012345678901234567890123456789"), ("8-1-01234567890123456789012345678901234567890123456789012345678901234567890123456789",88,"8-2-0123456789012345678901234567890123456789012345678901234567890123456789012345 6789"); DROP VIEW IF EXISTS `test`.`vbug25611_2`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vbug25611_2` AS select `testbug25611_2`.`ID` AS `ID`,`testbug25611_2`.`LText1` AS `LText1`,`testbug25611_2`.`RowIDTxt1` AS `RowIDTxt1`,`testbug25611_2`.`LText2` AS `LText2` from `testbug25611_2` WHERE `testbug25611_2`.`ID` >= 3 AND `testbug25611_2`.`ID` <= 7; DROP VIEW IF EXISTS `test`.`vbug25611_1_1`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vbug25611_1_1` AS select `testbug25611_1_1`.`ID` AS `ID`,`testbug25611_1_1`.`LText` AS `LText` from `testbug25611_1_1` WHERE `testbug25611_1_1`.`ID` >= 2 AND `testbug25611_1_1`.`ID` <= 5; DROP VIEW IF EXISTS `test`.`vbug25611_1`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vbug25611_1` AS select `testbug25611_1`.`LText` AS `LText` from `testbug25611_1` LIMIT 5; Update results from table with 2 LONGTEXT fields: 070125 10:12:31 2 Query SELECT `vbug25611_2`.`ID` FROM `vbug25611_2` 2 Query SELECT `ID`,`LText1`,`RowIDTxt1`,`LText2` FROM `vbug25611_2` WHERE `ID` = 3 OR `ID` = 4 OR `ID` = 5 OR `ID` = 6 OR `ID` = 7 OR `ID` = 7 OR `ID` = 7 OR `ID` = 7 OR `ID` = 7 OR `ID` = 7 070125 10:12:41 2 Query SELECT `ID`,`LText1`,`RowIDTxt1`,`LText2` FROM `vbug25611_2` WHERE `ID` = 4 070125 10:12:43 2 Query SET AUTOCOMMIT=0 2 Query UPDATE `vbug25611_2` SET `RowIDTxt1`=444 WHERE `ID` = 4 AND `RowIDTxt1` = 44 2 Query COMMIT 2 Query SET AUTOCOMMIT=1 070125 10:12:49 2 Query SELECT `ID`,`LText1`,`RowIDTxt1`,`LText2` FROM `vbug25611_2` WHERE `ID` = 5 070125 10:12:51 2 Query SET AUTOCOMMIT=0 2 Query UPDATE `vbug25611_2` SET `LText2`='55-2-01234567890123456789012345678901234567890123456789' WHERE `ID` = 5 AND `RowIDTxt1` = 55 2 Query COMMIT
[25 Jan 2007 20:35]
Louis Breda van
Tonci, A few points as a first reaction: 1) in test-1 you create a table testbug25611_1 only containing a longtext. This is not my situation. My table does contain a lot of fields and among them a longtext; The loingtext is (of course)not the PK. So your responce to problem-1 is not applicable 2) my secondbug (in fact additional info to bug-1), I indicated that I have two text fields (not long text fields), in fact I did change the longtext to text to test the consequences. Also here the text was not at all the PK 3) I was already starting had the intention to retest when I saw there was a new odbc driver :>, but first action to download that new driver failed :) 4) the run-time error I get = '-2147467259(80004005) "Query-based update failed because the row to update could not be found. 5) I get that message if: a) there is a textfield (not a long text because that never works) in the view and b) the text field dot get a new value Below, simplified, the code in my frontend access DB hope it helps, of cause I will retest as soon as new mysql and or odbc driver versions become available. I also retested today. Sincerely, Louis ------------------ * code to link/open the DB Dim cn_ALData As New ADODB.Connection Dim ConnectionString1 as String * here a structure for the odbc parameters LinkedDBProf.ProfileName = "test_it" LinkedDBProf.DB_Server = "Localhost" LinkedDBProf.DB_DSNDriver = "{MySQL ODBC 3.51 Driver}" LinkedDBProf.DB_DSN = "MySQLDB" ' for future :) 'LinkedDBProf.DB_DSNDriver = "{MySQL Connector/ODBC v5}" LinkedDBProf.DB_DriverOptions = "3" LinkedDBProf.DB_Name = "mydata" LinkedDBProf.DB_UID = "root" LinkedDBProf.DB_PWD = "secret" ConnectionString1 = "DRIVER=" & LinkedDBProf.DB_DSNDriver + ";SERVER=" & LinkedDBProf.DB_Server _ + ";DATABASE=" & LinkedDBProf.DB_Name + ";UID=" & LinkedDBProf.DB_UID + ";PASSWORD=" & LinkedDBProf.DB_PWD _ + ";OPTION=" & LinkedDBProf.DB_DriverOptions + ";Packet Size=4096" cn_ALData.Open ConnectionString1 " open test which is a view on a selection of a table RS_Messages.Open "Test", cn_Data, adOpenForwardOnly, adLockOptimistic -------------------- Here the simplified loop: If RS_Messages.BOF = True And RS_Messages.EOF = True Then GoTo Ready 'recordset is emty RS_Messages.MoveFirst RS_Messages.EOF Then GoTo myready EventNo = 1 GoTo myIdentify myIdentify: RecordID = RS_Messages!RecordID 'to know the record if something is wrong * here the normal loop starts do_the_loop: RecordID = RS_Messages!RecordID 'to know the record if something is wrong * with this line in place the update works !! Description = "De aap zat in de boom" 'Nz(RS_Messages!Description) * with this line active the update fails !! Description = Nz(RS_Messages!Description) FieldX = myfunction(Description, blabla, 12345) RS_Messages!RecStatus = RecStatus + 1 'identificatie stap correct doorlopen RS_Messages!FieldX = 999 'for test used to be FieldX RS_Messages!ChangeDate = Now() RS_Messages.Update #### here the error occurs RS_Messages.MoveNext Stop 'for test purpuses If RS_Messages.EOF Then GoTo myready GoTo do_the_loop etc
[29 Jan 2007 9:47]
Tonci Grgin
Ok Louis, so it has nothing to do with Access and I think, Vista and 64bit... You are having problems with ADO and testbug25611_2 - like case, right? I will do some more tests.
[29 Jan 2007 12:06]
Tonci Grgin
Louis, I think this is a duplicate of Bug#19065. What's your opinion?
[30 Jan 2007 7:57]
Louis Breda van
Tonci, It could be the same problem, I also found another bug related to update problems in relation with blob/text. Read #5838 it also hints into the same direction. IMHO something is Not OK as soon as "bulk data fields" are involved, and it is quite serieus (blocking, since jou simply can not use the DB). Louis
[30 Jan 2007 11:50]
Tonci Grgin
Louis, can you please post a complete test case, as simple as possible using one of my tables if adequate, either as VBS or as VS project?
[30 Jan 2007 14:20]
Louis Breda van
sql to create the testdatabase
Attachment: testupdate 20070130 1514.sql (text/plain), 4.95 KiB.
[30 Jan 2007 14:26]
Louis Breda van
Tonci, here the code, I do not know how to attach a second file to this discussion. Option Compare Database Option Explicit Public Type LinkedDBProfile 'declaring x fields of different data types in the structure ProfileName As String DB_Name As String DB_DSN As String DB_DSNDriver As String DB_DriverOptions As String DB_Server As String DB_UID As String DB_PWD As String End Type Sub MyMain() Dim MyWs As Workspace Dim MyDB As Database Set MyDB = CurrentDb Dim ConnectionString As String Dim cn_MyData As New ADODB.Connection Dim RS_NotYetIdentifiedMessages As New ADODB.Recordset Dim LinkedDBProf As LinkedDBProfile LinkedDBProf.ProfileName = "testupdate" LinkedDBProf.DB_Server = "Localhost" LinkedDBProf.DB_DSNDriver = "{MySQL ODBC 3.51 Driver}" LinkedDBProf.DB_DSN = "MyTestData" 'LinkedDBProf.DB_DSNDriver = "{MySQL Connector/ODBC v5}" 'TBD LinkedDBProf.DB_DriverOptions = "3" LinkedDBProf.DB_Name = "testupdate" LinkedDBProf.DB_UID = "root" LinkedDBProf.DB_PWD = "secret" '######### choose your self ConnectionString = "DRIVER=" & LinkedDBProf.DB_DSNDriver + ";SERVER=" & LinkedDBProf.DB_Server _ + ";DATABASE=" & LinkedDBProf.DB_Name + ";UID=" & LinkedDBProf.DB_UID + ";PASSWORD=" & LinkedDBProf.DB_PWD _ + ";OPTION=" & LinkedDBProf.DB_DriverOptions + ";Packet Size=4096" cn_MyData.Open ConnectionString RS_NotYetIdentifiedMessages.Open "ToBeIdentifiedMessages", cn_MyData, adOpenForwardOnly, adLockOptimistic Call Identify_Events(RS_NotYetIdentifiedMessages, "blabla") RS_NotYetIdentifiedMessages.Close Set RS_NotYetIdentifiedMessages = Nothing cn_MyData.Close MyDB.Close Set MyDB = Nothing End Sub Sub Identify_Events(RS_Messages As ADODB.Recordset, LookUpTable As String) ' the database 'Dim MyWs As Workspace Dim MyDB As Database Set MyDB = CurrentDb Dim Machine As String Dim SubSystem As String Dim BatchCnt As Integer Dim RecordID As Long Dim MyID As Long Dim Description As String Dim EventNo As Long Dim NotRec As Long Dim RecStatus As Integer Dim Selection As String Stop If RS_Messages.BOF = True And RS_Messages.EOF = True Then GoTo Ready 'recordset is emty RS_Messages.MoveFirst If RS_Messages.EOF Then GoTo Ready EventNo = 1 tstnext: RecordID = RS_Messages!RecordID 'to know the record if something is wrong 'Description = "De aap zat in de boom" '############# with this assignment => OK Description = Nz(RS_Messages!Description) '############# and with this statement the problem !! 'MyID = GetMyID_HP(Px, Py, Pz, etc, Description, 0) MyID = 999 RS_Messages!MyID = 999 'MyID 'RS_Messages!ChangeDate = Now() RS_Messages.Update RS_Messages.MoveNext Stop If RS_Messages.EOF Then GoTo Ready GoTo tstnext Ready: Stop Set MyDB = Nothing End Sub
[1 Feb 2007 10:04]
Tonci Grgin
Louis, can you provide me please with general query log so I can see what's sent to server? If what I presume is correct, you will see UPDATE statement with *all* fields, even LONGTEXT in it. This is not a bug by my opinion. Explanation: - A view can not have PK. If a view had a PK and you do "select * from t2 and t2" and you have duplicates, how would the view behave? On the other hand, ADO can update data in following ways: adCriteriaKey = 0 Uses only the primary key - not applicable adCriteriaAllCols = 1 Uses all columns in the recordset - not applicable, hard to match BLOBs/FLOATs... adCriteriaUpdCols = 2 (Default) Uses only the columns in the recordset that have been modified - not applicable as you're matching BLOBs adCriteriaTimeStamp = 3 Uses the timestamp column (if available) in the recordset - aplicable if you have TS column in table/view What you can do is: Add TS column to Table/View and use rs.Properties("Update Criteria").Value = adCriteriaTimeStamp (or 3) or make UPDATE statement by yourself.
[1 Feb 2007 17:38]
Louis Breda van
Tonci, A reaction written in between: [1 Feb 11:04] Tonci Grgin Louis, can you provide me please with general query log so I can see what's sent to server? If what I presume is correct, you will see UPDATE statement with *all* fields, even LONGTEXT in it. This is not a bug by my opinion. >> I do not know how to do that, I know ODBC has that functionallity, but the VISTA ODBC manager in vista RC2 does not work properly at least not with the MySQL driver (which is not recognised) Explanation: - A view can not have PK. >> Why this remark? IMHO a view is not more than a query with can have authorisation aspects, which will probably also include the PK's of the table(s) below, >> My view in the example is a quit simple one, just a selection from only one table. Reason for the origional view was to limmit the amount of data, the table could have some mln records, the selection was mutch smaller. >> the view works as long as there are text fields involved If a view had a PK and you do "select * from t2 and t2" and you have duplicates, how would the view behave? >> IMHO not different from a query kind which is not updatable (like e.g. union select) >> let me also state that my simple view does not contain this kind of problems On the other hand, ADO can update data in following ways: adCriteriaKey = 0 Uses only the primary key - not applicable adCriteriaAllCols = 1 Uses all columns in the recordset - not applicable, hard to match BLOBs/FLOATs... adCriteriaUpdCols = 2 (Default) Uses only the columns in the recordset that have been modified - not applicable as you're matching BLOBs >> I am not aware of the fact that I do match blobs, I use a blob to calculate something and update another field with the result adCriteriaTimeStamp = 3 Uses the timestamp column (if available) in the recordset - aplicable if you have TS column in table/view >> to be honest this ADO behavoir / posibilitys are new to me. Looks like microsoft. I am not even aware of a timestamp column! What you can do is: Add TS column to Table/View and use rs.Properties("Update Criteria").Value = adCriteriaTimeStamp (or 3) or make UPDATE statement by yourself. >> I do not like hidden attributes like this. Do not know why it is nessesary as well. Sometimes I would like to have a changedate column but that would be a normal visable DB field Bottum line, - I still not understand why the view is not updatable; - and why it the field type does matter - and I am a little bit confused by the information you discussed I was considering another posible error cause. Assume that the fields in the record are not update by the DB in one transaction (which is wrong IMHO), the record update would be gone halfway the update due to the selection criteria To check this, I made a little change to my programm. I changed the view name to the name of the underlaying table and tried again. Result: Exactly the same problem!! Louis
[1 Feb 2007 20:18]
Tonci Grgin
Ummmm, Louis... where to start... - Log: I am talking of MySQL general query log (start mysqld-max-nt --log, or put "log" in server section of my.ini) not ODBC log. I want to see if any UPDATE statement reached MySQL server and if so, how is it formed.(IMPORTANT!) - Driver recognition: Not a problem of MySQL nor Vista :) MyODBC is a 32-bit driver and ODBC driver manager on MS 64bit platforms looks for and displays only 64bit drivers. Search bug database for explanation on how to start 32bit ODBC DM on MS 64bit platform. After you master that, you can turn general ODBC SQL log on and attach that too. Please read old reports on this since it's important that you do not start 64bit ODBC DM!!! - "A view can not have PK": If you do "SHOW KEYS ..." on your view you will see there are none defined. No keys, ADO has to use other ways of forming UPDATE statement for you, end of story. - "IMHO not different from a query kind which is not updatable": Beside the point. The point is there's no convinient way to update view (via PK for example) be it simple or complex. - "On the other hand, ADO can update data in following ways": This is just what ADO is about! Nothing "hidden" there. - "Bottom line," - I still not understand why the view is not updatable; - Check in information_schema.VIEWS table if it is marked as "updateable". If so then it is. (IMPORTANT!) - and why it the field type does matter - Consider following example: I have table with FLOAT field and value of 12.1234567890123456789 and ADO tries to match it with adCriteriaUpdCols = 2 (Default!!!) it will never pass correct value back to mySQL server... That's just what float precision is about on computers today. You can only match if difference of two values is smaller that equality treshold you set. - and I am a little bit confused by the information you discussed - Try reading about things involved here. It's a lot of technology (and a lot of reading). Until you provide general query log from MySQL server I will keep this report in "Not a bug". When I do get chance to analyze it I may revert my rulling, of course.
[1 Feb 2007 21:48]
Louis Breda van
Tonci, I will try to provide you with loggings, however not sure if I can manage that, because: - defining a log file with mysql administrator works - nothing appart from the server start is logged even not when an entry log is entered in the startup file - I have mysqld-nt running not the max version - when starting mysqld-nt --log a lot of errors occur on the commandline among them: "althroug a path was specified logtables ar used", "can not create test file xyz", "InnoDB: Operating system error number 32 in a file operation" So that logging could be trouble some, and for the odbc log, I probably need to do some workarounds. All to gather perhaps too much. Also note that I can not start nor stop the service using the tray monitor I have to do that by the windows service control gui. General spoken, I would not be surprised if a lot of problems are related to vista 64 right managment. I plan to switch to vista64 formal version soon, perhaps ... that solves some problems. But feel that a lot of incompability issues will probably still needs a solution Louis
[1 Feb 2007 22:36]
Tonci Grgin
Louis, I disagree. Consider following example (on the basis of bug25611_2 table, 2 INT, 2 LONGTEXT fields) which works: Private Sub bnBug25611_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnBug25611.Click Dim p_conn As New ADODB.Connection p_conn.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;OPTION=35;PWD=;DATABASE=test;UID=root") '16387 Dim p_cmd As New ADODB.Command Dim RowIDTxt1 As Integer Dim LText2 As String Dim p_rs As New ADODB.Recordset p_rs.ActiveConnection = p_conn 'remains of older test... p_rs.LockType = LockTypeEnum.adLockOptimistic p_rs.CursorType = CursorTypeEnum.adOpenKeyset p_rs.CursorLocation = CursorLocationEnum.adUseClient p_rs.Open("SELECT * FROM vbug25611_2", p_conn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic) p_rs.Fields.Refresh() p_rs.MoveFirst() p_rs.MoveNext() RowIDTxt1 = 444 p_rs.Update("RowIDTxt1", RowIDTxt1) p_rs.MoveNext() LText2 = "0123456789BLABLABLA" p_rs.Update("LText2", LText2) End Sub -- General query log shows well formed updates: 070201 23:28:21 72 Connect root@localhost on test 72 Query SET SQL_AUTO_IS_NULL=0 72 Query select database() 72 Query SELECT * FROM vbug25611_2 070201 23:28:23 72 Query UPDATE `test`.`vbug25611_2` SET `RowIDTxt1`=44 WHERE `ID`=4 AND `RowIDTxt1`=444 070201 23:28:26 72 Query UPDATE `test`.`vbug25611_2` SET `LText2`='BLABLABLA' WHERE `ID`=5 070201 23:28:29 72 Quit
[2 Feb 2007 8:30]
Louis Breda van
Tonci, When you send me this test example, I immediately saw that you did use other parameters to open the recordset. So I did start investigating the effects. Other parameters did solve or bypass the problem. I will walk throug the relevant lines in your code: - OPTION=35; versus option3 Both works; 3 is more common;do not know what is best - p_rs.ActiveConnection = p_conn 'remains of older test... p_rs.LockType = LockTypeEnum.adLockOptimistic p_rs.CursorType = CursorTypeEnum.adOpenKeyset p_rs.CursorLocation = CursorLocationEnum.adUseClient this part of your code is strange to me since it seems to have a variant on the connection option parameters something "redundant / strange" in that regard - p_rs.Fields.Refresh() Why did you add this statement!!?? Forcing a refresh when not necessary is just killing for performance. I did remove the statement and it wass still working - and the best from this expiriment, the soluting / workarround / ?? to the problem turned out to be the line "p_rs.CursorLocation = CursorLocationEnum.adUseClient" Just adding that one line solved the problem However: - why???? - whay only required if there is a text/ blob field involved???? - the view is simple updatable, just as I did expect from the start Sincerely, Louis
[3 Dec 2007 8:07]
Tonci Grgin
Louis, sorry I forgot about this report. adUseClient problem is explained in so many bug reports, you just have to search bugs db.