Rich, a fix is to analyze the locking behavior of your database and try to find out why the locking SELECT has to wait long times. But I think the OUTFILE - INFILE trick is easier.
Inside MySQL the fix would be to log individually rows inserted by CREATE ... SELECT ... But I am not responsible for the logging code in MySQL, and am afraid that we will not get that capability soon. The reason why we cannot just let CREATE TABLE ... SELECT ... to proceed without appropriate locking and logging is that you may later use the contents of your temp table to insert some rows to ordinary tables. In a roll-forward recovery the operations logged in the MySQL binlog must be executed in the exact same way they happened the first time. If we have incomplete information about the inserted rows, we may end up with a database which is different from the original one. >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 > >Date: Tue, 13 Nov 2001 12:14:55 >To: [EMAIL PROTECTED] >From: Heikki Tuuri <[EMAIL PROTECTED]> >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
