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.

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

Hi Vladimir,

thanks for writing the bug report.

Regards,

Susanne

This bug is a duplicate of bug #25055.

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

Testing backup

Attachment: test_db.sql (application/octet-stream, text), 7.24 KiB.

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

Vladimir, I believe Susanne was right declaring the duplicate but I'll have to review your schema to be sure. Analyzing.

Trace file of both unsuccessful (view) and successful update (table) in that order

Attachment: SQLtrace-bug30933.zip (application/zip, text), 8.52 KiB.

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.

Same behavior observed in 5.1 too.

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

Vladimir,
We are working on the bug.

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

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.

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