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 not > > responsive. > > > > Do I need to set some parameters? > > > > Do you know a better way to do this? > > > > P.S. > > > > I'm also using log-bin (so another mysqld will be the slave of this one). > > > > > > > > > > Oren Zeev-Ben-Mordehai > > Infrastructure Engineer > > PhoneDo Networks > > office: +972-9-951-7771 ext. 204 > > fax: +972-9-951-7772 > > email: [EMAIL PROTECTED] > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php