MySQL Bugs: #86371: ODBC Connector MS Access #Deleted on Insert

Bug #86371	ODBC Connector MS Access #Deleted on Insert
Submitted:	18 May 2017 13:22	Modified:	9 Jul 2017 7:37
Reporter:	Barry Baruch	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	See description	OS:	Windows (See description)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	#Delete, access, insert

Description:
Hi,

We've been using MS-Access as frontend and MySQL as backend on machines for years and since last week we started having a very strange problem.

We are working with windows 2000/7 32 bit and windows 7 64 bit PCs.
Connector versions: 5.2.4 and 5.3.8.

Some of the tables started showing #Deleted right after inserting a new record and refreshing the view (F5) shows the new record.

How to repeat:
What I discovered so far:

I created a new table with ID column (INT(11), PK, NN, AI) and Text column (LongText).
- Adding a new record by typing in the text column and move to another record resulted with the #Deleted text on both fields.
- Adding a new line by typing in the ID column and moved to another record resulted with the record being added as it should be.
- Changing the text column to VarChar(255) "fixed" the problem
- Changing the text column to VarChar(any number bigger than 255 or MediumText, Text or TinyText) caused the same #Deleted behavior.

It may be due to a table corruption. I would try REPAIR TABLE/OPTIMIE TABLE depending on storage engine - refer https://dev.mysql.com/doc/refman/5.7/en/table-maintenance-sql.html

Also try google "access #deleted" or similar. There are more reasons for this error in Microsoft programs - including incompatible character sets between ODBC-driver and data.

-- Peter
-- not a MySQL/Oracle person

Thanks Peter for the quick response, but I already did that and as I wrote this happened also on a completely new table I just created.

Please read:
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-ac...

thank you Miguel, but this also didn't help.

You did the below?

 Include a TIMESTAMP column in all tables that you want to be able to update. For maximum portability, do not use a length specification in the column declaration (which is unsupported within MySQL in versions earlier than 4.1).

Include a primary key in each MySQL table you want to use with Access. If not, new or updated rows may show up as #DELETED#.

Dear Peter,

Sorry for the delayed reply, I'm at this office only twice a week.

I tried those 2 suggestions last week and none helped.
my table contains 4 columns.
ID: INT(11), PK, NN, AI
bbVarChar: VARCHAR(45)
bbLongText: LONGTEXT
bbTimeStamp: TimeStamp

the problem started last week after working for years: new records shown as #Deleted only when writing only in the bbLongText field and only in the bbVarChar field when it is set to any text field type with size longer than 256.

Best

Hello  Barry Baruch,

Thank you for your feedback.
Could you please provide repeatable test case  (exact steps, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?
Does the problem occurred after upgradation? If so, can you please specify from which version to version did you tried to upgrade.

Thanks,
Chiranjeevi.

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