Access to MySQL ODBC connection opens slowly

Question

I'm moving my access split database backend to a MySQL server. The frontend startup form runs a pass-trough query. It takes 1.25 sec to load the startup form. Without the query, it is 0.05 sec. I tried to run the query after dropping the connection at server side. The query finished in 1.21 sec. When the same query is running after opening the connection, it finishes in 0.016 sec.

So it seems, that Access needs 1.2 sec to open a new ODBC connection. Is there a way to speed up this? With mysqlslap I was able to open & drop 60 connections per second, so it really should not take 1.2 sec.

Configuration:

MySQL server is running on localhost, win 7 SP1 (i5, enough free RAM).
Client is MS Access prof 2013 32 bit (with latest patches).
Server is mysql-5.7.11-x64 community edition.
MySQL ODBC driver is Unicode 5.3.4 version, both 32 bit & 64 bit installed.
Connection string without valid server/user/pwd: "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=XXX;charset=UTF8;PORT=3309;DATABASE=movedb2;user=XXX;password=XXX;DFLT_BIGINT_BIND_STR=1;option=4196410"

The query:

Dim r as recordset, db as database
Set db = currentdb
Set r = db.Openrecordset(ptqueryname)

I tried it with select 1;, but the query still finished in 1.2 sec.

We don't see enough with the piece of code you posted. Where's the open connection statement? what's in ptqueryname — Thomas G
Try removing all the non-mandatory parameters. Also, port 3309 looks strange. Default is 3306. A typo? — Gustav
@Thomas G There is no open connection statement. ptqueryname is a pass-trough query. Running a pass-trough query with Openrecordset will implicitly open an ODBC connection. That connection can be seen in MySQL Workbench session list. MS Access keeps that connection open after the query has finished. The connect string is listed above. I have replaced the original ptqueryname.SQL with "select 1;", but it didn't speed up the process. The Openrecordset statement is in a function called from the startup form's Load event. The function is sitting in a general VBA module. — Eperbab
@Gustav I'm using more, then one MySQL server for development. This one has 3309. I would like to set up a replication slave, and also running some Mariadb vs. MySQL performance tests. I will try a minimalisitc connection string to see, if it helps. It's national holiday here tomorrow, so I will try it on Wednesday. — Eperbab
@Gustav Removing all the non-mandatory parameters didn't help. But, the connection opens fast, when I replace the SERVER name with "localhost" or a numeric IP. So the root cause of the delay must be something about ODBC driver & server name resolution. — Eperbab

JWST JWST · Accepted Answer · 2017-03-19T14:51:41

I got ODBC with IPv6 working:

port 3306 for MySQL in the firewall for TCP In and TCP6 In;
in Linux in /etc/my.cnf 'bind-address = 127.0.0.1' to '#bind-address = 127.0.0.1' and, I think, restart of the server is needed;
MySQL databases (in DirectAdmin) are authorized for your personal IPv4 and/or IPv6;
the domain name as server name may work, otherwise the IP address (IPv6 between brackets).

Furthermore: - a parameterstring in a MS Access VBA module for MySQL databases: Public Const gloODBCParameters As String = "PORT=3306;SSLmode=verify-full;TextAsLongVarchar=0;UseDeclareFetch=0;LANGUAGE=us_english;INITSTMT=set wait_timeout=600;FOUND_ROWS=1";

the 32 bits driver {MySQL ODBC 5.3 Unicode Driver} works on my pc;

Note: My provider switches my IPv6 address often because of privacy. This is a nice setting, so I did no longer authorize my databases using a fixed IPv6 address. Then contact the server with the IPv4 address.

Access to MySQL ODBC connection opens slowly

1 Answers