Re: 4.0.1 Bugs
My unions are only performed by a looping script so its ok for me. Thanks for the heads up. Did you encounter any extra problems beyond increasing the heap size? Just so I know in advance, is this a my.cnf option. Ric - Original Message - From: Michael Grover [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, May 23, 2002 5:15 AM Subject: Re: 4.0.1 Bugs I had problems with unions that result in large queries too. I found that MySQL 4 was using Heap tables for Unions. One can Increase the Heap Table size to help, but What about alot of conncurrent users running unions? This could eat up alot of memory. Is there a switch to stop unions from using heap tables? mike Heikki Tuuri wrote: Richard, I will look at the UNION problem later. I think MySQL uses a HEAP type temporary table when it calculates the UNION. HEAP type tables are allocated from the main memory. The message 'Table is full' tells that MySQL cannot allocate more memory. Regards, Heikki 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0
Re: 4.0.1 Bugs
Richard, I will look at the UNION problem later. I think MySQL uses a HEAP type temporary table when it calculates the UNION. HEAP type tables are allocated from the main memory. The message 'Table is full' tells that MySQL cannot allocate more memory. Regards, Heikki 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log output just said restared/shutdown incorrectly etc (no caught signals). Whether its any help or not, I don't know but here is some innodb_monitor output. - 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
Re: 4.0.1 Bugs
I had problems with unions that result in large queries too. I found that MySQL 4 was using Heap tables for Unions. One can Increase the Heap Table size to help, but What about alot of conncurrent users running unions? This could eat up alot of memory. Is there a switch to stop unions from using heap tables? mike Heikki Tuuri wrote: Richard, I will look at the UNION problem later. I think MySQL uses a HEAP type temporary table when it calculates the UNION. HEAP type tables are allocated from the main memory. The message 'Table is full' tells that MySQL cannot allocate more memory. Regards, Heikki 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log
Re: 4.0.1 Bugs
Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log output just said restared/shutdown incorrectly etc (no caught signals). Whether its any help or not, I don't know but here is some innodb_monitor output. - Before
Re: 4.0.1 Bugs
Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log output just said restared/shutdown incorrectly etc (no caught signals). Whether its any help or not, I don't know but here is some innodb_monitor output. - Before
Re: 4.0.1 Bugs
Heikki, My set of queries performs about 4/5 show create table queries each hour. The purpose being to drop it then recreate it automatically. Last time I tried truncate it was as slow as delete, but that was near the begining of my development and never saw reason to retry it after show create/drop/create ... worked fine. Even though these show creates happen 4/5 hours could they be the cause of it somehow only crashing every 4/5 days? Or is it more likely that the crashes I experience are a set of bugs. Maybe one day the assertion failure crashes it, then a few days after another bug crashes it?. Near the beginning of the development I experienced problems where Mysql would crash coz it bugged out on virtual memory. Doubling the virtual memory stopped this. I don't think the error is related to this since it used to singal 11 every time when that was happening. As for the memory consumption, PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This would suggest that memory consumption has decreased (to 79megs?) yet virtual memory has bloated to 1085-79?. This is on the other server mind you. The two servers that are crashing, crashed very recently, but I think they also will begin to exhibit the virtual memory increase, real memory decrease syndrome over the next couple of days. Ric. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 1:01 PM Subject: Re: 4.0.1 Bugs Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted 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 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring
Re: 4.0.1 Bugs
Richard, then it is possible that all the crashes are caused by SHOW CREATE TABLE. The memory corruption bug can crash InnoDB at any place, it is not always caught by an assertion like in the error log. Since the bug is nondeterministic, it is well possible it appears only under some specific database load. Lenz of MySQL AB is working hard to get good Linux builds of 3.23.51 and 4.0.2 done. Let us hope we finally get the new releases soon. 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: Richard Clarke [EMAIL PROTECTED] To: MYSQL [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 3:25 PM Subject: Re: 4.0.1 Bugs Heikki, My set of queries performs about 4/5 show create table queries each hour. The purpose being to drop it then recreate it automatically. Last time I tried truncate it was as slow as delete, but that was near the begining of my development and never saw reason to retry it after show create/drop/create ... worked fine. Even though these show creates happen 4/5 hours could they be the cause of it somehow only crashing every 4/5 days? Or is it more likely that the crashes I experience are a set of bugs. Maybe one day the assertion failure crashes it, then a few days after another bug crashes it?. Near the beginning of the development I experienced problems where Mysql would crash coz it bugged out on virtual memory. Doubling the virtual memory stopped this. I don't think the error is related to this since it used to singal 11 every time when that was happening. As for the memory consumption, PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This would suggest that memory consumption has decreased (to 79megs?) yet virtual memory has bloated to 1085-79?. This is on the other server mind you. The two servers that are crashing, crashed very recently, but I think they also will begin to exhibit the virtual memory increase, real memory decrease syndrome over the next couple of days. Ric. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 1:01 PM Subject: Re: 4.0.1 Bugs Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. 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: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 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=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld
Re: 4.0.1 Bugs
1) CREATE TABLE `summary_rts` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB CREATE TABLE `summary_rts_old` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB create table rank_union select * from summary_rts union select * from summary_rts_old; The error began to occur after the sum of rows was roughly 200,000. InnoDB free space was in the range of 4 gigs and disk space 2gigs. 2) Can't really give tables and queries here since they form a large set of statistical analysis queries. Only information I can helpfully give is that the same set of roughly 30 queries is run every 5 seconds 24/7. After about 5-7 days the db server segv's on both our 4.4-release machines. Ric. - Original Message - From: Carsten Gehling [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; MYSQL [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 9:48 PM Subject: SV: 4.0.1 Bugs Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - 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
Re: 4.0.1 Bugs
Richard, can you upload via ftp gzipped dumps of the two tables in the UNION to /pub/mysql/secret at support.mysql.com? About the crashes which occur in 5 - 7 days: please send me the error log of MySQL 'hostname'.err so that I can resolve the stack dumps, if they exist. 4.0.1 is already a rather old release and several bugs have been fixed since then. Unfortunately, 3.23 does not support unions. 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: Richard Clarke [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, May 19, 2002 12:27 AM Subject: Re: 4.0.1 Bugs 1) CREATE TABLE `summary_rts` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB CREATE TABLE `summary_rts_old` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB create table rank_union select * from summary_rts union select * from summary_rts_old; The error began to occur after the sum of rows was roughly 200,000. InnoDB free space was in the range of 4 gigs and disk space 2gigs. 2) Can't really give tables and queries here since they form a large set of statistical analysis queries. Only information I can helpfully give is that the same set of roughly 30 queries is run every 5 seconds 24/7. After about 5-7 days the db server segv's on both our 4.4-release machines. Ric. - Original Message - From: Carsten Gehling [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; MYSQL [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 9:48 PM Subject: SV: 4.0.1 Bugs Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - 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 - 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
