Spider Table Parameters

When a table uses the Spider storage engine, the following Spider table parameters can be set in the COMMENT clause of the CREATE TABLE statement. Many Spider table parameters have corresponding system variables, so they can be set for all Spider tables on the node. For additional information, see the Spider System Variables page.

From MariaDB 11.3, table parameters can be set using dedicated Spider table options. From MariaDB 11.4, using the COMMENT clause is deprecated.

access_balances

• Description: Connection load balancing integer weight.
• Default Table Value: 0
• DSN Parameter Name: abl

active_link_count

• Description: Number of active remote servers, for use in load balancing read connections
• Default Table Value: all backends
• DSN Parameter Name: alc

auto_increment_mode

• Description: The table level value of spider_auto_increment_mode
• Table Option Name: AUTO_INCREMENT_MODE
• Table Option Introduced: MariaDB 11.3.0

casual_read

• Description:
• Default Table Value:
• DSN Parameter Name:
• Introduced: Spider 3.2

database

• Description: Database name for reference table that exists on remote backend server.
• Default Table Value: local table database
• DSN Parameter Name: database

default_file

• Description: Configuration file used when connecting to remote servers. When the default_group table variable is set, this variable defaults to the values of the --defaults-extra-file or --defaults-file options. When the default_group table variable is not set, it defaults to none.
• Default Table Value: none
• DSN Parameter Name: dff

default_group

• Description: Group name in configuration file used when connecting to remote servers.
• Default Table Value: none
• DSN Parameter Name: dfg

delete_all_rows_type

• Description:
• Default Table Value:
• DSN Parameter Name:
• Introduced: Spider 3.2

host

• Description: Host name of remote server.
• Default Table Value: localhost
• DSN Parameter Name: host

idx000

• Description: When using an index on Spider tables for searching, Spider uses this hint to search the remote table. The remote table index is related to the Spider table index by this hint. The number represented by 000 is the index ID, which is the number of the index shown by the SHOW CREATE TABLE statement. 000 is the Primary Key. For instance, idx000 "force index(PRIMARY)" (in abbreviated format idx000 "f PRIMARY").
  • f force index
  • u use index
  • ig ignore index
• Default Table Value: none

internal_delayed

• Description: Whether to transmit existence of delay to remote servers when executing an INSERT DELAYED statement on local server.
  • 0 Doesn't transmit.
  • 1 Transmits.
• Default Table Value: 0
• DSN Parameter Name: idl

link_status

• Description: Change status of the remote backend server link.
  • 0 Doesn't change status.
  • 1 Changes status to OK.
  • 2 Changes status to RECOVERY.
  • 3 Changes status to no more in group communication.
• Default Table Value: 0
• DSN Parameter Name: lst

monitoring_bg_interval

• Description: Interval of background monitoring in microseconds.
• Default Table Value: 10000000
• DSN Parameter Name: mbi

monitoring_bg_kind

• Description: Kind of background monitoring to use.
  • 0 Disables background monitoring.
  • 1 Monitors connection state.
  • 2 Monitors state of table without WHERE clause.
  • 3 Monitors state of table with WHERE clause (currently unsupported).
• Default Table Value: 0
• DSN Parameter Name: mbk

monitoring_kind

• Description: Kind of monitoring.
  • 0 Disables monitoring
  • 1 Monitors connection state.
  • 2 Monitors state of table without WHERE clause.
  • 3 Monitors state of table with WHERE clause (currently unsupported).
• Default Table Value: 0
• DSN Parameter Name: mkd

monitoring_limit

• Description: Limits the number of records in the monitoring table. This is only effective when Spider monitors the state of a table, which occurs when the monitoring_kind table variable is set to a value greater than 1.
• Default Table Value: 1
• Range: 0 upwards
• DSN Parameter Name: mlt

monitoring_server_id

• Description: Preferred monitoring @@server_id for each backend failure. You can use this to geo-localize backend servers and set the first Spider monitoring node to contact for failover. In the event that this monitor fails, other monitoring nodes are contacted. For multiple copy backends, you can set a lazy configuration with a single MSI instead of one per backend.
• Default Table Value: server_id
• DSN Parameter Name: msi

password

• Description: Remote server password.
• Default Table Value: none
• DSN Parameter Name: password

port

• Description: Remote server port.
• Default Table Value: 3306
• DSN Parameter Name: port

priority

• Description: Priority. Used to define the order of execution. For instance, Spider uses priority when deciding the order in which to lock tables on a remote server.
• Default Table Value: 1000000
• DSN Parameter Name: prt

query_cache

• Description: Passes the option for the Query Cache when issuing SELECT statements to the remote server.
  • 0 No option passed.
  • 1 Passes the SQL_CACHE option.
  • 2 Passes the SQL_NO_CACHE option.
• Default Table Value: 0
• DSN Parameter Name: qch

read_rate

• Description: Rate used to calculate the amount of time Spider requires when executing index scans.
• Default Table Value: 0.0002
• DSN Parameter Name: rrt

scan_rate

• Description: Rate used to calculate the amount of time Spider requires when scanning tables.
• Default Table Value: 0.0001
• DSN Parameter Name: srt

server

• Description: Server name. Used when generating connection information with CREATE SERVER statements.
• Default Table Value: none
• DSN Parameter Name: srv

socket

• Description: Remote server socket.
• Default Table Value: none
• DSN Parameter Name: socket

ssl_ca

• Description: Path to the Certificate Authority file.
• Default Table Value: none
• DSN Parameter Name: sca

ssl_capath

• Description: Path to directory containing trusted TLS CA certificates in PEM format.
• Default Table Value: none
• DSN Parameter Name: scp

ssl_cert

• Description: Path to the certificate file.
• Default Table Value: none
• DSN Parameter Name: scr

ssl_cipher

• Description: List of allowed ciphers to use with TLS encryption.
• Default Table Value: none
• DSN Parameter Name: sch

ssl_key

• Description: Path to the key file.
• Default Table Value: none
• DSN Parameter Name: sky

ssl_verify_server_cert

• Description: Enables verification of the server's Common Name value in the certificate against the host name used when connecting to the server.
  • 0 Disables verification.
  • 1 Enables verification.
• Default Table Value: 0
• DSN Parameter Name: svc

table

• Description: Destination table name.
• Default Table Value: Same table name
• DSN Parameter Name: tbl