CONNECT Table Types - MYSQL Table Type: Accessing MySQL/MariaDB Tables

You are viewing an old version of this article. View the current version here.

This table type uses libmysql API to access a MySQL or MariaDB table or view. This table must be created on the current server or on another local or remote server. This is similar to what the federatedx storage engine provides with some differences.

Currently the FEDERATED like syntax can be used to create such a table, for instance:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL
connection='mysql://root@localhost/test/people';

The connection string can have the same syntax than the one used by FEDERATED, including reference to a federated server, but can also be mixed with CONNECT standard options. For instance:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL dbname=test tabname=people
connection='mysql://root@localhost';

The pure (deprecated) CONNECT syntax is also accepted:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL dbname=test tabname=people
option_list='user=root,host=localhost';

The specific connection items are:

OptionDefault valueDescription
TableThe table nameThe name of the table to access.
DatabaseThe current DB nameThe database where the table is located.
HostlocalhostThe host of the server, a name or an IP address.
UserThe current userThe connection user name.
PasswordNo passwordAn optional user password.
PortThe currently used portThe port of the server.
Quoted01 if remote Tabname must be quoted.

Caution: Take care not to refer to the MYSQL table itself to avoid an infinite loop!

MYSQL table can refer to the current server as well as to another server. Views can be referred by name or directly giving a source definition, for instance:

create table myuser engine=connect table_type=mysql dbname=mysql
srcdef='select host, user, file_priv from user';

The column description can be left to be retrieved by CONNECT. If specified, the columns of the MYSQL table must exist in the accessed table with the same name, but can be only a subset of them and specified in a different order. Their type must be a type supported by CONNECT and, if it is not identical to the type of the accessed table matching column, a conversion will be done.

MySQL tables are not indexable. However, CONNECT tries to access only the requested rows. For instance if you ask:

select * from essai where num = 23;

CONNECT will construct and send to the server the query:

SELECT num, line FROM try WHERE num = 23

If the people table is indexed on num, indexing will be used on the remote server. This, in all cases, will limit the amount of data to retrieve on the network.

Note 1: Be sure mysqld was started with the condition --engine_condition_pushdown=on. Otherwise CONNECT cannot get the WHERE clause of the query.

Note 2: For columns prone to be targeted by a WHERE clause, keep the column type compatible with the source table column type (numeric or character) to have a correct rephrasing of the WHERE clause.

If you do not want to restrict or change the column definition, do not provide it and leave CONNECT get the column definition from the remote server. For instance:

create table essai engine=CONNECT table_type=MYSQL
connection='mysql://root@localhost/test/people';

This will create the essai table with the same columns than the people table.

The CONNECT MYSQL type supports SELECT and INSERT and a somewhat limited form of UPDATE and DELETE . These are described below.

Data Modifying Operations

The MYSQL type uses similar methods than the ODBC type to implement the INSERT, UPDATE and DELETE commands. Refer to the ODBC chapter for the restrictions concerning them.

For the UPDATE and DELETE commands, there are fewer restrictions because the remote server being a MySQL server, the syntax of the command will be always acceptable by the remote server.

For instance, you can freely use keywords like IGNORE or LOW_PRIORITY as well as scalar functions in the SET and WHERE clauses.

However, there is still an issue on multi-table statements. Let us suppose you have a t1 table on the remote server and want to execute a query such as:

update essai as x set line = (select msg from t1 where id = x.num)
where num = 2;

When parsed locally, you will have errors if no t1 table exists or if it does not have the referenced columns. When t1 does not exist, you can overcome this issue by creating a local dummy t1 table:

create table t1 (id int, msg char(1)) engine=BLACKHOLE;

This will make the local parser happy and permit to execute the command on the remote server. Note however that having a local MySQL table defined on the remote t1 table does not solve the problem unless it is also names t1 locally.

This is why, to permit to have all types of commands executed by the data source without any restriction, CONNECT provides a specific MySQL table subtype described now.

Sending commands to a MySQL Server

This can be done like for ODBC tables by defining a specific table that will be used to send commands and get the result of their execution..

create table send (
  command varchar(128) not null,
  warnings int(4) not null flag=3,
  number int(5) not null flag=1,
  message varchar(255) flag=2)
engine=connect table_type=mysql
connection='mysql://user@host/database'
option_list='Execsrc=1,Maxerr=2';

The key points in this create statement are the EXECSRC option and the column definition.

The EXECSRC option tells that this table will be used to send commands to the MySQL server. Most of the sent commands do not return result set. Therefore, the table columns are used to specify the command to be executed and to get the result of the execution. The name of these columns can be chosen arbitrarily, their function coming from the FLAG value:

Flag=0:The command to execute (the default)
Flag=1:The number of affected rows, or the result number of columns if the command would return a result set.
Flag=2:The returned (eventually error) message.
Flag=3:The number of warnings.

How to use this table and specify the command to send? By executing a command such as:

select * from send where command = 'a command';

This will send the command specified in the WHERE clause to the data source and return the result of its execution. The syntax of the WHERE clause must be exactly as shown above. For instance:

select * from send where command =
'CREATE TABLE people (
num integer(4) primary key autoincrement,
line char(15) not null';

This command returns:

commandwarningsnumbermessage
CREATE TABLE people (num integer(4) primary key aut...00Affected rows

Sending several commands in one call

It can be faster to execute because there will be only one connection for all of them. To send several commands in one call, use the following syntax:

select * from send where command in (
"update people set line = 'Two' where id = 2",
"update people set line = 'Three' where id = 3");

When several commands are sent, the execution stops at the end of them or after a command that is in error. To continue after n errors, set the option maxerr=n (0 by default) in the option list.

Note 1: It is possible to specify the SRCDEF option when creating an EXECSRC table. It will be the command sent by default when a WHERE clause is not specified.

Note 2: Backslashes inside commands must be escaped. Simple quotes must be escaped if the command is specified between simple quotes, and double quotes if it is specified between double quotes.

Note 3: Sent commands apply in the specified database. However, they can address any table within this database.

Note 4: Currently, all commands are executed in mode AUTOCOMMIT.

Retrieving Warnings and Notes

If a sent command causes warnings to be issued, it is useless to resend a “show warnings” command because the MySQL server is opened and closed when sending commands. Therefore, getting warnings requires a specific (and tricky) way.

To indicate that warning text must be added to the returned result, you must send a multi-command query containing “pseudo” commands that are not sent to the server but directly interpreted by the EXECSRC table. These “pseudo” commands are:

WarningTo get warnings
NoteTo get notes
ErrorTo get errors returned as warnings (?)

Note that they must be spelled (case insensitive) exactly as above, no final “s”. For instance:

select * from send where command in ('Warning','Note',
'drop table if exists try',
'create table try (id int key auto_increment, msg varchar(32) not
null) engine=aria',
"insert into try(msg) values('One'),(NULL),('Three') ",
"insert into try values(2,'Deux') on duplicate key update msg =
'Two'",
"insert into try(message) values('Four'),('Five'),('Six')",
'insert into try(id) values(NULL)',
"update try set msg = 'Four' where id = 4",
'select * from try');

This can return something like this:

commandwarningsnumbermessage
drop table if exists try10Affected rows
Note01051Unknown table 'try'
create table try (id int key auto_increment, msg...00Affected rows
insert into try(msg) values('One'),(NULL),('Three')13Affected rows
Warning01048Column 'msg' cannot be null
insert into try values(2,'Deux') on duplicate key...02Affected rows
insert into try(msge) values('Four'),('Five'),('Six')01054Unknown column 'msge' in 'field list'
insert into try(id) values(NULL)11Affected rows
Warning01364Field 'msg' doesn't have a default value
update try set msg = 'Four' where id = 401Affected rows
select * from try02Result set columns

The execution continued after the command in error because of the MAXERR option. Normally this would have stopped the execution.

Of course, the last “select” command is useless here because it cannot return the table contain. Another MYSQL table without the EXECSRC option and with proper column definition should be used instead.

CONNECT MYSQL versus FEDERATED

The CONNECT MYSQL table type should not be regarded as a replacement for the FEDERATED(X) engine. The main use of the MYSQL type is to access other engine tables as if they were CONNECT tables. This was necessary when accessing tables from some CONNECT table types such as TBL, XCOL, OCCUR, or PIVOT that are designed to access CONNECT tables only. When their target table is not a CONNECT table, these types are silently using internally an intermediate MYSQL table.

However, there are cases where you can use MYSQL CONNECT tables yourself, for instance:

  1. When the table will be used by a TBL table. This enables you to specify the connection parameters for each sub-table and is more efficient than using a local FEDERATED sub-table.
  2. When the desired returned data is directly specified by the SRCDEF option. This is great to let the remote server do most of the job, such as grouping and/or joining tables. This cannot be done with the FEDERATED engine.
  3. For tables with the EXECSRC option on.
  4. When doing tests. For instance to check a connection string.

If you need multi-table updating, deleting, or bulk inserting on a remote table, you can alternatively use the FEDERATED engine or a “send” table specifying the EXECSRC option on.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.