What does this mean. How can I fix it. It seems silly that I have to read data out just to write it back in again instead of using the create...select command.
Rich ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 13, 2001 10:13 AM Subject: Re: innodb inserts/select crash > Hi! > > You are getting a lock wait timeout error, not a crash. In the newest > version 3.23.44 code 1000001 has been replaced by a native MySQL error > number 1205 and a descriptive message. > > >Hi there. > > > >Can anyone offer a solution to this problem. > >CREATE TABLE `raw` ( > > `cid` int(11) default NULL, `agent` char(255) default NULL, > > `referer` char(255) default NULL, `addr` char(15) default NULL, > > `via` char(255) default NULL, `forward` char(15) default NULL, > > `ctime` datetime default NULL, `uniq` int(1) default NULL, > > KEY `age` (`ctime`)) TYPE=InnoDB > > > >I have a process inserting 150 rows a second into this database. > >---- > >select cid,referer,count(*) as hits, ctime from raw WHERE uniq=1 group by > >cid,referer; > >takes 1.39 seconds to do. > > > >however if i add a simple insert statement to put those selects into a table > >i get this error > > > >mysql> create table pagehits_tmp select cid,referer,count(*) as hits, ctime > >from raw WHERE uniq=1 group by cid,referer; > >ERROR 1030: Got error 1000001 from table handler > > > >I don't understand why it cannot do this when the select statement takes no > >time at all. > >perror(1000001) just returns unknown error. > >Any suggestions appreciated > > > >Ric > > The reason why CREATE TABLE ... SELECT ... sets shared locks on the rows in > the SELECT table is that the MySQL binlog logs complete SQL strings as they > are. If we do not lock the rows we read, then in recovery we do not know > what rows actually were inserted. > > An ordinary SELECT does not set any locks: it is a consistent read. > > Hmm... what to do? You could do SELECT INTO OUTFILE + LOAD DATA INFILE to > your table pagehits_tmp. Then MySQL no locks need to be set. > > Or you could try to figure out why the inserts may keep locks for a long > time, and commit more often. The default for a lock wait timeout is 50 seconds. > > Regards, > > Heikki > http://www.innodb.com/ibman.html > > > > --------------------------------------------------------------------- > 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
