0

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
  • Note that it's usually more helpful to include the text of your queries and error messages than a screen shot. It can be much easier to read, and your code can simply be copied and run to try to replicate your issue. – RDFozz Jan 24 at 16:15
0

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.

-2

This bug was already fixed at 10.3.13.

  • I suggest to extend your answer with some references, so you will likely have some upvotes as well. Very short answers are mostly moderated out because they have many problems. – peterh Apr 2 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.