# Spider Table Parameters

When a table uses the [Spider](https://mariadb.com/docs/server/server-usage/storage-engines/spider) storage engine, the following Spider table parameters can be set in the `COMMENT` clause of the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/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](https://mariadb.com/docs/server/server-usage/storage-engines/spider/spider-system-variables) page.

From [MariaDB 11.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/what-is-mariadb-113), many table parameters can be set using dedicated Spider table options, see the Table Option Name fields below. From [MariaDB 11.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/what-is-mariadb-114), using the `COMMENT` clause is deprecated, as well as table parameters that do not have corresponding table options.

#### `access_balances`

* Description: Connection load balancing integer weight.
* Default Table Value: `0`
* DSN Parameter Name: `abl`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `auto_increment_mode`

* Description: The table level value of [spider\_auto\_increment\_mode](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_auto_increment_mode)
* Table Option Name: `AUTO_INCREMENT_MODE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `bgs_mode`

* Description: The table level value of [spider\_bgs\_mode](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_bgs_mode).
* Table Option Name: `BGS_MODE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `bulk_size`

* Description: The table level value of [spider\_bulk\_size](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_bulk_size).
* Table Option Name: `BULK_SIZE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `bulk_update_size`

* Description: The table level value of [spider\_bulk\_update\_size](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_bulk_update_size).
* Table Option Name: `BULK_UPDATE_SIZE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `casual_read`

* Description:
* Default Table Value:
* DSN Parameter Name:
* Introduced: Spider 3.2
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `connect_timeout`

* Description: The table level value of [spider\_connect\_timeout](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_connect_timeout).
* Table Option Name: `CONNECT_TIMEOUT`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `database`

* Description: Database name for reference table that exists on remote backend server.
* Default Table Value: `local table database`
* DSN Parameter Name: `database`
* Table Option Name: `REMOTE_DATABASE`
* Table Option Introduced: [MariaDB 10.8.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.8/10.8.0)

#### `default_file`

* Description: Configuration file used when connecting to remote servers. When the [default\_group](#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](#default_group) table variable is not set, it defaults to `none`.
* Default Table Value: `none`
* DSN Parameter Name: `dff`
* Table Option Name: `DEFAULT_FILE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `default_group`

* Description: Group name in configuration file used when connecting to remote servers.
* Default Table Value: `none`
* DSN Parameter Name: `dfg`
* Table Option Name: `DEFAULT_GROUP`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `delete_all_rows_type`

* Description: The table level value of [spider\_delete\_all\_rows\_type](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_delete_all_rows_type).
* Introduced: Spider 3.2
* Table Option Name: `DELETE_ALL_ROWS_TYPE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `force_bulk_delete`

* Description:
* Introduced: [MariaDB 10.0.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.0/10.0.5)
* Table Option Name: `FORCE_BULK_DELETE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `force_bulk_update`

* Description:
* Introduced: [MariaDB 10.0.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.0/10.0.5)
* Table Option Name: `FORCE_BULK_UPDATE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `host`

* Description: Host name of remote server.
* Default Table Value: `localhost`
* DSN Parameter Name: `host`
* Table Option Name: `REMOTE_HOST`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/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`
* Table Option Name: `IDX`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `monitoring_bg_interval`

* Description: Interval of background monitoring in microseconds.
* Default Table Value: `10000000`
* DSN Parameter Name: `mbi`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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](#monitoring_kind) table variable is set to a value greater than `1`.
* Default Table Value: `1`
* Range: `0` upwards
* DSN Parameter Name: `mlt`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `multi_split_read`

* Description: The table level value of [spider\_multi\_split\_read](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_multi_split_read).
* Table Option Name: `MULTI_SPLIT_READ`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `net_read_timeout`

* Description: The table level value of [spider\_net\_read\_timeout](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_net_read_timeout).
* Table Option Name: `NET_READ_TIMEOUT`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `net_write_timeout`

* Description: The table level value of [spider\_net\_write\_timeout](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_net_write_timeout).
* Table Option Name: `NET_WRITE_TIMEOUT`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `password`

* Description: Remote server password.
* Default Table Value: `none`
* DSN Parameter Name: `password`
* Table Option Name: `REMOTE_PASSWORD`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `port`

* Description: Remote server port.
* Default Table Value: `3306`
* DSN Parameter Name: `port`
* Table Option Name: `REMOTE_PORT`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `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`
* Table Option Name: `PRIORITY`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `query_cache`

* Description: Uses the option for the [Query Cache](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache) when issuing [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) statements to the remote server.
  * `0` No option used.
  * `1` Uses the [SQL\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache#sql_no_cache-and-sql_cache) option.
  * `2` Uses the [SQL\_NO\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache#sql_no_cache-and-sql_cache) option.
* Default Table Value: `0`
* DSN Parameter Name: `qch`
* Table Option Name: `QUERY_CACHE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `query_cache_sync`

* Description: A two-bit bitmap. Whether to pass the option for the [Query Cache](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache) (if any) when issuing [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) statements to the remote server.
  * `0` No option passed.
  * `1` Passes the [SQL\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache#sql_no_cache-and-sql_cache) option, if specified in the query to the spider table.
  * `2` Passes the [SQL\_NO\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache) option, if specified in the query to the spider table.
  * `3` Passes both the [SQL\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache#sql_no_cache-and-sql_cache) option and the [SQL\_NO\_CACHE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache) option, if specified in the query to the spider table.
* Default Table Value: `3`
* Table Option Name: `QUERY_CACHE_SYNC`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `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`
* Deprecated: [MariaDB 11.4.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.4/11.4.0)

#### `server`

* Description: Server name. Used when generating connection information with [CREATE SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-server) statements.
* Default Table Value: `none`
* DSN Parameter Name: `srv`
* Table Option Name: `REMOTE_SERVER`
* Table Option Introduced: [MariaDB 10.8.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.8/10.8.0)

#### `skip_parallel_search`

* Description: The table level value of [spider\_skip\_parallel\_search](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_skip_parallel_search).
* Table Option Name: `SKIP_PARALLEL_SEARCH`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `socket`

* Description: Remote server socket.
* Default Table Value: `none`
* DSN Parameter Name: `socket`
* Table Option Name: `REMOTE_SOCKET`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `ssl_ca`

* Description: Path to the Certificate Authority file.
* Default Table Value: `none`
* DSN Parameter Name: `sca`
* Table Option Name: `SSL_CA`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `ssl_capath`

* Description: Path to directory containing trusted TLS CA certificates in PEM format.
* Default Table Value: `none`
* DSN Parameter Name: `scp`
* Table Option Name: `SSL_CAPATH`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `ssl_cert`

* Description: Path to the certificate file.
* Default Table Value: `none`
* DSN Parameter Name: `scr`
* Table Option Name: `SSL_CERT`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `ssl_cipher`

* Description: List of allowed ciphers to use with [TLS encryption](https://mariadb.com/docs/server/security/encryption/data-in-transit-encryption/secure-connections-overview).
* Default Table Value: `none`
* DSN Parameter Name: `sch`
* Table Option Name: `SSL_CIPHER`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `ssl_key`

* Description: Path to the key file.
* Default Table Value: `none`
* DSN Parameter Name: `sky`
* Table Option Name: `SSL_KEY`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `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 Option Name: `SSL_VSC`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `table`

* Description: Destination table name.
* Default Table Value: `Same table name`
* DSN Parameter Name: `tbl`
* Table Option Name: `REMOTE_TABLE`
* Table Option Introduced: [MariaDB 10.8.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.8/10.8.0)

#### `table_count_mode`

* Description: for setting table flags HA\_STATS\_RECORDS\_IS\_EXACT and HA\_HAS\_RECORDS.
* Default Table Value: `0`
* Table Option Name: `TABLE_COUNT_MODE`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `username`

* Description: user name for the data node.
* Default Table Value: `Same user name`
* Table Option Name: `REMOTE_USERNAME`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `use_pushdown_udf`

* Description: The table level value of [spider\_use\_pushdown\_udf](https://mariadb.com/docs/server/server-usage/storage-engines/spider-system-variables#spider_use_pushdown_udf).
* Table Option Name: `USE_PUSHDOWN_UDF`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

#### `wrapper`

* Description: wrapper for the data node.
* Table Option Name: `WRAPPER`
* Table Option Introduced: [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)

### Spider Table: Connection Parameters

The Spider table's connection to a remote server is managed via the following engine-defined settings. The `CREATE TABLE` statement specifies these parameters, which take precedence over `COMMENT=` and `CONNECTION=`. See [Specifying Connection Information for Spider Tables](https://mariadb.com/docs/server/server-usage/storage-engines/spider-storage-engine-core-concepts#specifying-connection-information-for-spider-tables) for a detailed explanation of all connection methods and precedence rules.

**Engine-defined Connection Options**

| Option            | Description                                                                                                          | Available since |
| ----------------- | -------------------------------------------------------------------------------------------------------------------- | --------------- |
| `REMOTE_SERVER`   | Name of the server object generated with `CREATE SERVER` that will be used for the remote connection.                | 10.8.1          |
| `REMOTE_DATABASE` | Database name on the remote server. The value defined in the referenced server object is used if it is not provided. | 10.8.1          |
| `REMOTE_TABLE`    | Name of the table on the remote server. The local table name is used if it is not provided.                          | 10.8.1          |

{% hint style="info" %}
Starting with version 11.3.1, `COMMENT=` and `CONNECTION=` are silently ignored whenever any engine-defined option is present.
{% endhint %}

{% hint style="info" %}
To suppress the warning, `set spider_suppress_comment_ignored_warning = 1`. Since 11.4.1, the connection methods `COMMENT=` and `CONNECTION=` have been deprecated. Instead, make use of engine-defined options.
{% endhint %}

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}