Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

ERROR when using CUBRID SHARD with MySQL as backend database

CUBRID Official Forum » Programming Languages » Perl API » ERROR when using CUBRID SHARD with MySQL as backend database

author
message
[Level:3]Esen Sagynov

Post subject: ERROR when using CUBRID SHARD with MySQL as backend database

registered: 05/31/2011

IP: *.91.139.66

views: 3

Hi,

I'm Jesse. Today, I configured CUBRID SHARD to use MySQL as a backend database.

  • I use two shards.
  • CUBRID version: 9.0.0.0478
  • Client: Perl driver

CUBRID SHARD is started successfully:

ps x:
13633 ?        Sl     0:00 shard_broker
13640 ?        S      0:00 shard_shard_proxy_1
13641 ?        S      0:00 shard_shard_cas_mysql_1_0_1
13643 ?        S      0:00 shard_shard_cas_mysql_1_1_1

netstat:
tcp        0      0 0.0.0.0:45011               0.0.0.0:*                   LISTEN      13633/shard_broker
tcp        0      0 10.96.5.112:38267           10.96.5.112:3306            ESTABLISHED 13641/shard_shard_c
tcp        0      0 10.96.5.112:38069           10.96.5.113:3306            ESTABLISHED 13643/shard_shard_c

Code:

!/usr/bin/perl

use strict;
use DBI;
use Data::Dumper;

# Connect to the database.
my $dbh = DBI->connect (
                "DBI:cubrid:database=db_sample1;host=localhost;port=45011", "jesse", "jesse123",
                {RaiseError => 1, AutoCommit => 0});

# Now retrieve data from the table.
my $sql = "SELECT * FROM huge_table /*+ shard_id(1) */";

my $sth = $dbh->prepare($sql);

$sth->execute();

while (my $ref = $sth->fetchrow_hashref()) {
        print Dumper($ref);
        print "Found a row: id = $ref->{'id'}, name = $ref->{'name'} ";
}

# Disconnect from the database.
$dbh->disconnect();

When I run the above code, no result is returned. The logs display "CAS response error".

05/29 17:23:43.843 [SRD] ../src/broker/shard_proxy_io.c(1045): New socket io created. (fd:10).

05/29 17:23:43.843 [SRD] ../src/broker/shard_proxy_handler.c(1146): 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_tran:N, is_cas_in_tran:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:-1, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.843 [SRD] ../src/broker/shard_proxy_io.c(2534): New client connected. client(client_id:3, is_busy:Y, fd:10, ctx_cid:3, ctx_uid:4).

05/29 17:23:43.844 [DBG] ../src/broker/shard_proxy_function.c(815): Process requested prepare sql statement. (sql_stmt:[SELECT * FROM huge_table /*+ shard_id(1) */]). 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_tran:Y, is_cas_in_tran:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.844 [DBG] ../src/broker/shard_proxy_function.c(842): Rewrite sql statement. (organized_sql_stmt:[SELECT * FROM huge_table /*+ shard_id(1) */]). 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_tran:Y, is_cas_in_tran:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.844 [DBG] ../src/broker/shard_statement.c(279): Pin statement. statement(index:0, num_alloc:1, stmt_h_id:0, status:2, context id:-1, context uid:0, num pinned:1, lru_next:(nil), lru_prev:(nil), sql_stmt:[SELECT * FROM huge_table /*+ shard_id(1) */]).

05/29 17:23:43.844 [SRD] ../src/broker/shard_proxy_function.c(1192): Select shard. (prev_shard_id:-1, curr_shard_id:1). 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_tran:Y, is_cas_in_tran:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.844 [DBG] ../src/broker/shard_proxy_io.c(3333): Shard status. (num_cas_in_tran=1, shard_id=1).

05/29 17:23:43.844 [SRD] ../src/broker/shard_statement.c(605): Unable to find saved statement handle id. (shard_id:1, cas_id:0). stmt(index:0, num_alloc:1, stmt_h_id:0, status:2, context id:-1, context uid:0, num pinned:1, lru_next:(nil), lru_prev:(nil), sql_stmt:[SELECT * FROM huge_table /*+ shard_id(1) */]).

05/29 17:23:43.844 [SRD] ../src/broker/shard_proxy_function.c(1280): Do prepare before execute. cas(cas_id:0, shard_id:1, is_in_tran:Y, status:1, ctx_cid:3, ctx_uid:4, fd:9). context(cid:3, uid:4, is_busy:Y, is_in_tran:Y, is_prepare_for_execute:N, free_on_end_tran:N, free_on_client_io_write:N, free_context:N, is_client_in_tran:Y, is_cas_in_tran:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:1, cas_id:0, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.845 [DBG] ../src/broker/shard_statement.c(640): Create new sql statement. (shard_id:1, cas_id:0, srv_h_id:4). statement(index:0, num_alloc:1, stmt_h_id:0, status:2, context id:-1, context uid:0, num pinned:1, lru_next:(nil), lru_prev:(nil), sql_stmt:[SELECT * FROM huge_table /*+ shard_id(1) */]).

05/29 17:23:43.845 [SRD] ../src/broker/shard_proxy_function.c(1192): Select shard. (prev_shard_id:1, curr_shard_id:1). context(cid:3, uid:4, is_busy:Y, is_in_tran:Y, is_prepare_for_execute:N, free_on_end_tran:N, free_on_client_io_write:N, free_context:N, is_client_in_tran:Y, is_cas_in_tran:Y, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:1, cas_id:0, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.845 [NTC] ../src/broker/shard_proxy_function.c(2125): CAS response error. (error_ind:-1). context(cid:3, uid:4, is_busy:Y, is_in_tran:Y, is_prepare_for_execute:N, free_on_end_tran:N, free_on_client_io_write:N, free_context:N, is_client_in_tran:Y, is_cas_in_tran:Y, waiting_event:((nil), -), func_code:3, stmt_h_id:0, stmt_hint_type:4, client_id:3, shard_id:1, cas_id:0, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.846 [DBG] ../src/broker/shard_proxy_function.c(1803): Free context on client io write. context will be terminated. context(cid:3, uid:4, is_busy:Y, is_in_tran:Y, is_prepare_for_execute:N, free_on_end_tran:N, free_on_client_io_write:Y, free_context:N, is_client_in_tran:N, is_cas_in_tran:Y, waiting_event:((nil), -), func_code:1, stmt_h_id:-1, stmt_hint_type:-1, client_id:3, shard_id:1, cas_id:0, error_ind:0, error_code:0, error_msg:[-] ).

05/29 17:23:43.846 [DBG] ../src/broker/shard_proxy_io.c(3449): Shard status. (num_cas_in_tran=0, shard_id=1).

05/29 17:23:43.846 [DBG] ../src/broker/shard_proxy_io.c(1078): Close socket. (fd:10).

Why?? Is something wrong with the configurations??

Please  help me.... Thank you very much....

Quote
[Level:3]Esen Sagynov

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 05/31/2011

IP: *.91.139.66

Hello Jesse,

A few months ago we have released a new stable version of CUBRID 9.1. Please use it instead of 9.0 because 9.0 is beta for 9.1.

I will try to replicate your issue using the Perl driver and will get back to you with the answer.

Next time if you write your issue on CUBRID Perl forum http://www.cubrid.org/forum/category/667778, CUBRID developers would help you faster.

Anyway, I am glad you contacted me. I will be more than happy to help you.

Best regards,

Esen Sagynov.

Quote
[Level:8]CUBRID

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 03/29/2010

IP: *.91.139.66

Jesse writes:

I use CUBRID 9.1 and it works!

Thanks.

Quote
[Level:3]Esen Sagynov

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 05/31/2011

IP: *.91.139.66

Great! I'm glad it worked out well for you!

Let me know on CUBRID Forum of you have other questions. I will be glad to help you!

Cheers,

Esen. 

Quote
[Level:8]CUBRID

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 03/29/2010

IP: *.91.139.66

Jesse writes:

The following queries runs successfully.

select * from huge_table /*+ shard_id(1) */

But the following fails:

select * from huge_table where id = /*+ shard_key */ ?;

... as well as this:

INSERT INTO huge_table(id,name,sex) VALUES(/*+ shard_key */ ?,?,?);

Logs:

SELECT

05/30 14:43:51.821 [SRD] ../../src/broker/shard_proxy_io.c(1160): New socket io created. (fd:12).

05/30 14:43:51.821 [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_tran:N, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:-1, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:43:51.821 [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).

05/30 14:43:51.822 [DBG] ../../src/broker/shard_proxy_function.c(1002): Process requested prepare sql statement. (sql_stmt:[select * from huge_table where id = /*+ shard_key */ ?]). 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:43:51.822 [DBG] ../../src/broker/shard_proxy_function.c(1030): Rewrite sql statement. (organized_sql_stmt:[select * from huge_table where id = /*+ shard_key */ ?]). 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:43:51.822 [DBG] ../../src/broker/shard_statement.c(309): Pin statement. statement(index:1, num_alloc:1, stmt_h_id:1, status:2, context id:-1, context uid:0, num pinned:1, lru_next:(nil), lru_prev:0x2ac64a3af010, sql_stmt:[select * from huge_table where id = /*+ shard_key */ ?]).

05/30 14:43:51.822 [ERR] ../../src/broker/shard_proxy_function.c(799): Unsupported hint value type. (type:1).

05/30 14:43:51.822 [ERR] ../../src/broker/shard_proxy_function.c(628): Unable to get shard id. Invalid hint key range. (hint_type:HT_KEY).

05/30 14:43:51.822 [SRD] ../../src/broker/shard_proxy_function.c(1479): Select shard. (prev_shard_id:-1, curr_shard_id:-1). 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:43:51.822 [ERR] ../../src/broker/shard_proxy_function.c(1486): Invalid shard id. (shard_id:-1). 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:3, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:43:51.822 [ERR] ../../src/broker/shard_proxy_handler.c(705): Error returned. (client function, func_code:3, error:-1).

05/30 14:43:51.822 [DBG] ../../src/broker/shard_proxy_handler.c(336): send error(msg) to the client. (error_ind:-1, error_code:-10001, errro_msg:-)

05/30 14:43:51.822 [DBG] ../../src/broker/shard_proxy_io.c(1193): Close socket. (fd:12).

INSERT

05/30 14:50:24.204 [SRD] ../../src/broker/shard_proxy_io.c(1160): New socket io created. (fd:12).

05/30 14:50:24.204 [SRD] ../../src/broker/shard_proxy_handler.c(1239): New context created. context(cid:5, uid:6, 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_tran:N, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:-1, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:50:24.204 [SRD] ../../src/broker/shard_proxy_io.c(2733): New client connected. client(client_id:5, is_busy:Y, fd:12, ctx_cid:5, ctx_uid:6).

05/30 14:50:24.205 [DBG] ../../src/broker/shard_proxy_function.c(1002): Process requested prepare sql statement. (sql_stmt:[INSERT INTO huge_table(id,name,sex) VALUES(/*+ shard_key */ ?,?,?)]). context(cid:5, uid:6, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:5, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:50:24.205 [DBG] ../../src/broker/shard_proxy_function.c(1030): Rewrite sql statement. (organized_sql_stmt:[INSERT INTO huge_table(id,name,sex) VALUES(/*+ shard_key */ ?,?,?)]). context(cid:5, uid:6, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:5, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:50:24.205 [DBG] ../../src/broker/shard_statement.c(309): Pin statement. statement(index:0, num_alloc:1, stmt_h_id:0, status:2, context id:-1, context uid:0, num pinned:1, lru_next:(nil), lru_prev:0x2ac64a3af0d8, sql_stmt:[INSERT INTO huge_table(id,name,sex) VALUES(/*+ shard_key */ ?,?,?)]).

05/30 14:50:24.205 [ERR] ../../src/broker/shard_proxy_function.c(799): Unsupported hint value type. (type:1).

05/30 14:50:24.205 [ERR] ../../src/broker/shard_proxy_function.c(628): Unable to get shard id. Invalid hint key range. (hint_type:HT_KEY).

05/30 14:50:24.205 [SRD] ../../src/broker/shard_proxy_function.c(1479): Select shard. (prev_shard_id:-1, curr_shard_id:-1). context(cid:5, uid:6, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:5, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:50:24.205 [ERR] ../../src/broker/shard_proxy_function.c(1486): Invalid shard id. (shard_id:-1). context(cid:5, uid:6, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:5, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 14:50:24.205 [ERR] ../../src/broker/shard_proxy_handler.c(705): Error returned. (client function, func_code:3, error:-1).

05/30 14:50:24.205 [DBG] ../../src/broker/shard_proxy_handler.c(336): send error(msg) to the client. (error_ind:-1, error_code:-10001, errro_msg:-)

05/30 14:50:24.206 [DBG] ../../src/broker/shard_proxy_io.c(1193): Close socket. (fd:12).

Why??

My shard_db (db_sample1)

MySQL schema:

CREATE TABLE `huge_table` (
  `id` int(11) NOT NULL,
  `name` varchar(64) default NULL,
  `sex` char(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

My shard configurations:

shard.conf:

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

[%shard]
SERVICE                 =ON
BROKER_PORT             =45011
MIN_NUM_APPL_SERVER     =1
MAX_NUM_APPL_SERVER     =1
APPL_SERVER_SHM_ID      =45011
APPL_SERVER             =CAS_MYSQL
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           =db_sample1
SHARD_DB_USER           =jesse
SHARD_DB_PASSWORD       =jesse123
MIN_NUM_PROXY           =1
MAX_NUM_PROXY           =1
PROXY_LOG_FILE          =log/broker/proxy_log
PROXY_LOG               =ALL
MAX_CLIENT              =10
METADATA_SHM_ID         =45092
SHARD_CONNECTION_FILE   =shard_connection.txt
SHARD_KEY_FILE          =shard_key.txt

shard_connection.txt

# mysql
0       db_sample1      cspfdev002:3306
1       db_sample1      cspfdev003:3306

shard_key.txt

[%id]
#min    max shard_id
0       5       0
6       63      1

Why??

Thanks.

Quote
[Level:3]Esen Sagynov

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 05/31/2011

IP: *.91.139.66

Jesse, 

05/30 14:43:51.822 [ERR] ../../src/broker/shard_proxy_function.c(628): Unable to get shard id. Invalid hint key range. (hint_type:HT_KEY).

In both cases you have the same error. It is related to your shard_key.txt configuration file. Your configurations are as follows:

shard_key.txt

[%id]

#min    max shard_id

0       5       0   

6       63      1   

In CUBRID SHARD the default value of "SHARD_KEY_MODULAR" parameter in shard.conf file is 256. Which means that SHARD Broker will try to divide your input value (the value of id column in your case) by 256. Since you shard_key.txt file doesn't define rules for values over 63, that kind of error is displayed.

There are two solutions:

  1. You need to define the full range for the keys, i.e. from 0 to 255 in default case.
  2. Or set "SHARD_KEY_MODULAR" parameter in shard.conf file to 64 in your case.

Let me know if this helps you.

Regarding the CUBRID forum, you need to create an account first. Go to http://www.cubrid.org/?act=dispMemberSignUpForm and create an account. Then you can post on CUBRID forum.

The reason I'm asking to post on CUBRID forum is that other users who may have similar issues with CUBRID SHARD may look at your post and find answer.

Esen.

Quote
[Level:0]hellojesse

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 05/30/2013

IP: *.228.54.122

select * from huge_table where id in(?,?,?)

 

how to modify this sql for cubrid??

 

the bind param:

for example : 1,2,6 which in different shard...

 

i try this: 

select * from huge_table where id in(/*+ shard_key */ ?,/*+ shard_key */ ?,/*+ shard_key */ ?)

 

when bind params are 1,2,3 (in one shard),it works.

but if 1,2,6 ,that don't works...

 

the log:

 

05/30 18:39:29.225 [DBG] ../../src/broker/shard_proxy_function.c(1002): Process requested prepare sql statement. (sql_stmt:[   select * from huge_table where id in(/*+ shard_key */ ?,/*+ shard_key */ ?,/*+ shard_key */ ?)

]). context(cid:6, uid:37, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:6, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 18:39:29.225 [DBG] ../../src/broker/shard_proxy_function.c(1030): Rewrite sql statement. (organized_sql_stmt:[select * from huge_table where id in(/*+ shard_key */ ?,/*+ shard_key */ ?,/*+ shard_key */ ?)

]). context(cid:6, uid:37, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:6, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 18:39:29.225 [DBG] ../../src/broker/shard_statement.c(309): Pin statement. statement(index:9, num_alloc:3, stmt_h_id:4009, status:2, context id:-1, context uid:0, num pinned:1, lru_next:0x2b66f854c0d8, lru_prev:0x2b66f854c268, sql_stmt:[select * from huge_table where id in(/*+ shard_key */ ?,/*+ shard_key */ ?,/*+ shard_key */ ?)

]).

05/30 18:39:29.225 [SRD] ../../src/broker/shard_proxy_function.c(827): Select shard. (shard_id:0, key_column:[id], shard_key_id:1).

05/30 18:39:29.225 [SRD] ../../src/broker/shard_proxy_function.c(827): Select shard. (shard_id:0, key_column:[id], shard_key_id:2).

05/30 18:39:29.225 [SRD] ../../src/broker/shard_proxy_function.c(827): Select shard. (shard_id:1, key_column:[id], shard_key_id:6).

05/30 18:39:29.225 [ERR] ../../src/broker/shard_proxy_function.c(679): Shard id is different. (first_shard_id:0, next_shard_id:1). 

05/30 18:39:29.225 [SRD] ../../src/broker/shard_proxy_function.c(1479): Select shard. (prev_shard_id:-1, curr_shard_id:-1). context(cid:6, uid:37, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:6, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 18:39:29.225 [ERR] ../../src/broker/shard_proxy_function.c(1486): Invalid shard id. (shard_id:-1). context(cid:6, uid:37, 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_tran:Y, is_cas_in_tran:N, is_bypass_msg:N, waiting_event:((nil), -), func_code:-1, stmt_h_id:-1, stmt_hint_type:-1, wait_timeout:30, client_id:6, shard_id:-1, cas_id:-1, error_ind:0, error_code:0, error_msg:[-] ).

05/30 18:39:29.225 [ERR] ../../src/broker/shard_proxy_handler.c(705): Error returned. (client function, func_code:3, error:-1).

05/30 18:39:29.225 [DBG] ../../src/broker/shard_proxy_handler.c(336): send error(msg) to the client. (error_ind:-1, error_code:-10001, errro_msg:-)

05/30 18:39:29.226 [DBG] ../../src/broker/shard_proxy_io.c(1193): Close socket. (fd:12).

 

 

how to do it??

Quote
[Level:3]Esen Sagynov

# Post subject:Re: ERROR when using CUBRID SHARD with MySQL as backend database

profile

registered: 05/31/2011

IP: *.91.139.82

select * from huge_table where id in(?,?,?)

 

how to modify this sql for cubrid??

 

the bind param:

for example : 1,2,6 which in different shard...

As of CUBRID SHARD 9.1 there is no way to query multiple shards in one query. The SQL query must resolve to one shard_id, i.e. there should be single shard_key, shard_val() or shard_id specified.

In order to retrieve records where you'd like to match multiple shard_key values, then you need to execute the query multiple times with different bind values, i.e:

  1. Prepare "select * from huge_table where id = /*+ shard_key */ ?".
  2. Then bind value 1 and execute it. Get results.
  3. Then bind value 2 and execute it. Get results.
  4. Then bind value 6 and execute it. Get results.

This is the only way at this moment. In the future versions we are considering to allow cross-shard queries.

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: