[ODBC-159] Creation of temporary tables with multiple statements in one query fails. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 3.0.5
Fix Version/s: 3.0.6
Component/s: General
Labels:
None
Environment:
MariaDB 10.2.13 on FreeBSD 11.1, Client Windows 10 with ODBC Connector 3.0.5

Description

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;

CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);

SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:

using (var connection = new OdbcConnection(connectionString))

   connection.Open();

   using (var command = connection.CreateCommand())

      command.CommandText = Resource.CreateAndSelectTempTable;

      int result = command.ExecuteNonQuery();

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:

using (var connection = new OdbcConnection(connectionString))

   int result;

   connection.Open();

   using (var command = connection.CreateCommand())

      command.CommandText = Resource.DropTempTable;

      result = command.ExecuteNonQuery();

   using (var command = connection.CreateCommand())

      command.CommandText = Resource.CreateTempTable;

      result = command.ExecuteNonQuery();

   using (var command = connection.CreateCommand())

      command.CommandText = Resource.SelectTempTable;

      result = command.ExecuteNonQuery();

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

general_log.txt
0.5 kB
2018-07-10 13:24

Activity

Ascending order - Click to sort in descending order

Patrick Dudziak created issue - 2018-07-10 13:27

Patrick Dudziak made changes - 2018-07-10 13:29

Field	Original Value	New Value
Description	We are having issues with the creation of temporary tables through the MariaDB ODBC Connector. The statements we are issuing: DROP TABLE IF EXISTS _temp_statistics; CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS); SELECT * FROM _temp_statistics; We execute these statements in a single query like so: using (var connection = new OdbcConnection(connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = Resource.CreateAndSelectTempTable; int result = command.ExecuteNonQuery(); } } The error we get is: " ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist" However if we execute this in multiple queries it does work. Like so: using (var connection = new OdbcConnection(connectionString)) { int result; connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = Resource.DropTempTable; result = command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = Resource.CreateTempTable; result = command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = Resource.SelectTempTable; result = command.ExecuteNonQuery(); } } Is there a bug concering multiple statements in a single query and temporary tables? Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails. Like always i am happy to provide more information on this as needed.	We are having issues with the creation of temporary tables through the MariaDB ODBC Connector. The statements we are issuing: DROP TABLE IF EXISTS _temp_statistics; CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS); SELECT * FROM _temp_statistics; We execute these statements in a single query like so: ^using (var connection = new OdbcConnection(connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = Resource.CreateAndSelectTempTable; int result = command.ExecuteNonQuery(); } }^ The error we get is: " ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist" However if we execute this in multiple queries it does work. Like so: using (var connection = new OdbcConnection(connectionString)) { int result; connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = Resource.DropTempTable; result = command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = Resource.CreateTempTable; result = command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = Resource.SelectTempTable; result = command.ExecuteNonQuery(); } } Is there a bug concering multiple statements in a single query and temporary tables? Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails. Like always i am happy to provide more information on this as needed.

Patrick Dudziak made changes - 2018-07-10 13:29

Description

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:
^using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}^

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

Patrick Dudziak made changes - 2018-07-10 13:29

Description

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}
{code}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

Patrick Dudziak made changes - 2018-07-10 13:30

Description

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}
{code}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}
{code}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}
{code}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

Patrick Dudziak made changes - 2018-07-10 13:30

Description

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;

We execute these statements in a single query like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}
{code}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}
{code}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

We are having issues with the creation of temporary tables through the MariaDB ODBC Connector.

The statements we are issuing:

{code:java}
DROP TABLE IF EXISTS _temp_statistics;
CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS);
SELECT * FROM _temp_statistics;
{code}

We execute these statements in a single query like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateAndSelectTempTable;
      int result = command.ExecuteNonQuery();
   }
}
{code}

The error we get is:
" ERROR [42S02] [ma-3.0.5][10.2.13-MariaDB-log]Table 'DB._temp_statistics' doesn't exist"

However if we execute this in multiple queries it does work. Like so:

{code:java}
using (var connection = new OdbcConnection(connectionString))
{
   int result;
   connection.Open();
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.DropTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.CreateTempTable;
      result = command.ExecuteNonQuery();
   }
   using (var command = connection.CreateCommand())
   {
      command.CommandText = Resource.SelectTempTable;
      result = command.ExecuteNonQuery();
   }
}
{code}

Is there a bug concering multiple statements in a single query and temporary tables?

Both versions do work with the mysql connector. I have also attached the general logfile for the case when the command fails.

Like always i am happy to provide more information on this as needed.

Lawrin Novitsky added a comment - 2018-07-10 17:01

Thank you for your report!
Batch statement execution may be a problem. We honestly say that here https://mariadb.com/kb/en/library/about-mariadb-connector-odbc/ in "Bugs and Limitations" The problem is that the connector uses primarily prepared statements(aka mysql binary protocol). And preparing of multiple statements is not supported by any server(mariadb or mysql). Thus connector splits it into individual statements, and executes one by one. And thus once in a while people hit some parsing bugs. Also, it makes using of statement batches not very beneficial with C/ODBC. However, if all queries in a batch do not return results, i.e. all are DDL/DML, connector will send them in single batch via text protocol. But, again, if it parses everything correctly.

Lawrin Novitsky added a comment - 2018-07-10 17:01 Thank you for your report! Batch statement execution may be a problem. We honestly say that here https://mariadb.com/kb/en/library/about-mariadb-connector-odbc/ in "Bugs and Limitations" The problem is that the connector uses primarily prepared statements(aka mysql binary protocol). And preparing of multiple statements is not supported by any server(mariadb or mysql). Thus connector splits it into individual statements, and executes one by one. And thus once in a while people hit some parsing bugs. Also, it makes using of statement batches not very beneficial with C/ODBC. However, if all queries in a batch do not return results, i.e. all are DDL/DML, connector will send them in single batch via text protocol. But, again, if it parses everything correctly.

Lawrin Novitsky added a comment - 2018-07-13 11:51

Ok, everything is fine here with the parsing. The reason for the error is simple, and could be guessed from the error's text. Everything is prepared before been executed. And thus SELECT * FROM _temp_statistics is prepared when table does not exist, i.e. CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS) has only been prepared, and not executed.
There are few ways to fix that for the case of SQLExecDirect, and especially for MariaDB server >=10.2. And that is exactly your case. I need to think about that.

Lawrin Novitsky added a comment - 2018-07-13 11:51 Ok, everything is fine here with the parsing. The reason for the error is simple, and could be guessed from the error's text. Everything is prepared before been executed. And thus SELECT * FROM _temp_statistics is prepared when table does not exist, i.e. CREATE TEMPORARY TABLE _temp_statistics AS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS) has only been prepared, and not executed. There are few ways to fix that for the case of SQLExecDirect, and especially for MariaDB server >=10.2. And that is exactly your case. I need to think about that.

Patrick Dudziak added a comment - 2018-07-19 12:24

Thank you for your hard work. We really appreciate the insight you shared with us.
Since it dosen't seem like there will be a fix any time soon, i guess we can put this bug on hold.

Patrick Dudziak added a comment - 2018-07-19 12:24 Thank you for your hard work. We really appreciate the insight you shared with us. Since it dosen't seem like there will be a fix any time soon, i guess we can put this bug on hold.

Lawrin Novitsky added a comment - 2018-07-19 14:45

I actually planned to include the fix in the coming release, if I have time for it. I've already started work on it. But as of 3.0.5, you could send DROP in CREATE as a batch, and SELECT separately. In this case connector will send DROP and CREATE together. I am plan to make connector to do that automatically - e.g. split your multistatement query into 2 DROP+CREATE, and SELECT

Lawrin Novitsky added a comment - 2018-07-19 14:45 I actually planned to include the fix in the coming release, if I have time for it. I've already started work on it. But as of 3.0.5, you could send DROP in CREATE as a batch, and SELECT separately. In this case connector will send DROP and CREATE together. I am plan to make connector to do that automatically - e.g. split your multistatement query into 2 DROP+CREATE, and SELECT

Lawrin Novitsky added a comment - 2018-07-27 15:56 - edited

I've decided to push only fiex for SQLExecDirect case without optimization I was talking about
Commit d888051 in master and odbc-3.0

Lawrin Novitsky added a comment - 2018-07-27 15:56 - edited I've decided to push only fiex for SQLExecDirect case without optimization I was talking about Commit d888051 in master and odbc-3.0

Lawrin Novitsky made changes - 2018-07-27 15:56

Fix Version/s		3.0.6 [ 23133 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:28

Workflow

MariaDB v3 [ 88340 ]

MariaDB v4 [ 135473 ]

People

Assignee:: Lawrin Novitsky

Reporter:: Patrick Dudziak

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-07-10 13:27

Updated:: 2018-08-03 02:28

Resolved:: 2018-07-27 15:56

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Connector/ODBC

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration