mysql 5.5 crashed on Debian 6 after server crash
Hi there, I just had my mysql dB crash really badly on a small server I'm running and wonder if someone could point me in the right direction. I'm pretty basic with mysql... I set up master/slave replication to another site, so this could be a backup, so long as the errors were not replicated over there. Mysql boots with these types of errors recorded in syslog. Also is the my.cnf If you know of ideas that could be of help, then I woul dbe really grateful. I've been trying to get this to run for 4 hours solid. Best regards, Simon My.cnf ( My server only has 512Mb of RAM so mysql has to use not a lot). # grep -v ^$ /etc/mysql/my.cnf|grep -v ^# [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address= 127.0.0.1 max_allowed_packet = 1M key_buffer = 16K sort_buffer_size = 64K read_buffer_size = 256K thread_stack = 64K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 34 log_slow_queries= /var/log/mysql/mysql-slow.log query_cache_limit = 64K query_cache_size= 2M expire_logs_days= 10 max_binlog_size = 100M table_open_cache= 4 table_cache = 48 read_rnd_buffer_size = 256K net_buffer_length = 128K server-id = 1 log-bin=/var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 2 max_binlog_size = 15M expire_logs_days= 2 [mysqldump] quick quote-names max_allowed_packet = 1M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ [myisamchk] key_buffer_size = 386K sort_buffer_size = 64K read_buffer_size = 256K Syslog records 36000 lines of errors from mysql and below please find a snip it: Aug 28 11:50:14 emailserver mysqld_safe: Number of processes running now: 0 Aug 28 11:50:14 emailserver mysqld_safe: mysqld restarted Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Note] Plugin 'FEDERATED' is disabled. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: The InnoDB memory heap is disabled Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Compressed tables use zlib 1.2.3.4 Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Using Linux native AIO Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Initializing buffer pool, size = 128.0M Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Completed initialization of buffer pool Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: highest supported file format is Barracuda. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Waiting for the background threads to start Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 InnoDB: 5.5.31 started; log sequence number 1211163767 Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Recovering after a crash using /var/log/mysql/mysql-bin Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Starting crash recovery... Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Crash recovery finished. Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] - '127.0.0.1' resolves to '127.0.0.1'; Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Server socket created on IP: '127.0.0.1'. Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Event Scheduler: Loaded 0 events Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] /usr/sbin/mysqld: ready for connections. Aug 28 11:50:15 emailserver mysqld: Version: '5.5.31-1~dotdeb.0-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) Aug 28 11:50:15 emailserver mysqld: InnoDB: Dump of the tablespace extent descriptor: len 40; hex 01de01660002eaaaeafaabaabbaa; ascf; Aug 28 11:50:15 emailserver mysqld: InnoDB: Serious error! InnoDB is trying to free page 815 Aug 28 11:50:15 emailserver mysqld: InnoDB: though it is already marked as free in the tablespace! Aug 28 11:50:15 emailserver mysqld: InnoDB: The tablespace free space info is corrupt. Aug 28 11:50:15 emailserver mysqld: InnoDB: You may need to dump
Re: mysql 5.5 crashed on Debian 6 after server crash
Hi, I have managed to get the dB open with [mysqld] innodb_force_recovery = 2 I did a mysqldump for all tables, and restarted with innodb_force_recordy but still have the same old error messages. Regards, S On Wed, Aug 28, 2013 at 12:12 PM, Simon Loewenthal simon.loewent...@gmail.com wrote: Hi there, I just had my mysql dB crash really badly on a small server I'm running and wonder if someone could point me in the right direction. I'm pretty basic with mysql... I set up master/slave replication to another site, so this could be a backup, so long as the errors were not replicated over there. Mysql boots with these types of errors recorded in syslog. Also is the my.cnf If you know of ideas that could be of help, then I woul dbe really grateful. I've been trying to get this to run for 4 hours solid. Best regards, Simon My.cnf ( My server only has 512Mb of RAM so mysql has to use not a lot). # grep -v ^$ /etc/mysql/my.cnf|grep -v ^# [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address= 127.0.0.1 max_allowed_packet = 1M key_buffer = 16K sort_buffer_size = 64K read_buffer_size = 256K thread_stack = 64K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 34 log_slow_queries= /var/log/mysql/mysql-slow.log query_cache_limit = 64K query_cache_size= 2M expire_logs_days= 10 max_binlog_size = 100M table_open_cache= 4 table_cache = 48 read_rnd_buffer_size = 256K net_buffer_length = 128K server-id = 1 log-bin=/var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 2 max_binlog_size = 15M expire_logs_days= 2 [mysqldump] quick quote-names max_allowed_packet = 1M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ [myisamchk] key_buffer_size = 386K sort_buffer_size = 64K read_buffer_size = 256K Syslog records 36000 lines of errors from mysql and below please find a snip it: Aug 28 11:50:14 emailserver mysqld_safe: Number of processes running now: 0 Aug 28 11:50:14 emailserver mysqld_safe: mysqld restarted Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 [Note] Plugin 'FEDERATED' is disabled. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: The InnoDB memory heap is disabled Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Compressed tables use zlib 1.2.3.4 Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Using Linux native AIO Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Initializing buffer pool, size = 128.0M Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Completed initialization of buffer pool Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: highest supported file format is Barracuda. Aug 28 11:50:14 emailserver mysqld: 130828 11:50:14 InnoDB: Waiting for the background threads to start Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 InnoDB: 5.5.31 started; log sequence number 1211163767 Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Recovering after a crash using /var/log/mysql/mysql-bin Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Starting crash recovery... Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Crash recovery finished. Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] - '127.0.0.1' resolves to '127.0.0.1'; Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Server socket created on IP: '127.0.0.1'. Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] Event Scheduler: Loaded 0 events Aug 28 11:50:15 emailserver mysqld: 130828 11:50:15 [Note] /usr/sbin/mysqld: ready for connections. Aug 28 11:50:15 emailserver mysqld: Version: '5.5.31-1~dotdeb.0-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) Aug 28 11:50:15 emailserver mysqld: InnoDB: Dump of the tablespace extent descriptor: len 40; hex
mysql server crash
Hello, We see this error in the event viewer. The application, C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe, generated an application error The error occurred on 12/04/2008 @ 13:26:05.398 The exception generated was c005 at address 005471B4 (mysqld_nt). This is repeating daily. Please help us in finding out the solution Thanks in advance for your help Rakesh. ** DISCLAIMER This email and any files transmitted with it, including replies and forwarded copies (which may contain alterations) subsequently transmitted from Firmenich, are confidential and solely for the use of the intended recipient. The contents do not represent the opinion of Firmenich except to the extent that it relates to their official business. **
Server Crash
See Thread at: http://www.techienuggets.com/Detail?tx=24273 Posted on behalf of a User Hi I'm running 5.0.51a on Windows machine. The server crashes with: 080210 15:56:38 InnoDB: Page checksum 2545965801, prior-to-4.0.14-form checksum 1667729874 InnoDB: stored checksum 1195984440, prior-to-4.0.14-form stored checksum 1416128883 InnoDB: Page lsn 4294918911 4294967295, low 4 bytes of lsn at page end 541878627 InnoDB: Page number (if stored to page already) 962664704, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 4294967295 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 13178. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. When I do a error check. I have full backups but when I restore the backup to the original schema name I get the same problem but when I restore to a new schema name the server works fine. I have change my programs to use the new schema name but I don't understand what's causing this problem? I can even delete the original schema name and restore the backup but it still fails. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Crash
See Thread at: http://www.techienuggets.com/Detail?tx=24273 Posted on behalf of a User Help! I'm still facing this problem. I can run a check error successfully but when I try to dump the database using MySQL Administrator halfway through one table the server crashes. See message at the end. I restart the server run a check error and everything checks out but it crashes again when I try and do a backup. I'm using Innodb and my problems appear to have started when I upgraded from 5.0.45 to 5.0.51a. I'm not the normal Mysql admin, who's currently not available. Please any help would be great. InnoDB: stored checksum 4292411360, prior-to-4.0.14-form stored checksum 544367987 InnoDB: Page lsn 6553600 4293656593, low 4 bytes of lsn at page end 1869967971 InnoDB: Page number (if stored to page already) 1067590, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 19200 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 13375. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. In Response To: Hi I'm running 5.0.51a on Windows machine. The server crashes with: 080210 15:56:38 InnoDB: Page checksum 2545965801, prior-to-4.0.14-form checksum 1667729874 InnoDB: stored checksum 1195984440, prior-to-4.0.14-form stored checksum 1416128883 InnoDB: Page lsn 4294918911 4294967295, low 4 bytes of lsn at page end 541878627 InnoDB: Page number (if stored to page already) 962664704, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 4294967295 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 13178. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. When I do a error check. I have full backups but when I restore the backup to the original schema name I get the same problem but when I restore to a new schema name the server works fine. I have change my programs to use the new schema name but I don't understand what's causing this problem? I can even delete the original schema name and restore the backup but it still fails. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data corruption and server crash issues in replicated setup
Hi all, Starting Wednesday night, we observed several weird errors indicative of data corruption shortly before a CPU spike and complete crash on our master db server (opera.oursite.com). opera.oursite.com had crashed twice with signal 11 in recent weeks, but we had never observed any data corruption issues. This was about 15 minutes after an inadvertent and short-lived server id conflict between two slave servers (serenade.oursite.com and adagio.oursite.com). Shortly after, we replaced the master with sonata.oursite.com, we then did a full mysqldump from sonata.oursite.com and imported that dump onto our 3 other db servers and resumed slaving (with opera as a slave to sonata). Then Thursday morning, we brought opera back online as our master. See server list [1] and timeline [2]. Between Thursday and Saturday, we continued to observe apparent data corruption errors, now on sonata as well as opera, as well as many dropped and/or failed connections at various unexpected times, often one error immediately after the other. [3] We took opera offline completely on Sunday morning, with serenade as our new master. We continued to observe data integrity problems on sonata. We then completely disabled the use of slaves on Sunday night and haven't had any issues (yet). Note that adagio, no longer in production, never had any issues either. What insights might you have into this behavior? Might it be due to a known bug in MySQL 5.0.27? How would you go about investigating the cause of this? I am happy to provide any other information you might think relevant. Below is is a list of our DB servers for reference, a timeline of events, and an example of some of the errors we received. Any help you can provide is very much appreciated! Thanks, David [1] List of db servers opera.oursite.com - original master, currently out of production sonata.oursite.com - slave, then temporary master on Wednesday, currently out of production serenade.oursite.com - slave, now current master since Sunday morning adagio.oursite.com - slave that was brought up with serenade's server id originally [2] Timeline Wednesday, September 5th, 8:00 PM - We launch Adagio with conflicting srv ID Wednesday, September 5th, 8:01 PM - We stop adagio, launch with correct ID Wednesday, September 5th, 8:05 PM - We restart replication on adagio, catchup Wednesday, September 5th, 8:16 PM - Data corruption errors CPU spike on opera Wednesday, September 5th, 8:18 PM - Opera dies Wednesday, September 5th, 8:30 PM - Sonata becomes master Wednesday, September 5th, 8:40 PM - Opera comes back online after reboot Wednesday, September 5th, 9:30 PM - Sonata dies with signal 11 Wednesday, September 5th, 9:40 PM - Lost DB connections on sonata Wednesday, September 5th, 10:18 PM - Another lost DB connection on sonata Thursday, September 6th, 3:00 AM - Dump is performed on Sonata Thursday, September 6th, 4:00 AM - Dump imported on opera serenade adagio Thursday, September 6th, 5:00 AM - Opera becomes master again Serenade and Adagio replicate Thursday, September 6th, 3:00 PM - Sonata and import done Sonata back into production Thursday, September 6th, Afternoon - Sonata's replication lagging behind Lots of IO wait on sonata Sonata pulled out of production Thursday, September 6th, 7:05 PM TO 9:29 PM - More apparent data corruption errors and lost connections on opera Thursday, September 6th, 10:19 PM TO 11:20 PM - A ton of failed connections to opera Thursday, September 6th, 11:04 PM TO Friday, September 7th, 1:32 AM - More data corruption errors Friday, September 7th, 3:16 AM - Opera dies again with signal 11 Friday, September 7th, 6:37 AM - Opera dies again with signal 11 (and a bunch of failed connections) Friday, September 7th, 9:18 PM - A bunch more failed/lost connections Sunday, September 9th, 5:00 AM - Opera taken out of production - Sonata and Adagio are slaves, serenade master Sunday, September 9th, 3:06 PM - Incorrect key file error on sonata - work_music table (MyISAM) marked as crashed - more apparent DB corruption, this time on sonata Sunday, September 9th, 3:10 PM - 3:12 PM - Error 127 reading table work_music on sonata Sunday, September 9th, 10:13 PM - 11:39 PM - Error 134 reading table production_favs on sonata Sunday, September 9th, 11:39 PM - Slaves taken completely offline, serenade now the only master [3] Representative Errors (Note that the vast majority of our tables are MyISAM -- including the ones we had errors with) UPDATE work_music, (SELECT SUM(count) AS num_views, COUNT(*) AS num_viewers FROM workmusic_hits WHERE work_music_id='36079') AS hits SET work_music.__num_views=hits.num_views, work_music.__num_viewers=hits.num_viewers WHERE work_music.work_music_id='36079' [nativecode=1031 ** Table storage engine for 'hits' doesn't have this option]
Re: Server-Crash - What to do?
Karel W. Dingeldey wrote: Ok, I will do it more clearly. The server has two harddisks, one for the system, one for the data. The system harddisk breaked, hardware failure. It seems that this happed while mysqld was writing some data, so that some tables are corrupted. Most tables are reconstructable, only the one where I have only the MYD file is very important. On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. My question is, if there is any solution to get a working table from this MYD file. Because the data is saved in this file, IMHO it should. Am I right? If you're using MyISAM tables, then you should have an .MYD (data), .MYI (index) and .frm (table definition?) file for every table. If you don't have a backup copy (or two) of your MYD file by now, make one. Then try repeating the CREATE TABLE statement that defines the table. If you can get a CREATE TABLE that exactly matches the table definition previously used, the data in the MYD file may be accessible again. You may have to move the MYD file out of the way while you do the CREATE TABLE. How you get your CREATE TABLE statement is up to you. Have you got a mysqldump created since the last time the table definion was changed? Was it automatically created by some program which uses the database? If you can't get anywhere with that and the data is valuable, you should consider paid support. --Ludwig Pummer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server-Crash - What to do?
Hello, yesterday my server crashed. The harddisk got corrupted, including some MySQL table files. So MySQL can't see some of its tables, although the table files are visible on the filesystem. Is it possible to repair the files? It's a MySQL 4.1 (Debian Sarge). From one table I only have the MYD file. The documentation says, that this is the file with the data in it. I hope it's possible to reconstruct this table, too?! Thanks, Karel W. Dingeldey PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( man myisamchk I tried myisamchk already. But after running the above rescue attempt, the tables are empty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
At first - do not write anything on the corrupted disk until You have finished rescuing data. As I understad the crash is connected to HW or filesystem (FS) failure (HDD). In that case try to use fsck to repair FS (if it is regular linux FS, like ext2 or ext3, for xfs there were some other utils), do not use myisamchk at first. If Your data is really valuable I would recommend bit-by-bit copy of disk content to similar disk using dd, then try to restore data on that disk (the copy). If You make some mistakes or something goes wrong (on copy), You will still have source disk to start over again (with bit by bit copy). After You have done rescuing FS, use myisamchk on rescued tables. Martins Karel W. Dingeldey wrote: Hello, yesterday my server crashed. The harddisk got corrupted, including some MySQL table files. So MySQL can't see some of its tables, although the table files are visible on the filesystem. Is it possible to repair the files? It's a MySQL 4.1 (Debian Sarge). From one table I only have the MYD file. The documentation says, that this is the file with the data in it. I hope it's possible to reconstruct this table, too?! Thanks, Karel W. Dingeldey PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( man myisamchk I tried myisamchk already. But after running the above rescue attempt, the tables are empty. Then I hope you made backups You, do keep backups right? -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
At first - do not write anything on the corrupted disk until You have finished rescuing data. As I understad the crash is connected to HW or filesystem (FS) failure (HDD). Ok, I will do it more clearly. The server has two harddisks, one for the system, one for the data. The system harddisk breaked, hardware failure. It seems that this happed while mysqld was writing some data, so that some tables are corrupted. Most tables are reconstructable, only the one where I have only the MYD file is very important. On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. My question is, if there is any solution to get a working table from this MYD file. Because the data is saved in this file, IMHO it should. Am I right? Then I hope you made backups You, do keep backups right? Thanks for asking, but I'm that only stupid guy in the world who has no backup with actual data. ;-) No, that's not really funny. :-( I would bite my ass if I could! Karel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. MYD is your data, MYI is your indexes (Indexes are easy to rebuild, you should not worry about that). If myisamchk can't read the MYD, then I don't know what else there is. My guess would be that the data file is damage beyond repair :-( If you have a complete and working set of binary logs (incase you used replication which I doubt), you should also be able to get your data back out of that... But otherwise, I'd say you're out of luck. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql server crash on nested selects (fwd)
-- Forwarded message -- Date: Mon, 7 Mar 2005 15:54:11 -0800 (PST) From: Ronald Braunstein [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: mysql server crash on nested selects Description: Server crashes. This crashes the following servers: 4.1.10 4.1.10 debug release 4.1.11 nightly snapshot with safe memory checking Basically, my query died when I added a select ot.* from ( ...) around an already existing query. It dies on empty tables (and on tables with data). I'll include the schema Query: == set group_concat_max_len = 128000; select ot.* from ( select a.assigned_to , c.ticket_id , type , active_time as active_since , max(corr.received_time) last_message , c2.sender_email as last_replyer , count(corr.correspondence_id) as num_mails , (select received_time from correspondence where correspondence_id = (select min(correspondence_id) from correspondence co, resolvers re where co.ticket_id = c.ticket_id and co.sender_email = re.resolver)) as first_response , if(r1.resolver is null, 'OUR COURT', 'THEIR COURT') ball , now() , c.status , timediff(now(), active_time) as diff from contacts c left join assignment a on c.ticket_id = a.ticket_id , correspondence corr , correspondence c2 left join resolvers r1 on c2.sender_email = r1.resolver where status != 'resolved' and corr.ticket_id = c.ticket_id and c2.correspondence_id = (select max(correspondence_id) from correspondence where ticket_id = c.ticket_id) and active_time '2005-02-25' group by a.assigned_to, c.ticket_id, subject, type, active_time order by active_since ) ot == BackTrace: == (gdb) backtrace full #0 0x40104ae1 in __kill () from /lib/i686/libc.so.6 No locals. #1 0x4003776b in raise (sig=6) at signals.c:65 retcode = 32766 #2 0x40106062 in abort () at ../sysdeps/generic/abort.c:88 act = {__sigaction_handler = {sa_handler = 0x4020a860 _IO_2_1_stderr_, sa_sigaction = 0x4020a860 _IO_2_1_stderr_}, sa_mask = {__val = {0, 1703853748, 1073974542, 0, 146102944, 0, 1073962380, 1075882080, 1074014168, 1703853780, 1075883936, 146102936, 1073833280, 1074381984, 1074384032, 1703853836, 1073795894, 1074382396, 1074384064, 1, 0, 1075882080, 1073962380, 146103072, 1075889844, 1075890448, 1074678075, 1074643060, 1075889844, 146102944, 8, 1703853908}}, sa_flags = 1073796304, sa_restorer = 0x1 __strtol_internal+1} sigs = {__val = {32, 0 repeats 31 times}} #3 0x400fe200 in __assert_fail () at assert.c:74 function = 0x400427d8 \fJ\001 buf = 0x8b55aa0 \210« @\210« @d: item.cc:1739: void Item_field::make_field(Send_field *): Assertion `tmp_field-table_name' failed.\n #4 0x80d9844 in Item_field::make_field (this=0x8b90788, tmp_field=0x658ebfe4) at item.cc:1739 this = (Item_field *) 0x8b90788 #5 0x8121f15 in Protocol::send_fields (this=0x8b4b4a4, list=0x8b4adf8, flag=1) at protocol.cc:536 pos = 0x8b79091 esponse\f? cs = (CHARSET_INFO *) 0x85f9dc0 field = {db_name = 0x8436c3b , table_name = 0x0, org_table_name = 0x0, col_name = 0x8b78e08 now(), org_col_name = 0x8b78e08 now(), length = 19, charsetnr = 63, flags = 129, decimals = 0, type = MYSQL_TYPE_DATETIME} this = (Protocol *) 0x8b4b4a4 it = {base_list_iterator = {list = 0x8b4adf8, el = 0x8b907ec, prev = 0x0, current = 0x0}, No data fields} item = (Item *) 0x8b90788 buff = \f\017?\b\000\000\000\000?\017?\b\004Á\216eø´\bø\a¹\b\034Ð\025\b¸ù¸\bÐ\b¹\b\024Á\216e_Ñ\025\bl¬´\b\b\000\000\000$Á\216exÆ\026\b\000\000\000\000àË\216e$Á\216eÖÐ\026\b¸ù¸\b tmp = {Ptr = 0x658ec060 \f\017?\b, str_length = 80, Alloced_length = 80, alloced = false, str_charset = 0x8615360} prot = {Protocol = {thd = 0x8b4ac60, packet = 0x8b4b518, convert = 0x8b4b52c, field_pos = 6, field_types = 0x0, field_count = 1703854228, _vptr. = 0x862e9e0}, No data fields} local_packet = (String *) 0x8b4b518 thd_charset = (CHARSET_INFO *) 0x8616360 _db_func_ = 0x658ec070 ø´\bø\a¹\b\034Ð\025\b¸ù¸\bÐ\b¹\b\024Á\216e_Ñ\025\bl¬´\b\b _db_file_ = 0x2 Address 0x2 out of bounds _db_level_ = 1703854200 _db_framep_ = (char **) 0x815e1a4 count = 9 #6 0x811c900 in select_send::send_fields (this=0x8b8f9a8, [EMAIL PROTECTED], flag=1) at sql_class.cc:790 this = (select_send *) 0x8b8f9a8 #7 0x8172ae2 in do_select (join=0x8b8f9b8, fields=0x8b4adf8, table=0x0, procedure=0x0) at sql_select.cc:5638 error = 0 join_tab = (JOIN_TAB *) 0x8490941 end_select = (int (*)()) 0x81653e6 JOIN::exec(void)+50
Re: Mysql tuning - server Crash 1
Donny, - Original Message - From: Donny Simonton [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 2:00 AM Subject: RE: Mysql tuning - server Crash 1 Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I am sorry, I fell victim of statements like this: http://fedora.redhat.com/ The Fedora Project is one of the sources for new technologies and enhancements that may be incorporated into Red Hat Enterprise Linux in the future. Red Hat Enterprise Linux is the solution that provides a robust, stable operating system supported by Red Hat, Inc. and a wide variety of independent software vendors (ISVs). For production use, support, service level agreements (SLAs), and ISV support, we proudly ask that you look at Red Hat Enterprise Linux. Now that I Googled fedora + mysql + crash, I noticed that Fedora does not seem to have any more problems than other Linux distros. This bug report suggests a bug in an early version of Fedora Core 1/NPTL/AMD64: bugs.mysql.com/bug.php?id=4570 Fedora moved to NPTL early. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny 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 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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]
Re: Mysql tuning - server Crash 1
Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [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]
Re: Mysql tuning - server Crash 1
Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [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] -- Deluxe Web [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 11:44 AM Subject: Re: Mysql tuning - server Crash 1 Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? yes it may be. Please see: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html --Heikki On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [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] -- Deluxe Web [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]
Re: Mysql tuning - server Crash 1
I have increased innodb buffer pull size to 1G (50% of my 2G ram) What do you suggest (size) for innodb_data_file_path I'm note sure about this variable usage in mysql performance. Thanks. On Mon, 28 Feb 2005 11:47:27 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 11:44 AM Subject: Re: Mysql tuning - server Crash 1 Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? yes it may be. Please see: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html --Heikki On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [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
Re: Mysql tuning - server Crash 1
Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. Let me know if you have any questions. Thank you. I appreciate your help!! Max On Sun, 27 Feb 2005 09:34:40 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, please post all the relevant contents of the .err log. What MySQL version is this? What Linux version? 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 12:05 PM Subject: Mysql tuning - server Crash 1 Hi there, I have a problem with mysql. I have an application basically to track clicks to my website and last night it crashed. Can you have a look aty my situation? I run a server with 2GB RAM and a XEON processor! Thank you. - Max - error log - InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 241.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: ## Diagnostic info printed to the standard output InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 674.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 050225 15:29:55 InnoDB: Assertion failure in thread 1165138864 in file sync0arr.c line 934 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 640 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) 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... Cannot determine thread, fp=0x4572955c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d9be4 0xca1f18 (nil) 0xc9b79c 0xaae27a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/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 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050225 15:31:46 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 050225 15:31:46 Slave I/O thread: Failed reading log event, reconnecting to retry, log 's2-bin.003' position 99034 InnoDB: Thread 1356766128 stopped in file ut0mem.c line 157 InnoDB: Thread 1230535600 stopped in file trx0trx.c line 288 InnoDB: Thread 1310583728 stopped in file ../../innobase/fil/../include/sync0sync.ic line 109 Number of processes running now: 0 050225 15:36:08 mysqld restarted 050225 15:36:23 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 0 1132661722 InnoDB: Doing recovery: scanned up to log sequence number 0 1132661722 InnoDB: In a MySQL replication slave the last master binlog file -- MySQL
Re: Mysql tuning - server Crash 1
Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql tuning - server Crash 1
Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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 tuning - server Crash 1
As for as I understand my server crashed with 640 concurrent connections - http://lists.mysql.com/mysql/180583 -. Am I wrong? I think it could be a problem in my variables (tuning required) Can you have a look at my variables http://lists.mysql.com/mysql/180581 I have 2GB ram, and the server is running apache (perl dbi) and mysql only. Thank you. Max On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max 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 -- 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] -- Deluxe Web [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Mysql tuning - server Crash 2
show status (now) +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 1| | Aborted_connects | 2| | Bytes_received | 18819744 | | Bytes_sent | 67675629 | | Com_admin_commands | 2| | Com_alter_table | 0| | Com_analyze | 0| | Com_backup_table | 0| | Com_begin| 0| | Com_change_db| 323 | | Com_change_master| 0| | Com_check| 0| | Com_commit | 0| | Com_create_db| 0| | Com_create_function | 0| | Com_create_index | 0| | Com_create_table | 24 | | Com_delete | 189 | | Com_delete_multi | 0| | Com_drop_db | 0| | Com_drop_function| 0| | Com_drop_index | 0| | Com_drop_table | 0| | Com_flush| 0| | Com_grant| 0| | Com_ha_close | 0| | Com_ha_open | 0| | Com_ha_read | 0| | Com_insert | 20623| | Com_insert_select| 0| | Com_kill | 0| | Com_load | 0| | Com_load_master_data | 0| | Com_load_master_table| 0| | Com_lock_tables | 0| | Com_optimize | 0| | Com_purge| 0| | Com_rename_table | 0| | Com_repair | 0| | Com_replace | 0| | Com_replace_select | 0| | Com_reset| 2| | Com_restore_table| 0| | Com_revoke | 0| | Com_rollback | 0| | Com_savepoint| 0| | Com_select | 125999 | | Com_set_option | 1650 | | Com_show_binlog_events | 0| | Com_show_binlogs | 0| | Com_show_create | 1658 | | Com_show_databases | 6| | Com_show_fields | 1715 | | Com_show_grants | 0| | Com_show_keys| 136 | | Com_show_logs| 0| | Com_show_master_status | 5| | Com_show_new_master | 0| | Com_show_open_tables | 0| | Com_show_processlist | 0| | Com_show_slave_hosts | 2| | Com_show_slave_status| 11 | | Com_show_status | 2| | Com_show_innodb_status | 0| | Com_show_tables | 528 | | Com_show_variables | 1| | Com_slave_start | 3| | Com_slave_stop | 2| | Com_truncate | 0| | Com_unlock_tables| 0| | Com_update | 38792| | Connections | 33895| | Created_tmp_disk_tables | 41 | | Created_tmp_tables | 46 | | Created_tmp_files| 0| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_commit | 0| | Handler_delete | 164 | | Handler_read_first | 36274| | Handler_read_key | 342880 | | Handler_read_next| 1452689 | | Handler_read_prev| 0| | Handler_read_rnd | 2983 | | Handler_read_rnd_next| 14945472 | | Handler_rollback | 27680| | Handler_update | 13934| | Handler_write| 20901| | Key_blocks_used | 4461 | | Key_read_requests| 287443 | | Key_reads| 4403 | | Key_write_requests | 17035| | Key_writes | 16978| | Max_used_connections | 4| | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 64 | | Open_files | 67 | | Open_streams | 0| | Opened_tables| 31208| | Questions| 225613 | | Qcache_queries_in_cache | 0| | Qcache_inserts | 0| | Qcache_hits | 0| | Qcache_lowmem_prunes | 0| | Qcache_not_cached| 0| | Qcache_free_memory | 0| | Qcache_free_blocks | 0| | Qcache_total_blocks | 0| | Rpl_status | NULL | | Select_full_join | 0| | Select_full_range_join | 0| | Select_range | 10 | | Select_range_check | 0| | Select_scan | 36733| | Slave_open_temp_tables | 0| | Slave_running| ON | | Slow_launch_threads | 0| | Slow_queries | 0| | Sort_merge_passes
Mysql tuning - server Crash 1
Hi there, I have a problem with mysql. I have an application basically to track clicks to my website and last night it crashed. Can you have a look aty my situation? I run a server with 2GB RAM and a XEON processor! Thank you. - Max - error log - InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 241.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: ## Diagnostic info printed to the standard output InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 674.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 050225 15:29:55 InnoDB: Assertion failure in thread 1165138864 in file sync0arr.c line 934 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 640 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) 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... Cannot determine thread, fp=0x4572955c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d9be4 0xca1f18 (nil) 0xc9b79c 0xaae27a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/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 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050225 15:31:46 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 050225 15:31:46 Slave I/O thread: Failed reading log event, reconnecting to retry, log 's2-bin.003' position 99034 InnoDB: Thread 1356766128 stopped in file ut0mem.c line 157 InnoDB: Thread 1230535600 stopped in file trx0trx.c line 288 InnoDB: Thread 1310583728 stopped in file ../../innobase/fil/../include/sync0sync.ic line 109 Number of processes running now: 0 050225 15:36:08 mysqld restarted 050225 15:36:23 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 0 1132661722 InnoDB: Doing recovery: scanned up to log sequence number 0 1132661722 InnoDB: In a MySQL replication slave the last master binlog file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
Max, please post all the relevant contents of the .err log. What MySQL version is this? What Linux version? 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 - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 12:05 PM Subject: Mysql tuning - server Crash 1 Hi there, I have a problem with mysql. I have an application basically to track clicks to my website and last night it crashed. Can you have a look aty my situation? I run a server with 2GB RAM and a XEON processor! Thank you. - Max - error log - InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 241.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: ## Diagnostic info printed to the standard output InnoDB: Warning: a long semaphore wait: --Thread 1175628720 has waited at row0mysql.c line 1683 for 674.00 seconds the semaphore: Mutex at 40e1b168 created file srv0srv.c line 1647, lock var 1 Last time reserved in file trx0trx.c line 309, waiters flag 1 InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 050225 15:29:55 InnoDB: Assertion failure in thread 1165138864 in file sync0arr.c line 934 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 640 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) 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... Cannot determine thread, fp=0x4572955c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d9be4 0xca1f18 (nil) 0xc9b79c 0xaae27a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/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 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050225 15:31:46 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 050225 15:31:46 Slave I/O thread: Failed reading log event, reconnecting to retry, log 's2-bin.003' position 99034 InnoDB: Thread 1356766128 stopped in file ut0mem.c line 157 InnoDB: Thread 1230535600 stopped in file trx0trx.c line 288 InnoDB: Thread 1310583728 stopped in file ../../innobase/fil/../include/sync0sync.ic line 109 Number of processes running now: 0 050225 15:36:08 mysqld restarted 050225 15:36:23 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 0 1132661722 InnoDB: Doing recovery: scanned up to log sequence number 0 1132661722 InnoDB: In a MySQL replication slave the last master binlog file -- 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]
More on 5.02 server crash
MySQL Query Browser 1.1.2 causes 5.02 mysqld-nt.exe server crash as well remote and local. Fredrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Crash
Hello, This morning I got a crash from two mysql servers (one master, one slave). The stack trace is the following on both : 0x8071f44 handle_segfault + 420 0x82a0e38 pthread_sighandler + 184 0x82f09c8 _dl_relocate_object + 1208 0x82d1e4f dl_open_worker + 879 0x82d137a _dl_catch_error + 154 0x82d20d2 _dl_open + 114 0x8290d26 dlopen_doit + 38 0x82d137a _dl_catch_error + 154 0x8291026 _dlerror_run + 246 0x8290d55 dlopen + 37 0x80e1ce3 udf_init__Fv + 595 0x807302b main + 2491 0x82a4864 __libc_start_main + 212 0x8048101 _start + 33 As far as I understand this, is that I've got something wrong with the User Definable Function. Am I right ? We added the UDF on friday, on everything seems going well, but not this morning. Any ideas where I should investigate ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Crash
Hi, After downgrading to the 4.0.15 provided by Mandrake, we found a more explicit message : cannot found xxx function, which was one of our UDF. we recompile the UDF with the extern C (not needed on our dev. server, which is strange...), and everything is fine now. When one query : INSERT INTO func VALUES('myfunc_xxx',1,'libudf_cos.so','function'); are the .so tested and the function tested/loaded ? or do we have the restart the server ? Philippe Poelvoorde wrote: Hello, This morning I got a crash from two mysql servers (one master, one slave). The stack trace is the following on both : 0x8071f44 handle_segfault + 420 0x82a0e38 pthread_sighandler + 184 0x82f09c8 _dl_relocate_object + 1208 0x82d1e4f dl_open_worker + 879 0x82d137a _dl_catch_error + 154 0x82d20d2 _dl_open + 114 0x8290d26 dlopen_doit + 38 0x82d137a _dl_catch_error + 154 0x8291026 _dlerror_run + 246 0x8290d55 dlopen + 37 0x80e1ce3 udf_init__Fv + 595 0x807302b main + 2491 0x82a4864 __libc_start_main + 212 0x8048101 _start + 33 As far as I understand this, is that I've got something wrong with the User Definable Function. Am I right ? We added the UDF on friday, on everything seems going well, but not this morning. Any ideas where I should investigate ? -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Crash
Hi, We try to upgrade to 4.0.20, but we still have a crash on startup. Is there any binaries compatibilities regarding the binairies provided by MySQL ? Philippe Poelvoorde wrote: Hi, After downgrading to the 4.0.15 provided by Mandrake, we found a more explicit message : cannot found xxx function, which was one of our UDF. we recompile the UDF with the extern C (not needed on our dev. server, which is strange...), and everything is fine now. When one query : INSERT INTO func VALUES('myfunc_xxx',1,'libudf_cos.so','function'); are the .so tested and the function tested/loaded ? or do we have the restart the server ? Philippe Poelvoorde wrote: Hello, This morning I got a crash from two mysql servers (one master, one slave). The stack trace is the following on both : 0x8071f44 handle_segfault + 420 0x82a0e38 pthread_sighandler + 184 0x82f09c8 _dl_relocate_object + 1208 0x82d1e4f dl_open_worker + 879 0x82d137a _dl_catch_error + 154 0x82d20d2 _dl_open + 114 0x8290d26 dlopen_doit + 38 0x82d137a _dl_catch_error + 154 0x8291026 _dlerror_run + 246 0x8290d55 dlopen + 37 0x80e1ce3 udf_init__Fv + 595 0x807302b main + 2491 0x82a4864 __libc_start_main + 212 0x8048101 _start + 33 As far as I understand this, is that I've got something wrong with the User Definable Function. Am I right ? We added the UDF on friday, on everything seems going well, but not this morning. Any ideas where I should investigate ? -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with MySQL 4.0.14 - Server Crash
[EMAIL PROTECTED] wrote: I upgrade try to upgrade from MySQL 4.0.12 to 4.0.14 in Linux but it doesn't work. MySQL always crashs with error 11 Segmentation fault. I don't know why but I try to dump database (about 200,000 lines) and install MySQL 4.0.14 windows version and try to import database again to MySQL windows version. Nothing change database server crash. Then I downgrade to MySQL 3.23.55 (windows). There is no any problem, import work fine. Then I try to use MySQL 4.0.12 (windows), there is no problem again. I try to upgrade my Linux server to MySQL 4.0.13. The result is same as MySQL 4.0.14. Then I try to find which Query is crash. And know that MySQL 4.0.14 (Windows) always crashs when try to read field that include blank value in key (primary, index, unique) even MyISAM or InnoDB, server will crash. This bug is fixed in v4.0.15. * Fixed crash in comparisons of strings using the tis620 character set. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with MySQL 4.0.14 - Server Crash
Hi! On Sep 10, Victoria Reznichenko wrote: [EMAIL PROTECTED] wrote: I upgrade try to upgrade from MySQL 4.0.12 to 4.0.14 in Linux but it doesn't work. MySQL always crashs with error 11 Segmentation fault. I don't know why but I try to dump database (about 200,000 lines) and install MySQL 4.0.14 windows version and try to import database again to MySQL windows version. Nothing change database server crash. Then I downgrade to MySQL 3.23.55 (windows). There is no any problem, import work fine. Then I try to use MySQL 4.0.12 (windows), there is no problem again. I try to upgrade my Linux server to MySQL 4.0.13. The result is same as MySQL 4.0.14. Then I try to find which Query is crash. And know that MySQL 4.0.14 (Windows) always crashs when try to read field that include blank value in key (primary, index, unique) even MyISAM or InnoDB, server will crash. This bug is fixed in v4.0.15. * Fixed crash in comparisons of strings using the tis620 character set. Vita, why do you think that this bugfix is relevant ? How do you know he is using tis620 charset - was it in some other email ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with MySQL 4.0.14 - Server Crash
Victoria, Thanks. -- __ Sign-up for your own personalized E-mail at Mail.com http://www.mail.com/?sr=signup CareerBuilder.com has over 400,000 jobs. Be smarter about your job search http://corp.mail.com/careers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with MySQL 4.0.14 - Server Crash
I upgrade try to upgrade from MySQL 4.0.12 to 4.0.14 in Linux but it doesn't work. MySQL always crashs with error 11 Segmentation fault. I don't know why but I try to dump database (about 200,000 lines) and install MySQL 4.0.14 windows version and try to import database again to MySQL windows version. Nothing change database server crash. Then I downgrade to MySQL 3.23.55 (windows). There is no any problem, import work fine. Then I try to use MySQL 4.0.12 (windows), there is no problem again. I try to upgrade my Linux server to MySQL 4.0.13. The result is same as MySQL 4.0.14. Then I try to find which Query is crash. And know that MySQL 4.0.14 (Windows) always crashs when try to read field that include blank value in key (primary, index, unique) even MyISAM or InnoDB, server will crash. Try following query ---[CUT BEGIN]-- CREATE TABLE `temp` ( `a` VARCHAR( 100 ) NOT NULL , `b` VARCHAR( 50 ) NOT NULL , `c` VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( `a` , `b` ) ); INSERT INTO `temp` ( `a` , `b` , `c` ) VALUES ( 'fadfasdf', '', 'asdf' ); SELECT * FROM `temp` WHERE `a` = 'fadfasdf' AND `b` = '' ---[CUT END]-- I test this query in my PC and notebook, it is same. Below is my.cnf ---[CUT BEGIN] [mysqld] port=3306 socket=MySQL default-character-set = tis620 skip-locking set-variable= key_buffer=4M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M #server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following if you are NOT using BDB tables skip-bdb #skip-innodb # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:400M #innodb_data_home_dir = c:\mysql\ibdata #innodb_log_group_home_dir = c:\mysql\ibdata #innodb_log_arch_dir = c:\mysql\ibdata #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=10M #set-variable = innodb_log_buffer_size=2M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 set-variable = innodb_buffer_pool_size=32M #set-variable = innodb_additional_mem_pool_size=2M #set-variable = innodb_file_io_threads=2 #set-variable = innodb_lock_wait_timeout=50 ---[CUT END]--- I must use default-character-set = tis620 because I store Thai text in database. If anyone have any suggestion, please help. -- __ Sign-up for your own personalized E-mail at Mail.com http://www.mail.com/?sr=signup CareerBuilder.com has over 400,000 jobs. Be smarter about your job search http://corp.mail.com/careers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb deadlock leads to server crash
Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de
Re: innodb deadlock leads to server crash
Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326
Re: innodb deadlock leads to server crash
Hi! I have now fixed the hang to MySQL-4.0.12. I let mysqld to release the adaptive hash S-latch when it passes the control to the client as it sends result rows of a SELECT to the client. Can you test the latest 4.0.12 bk tree? Best regards, Heikki sql query ... Subject: Re: innodb deadlock leads to server crash From: Heikki Tuuri Date: Mon, 3 Mar 2003 10:42:48 +0200 Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash
innodb deadlock leads to server crash
Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown Architecture: i686 Some paths: /usr/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.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32 /usr/lib/libc-client.a Configure command: ./configure '--prefix=/usr' '--without-debug' '--enable-shared' '--without-mit-threads' '--libexecdir=/usr/sbin' '--localstatedir=/var/mysql' '--enable-thread-safe-client' '--sysconfdir=/etc' '--datadir=/usr/share' '--enable-large-files' '--without-readline' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/run/mysql.socket' '--enable-strcoll' '--with-comment=noris network MySQL' '--with-docs' '--with-bench' '--without-berkeley-db' '--without-bench' '--with-innodb' 'CPPFLAGS=-DTHREAD_SAFE_CLIENT' 'CXXFLAGS=-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' - Before posting, please check
Re: innodb deadlock leads to server crash
Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown Architecture: i686 Some paths: /usr/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.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt i' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32
Server crash selecting from merge table
Description: Certain queries on merge tables reliably fail or crash the server as the script below demonstrates. How-To-Repeat: Run this script: #! /bin/bash # This script demonstrates a bug in the way MySQL handles merge tables. The bug depends on # the index; it doesn't manifest if there is no index or if the fields are indexed separately. mysql -vv EOF USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; # Create two tables with the same definition. CREATE TABLE a ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ); CREATE TABLE b ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ); # Populate the tables. INSERT INTO a (ID, Date) VALUES (1, '2002-01-01'), (1, '2002-01-02'), (1, '2002-01-03'); INSERT INTO b (ID, Date) VALUES (1, '2002-02-01'), (1, '2002-02-02'), (1, '2002-02-03'); # Create a merge table combining the previous two. CREATE TABLE c ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ) TYPE=MERGE UNION=(a, b); # This works. SELECT MIN(Date) FROM c WHERE ID = 1; # This fails by returning NULL. SELECT MAX(Date) FROM c WHERE ID = 1; # Going straight to the component tables works though. SELECT MAX(Date) FROM a WHERE ID = 1; SELECT MAX(Date) FROM b WHERE ID = 1; # The different results from these might be a clue. EXPLAIN SELECT MIN(Date) FROM c WHERE ID = 1; EXPLAIN SELECT MAX(Date) FROM c WHERE ID = 1; # This crashes the server. SELECT MIN(Date), MAX(Date) FROM c WHERE ID = 1; EOF Fix: Submitter-Id: submitter ID Originator:Rob Steele Organization: FatKat, Inc. MySQL support: none Synopsis: Selecting from merge table crashes server Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.54 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.54-Max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 min 44 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.002 Environment: System: Linux warren.fatkat.com 2.4.18-19.8.0 #1 Thu Dec 12 05:39:29 EST 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/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/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Oct 2 19:15 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 19:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 18:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 18:50 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Oct 2 19:53 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - 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: Server crash upon remote access
Hi! This is a bug in RedHat 8.0. Also an up-to-date RedHat 7.x is affected by this bug. Sollution: Start mysql with --skip-name-resolve option. More info: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=74943 --- Lars Andre -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: 16. oktober 2002 11:56 To: [EMAIL PROTECTED] Subject: re: Server crash upon remote access x-gylee+mysql, Wednesday, October 16, 2002, 11:11:44 AM, you wrote: Description: xgmaidc MySQL server crashes when a remote client connects. xgmaidc A local client connecting thru unix socket file does not have the problem. How-To-Repeat: xgmaidc Connect via a remote host. Fix: xgmaidc No solution yet. Please, check it on binary distribution of MySQL. -- For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
Server crash upon remote access
Description: MySQL server crashes when a remote client connects. A local client connecting thru unix socket file does not have the problem. How-To-Repeat: Connect via a remote host. Fix: No solution yet. Submitter-Id: submitter ID Originator:Aldrian Gintingsuka Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Server crash upon remote access Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.52 (Source distribution) Environment: System: Linux www.indo.com 2.4.9-34 #1 Sat Jun 1 06:10:23 EDT 2002 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.2 2.96-108.7.2) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 7 07:43 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1285884 Sep 9 12:10 /lib/libc-2.2.4.so -rw-r--r--1 root root 27336078 Sep 9 11:48 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 9 11:48 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/depot/mysql-new/mysql --localstatedir=/var/lib/mysql-new --enable-thread-safe-client --enable-assembler --with-unix-socket-path=/var/lib/mysql-new/mysql.sock --with-mysqld-user=mysql --without-debug --with-low-memory --without-docs --without-bench --with-berkeley-db - 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: Server crash upon remote access
x-gylee+mysql, Wednesday, October 16, 2002, 11:11:44 AM, you wrote: Description: xgmaidc MySQL server crashes when a remote client connects. xgmaidc A local client connecting thru unix socket file does not have the problem. How-To-Repeat: xgmaidc Connect via a remote host. Fix: xgmaidc No solution yet. Please, check it on binary distribution of MySQL. -- For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
MySQL server crash on Red Hat 8.0
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: MySQL server crash on Red Hat 8.0 Description: The safe_mysqld daemon makes segmentation fault very often. Output: Starting mysqld daemon with databases from /var/lib/mysql /usr/local/mysql/bin/safe_mysqld: line 273: 2744 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2747 - killed 021015 16:20:03 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2806 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2810 - killed 021015 16:21:03 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2847 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2850 - killed 021015 16:21:39 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2870 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2873 - killed 021015 16:22:15 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2893 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2896 - killed 021015 16:29:19 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2933 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2936 - killed 021015 16:34:02 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2964 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2967 - killed 021015 16:34:52 mysqld restarted How-To-Repeat: Connect with the mysql client and wait. Fix: Submitter-Id: Originator: [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Server restarts Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.53 (Source distribution) Environment: System: Linux nicklas.mlm.tactel.se 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/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/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Oct 3 15:26 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql -- Nicklas Ekstrand, Ph.D. Tactel AB Tel: +46-40-250655 Mob: +46-70-3998808 - 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: MySQL server crash on Red Hat 8.0
Hi, It happens to me too.I think are some problems with GLIBC. RED HAT 8.0 install MySQL ver 3.23.52.I install from source distribution MySQL ver 4.0.4 and nothing change. MySQL seems can not create socket file(BAD FILE DESCRIPTOR) and for this reason make the error.It want to create a child (fork()) process without a valid connection..IMPOSSIBLE to work. Regards, Gelu ___ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Nicklas Ekstrand [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 4:46 PM Subject: MySQL server crash on Red Hat 8.0 SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: MySQL server crash on Red Hat 8.0 Description: The safe_mysqld daemon makes segmentation fault very often. Output: Starting mysqld daemon with databases from /var/lib/mysql /usr/local/mysql/bin/safe_mysqld: line 273: 2744 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2747 - killed 021015 16:20:03 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2806 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2810 - killed 021015 16:21:03 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2847 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2850 - killed 021015 16:21:39 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2870 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2873 - killed 021015 16:22:15 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2893 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2896 - killed 021015 16:29:19 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2933 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2936 - killed 021015 16:34:02 mysqld restarted /usr/local/mysql/bin/safe_mysqld: line 273: 2964 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 2967 - killed 021015 16:34:52 mysqld restarted How-To-Repeat: Connect with the mysql client and wait. Fix: Submitter-Id: Originator: [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Server restarts Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.53 (Source distribution) Environment: System: Linux nicklas.mlm.tactel.se 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/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/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Oct 3 15:26 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql -- Nicklas Ekstrand, Ph.D. Tactel AB Tel: +46-40-250655 Mob: +46-70-3998808
MySQL Server crash...
Hiya, We're running a 1gz server which crashed around an hour ago and I'm trying to find out why, it's averaging 60 queries per second according to mysql status and I figure that's why the server crashed although it had happily handled 15 million questions up to that point. Is there a logfile which would tell me what caused mysql to crash? while it restarted just fine it did cause some problems (got the nice 'Warning: Couldn't save resultset' message on the front page of the website). Oh and is there a way to check all tables in a single command with SQL instead of using myisamchk? I don't like the idea of using flush tables and then running myisamchk with a lag between the two operations for something to happen. Thanks in advance for any advice, William. - 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: MySQL Server crash...
Run myisamchk BEFORE you start the server! William_dw -- Sqlcoders wrote: Hiya, We're running a 1gz server which crashed around an hour ago and I'm trying to find out why, it's averaging 60 queries per second according to mysql status and I figure that's why the server crashed although it had happily handled 15 million questions up to that point. Is there a logfile which would tell me what caused mysql to crash? while it restarted just fine it did cause some problems (got the nice 'Warning: Couldn't save resultset' message on the front page of the website). Oh and is there a way to check all tables in a single command with SQL instead of using myisamchk? I don't like the idea of using flush tables and then running myisamchk with a lag between the two operations for something to happen. Thanks in advance for any advice, William. - 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: MySQL Server crash...
Sorry, I might not have been clear, the mySQL server magically restarted itself once it crashed apparently, I got woken up with a call saying the homepage had a 'funny message', and when I hopped into the mysql console it said it had been up 1 hour 30 minutes (considering it said it was up for 40 days last night, and top still says up for 40 days, and some tables were corrupt, I assume mySQL crashed). (over here trying to figure out how to make show all tables pipe into check table), William. - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: William_dw -- Sqlcoders [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 22, 2002 12:32 PM Subject: Re: MySQL Server crash... Run myisamchk BEFORE you start the server! William_dw -- Sqlcoders wrote: Hiya, We're running a 1gz server which crashed around an hour ago and I'm trying to find out why, it's averaging 60 queries per second according to mysql status and I figure that's why the server crashed although it had happily handled 15 million questions up to that point. Is there a logfile which would tell me what caused mysql to crash? while it restarted just fine it did cause some problems (got the nice 'Warning: Couldn't save resultset' message on the front page of the website). Oh and is there a way to check all tables in a single command with SQL instead of using myisamchk? I don't like the idea of using flush tables and then running myisamchk with a lag between the two operations for something to happen. Thanks in advance for any advice, William. - 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: Instant Mysql server crash problem with berkeley db enabled
Please try to reproduce a bug with our MySQL-Max binary for Linux. Your compiler and glibc versions are a bit too old for the current MySQL sources. -- 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
Instant Mysql server crash problem with berkeley db enabled
Hi, here is my half selfmade :) bug report. I´ve tried this on different machine with different linux version (other one is linux 2.2.18) And I also tried more RAM (512 MB ) without any change to the error. Maybe you can help me : From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Instant Mysql server crash problem with berkeley db enabled Description: Mysql crash after invoking with : safe_mysqld --skip-bdb --bdb-no-recover --log --skip-locking How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: none Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.43 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux BoH 2.4.10 #1 Sat Oct 6 13:40:44 CEST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 10 2001 /lib/libc.so.4 - libc.so .4.7.6 -rwxr-xr-x 1 root root 634880 Apr 29 1996 /lib/libc.so.4.7.6 -rwxr-xr-x 1 root root 4190668 Jul 22 1999 /lib/libc.so.6 -rw-r--r-- 1 root root 19921908 Jul 22 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jul 22 1999 /usr/lib/libc.so Configure command: ./configure --with-berkeley-db Perl: This is perl, version 5.005_03 built for i586-linux CPU states: 97.8% user, 2.8% system, 98.8% nice, 0.0% idle Mem: 126608K av, 122924K used, 3684K free, 0K shrd, 3112K buff Swap: 131536K av, 0K used, 131536K free 54592K cached Error Message from mysqld : 011025 00:37:14 mysqld started 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=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 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... Bogus stack limit or frame pointer, fp=0xbfffec9c, stack_bottom=0x6070607, threa d_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x6070607 is invalid pointer thd-thread_id=2 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 2 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 011025 00:37:14 mysqld ended - 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
MySQL Server Crash. Where to Start?
I have a busy website that uses mysql. It crashed this morning. I don't know where to begin to fix it. I did notice in my httpd-error.log that I've been getting a bunch of errors: DBI::db=HASH(0x8101d68)-disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /www/PlayByWeb.cgi line 244. There were thousands of these errors. Not just this script either. Could this crash the server? I've taken steps to fix this error by adding $sth-finish() statements to my perl scripts. mysql version 3.22 - 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