Sergei, ok, this explains it. Your buffer pool is too small. Look at the MySQL manual.
All operations in InnoDB happen inside transactions. ALTER TABLE essentially does INSERT INTO newtable SELECT * FROM oldtable; and sets lots of shared row locks on oldtable. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Sergei Skarupo"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, July 15, 2004 9:37 AM Subject: RE: Expensive InnoDB queries crash mysql daemon > Thanks for your reply. > > I think on this machine MySQL was installed from an RPM distribution... = > All the settings are default... > > I seem to have an unisual setup as far as the logfiles are concerned. = > There are no localhost.log and localhost.err files on this machine, nor = > any .log or .err files in the data directory. > > There is mysql.log in the /var/log directory, and it does have a record = > of this crash... Around the time of the crash it got flooded with = > messages like this: > > 040714 14:39:02 InnoDB: WARNING: over 4 / 5 of the buffer pool is = > occupied by > InnoDB: lock heaps or the adaptive hash index! Check that your > InnoDB: transactions do not set too many row locks. > InnoDB: Your buffer pool size is 8 MB. Maybe you should make > InnoDB: the buffer pool bigger? > InnoDB: Starting the InnoDB Monitor to print diagnostics, including > InnoDB: lock heap and hash index sizes. > > > I'm pretty sure that at the moment, no queries were setting locks or = > using transactions explicitly. > Then, the error itself: > > > 040714 14:39:03 InnoDB: Assertion failure in thread 1104214832 in file = > buf0lru.c line 234 > InnoDB: Failing assertion: 0 > InnoDB: We intentionally generate a memory trap. > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this = > binary > or one of the libraries it was linked against is corrupt, improperly = > built, > or misconfigured. This error can also be caused by malfunctioning = > hardware. > We will try our best to scrape up some info that will hopefully help = > diagnose > the problem, but since we have already crashed, something is definitely = > wrong > and this may fail. > > key_buffer_size=3D8388600 > read_buffer_size=3D131072 > max_used_connections=3D0 > max_connections=3D100 > threads_connected=3D1 > It is possible that mysqld could use up to=20 > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = > =3D 225791 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > > > The memory is OK. > > Then it printed a stack trace, which was terminated because a sanity = > check failed... I was able to resolve the stack trace using the = > resolve_stack_dump utility, but that did not help... I'm not including = > it here... > > Finally, the query that was running: > > thd->query at 0x8827870 =3D alter table sensortest_rawdata > change column gate_current gate_current float default null, > change column source_voltage source_voltage float default null > thd->thread_id=3D1 > > > Should I try to change the buffer size? Should I report this as a bug? > > Thanks, > > Sergei > > > > -----Original Message----- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 14, 2004 10:49 PM > To: [EMAIL PROTECTED] > Subject: Re: Expensive InnoDB queries crash mysql daemon > > > Sergei, > > please run CHECK TABLE on the tables, and check if it prints something = > to > the .err log. > > Also, resolve the stack trace printed by the crashing mysqld, as = > explained > in the manual. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up = > MyISAM > tables > http://www.innodb.com/order.php > > Order MySQL technical support from https://order.mysql.com/ > > > ----- Original Message -----=20 > From: ""Sergei Skarupo"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Thursday, July 15, 2004 1:50 AM > Subject: Expensive InnoDB queries crash mysql daemon > > > > ------_=3D_NextPart_001_01C469F5.1B46DF30 > > Content-Type: text/plain; > > charset=3D"iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > Hello All, > > =3D20 > > I hope someone can shed some light on this problem... > > =3D20 > > This concerns large InnoDB tables (having on the order of millions of = > =3D > > rows). > > =3D20 > > When I run ALTER TABLE (for example, to change the default column = > value) =3D > > or UPDATE or DELETE queries that affect many rows, mysqld-max crashes = > =3D > > and apparently gets restarted by mysqld_safe. This also seems to hang = > =3D > > the web server that runs on the same machine and maintains connections = > =3D > > with the database. Of course, the query does not complete. > > =3D20 > > I'm using mysql version 4.0.16 on Red Hat 9 Linux, kernel 2.4.20. > > =3D20 > > Thanks in advance, > > =3D20 > > Sergei > > > > ------_=3D_NextPart_001_01C469F5.1B46DF30-- > > > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: = > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
