DELETE seems to work fine:

DELETE FROM t1 WHERE a=30;

Query OK, 0 rows affected, 1 warning (0.07 sec)

MariaDB [test]> show warnings;

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

| Level | Code | Message             |

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

| Note  | 1105 | t1: 1 affected rows |

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

1 row in set (0.00 sec)

UPDATE also seems to work fine:

MariaDB [test]> UPDATE t1 SET a=21 WHERE a=20;

Query OK, 0 rows affected, 1 warning (0.06 sec)

Rows matched: 0  Changed: 0  Warnings: 1

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message             |

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

| Note  | 1105 | t1: 1 affected rows |

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

1 row in set (0.00 sec)

INSERT with a PostgreSQL data source seems to work fine.

As in MDEV-9414, it seems dependant on the used connector. Using the MySQL connector 5.3 the INSERT statement worked fine.
Now the problem seems to be located in the SQLPrepare ODBC function. Are you sure the connector you are using supports the SQLPrepare function? If not, the fix will be to test for the PREPARE feature and not use it in CONNECT with connectors that do not support it.
However, if this is the case, there is also a bug in the connector that should refuse the function and provide a proper error message.

The returned info in the DELETE and UPDATE examples above (delete reproduced below) don't seem exactly right to me. The result says "Query OK, 0 rows affected, 1 warning" and then SHOW WARNINGS shows 1 row affected. Shouldn't the result be "Query OK, 1 rows affected, 0 warning" ? I get this same behavior using the TDS ODBC driver, so it does not appear to be driver specific. Should this be opened as a separate issue, or am I missing something? This behavior is causing problems for our app as well.

DELETE FROM t1 WHERE a=30;

Query OK, 0 rows affected, 1 warning (0.07 sec)

MariaDB [test]> show warnings;

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

| Level | Code | Message             |

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

| Note  | 1105 | t1: 1 affected rows |

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

1 row in set (0.00 sec)

RE "Now the problem seems to be located in the SQLPrepare ODBC function. Are you sure the connector you are using supports the SQLPrepare function? If not, the fix will be to test for the PREPARE feature and not use it in CONNECT with connectors that do not support it."

We are hoping to use CONNECT with many different drivers, so testing for this and providing a fall back would be helpful. Basically make it as tolerant of drivers as possible.

About warnings: yes it should be like you say. Unfortunately Connect cannot change the message returned by MariaDB and for MariaDB there are 0 row affected in the Connect table that is not modified. Indeed this is the remote table that is modified.
This is why, Connect issues a warning that gives the number of rows affected in the remote table.

Yes, I try to make Connect as tolerant of drivers as possible and this is the experience of users such as yourself that can show where and when there are problems.

In the present case, we are in a situation because Alexander and I are unable to reproduce the bugs you have.

Would it be possible to provide UnixODBC trace for the crash?
Probably the root of the problem is in smth that happened (or not happened) before. Unlikely preparing simple query could cause the crash on its own

How do I get the trace?

Add Trace=On/Yes/1 and TraceFile=/path/to/logfile to the your dsn section in odbc.ini
But I thought you can't reproduce the crash.

Lawrin, Sorry for the delay. Here's the trace.

[ODBC][22108][1455965318.601771][__handles.c][460]

		Exit:[SQL_SUCCESS]

			Environment = 0x7fffc7839a00

[ODBC][22108][1455965318.601817][SQLAllocHandle.c][375]

		Entry:

			Handle Type = 2

			Input Handle = 0x7fffc7839a00

[ODBC][22108][1455965318.601849][SQLAllocHandle.c][493]

		Exit:[SQL_SUCCESS]

			Output Handle = 0x7fffc78b0000

[ODBC][22108][1455965318.601896][SQLDriverConnect.c][728]

		Entry:

			Connection = 0x7fffc78b0000

			Window Hdl = 0x1

			Str In = [dsn=myodbc;uid=root;][length = 20 (SQL_NTS)]

			Str Out = 0x7fffc28005a0

			Str Out Max = 512

			Str Out Ptr = 0x7ffff7f7d36a

			Completion = 0

		UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][22108][1455965318.643118][SQLDriverConnect.c][1697]

		Exit:[SQL_SUCCESS]

			Connection Out [[dsn=myodbc;uid=root;][length = 20 (SQL_NTS)]]

[ODBC][22108][1455965318.643163][SQLGetInfo.c][554]

		Entry:

			Connection = 0x7fffc78b0000

			Info Type = SQL_IDENTIFIER_QUOTE_CHAR (29)

			Info Value = 0x7fffc2800568

			Buffer Length = 2

			StrLen = 0x7ffff7f7d37c

[ODBC][22108][1455965318.643217][SQLGetInfo.c][617]

		Exit:[SQL_SUCCESS]

[ODBC][22108][1455965318.643258][SQLGetInfo.c][554]

		Entry:

			Connection = 0x7fffc78b0000

			Info Type = SQL_TXN_CAPABLE (46)

			Info Value = 0x7ffff7f7d3a4

			Buffer Length = 0

			StrLen = (nil)

[ODBC][22108][1455965318.643292][SQLGetInfo.c][617]

		Exit:[SQL_SUCCESS]

[ODBC][22108][1455965318.643326][SQLSetConnectAttr.c][396]

		Entry:

			Connection = 0x7fffc78b0000

			Attribute = SQL_ATTR_AUTOCOMMIT

			Value = (nil)

			StrLen = -5

[ODBC][22108][1455965318.643485][SQLSetConnectAttr.c][842]

		Exit:[SQL_SUCCESS]

[ODBC][22108][1455965318.643507][SQLAllocHandle.c][540]

		Entry:

			Handle Type = 3

			Input Handle = 0x7fffc78b0000

[ODBC][22108][1455965318.643690][SQLAllocHandle.c][1085]

		Exit:[SQL_SUCCESS]

			Output Handle = 0x7ffff3c86900

[ODBC][22108][1455965318.643716][SQLPrepare.c][196]

		Entry:

			Statement = 0x7ffff3c86900

			SQL = [INSERT INTO t1(a) VALUES (?)][length = 28 (SQL_NTS)]

Hmm, it doesn't get any more clear. I will look in it some more though.

And it looks like it crashes with both mysql's and mariadb's libmysql.
I will possibly ask Georg if he has any ideas about what can go on there.

Also I can offer a workaround. Add to the DSN used NO_SSPS=1
It should force myodbc to "prepare" queries on the client side, i.e. prepared statements won't be used.

And speakin about the DSN("myodbc") - I guess there is no fancy options in it, other than user credentials?

Looking in debug mode at what Connect does when inserting in a table I noted this. After calling SQLPrepare, Connect uses this function to bind the parameters:

/***********************************************************************/

/*  Bind a parameter for inserting.                                    */

/***********************************************************************/

bool ODBConn::BindParam(ODBCCOL *colp)

  {

  void        *buf;

  int          buftype = colp->GetResultType();

  SQLUSMALLINT n = colp->GetRank();

  SQLSMALLINT  ct, sqlt, dec, nul;

  SQLULEN      colsize;

  SQLLEN       len;

  SQLLEN      *strlen = colp->GetStrLen();

  SQLRETURN    rc;

  try {

    rc = SQLDescribeParam(m_hstmt, n, &sqlt, &colsize, &dec, &nul);

    if (!Check(rc))

      ThrowDBX(rc, "SQLDescribeParam", m_hstmt);

  } catch(DBX *x) {

    sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0));

    colsize = colp->GetPrecision();

    sqlt = GetSQLType(buftype);

    dec = IsTypeChar(buftype) ? 0 : colp->GetScale();

    nul = SQL_NULLABLE_UNKNOWN;

  } // end try/catch

  buf = colp->GetBuffer(0);

  len = IsTypeChar(buftype) ? colp->GetBuflen() : 0;

  ct = GetSQLCType(buftype);

  *strlen = IsTypeChar(buftype) ? SQL_NTS : 0;

  try {

    rc = SQLBindParameter(m_hstmt, n, SQL_PARAM_INPUT, ct, sqlt,

                          colsize, dec, buf, len, strlen);

    if (!Check(rc))

      ThrowDBX(rc, "SQLBindParameter", m_hstmt);

  } catch(DBX *x) {

    strcpy(m_G->Message, x->GetErrorMessage(0));

    SQLCancel(m_hstmt);

    rc = SQLFreeStmt(m_hstmt, SQL_DROP);

    m_hstmt = NULL;

    return true;

  } // end try/catch

  return false;

  } // end of BindParam

When the server implements SQLDescribeParam (as Oracle does) this function is used and when it is not implemented (for instance by Microsoft Access) Connect tries to retrieve the parameters values from the column definition.

Until now this worked fine, however there is a problem with the MySQL connector. Apparently it does implement that function (rc=0) but wrongly does not set all the given arguments. In particular colsize and dec are not set and keep there value. As they are not intitialized in their declaration, these values are trash and they are used in the call to SQLBindParameter.

Strangely enough this does not do anything wrong on Windows so I cannot be sure this is the cause of your problem.

Alexander, can you test that fixing this (for instance by giving an initial value to these variables, perhaps the one coming from the column definition) solves the problem.

If so, this would not be a bug only of Connect but also of the MySQL connector.

But connector crashes in the SQLPrepare call. Thus I do not think it has anything to do with SQLDescribeParam

As for its implementation im MySQL(and in MariaDB) - it's nothing more than giving default values good and safe for general case. e.g. it always return type SQL_VARCHAR, thus dec value is irrelevant. Colsize is always set though.

And there is no better way to implement it with MariaDB/MySQL. At least at the moment. And I would not recommend to use it for param binding. There are usually better information sources for this purpose.

Sure enough. But in that case it would be better not to implement it at all.

Well, connectors require it to comply with specs.
And basically, binding using its output should work. It's won't be optimal, but will work. So, why not.

I thought the following might help... NOTE this happened with the DSN having NO_SSPS=1 (on CentOS7 on Google Cloud)

CREATE OR REPLACE TABLE my_tables ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=TABLES CONNECTION='d

sn=MySQL;UID=root;PWD=test12;';

Query OK, 0 rows affected (0.01 sec)

but when you try to get the list of tables:

MariaDB [db11003]> select * from my_tables;

ERROR 2013 (HY000): Lost connection to MySQL server during query

... and the server has crashed.

[MySQL]
driver = MySQL ODBC 5.3 Unicode Driver
server = 173.194.250.82
port = 3306
database = ctest
NO_SSPS=1
uid =
pwd =

I am curious what happens in this case - is it possible to see the backtrace?

However iirc in this case SSPS would not be engaged even if NO_SSPS was set to 0

Yes, I have verified that if there is no parameter placeholders in the query, myodbc will prepare it on the client side. thus it is even more curious, since in original report 'select * from t1' works fine

BTW typing help in isql or iusql both return the list of table successfully.

What file would contain the backtrace?

Adding this line

NO_SSPS=1

into odbc.ini helps.
Thanks for the idea, Lawrin.

Robert, looks like a good temporary workaround.

We'll continue studying the problem in the meanwhile.

Lawrin,

The odbc.ini entry looks like this:

[myodbc]

Description=MySQL test database (Fedora Driver)

Trace=On

TraceFile=/home/bar/.odbc.log

Driver=MySQL

SERVER=localhost

USER=root

PASSWORD=

PORT=3306

DATABASE=msearchtest

#NO_SSPS=1

Olivier,

I tried to set a break point in ODBConn::BindParam().
But it does not stop there.

It crashes in ODBConn::PrepareSQL().

I guess, ODBConn::BindParam() is to be called after ODBConn::PrepareSQL().
So it crashes before the call for ODBConn::BindParam().

The same script does not crash for me:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Tables CONNECTION='dsn=myodbc;uid=root;';  

SELECT *FROM t1;

It returns an error instead:

Query OK, 0 rows affected (0.01 sec)

ERROR 1296 (HY000): Got error 174 'Invalid flag 0 for column a' from CONNECT

As for NO_SSPS=1 being temporary solution. In fact in many, if not in most, cases it should be even beneficial performance wise. Only if prepared statements re-used many times, they would be faster than client side emulation.

Here we(well, Georg) also got the same error, but seems that server crashes - on 2nd query it is away already. Or it crashes on 2nd query.

MariaDB [test]> create table t1(a int) ENGINE=Connect TABLE_TYPE=ODBC CATFUNC=TABLES CONNECTION='dsn=connect_test;UID=root;';
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> select * from t1;
ERROR 1296 (HY000): Unknown error 1296
MariaDB [test]> select * from t1;
ERROR 2006 (HY000): MySQL server has gone away

Invalid flag 0 for column a
This is not an error. Look at the documentation upon catalog tables. Column names must be exact name of a SQLTables return column or be recognized by a "flag".
In your create table, "a" is not a valid name and should be given a "flag". As a matter of fact, this create table is a nonsense because no column of a SQLTables result is of integer type.

Yes, I saw that too....the following

CREATE OR REPLACE TABLE t1 (a int) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Tables CONNECTION='dsn=myodbc;uid=root;';  

should be:

CREATE OR REPLACE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Tables CONNECTION='dsn=myodbc;uid=root;';  

This works with all other ODBC drivers I have tried except MySQL.

I have also attached a new trace from unixodbc in case there is any useful info there. log224-unixodbc.log
Note that in the first part of the attached log it contains info from connecting successfully with isql and issuing the help command (which by my understanding is calling the catalog function). This works fine. Then the second half of the log file contains what happens when I query a connect table with catfunc=tables.

Not knowing anything about the internals of all this, it would seem like there is a global variable that is colliding between client and server... so the mysql client code works fine when running in its own execution space, but when running within the mysql server execution space has a possible variable/symbol conflict. Since connect is probably the first use case where the client and server code are running in the same execution space, you can see where this would find the problem. Just speculation on my part that might trigger a thought by someone who actually knows this stuff.

Sorry for confusing.
This script works fine on my machine:

CREATE OR REPLACE TABLE t1  ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Tables CONNECTION='dsn=myodbc;uid=root;';

SELECT * FROM t1;

with the current sources, both 10.0 and 10.1.

Alex,

May I ask what OS and unixODBC version you are using?
Thank you.

Can somebody try if myodbc connector from mysql site makes difference for this issue? The difference is that mysql is linking against mysqlclient statically, when builds binaries. While linux distributions hate static linking, and connectors they provide are linked against libmysql dynamically.

I have tried the MySQL ODBC driver 5.3 (both unicode and ansi) downloaded from the Oracle website...crashes the server.

[MySQL ODBC 5.3 Unicode Driver]

Driver=/usr/lib64/libmyodbc5w.so

UsageCount=1

I tried mysql-connector-odbc-5.3.4-linux-glibc2.5-x86-64bit.tar.gz
with this script:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='dsn=myodbc3;uid=root;';

SELECT * FROM t1;

it crashes on SELECT with the following stack trace:

#0  0x00007ffff57efa98 in __GI_raise (sig=sig@entry=6)

    at ../sysdeps/unix/sysv/linux/raise.c:55

#1  0x00007ffff57f169a in __GI_abort () at abort.c:89

#2  0x00007ffff57e8227 in __assert_fail_base (fmt=<optimized out>, 

    assertion=assertion@entry=0xf88138 "(longlong) thd->status_var.memory_used >= 0", 

    file=file@entry=0xf86da8 "/home/bar/maria-git/server-10.0/sql/mysqld.cc", 

    line=line@entry=3886, 

    function=function@entry=0xf8e7a0 <my_malloc_size_cb_func::__PRETTY_FUNCTION__> "void my_malloc_size_cb_func(long long int, my_bool)") at assert.c:92

#3  0x00007ffff57e82d2 in __GI___assert_fail (

    assertion=0xf88138 "(longlong) thd->status_var.memory_used >= 0", 

    file=0xf86da8 "/home/bar/maria-git/server-10.0/sql/mysqld.cc", line=3886, 

    function=0xf8e7a0 <my_malloc_size_cb_func::__PRETTY_FUNCTION__> "void my_malloc_size_cb_func(long long int, my_bool)") at assert.c:101

#4  0x000000000058f209 in my_malloc_size_cb_func (size=-140736487993156, 

    is_thread_specific=1 '\001')

    at /home/bar/maria-git/server-10.0/sql/mysqld.cc:3886

#5  0x0000000000f29804 in update_malloc_size (size=-140736487993156, 

    is_thread_specific=1 '\001')

    at /home/bar/maria-git/server-10.0/mysys/my_malloc.c:67

#6  0x0000000000f29d90 in my_free (ptr=0x7fffca8404e0)

    at /home/bar/maria-git/server-10.0/mysys/my_malloc.c:215

#7  0x00007fffc46e70e7 in MySQLGetPrivateProfileStringW (

    lpszSection=<optimized out>, lpszEntry=<optimized out>, 

    lpszDefault=<optimized out>, lpszRetBuffer=0x7ffff7f710e0, 

    cbRetBuffer=256, lpszFilename=<optimized out>)

    at /export/home/pb2/build/sb_0-12449490-1403131949.23/mysql-connector-odbc-5.3.4-src/util/odbcinstw.c:108

#8  0x00007fffc46e6c3f in ds_lookup (ds=0x7fffca932388)

    at /export/home/pb2/build/sb_0-12449490-1403131949.23/mysql-connector-odbc-5.3.4-src/util/installer.c:936

#9  0x00007fffc4632f3f in MySQLDriverConnect (hdbc=<optimized out>, hwnd=0x1, 

    szConnStrIn=0x7fffca816b88, cbConnStrIn=21, szConnStrOut=0x7fffca88c108, 

    cbConnStrOutMax=512, pcbConnStrOut=0x7ffff7f7dfa4, fDriverCompletion=0)

    at /export/home/pb2/build/sb_0-12449490-1403131949.23/mysql-connector-odbc-5.3.4-src/driver/connect.c:575

...

Robert, Bar - thanks, very interesting.

Especially the new backtrace. The crash occurs in different place. But here we can see that myodbc function calls my_free from the MariaDB server.
I wonder which my_malloc it used for that memory allocation.
But probably we can guess that it was own my_malloc(from the linked mysqlclient), and that is root of problems we see here.
Another question is what to do with this...