Async MySQL database client for ReactPHP.

Friends of ReactPHP

Last update: Dec 11, 2022

Related tags

Database mysql

Overview

MySQL

Async MySQL database client for ReactPHP.

This is a MySQL database driver for ReactPHP. It implements the MySQL protocol and allows you to access your existing MySQL database. It is written in pure PHP and does not require any extensions.

Table of contents

Quickstart example
Usage
- Factory
  - createConnection()
  - createLazyConnection()
- ConnectionInterface
  - query()
  - queryStream()
  - ping()
  - quit()
  - close()
  - Events
Install
Tests
License

Quickstart example

This example runs a simple SELECT query and dumps all the records from a book table:

$factory = new React\MySQL\Factory();
$connection = $factory->createLazyConnection('user:pass@localhost/bookstore');

$connection->query('SELECT * FROM book')->then(
    function (QueryResult $command) {
        print_r($command->resultFields);
        print_r($command->resultRows);
        echo count($command->resultRows) . ' row(s) in set' . PHP_EOL;
    },
    function (Exception $error) {
        echo 'Error: ' . $error->getMessage() . PHP_EOL;
    }
);

$connection->quit();

Usage

Factory

The Factory is responsible for creating your ConnectionInterface instance.

$factory = new React\MySQL\Factory();

This class takes an optional LoopInterface|null $loop parameter that can be used to pass the event loop instance to use for this object. You can use a null value here in order to use the default loop. This value SHOULD NOT be given unless you're sure you want to explicitly use a given event loop instance.

If you need custom connector settings (DNS resolution, TLS parameters, timeouts, proxy servers etc.), you can explicitly pass a custom instance of the ConnectorInterface:

$connector = new React\Socket\Connector([
    'dns' => '127.0.0.1',
    'tcp' => [
        'bindto' => '192.168.10.1:0'
    ],
    'tls' => [
        'verify_peer' => false,
        'verify_peer_name' => false
    )
]);

$factory = new React\MySQL\Factory(null, $connector);

createConnection()

The createConnection(string $url): PromiseInterface method can be used to create a new ConnectionInterface.

It helps with establishing a TCP/IP connection to your MySQL database and issuing the initial authentication handshake.

$factory->createConnection($url)->then(
    function (ConnectionInterface $connection) {
        // client connection established (and authenticated)
    },
    function (Exception $e) {
        // an error occured while trying to connect or authorize client
    }
);

The method returns a Promise that will resolve with a ConnectionInterface instance on success or will reject with an Exception if the URL is invalid or the connection or authentication fails.

The returned Promise is implemented in such a way that it can be cancelled when it is still pending. Cancelling a pending promise will reject its value with an Exception and will cancel the underlying TCP/IP connection attempt and/or MySQL authentication.

$promise = $factory->createConnection($url);

Loop::addTimer(3.0, function () use ($promise) {
    $promise->cancel();
});

The $url parameter must contain the database host, optional authentication, port and database to connect to:

$factory->createConnection('user:secret@localhost:3306/database');

Note that both the username and password must be URL-encoded (percent-encoded) if they contain special characters:

$user = 'he:llo';
$pass = 'p@ss';

$promise = $factory->createConnection(
    rawurlencode($user) . ':' . rawurlencode($pass) . '@localhost:3306/db'
);

You can omit the port if you're connecting to default port 3306:

$factory->createConnection('user:secret@localhost/database');

If you do not include authentication and/or database, then this method will default to trying to connect as user root with an empty password and no database selected. This may be useful when initially setting up a database, but likely to yield an authentication error in a production system:

$factory->createConnection('localhost');

This method respects PHP's default_socket_timeout setting (default 60s) as a timeout for establishing the connection and waiting for successful authentication. You can explicitly pass a custom timeout value in seconds (or use a negative number to not apply a timeout) like this:

$factory->createConnection('localhost?timeout=0.5');

By default, the connection uses the utf8 charset encoding. Note that MySQL's utf8 encoding (also known as utf8mb3) predates what is now known as UTF-8 and for historical reasons doesn't support emojis and other characters. If you want full UTF-8 support, you can pass the charset encoding like this:

$factory->createConnection('localhost?charset=utf8mb4');

createLazyConnection()

Creates a new connection.

It helps with establishing a TCP/IP connection to your MySQL database and issuing the initial authentication handshake.

$connection = $factory->createLazyConnection($url);

$connection->query(…);

This method immediately returns a "virtual" connection implementing the ConnectionInterface that can be used to interface with your MySQL database. Internally, it lazily creates the underlying database connection only on demand once the first request is invoked on this instance and will queue all outstanding requests until the underlying connection is ready. Additionally, it will only keep this underlying connection in an "idle" state for 60s by default and will automatically end the underlying connection when it is no longer needed.

From a consumer side this means that you can start sending queries to the database right away while the underlying connection may still be outstanding. Because creating this underlying connection may take some time, it will enqueue all oustanding commands and will ensure that all commands will be executed in correct order once the connection is ready. In other words, this "virtual" connection behaves just like a "real" connection as described in the ConnectionInterface and frees you from having to deal with its async resolution.

If the underlying database connection fails, it will reject all outstanding commands and will return to the initial "idle" state. This means that you can keep sending additional commands at a later time which will again try to open a new underlying connection. Note that this may require special care if you're using transactions that are kept open for longer than the idle period.

Note that creating the underlying connection will be deferred until the first request is invoked. Accordingly, any eventual connection issues will be detected once this instance is first used. You can use the quit() method to ensure that the "virtual" connection will be soft-closed and no further commands can be enqueued. Similarly, calling quit() on this instance when not currently connected will succeed immediately and will not have to wait for an actual underlying connection.

Depending on your particular use case, you may prefer this method or the underlying createConnection() which resolves with a promise. For many simple use cases it may be easier to create a lazy connection.

The $url parameter must contain the database host, optional authentication, port and database to connect to:

$factory->createLazyConnection('user:secret@localhost:3306/database');

Note that both the username and password must be URL-encoded (percent-encoded) if they contain special characters:

$user = 'he:llo';
$pass = 'p@ss';

$connection = $factory->createLazyConnection(
    rawurlencode($user) . ':' . rawurlencode($pass) . '@localhost:3306/db'
);

You can omit the port if you're connecting to default port 3306:

$factory->createLazyConnection('user:secret@localhost/database');

$factory->createLazyConnection('localhost');

This method respects PHP's default_socket_timeout setting (default 60s) as a timeout for establishing the underlying connection and waiting for successful authentication. You can explicitly pass a custom timeout value in seconds (or use a negative number to not apply a timeout) like this:

$factory->createLazyConnection('localhost?timeout=0.5');

By default, this method will keep "idle" connection open for 60s and will then end the underlying connection. The next request after an "idle" connection ended will automatically create a new underlying connection. This ensure you always get a "fresh" connection and as such should not be confused with a "keepalive" or "heartbeat" mechanism, as this will not actively try to probe the connection. You can explicitly pass a custom idle timeout value in seconds (or use a negative number to not apply a timeout) like this:

$factory->createLazyConnection('localhost?idle=0.1');

$factory->createLazyConnection('localhost?charset=utf8mb4');

ConnectionInterface

The ConnectionInterface represents a connection that is responsible for communicating with your MySQL server instance, managing the connection state and sending your database queries.

query()

The query(string $query, array $params = []): PromiseInterface method can be used to perform an async query.

This method returns a promise that will resolve with a QueryResult on success or will reject with an Exception on error. The MySQL protocol is inherently sequential, so that all queries will be performed in order and outstanding queries will be put into a queue to be executed once the previous queries are completed.

$connection->query('CREATE TABLE test ...');
$connection->query('INSERT INTO test (id) VALUES (1)');

If this SQL statement returns a result set (such as from a SELECT statement), this method will buffer everything in memory until the result set is completed and will then resolve the resulting promise. This is the preferred method if you know your result set to not exceed a few dozens or hundreds of rows. If the size of your result set is either unknown or known to be too large to fit into memory, you should use the queryStream() method instead.

$connection->query($query)->then(function (QueryResult $command) {
    if (isset($command->resultRows)) {
        // this is a response to a SELECT etc. with some rows (0+)
        print_r($command->resultFields);
        print_r($command->resultRows);
        echo count($command->resultRows) . ' row(s) in set' . PHP_EOL;
    } else {
        // this is an OK message in response to an UPDATE etc.
        if ($command->insertId !== 0) {
            var_dump('last insert ID', $command->insertId);
        }
        echo 'Query OK, ' . $command->affectedRows . ' row(s) affected' . PHP_EOL;
    }
}, function (Exception $error) {
    // the query was not executed successfully
    echo 'Error: ' . $error->getMessage() . PHP_EOL;
});

You can optionally pass an array of $params that will be bound to the query like this:

$connection->query('SELECT * FROM user WHERE id > ?', [$id]);

The given $sql parameter MUST contain a single statement. Support for multiple statements is disabled for security reasons because it could allow for possible SQL injection attacks and this API is not suited for exposing multiple possible results.

queryStream()

The queryStream(string $sql, array $params = []): ReadableStreamInterface method can be used to perform an async query and stream the rows of the result set.

This method returns a readable stream that will emit each row of the result set as a data event. It will only buffer data to complete a single row in memory and will not store the whole result set. This allows you to process result sets of unlimited size that would not otherwise fit into memory. If you know your result set to not exceed a few dozens or hundreds of rows, you may want to use the query() method instead.

$stream = $connection->queryStream('SELECT * FROM user');
$stream->on('data', function ($row) {
    echo $row['name'] . PHP_EOL;
});
$stream->on('end', function () {
    echo 'Completed.';
});

You can optionally pass an array of $params that will be bound to the query like this:

$stream = $connection->queryStream('SELECT * FROM user WHERE id > ?', [$id]);

This method is specifically designed for queries that return a result set (such as from a SELECT or EXPLAIN statement). Queries that do not return a result set (such as a UPDATE or INSERT statement) will not emit any data events.

See also ReadableStreamInterface for more details about how readable streams can be used in ReactPHP. For example, you can also use its pipe() method to forward the result set rows to a WritableStreamInterface like this:

$connection->queryStream('SELECT * FROM user')->pipe($formatter)->pipe($logger);

Note that as per the underlying stream definition, calling pause() and resume() on this stream is advisory-only, i.e. the stream MAY continue emitting some data until the underlying network buffer is drained. Also notice that the server side limits how long a connection is allowed to be in a state that has outgoing data. Special care should be taken to ensure the stream is resumed in time. This implies that using pipe() with a slow destination stream may cause the connection to abort after a while.

ping()

The ping(): PromiseInterface method can be used to check that the connection is alive.

This method returns a promise that will resolve (with a void value) on success or will reject with an Exception on error. The MySQL protocol is inherently sequential, so that all commands will be performed in order and outstanding command will be put into a queue to be executed once the previous queries are completed.

$connection->ping()->then(function () {
    echo 'OK' . PHP_EOL;
}, function (Exception $e) {
    echo 'Error: ' . $e->getMessage() . PHP_EOL;
});

quit()

The quit(): PromiseInterface method can be used to quit (soft-close) the connection.

This method returns a promise that will resolve (with a void value) on success or will reject with an Exception on error. The MySQL protocol is inherently sequential, so that all commands will be performed in order and outstanding commands will be put into a queue to be executed once the previous commands are completed.

$connection->query('CREATE TABLE test ...');
$connection->quit();

close()

The close(): void method can be used to force-close the connection.

Unlike the quit() method, this method will immediately force-close the connection and reject all oustanding commands.

$connection->close();

Forcefully closing the connection will yield a warning in the server logs and should generally only be used as a last resort. See also quit() as a safe alternative.

Events

Besides defining a few methods, this interface also implements the EventEmitterInterface which allows you to react to certain events:

error event

The error event will be emitted once a fatal error occurs, such as when the connection is lost or is invalid. The event receives a single Exception argument for the error instance.

$connection->on('error', function (Exception $e) {
    echo 'Error: ' . $e->getMessage() . PHP_EOL;
});

This event will only be triggered for fatal errors and will be followed by closing the connection. It is not to be confused with "soft" errors caused by invalid SQL queries.

close event

The close event will be emitted once the connection closes (terminates).

$connection->on('close', function () {
    echo 'Connection closed' . PHP_EOL;
});

Install

The recommended way to install this library is through Composer. New to Composer?

This will install the latest supported version:

$ composer require react/mysql:^0.5.6

See also the CHANGELOG for details about version upgrades.

This project aims to run on any platform and thus does not require any PHP extensions and supports running on legacy PHP 5.4 through current PHP 8+ and HHVM. It's highly recommended to use the latest supported PHP version for this project.

Tests

To run the test suite, you first need to clone this repo and then install all dependencies through Composer:

$ composer install

The test suite contains a number of functional integration tests that send actual test SQL queries against your local database and thus rely on a local MySQL test database with appropriate write access. The test suite creates and modifies a test table in this database, so make sure to not use a production database! You can change your test database credentials by passing these ENV variables:

$ export DB_HOST=localhost
$ export DB_PORT=3306
$ export DB_USER=test
$ export DB_PASSWD=test
$ export DB_DBNAME=test

For example, to create an empty test database, you can also use a temporary mysql Docker image like this:

$ docker run -it --rm --net=host \
    -e MYSQL_RANDOM_ROOT_PASSWORD=yes -e MYSQL_DATABASE=test \
    -e MYSQL_USER=test -e MYSQL_PASSWORD=test mysql:5

To run the test suite, go to the project root and run:

$ vendor/bin/phpunit

License

MIT, see LICENSE file.

This is a community project now managed by @friends-of-reactphp. The original implementation was created by @bixuehujin starting in 2013 and has been migrated to @friends-of-reactphp in 2018 to help with maintenance and upcoming feature development.

The original implementation was made possible thanks to the following projects:

phpdaemon: the MySQL protocol implementation is based on code of this project (with permission).
node-mysql: the API design is inspired by this project.

Comments

Is there 10.4.17-MariaDB compatibility?

I'm having connection problems. I used the more simple example (quick start) too, with no password and default test database, without success. I always get the message: [Connection to localhost:3306 failed: Last error for IPv4: Connection to tcp://127.0.0.1:3306?hostname=localhost failed: Connection refused. Previous error for IPv6: Connection to tcp://[::1]:3306?hostname=localhost failed: Connection refused]
question

opened by carliedu 9
multiple insert support

Hello, it would be great to support multiple insert statements by accepting an array of array as parameter of the query method (like PDO) Possible?

moreover how to access the database link in order to issue mysqli_real_escape_string($link, ...)? it's needed until the multiple insert is supported in order to escape strings passed to mysql
question

opened by blacktek 9

PHP Fatal error: Uncaught Error: Call to a member function emit() on null

Hello.

I am getting a fatal error on version 0.3.3.

But all works fine on 0.3.2.

Something gone wrong in Parse. I attached a log trace below.

PHP Fatal error:  Uncaught Error: Call to a member function emit() on null in /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php:322
#0 /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php(309): React\MySQL\Protocal\Parser->onResultRow(Array)
#1 /var/www/huj/public_html/libraries/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php(123): React\MySQL\Protocal\Parser->parse('\x01\x00\x00\x01'R\x00\x00\x02\x03def\fc...')
#2 /var/www/huj/public_html/libraries/vendor/react/stream/src/Util.php(71): Evenement\EventEmitter->emit('data', Array)
#3 /var/www/huj/public_html/libraries/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php(123): React\Stream\Util::React\Stream\{closure}('\x01\x00\x00\x01'R\x00\x00\x02\x03def\fc...')
#4 /var/www/huj/public_html/libraries/vendor/react/stream/src/DuplexResourceStream.php(193): Evenement\EventEmitter->emit('data', Array)
#5 [internal function]: React\Stream\DuplexResourceStream->handleData(Resource id #397)
#6 / in /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php on line 322

Fatal error: Uncaught Error: Call to a member function emit() on null in /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php on line 322

Error: Call to a member function emit() on null in /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php on line 322

Call Stack:
    0.0004     398984   1. {main}() /var/www/huj/public_html/libraries/WebSocketServer/web-socket-server.php:0
    0.1307   13110064   2. Ratchet\Server\IoServer->run() /var/www/huj/public_html/libraries/WebSocketServer/web-socket-server.php:156
    0.1307   13110064   3. React\EventLoop\StreamSelectLoop->run() /var/www/huj/public_html/libraries/vendor/cboden/ratchet/src/Ratchet/Server/IoServer.php:74
  940.7393   16629272   4. React\EventLoop\StreamSelectLoop->waitForStreamActivity() /var/www/huj/public_html/libraries/vendor/react/event-loop/src/StreamSelectLoop.php:205
  940.7393   16632312   5. call_user_func:{/var/www/huj/public_html/libraries/vendor/react/event-loop/src/StreamSelectLoop.php:238}() /var/www/huj/public_html/libraries/vendor/react/event-loop/src/StreamSelectLoop.php:238
  940.7393   16632312   6. React\Stream\DuplexResourceStream->handleData() /var/www/huj/public_html/libraries/vendor/react/event-loop/src/StreamSelectLoop.php:238
  940.7393   16702344   7. React\Stream\DuplexResourceStream->emit() /var/www/huj/public_html/libraries/vendor/react/stream/src/DuplexResourceStream.php:193
  940.7393   16702344   8. React\Stream\Util::React\Stream\{closure}() /var/www/huj/public_html/libraries/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php:123
  940.7393   16702720   9. React\Socket\Connection->emit() /var/www/huj/public_html/libraries/vendor/react/stream/src/Util.php:71
  940.7393   16702720  10. React\MySQL\Protocal\Parser->parse() /var/www/huj/public_html/libraries/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php:123
  940.7432   16745824  11. React\MySQL\Protocal\Parser->onResultRow() /var/www/huj/public_html/libraries/vendor/react/mysql/src/Protocal/Parser.php:309

bug question help wanted

opened by ghost 9

Nullify connecting after canceling the timer

https://github.com/friends-of-reactphp/mysql/pull/106#issuecomment-490419998

Having a hard time reproducing this.

What basically happens is the connection closes on the database side, the connection is nullified before the idleTimer is cancelled.

https://github.com/friends-of-reactphp/mysql/blob/c420ca0d199f2867854508473f5eccecbcb692f1/src/Io/LazyConnection.php#L62-L69

My script somehow managed to run the idleTimer before the cancelTimer, and after connecting = null (yay for async). ...

My previous pr would actually trigger a new connection when trying to idle it... This just moves nullifying connecting AFTER canceling the idleTimer.

I can't seem to reproduce this, it was a one time fluke, which resulted in a fatal error and killed the script.
bug help wanted

opened by choval 8

Use Promise-based API for connect() and query() etc.

Starting an issue per email chain

Connection::connect()->then(
    function($conn)  {},
    function($err) {});

Connection::query($query, $bindParams)->then(
    function($resultSet $conn)  {
         $resultSet->insertId, $affectedRows, $fields, $rows etc..
    },
    function($err) {});

new feature BC break

opened by attozk 8

Something strange with streams

Hi, I cannot describe it in details, but after a some time working stream starts output something unusual.

selection_20180621_a20bc9d

Version of used library below

{
            "name": "react/mysql",
            "version": "dev-master",
            "source": {
                "type": "git",
                "url": "https://github.com/friends-of-reactphp/mysql.git",
                "reference": "967ed259ce4eaf28adfe588c391faf2c8439c74d"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/friends-of-reactphp/mysql/zipball/967ed259ce4eaf28adfe588c391faf2c8439c74d",
                "reference": "967ed259ce4eaf28adfe588c391faf2c8439c74d",
                "shasum": ""
            },
            "require": {
                "evenement/evenement": "^3.0 || ^2.1 || ^1.1",
                "php": ">=5.4.0",
                "react/event-loop": "^1.0 || ^0.5 || ^0.4 || ^0.3",
                "react/socket": "^1.0 || ^0.8"
            },
            "require-dev": {
                "phpunit/phpunit": "^4.8.35"
            },
            "type": "library",
            "autoload": {
                "psr-4": {
                    "React\\MySQL\\": "src"
                }
            },
            "notification-url": "https://packagist.org/downloads/",
            "license": [
                "MIT"
            ],
            "description": "Async MySQL database client for ReactPHP.",
            "keywords": [
                "async",
                "database",
                "mysql",
                "reactphp"
            ],
            "time": "2018-06-20T18:40:29+00:00"
        },

The same code but for version 0.3.3 is working fine

code sample


$coreResource = $connection->query('SELECT * FROM core_resource');

$coreResource->on('result', function ($result, $command, $conn) {
  if ($command->hasError()) {
    $error = $command->getError();
    echo 'Error: ' . $error->getMessage() . PHP_EOL;
  } else {
    echo 'CORE RESOURCE2 ' . $result['id'] . ' - ' . $result['resource'] . PHP_EOL;
  }
});

bug

opened by valVk 7

Rename to bixuehujin/reactphp-mysql

Thanks for this great library! Let's get this released under your name so your own name gets more visibility.

See also https://github.com/reactphp/react/wiki/VendorName

Note that you should probably also check this on Packagist.
maintenance

opened by clue 7
Run SQL commands on connection

I'm trying to run specific SQL queries at connection time, eg. SET time_zone = '+03:00';.

Is there a way to do so that will re-run this query every time the connection is re-established? As far as I can see, no, because if one chains a ->then() onto the original LazyConnection or Connection, this will NOT be rerun if the connection closes and is reopened.

Alternatively, one could use an 'open' or 'connect' event, but it doesn't look like there is one.

Any good ideas? Thanks!
new feature question help wanted

opened by langabi 6

Ratchet Websocket best practice

Hey,

I've build a Websocket chat based on ratchet that uses reactphp async mysql and just got a couple of questions to make sure I'm doing things right, as I couldn't really find any examples out there.

So, the Socket will be used by different applications, each of them has it's own database where the data has to be stored and published, which means there are multiple Lazy connections created, this is done by the Ratchet Pusher. There is one database locally on the Websocket server that stores all connected interfaces in one table, out of there the connections are distributed.

The push-server starts the event loop and passes the mysql factory to the pusher constructor (push-server.php):

My first question is: Is it ok like that to pass the whole factory object directly to the pusher?

require dirname(__DIR__).'/websocket/vendor/autoload.php';

use React\MySQL\Factory;
use React\MySQL\QueryResult;

$loop = React\EventLoop\Factory::create();
$factory = new Factory($loop);
$pusher = new websocket01\Pusher($factory);

$webSock = new React\Socket\Server('0.0.0.0:8090', $loop);
$webServer = new Ratchet\Server\IoServer(
    new Ratchet\Http\HttpServer(
        new Ratchet\WebSocket\WsServer(
            new Ratchet\Wamp\WampServer(
                $pusher
            )
        )
    ),
    $webSock
);

$loop->run();

The Pusher then creates a lazy connection for each connected Interface in the constructor, and thats my second question: Is it better to create just one async db connection at all and keep it alive (like now), or is is better to reopen the connection at every client interaction ?

(Pusher.php):

    protected $connection;
    protected $subconnections = array();

    public function __construct($factory){
        //Main Websocket db
        $uri = 'localhost...';
        $this->connection = $factory->createLazyConnection($uri);

        //Create array with Connections to Sub dbs for every entry in the interface table
        $stream = $this->connection->queryStream('SELECT * from interfaces');

        $stream->on('data', function ($interface) use ($factory) {

                if (!array_key_exists($interface['interface_dbhost'], $this->subconnections)) {
                    $uri = "".$interface['interface_dbuser'].":".$interface['interface_dbpass']."@".$interface['interface_dbhost'].":".$interface['interface_dbport']."/".$interface['interface_dbname']."";

                    $this->subconnections[$interface['interface_dbhost']] = $factory->createLazyConnection($uri);
                }
        });
        $stream->on('end', function () {
            echo 'Completed.';
        });
    }

The main (local) database will still be used to save clients data, errors, verify tokens etc.

The right database connection will then be chosen in onopen, onsubscribe, onpublish... automatically out of the subconnections array, depending on the application the client comes from, and the action he wants to perfom, for e.g:

    public function onPublish(ConnectionInterface $conn, $conversation_id, $event, array $exclude, array $eligible) {
        $obj = json_decode($event);
        $interface = $obj->frontend;
        $channel = $obj->channel;

        $db = $this->selectDatabase($conversation_id, $interface); //Choose right database connection
        $clienthandler = new Clienthandler\Clienthandler($this->connection);

        $action = $this->processRequest($db,$obj, $conversation_id, $clienthandler, $operatorhandler, $channel);
    }

I'm also passing the local db connection to the clienthandler too, as I need it there to log actions.

    public function selectDatabase($conversation_id, $interface) {
            if(array_key_exists($interface, $this->subconnections)) {
                $dbconn = $this->subconnections[$interface];

                $dbconn->ping()->then(function () {
                    echo 'Connection alive' . PHP_EOL;
                }, function (Exception $e) {
                    echo 'Error: ' . $e->getMessage() . PHP_EOL;
                });
            }
        }
        return $dbconn;
    }

The clienthandler then saves the data and broadcasts it to all subscribers

Actually this is working perfect for now, but as I mentioned above, I'm not sure if this is the best way to do this if there are 1000+ Users connected at the same time.

I'm open for any advice :-)

Thank you for your help

opened by manuduxi 6

Concerns from Reddit

The first problem is that the parser for locating placeholders is literally strpos($sql, '?');. If you have a query with a literal ? in it then it will fail - e.g. "select col1, 'wat?' some_literal from table where id=?" then you will get an exception. This might not be a very common occurrence and indeed you may never run into it, but it show a great lack of attention to fundamental functionality.

Worse is the way that the values are then escaped. It's basically a direct reimplementation of mysql_escape_string(). Note I didn't say mysqli_escape_string, nor mysql_real_escape_string(). So this library is subject to all of the usual multibyte encoding pitfalls.
question help wanted

opened by WyriHaximus 5

Connection errors for lazy connections

Hi. Is there a canonical way to handle connection errors for lazy connections (eg an invalid connection string)?

I tried

        $database = (new \React\MySQL\Factory($loop))->createLazyConnection('die-horribly');
        $database->on(
            'error',
            function ($error) {
                // this error never happens
                die($error);
            }
        );
        $database->query('select database()')->done(
            function (QueryResult $result) {
                // cannot get to here because of the bad mysql dsn
                var_dump($result->resultRows);
            },
            function (\Throwable $ex) {
                // this is absorbed by the promise?
                throw $ex;
            }
        );

What I want is a way to catch the failed connection from any query anywhere.... (deeply nested)

thanks!

question

opened by pavarnos 5

Can not send more queries after 'Not supportted value type' exception

Hello

I have a bug in v 0.5.* and v0.6.*

While we have an invalid type of argument, we haven't drop current state of mysql, look at example, because exception does not catch

<?php


declare(strict_types=1);

require_once 'vendor/autoload.php';

$connection = $factory->createLazyConnection('root:[email protected]:3306/storage');
$connection->query("update table set value = ?", [new stdClass()]) // Invalid type of object
    ->otherwise(function ($e) use ($connection) {
        return $connection->query("select * from table where id = 1")
            ->then(function ($e) {
                echo 1; // thats never happened
            })
            ->otherwise(function ($e) use ($connection) {
                echo 1; // thats too
            });
    });

Look at this method: https://github.com/friends-of-reactphp/mysql/blob/c36b92f1ede95f49036b6ba1805736f80fa4d4e0/src/Io/Parser.php#L383

And its body (i do not know how to reply multiple lines of code, sorry, so paste my)

protected function nextRequest($isHandshake = false)
    {
        if (!$isHandshake && $this->phase != self::PHASE_HANDSHAKED) {
            return false;
        }

        if ($this->currCommand === null && !$this->executor->isIdle()) {
            $command = $this->executor->dequeue(); 
            $this->currCommand = $command; // current command set here

            if ($command instanceof AuthenticateCommand) {
                $this->phase = self::PHASE_AUTH_SENT;
                $this->sendPacket($command->authenticatePacket($this->scramble, $this->buffer));
            } else {
                $this->seq = 0;
                $this->sendPacket($this->buffer->buildInt1($command->getId()) . $command->getSql()); // Exception occurs here
            }
        }

        return true;
    }

Exception line never catched and just reject: https://github.com/friends-of-reactphp/mysql/blob/89067b22fef892b90670722c20368a647c5b5fc6/src/Io/Query.php#L119 https://github.com/friends-of-reactphp/mysql/blob/89067b22fef892b90670722c20368a647c5b5fc6/src/Io/Connection.php#L72

So we are cannot send more queries from its connection, cause do not drop $this->currCommand

question

opened by yyydevf 2

How to support duplicate column names?
How should we handle queries that use the same name column name multiple times?

-- Bob wins SELECT "Alice" AS name, "Bob" AS name -- Bob wins SELECT "Alice" AS name, user.* FROM (SELECT "Bob" AS name) AS user -- Alice wins SELECT user.*, "Alice" AS name FROM (SELECT "Bob" AS name) AS user

At the moment, the value from the last occurrence wins because we expose each row as an associative array (later values with the same key overwrite previous values).

Technically, the protocol exposes a list of all values, so we do have access to all values inside the protocol implementation, but do not currently expose this to the user. Other projects expose a "fetch mode" to control whether the data should be exposed as a plain list (PDO::FETCH_NUM) or as an associative array (PDO::FETCH_ASSOC) or object (PDO::FETCH_OBJ / PDO::FETCH_CLASS), etc. (https://www.php.net/manual/en/pdostatement.fetch.php)

The current behavior can be seen as inconvenient or inconsistent, given that QueryResult::$resultFields contains a list of all(!) column names, yet QueryResult::$resultRows[0] contains an associative array with unique column names only. If you run any of the above queries with the examples/01-query.php script, you'll see that it reports a table with 2 header columns, but only reports one value per row.

Providing this kind of API isn't too much work, but I wonder how big of a problem this actually is and whether this actually warrants increasing our API surface?
question help wanted
opened by clue 2
Simplify API, instantiate `MysqlClient` instead of `Factory`
There are plans to simplify the API by removing the Factory and instead replacing it with a simpler MysqlClient constructor. In other words, this:

$factory = new React\MySQL\Factory(); $db = $factory->createLazyConnection('alice:secret@localhost/bookstore'); $db->query('INSERT INTO book (title) VALUES (?)', ['Hello wörld!']);

Could potentially look like this:

$db = new React\Mysql\MysqlClient('alice:secret@localhost/bookstore'); $db->query('INSERT INTO book (title) VALUES (?)', ['Hello wörld!']);

Internally, the MysqlClient would work similar to what is currently called a "lazy" connection, but we'll probably get rid of this wording altogether. This means it would automatically create the underlying database connection only when it's actually needed and will close this connection after a short while when it is not in use anymore. What is currently called "idle" time would probably be reworded to be more of a "keepalive" with much lower defaults.

Additionally, we'll probably fire "open" and "close" events as needed (loosely inspired by https://github.com/clue/reactphp-eventsource). Unlike the current version, my idea is to also keep the instance in a usable state even after calling close() on it. Reusing the same instance at a later time makes a lot more sense in long-running applications instead of locking it in an unusable state.

This is obviously a BC break, but there are no plans to break any of the existing code besides this. Most consumers should be able to upgrade to the new instantiation without issues, actual query APIs etc. would not be affected.

Obviously the biggest hindrance: I'm not entirely sure about the casing of the namespace and class name (MySQL vs. Mysql).

The same API discussion also applies to SQLite (https://github.com/clue/reactphp-sqlite/issues/47) and others Builds on top of #134
new feature help wanted BC break
opened by clue 0
What about transactions?
Atomicity of operations is a vital part of any application. How do you handle transactions? I reckon, one can just make it this (simplified):

$conn->query("BEGIN") ->then(function() use ($conn) { $conn->query("INSERT/UPDATE ...") ->then(function() use ($conn) { $conn->query("COMMIT"); }); });

Would be nice to have this covered in docs.
question easy pick help wanted
opened by crocodile2u 9

Releases(v0.5.7)

v0.5.7(Sep 15, 2022)
Feature: Full support for PHP 8.2. (#161 by @clue)

Feature: Mark passwords and URIs as #[\SensitiveParameter] (PHP 8.2+). (#162 by @clue)

Feature: Forward compatibility with upcoming Promise v3. (#157 by @clue)

Feature / Fix: Improve protocol parser, emit parser errors and close invalid connections. (#158 and #159 by @clue)

Improve test suite, fix legacy HHVM build by downgrading Composer. (#160 by @clue)

Source code(tar.gz)
Source code(zip)
v0.5.6(Dec 14, 2021)
Feature: Support optional charset parameter for full UTF-8 support (utf8mb4). (#135 by @clue)

$db = $factory->createLazyConnection('localhost?charset=utf8mb4');

Feature: Improve error reporting, include MySQL URI and socket error codes in all connection errors. (#141 by @clue and #138 by @SimonFrings)

For most common use cases this means that simply reporting the Exception message should give the most relevant details for any connection issues:

$db->query($sql)->then(function (React\MySQL\QueryResult $result) { // … }, function (Exception $e) { echo 'Error:' . $e->getMessage() . PHP_EOL; });

Feature: Full support for PHP 8.1 release. (#150 by @clue)

Feature: Provide limited support for NO_BACKSLASH_ESCAPES SQL mode. (#139 by @clue)

Update project dependencies, simplify socket usage, and improve documentation. (#136 and #137 by @SimonFrings)

Improve test suite and add .gitattributes to exclude dev files from exports. Run tests on PHPUnit 9 and PHP 8 and clean up test suite. (#142 and #143 by @SimonFrings)

Source code(tar.gz)
Source code(zip)
v0.5.5(Jul 19, 2021)
Feature: Simplify usage by supporting new default loop. (#134 by @clue)

// old (still supported) $factory = new React\MySQL\Factory($loop); // new (using default loop) $factory = new React\MySQL\Factory();

Improve test setup, use GitHub actions for continuous integration (CI) and fix minor typo. (#132 by @SimonFrings and #129 by @mmoreram)

Source code(tar.gz)
Source code(zip)
v0.5.4(May 21, 2019)
Fix: Do not start idle timer when lazy connection is already closed. (#110 by @clue)

Fix: Fix explicit close() on lazy connection when connection is active. (#109 by @clue)

Source code(tar.gz)
Source code(zip)
v0.5.3(Apr 3, 2019)
Fix: Ignore unsolicited server error when not executing any commands. (#102 by @clue)

Fix: Fix decoding URL-encoded special characters in credentials from database connection URI. (#98 and #101 by @clue)

Source code(tar.gz)
Source code(zip)
v0.5.2(Feb 5, 2019)
Fix: Fix ConnectionInterface return type hint in Factory. (#93 by @clue)

Minor documentation typo fix and improve test suite to test against PHP 7.3, add forward compatibility with PHPUnit 7 and use legacy PHPUnit 5 on HHVM. (#92 and #94 by @clue)

Source code(tar.gz)
Source code(zip)
v0.5.1(Jan 12, 2019)
Fix: Fix "bad handshake" error when connecting without database name. (#91 by @clue)

Source code(tar.gz)
Source code(zip)
v0.5.0(Nov 28, 2018)
A major feature release with a significant API improvement!

This update does not involve any BC breaks, but we figured the new API provides significant features that warrant a major version bump. Existing code will continue to work without changes, but you're highly recommended to consider using the new lazy connections as detailed below.

Feature: Add new createLazyConnection() method to only connect on demand and implement "idle" timeout to close underlying connection when unused. (#87 and #88 by @clue)

// new $connection = $factory->createLazyConnection($url); $connection->query(…);

This method immediately returns a "virtual" connection implementing the ConnectionInterface that can be used to interface with your MySQL database. Internally, it lazily creates the underlying database connection only on demand once the first request is invoked on this instance and will queue all outstanding requests until the underlying connection is ready. Additionally, it will only keep this underlying connection in an "idle" state for 60s by default and will automatically end the underlying connection when it is no longer needed.

From a consumer side this means that you can start sending queries to the database right away while the underlying connection may still be outstanding. Because creating this underlying connection may take some time, it will enqueue all oustanding commands and will ensure that all commands will be executed in correct order once the connection is ready. In other words, this "virtual" connection behaves just like a "real" connection as described in the ConnectionInterface and frees you from having to deal with its async resolution.

Feature: Support connection timeouts. (#86 by @clue)

Source code(tar.gz)
Source code(zip)
v0.4.1(Oct 18, 2018)
Feature: Support cancellation of pending connection attempts. (#84 by @clue)

Feature: Add warningCount to QueryResult. (#82 by @legionth)

Feature: Add exception message for invalid MySQL URI. (#80 by @CharlotteDunois)

Fix: Fix parsing error message during handshake (Too many connections). (#83 by @clue)

Source code(tar.gz)
Source code(zip)
v0.4.0(Sep 21, 2018)
A major feature release with a significant documentation overhaul and long overdue API cleanup!

This update involves a number of BC breaks due to various changes to make the API more consistent with the ReactPHP ecosystem. In particular, this now uses promises consistently as return values instead of accepting callback functions and this now offers an additional streaming API for processing very large result sets efficiently.

We realize that the changes listed below may seem a bit overwhelming, but we've tried to be very clear about any possible BC breaks. See below for changes you have to take care of when updating from an older version.

Feature / BC break: Add Factory to simplify connecting and keeping connection state, mark Connection class as internal and remove connect() method. (#64 by @clue)

// old $connection = new Connection($loop, $options); $connection->connect(function (?Exception $error, $connection) { if ($error) { // an error occured while trying to connect or authorize client } else { // client connection established (and authenticated) } }); // new $factory = new Factory($loop); $factory->createConnection($url)->then( function (ConnectionInterface $connection) { // client connection established (and authenticated) }, function (Exception $e) { // an error occured while trying to connect or authorize client } );

Feature / BC break: Use promises for query() method and resolve with QueryResult on success and and mark all commands as internal and move its base to Commands namespace. (#61 and #62 by @clue)

// old $connection->query('CREATE TABLE test'); $connection->query('DELETE FROM user WHERE id < ?', $id); $connection->query('SELECT * FROM user', function (QueryCommand $command) { if ($command->hasError()) { echo 'Error: ' . $command->getError()->getMessage() . PHP_EOL; } elseif (isset($command->resultRows)) { var_dump($command->resultRows); } }); // new $connection->query('CREATE TABLE test'); $connection->query('DELETE FROM user WHERE id < ?', [$id]); $connection->query('SELECT * FROM user')->then(function (QueryResult $result) { var_dump($result->resultRows); }, function (Exception $error) { echo 'Error: ' . $error->getMessage() . PHP_EOL; });

Feature / BC break: Add new queryStream() method to stream result set rows and remove undocumented "results" event. (#57 and #77 by @clue)

$stream = $connection->queryStream('SELECT * FROM users'); $stream->on('data', function ($row) { var_dump($row); }); $stream->on('end', function () { echo 'DONE' . PHP_EOL; });

Feature / BC break: Rename close() to quit(), use promises for quit() method and add new close() method to force-close the connection. (#65 and #76 by @clue)

// old: soft-close/quit $connection->close(function () { echo 'closed'; }); // new: soft-close/quit $connection->quit()->then(function () { echo 'closed'; }); // new: force-close $connection->close();

Feature / BC break: Use promises for ping() method and resolve with void value on success. (#63 and #66 by @clue)

// old $connection->ping(function ($error, $connection) { if ($error) { echo 'Error: ' . $error->getMessage() . PHP_EOL; } else { echo 'OK' . PHP_EOL; } }); // new $connection->ping(function () { echo 'OK' . PHP_EOL; }, function (Exception $error) { echo 'Error: ' . $error->getMessage() . PHP_EOL; });

Feature / BC break: Define events on ConnectionInterface (#78 by @clue)

BC break: Remove unneeded ConnectionInterface methods getState(), getOptions(), setOptions() and getServerOptions(), selectDb() and listFields() dummy. (#60 and #68 by @clue)

BC break: Mark all protocol logic classes as internal and move to new Io namespace. (#53 and #62 by @clue)

Fix: Fix executing queued commands in the order they are enqueued (#75 by @clue)

Fix: Fix reading all incoming response packets until end (#59 by @clue)

[maintenance] Internal refactoring to simplify connection and authentication logic (#69 by @clue)

[maintenance] Internal refactoring to remove unneeded references from Commands (#67 by @clue)

[maintenance] Internal refactoring to remove unneeded EventEmitter implementation and circular references (#56 by @clue)

[maintenance] Refactor internal parsing logic to separate Buffer class, remove dead code and improve performance (#54 by @clue)

Source code(tar.gz)
Source code(zip)
v0.3.3(Jun 18, 2018)
Fix: Reject pending commands if connection is closed (#52 by @clue)

Fix: Do not support multiple statements for security and API reasons (#51 by @clue)

Fix: Fix reading empty rows containing only empty string columns (#46 by @clue)

Fix: Report correct field length for fields longer than 16k chars (#42 by @clue)

Add quickstart example and interactive CLI example (#45 by @clue)

Source code(tar.gz)
Source code(zip)
v0.3.2(Apr 4, 2018)
Fix: Fix parameter binding if query contains question marks (#40 by @clue)

Improve test suite by simplifying test structure, improve test isolation and remove dbunit (#39 by @clue)

Source code(tar.gz)
Source code(zip)
v0.3.1(Mar 26, 2018)
Feature: Forward compatibility with upcoming ReactPHP components (#37 by @clue)

Fix: Consistent connect() behavior for all connection states (#36 by @clue)

Fix: Report connection error to connect() callback (#35 by @clue)

Source code(tar.gz)
Source code(zip)
v0.3.0(Mar 13, 2018)
This is now a community project managed by @friends-of-reactphp. Thanks to @bixuehujin for releasing this project under MIT license and handing over! (#12 and #33 by @bixuehujin and @clue)

Feature / BC break: Update react/socket to v0.8.0 (#21 by @Fneufneu)

Feature: Support passing custom connector and load system default DNS config by default (#24 by @flow-control and #30 by @clue)

Feature: Add ConnectionInterface with documentation (#26 by @freedemster)

Fix: Last query param is lost if no callback is given (#22 by @Fneufneu)

Fix: Fix memory increase (memory leak due to keeping incoming receive buffer) (#17 by @sukui)

Improve test suite by adding test instructions and adding Travis CI (#34 by @clue and #25 by @freedemster)

Improve documentation (#8 by @ovr and #10 by @RafaelKa)

Source code(tar.gz)
Source code(zip)
v0.2.0(Oct 15, 2014)

Now compatible with reactphp 0.4.*
Source code(tar.gz)
Source code(zip)
v0.1.0(Oct 15, 2014)

This release depends on reactphp 0.3.
Source code(tar.gz)
Source code(zip)