Oren,
I did not find any clear crash from the error log. Looks like MySQL somehow
failed in shutdown:
...
020507 9:59:12
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: Forcing close
of thread 109 user: 'root'
A mysqld process already exists at Tue May 7 09:59:39 IDT 2002
...
Below we see that the big rollback actually finished successfully in 45
minutes:
020507 10:25:39 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 94594158
InnoDB: Doing recovery: scanned up to log sequence number 0 94659584
InnoDB: Doing recovery: scanned up to log sequence number 0 94725120
...
InnoDB: Rolling back trx with id 0 315126
020507 10:51:38 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
97 98 99
InnoDB: Apply batch completed
InnoDB: Rolling back of trx id 0 315126 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file offset 0 7074979, file name ./sun06-bin.024
020507 11:11:26 InnoDB: Flushing modified pages from the buffer pool...
020507 11:11:27 InnoDB: Started
Status information:
Current dir: /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/data/
Current locks:
key_cache status:
blocks used: 0
not flushed: 0
w_requests: 0
writes: 0
r_requests: 0
reads: 0
handler status:
read_key:0
read_next: 0
read_rnd 0
read_first: 0
write: 0
delete 0
update: 0
Table status:
Opened tables: 0
Open tables:0
Open files: 2
Open streams: 0
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: ready for
connections
020507 11:11:27 Error in accept: Software caused connection abort
...
Looks like the communication between the client and the server is somehow
blocked. You can try killing all mysqld and client processes, and restarting
mysqld.
To get good performance please set my.cnf options as recommended in section
2 of http://www.innodb.com/ibman.html.
Regards,
Heikki
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Sent: Tuesday, May 07, 2002 5:26 PM
Subject: RE: innodb tables problem
my.cnf
--
[mysqld]
innodb_data_file_path = ibdata1:2000M
log-bin
server-id=37
hostname.err
(Attached)
--
I didn't use 'set innodb_flush_log_at_trx_commit=0'. I'm going to try
this.
Table creation:
create table A (
A1 int(11) not null auto_increment,
A2 varchar(32) not null,
A3 int(11),
A4 varchar(32) not null,
primary key (A1),
index (A4),
unique index (A3,A4),
foreign key (A4) B (B4)
) type = InnoDB;
insertion:
printf insert into A select null,concat('user',Num+1),1,$base_number+Num
from TmpTbl;\n
Where TmpTbl contains the numbers 1..3,000,000
Regards,
Oren.
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 07, 2002 3:11 PM
To: Oren Zeev-Ben-Mordehai
Cc: [EMAIL PROTECTED]
Subject: Re: innodb tables problem
Oren,
what MySQL version you are running? What OS?
Send me a copy of your my.cnf, the error log 'hostname'.err, and the
exact
sequence of SQL statements you used to crash InnoDB, including the table
CREATE statements. Did InnoDB run out of tablespace?
Regarding the performance, did you set innodb_flush_log_at_trx_commit=0
when
you tried to use the script to pipe inserts to MySQL?
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, May 07, 2002 3:16 PM
Subject: innodb tables problem
I have defined some InnoDB tables and try to feed one of them
with 3,000,000 records.
Using a script piped to mysql.
First I tried to insert the rows one by one. It worked very slow. I
didn't
wait to see if it ends.
Next try was to fill some temporary table with the numbers 1..3,000,000.
I
was able to do it with Log(3,000,000) inserts.
select count(*) from TmpTbl; returns 3,000,000 successfully.
And now I used 'insert null,Num,concat('user',Num) into .. select Num
from
TmpTbl'
(null is for a auto_increment primary key).
This fails, the mysqld crashed, restarts, trying to roll back the
transaction,
and I've been wating for a long time and the database is still
