| Bug #30933 | Timestamp field in View is not updated after changing data in the view. | ||
|---|---|---|---|
| Submitted: | 10 Sep 2007 14:26 | Modified: | 2 Aug 2013 8:16 |
| Reporter: | Vladimir Dusa | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.18 and higher | OS: | Windows (MyODBC 3.51.18) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | access, default value, timestamp | ||
[13 Sep 2007 12:31]
Vladimir Dusa
We have tried the MyODBC 3.51.17 - in this version the problem doesn't come. The first version in which the problem occurs is 3.51.18. Best regards Vladimír Duša
[5 Oct 2007 11:53]
Susanne Ebrecht
Hi Vladimir, thanks for writing the bug report. Regards, Susanne
[5 Oct 2007 14:04]
Susanne Ebrecht
This bug is a duplicate of bug #25055.
[24 Apr 2008 11:30]
Vladimir Dusa
Hello, I would like to ask you how can I solve my problem described in this Bug. If I have a table "proj_vorgaenge_data" and view "proj_vorgaenge" defined as "SELECT * from proj_vorgaenge_data where deleted=0;". Then I link proj_vorgaenge_data and proj_vorgaenge into MS Access 2003 DB. There I can add and edit rows direct in the table proj_vorgaenge_data with no problem. I can add new rows in the view proj_vorgaenge with no problem, but I can't edit any row in this view (see description of the bug) - because of write conflict. What should we do, if we need to modify data directly in the view linked in MS Access. I add a backup of example database with the table proj_vorgaenge_data and view proj_vorgaenge to this issue "test_db.sql" for your tests. I have tested it and the problem is still occuring in MyODBC 5.01.04. We still use MyODBC 3.51.17, but our new customer must use at least MyODBC 3.51.20 because of his applications. It is not possible to have two MyODBC versions on one computer. Thank you very much for your help Best regards Vladimír Duša BSA Informatik AG
[24 Apr 2008 11:33]
Vladimir Dusa
Testing backup
Attachment: test_db.sql (application/octet-stream, text), 7.24 KiB.
[24 Apr 2008 11:35]
Vladimir Dusa
I forgot one small thing - during linking the tables from the backup test_db.sql is needed to set manually the Primary Key of the View. MS Access will ask you for it. Choose the column Vor_Nr. Best regards Vladimir Dusa
[25 Apr 2008 7:15]
Tonci Grgin
Vladimir, I believe Susanne was right declaring the duplicate but I'll have to review your schema to be sure. Analyzing.
[30 Jul 2008 13:14]
Tonci Grgin
Trace file of both unsuccessful (view) and successful update (table) in that order
Attachment: SQLtrace-bug30933.zip (application/zip, text), 8.52 KiB.
[30 Jul 2008 13:19]
Tonci Grgin
Verified as described with attached database. There is no connection to TEXT, TIMESTAMP ... fields that I can see, just UPDATE statement on linked VIEW is not formed correctly as opposed to one formed for linked table. General query log opening linked table and linked view, trying to update linked view: 59 Query SELECT `Vor_Nr`,`Vor_Nr_int`,`Vor_ProJNr`,`Vor_Nr_Proj`,`Vor_Name`,`Vor_Typ`,`Vor_Verantw`,`Vor_Dauer_soll`,`Vor_Einheit`,`Vor_Dauer_ist`,`Vor_TerminFuerStart`,`Vor_TerminFuerStartIst`,`Vor_MussStartenAmTermin`,`Vor_Auf_Nr_intern`,`Vor_ext`,`Vor_abgeschl`,`Vor_Start`,`Vor_End`,`Vor_StartPlan`,`Vor_EndPlan`,`Vor_Color`,`Vor_Beware`,`Vor_Artikel`,`Vor_Stuckzahl`,`Vor_KopiertVon`,`Vor_ErstelltAm`,`Vor_Geteilt`,`Vor_Niveau`,`tmstmp`,`Vor_StartFix`,`Vor_EndFix`,`Vor_Tag`,`deleted`,`Vor_ExternAdresse` FROM `proj_vorgaenge` WHERE `Vor_Nr` = 1 OR `Vor_Nr` = 2 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 OR `Vor_Nr` = 3 59 Query SELECT `Vor_Nr`,`Vor_Nr_int`,`Vor_ProJNr`,`Vor_Nr_Proj`,`Vor_Name`,`Vor_Typ`,`Vor_Verantw`,`Vor_Dauer_soll`,`Vor_Einheit`,`Vor_Dauer_ist`,`Vor_TerminFuerStart`,`Vor_TerminFuerStartIst`,`Vor_MussStartenAmTermin`,`Vor_Auf_Nr_intern`,`Vor_ext`,`Vor_abgeschl`,`Vor_Start`,`Vor_End`,`Vor_StartPlan`,`Vor_EndPlan`,`Vor_Color`,`Vor_Beware`,`Vor_Artikel`,`Vor_Stuckzahl`,`Vor_KopiertVon`,`Vor_ErstelltAm`,`Vor_Geteilt`,`Vor_Niveau`,`tmstmp`,`Vor_StartFix`,`Vor_EndFix`,`Vor_Tag`,`deleted`,`Vor_ExternAdresse` FROM `proj_vorgaenge` WHERE `Vor_Nr` = 3 59 Query SET AUTOCOMMIT=0 59 Query UPDATE `proj_vorgaenge` SET `Vor_Name`='pero' WHERE `Vor_Nr` = 3 AND `Vor_Nr_int` = '2234' AND `Vor_ProJNr` = 0 AND `Vor_Nr_Proj` = 0 AND `Vor_Name` = 're56436' AND `Vor_Typ` = 'n' AND `Vor_Verantw` IS NULL AND `Vor_Dauer_soll` = 0.00000000000000000e+000 AND `Vor_Einheit` = 2 AND `Vor_Dauer_ist` = 0.00000000000000000e+000 AND `Vor_TerminFuerStart` IS NULL AND `Vor_TerminFuerStartIst` IS NULL AND `Vor_MussStartenAmTermin` = 0 AND `Vor_Auf_Nr_intern` IS NULL AND `Vor_ext` = 0 AND `Vor_abgeschl` = 0.00000000000000000e+000 AND `Vor_Start` IS NULL AND `Vor_End` IS NULL AND `Vor_StartPlan` IS NULL AND `Vor_EndPlan` IS NULL AND `Vor_Color` = -1 AND `Vor_Beware` = 0 AND `Vor_Artikel` = 0 AND `Vor_Stuckzahl` = 0 AND `Vor_KopiertVon` = 0 AND `Vor_ErstelltAm` IS NULL AND `Vor_Geteilt` IS NULL AND `Vor_Niveau` = 0 AND `tmstmp` = '2008-04-24 13:09:30' AND `Vor_StartFix` IS NULL AND `Vor_EndFix` IS NULL AND `Vor_Tag` IS NULL AND `deleted` = 0 AND `Vor_ExternAdresse` IS NULL 59 Query ROLLBACK 59 Query SET AUTOCOMMIT=1 --- Update on linked table: 58 Query UPDATE `proj_vorgaenge_data` SET `Vor_Name`='re56436' WHERE `Vor_Nr` = 3 AND `tmstmp` = '2008-04-24 13:09:30' Suggested workaround: Use linked table instead of view.
[30 Jul 2008 13:24]
Tonci Grgin
Same behavior observed in 5.1 too.
[30 Jul 2008 14:49]
Vladimir Dusa
Hello, I am sorry, but I don't understand, if this behaviour is accepted as a bug, or correct. Let me describe our problem: We have some tables on the MySQL Server containing all data (i.e. deleted rows too). Then we have corresponding views for each this table: "SELECT * FROM table WHERE deleted<>0". Data are deleted such, that the the column deleted is set to nonzero value. This construction is needed by our application beause of synchronizing data with external clients. Our SW is made in MS Access and uses linked tables and views from MySQL Server. Some tables are linked directly, some (that use the construction introduced above) are not linked directly, but only corresponding views are linked in MS Access. MS Access don't know if it works with linked table or linked view. Until MyODBC 3.51.17 was the behaviour correct, but in all newer versions comes an write conflict error during update of any row in the linked view (possibly because of wrong or missing update of timestamp?) Thank you very much for your help Best regards Vladimír Duša BSA-Informatik AG
[30 Jul 2008 15:22]
Jess Balint
Vladimir, We are working on the bug.
[13 Oct 2010 11:48]
Vladimir Dusa
Hello, until now still use MyODBC connector 3.51.17 and MySQL Community Server 5.0.45. After a long time we have tried newest Version of MySQL Community Server 5.1.51 and MyODBC 5.01.07. The problem with "write conflict" on linked view into ms access still occurs. Suggestion, that we should link tables instead of views is not acceptable for us, while our software architecture doesn't allow it. Could you help us to solve this problem please. We are should use newer version of MySQL, because Server 5.0.45 with MyODBC 3.51.17 is really old. Thank you very much for your help Best Regards Vladimír Duša
[7 Dec 2010 5:22]
Bogdan Degtyariov
Verified with 3.51.27 and 5.1.8. The writing problem occurs for every second update and TIMESTAMP field is never updates as it was with 3.51.14.
[2 Aug 2013 8:16]
Vladimir Dusa
Hello, we tested this issue again with MySQL 5.6.13 and MyODBC Connector 5.2 (5.02.02.00) after a longer time. Unfortunately the same problem with write conflict is still here. We are compelled to use the (ancient) MyODBC Version 3.51.17 where this problem don't occur. Is there any hope, that this issue will be solved and we will be able to use newer versions of MyODBC? Best Regards Vladimír Duša
Description: It is not possible to change any Data in a View linked in MS Access. It comes a write conflict. We still use the MyODBC Connector 3.51.14 - there is no problem. With Connector 3.51.18 and 3.51.19 comes write conflict. We have no experiences with other Versions of MyODBC Connector. We are using MS Access 2003, MySQL Community Server 5.0.45 How to repeat: 0) Create new Datbase in MySQL 1) Create new Table "mytable". CREATE TABLE `mytable` ( `ID` int(11) NOT NULL auto_increment, `Name` char(20) default NULL, `tmstmp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID`) ) ENGINE=InnoDB; 2) Create new View "myview" which selects all data from mytable. CREATE VIEW `myview` AS select `mytable`.`ID` AS `ID`, `mytable`.`Name` AS `Name`, `mytable`.`tmstmp` AS `tmstmp` from `mytable`; 3) Create new DSN using MyODBC Connector 3.51.19 a) Type some Data Source Name, Server, User, Password, Database created in step 0). b) In Tab Advanced choose "Don't Optimize Column Width" and "Return Matching Rows" which are important options for linked tables in MS Access. 3) Link myview in some MS Access Database using previously created DSN. In the upcoming warning window about missing Primary Key of the View "myview" choose the column "ID" and press OK. (In this moment must be the Connector 3.51.18 or 19 already installed. With views linked in access as a table using version 3.51.14 still work with version 3.51.18 or 19. Only after relinking the view with 3.51.18 or 19 will appear the problem.) 4) Open the newly linked "myview" in access (simply double click on the table) 5) Try to type some text in the Field "Name" and skip to some other row - new row will be created and updated. 6) Try to change the text in the Field "Name" in the previously created row => It comes warning about write conflict.