Re: [Bug 509803] Re: Can't run tpcc
Vladimir,

you should create tables before:

create_table.sql (change engine from InnoDB to PBXT)
add_fkey_idx.sql

On Mon, Jan 25, 2010 at 2:34 AM, Vladimir Kolesnikov
<vladimir@primebase.org> wrote:
> Vadim,
>
> tried to run the test but got segfault in tpcc_load:
>
> (gdb) run localhost test root "" 10
> Starting program: /home/bb/projects/tpcc-mysql/tpcc_load localhost test root "" 10
> [Thread debugging using libthread_db enabled]
> [New Thread 0x2b998f2fd200 (LWP 20042)]
> *************************************
> *** ###easy### TPC-C Data Loader  ***
> *************************************
> <Parameters>
>     [server]: localhost
>     [DBname]: test
>       [user]: root
>       [pass]:
>  [warehouse]: 10
> [New Thread 0x40800950 (LWP 20045)]
> [Thread 0x40800950 (LWP 20045) exited]
>
> 0, 00000,
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x2b998f2fd200 (LWP 20042)]
> 0x0000000000000000 in ?? ()
> (gdb) bt
> #0  0x0000000000000000 in ?? ()
> #1  0x00002b998e14a257 in net_real_write () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #2  0x00002b998e14a6cb in net_flush () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #3  0x00002b998e14a860 in net_write_command () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #4  0x00002b998e147311 in cli_advanced_command () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #5  0x00002b998e143f11 in mysql_send_query () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #6  0x00002b998e143f79 in mysql_real_query () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #7  0x00002b998e116d25 in mysql_rollback () from /home/bb/projects/test/lib/mysql/libmysqlclient_r.so.16
> #8  0x0000000000401c64 in Error (mysql_stmt=0x0) at load.c:1222
> #9  0x000000000040482b in main (argc=<value optimized out>, argv=0xb) at load.c:271
>
> –
> Can't run tpcc
> https://bugs.launchpad.net/bugs/509803
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in PrimeBase XT: In Progress
>
> Bug description:
> I'm sorry if we discussed this before, I can't recall what is outcome.
>
> Basically I am trying to run tpcc benchmark on latest pbxt-1.0.10, and run fails for me with a lot of errors
>
> neword 9:3
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_customer_1`)
> neword 7:3
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_customer_1`)
> payment 10:1
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_district_1`)
> payment 4:1
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_stock_1`)
> neword 1:3
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_customer_1`)
> payment 6:1
> 1451, 23000, Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `fkey_district_1`)
>
>
> The schema is:
>
> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
>
> drop table if exists warehouse;
>
> create table warehouse (
> w_id smallint not null,
> w_name varchar(10),
> w_street_1 varchar(20),
> w_street_2 varchar(20),
> w_city varchar(20),
> w_state char(2),
> w_zip char(9),
> w_tax decimal(4,2),
> w_ytd decimal(12,2) ) TYPE=PBXT;
>
> drop table if exists district;
>
> create table district (
> d_id tinyint not null,
> d_w_id smallint not null,
> d_name varchar(10),
> d_street_1 varchar(20),
> d_street_2 varchar(20),
> d_city varchar(20),
> d_state char(2),
> d_zip char(9),
> d_tax decimal(4,2),
> d_ytd decimal(12,2),
> d_next_o_id int ) TYPE=PBXT;
>
> drop table if exists customer;
>
> create table customer (
> c_id int not null,
> c_d_id tinyint not null,
> c_w_id smallint not null,
> c_first varchar(16),
> c_middle char(2),
> c_last varchar(16),
> c_street_1 varchar(20),
> c_street_2 varchar(20),
> c_city varchar(20),
> c_state char(2),
> c_zip char(9),
> c_phone char(16),
> c_since datetime,
> c_credit char(2),
> c_credit_lim bigint,
> c_discount decimal(4,2),
> c_balance decimal(12,2),
> c_ytd_payment decimal(12,2),
> c_payment_cnt smallint,
> c_delivery_cnt smallint,
> c_data text ) TYPE=PBXT;
>
> drop table if exists history;
>
> create table history (
> h_c_id int,
> h_c_d_id tinyint,
> h_c_w_id smallint,
> h_d_id tinyint,
> h_w_id smallint,
> h_date datetime,
> h_amount decimal(6,2),
> h_data varchar(24) ) TYPE=PBXT;
>
> drop table if exists new_orders;
>
> create table new_orders (
> no_o_id int not null,
> no_d_id tinyint not null,
> no_w_id smallint not null) TYPE=PBXT;
>
> drop table if exists orders;
>
> create table orders (
> o_id int not null,
> o_d_id tinyint not null,
> o_w_id smallint not null,
> o_c_id int,
> o_entry_d datetime,
> o_carrier_id tinyint,
> o_ol_cnt tinyint,
> o_all_local tinyint ) TYPE=PBXT;
>
> drop table if exists order_line;
>
> create table order_line (
> ol_o_id int not null,
> ol_d_id tinyint not null,
> ol_w_id smallint not null,
> ol_number tinyint not null,
> ol_i_id int,
> ol_supply_w_id smallint,
> ol_delivery_d datetime,
> ol_quantity tinyint,
> ol_amount decimal(6,2),
> ol_dist_info char(24) ) TYPE=PBXT;
>
> drop table if exists item;
>
> create table item (
> i_id int not null,
> i_im_id int,
> i_name varchar(24),
> i_price decimal(5,2),
> i_data varchar(50) ) TYPE=PBXT;
>
> drop table if exists stock;
>
> create table stock (
> s_i_id int not null,
> s_w_id smallint not null,
> s_quantity smallint,
> s_dist_01 char(24),
> s_dist_02 char(24),
> s_dist_03 char(24),
> s_dist_04 char(24),
> s_dist_05 char(24),
> s_dist_06 char(24),
> s_dist_07 char(24),
> s_dist_08 char(24),
> s_dist_09 char(24),
> s_dist_10 char(24),
> s_ytd decimal(8,0),
> s_order_cnt smallint,
> s_remote_cnt smallint,
> s_data varchar(50) ) TYPE=PBXT;
>
> ALTER TABLE warehouse ADD CONSTRAINT pkey_warehouse PRIMARY KEY(w_id);
> ALTER TABLE district  ADD CONSTRAINT pkey_district  PRIMARY KEY(d_w_id, d_id);
> ALTER TABLE customer  ADD CONSTRAINT pkey_customer  PRIMARY KEY(c_w_id, c_d_id, c_id);
> ALTER TABLE new_orders ADD CONSTRAINT pkey_new_orders PRIMARY KEY(no_w_id, no_d_id, no_o_id);
> ALTER TABLE orders    ADD CONSTRAINT pkey_orders    PRIMARY KEY(o_w_id, o_d_id, o_id);
> ALTER TABLE order_line ADD CONSTRAINT pkey_order_line PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number);
> ALTER TABLE item ADD CONSTRAINT pkey_item PRIMARY KEY(i_id);
> ALTER TABLE stock ADD CONSTRAINT pkey_stock PRIMARY KEY(s_w_id, s_i_id);
>
> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
>
> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
>
>
> ALTER TABLE district  ADD CONSTRAINT fkey_district_1 FOREIGN KEY(d_w_id) REFERENCES warehouse(w_id);
> ALTER TABLE customer ADD CONSTRAINT fkey_customer_1 FOREIGN KEY(c_w_id,c_d_id) REFERENCES district(d_w_id,d_id);
> ALTER TABLE history  ADD CONSTRAINT fkey_history_1 FOREIGN KEY(h_c_w_id,h_c_d_id,h_c_id) REFERENCES customer(c_w_id,c_d_id,c_id);
> ALTER TABLE history  ADD CONSTRAINT fkey_history_2 FOREIGN KEY(h_w_id,h_d_id) REFERENCES district(d_w_id,d_id);
> ALTER TABLE new_orders ADD CONSTRAINT fkey_new_orders_1 FOREIGN KEY(no_w_id,no_d_id,no_o_id) REFERENCES orders(o_w_id,o_d_id,o_id);
> ALTER TABLE orders ADD CONSTRAINT fkey_orders_1 FOREIGN KEY(o_w_id,o_d_id,o_c_id) REFERENCES customer(c_w_id,c_d_id,c_id);
> ALTER TABLE order_line ADD CONSTRAINT fkey_order_line_1 FOREIGN KEY(ol_w_id,ol_d_id,ol_o_id) REFERENCES orders(o_w_id,o_d_id,o_id);
> ALTER TABLE order_line ADD CONSTRAINT fkey_order_line_2 FOREIGN KEY(ol_supply_w_id,ol_i_id) REFERENCES stock(s_w_id,s_i_id);
> ALTER TABLE stock ADD CONSTRAINT fkey_stock_1 FOREIGN KEY(s_w_id) REFERENCES warehouse(w_id);
> ALTER TABLE stock ADD CONSTRAINT fkey_stock_2 FOREIGN KEY(s_i_id) REFERENCES item(i_id);
>
>
> CREATE INDEX idx_customer ON customer (c_w_id,c_d_id,c_last,c_first);
> CREATE INDEX idx_orders ON orders (o_w_id,o_d_id,o_c_id,o_id);
>
>
> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
>
>
> Thanks,
> Vadim
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/pbxt/+bug/509803/+subscribe
>

–
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-888-401-3403, Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Re: Can't run tpcc
Vadim,

I got the error you reported initially from the tool. An error message starts with "neword 9:3" which means thread #9 step 3. On step 3 the following query is executed:

"UPDATE district SET d_next_o_id = ? + 1 WHERE d_id = ? AND d_w_id = ?"

i tried to execute the query from mysql console and it worked (I checked FK checks to be on). So either this is something specific to the tool, or a wrong error is reported...

Re: Can't run tpcc
Vladimir,

I have no problem with running this workload on InnoDB / XtraDB.

Re: Can't run tpcc
Vadim,

thanks for the report. The fix is immediately available in the attached branch.

Launchpad bug id: 509803