crate / crate-pdo

CrateDB PHP PDO adapter

CrateDB PDO Adapter

The CrateDB PDO adapter is a CrateDB specific database driver implementation of the PDO API.

This adapter allows you to use the standardized PDO API you're already familiar with when developing PHP applications with a CrateDB database.

Prerequisites

You need to be using PHP and Composer.

Installation

The CrateDB PDO adapter is available as a Composer package. Install it like:

composer require crate/crate-pdo

See the installation documentation for more info.

Contributing

This project is primarily maintained by Crate.io, but we welcome community contributions!

See the developer docs and the contribution docs for more information.

Help

Looking for more help?

• Read the project docs
• Check out our support channels

crate-pdo's Issues

0-based parameter binding not supported

Hi, line below:

https://github.com/crate/crate-pdo/blob/0.6.2/src/Crate/PDO/PDOStatement.php#L200

needs to be changed to:

$zero_based = array_key_exists(0, $input_parameters_array);

or when sending a query with first value being NULL an exception will be thrown.

Regards,
Chris

Constants must be pulled from `PDOCrateDB::` namespace?

About

@hlcianfagna discovered that addressing constants through PDO:: is no longer viable, one must use PDOCrateDB:: instead.

• #151 (comment)

Thoughts

It may have happened on the update to PHP 8.

• GH-115

Add compatibility with PHP 8.0

Hi there,

PHP 8.0.2 has been released and PHP 8.1.x is in the making. We might want to follow that. After adding this improvement here, we should follow up at crate/crate-dbal#105.

At the same time, we might want to remove support for PHP 7.2, as it reached end of life (EOL) [1,2]. Because some signatures of the PDO interface have changed, this will probably make the support of PHP 7.3, PHP 7.4 and PHP 8.x easier.

With kind regards,
Andreas.

[1] https://www.php.net/supported-versions.php
[2] https://www.zend.com/blog/php-72-eol

Some INSERTs seem to fail randomly

CrateDB version: 2.0.6

JVM version: OpenJDK 1.8.0_131

OS version / environment description: CentOS 7 (3.10.0-514.26.2.el7.x86_64)

Problem description:
Using PHP, some INSERT statements seem to fail randomly.

Relations are to be inserted, and it seems like about 50% of the queries pass, while the rest fails.

The error message is:
Crate Prepare Error: Code = 4000, Message = SQLActionException[SQLParseException: line 1:number: no viable alternative at input ',']

If you copy the queries without any change into the console in the AdminUI, every query succeeds.

The driver we use is the crate database driver (crate-pdo-0.6.3 (composer package)).

Steps to reproduce:
CREATE TABLE IF NOT EXISTS "company"."relation" (
"id_from" STRING,
"id_to" STRING,
PRIMARY KEY ("id_from", "id_to")
)
CLUSTERED BY ("id_from") INTO 8 SHARDS
WITH (
"blocks.metadata" = false,
"blocks.read" = false,
"blocks.read_only" = false,
"blocks.write" = false,
column_policy = 'strict',
number_of_replicas = '1',
"recovery.initial_shards" = 'quorum',
refresh_interval = 1000,
"routing.allocation.enable" = 'all',
"routing.allocation.total_shards_per_node" = -1,
"translog.durability" = 'REQUEST',
"translog.flush_threshold_size" = 536870912,
"translog.sync_interval" = 5000,
"unassigned.node_left.delayed_timeout" = 60000,
"warmer.enabled" = true,
"write.wait_for_active_shards" = 'all'
)

INSERT that SUCCEEDS:
INSERT INTO company.relation (id_from, id_to) VALUES ('413462662023162', '1691996757702397'), ('413462662023162', '359532474088827'), ('413462662023162', '1591614901072190');

INSERT that FAILS:
INSERT INTO company.relation (id_from, id_to) VALUES ('413462662023162', '1691996757702397'), ('413462662023162', '359532474088827'), ('413462662023162', '1591614901072190'), ('413462662023162', '42199325465'), ('413462662023162', '440195839442412'), ('413462662023162', '106197822745388');

High number of database connections

Hi there,

users of this library reported about a high number of database connections to CrateDB. They are using it in a high-volume data ingest environment.

There seems to be a ATTR_PERSISTENT attribute ¹, which, under normal circumstances, would keep a connection open. But in the CrateDB PHP PDO adapter, it dumps that option if it finds it configured.

What we want ideally is connection pooling, that is, for connections to be reused to save all the overhead of creating new connections for each and every database conversation.

With kind regards,
Andreas.

/cc @hlcianfagna, @hammerhead

Footnotes

1. PHP PDO » Connections and Connection management ↩

The function `Crate\PDO\PDOStatement::fetchAll()` has been deprecated?

Hi there,

at ¹, the code quality checker reports some deprecation about the PDOStatement::fetchAll() function. Is it right?

The function Crate\PDO\PDOStatement::fetchAll() has been deprecated: Use fetchAllNumeric(), fetchAllAssociative() or fetchFirstColumn() instead.

IssueId: 46905417  Message: The function ``Crate\PDO\PDOStatement::fetchAll()`` has been deprecated: Use fetchAllNumeric(), fetchAllAssociative() or fetchFirstColumn() instead.  Filename: src/Crate/PDO/PDOStatement.php  LineNumber: 619  Link: https://scrutinizer-ci.com/g/crate/crate-pdo/code-structure/main/class/Crate%5CPDO%5CPDOStatement?issueId=46905417

With kind regards,
Andreas.

Footnotes

1. https://scrutinizer-ci.com/g/crate/crate-pdo/code-structure/main/class/Crate%5CPDO%5CPDOStatement ↩

PDO bindParam should support named parameters with leading colon :

Consider the following statement:

    INSERT INTO `doc`.`tweets` (`id`, `text`, `user`) VALUES (:qp0, :qp1, :qp2)

When I prepare the statement, the value of namedToPositionalParams is:

    array(3) {
    ["qp0"]=>
    int(0)
    ["qp1"]=>
    int(1)
    ["qp2"]=>
    int(2)
    }

When I call:

    $pdoStatement->bindParam(":qp0", $value, $dataType);

My statement fails with:

    Exception\UnsupportedException("0-based parameter binding not supported, use 1-based");

This is because of line 277 of PDOStatement.php:

    if (array_key_exists($parameter, $this->namedToPositionalMap))

The keys in namedToPositionalParams should have leading colons, such as [":qp0"=>0, ":qp1"=>1, ":qp2"=>2]. http://php.net/manual/en/pdostatement.bindparam.php states: Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name

This can be fixed by moving the pattern's capturing group to include the colon on line 109 of PDOStatement.php:

    $pattern = '/(:(?:[\w|\d|_](?=([^\'\\\]*(\\\.|\'([^\'\\\]*\\\.)*[^\'\\\]*\'))*[^\']*$))*)/';

Quote the string param

Hi,

the driver throws exception if anyone tries to use the quote method with string parameter.

Is this something that can be changed or is a no-go?

The problem is that laravel's debugbar uses quote() methods to render the complete SQL and fails with "This is not supported" exception. It basically quotes each of the sql params and puts it back in the SQL so that the user sees full SQL.

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

Support for object fetch style

The code currently does not support fetching results with style PDO::FETCH_OBJ. It would be really nice to have support for this added. I appreciate that it's not a major issue though.

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

Driver silently dumps all but first server in DSN

The docs suggest that you should be able to use the driver with multiple Crate servers by using a DSN of the form:

crate:c1.example.com:4200,c2.example.com:4200,c3.example.com:4200

However, the driver just discards all but the first server and uses that for all connections, which happens here in the parseDSN() function. I guess its a case of the docs getting ahead of the functionality? I was building an application that assumed the driver would handle fail-over for me only to discover it wouldn't.

I think that the documentation should definitely be updated to reflect that the driver can't handle multi-server DSNs. It would be nice if the driver did behave as promised though. I'll try to put some time into it a little later.

Implement "PDO::query()" parameters of new PHP PDO interface

With the updates to the PHP PDO interface, query() operations using fetch styles/modes are now possible directly on the main PDO object reference. In earlier versions, a PDOStatement was needed for that.

While this patch already brings in PHP8 compatibility in general, this specific detail has not been implemented yet.

Originally posted by @amotl in #116 (comment)

Bump guzzle version to 7

Hi all,

laravel 8 requires guzzle 7. Any chance you can bump the required guzzle to 7 as well?

Driver is extremenly slow

Hi,

I am testing crate-pdo performance and it seems extremely slow. Doing 10 000 INSERTs statements to table as below:

CREATE TABLE cdr (
  id long primary key,
  live_id long,
  partner_id integer,
  customer_id integer,
  user_id integer,
  term_route_id integer,
  term_gw_id integer,
  currency_iso string,
  server_id integer,
  type string,
  calldate timestamp,
  clid string,
  src_id string,
  src string,
  src_ua string,
  src_contact string,
  src_call_id string,
  src_codec string,
  dst_id string,
  dst string,
  dst_name string,
  dst_ua string,
  dst_contact string,
  dst_call_id string,
  dst_codec string,
  src_channel string,
  dst_channel string,
  duration integer,
  billsec integer,
  disposition string,
  destination string,
  context string,
  rate float,
  charge float,
  charge_gw float,
  t_cause string
) 

using crate-pdo takes 413.2529 sec. When doing the same 10 000 INSERTs using simple code below:

class Level7Crate
{
    /**
     * Sends SQL to Crate.io server
     * 
     * @param string $server
     * @param int $port
     * @param string $sql
     * 
     * return json response
     */
    public static function sendSQL($server, $port, $sql)
    {        
        $json = json_encode(array(
            "stmt" => $sql
        ));

        $opts = array('http' =>
            array(
                'method'  => 'POST',
                'header'  => 'Content-type: application/x-www-form-urlencoded',
                'content' => $json
            )
        );

        $context  = stream_context_create($opts);

        if (!$result = file_get_contents('http://'.$server.':'.$port.'/_sql', false, $context)) {
            throw new Exception("No result found");
        }

        if (!$json = json_decode($result)) {
            throw new Exception("Failed to json_decode response");
        }

        return $json;
    }

}

reduces time required to populate the table to 17.2668 sec.

Regards,
Chris

Remove dependency on guzzle 5.x.

If you already have guzzle 6.x installed, and try to install crate-pdo, composer complains that it can't install guzzle-5.x because guzzle-6.x is already installed.

$ composer require crate/crate-pdo:~0.3.1
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

Problem 1
- Installation request for crate/crate-pdo ~0.3.1 -> satisfiable by crate/crate-pdo[0.3.1].
- Conclusion: remove guzzlehttp/guzzle 6.2.0
- Conclusion: don't install guzzlehttp/guzzle 6.2.0
- crate/crate-pdo 0.3.1 requires guzzlehttp/guzzle ~5.0 -> satisfiable by guzzlehttp/guzzle[5.0.0, 5.0.1, 5.0.2, 5.0.3, 5.1.0, 5.2.0, 5.3.0].
- Can only install one of: guzzlehttp/guzzle[5.3.0, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.0.0, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.0.1, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.0.2, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.0.3, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.1.0, 6.2.0].
- Can only install one of: guzzlehttp/guzzle[5.2.0, 6.2.0].
- Installation request for guzzlehttp/guzzle == 6.2.0.0 -> satisfiable by guzzlehttp/guzzle[6.2.0].

Installation failed, reverting ./composer.json to its original content.

Driver hangs forever on unreachable IP

If one of the first servers specified in the list of servers to try is on an unreachable IP address, then the PDO driver hangs on this first connection attempt and doesn't proceed to try the other servers as would be expected. To be fair, the connection does eventually time out after something like a minute but upstream timeouts have long given up on the request by that point.

This might seem far fetched but has happened to me recently on a production deployment where an interface on the server (needed to reach the IP space of the Crate server) failed. This hung the PHP application rather than moving onto other accessible Crate servers in the list as I would have expected. Part of the reason for using Crate was for this fail-over potential in these cases so this was a big deal to us.

I think it may be a case of a connection timeout being set to a very high number, not configurable via the API somewhere in the code base but I'm not sure where. I noticed you aren't setting the connect_timeout key anywhere in the code base and this defaults to "forever". However, changing this didn't seem to help in my case.

Examples of INSERT/UPDATE object/array/geo_point

It would be great to see some examples on how to use this library in PHP to INSERT/UPDATE non-standard fields like object, array, and geo_point . Thanks!

PDO::PARAM_STR - This is not supported, please use prepared statements.

I'm using Crate with the Laravel Eloquent ORM and I'm having problems PDO::PARAM_STR.
For now I replaced

throw new Exception\UnsupportedException('This is not supported, please use prepared statements.');

with:

return str_replace( "'", "''", $string );

And that seemed to work for my current use case.

Is there a way to fully support PDO::PARAM_STR?

Cheers!

Improving efficiency with bulk inserts

Hi again,

as a followup to GH-138, users of this library also reported flaws on the aspect of running bulk data imports.

We batch together multiple messages, and then translate them into bulk insert statements. Are there any limits we should bear in mind, for doing bulk inserts?

After analyzing a wire dump of database conversations with CrateDB ¹, it looks like this driver is not using bulk_args ² yet.

With kind regards,
Andreas.

/cc @hlcianfagna, @hammerhead

Footnotes

1. sudo ngrep -d lo0 -Wbyline port 4200 ↩

2. CrateDB HTTP bulk operations interface ↩

Interface "Crate\PDO\PDOInterface" not found

Hi I have an issue with crate-pdo. It seems like if you use composer dump-autoload -a the PDOInterface and the class alias in it can't be resolved.

For now we are switching to the -o optimization but it would be good to have a dedicated version for 8.x. As I don't see any other workaround after googling a bit.

No communication with crate.io server

Hi,

When trying to run simple script as below:

#!/usr/bin/php -q
<?php

require '../../vendor/autoload.php';

try {
  $dbh = new \Crate\PDO\PDO('crate:192.168.10.40:4200', null, null, []);
  foreach($dbh->query('SELECT * from testtable') as $row) {
      print_r($row);
  }
} catch (Exception $e) {
    echo $e->getMessage();
}

all I am getting is two PHP Deprecated errors

PHP Deprecated:  Amp\ReactorFactory is deprecated and scheduled for removal. Please update code to use the Amp\getReactor() function instead. in /enc/var/www/crate-pdo/vendor/amphp/amp/lib/ReactorFactory.php on line 16
PHP Deprecated:  Amp\Promise::wait() is deprecated and scheduled for removal. Please update code to use Amp\wait($promise) instead. in /enc/var/www/crate-pdo/vendor/amphp/amp/lib/Future.php on line 65

and script hangs without producting any results (I need to terminate it with Ctrl + C).

When watching network traffic on port 4200 there are no packets transmitted.

Regards,
Chris

Error with: SELECT cast(id as string) AS id, *

Crate-PDO: verison 0.6.1

Not an important issue as there is a quick workaround for it.

SELECT cast(id as string) AS id, * FROM mytable LIMIT 1
This query causes this error:
Message: array_combine(): Both parameters should have an equal number of elements
Filename: PDO/PDOStatement.php
Line Number: 399

Workaround is to use another name for the 'id' field :
SELECT cast(id as string) AS id_str, * FROM mytable LIMIT 1

exec method is mangling SQL syntax

What I would expect from the code below:

$crate = new \Crate\PDO\PDO('crate:192.168.10.40:4200', null, null, []);
$crate->exec("INSERT INTO test VALUES ('a:b')");

is to send to Crate.io server SQL query INSERT INTO test VALUES ('a:b'), what it sends instead is INSERT INTO test VALUES ('a?'), see output of ngrep below:

ngrep -d any -W byline port 4200
interface: any
filter: (ip or ip6) and ( port 4200 )
###
T 192.168.10.4:33061 -> 192.168.10.40:4200 [AP]
POST /_sql HTTP/1.1.
Content-Length: 51.
Accept-Encoding: gzip, identity.
Host: 192.168.10.40:4200.
User-Agent: Amp\Artax/1.0.0-dev (PHP).
Accept: */*.
.

##
T 192.168.10.4:33061 -> 192.168.10.40:4200 [AP]
{"stmt":"INSERT INTO test VALUES ('a?')","args":[]}
##
T 192.168.10.40:4200 -> 192.168.10.4:33061 [AP]
HTTP/1.1 200 OK.
Content-Type: application/json; charset=UTF-8.
Content-Length: 47.
.
{"cols":[],"duration":2,"rows":[],"rowcount":1}

and as a result what I endup in test table is:

select * from test limit 100; -> a?

what I would expect is:

select * from test limit 100; -> a:b

Regards,
Chris