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
