MySQL Bugs: #67273: cant edit records in linked MySQL table under MS Access97;2003;2010

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:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	>3.51.06	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
3.51.06 is last working Connector

found one tread about it, but without solution
http://board.issociate.de/thread/507145/MS-AccessMyODBC-Interface-Issue.html

How to repeat:
part1:
when try edit some previous edited record comes mesage
`The data has been changed`
after press [ok] receord can be changed

part2:
stay on any textual field and add one space to the end of existing text
then go to next record and have `Write Conflict - Drop Changes` dialog

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.

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

Please provide the ouput of show create table table_name. Thanks.

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

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

MS Access 2007 Works ok

Attachment: msaccess_bug67273.PNG (image/png, text), 69.95 KiB.

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

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.

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".

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

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.

130308_myodbc.sql

Attachment: 130308_myodbc.sql (application/octet-stream, text), 4.08 KiB.

Connection line is cut to 126

Attachment: 130308_ODBC_Connector_51_52.png (image/png, text), 41.15 KiB.

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

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.

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?

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".

'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?

MySQL Server versions 4.1 and 5.0 are deprecated.
Have you tried repeating the problem with MySQL 5.1 or 5.5?

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".