PBXT

Can't run tpcc

Bug #509803 reported by Vadim Tkachenko on 2010-01-19

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Fix Released	Undecided	Unassigned
	PBXT	Fix Committed	Undecided	Vladimir Kolesnikov

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

Related branches

lp:~vkolesnikov/pbxt/pbxt-bug-509803

Merged into lp:pbxt

PBXT Core: Pending requested 2010-02-12

Vladimir Kolesnikov (vkolesnikov) on 2010-01-22

Changed in pbxt:
assignee:	nobody → Vladimir Kolesnikov (vkolesnikov)
status:	New → In Progress

Revision history for this message

Vladimir Kolesnikov (vkolesnikov) wrote on 2010-01-22:

Vadim,

can you supply queries that fail and data on which they fail?

Revision history for this message

Vadim Tkachenko (vadim-tk) wrote on 2010-01-22: Re: [Bug 509803] Re: Can't run tpcc

Download full text (12.5 KiB)

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:

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

Revision history for this message

Vladimir Kolesnikov (vkolesnikov) wrote on 2010-01-22:

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?

Revision history for this message

Vadim Tkachenko (vadim-tk) wrote on 2010-01-22:

Download full text (7.1 KiB)

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
<email address hidden> 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 smalli...

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

Revision history for this message

Vladimir Kolesnikov (vkolesnikov) wrote on 2010-01-25:

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

Revision history for this message

Vadim Tkachenko (vadim-tk) wrote on 2010-01-25:

Download full text (8.4 KiB)

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
<email address hidden> 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=@@FOR...

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

Revision history for this message

Vladimir Kolesnikov (vkolesnikov) wrote on 2010-02-09:

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...

Revision history for this message

Vadim Tkachenko (vadim-tk) wrote on 2010-02-10:

Vladimir,

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

Revision history for this message

Vladimir Kolesnikov (vkolesnikov) wrote on 2010-02-12:

Vadim,

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

Changed in pbxt:
status:	In Progress → Fix Committed

Sergei Golubchik (sergii) on 2010-06-28

Changed in maria:
status:	New → Fix Released

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.