[MDEV-27106] Spider: specify connection to data node by engine-defined attributes - Jira

Geoff Montee (Inactive) created issue - 2020-05-13 02:46

Geoff Montee (Inactive) made changes - 2020-05-13 02:46

Field	Original Value	New Value
Link		This issue relates to MENT-762 [ MENT-762 ]

Geoff Montee (Inactive) made changes - 2020-05-13 02:46

Link

This issue relates to MDEV-22541 [ MDEV-22541 ]

Geoff Montee (Inactive) made changes - 2020-05-15 20:03

Link

This issue relates to MENT-796 [ MENT-796 ]

Geoff Montee (Inactive) made changes - 2020-05-15 20:34

Link

This issue relates to MENT-797 [ MENT-797 ]

Julien Fritsch made changes - 2020-05-18 07:59

Fix Version/s		10.4 [ 23604 ]
Fix Version/s		10.3 [ 23605 ]
Fix Version/s		10.5 [ 23608 ]

Geoff Montee (Inactive) made changes - 2020-05-18 17:47

Link

This issue relates to MENT-799 [ MENT-799 ]

Ralf Gebhardt made changes - 2020-05-20 08:45

Fix Version/s		N/A [ 23617 ]
Fix Version/s	10.4 [ 23604 ]
Fix Version/s	10.3 [ 23605 ]
Fix Version/s	10.5 [ 23608 ]

Geoff Montee (Inactive) made changes - 2020-05-22 03:31

Link

This issue relates to MENT-805 [ MENT-805 ]

Geoff Montee (Inactive) made changes - 2020-05-27 00:14

Link

This issue is blocked by ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Geoff Montee (Inactive) made changes - 2020-05-28 00:18

Link

This issue relates to MENT-812 [ MENT-812 ]

Christine Lieu (Inactive) made changes - 2020-06-09 06:09

Link

This issue relates to CLX-300 [ CLX-300 ]

Geoff Montee (Inactive) made changes - 2020-06-09 16:18

Link

This issue is duplicated by MENT-813 [ MENT-813 ]

Ralf Gebhardt made changes - 2020-09-24 13:25

Link

This issue includes MENT-813 [ MENT-813 ]

Ralf Gebhardt made changes - 2020-09-24 13:26

Fix Version/s		10.6 [ 24027 ]
Fix Version/s	N/A [ 23617 ]

Ralf Gebhardt made changes - 2020-09-24 13:26

Assignee

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Kentoku Shiba [ kentoku ]

Ralf Gebhardt made changes - 2020-11-27 08:08

Link

This issue is blocked by ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Ralf Gebhardt made changes - 2020-11-27 08:09

Link

This issue relates to ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Ralf Gebhardt made changes - 2021-02-17 12:42

Fix Version/s

10.6 [ 24027 ]

Julien Fritsch made changes - 2021-02-22 09:36

Fix Version/s

10.7 [ 25034 ]

Sergei Golubchik made changes - 2021-10-05 20:02

Link

This issue is blocked by ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Sergei Golubchik made changes - 2021-10-05 20:02

Link

This issue is duplicated by MENT-813 [ MENT-813 ]

Sergei Golubchik made changes - 2021-10-05 20:02

Link

This issue relates to ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Ralf Gebhardt made changes - 2021-10-08 14:55

Fix Version/s

10.7 [ 25034 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-10-27 16:00

Assignee

Kentoku Shiba [ kentoku ]

Nayuta Yanagisawa [ JIRAUSER47117 ]

Sergei Golubchik made changes - 2021-11-02 12:54

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2021-11-18 14:22

Fix Version/s

10.8 [ 26501 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-11-19 07:50

Status

Open [ 1 ]

In Progress [ 3 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 06:19

Affects Version/s		10.8 [ 26501 ]
Affects Version/s	10.4 [ 23604 ]
Affects Version/s	10.3 [ 23605 ]
Affects Version/s	10.5 [ 23608 ]

Ralf Gebhardt made changes - 2021-11-22 07:59

Component/s		Storage Engine - Spider [ 10132 ]
Component/s	Storage Engine - Spider [ 14994 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.8 [ 26501 ]
Key	~~MENT-763~~	~~MDEV-27106~~
Affects Version/s	10.8 [ 26501 ]
Project	MariaDB Enterprise [ 11500 ]	MariaDB Server [ 10000 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 12:43

Summary

Implement proper table options for SPIDER

Spider: specify connection to data node by engine-defined attributes

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 13:50

Description

With Spider, you currently have to set its options in the {{COMMENT}} field for the Spider table. For example:

{code:sql}
CREATE TABLE spider_hq_sales.invoices (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         customer_id INT,
         branch_id INT,
         invoice_date DATETIME(6),
         invoice_total DECIMAL(13, 2),
         payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD')
      ) ENGINE=Spider
      COMMENT='server "hq_server", table "invoices"';
{code}

However, I think that it would be more user-friendly if Spider supported proper table options. For example, the above statement could be transformed into something like this:

{code:sql}
CREATE TABLE spider_hq_sales.invoices (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         customer_id INT,
         branch_id INT,
         invoice_date DATETIME(6),
         invoice_total DECIMAL(13, 2),
         payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD')
) ENGINE=Spider
REMOTE_SERVER="hq_server"
REMOTE_TABLE="invoices";
{code}

It would at least be nice to transform the most common options into table options, such as:

* server - This is the server name defined in the CREATE SERVER statement.
* table - The remote table to query.
* ssl_ca - The path to the TLS certificate authority chain.
* ssl_capath - The path to the TLS certificate authority chain.
* ssl_cert - The path to the TLS client certificate.
* ssl_key - The path to the TLS private key.
* ssl_cipher - The path to the TLS certificate authority chain.
* wrapper - The foreign data wrapper should be mariadb or mysql.
* host - The hostname or IP address to use when connecting to the remote server. This option is mututally exclusive with socket.
* port - The port to use when connecting to the remote server. This option is mututally exclusive with socket.
* socket - The path to the Unix socket file to use when connecting to the remote server. This option is mututally exclusive with user and port.
* user - The username to use when connecting to the remote server.
* password - The password to use when connecting to the remote server.
* database - The database to select when connecting to the remote server.

In addition to these common ones, Spider also has a lot of less common ones. It may be a good idea to transform some others into proper table options as well. You can view the full list in the source code here:

https://github.com/MariaDB/server/blob/mariadb-10.4.13/storage/spider/spd_table.cc#L2211

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine attribute || DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that it should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:00

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine attribute || DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that it should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that it should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:00

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that it should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:16

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_WRAPPER|wrapper|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_WRAPPER="mysql" REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:32

Link

This issue includes MENT-813 [ MENT-813 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:32

Link

This issue relates to MENT-813 [ MENT-813 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:35

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes (see MENT-762).

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-22 14:35

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes (see MENT-762).

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-23 03:25

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce yet another way to specify remote data nodes. We introduce engine-defined attributes that correspond to a subset of the existing DNS parameters. https://mariadb.com/kb/en/spider-table-system-variables/

We do not cover all the existing parameters because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 06:46

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 08:04

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_HOST|host|
|REMOTE_PORT|port|
|REMOTE_USER|user|
|REMMOTE_PASSWORD|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 08:05

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|REMOTE_SERVER| srv|
|REMOTE_DATABASE| database|
|REMOTE_TABLE|tbl|
|REMOTE_HOST|host|
|REMOTE_PORT|port|
|REMOTE_USER|user|
|REMMOTE_PASSWORD|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE|}}tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 08:05

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE|}}tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 08:05

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-25 09:10

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|
|{{REMOTE_HOST}}|host|
|{{REMOTE_PORT}}|port|
|{{REMOTE_USER}}|user|
|{{REMOTE_PASSWORD}}|password|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-26 14:32

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TBD:

* (x) What happens if, for example, both {{COMMENT="tbl: 't1'"}} and {{REMOTE_TABLE="t1"}} are specified.

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

If, for example, both {COMMENT='"table"

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-26 14:33

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

If, for example, both {COMMENT='"table"

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-26 14:37

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-26 14:56

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing parameters|https://mariadb.com/kb/en/spider-table-system-variables/ ] because only a few of the parameters seem to actually be used. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing DNS parameters|https://mariadb.com/kb/en/spider-server-system-variables/] because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 04:59

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing DNS parameters|https://mariadb.com/kb/en/spider-server-system-variables/] because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing DSN parameters|https://mariadb.com/kb/en/spider-server-system-variables/] because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 05:03

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause. However, writing every specification in a single connection string is not very user-friendly.

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the [existing DSN parameters|https://mariadb.com/kb/en/spider-server-system-variables/] because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 08:36

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (x) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 09:27

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, {{REMOTE_TABLE}} takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

The corresponding options cannot be specified by COMMENT and engine-defined options at the same time. If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 09:41

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

The corresponding options cannot be specified by COMMENT and engine-defined options at the same time. If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, COMMENT takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 09:43

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, COMMENT takes precedence.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

Nayuta Yanagisawa (Inactive) made changes - 2021-11-29 16:29

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (x) Make Spider to use the attributes;

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (x) Write comprehensive test cases;
* (x) Refactor entire patch.

Nayuta Yanagisawa (Inactive) made changes - 2021-11-30 10:14

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (x) Write comprehensive test cases;
* (x) Refactor entire patch.

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (/) Write comprehensive test cases;
* (/) Refactor entire patch.

Nayuta Yanagisawa (Inactive) added a comment - 2021-11-30 10:42

Please review. The patch depends on one for ~~MDEV-5271~~.
https://github.com/MariaDB/server/commit/ae9e72ca06d95947e44ef0801c94fbac6882b38e

Nayuta Yanagisawa (Inactive) added a comment - 2021-11-30 10:42 Please review. The patch depends on one for MDEV-5271 . https://github.com/MariaDB/server/commit/ae9e72ca06d95947e44ef0801c94fbac6882b38e

Nayuta Yanagisawa (Inactive) made changes - 2021-11-30 10:42

Assignee	Nayuta Yanagisawa [ JIRAUSER47117 ]	Alexey Botchkov [ holyfoot ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-02 06:32

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should recommend the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (/) Write comprehensive test cases;
* (/) Refactor entire patch.

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should encourage the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (/) Write comprehensive test cases;
* (/) Refactor entire patch.

Nayuta Yanagisawa (Inactive) made changes - 2021-12-05 08:10

Link

This issue relates to ~~MDEV-27169~~ [ ~~MDEV-27169~~ ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 108594 ]

MariaDB v4 [ 131777 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-09 12:24

Link

This issue relates to ~~MDEV-27211~~ [ ~~MDEV-27211~~ ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-10 06:01

Link

This issue is blocked by ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-10 06:01

Link

This issue relates to ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Alexey Botchkov added a comment - 2021-12-10 09:29

see minor comments to the patch.

Alexey Botchkov added a comment - 2021-12-10 09:29 see minor comments to the patch.

Alexey Botchkov made changes - 2021-12-10 09:29

Assignee	Alexey Botchkov [ holyfoot ]	Nayuta Yanagisawa [ JIRAUSER47117 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-10 11:30

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-10 13:30

holyfoot Thank you for your review. I fixed the patch according to your comments. Please check it again. https://github.com/MariaDB/server/commit/384f9d34ff29fe81de234c48516ccf257b9b10f5

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-10 13:30 holyfoot Thank you for your review. I fixed the patch according to your comments. Please check it again. https://github.com/MariaDB/server/commit/384f9d34ff29fe81de234c48516ccf257b9b10f5

Nayuta Yanagisawa (Inactive) made changes - 2021-12-10 13:30

Assignee	Nayuta Yanagisawa [ JIRAUSER47117 ]	Alexey Botchkov [ holyfoot ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexey Botchkov added a comment - 2021-12-13 14:10

Ok to push.

Alexey Botchkov added a comment - 2021-12-13 14:10 Ok to push.

Alexey Botchkov made changes - 2021-12-13 14:10

Assignee	Alexey Botchkov [ holyfoot ]	Nayuta Yanagisawa [ JIRAUSER47117 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-12-13 15:57

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2021-12-13 15:57

Assignee

Nayuta Yanagisawa [ JIRAUSER47117 ]

Sergei Golubchik made changes - 2021-12-15 11:10

Assignee

Roel Van de Paar [ roel ]

Nayuta Yanagisawa (Inactive) made changes - 2021-12-21 07:13

Description

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should encourage the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (/) Write comprehensive test cases;
* (/) Refactor entire patch.

The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

* https://mariadb.com/kb/en/spider-table-system-variables/
* https://mariadb.com/kb/en/spider-server-system-variables/ (DSN parameter)

We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

I believe that the following attributes are enough:

|| engine-defined attribute || corresponding DNS parameter ||
|{{REMOTE_SERVER}}| srv|
|{{REMOTE_DATABASE}}| database|
|{{REMOTE_TABLE}}|tbl|

Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should encourage the use of the CREATE SERVER statement, and not introduce attributes such as {{REMOTE_USER}} and {{REMOTE_PASSWORD}}.

SSL-related parameters might be essential but I believe that they should be covered by {{CREATE SERVER}} statement rather than per-table or per-partition attributes.

If, for example, both {{COMMENT='table "t1"'}} and {{REMOTE_TABLE="t2"}} are specified for a single table or a single partition, the Spider returns an error.

Example:

{code:sql}
CREATE TABLE `users` (
    `id` INT,
    `name` VARCHAR(255)
) ENGINE=SPIDER REMOTE_TABLE="users"
PARTITION BY HASH(id)
(
    PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
    PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
    PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
);
{code}

TODO:

* (/) Introduce engine-defined attributes described above;
* (/) Make Spider to use the attributes;
* (/) Write comprehensive test cases;
* (/) Refactor entire patch.

Roel Van de Paar made changes - 2022-01-01 12:59

Link

This issue is blocked by MDEV-27400 [ MDEV-27400 ]

Roel Van de Paar added a comment - 2022-01-01 13:00 - edited

Logged MDEV-27400 Spider attempts localhost TCP/IP connection, even when none is specified.

Roel Van de Paar added a comment - 2022-01-01 13:00 - edited Logged MDEV-27400 Spider attempts localhost TCP/IP connection, even when none is specified.

Roel Van de Paar made changes - 2022-01-01 13:23

Link

This issue relates to MDEV-27400 [ MDEV-27400 ]

Roel Van de Paar made changes - 2022-01-01 13:23

Link

This issue is blocked by MDEV-27400 [ MDEV-27400 ]

Roel Van de Paar made changes - 2022-01-16 22:18

Link

This issue causes ~~MDEV-27521~~ [ ~~MDEV-27521~~ ]

Roel Van de Paar added a comment - 2022-01-16 22:19

Logged ~~MDEV-27521~~ SIGSEGV in spider_parse_connect_info in ~~MDEV-27106~~ branch

Roel Van de Paar added a comment - 2022-01-16 22:19 Logged MDEV-27521 SIGSEGV in spider_parse_connect_info in MDEV-27106 branch

Roel Van de Paar made changes - 2022-01-21 09:34

Link

This issue relates to MDEV-27564 [ MDEV-27564 ]

Sergei Golubchik made changes - 2022-01-21 13:29

Link

This issue is part of ~~MDEV-27373~~ [ ~~MDEV-27373~~ ]

Roel Van de Paar added a comment - 2022-01-22 04:56

Confirming this comment, it is interesting to note that Spider already supported ~~MDEV-5271~~-like functionality (i.e. per partition engine-defined attributes) using the comment field. iow, in the comment field one could specify per-partition options even before ~~MDEV-5271~~ was implemented.

Roel Van de Paar added a comment - 2022-01-22 04:56 Confirming this comment , it is interesting to note that Spider already supported MDEV-5271 -like functionality (i.e. per partition engine-defined attributes) using the comment field. iow, in the comment field one could specify per-partition options even before MDEV-5271 was implemented.

Roel Van de Paar added a comment - 2022-01-22 04:58

As the COMMENT field functionality was not changed in any way, and as any new SQL syntax is 10.8-only, I did not do any upgrade testing for this issue. nayuta-yanagisawa and serg If you think any should be done, please let me know your thoughts.

Roel Van de Paar added a comment - 2022-01-22 04:58 As the COMMENT field functionality was not changed in any way, and as any new SQL syntax is 10.8-only, I did not do any upgrade testing for this issue. nayuta-yanagisawa and serg If you think any should be done, please let me know your thoughts.

Roel Van de Paar added a comment - 2022-01-22 06:42

OK to push. However, I am still running a generic crash test against the feature branch (including the ~~MDEV-27521~~ patch). Results from that will be available around next week Wednesday.

Roel Van de Paar added a comment - 2022-01-22 06:42 OK to push. However, I am still running a generic crash test against the feature branch (including the MDEV-27521 patch). Results from that will be available around next week Wednesday.

Roel Van de Paar made changes - 2022-01-22 06:44

Assignee	Roel Van de Paar [ roel ]	Sergei Golubchik [ serg ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Roel Van de Paar made changes - 2022-01-22 07:42

Link

This issue causes ~~MDEV-27575~~ [ ~~MDEV-27575~~ ]

Roel Van de Paar added a comment - 2022-01-22 07:43 - edited

From the same run, logged ~~MDEV-27575~~ SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global)

Roel Van de Paar added a comment - 2022-01-22 07:43 - edited From the same run, logged MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global)

Sergei Golubchik made changes - 2022-01-22 11:54

Assignee

Sergei Golubchik [ serg ]

Nayuta Yanagisawa [ JIRAUSER47117 ]

Sergei Golubchik made changes - 2022-01-22 11:54

Priority

Critical [ 2 ]

Blocker [ 1 ]

Nayuta Yanagisawa (Inactive) made changes - 2022-01-24 10:51

Fix Version/s		10.8.1 [ 26815 ]
Fix Version/s	10.8 [ 26121 ]
Resolution		Done [ 10200 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Roel Van de Paar made changes - 2022-01-29 05:15

Link

This issue causes ~~MDEV-27676~~ [ ~~MDEV-27676~~ ]

Roel Van de Paar added a comment - 2022-01-29 05:16 - edited

Logged ~~MDEV-27676~~ Assertion `str.alloced_length() >= str.length() + data_len' failed in spider_string::q_append

Roel Van de Paar added a comment - 2022-01-29 05:16 - edited Logged MDEV-27676 Assertion `str.alloced_length() >= str.length() + data_len' failed in spider_string::q_append

Nayuta Yanagisawa (Inactive) made changes - 2022-03-10 05:36

Link

This issue causes ~~MDEV-27575~~ [ ~~MDEV-27575~~ ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-15 16:12

Link

This issue relates to ~~MDEV-28856~~ [ ~~MDEV-28856~~ ]

Ralf Gebhardt made changes - 2023-03-21 10:54

Labels

Preview_10.8

Yuchen Pei added a comment - 2023-04-26 23:35 - edited

Hi greenman, how much work do you think is needed to document the nice feature introduced in this ticket in the KB?

Yuchen Pei added a comment - 2023-04-26 23:35 - edited Hi greenman , how much work do you think is needed to document the nice feature introduced in this ticket in the KB?

Yuchen Pei made changes - 2023-04-28 01:40

Link

This issue relates to ~~MDEV-28861~~ [ ~~MDEV-28861~~ ]

Ralf Gebhardt made changes - 2023-04-28 06:11

Link

This issue relates to MDEV-31146 [ MDEV-31146 ]

Yuchen Pei added a comment - 2023-06-28 03:33

> Hi Ian Gilfillan, how much work do you think is needed to document the nice feature introduced in this ticket in the KB?

I added to the kb two weeks ago: https://mariadb.com/kb/en/spider-storage-engine-overview/#basic-usage

Yuchen Pei added a comment - 2023-06-28 03:33 > Hi Ian Gilfillan, how much work do you think is needed to document the nice feature introduced in this ticket in the KB? I added to the kb two weeks ago: https://mariadb.com/kb/en/spider-storage-engine-overview/#basic-usage

MariaDB Server

Spider: specify connection to data node by engine-defined attributes

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration