MySQL Bugs: #92947: "#Deleted" rows in MS Access for TEXT columns containing

Bug #92947	"#Deleted" rows in MS Access for TEXT columns containing > 1024 characters
Submitted:	25 Oct 2018 15:12	Modified:	10 Dec 2019 4:22
Reporter:	Ronny H.	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.16	OS:	Windows (10 (1803))
Assigned to:		CPU Architecture:	Any (x64)
Tags:	#DELETED, 1024, LENGTH, Microsoft Access, text

Description:
This bug affects MySQL Connector ODBC 8.0.13 on MySQL Server 5.7.24, Microsoft Access 2016 MSO (16.0.10827.20181) 64-bit and Windows 10 (1803) x64.

If text with length > 1024 is inserted in tables with columns TEXT, MEDIUMTEXT or LONGTEXT, Microsoft Access shows #Deleted rows if you open the table in a form or other view. Pressing F5 (refresh) in an open view shows the data correctly. Closing and reopening a view causes #Deleted rows again. If the number of characters is <= 1024, the data is shown correctly (without #Deleted).

How to repeat:
1.) Create table:

CREATE TABLE odbc_debug.test (
	id int(11) NOT NULL AUTO_INCREMENT,
	t_TEXT text DEFAULT NULL,
	t_TINY tinytext DEFAULT NULL,
	t_MEDIUM mediumtext DEFAULT NULL,
	t_LONG longtext DEFAULT NULL,
	PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_unicode_ci;

2.) Fill data (there are 1025 'x' per column, except for t_TINY):

INSERT INTO test(id, t_TEXT, t_TINY, t_MEDIUM, t_LONG) VALUES(1, NULL, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', NULL, NULL);
INSERT INTO test(id, t_TEXT, t_TINY, t_MEDIUM, t_LONG) VALUES(2, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', NULL, NULL, NULL);
INSERT INTO test(id, t_TEXT, t_TINY, t_MEDIUM, t_LONG) VALUES(3, NULL, NULL, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', NULL);
INSERT INTO test(id, t_TEXT, t_TINY, t_MEDIUM, t_LONG) VALUES(4, NULL, NULL, NULL, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

3.) Create new database in Microsoft Access and choose ODBC source (mysql-connector-odbc-8.0.13-winx64, ODBC;DSN=<name>;;TABLE=test, no options set) and link "test" table.

4.) View "test" table. You will see

id              t_TEXT        t_TINY        t_MEDIUM        t_LONG
1               xxxxxxx…
#Deleted        #Deleted      #Deleted      #Deleted        #Deleted
#Deleted        #Deleted      #Deleted      #Deleted        #Deleted
#Deleted        #Deleted      #Deleted      #Deleted        #Deleted

5.) Press F5 (refresh view). You will see the proper data without #Deleted.

6.) Reduce number of 'x' to 1024 or less. Close view. Open view. You will see no #Deleted.

Suggested fix:
Bug does not occur on mysql-connector-odbc-5.3.10-winx64 or devart ODBC Driver for MySQL.

No other workaround known.

"#Deleted" rows in Microsoft Access

Attachment: shoot-1.png (image/png, text), 15.88 KiB.

The test table and data

Attachment: test.sql (application/octet-stream, text), 3.88 KiB.

Access

Attachment: 92947.png (image/png, text), 45.81 KiB.

Thank you for the bug report. I couldn't repeat. On your side try adding a column timestamp and check it helps.

Thanks for your replay.

I already tried https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-ac... Furthermore we have the issue on 6 different computers. I even tried it on different MySQL installations. If I drop in replace ODBC 8.0.13 with ODBC 5.0.10 it works. All versions above 5.0.10 seem not to work.

Please, can you try the following? Close Access. Open Access. Open test database. Double click on the "test" table. Do you see #Deleted now? #Deleted temporarily disappears if you refresh (e. g. F5) an open table.

The bug affects our production system. So we really appreciate a fix.

Thanks!

Can you show me your ODBC User DSN for the test setting, please?

Thank you for the feedback. I followed exactly your instructions NOT REFRESH, open Access, open database test, double click linked table test and the result is the screenshot I attached.

DSN

Attachment: dsn.png (image/png, text), 36.75 KiB.

Hi,

I am experiencing exactly the same problem.

I have a table with some mediumtext fields. They should be able to store at least 5000 characters. As soon as I enter more than 1024 characters the row is displayed as #deleted in the linked access table. After a refresh with F5 the data is displayed. If I close the table in access and open it again, it is again shown as #deleted.

I use ODBC connector 8.0.13 32-bit, Access 2016 (Office 365) 32-bit and MySql version 8.0.12 and windows 10 Pro 64-bit.

Any ideas how this issue can be solved?

Thanks and best regards

Thanks, Janina Bobrich! Nice to see that I’m not alone.

Hello, Miguel Solorzano,

I did more screenshots, please, see attachment. Please, can you compare them with your system and steps?

Best regards

Screenshots steps and versions

Attachment: Screenshots.zip (application/x-zip-compressed, text), 629.87 KiB.

I traced the ODBC communication while double clicking the table in Access (#Deleted is shown). You can see the log in the attachment. There are entries like

MSACCESS        884-1b48	ENTER SQLGetData 
		HSTMT               0x000001FFAA5D9420
		UWORD                        4 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x000001FFD65829F8 
		SQLLEN                 32000
		SQLLEN *            0x0000008D80DBB690

MSACCESS        884-1b48	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x000001FFAA5D9420
		UWORD                        4 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x000001FFD65829F8 <Invalid string length!> [-1]
		SQLLEN                 32000
		SQLLEN *            0x0000008D80DBB690 (-1)

<Invalid string length!> seems to be interesting because the number of characters is > 1024. At TINYTEXT it logs

MSACCESS        884-1b48	ENTER SQLGetData 
		HSTMT               0x000001FFAA5D9420
		UWORD                        3 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x000001FFD65829F8 
		SQLLEN                 32000
		SQLLEN *            0x0000008D80DBB690

MSACCESS        884-1b48	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x000001FFAA5D9420
		UWORD                        3 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x000001FFD65829F8 [     510] "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
		SQLLEN                 32000
		SQLLEN *            0x0000008D80DBB690 (510)

We can see the x’s and now error occurs.

Maybe this helps further investigation?

ODBC trace log while #Deleted occurs

Attachment: SQL.LOG (application/octet-stream, text), 30.30 KiB.

Please test adding to the table a TIMESTAMP column to your test table.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| odbc_debug         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.02 sec)

mysql> USE odbc_debug
Database changed
mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_TEXT` text COLLATE utf8_unicode_ci,
  `t_TINY` tinytext COLLATE utf8_unicode_ci,
  `t_MEDIUM` mediumtext COLLATE utf8_unicode_ci,
  `t_LONG` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE test ADD COLUMN t_TIMESTAMP TIMESTAMP;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT t_TIMESTAMP FROM test;
+---------------------+
| t_TIMESTAMP         |
+---------------------+
| 2018-10-26 13:55:35 |
| 2018-10-26 13:55:35 |
| 2018-10-26 13:55:35 |
| 2018-10-26 13:55:35 |
+---------------------+
4 rows in set (0.00 sec)

mysql>

Thank you! I did it. I executed your ALTER query. Please, see the attachment. Sadly, #Deleted still occurs.

It’s really strange that the issue occurs at my system, but not in yours. I have no idea what to do now …

Screenshots, after adding TIMESTAMP column

Attachment: Screenshots-2.zip (application/x-zip-compressed, text), 85.28 KiB.

Deleted#

Attachment: 92947.png (image/png, text), 41.02 KiB.

Thank you for the feedback. I was to repeat when linking the table with a remote Linux computer, I was using localhost Windows connection.

Nice! Thank you for your patience. For me it’s strange because I’ve tested with MySQL Sever on 127.0.0.1 (same Windows where ODBC and Access is used), but have #Deleted too.

I can comfirm the same behavior on my side. I am not working on a localhost, but on a server within the network.

The timestamp column does not change anything. Screenshot looks the same, so I won't post it.

https://bugs.mysql.com/bug.php?id=93038 marked as duplicate of this one.

Are there any news? 
Will there be a fix in the near future?

I have the same problem with MySQL server 5.7.19 & MS Access 2016 running on  Windows 10 using MySQL ODBC driver 8.0.13. 

The record shows up in the linked table, but it is "#deleted#" in the local query based on the linked table. F5 doesn't help. The same Access file works with the very same record using the 5.3.10 driver. Both driver works with 1024 character.

If anyone has problems reproducing the error, please, use a local query based on the linked table.

I have added 1025 character to a TEXT field in a table wich has a primary key and a timestamp column / `mytimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP /. 
This failes: 'ABCDE0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789BX'

And this passes:
'ABCD0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789BX'

Update: the query shows the record as "#deleted" only if this is the first record.

I have edited the record with the highest primary key value (ID_EA = 103676) in my test table, and tested with two local queries:
SELECT t_h_ea.* FROM t_h_ea ORDER BY t_h_ea.ID_EA DESC; --> "#deleted"
SELECT t_h_ea.* FROM t_h_ea ORDER BY t_h_ea.ID_EA Asc; --> same record shows up properly.

BTW, here are the connection options used: ;DFLT_BIGINT_BIND_STR=1;option=4196410

Hi,
I've the same issue.
No solution but going down to ODBC driver 5.3.4
Tested with ODBC driver 5.3.11 and 5.3.12 and still the same issue.
So the bug has raised after 5.3.4

After trying everything, I found it works / fixes the problem using odbc connector 5.3.10 x64.  And that's using it with mysql sever 8.0.14.  All other odbc connectors I tried after that version cause the issue.

And, to follow up on my last comment, I was able to duplicate this problem in mysql 5.7.25 x64 using odbc connector 5.3.12 x64.  So, based on what I'm seeing, it appears this is an issue with the connectors after 5.3.10.  And it only happens with text fields > 1024 char.  But, using connector 5.3.10 does not create the problem.  Not sure what other problems I might run into (hopefully not) using that version connector with mysql 8.0.14  So far, working get.

https://bugs.mysql.com/bug.php?id=94390 marked as duplicate of this one.

I have timestamp columns in all my tables, and have eliminated all text fields, although I do have some varchar(2000) fields.  Do you think I would eliminate the problem if I kept all the varchar fields under 1000 characters?

Is there any good documentation about data types to use or avoid or similar practices for Access linked tables?  Or ODBC settings to use or avoid? 

Do you expect 8.0.15 will help with this problem at all?

Thanks!

Hi,

the same problem there is also with LONGBLOB data type.

Having this issue in MS Access in the latest version of Office 365.
Any table that has a BLOB field with more than one row, the first row shows up #DELETED. As soon as s requery of the recordset is performed, the row shows up just fine.

I have tried various combinations of options but not one of them has helped.

We recently imported a few tables from MS Access to MySQL, and we ran into this same issue. Some records appear as #DELETED# on MS Access Office 365 when we used the ODBC connector 8.0.15.

After reading this thread, we decided to install a version previous to 5.3.10, in this case we installed 5.2.7, and that solved the problem.

Is there an update as to when would this bug be fixed for the latest ODBC connector version?

The driver version 5.3.12 and 8.0.14 still has the bug, but 5.3.13 and 8.0.16 works in all of my test cases with 'NO_SSPS=1' in the connection string.

When  8.0.16 or 5.3.13 is used without 'NO_SSPS=1', my original test case passes. 
I can still see some "deleted records", but these are not related to text column length.

8.0.16 marked as duplicate of this one

Same bug in 8.0.16. So, 1) MySQL is unusable with Access 2016. Connector 5.2.7 is working but it is too old and unsecured, so no way to use MySQL now 2) Severity of this bug is S1 - Critical, not S2 - Serious 3) Bug Version not only 8.013 but 8.016 too. 4) Look #95914 - more details and screenshots. Thanks. https://bugs.mysql.com/bug.php?id=95914

You do not have access to bug #95914 …

"Severity of this bug is S1 - Critical, not S2 - Serious 3) Bug Version not only 8.013 but 8.016 too." – done.

I have the same problem, using Access 2010 and Access 2013:
- the ODBC connector 5.3.10 doesn't have the problem but doesn't support characters set utf8mb4

- the ODBC connector 5.3.13 doesn't work (record deleted)

- the ODBC connector 8.0.16 support characters set utf8mb4 but doesn't work (record deleted)

The workaround no_ssps=1 is not enough
Do you have any suggestion ?
Thanks

I have this issue with all ODBC 8 version greater than 8.0.15.  8.0.15 looks to be working but .16 to .18 show #deleted# records. I also tried .13 but it did not work properly.  Did not test them all.

10.1.43-MariaDB MariaDB Server   
Centos 7.7 
Access 2016 (office 365).

Took me a long time to find this bug report.

Since the first GA version of the 8.0.x ODBC driver was basically the same, as the 5.3.11, and "legacy code cleanup" happened in 5.3.11, now we have 4 options:
- stick to 5.3.10
- use 8.0.16 or newer with NO_SPSS = 1 and other connect options recommended for Access if it helps 
- switch to other vendor (like MariaDB ODBC driver 3.1.5)
- switch from Access frontend to something else

I've had the same issue for a year and a half now. Is anyone even working on fixing this?

Same issue here, additionally affects versions 8.0.17 and 8.0.18.

Posted by developer:
 
Apologies to everyone that this bug was opened for so long. Due to lack of good diagnostics from MS Access it is very hard to find out the exact reason for such issues which result in #deleted rows - many things can lead to such errors.

We were able to reproduce and analyze the issue and it seems that the root cause was another problem with handling of server-side prepared statement results (bug#97191). Apparently this confused MS Access logic which reported #deleted rows. We have fixed bug#97191 in 8.0.19 and we believe it should also fix the issue reported here. This is confirmed in our tests, where we could not reproduce the issue with 8.0.19.

We were able to reproduce the issue with 8.0.18 (only if NO_SSPS is not set) but not with 8.0.19. So even if we do not know the exact root cause, it seems the issue is fixed in 8.0.19. This can be related to a fix of bug#97191 that we did in 8.0.19.

We close the bug for now. If you see such issue with 8.0.19 or later please let us know!

All working now with the latest update. Thanks!

I have just set up a MySQL database and am using Access as the FE.  There are 18 tables linked to Access all using the connection string ...

"DSN=MySQL_SportsData;UID=BigCheese;PWD=nnBnncnnnn;SERVER=localhost;DATABASE=sportsdata;PORT=3306;DFLT_BIGINT_BIND_STR=1;ACCDB=YES;"

Of the 18 tables all but one work as expected but one table shows every field in every row as "#Deleted".  There are 3 columns and when the table is opened in Access it shows the three fields for each of the 13 rows as "#Deleted".  The table can be freely viewed in phpmyadmin and all data is correct.  As I say all other 17 tables are as expected.

I have tried deleting the link and reinstalling it both with Access's Linked Table Manager and by selecting the table with the External Data facility.  I have Compacted & Repaired the Access database after deleting the link and after reinstalling the link but it seems to have a natural hatred for this table.  I don't see that I have any reserved word embedded in the name (finposlookups).  I have read this thread but it seems to offer no solution to this specific problem.  It makes no sense that it should affect just 1 in 18 tables.

I performed "SHOW CREATE TABLE finposlookups" which returns ...

"CREATE TABLE `finposlookups` (
 `FPL_ID` tinyint unsigned NOT NULL,
 `FPL_PositionText` char(2) DEFAULT NULL,
 `FPL_Text` varchar(30) DEFAULT NULL,
 `Stamp` timestamp NOT NULL,
 PRIMARY KEY (`FPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8"

Note: the longest allowed field is only 30 characters (actual longest content is 20 characters) and a Timestamp column has been added.

Can you throw some light on what is wrong and how to work around it please.

Sorry I forgot to confirm my current version is: 8.0.21 - MySQL Community Server - GPL

(and I can't see an edit facility for an existing post! :( )

David,

This thread is about a bug with the SQL connector, which is now fixed. However, to help you out I looked at your table, which doesn't have an auto increment, or default values set for your Primary Key ID or your timestamp, which I'm not certain, but strongly suspect is your problem.

I tested the following table and it worked fine for me in Access with the latest MYSQL Connector

# Table	Create Table
finposlookups	CREATE TABLE `finposlookups` (
  `FPL_ID` int unsigned NOT NULL AUTO_INCREMENT,
  `FPL_PositionText` char(2) DEFAULT NULL,
  `FPL_Text` varchar(30) DEFAULT NULL,
  `Stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`FPL_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Hope this works for you too, and good luck.

This problem occurs even when the column size does not exceed 1024 bytes.