[MDEV-15696] Implement SHOW CREATE SERVER - Jira

Details

Type: New Feature
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 11.7.1
Component/s: Storage Engine - Federated, Storage Engine - Spider
Labels:
- Preview_11.7
- create_server
- federated
- federatedx
- show
- spider

Description

CREATE SERVER is a valid statement in MariaDB:

https://mariadb.com/kb/en/library/create-server/

However, it seems like SHOW CREATE SERVER isn't implemented yet. Maybe it would be worth implementing?

MariaDB [(none)]> CREATE SERVER srv1

    ->   FOREIGN DATA WRAPPER mysql

    -> OPTIONS(

    ->   HOST '172.30.0.58',

    ->   DATABASE 'db1',

    ->   USER 'maxscale',

    ->   PASSWORD 'password'

    -> );

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE SERVER srv1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SERVER srv1' at line 1

MariaDB [(none)]> SELECT * FROM mysql.servers\G

*************************** 1. row ***************************

Server_name: srv1

       Host: 172.30.0.58

         Db: db1

   Username: maxscale

   Password: password

       Port: 3306

     Socket:

    Wrapper: mysql

      Owner:

1 row in set (0.00 sec)

Attachments

Issue Links

blocks

MDEV-34716 Allow arbitrary options in CREATE SERVER

Closed

causes

MDEV-35136 Sporadic hang on shutdown after using [SHOW] CREATE SERVER syntax

Open

includes

MDEV-34716 Allow arbitrary options in CREATE SERVER

Closed

is duplicated by

MDEV-19559 Provide SHOW commands for SERVERs

Closed

relates to

MDEV-33196 [ERROR] Got error 12524 when reading table './test/t'

Open

Activity

Ascending order - Click to sort in descending order

View 10 older comments

Roel Van de Paar added a comment - 2024-09-28 03:04 - edited

ycp Before the change we had:

CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized)
11.7.0-opt>CREATE SERVER svr FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1', DATABASE 'test', USER 'usr', PASSWORD 'pwd');
Query OK, 0 rows affected (0.001 sec)

11.7.0-opt>SELECT * FROM mysql.servers\G
************************* 1. row *************************
Server_name: svr
Host: 127.0.0.1
Db: test
Username: usr
Password: pwd
Port: 3306
Socket:
Wrapper: mysql
Owner:
1 row in set (0.000 sec)

After we see:

bb-11.7-mdev-15696 CS 11.7.0 f19446cc2cacfb7c78ce3c198042b86b4754321e (Optimized)
11.7.0-opt>CREATE SERVER svr FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1', DATABASE 'test', USER 'usr', PASSWORD 'pwd');
Query OK, 0 rows affected (0.001 sec)

11.7.0-opt>SELECT * FROM mysql.servers\G
************************* 1. row *************************
Server_name: svr
Host: 127.0.0.1
Db: test
Username: usr
Password: pwd
Port: 3306
Socket:
Wrapper: mysql
Owner:
Options: {"HOST": "127.0.0.1", "DATABASE": "test", "USER": "usr", "PASSWORD": "pwd"}
1 row in set (0.000 sec)

It looks like the values are duplicated into options? Is it necessary and can we avoid it?

Roel Van de Paar added a comment - 2024-09-28 03:04 - edited ycp Before the change we had: CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized) 11.7.0-opt>CREATE SERVER svr FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1', DATABASE 'test', USER 'usr', PASSWORD 'pwd'); Query OK, 0 rows affected (0.001 sec) 11.7.0-opt>SELECT * FROM mysql.servers\G *************************** 1. row *************************** Server_name: svr Host: 127.0.0.1 Db: test Username: usr Password: pwd Port: 3306 Socket: Wrapper: mysql Owner: 1 row in set (0.000 sec) After we see: bb-11.7-mdev-15696 CS 11.7.0 f19446cc2cacfb7c78ce3c198042b86b4754321e (Optimized) 11.7.0-opt>CREATE SERVER svr FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1', DATABASE 'test', USER 'usr', PASSWORD 'pwd'); Query OK, 0 rows affected (0.001 sec) 11.7.0-opt>SELECT * FROM mysql.servers\G *************************** 1. row *************************** Server_name: svr Host: 127.0.0.1 Db: test Username: usr Password: pwd Port: 3306 Socket: Wrapper: mysql Owner: Options: {"HOST": "127.0.0.1", "DATABASE": "test", "USER": "usr", "PASSWORD": "pwd"} 1 row in set (0.000 sec) It looks like the values are duplicated into options? Is it necessary and can we avoid it?

Yuchen Pei added a comment - 2024-09-29 23:36

Roel: it is a price to pay for backward compatibility

Yuchen Pei added a comment - 2024-09-29 23:36 Roel : it is a price to pay for backward compatibility

Roel Van de Paar added a comment - 2024-09-30 00:36

ycp ack, thank you

Roel Van de Paar added a comment - 2024-09-30 00:36 ycp ack, thank you

Roel Van de Paar added a comment - 2024-10-14 21:39

OK to push

Roel Van de Paar added a comment - 2024-10-14 21:39 OK to push

Yuchen Pei added a comment - 2024-10-15 01:06

Thanks for the testing Roel. Pushed the following to 11.7/main

35cebfdc513 upstream/bb-11.7-mdev-15696 upstream/main MDEV-15696 Implement SHOW CREATE SERVER

d2eba35653b MDEV-34716 Allow arbitrary options in CREATE SERVER

2345407b8c2 MDEV-34716 Fix mysql.servers socket max length too short

84df8d72751 MDEV-34716 spider: some trivial cleanups and documentation

13cd8ad8db3 json_get_object_nkey() function implemented.

Yuchen Pei added a comment - 2024-10-15 01:06 Thanks for the testing Roel . Pushed the following to 11.7/main 35cebfdc513 upstream/bb-11.7-mdev-15696 upstream/main MDEV-15696 Implement SHOW CREATE SERVER d2eba35653b MDEV-34716 Allow arbitrary options in CREATE SERVER 2345407b8c2 MDEV-34716 Fix mysql.servers socket max length too short 84df8d72751 MDEV-34716 spider: some trivial cleanups and documentation 13cd8ad8db3 json_get_object_nkey() function implemented.

People

Assignee:: Yuchen Pei

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2018-03-27 22:12

Updated:: 2025-03-13 11:40

Resolved:: 2024-10-15 01:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server