CentOS7 (standard Google Compute Engine image)

Please let me know if there is any additional info you need on this.

I did have problems using the MySQL ODBC connector. For instance, in the documentation under "Multiple ODBC tables" I have inserted this:
Caution: Avoid accessing tables belonging to the currently running MariaDB server via the MySQL ODBC connector. This may not work and may cause the server to be restarted.
This should be changed to discourage any use of MySQL or MariaDB connectors. Indeed, not knowing how they work, I can hardly guess what goes wrong with them.
Anyway, the normal way to access a remote MariaDB table is to use the MYSQL table type, not ODBC.
Let me know if you find a case where the MYSQL type cannot be used instead of the ODBC type.

The big reason not to use the MYSQL table type is that the databases are remote and, as far as I can see, there is no way to do a MYSQL table type with an SSL connection. If there is, that would solve my problem. Also, are there any other ODBC connectors you tried that are known to be incompatible? We have a near term need for DB2 also.

It seems to work fine on my box:

OS version: Fedora 23
The local server: 10.1.10-MariaDB-debug
ODBC driver version: mysql-connector-odbc-5.3.4-4.fc23.x86_64
ODBC manager version: unixODBC-2.3.4-1.fc23.x86_64
The remote server: 10.0.21-MariaDB

odbcinst.ini:

[MySQL]

Description     = ODBC for MySQL

Driver          = /usr/lib/libmyodbc5.so

Setup           = /usr/lib/libodbcmyS.so

Driver64        = /usr/lib64/libmyodbc5.so

Setup64         = /usr/lib64/libodbcmyS.so

FileUsage       = 1

odbc.ini:

[MySQL]

driver = MySQL

server = 192.168.0.6

port = 3306

database = test

uid =

pwd =

SQL script:

INSTALL SONAME 'ha_connect';

CREATE OR REPLACE TABLE my_tables ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=TABLES CONNECTION='dsn=MySQL;uid=root;pwd=;';

SELECT * FROM my_tables;

it returned the list of the tables without any problems:

+-----------+--------------+--------------------+------------+--------+

| Table_Cat | Table_Schema | Table_Name         | Table_Type | Remark |

+-----------+--------------+--------------------+------------+--------+

| test      |              | bdict              | TABLE      |        |

| test      |              | cachedcopy         | TABLE      |        |

| test      |              | dict               | TABLE      |        |

| test      |              | links              | TABLE      |        |

| test      |              | poprank_user_table | TABLE      |        |

| test      |              | qcache             | TABLE      |        |

| test      |              | qinfo              | TABLE      |        |

| test      |              | qtrack             | TABLE      |        |

| test      |              | redirect           | TABLE      |        |

| test      |              | server             | TABLE      |        |

| test      |              | srvinfo            | TABLE      |        |

| test      |              | t1                 | TABLE      |        |

| test      |              | t2                 | TABLE      |        |

| test      |              | url                | TABLE      |        |

| test      |              | urlinfo            | TABLE      |        |

| test      |              | urlview            | TABLE      |        |

| test      |              | v1                 | VIEW       | VIEW   |

| test      |              | wrdstat            | TABLE      |        |

+-----------+--------------+--------------------+------------+--------+

I did not use SSL though.
Can you please clarify how you use SSL?

I tried ConnectSE+ODBC on Linux with:

• MariaDB
• PostgreSQL
• SQLite with http://www.ch-werner.de/sqliteodbc/
• Oracle
• SQL Server via FreeTDS

and on Windows:

• XLS driver

Olivier, which combinations did you try?

One of our users also successfully connected to the Intersystems Caché server.

There were some SELinux related problems initially though, but they were solved.
Please find details in:

https://mariadb.atlassian.net/browse/MDEV-4555

Via isql I could get an ODBC connection with and without SSL, but via CONNECT I could not get it to work even without SSL.

I wonder if the problem is related to unixODBC? You were on 2.3.4 while centos7 is on 2.3.1.
Or I wonder if it was because you were connecting to a remote MariaDB db and not a MySQL db.
If you email me out of this forum at robert.dyas@parasql.com I will provide you with the ip/password and SSL files for the Google Cloud SQL (it is MySQL 5.5 or 5.6) so you can quickly test.

Note previous to my centos7 test I tried and failed to get it working on Ubuntu trusty.

On Windows 7 I loaded MySQL connector version 5.3.04 and tried your case using the ANSI and Unicode drivers with no problems:

/* try to get list of tables - this command completes no problem */

CREATE OR REPLACE TABLE my_tables ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=TABLES CONNECTION='dsn=MySQL-ANSI;uid=root;';

/* or */

CREATE OR REPLACE TABLE my_tables ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=TABLES CONNECTION='dsn=MySQL-Unicode;uid=root;';

show create table my_tables;

CREATE TABLE `my_tables` (

  `Table_Cat` char(128) NOT NULL,

  `Table_Schema` char(128) NOT NULL,

  `Table_Name` char(128) NOT NULL,

  `Table_Type` char(16) NOT NULL,

  `Remark` char(255) NOT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='dsn=MySQL-ANSI;uid=root;' `TABLE_TYPE`='ODBC' `CATFUNC`='TABLES';

/* try to query it and it DOES NOT HANGS (never returns) */

select * from my_tables;

The CONNECT tests are done using the Oracle, Postgres, Sqlite3, and XLS drivers and pass on all platform supporting them.
On Windows, I also used the Access, SQL Server, and FireBird drivers.
I did have problems with some older versions but cannot remember exactly which. I seems it was using the first version of the MariaDB connector.

Olivier, it seems there are some problems with table discovery.
I'm not sure if this is the same problem with what Robert met. So created a separate issue:
https://mariadb.atlassian.net/browse/MDEV-9414
Please have a look.

Olivier, please also have a look into this one:
MDEV-9415 Crash with ConnectSE + MySQL ODBC driver + INSERT

Note that in the odbcinst.ini file there are three different MySQL ODBC drivers; I have tried each of them without success.

odbcinst.ini file:

[PostgreSQL]

Description=ODBC for PostgreSQL

Driver=/usr/lib/psqlodbcw.so

Setup=/usr/lib/libodbcpsqlS.so

Driver64=/usr/lib64/psqlodbcw.so

Setup64=/usr/lib64/libodbcpsqlS.so

FileUsage=1

[MySQL]

Description=ODBC for MySQL

Driver=/usr/lib/libmyodbc5.so

Setup=/usr/lib/libodbcmyS.so

Driver64=/usr/lib64/libmyodbc5.so

Setup64=/usr/lib64/libodbcmyS.so

FileUsage=1

[MySQL ODBC 5.3 Unicode Driver]

Driver=/usr/lib64/libmyodbc5w.so

UsageCount=1

[MySQL ODBC 5.3 ANSI Driver]

Driver=/usr/lib64/libmyodbc5a.so

UsageCount=1

[TDS]

Driver = /usr/lib64/libtdsodbc.so.0

FileUsage = 1

Version info for the above drivers is below. My tests have been with the 5.3.x drivers, not the 5.2.x

mysql-connector-odbc-5.2.5-6.el7.x86_64 : ODBC driver for MySQL

Repo        : base

Matched from:

Filename    : /usr/lib64/libmyodbc5.so

Provides    : libmyodbc5w.so()(64bit)

Filename    : /usr/lib64/libmyodbc5w.so

mysql-connector-odbc-5.3.4-1.el6.x86_64 : An ODBC 5.3 driver for MySQL - driver package

Repo        : @/mysql-connector-odbc-5.3.4-1.el6.x86_64

Matched from:

Provides    : libmyodbc5w.so()(64bit)

Filename    : /usr/lib64/libmyodbc5w.so

Provides    : libmyodbc5a.so()(64bit)

Filename    : /usr/lib64/libmyodbc5a.so

mysql-connector-odbc-5.3.4-1.el6.x86_64 : An ODBC 5.3 driver for MySQL - driver package

Repo        : @/mysql-connector-odbc-5.3.4-1.el6.x86_64

Matched from:

Provides    : libmyodbc5a.so()(64bit)

Filename    : /usr/lib64/libmyodbc5a.so

I also used the ANSI and Unicode drivers and both work fine.

In the present case, we are in a situation because I cannot reproduce the bug Alexander has.

By the way, it does not seem to be coming from the MySQL connector not handling SQLPrepare because the same connector works fine on my Windows machine.

Is it possible for you to try a linux distro with unixODBC 2.3.1?
I will build up a new server with Fedora23 and unixODBC 2.3.4 as there seem to be many bug fixes in unixODBC.

Is there anything I can provide you to help with this bug?

Please explain what really happens. The title speak of a crash, then your description shows an indefinite loop.
Also, did the log.txt file says anything? If so can you attach it?

This issue of crashing appears very specific to the MySQL ODBC driver.
I have seen it both crash the server (most common) and then I changed something (can't remember what) and got the infinite loop scenario. Issues 9414 and 9415 are related to this one and have much more in terms of log files. The other two drivers I have been using (Salesforce and TDS) have never given any problem like this and seem quite stable.

It will take me a few days to build up an environment just for testing this... if you would like I can do so and then run some more tests.

Both 9414 and 9415 traces possibly show that the crash occurs in the MySQL connector.
Perhaps the error report should be put also for the MySQL connector.

Seeing this in 10.0.16 as well.

Signal 11 generally shows that one of the numerous infamous DEBUG_ASSERT MariaDB is full off has triggered a crash. Are you using a DEBUG version? Another thing that could help me, because I am unable to reproduce this, would be to set the connect_xtrace variable to 1 or 2 allowing to see what was executed before the crash.

Note also that using ODBC to connect to MySQL or MariaDB is a bad idea. You should use instead the MYSQL table type or the FEDERATED(X) storage engine.

Nope, not a debug version: 10.0.16-MariaDB-log

Olivier, can you see the "private" comment?

It has the exact query run - which is a simple SELECT:

SELECT * FROM t1 LIMIT 1;

Hi Olivier,

Also, regarding this comment:

"Note also that using ODBC to connect to MySQL or MariaDB is a bad idea. You should use instead the MYSQL table type or the FEDERATED(X) storage engine."

I'm not quite sure what you mean. I mean, I read the following page, which says you can use CONNECT with ODBC:

https://mariadb.com/kb/en/mariadb/connect-table-types-odbc-table-type-accessing-tables-from-other-dbms/

Unless I am misunderstanding something. Presumably that is the case, so if you could please elaborate a little more, that would be most appreciated, as the link above makes it seem like this should be possible.

And regardless, there still seems to be some crashing bug that needs fixed, so I think we should attempt to resolve this.

Also, regarding connect_xtrace, I'm not so sure they can set this in production, since it requires both a restart and the --console option. I'm not sure of any production system where one can start using --console if they are not already.

In the document you mention in you last comment, there is a paragraph beginning with "Caution" that already was warning the user about using ODBC to connect to MySQL or MariaDB.

But the general idea about all this is:

• ODBC was firstly a Microsoft Windows invention. Some implementations have been done under Linux but are they really reliable?
• Anyway, ODBC is a (cumbersome) black box between the client and the data source. Its main use is to make possible to write a unique code that can be used with different data source.
• Therefore, when a direct connection can be done, it is better and simpler to avoid it. This is the case with MySQL/MariaDB, which provide an API to achieve that. And using this API is what the FEDERATED(X) engine and the CONNECT MySQL table type do.

In addition, if you really need to have a connection that is independent of the data source (like using the TBL type to access many data sources) CONNECT now features the JDBC table type.
Unlike ODBC, JDBC is not a Microsoft invention and its Java implementation is the same on all operating systems. It is perhaps more reliable than ODBC.

I still understand that it is important to find the cause of a crash and I shall continue to work on this but things are very difficult when bugs cannot be reproduced. This is why I was telling you about tracing, not as a general thing to do, but just to add it to the log report you can attach to this JIRA report (using preferably a DEBUG version of the server)

Looking into the error log snippet I see:

/usr/lib64/libodbc.so.2(SQLCancel+0x6b)[0x3ec1e09d5b]

Did the crash happened during the query or when trying to cancel it because it was looping?
Or did this was because of a query timeout?

HI Olivier,

Sorry, but I am still a little confused.

The caution is about using CONNECT to connect to a MySQL/MariaDB table. However, this is not the case. They are trying to access a OneTick ODBC table from another source in their MariaDB instance using CONNECT. So it seems like it is the other way around from what you are stating, but perhaps I am missing something. Also, I do not see how I would create a FederatedX table to accomplish this, nor do I see how changing the type to MYSQL would make a difference either. All of the federated examples show it connecting to another MySQL/MariaDB table, but that is not the case here.

Please advise.

My turn to be confused.
This problem description says:

MariaDB server crashes when I select from a CONNECT table of type ODBC that uses the MySQL 5.3 ODBC connector. Note that I CAN select from a CONNECT table that uses ODBC and FreeTDS driver, and that both the FreeTDS driver and MySQL driver work fine in isql.

I understood that this report was about crashes occurring when using the MySQL ODBC connector.
If so, this is to connect to another MySQL or MariaDB database. Am I wrong?

Hehe, understandable.

First, perhaps I should not have added my comments to this existing bug. I thought it was close enough to what I was seeing, and there was not a resolution to this bug, so (at the time) I thought it would be a good idea to add what I'd seen to this bug report.

I see now that was not a good idea.

Can you start at just what I posted, as I really cannot comment on what happened previously? Or, would it be better if I contacted you outside of this bug report?