Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
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
-
-
- Closed
-
- causes
-
MDEV-35136 Sporadic hang on shutdown after using [SHOW] CREATE SERVER syntax
-
- Open
-
- includes
-
-
- Closed
-
- is duplicated by
-
MDEV-19559 Provide SHOW commands for SERVERs
-
- Closed
-
- relates to
-
-
- Open
-
Activity
It would be useful for humans, but scripts would have to parse the output SQL. Or the can query the `servers` table in the `mysql` database, which is currently the only way. But I believe that this is a bad practice.
I'd suggest to add a table to the `information_schema`. I'm not filing a feature request because, if you decide to do it, I'm not sure if that would be part of this task.
A PoC:
5c946847812 bb-11.4-mdev-15696 MDEV-15696 [poc] SHOW CREATE SERVER
|
It is based on the change for MDEV-34272*, which simplifies the implementation by just going through the options. Otherwise we'd have to implement it twice... The caveat with this approach is that a mysqldump test fails at the commit in MDEV-34272, but if we could push this one immediately after that one then it will be ok.
Still need to update and test mysqldump, at least...
*: now splitted to MDEV-34716
Thanks for the review. I addressed the comments. However, multiple mysql_upgrade tests fail in mtr internal checks, like so:
--- /home/ycp/source/mariadb-server/main/build/mysql-test/var/tmp/check-mysqld_1.result 2024-09-04 16:43:10.518335709 +1000
|
+++ /home/ycp/source/mariadb-server/main/build/mysql-test/var/tmp/check-mysqld_1.reject 2024-09-04 16:43:12.382321402 +1000
|
@@ -944,7 +944,7 @@
|
mysql.servers Socket 7 '' NO char 108 324 NULL NULL utf8mb3 utf8mb3_general_ci char(108)
|
mysql.servers Wrapper 8 '' NO char 64 192 NULL NULL utf8mb3 utf8mb3_general_ci char(64)
|
mysql.servers Owner 9 '' NO varchar 512 1536 NULL NULL utf8mb3 utf8mb3_general_ci varchar(512)
|
-mysql.servers Options 10 '{}' NO longtext 4294967295 4294967295 NULL NULL utf8mb4 utf8mb4_bin longtext
|
+mysql.servers Options 10 '{}' NO longtext 4294967295 4294967295 NULL NULL utf8mb3 utf8mb3_general_ci longtext
|
I did some investigation and opened MDEV-34872 and temporarily block this issue with that bug while we try to fix it.
The behaviour of JSON columns reported in MDEV-34872 is expected, according to serg and bar there. So I am doing an alternative fix to the mtr internal check failure by specifying explicitly utf8mb3 for the newly added Options column.
Hi holyfoot, ptal at this extra commit thanks:
c16e70d56e3 bb-11.7-mdev-15696 MDEV-34716 [to-squash] Fix collate of the newly added Options column
|
One approved, I will squash it into the main MDEV-34716 patch that introduces the new column.
I propose a different approach.
The core of the problem is in this statement in scripts/mariadb_system_tables_fix.sql:
ALTER TABLE servers ENGINE=Aria transactional=1, |
CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; |
If the table already has the column Options, then its collation will change from utf8mb4_bin to utf8mb3_general_ci.
I suggest to keep the collation as utf8mb4_bin.
In scripts/mariadb_system_tables.sql you can create the JSON column without a collate clause:
CREATE TABLE IF NOT EXISTS servers ( |
...
|
Options JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Options)) |
...
|
) engine=Aria transactional=1
|
CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci comment='MySQL Foreign Servers table'; |
Like you did before this additional fix.
So it will create the JSON column with utf8mb4_bin.
Then, put the following into scripts/mariadb_system_tables_fix.sql:
ALTER TABLE servers |
ADD Options JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Options)); |
ALTER TABLE servers |
MODIFY Options JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Options)); |
The idea is:
- The first ALTER will create the column Options if it does not exist.
- The second column will change its collation back to utf8mb4_bin, if it existed.
Thanks bar for the cleaner solution. I've amended the top commit accordingly:
d6b4b7fd752 * upstream/bb-11.7-mdev-15696-before-squash MDEV-34716 [to-squash] Fix collate of the newly added Options column
|
be688ecb9fc * MDEV-34716 [to-squash] 11.6 fixup caused by MDEV-31340
|
b497bf16a1f * MDEV-15696 Implement SHOW CREATE SERVER
|
3597b800800 * MDEV-34716 Allow arbitrary options in CREATE SERVER
|
Given the simplicity of this commit I don't think another round of review is needed. So I squashed the two top commits into the fourth commit. Roel: Can you test the following please? Thanks.
f19446cc2ca upstream/bb-11.7-mdev-15696 MDEV-15696 Implement SHOW CREATE SERVER
|
817dee3ab27 MDEV-34716 Allow arbitrary options in CREATE SERVER
|
8074121f741 MDEV-34716 Fix mysql.servers socket max length too short
|
2e5b944f71d MDEV-34716 spider: some trivial cleanups and documentation
|
a8e3b500616 json_get_object_nkey() function implemented.
|
ycp Thank you for always delivering clean ready-to-test (upmerged) branches
https://github.com/MariaDB/server/compare/main...bb-11.7-mdev-15696
Testing underway.
UPDATE: testing shows number of issues which require debugging, but overall the branch looks quite good. With MDEV-31466 out of the way, this one is up next.
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?
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.
|
This task itself looks it could be useful and easy to implement.
sanja Is SHOW CREATE FOO part of the sql standard? I know for example SHOW CREATE TABLE it is not supported in postgresql.