[MDEV-32753] Spider engine does not load in ORACLE mode Created: 2023-11-09  Updated: 2024-01-16  Resolved: 2023-11-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Yuchen Pei
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Blocks
blocks MDEV-29870 Backport fixes to spider init bugs to... Closed
blocks MDEV-32773 Syntax error upon Spider initializati... Open
Problem/Incident
is caused by MDEV-22979 "mysqld --bootstrap" / mysql_install_... Closed
Relates
relates to MDEV-33242 SPIDER plugin initialization failed a... Closed

 Description   

Spider does not load if the server is running with sql_mode=ORACLE.

It is true both when the server is started in ORACLE mode and plugin-load-add=ha_spider or when they are set/loaded at runtime.

I didn't check, but it must have started around the same time as other similar issues, with MDEV-27095 and further fixes.

bb-10.10-release 04d9a46c41b36b61057741abddf7840962e76893

[ERROR] SPIDER plugin initialization failed at 'if @win_plugin = 0 then  begin not atomic    declare exit handler for 1041, 1123      replace into mysql.func values        ('spider_direct_sql', 2, 'ha_spider.so', 'function'),        ('spider_bg_direct_sql', 2, 'ha_spider.so', 'aggregate'),        ('spider_ping_table', 2, 'ha_spider.so', 'function'),        ('spider_copy_tables', 2, 'ha_spider.so', 'function'),        ('spider_flush_table_mon_cache', 2, 'ha_spider.so', 'function');    create function if not exists spider_direct_sql returns int      soname 'ha_spider.so';    create aggregate function if not exists spider_bg_direct_sql returns int      soname 'ha_spider.so';    create function if not exists spider_ping_table returns int      soname 'ha_spider.so';    create function if not exists spider_copy_tables returns int      soname 'ha_spider.so';    create function if not exists spider_flush_table_mon_cache returns int      soname 'ha_spider.so';  end;else  begin not atomic    declare exit handler for 1041, 1123      replace into mysql.func values        ('spider_direct_sql', 2, 'ha_spider.dll', 'function'),        ('spider_bg_direct_sql', 2, 'ha_spider.dll', 'aggregate'),        ('spider_ping_table', 2, 'ha_spider.dll', 'function'),        ('spider_copy_tables', 2, 'ha_spider.dll', 'function'),        ('spider_flush_table_mon_cache', 2, 'ha_spider.dll', 'function');    create function if not exists spider_direct_sql returns int      soname 'ha_spider.dll';    create aggregate function if not exists spider_bg_direct_sql returns int      soname 'ha_spider.dll';    create function if not exists spider_ping_table returns int      soname 'ha_spider.dll';    create function if not exists spider_copy_tables returns int      soname 'ha_spider.dll';    create function if not exists spider_flush_table_mon_cache returns int      soname 'ha_spider.dll';  end;end if;' by 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'create function if not exists spider_direct_sql returns int      soname 'ha_s...' at line 1'



 Comments   
Comment by Yuchen Pei [ 2023-11-10 ]

Hi holyfoot, ptal thanks:

bb-10.10-mdev-32753 8b871cd6e11e7ac7df2ea2b8292c1f6528ead346
MDEV-32753 Make spider init queries compatible with oracle sql mode
 
When sql_mode contains oracle, declare should be before begin, and
'not atomic' is not needed.

Comment by Sergei Golubchik [ 2023-11-12 ]

wouldn't it be simpler to reset MODE_ORACLE bit before executing your init queries?

Comment by Yuchen Pei [ 2023-11-14 ]

Good point serg. I updated my patch, holyfoot ptal thanks

upstream/bb-10.10-mdev-32753 555b7aed68ddb3f0a4cb0d4eb553162a17b7e561
MDEV-32753 Make spider init queries compatible with oracle sql mode
 
Remove ORACLE from the (session) sql_mode in connections made with sql
service to run init queries
 
The connection is new and the global variable value takes effect
rather than the session value from the caller of spider_db_init.

Comment by Alexey Botchkov [ 2023-11-20 ]

ok to push.

Comment by Yuchen Pei [ 2023-11-21 ]

Pushed 45fadb64c1c4ad7c71fea874d51c83b1d11b4e63 to 10.11

Generated at Thu Feb 08 10:33:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.