[MXS-3506] SELECT on temporary table fails with "Table doesn't exist" error Created: 2021-04-16  Updated: 2021-04-22  Resolved: 2021-04-21

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mike Ilin Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: maxscale
Environment:

OS Xubuntu 16.04

Attachments: Text File fragment.cpp    

 Description   

The problem is specific for MariaDB Galera Cluster, and cannot be reproduced on standalone MariaDB.

Steps to reproduce:
1. Execute the following two statements within one query, using mysql_real_query( ) function:
(CLIENT_MULTI_STATEMENTS flag is passed to mysql_real_connect() )

DROP TEMPORARY TABLE IF EXISTS __ttmp_test_host_product_table;
 
CREATE TEMPORARY TABLE IF NOT EXISTS `__ttmp_test_host_product_table` (   `hostid` INT,   `productname` VARCHAR(32) UNICODE,   `bitmask` INT )ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;

2. Execute the statement below in a separate query, but on the same connection:

SELECT * FROM `__ttmp_test_host_product_table`

Result is:

mysql_errno:1146, mysql_warning_count:0, mysql_sqlstate:'42S02', mysql_error:'Table 'ILIN.__ttmp_test_host_product_table' doesn't exist'

Still it's possible to execute DELETE or INSERT on the temporary table 'ILIN.__ttmp_test_host_product_table'

 Comments   
Comment by Jan Lindström (Inactive) [ 2021-04-19 ]

Hi, can you give full example program how to reproduce this?

Comment by Mike Ilin [ 2021-04-20 ]

Hi, code fragment is in the attachment.

Here is trace fragment corresponding to the code execution:

~~ Execute query DROP TEMPORARY TABLE IF EXISTS `__ttmp_test_host_product_table`; CREATE TEMPORARY TABLE IF NOT EXISTS `__ttmp_test_host_product_table` (   `hostid` INT,   `productname` VARCHAR(32) UNICODE,   `bitmask` INT ) ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
 
~~ Query ok, result: 0
 
~~ Execute query SELECT hostid FROM `__ttmp_test_host_product_table`
 
~~ Error code: 1146 Error msg: Table 'ILIN.__ttmp_test_host_product_table' doesn't exist Sql state: 42S02

Comment by Jan Lindström (Inactive) [ 2021-04-20 ]

Hi, thanks but can I really use that code as it uses KLSTD_TRACE and KLSTD_CHECK ?

Comment by Mike Ilin [ 2021-04-20 ]

I updated the code in the attachment

Comment by Ramesh Sivaraman [ 2021-04-20 ]

Mike Ilin Could not reproduce the issue on my local machine using given program. 

$ cat '$kldb.txt'
 
~~ Execute query DROP TEMPORARY TABLE IF EXISTS `__ttmp_test_host_product_table`; CREATE TEMPORARY TABLE IF NOT EXISTS `__ttmp_test_host_product_table` (   `hostid` INT,   `productname` VARCHAR(32) UNICODE,   `bitmask` INT ) ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
 
~~ Query ok, result: 0
 
~~ Execute query SELECT hostid FROM `__ttmp_test_host_product_table`
 
$
 
 
 
MariaDB [(none)]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.3.27-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]>

Comment by Mike Ilin [ 2021-04-20 ]

@Ramesh Sivaraman perhaps, the problem is caused by Maxscale proxy which is used to redirect queries to Galera nodes. Is it possible that maxscale proxy connects to some other node while running the queries ?

Comment by Ramesh Sivaraman [ 2021-04-20 ]

Mike Ilin MaxScale can do read/write splitting with Galera cluster. Please check whether you have enabled read/write splitting in maxscale.
https://mariadb.com/kb/en/mariadb-maxscale-20-readwrite-splitting-with-galera-cluster/

Comment by Mike Ilin [ 2021-04-21 ]

Hi !
Yes, Read/Write splitting is enabled in the configuration file.

Comment by Ramesh Sivaraman [ 2021-04-21 ]

Mike Ilin This confirms the issue is due to read/write splitting. Please let us know if you see any other issue.

Comment by Mike Ilin [ 2021-04-21 ]

Thanks for the information ! Can I just disable splitting by removing this section from the config file:

[Splitter Service]
 
type=service
 
router=readwritesplit
 
servers=dbserv1, dbserv2, dbserv3

or additional settings are required ?

Comment by markus makela [ 2021-04-21 ]

Change it from router=readwritesplit to router=readconnroute and add router_options=master to that section.

Generated at Thu Feb 08 04:21:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.