Re: Table crashes
[EMAIL PROTECTED] wrote: Can someone help me out on this..I need a detailed document Dude, you need to be specific. Why tables in MySQL crash is very broad topic. which engine you are referring to ? Tables can crash due to various reasons and they have different recovery/repair procedures. good application + DB design/descent HW sizing and great monitoring will help reduce the crashes If you do some more research on this topic you can figure out some reasons and some pointers to avoid failures. Regards, Ranjeet Walunj Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
The manual is your friend for everything, besides any additional book like Paul Dubois (cool book). But, in order to help you, we need more information about the crash: errors, version, table type, hardware, logs, etc. in that way we can offer solutions to your specific issue. feel free to send us more details and we'll be happy to help. Carlos [EMAIL PROTECTED] wrote: Can someone help me out on this..I need a detailed document Regards, Amarnath Shivashankar SQL Database Management | GSMC | Wipro Infotech | Mysore | Toll free: 1800-345-5656 | Spirit of Wipro : Intensity to Win | Act with Sensitivity | Unyielding Integrity From: Amarnath Shivashankar (WI01 - Services) Sent: Tuesday, October 23, 2007 11:40 AM To: 'mysql@lists.mysql.com' Subject: Table crashes Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table crashes
Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
RE: Table crashes
Can someone help me out on this..I need a detailed document Regards, Amarnath Shivashankar SQL Database Management | GSMC | Wipro Infotech | Mysore | Toll free: 1800-345-5656 | Spirit of Wipro : Intensity to Win | Act with Sensitivity | Unyielding Integrity From: Amarnath Shivashankar (WI01 - Services) Sent: Tuesday, October 23, 2007 11:40 AM To: 'mysql@lists.mysql.com' Subject: Table crashes Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: mysql bug 9670 OPTIMIZE TABLE crashes
On Mon, 1 Aug 2005, Heikki Tuuri wrote: unfortunately, the bug fix was never made to 4.0. It is only in 4.1.12 and later. Hi Heikki, Thanks for your response. The fix seems to consists only of a few lines: http://mysql.bkbits.net:8080/mysql-4.1/[EMAIL PROTECTED]|[EMAIL PROTECTED] It looks like this could be easily applied to MySQL 4.0: http://mysql.bkbits.net:8080/mysql-4.0/anno/sql/[EMAIL PROTECTED]|src/|src/sql|hist/sql/ha_innodb.cc|diffs/sql/[EMAIL PROTECTED] Is this an option? Or is MySQL 4.0 falling into the 'discontinued' range of products nowadays? I could apply the patch myself, but I'd rather use the official binaries from the MySQL site than rolling my own package. Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql bug 9670 OPTIMIZE TABLE crashes
Hi, I am experiencing assertion failures described in the following bugreport: http://bugs.mysql.com/bug.php?id=9670 The bug has been closed, but I'm running the most current 4.0.25 version of MySQL. The report mentions downgrading to 4.0.23 or upgrading to 4.1. Is this really neccessary or should this bug already be fixed in 4.0.25? Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql bug 9670 OPTIMIZE TABLE crashes
Matthijs, in the bug report Heikki Tuuri wrote: *** [13 Apr 20:59] Heikki Tuuri The bug was introduced in 4.0.24 and 4.1.10. The fix will be in 4.1.12 and 5.0.5. *** Best regards, Mikhail. - Original Message - From: Matthijs van der Klip [EMAIL PROTECTED] To: MySQL mailing list mysql@lists.mysql.com Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 01, 2005 4:08 PM Subject: mysql bug 9670 OPTIMIZE TABLE crashes Hi, I am experiencing assertion failures described in the following bugreport: http://bugs.mysql.com/bug.php?id=9670 The bug has been closed, but I'm running the most current 4.0.25 version of MySQL. The report mentions downgrading to 4.0.23 or upgrading to 4.1. Is this really neccessary or should this bug already be fixed in 4.0.25? Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- 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]
Re: mysql bug 9670 OPTIMIZE TABLE crashes
On Mon, 1 Aug 2005, Mikhail Entaltsev wrote: in the bug report Heikki Tuuri wrote: *** [13 Apr 20:59] Heikki Tuuri The bug was introduced in 4.0.24 and 4.1.10. The fix will be in 4.1.12 and 5.0.5. *** Hi Mikhail, Thank you for your quick response. I had noticed myself that the fix seemed to address only 4.1 and 5.0, but I'd like to hear an official statement that this bug will not be fixed in 4.0. Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql bug 9670 OPTIMIZE TABLE crashes
Matthis, unfortunately, the bug fix was never made to 4.0. It is only in 4.1.12 and later. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: Matthijs van der Klip [EMAIL PROTECTED] Vastaanottaja: MySQL mailing list mysql@lists.mysql.com Kopio: [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Monday, August 01, 2005 5:08 PM Aihe: mysql bug 9670 OPTIMIZE TABLE crashes Hi, I am experiencing assertion failures described in the following bugreport: http://bugs.mysql.com/bug.php?id=9670 The bug has been closed, but I'm running the most current 4.0.25 version of MySQL. The report mentions downgrading to 4.0.23 or upgrading to 4.1. Is this really neccessary or should this bug already be fixed in 4.0.25? Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeated table crashes (Errno 145). Why?
Hi, Recently I've been seeing the following kind of error message for several of the MyISAM tables in my database: General error: Can't open file: 'build_packages.MYI'. (errno: 145) Running a simple repair operation on the corrupted table will make MySQL happy again, even though the repair statement doesn't indicate that it found any corruption: mysql repair table build_packages; +++--+--+ | Table | Op | Msg_type | Msg_text | +++--+--+ | dvs.build_packages | repair | status | OK | +++--+--+ 1 row in set (0.13 sec) But unfortunately this doesn't solve the underlying problem. I'm seeing this kind error at least once a week in the system, though not always the same table. Also, the server .err file is not being updated with any useful info when these failures occur. The system is under fairly constant and heavy use. It typically has about 60 simultaneous (automated) clients at any given time, and a large percentage of the query operations are updates/inserts/deletes. There are about 15 active tables, some of which are about 200 megs. The only unusual characteristic of this setup might be the rather frequent use of replace into commands to synchronize pairs of tables. The database is running Windows 2003 server. Any hints on how I can work around this would be greatly appreciated. I don't know what kind of information will help in diagnosing this. Here's the output of show variables. If there is something else I can include please let me know. +-+- | Variable_name | Value +-+- | back_log| 50 | basedir | C:\mysql\ | bdb_cache_size | 8388572 | bdb_log_buffer_size | 32768 | bdb_home| C:\mysql\data\ | bdb_max_lock| 1 | bdb_logdir | | bdb_shared_data | OFF | bdb_tmpdir | C:\WINDOWS\TEMP\ | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set | latin1 | concurrent_insert | ON | connect_timeout | 5 | convert_character_set | | datadir | C:\mysql\data\ | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 1800 | ft_boolean_syntax | + -()~*:| | ft_min_word_len | 4 | ft_max_word_len | 254 | ft_max_word_len_for_sort| 20 | ft_stopword_file| (built-in) | have_bdb| YES | have_crypt | NO | have_innodb | YES | have_isam | YES | have_raid | NO | have_symlink| YES | have_openssl| NO | have_query_cache| YES | init_file | | innodb_additional_mem_pool_size | 1048576 | innodb_buffer_pool_size | 8388608 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir| | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 8 | innodb_flush_log_at_trx_commit | 0 | innodb_fast_shutdown| ON | innodb_flush_method | | innodb_lock_wait_timeout| 50 | innodb_log_arch_dir | .\ | innodb_log_archive | OFF | innodb_log_buffer_size | 1048576 | innodb_log_file_size| 5242880 | innodb_log_files_in_group | 2 | innodb_log_group_home_dir | .\ | innodb_mirrored_log_groups | 1 | interactive_timeout | 28800 | join_buffer_size| 131072 | key_buffer_size | 8388572 | language| C:\mysql\share\english\ | large_files_support | ON | local_infile| ON | log | OFF | log_update | OFF | log_bin | ON | log_slave_updates | OFF | log_slow_queries| OFF | log_warnings| OFF | long_query_time | 10 | low_priority_updates| OFF | lower_case_table_names | ON | max_allowed_packet | 1048576 | max_binlog_cache_size | 4294967295 | max_binlog_size | 1073741824 | max_connections | 200 | max_connect_errors | 10 | max_delayed_threads
Table crashes
Hello Some one my tables have crashed (again). I had to repair them ASAP since the db is in my production enviroment. 1. How can I find the cause of the crashes? 2. I've created a my.cnf and added the following lines. Is the format correct? [mysqld] set-variable = myisam-recover=BACKUP,FORCE set-variable = myisam_sort_buffer_size=4M I need to get this going without getting crashes every week, please help. /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
Hi, Jonas crashes occurs for some reasons like : - unclean tables ( tables are closed improprely), - hardware problems - something goes wrong in the system - know bugs Check that your tables are clean, before start using them. It seems that you said the REPAIR doesn't occurs after you configuring related variables in the my.cnf file ? Because the daemon will not start when the my.cnf contains any errors (variable names), the format is to be correct. Can you indicates what MySQL version you run and your OS version. Regards Thierno6C - MySQL 4.0.15 - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 7:05 AM Subject: Table crashes Hello Some one my tables have crashed (again). I had to repair them ASAP since the db is in my production enviroment. 1. How can I find the cause of the crashes? 2. I've created a my.cnf and added the following lines. Is the format correct? [mysqld] set-variable = myisam-recover=BACKUP,FORCE set-variable = myisam_sort_buffer_size=4M I need to get this going without getting crashes every week, please help. /Jonas -- 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]
SV: Table crashes
Hello crashes occurs for some reasons like : - unclean tables ( tables are closed improprely), - hardware problems - something goes wrong in the system - know bugs Check that your tables are clean, before start using them. MyODBC 3.51 is the only way that I access the database. (MyODBC is used in a multithreaded server and in InternetInformationServer (asp)) The server is coded in c++ and got a class that acts like a odbc pool (keeps connections open during the lifetime of the server and handles them to threads that requests a db connection) How are tables getting unclean? When are tables closed? Should I disconnect/connect when a thread is handling the dbconnection back to the pool? It seems that you said the REPAIR doesn't occurs after you configuring related variables in the my.cnf file ? Because the daemon will not start when the my.cnf contains any errors (variable names), the format is to be correct. No. I asked if my configuration were correct. I've just added it to my.cnf. Can you indicates what MySQL version you run and your OS version. Win2000 Mysql 4.0.12 (mysqld.exe) MyODBC was an old version (2.x), I've just installed 3.51.06 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
Hello, MyODBC 3.51 is the only way that I access the database. (MyODBC is used in a multithreaded server and in InternetInformationServer (asp)) The server is coded in c++ and got a class that acts like a odbc pool (keeps connections open during the lifetime of the server and handles them to threads that requests a db connection) It may help to know more about the work of features (multithreaded server, 'odbc pool') you are using with MySQL. I have only one vague idea it above. I know that MySQL is multi-threaded (it may have many queries on the same table simultaneously). Remarks that keeps connections open during the lifetime of the server and handles them to threads that requests a db connection SHOULDN'T be the culprit of table crash. In contrary this is a good thing; PHP, that i used, do the same job with the feature of PERSISTENT CONNECTIONS. Also the version of MyODBC (MyODBC 3.51 ) you are using is declared STABLE by MySQL , though MySQL says : 'some issues brought up appear to be application-related and independent of the ODBC driver or underlying database server. How are tables getting unclean? Has many ways like : - the mysql daemon server stops in a abnormal way , you can see it in the mysql error log file - a query fail to continue processing - a system problem When are tables closed? A table is opened for each concurrent acces, A table is closed when the connection using them is closed / lost Regards Thierno6C - MySQL 4.0.15 - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 10:05 AM Subject: SV: Table crashes Hello crashes occurs for some reasons like : - unclean tables ( tables are closed improprely), - hardware problems - something goes wrong in the system - know bugs Check that your tables are clean, before start using them. MyODBC 3.51 is the only way that I access the database. (MyODBC is used in a multithreaded server and in InternetInformationServer (asp)) The server is coded in c++ and got a class that acts like a odbc pool (keeps connections open during the lifetime of the server and handles them to threads that requests a db connection) How are tables getting unclean? When are tables closed? Should I disconnect/connect when a thread is handling the dbconnection back to the pool? It seems that you said the REPAIR doesn't occurs after you configuring related variables in the my.cnf file ? Because the daemon will not start when the my.cnf contains any errors (variable names), the format is to be correct. No. I asked if my configuration were correct. I've just added it to my.cnf. Can you indicates what MySQL version you run and your OS version. Win2000 Mysql 4.0.12 (mysqld.exe) MyODBC was an old version (2.x), I've just installed 3.51.06 -- 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]
Table crashes after optimize
I am the admin of a Mysql 4.0.14-standard databas running on Solaris 9. A developer and I created a shell script that does a mysqldump of the database and then runs an optimize on the largest table we have. The command line is as follows: $EXECPATH/mysql --host=172.65.12.10 --user=user --password=pass --execute=optimize table MESSAGES test Here is the output we got this morning: test.MESSAGES optimizeerror 13 when fixing table test.MESSAGES optimizestatus Operation failed We ran this same script last night and it ran fine! Here is something the developer sent me. it is a description of the problems he sees in the database that seemed to be caused by the optimize: When I start mysql from command line and try to use test database, I get ... Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'MESSAGES' Database changed When I try to desc MESSAGES or select some data, I get ... ERROR 1016: Can't open file: 'MESSAGES.MYI'. (errno: 144) When I run myisamchk on .MYI tables, I get ... Checking MyISAM file: /usr/local/mysql/data/test/MESSAGES.MYI Data records:5202 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: warning: Size of indexfile is: 34373632 Should be: 445440 myisamchk: warning: Size of datafile is: 38468336 Should be: 24260468 - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 28440 keys of 5202 - check record links myisamchk: error: Record-count is not ok; is 28440 Should be: 5202 myisamchk: warning: Found 16653 deleted blocks Should be: 0 myisamchk: warning: Found 45093 parts Should be: 5203 parts MyISAM-table '/usr/local/mysql/data/test/MESSAGES.MYI' is corrupted Fix it using switch -r or -o Anyone have any ideas what is going on? I need help ASAP on this, if you can! Thanks, Ben Ricker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes after optimize
On Thu, 25 Sep 2003 09:23:13 -0500 Ben Ricker [EMAIL PROTECTED] wrote: Here is the output we got this morning: test.MESSAGES optimizeerror 13 when fixing table test.MESSAGES optimizestatus Operation failed # perror 13 Error code 13: Permission denied check if it's allright with rights on this table ? --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My Table Crashes Frequently
Hello,
I have a table, not too large (200,000+). It crashes all the time. I
should qualifiy that: it crashes when I perform updates to it. I can
qualify that further, and say that it crashes when I update certain fields.
One more thing, the fields that it crashes seem to all be part of a fulltext
index. So, I'm wondering if maybe fulltext indexes for my version of MySQL
are unstable, should I elminate them, upgrade or what.
Here is what I've got:
MySQL (4.0.0-alpha) running on a FREEBSD Box
Latest ODBC drivers for MySQL running on a Win2k box - all of my code is in
ASP and uses ADO / SQL via the MYSql ODBC.
I've included the structure of the table below.
Thanks for the help!!
Karl
CREATE TABLE submit (
submitid int(11) NOT NULL auto_increment,
name varchar(50) default '',
title varchar(80) NOT NULL default '',
url varchar(100) NOT NULL default '',
email varchar(50) default '',
keywords varchar(255) NOT NULL default '',
subject varchar(50) default '',
phone varchar(50) default '',
company varchar(50) default '',
address1 varchar(50) default '',
address2 varchar(50) default '',
city varchar(50) default '',
state varchar(25) default '',
zipcode varchar(25) default '',
country varchar(50) default '',
description varchar(255) default NULL,
service varchar(30) NOT NULL default '',
dbedit timestamp(14) NOT NULL,
amount varchar(16) NOT NULL default '0',
submitdate datetime default NULL,
phone2 varchar(30) NOT NULL default '',
callstatus char(2) NOT NULL default 'NC',
notes varchar(255) NOT NULL default 'Add your comments here (max 255
char.)',
caller varchar(10) NOT NULL default '',
followupdate varchar(15) NOT NULL default '',
pending enum('Y','N') default 'Y',
famappvd enum('Y','N') default 'N',
optkeywords varchar(255) NOT NULL default '',
opturl varchar(255) NOT NULL default '',
srvcOptCode varchar(16) NOT NULL default '',
srvcAuthCode varchar(16) NOT NULL default '',
srvcExpDate date NOT NULL default '-00-00',
srvcStatus varchar(10) NOT NULL default '',
preferred int(11) NOT NULL default '0',
submittedby varchar(15) NOT NULL default '',
PRIMARY KEY (submitid),
UNIQUE KEY url_unique (url),
KEY service (service),
KEY name_index (name),
KEY opturl_index (opturl),
KEY optkeywords_index (optkeywords),
KEY submitdate_index (submitdate),
FULLTEXT KEY full_4_index (title,keywords,description,url)
) TYPE=MyISAM PACK_KEYS=1;
-
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: bdb table crashes?
I've had many problems with BDB table crashes. Up until now, I haven't really cared, running safe_mysqld, the database just restarts, and things proceed as normal. But recently I've had some strange behaviour, where table data is deleted after an alter table statement. Upon looking through the error logs, I found many cases much like yours, but NOT due to delete queries, mine were just select queries. I don't know if the 2 problems are related at all, the crashes, and the data deletion I'm runing MYSQL server version: 3.23.36 Here is 1 sample of what I'm getting. -- mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x4007d0fe 0x810afb0 0x81554ca 0x819bc49 0x819108a 0x818e9ea 0x81699d6 0x81659f0 0x8109119 0x80e2776 0x80e252b 0x80d9bcf 0x80c0cd3 0x80c3d8b 0x80c008f 0x80bf599 Stack trace successful, trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x82a9338 = Select l.name l_name , s.name s_name, description, shopa_notes, not_allowed, year from list l, school s, grade g where list_id = 50 and s.school_id=l.school_id and g.grade_id=l.grade_id thd-thread_id = 10283 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 10283 did to cause the crash. In some cases of really bad corruption, this value may be invalid Please use the information above to create a repeatable test case for the crash, and send it to [EMAIL PROTECTED] Number of processes running now: 0 011217 13:17:06 mysqld restarted - Original Message - From: Geoffrey Soh [EMAIL PROTECTED] To: Mysql@Lists. Mysql. Com [EMAIL PROTECTED] Sent: Monday, January 21, 2002 10:27 PM Subject: bdb table crashes? Hi, Having some problems with a particular BDB table, which crashes once in a while. Just wondering if anyone has experienced such BDB table crashes? The relevant info is included below for troubleshooting. Let me know if I need to send anything else. Appreciate any help. Thanks! Description: BDB table crashes on delete query, such delete queries execute flawlessly but once in a while, the table crashes on a delete, and causes a restart of MySQL How-To-Repeat: Not very repeatable, happens intermittently and at random. Fix: No fix yet. Submitter-Id: submitter ID Originator: Organization: Ufinity Pte Ltd MySQL support: none Synopsis: bdb table crashes Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux axle 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 12 02:51 /lib/libc.so.6 - libc-2 .2.2.so -rwxr-xr-x2 root root 1236396 Apr 7 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 7 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 7 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/home/mysql/mysql-lite --localstatedir= /home/mysql/data/mysql-lite --with-berkeley-db --with-raid --enable-assemble r --with-mysqld-ldflags=-all-static Stack Trace : 0x806c666 handle_segfault__Fi + 258 0x818d3ee btr_cur_pessimistic_update + 386 0x80b4c6e delete_row__12ha_myisammrgPCc + 14 0x80feb28 txn_abort + 184 0x81429c2 __bam_repl_recover + 566 0x8138859 __bam_pgout + 41 0x8135204 __qam_c_get + 1008 0x8111a66 __db_rename + 926 0x80b6b65 update_row__11ha_berkeleyPCcPc + 1153 0x80b6d5a update_row__11ha_berkeleyPCcPc + 1654 0x809b2e1 generate_table__FP3THDP13st_table_listP8st_table + 673 0x8074115 mysql_execute_command__Fv + 5705 0x8075a73 my_yyoverflow__FPPsPP7YYSTYPEPi + 31 0x8072082 do_command__FP3THD + 862 0x80715a4 handle_one_connection__FPv + 180 Schema from table : mysql desc user_status; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | userid| varchar(255)| | PRI | | | | domain| varchar(150)| | PRI | | | | password | varchar(100)| YES | | NULL
Re: bdb table crashes?
Kalok Lo writes: I've had many problems with BDB table crashes. Up until now, I haven't really cared, running safe_mysqld, the database just restarts, and things proceed as normal. But recently I've had some strange behaviour, where table data is deleted after an alter table statement. Upon looking through the error logs, I found many cases much like yours, but NOT due to delete queries, mine were just select queries. I don't know if the 2 problems are related at all, the crashes, and the data deletion Hi! Please try a latest MySQL-Max binary from our site and see if the problem reccurs. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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
bdb table crashes?
Hi, Having some problems with a particular BDB table, which crashes once in a while. Just wondering if anyone has experienced such BDB table crashes? The relevant info is included below for troubleshooting. Let me know if I need to send anything else. Appreciate any help. Thanks! Description: BDB table crashes on delete query, such delete queries execute flawlessly but once in a while, the table crashes on a delete, and causes a restart of MySQL How-To-Repeat: Not very repeatable, happens intermittently and at random. Fix: No fix yet. Submitter-Id: submitter ID Originator: Organization: Ufinity Pte Ltd MySQL support: none Synopsis: bdb table crashes Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux axle 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 12 02:51 /lib/libc.so.6 - libc-2 .2.2.so -rwxr-xr-x2 root root 1236396 Apr 7 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 7 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 7 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/home/mysql/mysql-lite --localstatedir= /home/mysql/data/mysql-lite --with-berkeley-db --with-raid --enable-assemble r --with-mysqld-ldflags=-all-static Stack Trace : 0x806c666 handle_segfault__Fi + 258 0x818d3ee btr_cur_pessimistic_update + 386 0x80b4c6e delete_row__12ha_myisammrgPCc + 14 0x80feb28 txn_abort + 184 0x81429c2 __bam_repl_recover + 566 0x8138859 __bam_pgout + 41 0x8135204 __qam_c_get + 1008 0x8111a66 __db_rename + 926 0x80b6b65 update_row__11ha_berkeleyPCcPc + 1153 0x80b6d5a update_row__11ha_berkeleyPCcPc + 1654 0x809b2e1 generate_table__FP3THDP13st_table_listP8st_table + 673 0x8074115 mysql_execute_command__Fv + 5705 0x8075a73 my_yyoverflow__FPPsPP7YYSTYPEPi + 31 0x8072082 do_command__FP3THD + 862 0x80715a4 handle_one_connection__FPv + 180 Schema from table : mysql desc user_status; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | userid| varchar(255)| | PRI | | | | domain| varchar(150)| | PRI | | | | password | varchar(100)| YES | | NULL| | | timestamp | bigint(20) unsigned | YES | MUL | NULL| | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) Snippet from the error log : Errors from /home/mysql/data/mysql-lite/hotspring.err 020121 15:31:36 mysqld restarted /home/mysql/mysql-lite/libexec/mysqld: ready for connections 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 agaist 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=67104768 record_buffer=131072 sort_buffer=524280 max_used_connections=73 max_connections=100 threads_connected=73 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 129531 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806c666 0x818d3ee 0x80b4c6e 0x80feb28 0x81429c2 0x8138859 0x8135204 0x8111a66 0x80b6b65 0x80b6d5a 0x809b2e1 0x8074115 0x8075a73 0x8072082 0x80715a4 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8375c48 = DELETE FROM user_status WHERE userid = '91235434' AND domain = 'ufinity' thd-thread_id=30 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 30 did to cause the crash. In some cases of really bad corruption
Re: Table Crashes Constantly on prod server!!
Heikki Tuuri wrote: Hi! Michael Blood wrote: I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram. I have been getting an table handler returns error 127 error If I fix the table with myisamchk -r or -o it will work for a while and then I will get the same error again. Try dumping and restroing the table. (backup your database files) mysqldump --add-drop-table -q db table tmp.sql (possibly remove the table.* files here) mysql db tmp.sql You may also want to either start mysqld with the flush option or run the SQL FLUSH TABLES before a program exit or occasionally while running. b. What is your operating system version? Linux kernel 2.2.14 probably has bugs in its i/o system. Upgrading to a newer kernel version might help, though people have reported disk corruption also from newer kernels. Are you using a binary compiled by MySQL AB? Some gcc versions require right compiler flags to produce stable code. Regards, Heikki Hi, In January, we installed MySQL 3.23.30 on Linux 2.2.14 (RedHat 6.2) on a bi-processor production machine and we had a lot of table corruption problems; these problems seem to appear under high load only since we were not able to reproduce it on our integration platforms. These corruption problems disappeared when we rebooted in single-processor mode. Last week, we have upgraded this database server to Linux 2.4.3 (RedHat 7.1 with kernel update) and it is now running in dual processor mode without any problem. As a side effect, MySQL is also much more stable. It used to crash (signal 11) almost once per day even in single processor mode, it is now running since we booted the machine 8 days ago. Hope this helps -- Joseph Bueno NetClub/Trader.com - 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
Table Crashes Constantly on prod server!!
I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram. I have been getting an table handler returns error 127 error If I fix the table with myisamchk -r or -o it will work for a while and then I will get the same error again. Here are some of the stats on the table rows:1126904 index len 48091136 auto inc:1819060 table size(MYD):166 MB index size(MYI):48 MB The table has a big int, primary key, auto increment here is the out put from a myisamchk -r - recovering (with sort) MyISAM-table '../var/table.MYI' Data records: 1126904 - Fixing index 1 Found link that points at 166818084 (outside data file) at 73992880 Found link that points at 166818044 (outside data file) at 83991788 Found link that points at 166818064 (outside data file) at 125436372 - Fixing index 2 - Fixing index 3 Data records: 1126901 Index 1 is the primary key on the bigint, auto increment column. Another point I have a varchar field which has an index on it. I have heard that this can cause some problems but it has never been an issue before. Load average on the server is low 90-95% idle cpu. However mysqld is consistently using about 30% of the Memory and total utilization of memory on the server is holding about 85% of standard memory but 0.2% of swap used. Could this have anything to do with the settings for the server? Thanks for any help Michael Blood +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 2491 | | Aborted_connects | 5 | | Bytes_received | 153499291 | | Bytes_sent | 482722726 | | Connections | 3012 | | Created_tmp_disk_tables | 8 | | Created_tmp_tables | 54| | Created_tmp_files| 2 | | Delayed_insert_threads | 1 | | Delayed_writes | 114 | | Delayed_errors | 0 | | Flush_commands | 20| | Handler_delete | 76317 | | Handler_read_first | 9743 | | Handler_read_key | 7400844 | | Handler_read_next| 8428214 | | Handler_read_prev| 0 | | Handler_read_rnd | 29873 | | Handler_read_rnd_next| 190413426 | | Handler_update | 123277| | Handler_write| 6176687 | | Key_blocks_used | 249376| | Key_read_requests| 80361241 | | Key_reads| 173557| | Key_write_requests | 15295279 | | Key_writes | 1161751 | | Max_used_connections | 27| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 40| | Open_files | 78| | Open_streams | 0 | | Opened_tables| 1163 | | Questions| 398056| | Select_full_join | 744 | | Select_full_range_join | 0 | | Select_range | 758 | | Select_range_check | 0 | | Select_scan | 4916 | | Slave_running| OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 65| | Sort_merge_passes| 0 | | Sort_range | 226 | | Sort_rows| 29639 | | Sort_scan| 753 | | Table_locks_immediate| 674329| | Table_locks_waited | 74| | Threads_cached | 5 | | Threads_created | 114 | | Threads_connected| 14| | Threads_running | 1 | | Uptime | 34266 | - 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: Table Crashes Constantly on prod server!!
Michael Blood wrote: I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram. I have been getting an table handler returns error 127 error If I fix the table with myisamchk -r or -o it will work for a while and then I will get the same error again. Try dumping and restroing the table. (backup your database files) mysqldump --add-drop-table -q db table tmp.sql (possibly remove the table.* files here) mysql db tmp.sql You may also want to either start mysqld with the flush option or run the SQL FLUSH TABLES before a program exit or occasionally while running. b. - 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: Table Crashes Constantly on prod server!!
Hi! Michael Blood wrote: I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram. I have been getting an table handler returns error 127 error If I fix the table with myisamchk -r or -o it will work for a while and then I will get the same error again. Try dumping and restroing the table. (backup your database files) mysqldump --add-drop-table -q db table tmp.sql (possibly remove the table.* files here) mysql db tmp.sql You may also want to either start mysqld with the flush option or run the SQL FLUSH TABLES before a program exit or occasionally while running. b. What is your operating system version? Linux kernel 2.2.14 probably has bugs in its i/o system. Upgrading to a newer kernel version might help, though people have reported disk corruption also from newer kernels. Are you using a binary compiled by MySQL AB? Some gcc versions require right compiler flags to produce stable code. Regards, Heikki - 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
