MySQL Bugs: #13367: InnoDB does not create doublewrite buffer in new installation: assertion failure

Bug #13367	InnoDB does not create doublewrite buffer in new installation: assertion failure
Submitted:	21 Sep 2005 4:24	Modified:	6 Oct 2005 1:03
Reporter:	Yasufumi Kinoshita	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.12-beta-standard	OS:	Linux (SuSE Linux 9.3)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
MySQL Serevr crushes while prepareing data.

The data are generated by DBGEN (TPC-H data generator).
*http://www.tpc.org/tpch/
(I had to add "-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64"
  to CFLAGS in Makefile for Largefile support.)

shell> dbgen -s 3
(generate data (ScaleFactor=3))

I tried several patterns.
--------------------------------------
(create table) -> (LOAD) -> (add PKEY) -> (add FKEY) : No problems
(create table) -> (add PKEY) -> (add FKEY) -> (LOAD) : *crushed at LOAD
(create table) -> (add PKEY) -> (LOAD) -> (add FKEY) : *crushed at FKEY

There is 100% reproducibility in my environment...

How to repeat:
-- create tables

create table nation (
n_nationkey  decimal(3,0) not null,
n_name       char(25) not null,
n_regionkey  decimal(2,0) not null,
n_comment    varchar(152)
) TYPE=InnoDB;

create table region (
r_regionkey  decimal(2,0) not null,
r_name       char(25) not null,
r_comment    varchar(152)
) TYPE=InnoDB;

create table part (
p_partkey     decimal(10,0) not null,
p_name        varchar(55) not null,
p_mfgr        char(25) not null,
p_brand       char(10) not null,
p_type        varchar(25) not null,
p_size        decimal(2,0) not null,
p_container   char(10) not null,
p_retailprice decimal(6,2) not null,
p_comment     varchar(23) not null
) TYPE=InnoDB;

create table supplier (
s_suppkey     decimal(8,0) not null,
s_name        char(25) not null,
s_address     varchar(40) not null,
s_nationkey   decimal(3,0) not null,
s_phone       char(15) not null,
s_acctbal     decimal(7,2) not null,
s_comment     varchar(101) not null
) TYPE=InnoDB;

create table partsupp (
ps_partkey     decimal(10,0) not null,
ps_suppkey     decimal(8,0) not null,
ps_availqty    decimal(5,0) not null,
ps_supplycost  decimal(6,2) not null,
ps_comment     varchar(199) not null
) TYPE=InnoDB;

create table customer (
c_custkey     decimal(9,0) not null,
c_name        varchar(25) not null,
c_address     varchar(40) not null,
c_nationkey   decimal(3,0) not null,
c_phone       char(15) not null,
c_acctbal     decimal(7,2) not null,
c_mktsegment  char(10) not null,
c_comment     varchar(117) not null
) TYPE=InnoDB;

create table orders  (
o_orderkey       decimal(12,0) not null,
o_custkey        decimal(9,0) not null,
o_orderstatus    char(1) not null,
o_totalprice     decimal(8,2) not null,
o_orderdate      date not null,
o_orderpriority  char(15) not null,
o_clerk          char(15) not null,
o_shippriority   decimal(1,0) not null,
o_comment        varchar(79) not null
) TYPE=InnoDB;

create table lineitem (
l_orderkey    decimal(12,0) not null,
l_partkey     decimal(10,0) not null,
l_suppkey     decimal(8,0) not null,
l_linenumber  decimal(1,0) not null,
l_quantity    decimal(2,0) not null,
l_extendedprice  decimal(8,2) not null,
l_discount    decimal(3,2) not null,
l_tax         decimal(3,2) not null,
l_returnflag  char(1) not null,
l_linestatus  char(1) not null,
l_shipdate    date not null,
l_commitdate  date not null,
l_receiptdate date not null,
l_shipinstruct char(25) not null,
l_shipmode     char(10) not null,
l_comment      varchar(44) not null
) TYPE=InnoDB;

-- define primary keys
ALTER TABLE region ADD CONSTRAINT pkey_region PRIMARY KEY(r_regionkey);
ALTER TABLE nation ADD CONSTRAINT pkey_nation PRIMARY KEY(n_nationkey);
ALTER TABLE part ADD CONSTRAINT pkey_part PRIMARY KEY(p_partkey);
ALTER TABLE supplier ADD CONSTRAINT pkey_supplier PRIMARY KEY(s_suppkey);
ALTER TABLE partsupp ADD CONSTRAINT pkey_partsupp PRIMARY KEY(ps_partkey,ps_suppkey);
ALTER TABLE customer ADD CONSTRAINT pkey_customer PRIMARY KEY(c_custkey);
ALTER TABLE lineitem ADD CONSTRAINT pkey_lineitem PRIMARY KEY(l_orderkey,l_linenumber);
ALTER TABLE orders ADD CONSTRAINT pkey_orders PRIMARY KEY(o_orderkey);

-- define foreign keys
ALTER TABLE nation ADD CONSTRAINT fkey_nation_1 FOREIGN KEY(n_regionkey) REFERENCES region(r_regionkey);
ALTER TABLE supplier ADD CONSTRAINT fkey_supplier_1 FOREIGN KEY(s_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE customer ADD CONSTRAINT fkey_customer_1 FOREIGN KEY(c_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE partsupp ADD CONSTRAINT fkey_partsupp_1 FOREIGN KEY(ps_suppkey) REFERENCES supplier(s_suppkey);
ALTER TABLE partsupp ADD CONSTRAINT fkey_partsupp_2 FOREIGN KEY(ps_partkey) REFERENCES part(p_partkey);
ALTER TABLE orders ADD CONSTRAINT fkey_orders_1 FOREIGN KEY(o_custkey) REFERENCES customer(c_custkey);
ALTER TABLE lineitem ADD CONSTRAINT fkey_lineitem_1 FOREIGN KEY(l_orderkey) REFERENCES orders(o_orderkey);
ALTER TABLE lineitem ADD CONSTRAINT fkey_lineitem_2 FOREIGN KEY(l_partkey,l_suppkey) REFERENCES partsupp(ps_partkey,ps_suppkey);

-- load data (execute as root; data are generated by "dbgen -s 3")
LOAD DATA INFILE '/somewhere/region.tbl'   INTO TABLE region   FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/nation.tbl'   INTO TABLE nation   FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/part.tbl'     INTO TABLE part     FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/orders.tbl'   INTO TABLE orders   FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA INFILE '/somewhere/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';

Suggested fix:
No Idea...

.err file

Attachment: mysql.err (application/octet-stream, text), 21.25 KiB.

/etc/my.cnf

Attachment: my.cnf (application/octet-stream, text), 20.45 KiB.

Hi!

This is really strange. When you create a new InnoDB installation, it should print:

"
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
"

Those lines are missing from your .err log!

Could it be that another mysqld server was running at the same time, and it stamped the doublewrite buffer magic number to the page in the ibdata file?

If you create a new InnoDB installation, does it then print that it is creating the doublewrite buffer?

Regards,

Heikki

heikki@hundin:~/mysql-5.0/sql> ./mysqld
InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist:
InnoDB: a new database to be created!
050921 12:20:48  InnoDB: Setting file /home/heikki/data/ibdata1 size to 20 MB
InnoDB: Database physically writes the file full: wait...
050921 12:20:50  InnoDB: Log file /home/heikki/data/ib_logfile0 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
050921 12:21:11  InnoDB: Log file /home/heikki/data/ib_logfile1 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
050921 12:21:38  InnoDB: Started; log sequence number 0 0
050921 12:21:38 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
050921 12:21:38 [Note] ./mysqld: ready for connections.
Version: '5.0.14-rc-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  So
urce distribution

Hi!

Ok, I see. You have probably specified

skip-innodb-doublewrite

in your my.cnf.

Normally, you should use the doublewrite buffer. That new option is currently mostly used in benchmarking.

I am assigning Vadim to this. I think the doublewrite buffer SHOULD be created when a new InnoDB installation is created. The disable operation should just skip its use.

Regards,

Heikki

Assigning Marko Mäkelä as the reviewer of Alexey's patch to the bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30335

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30366

Fixed in 5.0.14

Noted in 5.0.14 changelog.

Can someone get me a copy of this dbgen tool for tpch..i cant get it to compile from the website...--> jamesferris@hotmail.com

thanks

-Jim