Inserting a record is very slow and fails with database with 20k+ records

[Leo Ayala]

Hi. I've been using MySQL Server 5.7 for a number of years without any issues. Client PCs use MySQL ODBC connector 5.1. I use a legacy MFC application that adds records to the database. As I mentioned, this has worked for a number of years. I started seeing that when the total number of records reaches 20k+, the application was unable to add new records. The application's debug log shows an ODBC error but it isnt obvious as to the cause. For example, on one system running Windows 10 it shows:

COM EXCEPTION Error
Code = 80040e21
Code meaning = IDispatch error #3105
Source = Microsoft OLE DB Provider for ODBC Drivers
Description = ODBC driver does not support the requested properties.

I have a second client PC that is running the same application with the same ODBC Connector version but on Windows 7 and it is able to add the records.

I have to admit that I am not very familiar with MySQL and its configuration. My guess is that there must be some timeout or buffer setting that I have not set. I just dont know which. I noticed that it takes between 10 and 15 seconds for the insert command to fail.

I played around with some of the timeout settings but they did not make any difference.

Eventually, I started deleting records from the database. Once I had deleted 2k records, suddenly, the application on the Windows 10 system was able to add a record. This makes me believe that there must be a timeout or buffer setting I'm missing.

The client application I'm using is an MFC application that uses OLEDB to connect connect and add/modify/fetch records. Before adding, it checks to see if a record already exists for a specific key. If not, then it adds it. Ocasionally it will fetch a record.

When MySQL Server was installed, it was configured as follows:
-Detailed Configuration
-Server Machine
-Transactional Database Only
-Decision Support (DSS)/OLAP
-Enable TCP/IP Networking (port 3306)
-Enable Strict Mode
-Standard Character Set
-Installed as a Windows Service

This has worked for me for several years. It wasnt until one client PC was upgraded from Windows 7 to Windows 10 that we ran into this problem. Also, even on the Windows 7 system it takes several seconds to add a record (>10sec). This doesnt seem right and I fear that if the database continues to grow, eventually the Windows 7 system will also fail the same way.

I hope that I've included enough information. If anyone has any clue what may be happening, please let me know.

Thanks in advance.

Regards,
LA