Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

author
message
[레벨:0]invisible73

Post subject: Can't use shard with PHP

registered: 05/19/2013

아이피: *.226.189.236

views: 6

Hello.

I try to insert data into shards with PHP, but connect using PHP PDO with error:

Error: SQLSTATE[HY000] [-10100] CAS, Unknown error
Code: 4294957196

PHP code:

try {
    $db = new PDO('cubrid:dbname=shard1;host=localhost;port=45511', 'shard', 'shard123' );
    echo "PDO connection created ok!"." ";
} catch(PDOException $e) {
   echo "Error: ".$e->getMessage()." "."Code: ".$e->getCode()." ";
}

But with JAVA example ( http://www.cubrid.org/manual/91/en/shard.html ) everything works fine. JAVA code:

connection = DriverManager.getConnection("jdbc:cubrid:localhost:45511:shard1:::?charSet=utf8", "shard", "shard123");

If in PHP i use port 30000 - everything works fine, data inserted, but without sharding.

What's the problem?

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.82

Hello invisible73!

This is to inform that I have seen your post and I'm looking into your issue. I will reply to you after I try and replicate your problem.

Quote
[레벨:8]CUBRID

# Post subject:Re: Can't use shard with PHP

profile

registered: 03/28/2010

아이피: *.181.249.254

Hello,

The error -10100 in CUBRID 9.1 (or -1100 in CUBRID 8.4.3) means Operation Not Supported.

We have managed to replicate your problem and we will work on finding on workaround. We apologize for the inconvenience. The PDO Driver currently can only connect to the broker (running on port 33000) or query editor (running on 30000). It cannot connect to the sharding port.

As soon as we find a solution for the problem, we will let you know, so that you can use the PDO driver with sharding.

Regards,

The CUBRID Team

Quote
[레벨:0]invisible73

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/19/2013

아이피: *.226.189.236

ok

I try to insert data into shards with PHP by other ways:

$conn = cubrid_connect('localhost', 45511, 'sharddb', 'shard', 'shard123');
if (!$conn) die('Connect Error ('. cubrid_error_code() .')' . cubrid_error_msg());
for ($i = 0; $i<1024; $i++) {
   $sql = "INSERT INTO tbl_users VALUES (/*+ shard_key */ ?, ?, ?)";
   $req = cubrid_prepare($conn, $sql);
   $age = (int)(($i % 64)+10);
   $name = 'name_'.$i;
   cubrid_bind($req, 1, $i);
   cubrid_bind($req, 2, $name);
   cubrid_bind($req, 3, $age);
   cubrid_execute($req);
}
cubrid_disconnect($conn); 

MySQL code:

$mysqli = new mysqli('localhost', 'shard', 'shard123', 'sharddb', 45511);
if ($mysqli->connect_error) die('Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
for ($i = 0; $i<1024; $i++) {
   $stmt = $mysqli->prepare('INSERT INTO tbl_users VALUES (/*+ shard_key */ ?, ?, ?)');
   $age = (int)(($i % 64)+10);
   $name = 'name_'.$i;
   $stmt->bind_param('isi', $i, $name, $age);
   $stmt->execute();
   $stmt->close();
}
$mysqli->close();

shard.conf

[shard]
MASTER_SHM_ID           =45501
ADMIN_LOG_FILE          =log/broker/cubrid_broker.log

[%shard1]
SERVICE                 =ON
BROKER_PORT             =45511
MIN_NUM_APPL_SERVER     =1
MAX_NUM_APPL_SERVER     =1
APPL_SERVER_SHM_ID      =45511
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =ON
SHARD_DB_NAME                   =sharddb
SHARD_DB_USER                   =shard
SHARD_DB_PASSWORD               =shard123
APPL_SERVER                     = CAS_MYSQL
MIN_NUM_PROXY           =1
MAX_NUM_PROXY           =1
PROXY_LOG_DIR          =log/broker/proxy_log
PROXY_LOG               =ALL
MAX_CLIENT              =10
METADATA_SHM_ID         =45591
SHARD_CONNECTION_FILE   =shard_connection.txt
SHARD_KEY_FILE                  =shard_key.txt

shard_connection.txt

# mysql
0               sharddb                 localhost:3306
1               sharddb1                localhost:3306
2               sharddb2                localhost:3306
3               sharddb3                localhost:3306

shard_key.txt

[%shard_key]
0       63      0
64      127     1
128     191     2
192     255     3

Data inserted, BUT without sharding (data inserted in one database "sharddb"). Can I use CUBRID sharding with PHP?

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.82

Can I use CUBRID sharding with PHP?

Since CUBRID PDO driver shares the implementation with CUBRID PHP driver, they both don't support CUBRID SHARD. We will release a patch for it soon.

I have registered this issue to http://jira.cubrid.org/browse/APIS-546. Thank you for reporting about it.

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.67

We have fixed this issue. We will run the code through QA and if it passes, we will release it soon.

Quote
[레벨:0]invisible73

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/19/2013

아이피: *.226.189.236

Hello

I try to use http://pecl.php.net/get/CUBRID-9.1.0.0003.tgz , but it still not working ( Error: SQLSTATE[HY000] [-10100] CAS, Unknown error, code: 4294957196 )

 

log/broker/proxy_logshard1_1.log

 

06/18 17:15:14.620 [SRD] ../../src/broker/shard_proxy_io.c(1160): New socket io created. (fd:12).

06/18 17:15:14.620 [SRD] ../../src/broker/shard_proxy_handler.c(1239): New context created. context(cid:3, uid:4, is_busy:Y, is_in_tran:N, is_prepare_for_execute:N, free_on_end_tran:N, free_on_client_io_write:N, free_context:N, is_client_in$

06/18 17:15:14.620 [SRD] ../../src/broker/shard_proxy_io.c(2733): New client connected. client(client_id:3, is_busy:Y, fd:12, ctx_cid:3, ctx_uid:4).

06/18 17:15:14.621 [DBG] ../../src/broker/shard_proxy_handler.c(336): send error(msg) to the client. (error_ind:-1, error_code:-10100, errro_msg:-)

06/18 17:15:14.621 [DBG] ../../src/broker/shard_proxy_io.c(1193): Close socket. (fd:12).

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.67

Hi invisible73,

I will test your case, then will get back to you.

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.66

Hi, invisible73,

I've found the cause of your issue. The problem was that cubrid_execute() under the hood used a new API (cci_prepare_and_execute()) supported by CUBRID Broker (not SHARD Broker) which was not (yet) implemented in SHARD Broker. This is why you were getting 10100 Error meaning "Not Implemented". We have fixed this issue yesterday. We will soon release a new version of the PHP driver.

The good news is that you can still use the current version CUBRID PHP 9.1.0.0003, but instead of using cubrid_execute(), use cubrid_query(). It doesn't depend on cci_prepare_and_execute(), so you can execute queries successfully.

In addition, I've just pushed to cubrid-shard-demo Github repo an example which shows how to use the PHP driver to connect to and query CUBRID SHARD which is configured with MySQL as a backend database. The actual PHP code is located in /mysql/php directory.

If you follow the README on the main page, you can quickly configure a multi VM environment with CUBRID SHARD and two MySQL shard databases installed.

Let me know if you have other questions. I will be glad to help you.

Quote
[레벨:0]kshvakov

# Post subject:Re: Can't use shard with PHP

profile

registered: 06/19/2013

아이피: *.226.189.236

In current version CUBRID PHP 9.1.0.0003 if use cubrid_query() - "OK"

 

When using PDO error occurred while connecting to the database

<?php

$db_name  = "shard_1";
$host         = "localhost";
$port         = "45061";
$username = "shard";
$password = "shard123";

$conn = cubrid_connect($host, $port, $db_name, $username, $password);

if ($conn)
{
echo 'cubrid_connect success' . PHP_EOL;
}
else
{
exit('Connect Error (' . cubrid_error_code() . ')' . cubrid_error_msg()) . PHP_EOL;
}

try
{

$db = new PDO("cubrid:dbname=" . $db_name . ";host=" . $host . ";port=" . $port, $username, $password);

echo 'Cubrid PDO connect success' . PHP_EOL;
}
catch(PDOException $e)
{
echo $e->getMessage() . PHP_EOL;
}

 

php CubridShard.php
cubrid_connect success
SQLSTATE[HY000] [-10100] CAS, Unknown error

Quote
[레벨:3]Esen Sagynov

# Post subject:Re: Can't use shard with PHP

profile

registered: 05/31/2011

아이피: *.91.139.66

What PDO version are you using? Is it the latest PDO 9.1.0.002?

Quote
[레벨:0]kshvakov

# Post subject:Re: Can't use shard with PHP

profile

registered: 06/19/2013

아이피: *.226.189.236

Ooops,sory i'm run script on machine with PDO 9.1.0.001 driver ) 9.1.0.002 - OK

Quote
[레벨:0]kshvakov

# Post subject:Re: Can't use shard with PHP

profile

registered: 06/19/2013

아이피: *.226.189.236

Shrding work

 

for ($i=1; $i < 1000; $i++)
{
$stmt = $db->prepare('INSERT INTO `code` (`f_name`,`s_name`) VALUES (/*+ shard_key */ :f_name, :s_name)');

$stmt->bindParam(':f_name', $i, PDO::PARAM_INT);
$stmt->bindParam(':s_name', md5(microtime()), PDO::PARAM_STR);
$stmt->execute();
}

mysql> select count(*) from  shard_2.code;  select count(*) from  shard_1.code;
+----------+
| count(*) |
+----------+
|      872 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|      127 |
+----------+
1 row in set (0.00 sec)

 

Not work

 

for ($i=1; $i < 1000; $i++)
{
$stmt = $db->prepare('INSERT INTO `code` (`f_name`,`s_name`) VALUES (/*+ shard_key */ :f_name, :s_name)');

$stmt->execute(array(':f_name' => $i, ':s_name' => md5(microtime())));
}


mysql> truncate shard_1.code; truncate shard_2.code;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from  shard_2.code;  select count(*) from  shard_1.code;
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)



for ($i=1; $i < 1000; $i++)
{
$stmt = $db->prepare('INSERT INTO `code` (`f_name`,`s_name`) VALUES (/*+ shard_key */ ?, ?)');

$stmt->execute(array(
$i,
md5(microtime())
)
);

}


mysql> truncate shard_1.code; truncate shard_2.code;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from  shard_2.code;  select count(*) from  shard_1.code;
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


Quote




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: