Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
The idea is to enhance the existing CREATE SERVER command to work better with CONNECT engine JDBC and other future extensions & improvements to CONNECT.
Proposed syntax:
CREATE SERVER MyServerName FOREIGN DATA WRAPPER ConnectEngine
|
OPTIONS (URL 'jdbc:sqlserver://blah/blah?key=val;', |
USER 'username', |
PASSWORD 'mypass', |
OTHER 'table_type=jdbc;dbcp=1;memory=3;quote=1;') |
By having the URL as its own field you eliminate complicated escaping, and by putting all the CONNECT specific options in a single OTHER you have an easy to understand syntax.
Some notes on the above:
- For our needs, CREATE SERVER really needs to not require SUPER priv and be a DATABASE level priv so would need to be able to grant something like this:
GRANT CREATE SERVER, DROP SERVER on dbname.* to X - for our purposes, the drop server permission could automatically go with create server; we would mostly use CREATE OR REPLACE SERVER x .... and DROP SERVER x
- The URL string size limit needs to handle URLs with inline .pem SSL data, so 2k minimum but better if 6k or TEXT datatype to allow for other SSL files in future being passed in the URL itself
- in the above example I have "dbcp=1" as a synonym for wrapper=ApacheWrapper as I see this as the standard db connection pooling solution for connect jdbc - works great, no special driver needed.
Being able to issue a command like CREATE TABLE .... ENGINE=CONNECT SERVER=MyServerName means that we could change passwords, turn pooling on-off, edit the connection params, replace ssl files, etc without having to recreate each of the connect tables (customers have dozens).
This would be a REALLY great enhancement! 
Attachments
Activity
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?
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');