MariaDB Spider Engine UDF spider_bg_direct_sql can’t find TEMPORARY table

mariadbspidertemporary-tables

I am testing the Spider engine in MariaDB. In version mariadb-10.1.12-linux-x86_64 the UDF works, but there are some issues so I want to upgrade to a newer version. However, mariadb-10.2.18-linux-x86_64,mariadb-10.3.12-linux-x86_64,mariadb-10.3.9-linux-x86_64,mariadb-10.4.1-linux-x86_64 the Spider UDF spider_bg_direct_sql can't find the TEMPORARY table even though I can select that temporary table outside of the UDF.

Does anyone know how to solve this?

enter image description here

enter image description here

CREATE TEMPORARY TABLE test.test1(
  `IMSI` VARCHAR(20) NOT NULL,
  `DATA_DATE` DATE NOT NULL,
  `PU_ID` MEDIUMINT(9) NOT NULL DEFAULT 0,
  `DATA_DATE_TS` INT(11) NOT NULL,
  `TECH_MASK` TINYINT(2) NOT NULL,
  PRIMARY KEY (`IMSI`,`DATA_DATE`,`PU_ID`,`DATA_DATE_TS`,`TECH_MASK`)
)

SELECT * FROM test.test1   #this command can find a table
SELECT spider_bg_direct_sql('select * from `gt_global_imsi`.`table_imsi_pu` '
    , 'test.test1'
    ,  'srv "PU1", port "3308"'
    )

error msg

Error Code: 12703

Temporary table 'test.test1' is not found

My spider settings:

Variable_name   Value
spider_auto_increment_mode  -1
spider_bgs_first_read   -1
spider_bgs_mode -1
spider_bgs_second_read  -1
spider_bka_engine   
spider_bka_mode -1
spider_bka_table_name_type  -1
spider_block_size   16384
spider_bulk_size    -1
spider_bulk_update_mode -1
spider_bulk_update_size -1
spider_casual_read  -1
spider_conn_recycle_mode    0
spider_conn_recycle_strict  0
spider_conn_wait_timeout    10
spider_connect_error_interval   1
spider_connect_mutex    OFF
spider_connect_retry_count  1000
spider_connect_retry_interval   1000
spider_connect_timeout  -1
spider_crd_bg_mode  -1
spider_crd_interval -1
spider_crd_mode -1
spider_crd_sync -1
spider_crd_type -1
spider_crd_weight   -1
spider_delete_all_rows_type -1
spider_direct_dup_insert    -1
spider_direct_order_limit   -1
spider_dry_access   OFF
spider_error_read_mode  -1
spider_error_write_mode -1
spider_first_read   -1
spider_force_commit 1
spider_general_log  OFF
spider_index_hint_pushdown  OFF
spider_init_sql_alloc_size  -1
spider_internal_limit   -1
spider_internal_offset  -1
spider_internal_optimize    -1
spider_internal_optimize_local  -1
spider_internal_sql_log_off -1
spider_internal_unlock  OFF
spider_internal_xa  OFF
spider_internal_xa_id_type  0
spider_internal_xa_snapshot 0
spider_load_crd_at_startup  -1
spider_load_sts_at_startup  -1
spider_local_lock_table OFF
spider_lock_exchange    OFF
spider_log_result_error_with_sql    0
spider_log_result_errors    0
spider_low_mem_read -1
spider_max_connections  0
spider_max_order    -1
spider_multi_split_read -1
spider_net_read_timeout -1
spider_net_write_timeout    -1
spider_ping_interval_at_trx_start   3600
spider_quick_mode   -1
spider_quick_page_size  -1
spider_read_only_mode   -1
spider_remote_access_charset    
spider_remote_autocommit    -1
spider_remote_default_database  
spider_remote_sql_log_off   -1
spider_remote_time_zone 
spider_remote_trx_isolation -1
spider_reset_sql_alloc  -1
spider_same_server_link OFF
spider_second_read  -1
spider_select_column_mode   -1
spider_selupd_lock_mode -1
spider_semi_split_read  -1
spider_semi_split_read_limit    -1
spider_semi_table_lock  1
spider_semi_table_lock_connection   -1
spider_semi_trx ON
spider_semi_trx_isolation   -1
spider_skip_default_condition   -1
spider_skip_parallel_search -1
spider_split_read   -1
spider_store_last_crd   -1
spider_store_last_sts   -1
spider_sts_bg_mode  -1
spider_sts_interval -1
spider_sts_mode -1
spider_sts_sync -1
spider_support_xa   ON
spider_sync_autocommit  ON
spider_sync_trx_isolation   ON
spider_table_crd_thread_count   10
spider_table_init_error_interval    1
spider_table_sts_thread_count   10
spider_udf_ct_bulk_insert_interval  -1
spider_udf_ct_bulk_insert_rows  -1
spider_udf_ds_bulk_insert_rows  -1
spider_udf_ds_table_loop_mode   -1
spider_udf_ds_use_real_table    -1
spider_udf_table_lock_mutex_count   20
spider_udf_table_mon_mutex_count    20
spider_use_all_conns_snapshot   OFF
spider_use_consistent_snapshot  OFF
spider_use_default_database ON
spider_use_flash_logs   OFF
spider_use_handler  -1
spider_use_pushdown_udf -1
spider_use_snapshot_with_flush_tables   0
spider_use_table_charset    -1
spider_version  3.3.13
spider_xa_register_mode 1

Best Answer

CREATE TEMPORARY TABLE creates a table that is visible only to the one connection. Quite possibly, going through a UDF loses that context, thereby not letting you see it.

I suggest creating a permanent table, perhaps with a predictable table name (such as tacking on the pid); but be dure to DROP it eventually.

Related Question