NAME

DBIx::MyServer - Server-side implementation of the MySQL network protocol

SYNOPSIS

Please see the scripts in the examples directory. examples/dbi.pl along with DBIx::MyParse::DBI shows how to use this module by subclassing it. examples/echo.pl shows how to use this module directly.

DESCRIPTION

This module emulates the server side of the MySQL protocol. This allows you to run your own faux-MySQL servers which can accept commands and queries and reply accordingly.

Please see examples/myserver.pl for a system that allows building functional mysql servers that rewrite queries or return arbitary data.

CONSTRUCTOR

my $myserver = DBIx::MyServer->new( socket => $socket, parser => $parser, dbh => $dbh ... )

The following parameters are accepted:

socket - the socket that will be used for communication with the client. The socket must be created in advance with socket() and an incoming connection must be already established using accept()

dbh - a DBI handle. DBIx::MyServer does not use it however it can be used by modules inheriting from DBIx::MyServer

parser - a DBIx::MyParser handle. If you do not override the comQuery() method, this handle will be used to parse the SQL in order to call the appropriate sqlcom... method.

banner - the server version string that is announced to the client. If not specified, the name and the version of the DBIx::MyServer module is sent. Some clients, e.g. DBD::mysql attempt to parse that string as a version number in order to determine the server capabilities. Therefore, it may be a good idea to start your banner with a string in the form 5.0.37. For example, the examples/dbi.pl example takes the banner from the actual MySQL server the connection is forwarded to. Please note that the MySQL client may isssue a select @@version_comment limit 1 command and display the result to the user.

server_charset - the character set (and collation) the server announces to the client. A number is expected, not a character set or collation name. To onvert between numbers and names, please consult the sql/share/charsets/Index.xml file from your MySQL source tree. The numbers are found under the id property of each XML leaf.

Generally you do not need to override the constructor. If you do, please keep in mind that it returns a blessed @ARRAY, not a blessed %HASH, even though new() accepts an argument hash. This is done for performance reasons. The first 20 items from the array are reserved for the parent module, so please put your object properties into the array members from 20 onwards.

CONNECTION ESTABLISHMENT

You are responsible for opening your listening socket and accepting a connection on it (and possibly forking a child process). Once the connection has been accepted, you create a DBIx::MyServer object and pass the socket to it. From then on, you have two options:

Procedural Connection Establishment

If you want to handle connection establishment yourself, you will need to call those two functions consequtively. Please see examples/echo.pl for a script that uses procedural connection establishment.

$myserver->sendServerHello()

The server is the one that initiates the handshake by sending his greeting to the client.

my ($username, $database) = $myserver->readClientHello()

The client provides his username and the database it wants to connect to. If $username is undef, the client disconnected before authenticating. To check the password, use $myserver-passwordMatches($correct_password) which will return 1 if the password provided by the client is correct and undef otherwise.

If you need to know the IP of the client, you need to extract it from the socket that you established yourself. Please check out the implementation of DBIx::MyParse::handshake() for the correct way to call getpeername(). The socket being serviced by the current DBIx::MyParse object can be obtained by calling getSocket().

If you want to let the client in, do a $myserver->sendOK(). Otherwise, use $myserver->sendError() as described below.

Connection Establishment with Subclassing

If you are subclassing DBIx::MyParse, the way DBIx::MyParse::DBI does, to establish a connection you call:

$myserver->handshake()

which completes the handshake between the two parties. The return value will be undef if some I/O error occured, or the result of the client authorization routine. When the client sends its credentials, the module will call:

$myserver->authorize($remote_host, $username, $database)

whose default action is to accept only localhost connections regardless of username or password. You should override this method to install your own security requirements. If your authorize() returns undef, the connection will be rejected, if it returns anything else, the connection will be completed and the return value will be passed back to the caller of handshake(). You can use this return value to communicate the access rights the particular user is entitled to, so that your script can know them and enforce them.

In case you want to reject the connection, you need to send your own error message via sendError(). If you accept the connection, the module will send the OK message back to the client for you.

The password supplied by the client is irreversibly encrypted, therefore to verify it, you need to use:

$myserver->passwordMatches($expected_password)

which will return undef if the password does not match and 1 otherwise.

If the client supplied a database name on connect, comInitDb($database) will be called.

For an example of a custom authorize(), please see DBIx::MyParse::DBI which does some extra connection setup.

COMMAND PROCESSING

Procedural Command Processing

If you want to handle each command individually on your own, you need to call

my ($command, $data) = $myserver->readCommand()

in a loop and process each command. Sending result sets and errors and terminating the connection is entirely up to you. The examples/odbc.pl script uses this approach to process queries in the simples possible way without reflecting much on their contents.

Command Processing with Subclassing

If you are subclassing DBIx::MyParse, your main script needs to call:

$myserver->processCommand()

in a loop. The default processCommand() handler will obtain the next command from the client using readCommand() and will call the appropriate individual command handler, described below. You can override processCommand() if you want to process the entire packet yourself, in which case you are responsible for calling readCommand() yourself.

processCommand() will return whatever the individual command handler returned. undef is reserved for I/O errors, which will allow you to conveniently exit your loop. Therefore it is recommended that any handlers that you override return 1 to indicate correct operation or non-fatal errors.

INDIVIDUAL COMMAND HANDLERS

If you do not override the generic processCommand() method, the following individual handlers will be called depending on the actual MySQL command received by the server. The default action of those handlers is to send a "command unsupported" error back to the client, unless specified otherwise below.

If you want to send an error or an OK message to the client as a response to a command or query, you need to use sendOK() and sendError() yourself. The parent module will not send any of those for you under no circumstances.

$myserver->comSleep($data)

The meaning of this command is not clear.

$myserver->comQuit()

The default action is to return undef, meaning that processCommand() will return undef, which you can use to exit your command loop. Alternatively, you can override this to call die() or exit(), if you are forking individual child processes for each client.

$myserver->comInitDb($database)

This command is used by the client to select a default working database. The default action is to set the default database for the parser object if one has been specified. This enables the parser to parse some SQL statements that require a default database, such as SHOW TABLE STATUS. The command is then converted into a USE $database SQL statement which in turn will trigger comQuery("USE $database") or sqlcomChangeDB()

$myserver->comQuery($query_text)

This handler is called for all SQL queries received by the server. The action of the default handler is to parse the query using DBIx::MyParse and evoke a more specific handler. If the parsing results in an error, sqlcomError() is called which returns the parser error message to the client.

If you override this handler, your implementation must return an array of three items. The first item is the value that will be returned to your main loop as the return value of processCommand(). You can return anything you please, including references to complex objects. Returning undef can be used to conveniently terminate the command loop, so you are generally encouraged to return some true and definied value to indicate proper operation.

The second item you can return is a reference to an array of field definitions created with newDefinition(). If you provide it, your field definitions will be sent to the client using sendDefinitions(). If you do not provide a reference, you are responsible for calling sendDefinitions() yourself before you send any data.

The third item you can return is a reference to an array of values that is the actual data to be sent to the client in response to the query. If you do not provide a reference, you are responsible for sending the data yourself by using the functions described elsewhere in this document.

$myserver->comFieldList($table)

This handler is called if the mysql client requests the field list for the specified table. The handler must create a set of field definitions using newDefinition() and then send them to the client using sendDefinitions(\@definitions, 1). It is strongly reccomended that you provide your own working implementation for comFieldList() because this MySQL command is often issued by the various MySQL connectors and is essential for the functioning of the FEDERATED table handler.

For an example of easily handling comFieldList(), see DBIx::MyServer::DBI which uses DBI's column_info to return the definition of an actual MySQL table from another server.

$myserver->comCreateDb($database_name)

This handler is called when mysqladmin create $database_name is used. The default is to convert the command into a CREATE DATABASE $database_name SQL statement, so that comQuery("CREATE DATABASE $database_name") is triggered, which in turn will call sqlcomCreateDb().

$myserver->comDropDb($database_name)

This handler is called when mysqladmin drop $database_name is used. The default is to convert the command into a DROP DATABASE $database_name SQL statement so that comQuery("DROP DATABASE $database_name") is triggered, which in turn will call sqlcomDropDb().

$myserver->comShutdown()

This handler is called when mysqladmin shutdown is used. The default action is to die(), in other words, any authorized client can shut down its own child, or the entire server, if the server is not forking.

$myserver->comStatistics()

The default action is to send our PID to the client. If you want to override that, you can use _sendPacket($string) (rather than sendOK()) to deliver a single string to your client. Please note that mysql and mysqladmin may attempt to parse this string before displaying it, so you may wish to keep it identical to the one sent by real MySQL servers:

Uptime: 10659 Threads: 1 Questions: 756 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 30 Queries per second avg: 0.071

$myserver->comProcessInfo()

This handler is called when mysqladmin processlist is called from the command line.

$myserver->comProcessKill($thread_id)

This handler is called as a response by the command issued by mysqladmin kill. In DBIx::MyServer, the MySQL thread ID is equal to the PID of the server process. The default action will kill() the PID however you are only allowed to kill your own PID.

$myserver->comPing()

This is used to check if the server is reachable and running. The default action is to do a sendOK(). If you are using that in a conjunction with an automated monitoring and alert system, you may wish to do a sendOK() only after doing extra health checks on your own, e.g. if you are reading data from an external data source, do not send an OK unless that source is reachable.

INDIVIDUAL QUERY HANDLERS

If you do not override the generic comQuery() method, the incoming MySQL query will be parsed to a DBIx::MyParse::Query object and the object will be dispatched to one of the sqlcom... handlers based on the type of the query.

The default handlers all return "query unsupported". If you override any of the default handlers, your handler must return one two or three items as described under comQuery().

Each sqlcom... handler receives at least two arguments. The first one is the DBIx::MyParse::Query object produced by DBIx::MyParse. The second is the text of the query. sqlcom... handlers dealing with databases will get the database name in question as their third argument.

If the parsing resulted in an error, sqlcomError($query, $query_string) is called. The default action of this handler is to send the parser error message as is to the client.

RETURNING MESSAGES TO CLIENT

$myserver->sendOK($message, $affected_rows, $insert_id, $warning_count)

Returns a simple OK response, which can contain a custom message, the number of rows affected by the query, etc.

$myserver->sendError($message, $errno, $sqlstate)

Returns an error response. IF no errno and sqlstate are specified, generic values will be sent to client.

RETURNING DATA TO CLIENT

my $definition = $myserver->newDefinition(name => 'field_name')

Prepares a new field definition that can then be sent to the client. Apart from name, the following attributes are supported:

        catalog, db, table, org_table
        name, org_name, length, type,
        flags, decimals, default

The default type is MYSQL_TYPE_STRING. The complete list of type constants is available from MyServer.pm.

$myserver->sendDefinitions( \@definitions, $skip_envelope )

Sends the previously prepared field definitions to the client. You need to do that before sending the first data row. You also need to use sendDefinitions() to send a table definition in response to comFieldList() command, however due to an inconsistency in the MySQL protocol, $skip_envelope must be true.

sendRow($array_ref) and sendRows($array_ref)

Those two functions are used to send the actual data to the client. sendRow() expect a reference to the array containing the values you wish to send. sendRows() expects a reference to an array containing references to arrays.

Please note in the MySQL protocol, all values are sent consequtively without row boundaries. The number of columns in each row is determined by how many field definitions you send before you start sending the actual data.

sendEOF()

Indicates the end of the data from the record set.

PREPARED STATEMENTS

DBIx::MyServer does not currently support the prepared statement protocol. By default, the comStmtPrepare() handler will return ER_UNSUPPORTED_PS back to the client, which should instruct a wise client to retry the query using conventional statemements.

As of Nov 7th, 2006, the ODBC Driver version 3.51.12 does not use prepared statements at all. The FEDERATED database engine does not use them. DBD::Mysql version 3.0008_1 will attempt a prepared statement and if ER_UNSUPPORTED_PS is received, it will fall back to normal statements. To force the use of normal statements, add mysql_emulated_prepare=1 to your DSN string.

SECURITY CONSIDERATIONS

The defaults of this module are meant to allow quick prototyping of MySQL servers by subclassing. Therefore, by default:

• Only localhost connections are allowed if you use handshake(). Beyond checking that, no other access checks are made and all usernames are accepted. No password checks are made. After the handshake, any access restrictions on individual commands or queriesare entirely up to you.

• The default action for comQuit() and comShutdown() is to die() which allows anyone to bring down a server that is not forking child processes. The default action of comProcessKill() only allows you to kill your own server process via SIGTERM.

• A single cryptographic salt is used throughout the lifetime of the process, which may be a security risk.

AUTHOR

Philip Stoev, <philip@stoev.org<gt>

COPYRIGHT AND LICENSE

Copyright (C) 2006 by Philip Stoev

This library is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License Agreement.

Please note that that the MySQL Protocol is proprietary and is also covered by the GNU General Public License. Please see

http://dev.mysql.com/doc/internals/en/licensing-notice.html

If you have any licensing doubts, please contact MySQL AB directly.

SEE ALSO AND THANKS

The following sources of information have been very helpful in creating this module:

http://dev.mysql.com/doc/internals/en/client-server-protocol.html

http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html

cpanm DBIx::MyServer

perl -MCPAN -e shell
install DBIx::MyServer