[ODBC-159] Creation of temporary tables with multiple statements in one query fails. Created: 2018-07-10  Updated: 2018-08-03  Resolved: 2018-07-27

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.0.5
Fix Version/s: 3.0.6

Type: Bug Priority: Major
Reporter: Patrick Dudziak Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

MariaDB 10.2.13 on FreeBSD 11.1, Client Windows 10 with ODBC Connector 3.0.5

Attachments: Text File general_log.txt    

 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.

 Comments   
Comment by Lawrin Novitsky [ 2018-07-10 ]

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.

Comment by Lawrin Novitsky [ 2018-07-13 ]

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.

Comment by Patrick Dudziak [ 2018-07-19 ]

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.

Comment by Lawrin Novitsky [ 2018-07-19 ]

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

Comment by Lawrin Novitsky [ 2018-07-27 ]

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

Generated at Thu Feb 08 03:26:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.