Can this be assigned to Olivier Bertrand?
We have exchanged emails and he believes this is a good thing to add.

I would expect this to need some changes in the server before the engine would be able to do it.

Any hope of this getting assigned?

Any chance of getting this in 10.3? I imagine 10.4 is more than a year away, right?

There's always a chance. But, to be honest, it's very slim — 10.3 is quite overbooked with features already. Features that are in progress and were in progress for months. I'll change the FixVersion to 10.3, so that this MDEV will be at least considered for 10.3. But it probably won't make it.

I see this was changed to 10.3. Great! Please let me know if you need any additional detail.

Can this still be considered for 10.3?

Hi,

This is becoming a big issue for us. We really need to be able to separate the connection URL, user, password, and options from the CONNECT table definition itself. We have a set of CONNECT tables that are standard across customers, but each of course has its own passwords, urls, etc. Right now we have to create everything custom for customers which is a royal pain.

It doesn't matter if it uses CREATE SERVER or some other hack specific to CONNECT, but the connect definition needs to refer to a config name of some sort that contains the JDBC URL, options, user, pass, etc. If a new system table that supports this for connect is easier, then that is great too.

Thank you!

bertrandop, if you'd want to support CREATE SERVER, you could see how it's done in ha_federated.cc. Basically, you need to call get_server_by_name() and it'll provide you all the connection info by the server name.

CONNECT DOES support federated servers for the JDBC table type. Maybe this could be extended to ODBC table types if required.

It is documented in https://mariadb.com/kb/en/library/connect-jdbc-table-type-accessing-tables-from-another-dbms/ paragraph Using a Federated Server

I guess rdyas knows it and the true target of this MDEV is to introduce new options such as URL in the Federated Server definition. This does not depend on me and this MDEV should be re-assigned to serg.

Hi Guys,

Robert here. There are a couple of issues at play:

1. Per https://mariadb.com/kb/en/library/create-server/ it requires the SUPER priv (notes those docs don't say it supports CONNECT). That makes it not very workable in our situation as nobody has it. But maybe we could consider giving the SUPER priv. WE would really like to have a separate SERVER priv so if the user has it they can create or drop a server. This seems like a better security arrangement. Also permission being GLOBAL rather than DATABASE level has issues for us in a multi-tenant environment [this latter point is becoming increasingly important as integration becomes more common]. In an ideal world we would be able to GRANT SERVER ON dbname.* TO user123@% so that user123 can only see/create/drop servers on a specific database.

2. Maybe I misunderstood before. If HOST is the URL and USER and PASSWORD are specified, all connect currently does (when I tested) is COPY those values when the CREATE TABLE is issued, correct? Therefore if I go back and change a password i have to CREATE TABLE again for it to see it. Did I misunderstand? The idea is that I could change the server definition and NOT have to do CREATE TABLE again for connect to see the new password.[there might be 30 tables from 1 server with the same password]. Same applies to connection URL. See point 3 for CONNECT specific options.

3. We need to be able to specify a series of CONNECT specific options at the server level. Things like memory=3;quote=1;wrapper=ApacheWrapper; that are NOT part of the JDBC connection URL but are CONNECT specific. Also, in an ideal world, these would be read dynamically from the SERVER definition unless another value was specifically specified at CREATE TABLE time. For example, the SERVER definition might have OPTIONS='Memory=2;' but a specific CREATE TABLE might override with Memory=1.

So ideally we would a) introduce URL and OPTIONS key words to the CREATE SERVER syntax (reusing HOST for URL sort of works but is ugly, and there is no way to specify OPTIONS ), b) change the way permissions are granted, and c) have CONNECT use the values (url, user, pass, options) dynamically unless overridden in the CREATE TABLE statement.

Please let me know if any of that is stupid and especially if I misunderstood or mis-tested and CONNECT does see new HOST and PASS from server definition dynamically and not just at CREATE TABLE time.

Also, I thought some real world examples of unusual JDBC URL connection strings (that are critical and in production) might be helpful to see... the point is you can't "mangle" the connection url reliably... it needs to be passed direct as specified.

jdbc:rest:DataModel=Relational;URI=/opt/parasql/jdbc/xml/schema/demo/people.txt;Format=XML;XPath="/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;/root/people/vehicles/insurance;";Location=/opt/parasql/jdbc/xml/schema/demo;GenerateSchemaFiles=OnStart;

jdbc:json:Location=/var/opt/parasql/federated/json/fbif;CustomUrlParams="key=bd4ff21a-1239-42bf-aaef-39sfsdf57dd35";Logfile="/var/log/mysql/fbif.log";Verbosity=3;

In the following, for example, the "Password" param MUST be specified in the URL param:

jdbc:shopify:AppId=0b12345123455723881fdc7;Password=24123451234512345badfasa5;ShopUrl=https://kiwi-diamond.myshopify.com;Logfile="/var/log/mysql/shopify.log";Verbosity=4;

Your points 2 and 3: for the user and password information, they are taken from the server but overridden if they are specified in the create table statement. So you can have values used by most tables but for a few specifying them when creating.
Also, the use of a server is specified when creating the table but getting information from it is done when using the table. Therefore, when modifying information in a server (such as password) it is not required to re-create the tables using it.

About specific CONNECT options: It seems difficult and un-natural to ask MariaDB to include anything concerning eventual existing or future storage engine in the main code. However, what could be done is to provide a general OPTION_LIST option that could be used by storage engines the way CONNECT does in CREATE TABLE statements.

Checking into my code I found that currently the user and password specified in the server have precedence over the ones specified in the CREATE TABLE statement. Sorry about that. I have fixed it and it will be only effective in new MariaDB versions. Anyway, when changing them in the server you do not have to re-create the tables.

If they are specified both in the URL and in the server or in the create table I think this will be remote source dependant.

Can you verify that HOST is taken from the server dynamically for the JDBC URL in the same way user and password now are? If HOST is handed off directly to CONNECT for the JDBC URL, then that is good enough and no need to add URL param (so long as it is not rewritten by connect in any way).

Agree having an OPTION_LIST parameter for SERVER is ideal if those options can be handed off directly to CONNECT. If that is going to take time, could we reuse an existing SERVER option like database or owner to pass in the options list to connect?

Yes the whole URL is retrievd dynamically from the server, including the HOST information.

Currently, option such as database or owner cannot be used as an option list.

Just to be sure I understand, if I create a SERVER with a HOST as follows [note the strange url format]:

jdbc:shopify:AppId=0b12345123455723881fdc7;Password=24123451234512345badfasa5;ShopUrl=https://kiwi-diamond.myshopify.com;Logfile="/var/log/mysql/shopify.log";Verbosity=4;

them create a CONNECT table using that SERVER, then change the HOST variable for the server, the next SELECT from that CONNECT table will use the new HOST variable?
#1 Please confirm that my understanding is correct or where I'm wrong.
#2 Are any special permissions needed to SELECT from a CONNECT table that is defined with a SERVER? [just confirming super or some other priv isn't required - just normal select/insert/update/delete privs]

If correct, the most important aspect of this MDEV is being able to GRANT SERVER on dbname.* TO user so that a) super isn't required and b) db user 1 can't see db user 2's servers.

Per the documentation (here https://mariadb.com/kb/en/library/create-server/) the following won't work (and I've tried and verified that it doesn't work) because the HOST is silently truncated to 64 chars. First, it should generate at least a warning, second, for host to be used as a kluge for URL it needs to be far longer than 64 chars as shown in the potential usage example below:

CREATE OR REPLACE SERVER 'S12345678901234' FOREIGN DATA WRAPPER 'FDW12345678901234'

    OPTIONS (HOST 'jdbc:json:DataModel=Relational;URI=/opt/parasql/jdbc/json/schema/demo/people.json;JSONPath="$.people;$.people.vehicles;$.people.vehicles.maintenance;$.people.vehicles.insurance;";Location=/opt/parasql/jdbc/json/schema/demo;', USER 'username', PASSWORD 'password')

The current limitations make it essentially USELESS, at least for specifying real world JDBC URLs. I would suggest adding a proper URL or CONNECTION_URL parameter to SERVER (with a length of 2,000 chars or longer as stated in the original request), and OPTIONS_LIST if that is a standard MariaDB create table parameter (since a paramater such as OPTIONS_LIST is being specified already within an OPTIONS clause I would suggest an alternate name for clarity, such as MISC or OTHER [as per the original request] and make sure it is of sufficient length to handle future requirements such as passing PEM files inline).

#1: Correct
#2: none coming from CONNECT

Too bad about this limitation to 64 characters. It is not done by CONNECT and probably comes from host names actually being limited to 64 characters.

Thus this MDEV should be re-assigned to a MariaDB staff member.

Ok, before assigning to a MariaDB staff member, could you check one thing for me?
I tried to check but the shortest JDBC URL I have for testing is > 150 chars long (see below... this is the SHORTEST out of a dozen or so)

jdbc:sqlserver://parasqltest.database.windows.net:1433;database=test6;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

if you create the SERVER as root, then create a new user and only GRANT create table on a specific database to that user, then can that new user create a CONNECT table that uses that SERVER and SELECT from it? [If that doesn't work it would be good to know why so we can wrap it into this MDEV, but if that works, then the most important change from our perspective is adding URL (my preference) or making HOST longer (ugly in my view, but if one can be done sooner than the other, that works for me)... for backward compatibility CONNECT could prefer URL over HOST if URL is specified ]. Also, if the above works, the need for the need for adding SERVER permissions at the database level is greatly reduced [we could use it without that by managing server creating as root, so long as the CONNECT table definer does not need root/super to access the SERVER we created as root]

One more side question to ask: would it be safe for me to change the column length definition of the mysql.servers table for host as a hack until the next version came out? we currently don't use servers for anything.

I don't see how this could not be possible. When creating the table nothing indicate the system that you are refering to a server and when you use the table getting information from the server is done by calling some functions that are not likely to check for grant; how could they know who is calling them?

I don't understand your second question.

clarification of second question:

Would it be a safe hack for me to do until the other changes get made?

ALTER TABLE mysql.servers CHANGE COLUMN Host Host VARCHAR(2000) NOT NULL

Are there internal mariadb buffers probably allocated for char 64 and not not a string?

EDIT: I just went ahead and tested this - and it seems to work great on the surface
server restart ok
backup ok - no errors anyway
...so the new question is: do you think there would be any ugly consequences from this hack?

I am not the one to address this question.

As a work around until something more polished is done, can you easily make it so CONNECT will use the server OWNER column for OPTIONS_LIST settings? Specifically, I would like to merge the variables specified in server OWNER with those options specified in the OPTION_LIST when the create table is issued.

A typical use case would be to specify "Wrapper=ApacheInterface" in the server OWNER and specify "Memory=2" on the create table - but have both be used. That way we can turn on/off the Wrapper=ApacheInterface just by changing the SERVER definition.

Would that be easy to add?

Federated servers, in sql_servers.h, are defined as:

/* structs */

typedef struct st_federated_server

{

  const char *server_name;

  long port;

  size_t server_name_length;

  const char *db, *scheme, *username, *password, *socket, *owner, *host, *sport;

} FOREIGN_SERVER;

Therefore, I don't understand why some of these CONST CHAR variables be limited in size. Also, I don't see OWNER 'column' in them.

To support an option list, I would prefer one item not normally used. What about sport that seems to duplicate the port entry?

*owner is right there... look again... right?
It's after socket and b4 host.

You're right; it does exist and it is currently not used. sport is probably used internally but is not an option of the CREATE SERVER statement. And indeed, the documentation says that all character values are "silently" truncated to 64 characters.

Therefore, it would be ok to use owner as an "OPTION_LIST" for CONNECT. I will see if it is feasible.

Using owner as an "OPTION_LIST" for CONNECT seems feasible but complicated. It is also needed to decide whether these informations had precedence or not on the ones in the CREATE TABLE OPTION_LIST.

By the way, I found a perhaps temporary solution with big URLs and federated servers. It is to define a user variable as the url and to give its name, with the @ character, in the HOST server option:

CREATE SERVER url_test

    FOREIGN DATA WRAPPER whatever

    OPTIONS (HOST '@MY_URL');

SET @`MY_URL`='jdbc:mariadb://localhost:3308/test?user=root&password=whatever&useSSL=false';

CREATE OR REPLACE TABLE connect_testme

ENGINE=CONNECT TABLE_TYPE=JDBC BLOCK_SIZE=500

CONNECTION='url_test/testme';

The url is more than 64 characters long. I tested it and it in a development CONNECT version and it works!

Of course, this is valid only for the client user and session. I don't think one can define global user variables. But note that you can change the url just by resetting the variable, no need to recreate the server and/or the table.

IF a given option is specified in both server OWNER and in the create table option_list, the create table option should prevail. I expect it to be very common to have different options in each list, so in most cases BOTH server OWNER options and create table options will be used (i.e. they will be non-overlapping). For options, the 80 char limit will work for all our intended current use cases, and I can extend the mysql.servers column length if needed until a more permenent solution is released... maybe that can be in a separate mdev from this once the basics are working?

For the host, for now, I'll extend the mysql.servers.host column length to varchar 2000 and submit a new MDEV for either formally making host and owner longer or adding new URL and OPTION_LIST params.

BTW... using @var doesn't seem like a good solution for our use case.

Using OWNER to extend the current OPTION_LIST is very difficult to implement, due to the internal structure of getting options. Firstly named options, then in the OPTION_LIST. It would require to make major changes in the code existing from almost the begining of CONNECT and could make it loose its GA ranking. And this for just a few options concerning only one table type.

However, this could be done easily if restricted to a limited set of some options. You mentionned WRAPPER or MEMORY. Could you make a list of those options that would be allowed to be specified in OWNER?

Only WRAPPER, MEMORY, and DRIVER would need to be specified in OWNER. Most of the time it would just be WRAPPER and DRIVER in OWNER and MEMORY would be specified at the CREATE TABLE options list level. Is that doable?

If MEMORY is only specified in the CREATE TABLE option list that would be perfect. No more need for a pseudo option list in OWNER. Wrapper will be specified in the WRAPPER name of the server and the driver in OWNER:

CREATE SERVER 'oracle' FOREIGN DATA WRAPPER 'ApacheInterface' OPTIONS (

HOST 'jdbc:oracle:thin:@localhost:1521:xe',

DATABASE 'HR',

USER 'scott',

PASSWORD 'tiger',

OWNER 'oracle.jdbc.driver.OracleDriver');

If another option is needed, OWNER can be used as an option list and this could become:

CREATE SERVER 'oracle' FOREIGN DATA WRAPPER whatever OPTIONS (

HOST 'jdbc:oracle:thin:@localhost:1521:xe',

DATABASE 'HR',

USER 'scott',

PASSWORD 'tiger',

OWNER 'Driver=oracle.jdbc.driver.OracleDriver,Wrapper=ApacheInterface,Memory=2');

Your second style is perfect... totally gets the job done.

OWNER 'Driver=oracle.jdbc.driver.OracleDriver,Wrapper=ApacheWrapper,Memory=2');

Just to be clear, if we specify

OWNER 'Driver=oracle.jdbc.driver.OracleDriver,Wrapper=ApacheWrapper'

and on the create table specify

OWNER OPTION_LIST='Memory=2'

All 3 options will be applied. If so, that is idea

Fundamentally, Federated Servers are some information contained in a system table with some facilities to create and retrieve them. Therefore, a CONNECT solution of all the above problems can be to do the same, meaning using a table that will be used to contain the connection informations shared by several JDBC tables. Firstly:

CREATE OR REPLACE TABLE connect_servers

    name CHAR(32) NOT NULL, 

    url VARCHAR(63000),

    driver CHAR(255),

    scheme CHAR(16),

    host CHAR(64),

    database CHAR(64),

    user CHAR(64),

    password CHAR(64),

    socket CHAR(64),

    wrapper CHAR(64),

    memory SMALLINT,

    port SMALLINT)

[ENGINE=CONNECT TABLE_TYPE=???];

Could be any table. Making it a CONNECT table with a specific type could be a facility allowing to create it without specifying the columns (it would be done by Discovery). Making it a non CONNECT table could enable to use TEXT for the url column.

Then creating a connection server would be:

INSERT INTO connect_servers (name, url, db, user, password, driver, wrapper, memory)

VALUES (

  'oracle',

  'jdbc:oracle:thin:@localhost:1521:xe',

  'HR',

  'scott',

  'tiger',

  'oracle.jdbc.driver.OracleDriver',

  'ApacheInterface', 2);

CONNECT has internal facilities to retrieve the information from this table. Note that such servers can be added, deleted, and even modified by standard INSERT, DELETE or UPDATE statements.

This would solve the privilege, url size, and additional options problems.

Let's use the standard SERVER feature for now... if you support this for the next release:

OWNER 'Driver=oracle.jdbc.driver.OracleDriver,Wrapper=ApacheWrapper'

... and I change the column definition of mysql.servers.host to varchar(2000) [works fine in my testing - no issues] I think we are good. I have found that is is OK to create server as root because the admin user [i.e. non super] doesn't need to create servers (we can do it as root for them) just access them to create a connect table based on them [I tested this too and it works perfectly]. So adding the OWNER as above and me temporarily changing the mysql.servers.host to varchar(2000) gets me a 100% solution. Then once that is working for a month or two, I'll submit a new MDEV asking for the powers that be to either extend host to 2000 OR add the URL and OTHER parameters... that seems like the fastest path to a working solution.

Now that we have manually altered (on our servers) the mysql.servers.Host column to 2000 chars long and the mysql.servers.Owner column to 255 and have run this in production for a couple of months with CONNECT engine, everything seems to work correctly. We are requesting that in the next version of MariaDB this become the new standard size for these columns. To summarize the reason why, CONNECT engine uses the Host column for a JDBC URL and the Owner column for various options (this seems to work well with our manually extended columns, we would just like to see it become a "standard" part of the server).

Is this the right place to post this or should it be opened as a new MDEV?