|
Re: [Bug 509803] Re: Can't run tpcc
I can't really provide data, it's 90GB,
but it was generated by tpcc-mysql benchmark which you can get there
https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql
I was able to repeat problem for smaller dataset.
Data was generated as:
./tpcc_load localhost tpcc root "" 10
and benchmark run
is
./tpcc_start localhost tpcc root "" 10 1 100 3600
In general log I see:
1 Execute SELECT c_discount, c_last, c_credit,
w_tax FROM customer, warehouse WHERE w_id = 10 AND c_w_id = w_i
d AND c_d_id = 1 AND c_id = 1144
1 Execute SELECT d_next_o_id, d_tax FROM
district WHERE d_id = 1 AND d_w_id = 10 FOR UPDATE
1 Query rollback
Full log:
libexec/mysqld, Version: 5.1.42-log (Source distribution). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
100122 9:51:13 1 Connect root@localhost on tpcc
1 Query set autocommit=0
1 Prepare SELECT c_discount, c_last, c_credit,
w_tax FROM customer, warehouse WHERE w_id = ? AND c_w_id = w_id
AND c_d_id = ? AND c_id = ?
1 Prepare SELECT d_next_o_id, d_tax FROM
district WHERE d_id = ? AND d_w_id = ? FOR UPDATE
1 Prepare UPDATE district SET d_next_o_id = ? +
1 WHERE d_id = ? AND d_w_id = ?
1 Prepare INSERT INTO orders (o_id, o_d_id,
o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES(?, ?, ?,
?, ?, ?, ?)
1 Prepare INSERT INTO new_orders (no_o_id,
no_d_id, no_w_id) VALUES (?,?,?)
1 Prepare SELECT i_price, i_name, i_data FROM
item WHERE i_id = ?
1 Prepare SELECT s_quantity, s_data, s_dist_01,
s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_
07, s_dist_08, s_dist_09, s_dist_10 FROM stock WHERE s_i_id = ? AND
s_w_id = ? FOR UPDATE
1 Prepare UPDATE stock SET s_quantity = ? WHERE
s_i_id = ? AND s_w_id = ?
1 Prepare INSERT INTO order_line (ol_o_id,
ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_dist_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
1 Prepare UPDATE warehouse SET w_ytd = w_ytd + ?
WHERE w_id = ?
1 Prepare SELECT w_street_1, w_street_2, w_city,
w_state, w_zip, w_name FROM warehouse WHERE w_id = ?
1 Prepare UPDATE district SET d_ytd = d_ytd + ?
WHERE d_w_id = ? AND d_id = ?
1 Prepare SELECT d_street_1, d_street_2, d_city,
d_state, d_zip, d_name FROM district WHERE d_w_id = ? AND d_i
d = ?
1 Prepare SELECT count(c_id) FROM customer WHERE
c_w_id = ? AND c_d_id = ? AND c_last = ?
1 Prepare SELECT c_id FROM customer WHERE c_w_id
= ? AND c_d_id = ? AND c_last = ? ORDER BY c_first
1 Prepare SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_credit,
c_credit_lim, c_discount, c_balance, c_since FROM customer WHERE
c_w_id = ? AND c_d_id = ? AND c_id = ? FOR UPDATE
1 Prepare SELECT c_data FROM customer WHERE
c_w_id = ? AND c_d_id = ? AND c_id = ?
1 Prepare UPDATE customer SET c_balance = ?,
c_data = ? WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
1 Prepare UPDATE customer SET c_balance = ?
WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
1 Prepare INSERT INTO history(h_c_d_id,
h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES(?,
?, ?, ?, ?, ?, ?, ?)
1 Prepare SELECT count(c_id) FROM customer WHERE
c_w_id = ? AND c_d_id = ? AND c_last = ?
1 Prepare SELECT c_balance, c_first, c_middle,
c_last FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last
= ? ORDER BY c_first
1 Prepare SELECT c_balance, c_first, c_middle,
c_last FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id =
?
1 Prepare SELECT o_id, o_entry_d,
COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = ? AND o_d_id = ?
AND o_c
_id = ? AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = ? AND
o_d_id = ? AND o_c_id = ?)
1 Prepare SELECT ol_i_id, ol_supply_w_id,
ol_quantity, ol_amount, ol_delivery_d FROM order_line WHERE ol_w_id
= ? AND ol_d_id = ? AND ol_o_id = ?
1 Prepare SELECT COALESCE(MIN(no_o_id),0) FROM
new_orders WHERE no_d_id = ? AND no_w_id = ?
1 Prepare DELETE FROM new_orders WHERE no_o_id =
? AND no_d_id = ? AND no_w_id = ?
1 Prepare SELECT o_c_id FROM orders WHERE o_id =
? AND o_d_id = ? AND o_w_id = ?
1 Prepare UPDATE orders SET o_carrier_id = ?
WHERE o_id = ? AND o_d_id = ? AND o_w_id = ?
1 Prepare UPDATE order_line SET ol_delivery_d =
? WHERE ol_o_id = ? AND ol_d_id = ? AND ol_w_id = ?
1 Prepare SELECT SUM(ol_amount) FROM order_line
WHERE ol_o_id = ? AND ol_d_id = ? AND ol_w_id = ?
1 Prepare UPDATE customer SET c_balance =
c_balance + ? , c_delivery_cnt = c_delivery_cnt + 1 WHERE c_id = ? A
ND c_d_id = ? AND c_w_id = ?
1 Prepare SELECT d_next_o_id FROM district WHERE
d_id = ? AND d_w_id = ?
1 Prepare SELECT DISTINCT ol_i_id FROM
order_line WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id < ? AND
ol_o_i
d >= (? - 20)
1 Prepare SELECT count FROM stock WHERE
s_w_id = ? AND s_i_id = ? AND s_quantity < ?
1 Execute SELECT c_discount, c_last, c_credit,
w_tax FROM customer, warehouse WHERE w_id = 10 AND c_w_id = w_i
d AND c_d_id = 1 AND c_id = 1144
1 Execute SELECT d_next_o_id, d_tax FROM
district WHERE d_id = 1 AND d_w_id = 10 FOR UPDATE
1 Query rollback
On Fri, Jan 22, 2010 at 4:28 AM, Vladimir Kolesnikov
<vladimir@primebase.org> wrote:
> Vadim,
>
> can you supply queries that fail and data on which they fail?
>
> –
> 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: [Bug 509803] Re: Can't run tpcc
Vladimir,
I am able to repeat problem with 10W (~1GB of data).
I could allocate queries, but not right now, may be next week.
On Fri, Jan 22, 2010 at 11:40 AM, Vladimir Kolesnikov
<vladimir@primebase.org> wrote:
> Hi Vadim,
>
> generating and using 90 GB of data would be a bit of problem for my
> notebook anyway, so maybe you can provide the data that is related to
> the failing query (I mean related by the PK/FK references). Also as I
> understand there should be something like "Execute UPDATE" or "Execute
> DELETE" that I don't see in the log you pasted... Do I miss something?
>
> –
> 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: [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
|
LPexportBug509803.xml