SYNOPSIS

        perl examples/myserver.pl --config=examples/myserver.conf --port=1234 --dsn="dbi:mysql:"

        mysql -h127.0.0.1 -P1234 -umyuser -e 'info'

DESCRIPTION

This is a simple server that listens for incoming connections from MySQL clients or connectors.

Each query received is processed according to a set of configuration files, which can rewrite the query, forward it to a DBI handle or construct a response or a result set on the fly from any data.

COMMAND LINE OPTIONS

--port=XXXX - port to listen on. Default is 23306, which is the default MySQL port with a 2 in front.

--interface=AAA.BBB.CCC.DDD - interface to listen to. Default is 127.0.0.1 which means that only connections from the localhost will be accepted. To enable connections from the outside use --interface=0.0.0.0. In this case, please make sure you have some other form of access protection, e.g. like the first rule in the myserver.conf example configuration file.

--config=config.file - a configuration file containing rules to be executed. The option can be specified multiple times and the rules will be checked in the order specified.

--dsn - specifies a DBI DSN. All queries that did not match a rule or where the rule rewrote the query or did not return any response or a result set on its own will be forwarded to that database. Individual rules can forward specific queries to specific DSNs. If you do not want non-matching queries to be forwarded, either create a match-all rule at the bottom of your last configuration file or omit the --dsn option. If you omit the option, an error message will be sent to the client.

--dsn_user and --dsn_password can be used to specify username and password for DBI drivers where those can not be specified in the DSN string.

RULES

Rules to be executed are contained in configuration files. The configuration files are actually standard perl scripts and are executed as perl subroutines. Therefore, they can contain any perl code -- the only requirement is that the last statement in the file (that is, the return value of the file) is an array containing the rules to be executed.

The actions from a rule will be executed for all queries that match a specific pattern. Rules are processed in order and processing is terminated at the first rule that returns some data to the client. This allows you to rewrite a query numerous times and have a final default rule that forwards the query to another server. If forward is defined, further rules are not processed.

Each rule can have the following attributes:

command The rule will match if the MySQL command issued by the client matches command. command can either be an integer from the list found at DBIx::MyServer.pm or a reference to a SUB that returns such an integer. This is mainly useful for processing incoming COM_PING, COM_INIT_DB and COM_FIELD_LIST.

match The rule will match if the test of the query matches a regular expression or is identical to a string. match can also be a reference to a SUB in which case the sub is executed and can either return a string or a regular expression.

If both command and match are specified, both must match for the rule to be executed.

dbh if specified, any matching query will be forwarded to this database handle (possibly after a rewrite), rather than the default handle specifeid on the command line.

dsn behaves identically, however a database handle is contructed from the dsn provided and an attempt is made to connect to the database. If dsn is a reference to an array, the first item from the array is used as a DSN, the second one is used as username and the third one is used as password.

before this can be a reference to a subroutine that will be called after a matching query has been encountered but before any further processing has taken place. The subroutine will be called with the text of the query as the first argument, followed by extra arguments containing the strings matched from any parenthesis found in the match regular expression. You can use before to execute any extra queries before the main query, such as EXPLAIN. The return value from the before subroutine is discarded and is not used.

rewrite is a string that will replace the original query that matches the rule, or a reference to a subroutine that will produce such a string. If rewrite is not defined, and match was a string, the query is passed along unchanged. If match was a regular expression, the string matched by the first set of parenthesis is used. This way, if the rule does not specify any data, columns, error or ok clauses, but a valid DBI handle is defined, the query will be forwarded to that handle automatically.

error can be either an array reference containing three arguments for DBIx::MyServer::sendError() or a reference to a subroutine returning such an array (or array reference). If this is the case, the error message will be sent to the client. If error is not defined or the subroutine returns undef, no error message will be sent. In this case, you need to send at some point either an ok or a result set, otherwise the client will hang forever waiting for a response.

ok behaves identically to error -- if it is defined or points to a subroutine which, when called, returns a true value, an OK response will be sent to the client. ok can also be a reference to an array, or the subroutine can return such an array -- in this case the first item is the message to be sent to the client, the second one is the number of affected rows, the third is the insert_id and the last one is the warning count.

columns must contain either an array reference or a reference to a subroutine which returns and array or array reference. The column names from the array will be sent to the client. By default, all columns are defined as MYSQL_TYPE_STRING.

data must contain either a reference to the data to be returned to the client or a reference to subroutine that will produce the data. "Data" can be a reference to a HASH, in which case the hash will be sent with the key names in the first column and the key values in the second. It can be a flat array, in which case the array items will be sent as a single column, or it can be a reference to a nested array, with each sub-array being a single row from the response.

after is called after all other parts of the rule have been processed.

forward if defined, the query will be immediately forwarded to the server and no further rules will be processed.

All subroutine references that are called will have the text of the query passed as the first argument and the subsequent arguments will be any strings matched by parenthesis in the match regular expression.

VARIABLES

Your code in the configuration file can save and retrieve state by using get($variable) and set($variable, $value). State is retained as long as the connection is open. Each new connection starts with a clean state. The following variables are maintained by the system:

myserver contains a reference to the DBIx::MyServer object being used to service the connection. You can use this to inject data and packets directly into the network stream.

username contains the username provided by the client at connection establishment.

database contains the database requested by the client at connection establishment. By default, myserver.pl will not automatically handle any database changes requested by the client. You are responsible for handling those either by responding with a simple OK or by updating the variables.

remote_host contains the IP of the client.

dbh and dsn will contain a reference to the default DBI handle and the DSN string it was produced from, as taken from the command line. Even if a specific rule has its own dsn, the value of those variables will always refer to the default dbh and dsn. If you change the <dsn> variable, the system will attempt to connect to the new dsn string and will produce a new dbh handle from it. If you set dsn to an array reference, the first item will be used as a DSN, the second one as a username and the third one as a password. dsn_user and dsn_password can be used for the same purpose.

args contains a reference to the @ARGV array, that is, the command line options that evoked myserver.pl

remote_dsn, remote_dsn_user and remote_dsn_password are convenience variables that can also be specified on the command line. It is not used by myserver.pl however you can use it in your rules, the way remotequery.conf does.

SECURITY

IMPORTANT NOTICE: THIS SCRIPT IS MEANT FOR DEMONSTRATION PURPOSES ONLY AND SHOULD NOT BE ASSUMED TO BE SECURE BY ANY MEANS!

By default the script will only accept incoming connections from the local host. If you relax that via the --interface command-line option, all connections will be accepted. However, once the connection has been established, you can implement access control as demonstrated in the first rule of the myserver.conf file -- it returns "Access denied" for every query unless the username is "myuser". Future versions of the script will allow connections to be rejected during handshake.

The script expects that the password is equal to the username. This is currently hard-coded.

SAMPLE RULES

The following rule sets are provided in the examples/ directory.

Simple examples - myserver.conf

This configuration provides some simple query rewriting examples as suggested by Giuseppe Maxia and Jan Kneschke, e.g. commands like ls, cd as well as fixing spelling mistakes. In addition, some very simple access control is demonstrated at the top of the file.

Remote queries - remotequery.conf

This rule set implements a SELECT REMOTE select_query ON 'dsn' operator which will execute the query on <dsn> specified, bring the results back into a temporary table on the default server and substitute the REMOTE_SELECT part in the orignal query with a reference to the temoporary table. The following scenarios are possible:

        # Standalone usage
        mysql> SELECT REMOTE * FROM mysql.user ON 'dbi:mysql:host=remote:user=foo:password=bar'

        # CREATE ... SELECT usage
        mysql> CREATE TABLE local_table SELECT REMOTE * FROM remote_table ON 'dbi:mysql:host=remote'

        # Non-correlated subquery
        mysql> SELECT *
        mysql> FROM (SELECT REMOTE * FROM mysql_user ON 'dbi:mysql:host=remote:user=foo:password=bar')
        mysql> WHERE user = 'mojo'

        # Specify remote dsn on the command line
        shell> ./myserver.pl --config=remotequery.conf --dsn=dbi:mysql: --remote_dsn=dbi:mysql:host=host2
        mysql> select remote 1;

        # Specify remote dsn as variable

        mysql> set @@@remote_dsn=dbi:mysql:host=host2
        mysql> select remote NOW();

        mysql> set @@@devel_dsn=dbi:mysql:host=dev3
        mysql> select remote NOW() ON @@@devel_dsn; 

This is different from using the Federated storage handler because the entire REMOTE_SELECT query is executed on the remote server and only the result is sent back to the default server for further processing. This is useful if a lot of processing is to be done on the remote server -- the Federated engine will bring most of the data to the connecting server and will process it there, which can potentially be very time consuming.

Please note that since a temporary table is created and it must reside somewhere, you need to be in a working database on the default server. Updateable VIEWa are not supported.

Development support - devel.conf

This configuration provides the following operators:

shell - can be used to execute shell commands, e.g. shell ls -la. env - returns the operating environment of myserver.pl. stats - executes SHOW STATUS before and after each query and returns the difference. First you execute stats select a from b and then show stats. devel - can be used to send specfic queries to a different server. You can execute a single query as devel select a from b or use a standalone devel to redirect all future queries until you issue restore.

You specify the server to send "development" queries to via set('devel_dsn') at the top of devel.conf

ODBC compatibility - odbc.conf

The odbc.conf contains an example on how to unintelligently answer generic queries sent by the MySQL ODBC driver and the applications that use it, up to the point where real data can be sent over the connection and imported into the client application.

cpanm DBIx::MyServer

perl -MCPAN -e shell
install DBIx::MyServer