Re: Corruption? Performance issue + strange 'explain'
Hi, Have you tried changing the date format like delete from TelecomAccountPosting where LocID=19014 and InvDate='2008-01-15'; I know it shouldn't matter but I have had similar issues due to the date format. Also are you sure there are 3773 records matching this criteria - according to your table structure there is an UNIQUE index on LocID and InvDate so there should not be more than one record. Dobromir Velev WebSitePulse.com On Tuesday 22 January 2008 02:39, Daniel Kasak wrote: On Tue, 2008-01-22 at 11:23 +1100, Chris wrote: Why is it saying 'Impossible WHERE noticed after reading const tables'? http://dev.mysql.com/doc/refman/4.1/en/explain.html MySQL has read all const (and system) tables and notice that the WHERE clause is always false. ie - no rows match that query and so there's nothing to 'explain'. There must be a problem then. In this particular example, there were 3773 records returned by this select. I'd suspect that the time is spent trying to check or clean up the foreign key reference. Are there lots of locations with that id in the tlocations table? 1 I'd also assume that since it's named 'id' it would be a primary key (and indexed) ? Indexed, yes. I'll go ahead with that restore from a backup tonight. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption? Performance issue + strange 'explain'
Daniel Kasak wrote: Greetings. I have a script which seems to be bringing our server to its knees. I've isolated one query which is doing it: delete from TelecomAccountPosting where LocID = 19014 and InvDate = '20080115'; I have an index which covers both LocID and InvDate: CREATE TABLE `TelecomAccountPosting` ( `ID` mediumint(8) unsigned NOT NULL auto_increment, `LocID` mediumint(8) unsigned NOT NULL default '0', `BatchNo` mediumint(8) unsigned NOT NULL default '0', `AccountNo` varchar(20) NOT NULL default '', `InvDate` date NOT NULL default '-00-00', PRIMARY KEY (`ID`), UNIQUE KEY `IDX_LocID_InvDate` (`LocID`,`InvDate`), KEY `IDX_AccountNo` (`AccountNo`), KEY `IDX_LocID` (`LocID`), KEY `IDX_BatchNo` (`BatchNo`), CONSTRAINT `0_21` FOREIGN KEY (`LocID`) REFERENCES `TLocations` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=111073 DEFAULT CHARSET=latin1 If I do an 'explain' on a *select* with the above values, I get: mysql explain select * from TelecomAccountPosting where LocID = 19014 and InvDate = '20080115'; ++-+---+--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+--+-+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Impossible WHERE noticed after reading const tables | ++-+---+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) mysql Why is it saying 'Impossible WHERE noticed after reading const tables'? http://dev.mysql.com/doc/refman/4.1/en/explain.html MySQL has read all const (and system) tables and notice that the WHERE clause is always false. ie - no rows match that query and so there's nothing to 'explain'. Every other such delete query PRIOR to this one executes in WAY less than 1 second. But from this query on, the deletes take well over a minute, and MySQL has a GREAT deal of problem servicing all other database clients. I'd suspect that the time is spent trying to check or clean up the foreign key reference. Are there lots of locations with that id in the tlocations table? I'd also assume that since it's named 'id' it would be a primary key (and indexed) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption? Performance issue + strange 'explain'
On Tue, 2008-01-22 at 11:23 +1100, Chris wrote: Why is it saying 'Impossible WHERE noticed after reading const tables'? http://dev.mysql.com/doc/refman/4.1/en/explain.html MySQL has read all const (and system) tables and notice that the WHERE clause is always false. ie - no rows match that query and so there's nothing to 'explain'. There must be a problem then. In this particular example, there were 3773 records returned by this select. I'd suspect that the time is spent trying to check or clean up the foreign key reference. Are there lots of locations with that id in the tlocations table? 1 I'd also assume that since it's named 'id' it would be a primary key (and indexed) ? Indexed, yes. I'll go ahead with that restore from a backup tonight. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corruption in db. myisam bad? innodb good?
You would need to find out the reason for the crash to prevent or minimize it. The reason may be external to mysql. Innodb can get really, really slow when tables get physically large if you don't have a similar amount of RAM. MyISAM doesn't support transactions, so no, that wouldn't help. If you want to test Innodb as the table type, setup a replication/slave server with the tables as Innodb instead. See how your queries run against it. The table structure needs to match on a replication/salev server, not the table type. You can also make the slave the master in the event of a crash. - Original Message - From: tim h [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 22, 2007 2:56 PM Subject: corruption in db. myisam bad? innodb good? hi. database is myisam, 5.8Gb, 7mil records. recently had some corruption i think due to mysqld service failure. 10 tabes were crashed. question -- how can i prevent or minimize this? Will switching to innodb help? Will converting all my queries to transactions help? thanks. -- Tim H Berkeley, CA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corruption after database restore
Baba, - Original Message - From: Baba Buehler [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 07, 2004 12:34 AM Subject: corruption after database restore I'm having a corruption problem after doing a backup and then a restore with ibbackup (v1.40). After restoring from the backup, when mysqld starts I get: 041006 07:46:53 mysqld started InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 0 page number 5877102, 7'th page in dblwr buf. InnoDB: Warning: an inconsistent page in the doublewrite buffer The sum of data file sizes is 5767168 pages How is it possible that the doublewrite buffer contains page number 5877102? InnoDB: space id 0 page number 5877103, 8'th page in dblwr buf. InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 0 page number 5877104, 9'th page in dblwr buf. InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 0 page number 5877105, 10'th page in dblwr buf. InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 0 page number 5877106, 11'th page in dblwr buf. InnoDB: Error: tablespace size stored in header is 6029312 pages, but InnoDB: the sum of data file sizes is 5767168 pages Are you sure you did not forget some ibdata file from the my.cnf you are using? Looks like the tablespace data files are smaller than they should be. Please show the my.cnf that you used in taking the backup, as well as in restoring the backup. 041006 7:46:54 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.18-standard' socket: '/tmp/mysql4.sock' port: 3306 Then when queries start hitting the database, I start getting a lot of errors like (full backtrace included below): InnoDB: Assertion failure in thread 36874 in file fil0fil.c line 1204 These assertions happen because the data files are too small. I've restored from this backup multiple times with the same results, so I'm presuming its the backup itself that is corrupt. Does anyone have any ideas on what might cause ibbackup to corrupt files, as the backup appeared to complete successfully? Please show the printout of the backup run, as well as the --restore run. InnoDB: Error: trying to access page number 141623 in space 0 InnoDB: which is outside the tablespace bounds. Look, it says that page number 142 000 is outside the tablespace data files, though above you had over 5 million pages in data files! You have probably forgotten some data files when you restarted mysqld. Thanks, baba 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 InnoDB: Error: trying to access page number 141623 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041006 7:49:38 InnoDB: Assertion failure in thread 36874 in file fil0fil.c line 1204 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576 read_buffer_size=4190208 max_used_connections=1 max_connections=35 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 287603 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87b2120 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=0xbfe7df28, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8071f44 handle_segfault + 420 0x82a0e38 pthread_sighandler + 184 0x81edc77 fil_io + 1287 0x81b416b buf_read_page_low + 203 0x81b45c9 buf_read_page + 41 0x81a6fb8 buf_page_get_gen + 888 0x8167771 btr_cur_open_at_rnd_pos + 897 0x8171a4e btr_estimate_number_of_different_key_vals + 670 0x8101c99 dict_update_statistics_low + 89 0x8101d04 dict_update_statistics + 20 0x80f78f8 dict_table_get_and_increment_handle_count + 552 0x80ccc5b open__11ha_innobasePCciUi + 203 0x80c6b54 ha_open__7handlerPCcii + 36 0x8094595 openfrm__FPCcT0UiUiUiP8st_table + 5317 0x8090d27 open_unireg_entry__FP3THDP8st_tablePCcN22 + 87 0x8090178 open_table__FP3THDPCcN21Pb + 888 0x809102b open_tables__FP3THDP13st_table_list + 75 0x8091308 open_and_lock_tables__FP3THDP13st_table_list + 24 0x807ccb3 mysql_execute_command__Fv + 947
RE: Corruption and my.cnf
Dear Mark, I think your emailer has also experienced corruption since your from address is actually mine. Please set it to your own email address, such as [EMAIL PROTECTED] Thanks, Mike -Original Message- From: Michael McTernan Sent: 09 April 2004 12:18 To: [EMAIL PROTECTED] Subject: Re: Corruption and my.cnf I've experienced more corruption lately on my main site since I moved to my own server running 4.0.17max. The site is very busy (60GB a month) and the tables are large. I didn't have this level of problems on the rental server. What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Looking at my dmesg output.. end_request: I/O error, dev 03:42 (hdb), sector 52228450 hdb: read_intr: status=0x59 { DriveReady SeekComplete DataRequest Error } hdb: read_intr: error=0x40 { UncorrectableError }, LBAsect=56276830, high=3, low=5945182, sector=52228450 Is it possible this is related to my MySQL table corruption issues? There are more of these in the file. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- 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: Corruption and my.cnf
Mark, - Original Message - From: Mark Susol|Ultimate Creative Media [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, April 08, 2004 11:25 PM Subject: Re: Corruption and my.cnf I've experienced more corruption lately on my main site since I moved to = my own server running 4.0.17max. The site is very busy (60GB a month) and th= e tables are large. I didn't have this level of problems on the rental serv= er. =20 What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Looking at my dmesg output.. end_request: I/O error, dev 03:42 (hdb), sector 52228450 hdb: read_intr: status=3D0x59 { DriveReady SeekComplete DataRequest Error } hdb: read_intr: error=3D0x40 { UncorrectableError }, LBAsect=3D56276830, high=3D3= , low=3D5945182, sector=3D52228450 Is it possible this is related to my MySQL table corruption issues? There are more of these in the file. it might be. Corruption has a cause, and usually it is the OS/drivers or the hardware. Mark S=FAsol 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html --- u l t i m a t e =AD CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- 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: Corruption and my.cnf
I've experienced more corruption lately on my main site since I moved to my own server running 4.0.17max. The site is very busy (60GB a month) and the tables are large. I didn't have this level of problems on the rental server. What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Looking at my dmesg output.. end_request: I/O error, dev 03:42 (hdb), sector 52228450 hdb: read_intr: status=0x59 { DriveReady SeekComplete DataRequest Error } hdb: read_intr: error=0x40 { UncorrectableError }, LBAsect=56276830, high=3, low=5945182, sector=52228450 Is it possible this is related to my MySQL table corruption issues? There are more of these in the file. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and weird service terminations
Hi Matt, I'm sorry to say that even after upgrading to MySQL-4.0.16, our server is still suffering from the exact same symptoms, albeit on a Linux system. We have recently done some serious testing to rule out hardware failure, but the error persists. Tables corrupt and mysqld restarts with a signal 11 error code approximately every tenth minute. Any words of the wise will be greatly appreciated.. :) From: Matt W Date: October 28 2003 2:59am Subject: Re: Corruption and weird service terminations For the corruption, upgrade to 4.0.16, since it may be caused by a corruption bug in versions before 4.0.15. Matt -- Kind regards, Marcus Brofeldt [EMAIL PROTECTED] www.Heste-Nettet.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and weird service terminations
Hi Mike, For the corruption, upgrade to 4.0.16, since it may be caused by a corruption bug in versions before 4.0.15. Matt - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 12:07 PM Subject: RE: Corruption and weird service terminations Hi everyone, I'm having some odd intermitent problems with MySQL-nt 4.0.14 on a Win2K server platform that's fully updated with MS service packs and security patches. My large database on this server is having some quite significant corruption problems requiring me to run repair on tables almost every other day. Related to this problem, I am having trouble with the database server service crashing a couple times a day. Is this a problem other people have run in to before and if so, is there a solurion to it? Thanks for any help you can give me. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption of MyISAM table when deleting/inserting ~7000 rows
Hello Tommy! We suspect that the problem is related to the fact that we delete ~7000 rows and inserts the same shortly thereafter. The mysql-manual mentions that you should run an optimize table after deleting many rows from a table, but doesn't list it as an requirement. If you're having an intermittent but serious problem I'd interpret 'should' to mean 'required' as a safety net. If you see any -patterns- logged regarding repairs then that'd be very useful to the developers. They can't improve the software without documentation or exact instructions about how to reproduce the problem themselves. Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] - 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: Corruption
This table is getting corrupted all the time (twice a week). We have only SELECTS and INSERTS with little updates. (we have 140.000 registers growing faster to 100 :)) CREATE TABLE conta ( idConta int(8) unsigned NOT NULL auto_increment, idDominio int(6) unsigned DEFAULT '0' NOT NULL, conta varchar(30) DEFAULT '' NOT NULL, senha varchar(32) DEFAULT '' NOT NULL, usermount varchar(100) DEFAULT '' NOT NULL, userlock int(1) DEFAULT '0' NOT NULL, senha2 varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (idConta), UNIQUE idDominio (idDominio,conta) ); The worst is tha we have another table with 1.000.000 inserts/updates/deletes/selects and this table never get corrupted. What should I do to prevent corruptions. (every day I run an optimize, check table... etc..) Help us find and fix the bug You should work on creating a repeatable case of corruption. Start by upgrading to 3.23.33 once it is released. 3.23.33 will print the trouble query to the error log in the crash handler along with the backtrace on x86 Linux, so this should make debugging a lot easier. I'm running mysql under a NetServer LC2000 (HP) with RAID 5 and 1GB RAM, PIII 667/600. Shoudl I use -O key_buffer and/or table_cache. This will not help, although different setting that what you have may randomly obscure the bug. You should not rely on this, though. What really does FLUSH TABLES? it's ok do it once a day? It closes all the currently open tables and flushed all the buffers related to tables to to the disk. This is needed only when you want to be sure that what you have on the disk is current, and is normally not necessary. By the way I got this interesting message in my slave log: 010207 12:47:42 Error reading packet from server: Contratulations! You have hit the magic number and can win sweepstakes if you report the bug (0) If your version is pre-3.23.29, you are hitting the bug that has already been fixed. This should not happen in newer versions, if it does, it is a bug, just like the message says. Actually, looking at the message, you **are** running an old version. The evidence is the misspeled "Congratulations" in the error message. The fix is to upgrade - to 3.23.33 once it gets out, or if you cannot wait, 3.23.31. -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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: Re: Corruption
mysql select VERSION(); +-+ | VERSION() | +-+ | 3.23.31-log | +-+ 1 row in set (0.00 sec) I swer I'm running 3.23.31 (with pgcc) but let's forget about "Contratulation message", I think the error is something related to master.info position. Thanks for your answers to another questions. Well, give me some advices to get some stability. I'll prepare a LVS cluster to avoid problems with unexpected corruption, I gave up to use mysql with pgcc (I'm afraid about it). I'm planning use a Bi-Directional replication, 2 servers with --log-slave-update and other required replication stuff, so I'll put them replicating each other and if some table get corrupted I'll change the LVS switch and my system will work fine until I fix the problem. Next time I see some bug I'll send a report with mysqlbug. Thanks, Best Regards ... Rodolfo Sikora = This table is getting corrupted all the time (twice a week). We have only SELECTS and INSERTS with little updates. (we have 140.000 registers growing faster to 100 :)) CREATE TABLE conta ( idConta int(8) unsigned NOT NULL auto_increment, idDominio int(6) unsigned DEFAULT '0' NOT NULL, conta varchar(30) DEFAULT '' NOT NULL, senha varchar(32) DEFAULT '' NOT NULL, usermount varchar(100) DEFAULT '' NOT NULL, userlock int(1) DEFAULT '0' NOT NULL, senha2 varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (idConta), UNIQUE idDominio (idDominio,conta) ); The worst is tha we have another table with 1.000.000 inserts/updates/deletes/selects and this table never get corrupted. What should I do to prevent corruptions. (every day I run an optimize, check table... etc..) Help us find and fix the bug You should work on creating a repeatable case of corruption. Start by upgrading to 3.23.33 once it is released. 3.23.33 will print the trouble query to the error log in the crash handler along with the backtrace on x86 Linux, so this should make debugging a lot easier. I'm running mysql under a NetServer LC2000 (HP) with RAID 5 and 1GB RAM, PIII 667/600. Shoudl I use -O key_buffer and/or table_cache. This will not help, although different setting that what you have may randomly obscure the bug. You should not rely on this, though. What really does FLUSH TABLES? it's ok do it once a day? It closes all the currently open tables and flushed all the buffers related to tables to to the disk. This is needed only when you want to be sure that what you have on the disk is current, and is normally not necessary. By the way I got this interesting message in my slave log: 010207 12:47:42 Error reading packet from server: Contratulations! You have hit the magic number and can win sweepstakes if you report the bug (0) If your version is pre-3.23.29, you are hitting the bug that has already been fixed. This should not happen in newer versions, if it does, it is a bug, just like the message says. Actually, looking at the message, you **are** running an old version. The evidence is the misspeled "Congratulations" in the error message. The fix is to upgrade - to 3.23.33 once it gets out, or if you cannot wait, 3.23.31. -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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