Bug #67273 | cant edit records in linked MySQL table under MS Access97;2003;2010 | ||
---|---|---|---|
Submitted: | 17 Oct 2012 13:11 | Modified: | 1 Jun 2013 6:02 |
Reporter: | Martins Brivnieks | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | >3.51.06 | OS: | Windows |
Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[17 Oct 2012 13:11]
Martins Brivnieks
[17 Oct 2012 14:28]
MySQL Verification Team
Thank you for the bug report. Have you followed the rrcommendation at: http://dev.mysql.com/doc/refman/5.0/es/myodbc-usagenotes-apptips.html#myodbc-usagenotes-ap... Thanks.
[18 Oct 2012 8:39]
Martins Brivnieks
Thanks for answer didn't found option `Simulate ODBC 1.0` option `Return matching rows` (FOUND_ROWS=1 in the myodbc52a-test.dsn) solve only problem which i describe in "part2:" but now i found another new problem(Part3), in MS Access is table [MSysObjects] where stored connections info for linked tables and there field `Connect` contains incomplete data when linked with 5.1 or 5.2 connector ?len("DRIVER={MySQL ODBC 3.51 Driver};DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=generator;SERVER=10.0.0.102;UID=xxxxxxx;PASSWORD=123456;PORT=3306;OPTION=2051;STMT=;") 157 ?len("DRIVER={MySQL ODBC 5.1 Driver};UID=xxxxxxx;SERVER=10.0.0.102;PORT=3306;DATABASE=top_songs;PASSWORD=123456;CHARSET=cp1257;FILED") 126 ?len("DRIVER={MySQL ODBC 5.2a Driver};UID=xxxxxxx;SERVER=10.0.0.102;PORT=3306;DATABASE=shedules;PASSWORD=123456;FOUND_ROWS=1;FILEDSN") 126 ?len("DRIVER={MySQL ODBC 5.2a Driver};UID=xxxxxxx;SERVER=10.0.0.102;PORT=3306;DATABASE=shedules;PASSWORD=123456;FOUND_ROWS=1;BIG_PAC") 126
[18 Oct 2012 21:31]
MySQL Verification Team
Please provide the ouput of show create table table_name. Thanks.
[19 Oct 2012 6:59]
Martins Brivnieks
CREATE TABLE `_test_121019` ( `Data` varchar(50) default NULL, `id` int(11) unsigned NOT NULL auto_increment, `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `Updated` (`Updated`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Insert InTo `_test_121019` (Data) Values ('text0'),('text1'),('text2'),('text3'),('text4'),('text5'),('text6'),('text7'),('text8'),('text9') i can edit any record once, problem comes when try edit again www.superfm.lv/m/MySQL/121019_ODBC_Connector_51_52.png
[26 Oct 2012 6:27]
Bogdan Degtyariov
Connector/ODBC 5.2.2a works perfectly for me in MS Access 2007 (please check the attached screenshot). I edited all records and the changes were stored in the linked table correctly. Probably you need to upgrade your MS Access 97 (I guessed it from the screenshot) as it is not supported and is not getting along well with the latest ODBC drivers. Accordingly to Microsoft Products Lifecycle page the end of support for Access 97 is on 31 Jan 2004: http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=+&Filter=FilterNO
[26 Oct 2012 6:28]
Bogdan Degtyariov
MS Access 2007 Works ok
Attachment: msaccess_bug67273.PNG (image/png, text), 69.95 KiB.
[26 Oct 2012 7:10]
Martins Brivnieks
ok, will try laiter stay on ma97 decided after made speedtests with ma2000 and ma2003 where performace was ~8-12% slower 2007;2010 not tested jet but interesting why with connector 3.51.06 there isnt present described problem
[26 Oct 2012 8:32]
Bogdan Degtyariov
MySQL ODBC Connector 3.51.06 had many ODBC API functions not implemented. Therefore MS Access used the limited set of functions. However, ODBC API evolved, MS Access and MySQL ODBC driver fixed a few bugs, which results in better compatibility of new versions of Access and new versions of MySQL ODBC drivers.
[27 Nov 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[15 Feb 2013 16:20]
Martins Brivnieks
have finaly installed msAccess 2010, the problem "part1:" still here http://www.superfm.lv/m/MySQL/130215_ODBC_Connector_51_52.png 1. edit record id=1 2. move to another record 3. try edit id=1 currently have to link tables with two connectors 3.51.06 to able edit 5.02.03 to able see non latin characters
[21 Feb 2013 9:44]
Bogdan Degtyariov
I still cannot repeat the problem, followed the instructions: - edited record with id=1 - moved to another record - edited record with id=1 - result saved to the database with no problems Can you enable the ODBC tracing on ODBC Administrator and send the trace file to me? Note that the trace should be enabled BEFORE you start Access. Thanks.
[8 Mar 2013 10:44]
Martins Brivnieks
130308_myodbc.sql
Attachment: 130308_myodbc.sql (application/octet-stream, text), 4.08 KiB.
[8 Mar 2013 10:55]
Martins Brivnieks
Connection line is cut to 126
Attachment: 130308_ODBC_Connector_51_52.png (image/png, text), 41.15 KiB.
[8 Mar 2013 10:55]
Martins Brivnieks
about "part1" in logfile(130308_myodbc.sql) can't find nothing about `The data has been changed` http://bugs.mysql.com/file.php?id=19822 about "part3" when database.dsn file contains parameter PSW= then stored Connection strings are cut to 126 symbols in MsAccess table MSysObjects when password not stored then comes connection setup dialog where to enter manualy, but in MsAccess table MSysObjects storred different way and not cut http://bugs.mysql.com/file.php?id=19823
[13 Mar 2013 10:22]
Bogdan Degtyariov
Hi Martins, The log you provided is just the query log made by ODBC driver. I asked for the ODBC trace, which is different. It can be enabled from ODBC Administrator -> Tracing -> Start Tracing Now. You have to make sure that the directory for "Log File Path" exists and has the write permission.
[25 Mar 2013 11:40]
Bogdan Degtyariov
Martins, Thanks for providing the ODBC traces. Unfortunately, it still does not show the signs of the problem. The UPDATE query in the log file returns SQL_SUCCESS and SQLRowCount returns (1): SQL_130322_3_try_edit_again_NOK(msg_TheDataHasBeenChanged).LOG For your information: SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement. 15cc-b38 ENTER SQLExecDirect HSTMT 0x0914E408 UCHAR * 0x0F402128 [ -3] "UPDATE `_test_121019` SET `Data`=? WHERE `id` = ? AND `Data` = ? AND `Updated` = ?\ 0" SDWORD -3 15cc-b38 EXIT SQLExecDirect with return code 0 (SQL_SUCCESS) HSTMT 0x0914E408 UCHAR * 0x0F402128 [ -3] "UPDATE `_test_121019` SET `Data`=? WHERE `id` = ? AND `Data` = ? AND `Updated` = ?\ 0" SDWORD -3 15cc-b38 ENTER SQLRowCount HSTMT 0x0914E408 SQLLEN * 0x0018F2D0 15cc-b38 EXIT SQLRowCount with return code 0 (SQL_SUCCESS) HSTMT 0x0914E408 SQLLEN * 0x0018F2D0 (1) <--- the number of updated rows Perhaps you could come up with a short VB script that demonstrates the problem?
[26 Apr 2013 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 Apr 2013 11:04]
Martins Brivnieks
'Part2' - solved 'Part1': problem can't be shown in vb code, it comes only when edit records in linked table direct in access window or table used in form found this problem deppendends also on MySQL server version 4.1.22-standard - problem 5.0.67 - problem 5.6.10-log - All OK but currently i can't go to newer MySQL server version cause: 1. last stable connector 3.51.06 can't link tables with underline in names if MySQL server newer than 5.0.67 2. connector 5.02.04 has other problems too - every 2-3 hours come '[MySQL][ODBC 5.2(a) Driver][mysqld-5.6.10-log]MySQL client ran out of memory' 'Part3': any comments?
[1 May 2013 6:02]
Bogdan Degtyariov
MySQL Server versions 4.1 and 5.0 are deprecated. Have you tried repeating the problem with MySQL 5.1 or 5.5?
[2 Jun 2013 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".