Monitoring for corrupt tables and transiently failing master INSERTs
We're trying to write a monitoring process for our master so that if a table is corrupt it will raise flags which can then trigger operations. We can do the basic stuff such as asserting that the port is open and that we can ping the machine but I want to test if any INSERT/UPDATE/DELETEs are failing on the master due to table corruption. For example, if you have a functioning DB and then deliberately corrupt the tables (for testing of course) I'd want SOME way to detect that INSERTs were failing on this table. There's no way to currently detect this I believe. SHOW STATUS doesn't help nor does SHOW TABLE STATUS. Any pointers? -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corrupt tables und extremly slow querys
Hello, we encounter a strange behaviour in one of our Database Server. Our setup consists of a SUN V440 which has mounted a NFS Share from a Network Appliance File Server. This setup have been running a long term without complications although we knew that there are many statements concerning not using the NFS for storage. But in the last few Months we occasionally notice some sort of "running threads" accumulation and very slow queries, on tables with many inserts/updates, as if the Server locks itself. Sometimes we have also corrupt tables. however we can eliminate this condition by "refreshing" the Server via "mysqladmin refresh". So we got one or two questions ;o) - what does "mysqladmin refresh" do exactly - regarding the setup with NFS, etc. - is this some case of the well known "lock contention" - we can't figure that out for now - could it be that we just tampering with some bad configuration, although this setups runs trouble-free for "ages" - what part should we take for a closer inspection (NFS, Locking, Lock Contention, etc.) For those of you whishing to have moe information an that, continue reading ;o) output of MySQL - server status Created tmp disk tables 357243 Created tmp tables46643862 Created tmp files 0 Delayed insert threads2 Delayed writes380091 Delayed errors352 Flush commands7 Handler commit0 Handler delete2117679 Handler read first123506 Handler read key 3719247462 Handler read next 1733660317 Handler read prev 864031371 Handler read rnd 479522668 Handler read rnd next 662387797 Handler rollback 0 Handler update21722296 Handler write 562227857 Key blocks used 1869200 Key read requests 2110396355 Key reads 2791032 Key write requests48615181 Key writes19078871 Max used connections 2241 Not flushed key blocks0 Not flushed delayed rows 0 Open tables 1169 Open files1261 Open streams 0 Opened tables 52579 Qcache queries in cache 49303 Qcache inserts142463949 Qcache hits 271540313 Qcache lowmem prunes 38858 Qcache not cached 227747419 Qcache free memory968456720 Qcache free blocks38868 Qcache total blocks 138417 Rpl statusNULL Select full join 9344 Select full range join124 Select range 11702375 Select range check0 Select scan 2146643 Slave open temp tables0 Slave running OFF Slow launch threads 0 Slow queries 597768 Sort merge passes 0 Sort range4423868 Sort rows 572734169 Sort scan 47110770 Table locks immediate 838476154 Table locks waited6643354 Threads cached5 Threads created 5098289 Threads connected 24 Threads running 2 Servertraffic: Traffic ø per hour Received460.159 KB 935.627 Bytes Send26.752 KB 54.394 Bytes Total 486.912 KB 990.022 Bytes Connections ø per hour % Failed 27.771 55,14 0,07 % Aborted 28.056 55,71 0,07 % Total 41.856.775 83.111,36 100,00 % Query statistic: Total ø per hourø per min ø per sec 810.808.855 1.609.952,7126.832,55 447,21 Query type ø per hour % admin commands 328.082 651,44 0,04 % alter table 36 0,070,00 % analyze40,010,00 % backup table 0 0,000,00 % begin 00,000,00 % change db 107.083.472 212.626,35 13,93 % change master0 0,000,00 % check 310,060,00 % commit 00,000,00 % create db 00,000,00 % create function 0 0,000,00 % create index 0 0,000,00 % create table72 0,140,00 % delete 1.760.259 3.495,19 0,23 % delete multi 0 0,000,00 % drop db00,000,00 % drop function0 0,000,00 % drop index 0 0,000,00 % drop table 64 0,130,00 % flush 80,020,00 % grant 00,000,00 %
Re: repairing corrupt tables
In the last episode (Jan 26), Harriet Wheeler said: > I recently moved from Mac OS X Server 1.x (rhapsody) with MySQL 3.23.27 to > Mac OS X 10.1 (darwin) with MySQL 3.23.47. Due to an oversight the only db > backups after the move were in non-gzip'd tarballs that were ftp'd in ASCII > mode -- ugh. Lots of missing and corrupt files. Hmm. MacOS to MacOS ftps in ASCII mode should not corrupt data, assuming both ends use the Mac CR end-of-line character. Anyhow, if you are extremely lucky, you can sometimes prefectly recover files that were transferred in ASCII mode, but only if they went in the expanding direction (CR -> CR/LF or LF -> CR/LF). Simply do a binary replace of CR/LF back to the original EOL character. In fact, you can even use the 'replace' command included in mysql to do it :) Going in the other direction irrevocably loses information, so even if you do manage to 'repair' the tables, be assured that any integer fields will have corruption issues. -- Dan Nelson [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
repairing corrupt tables
I recently moved from Mac OS X Server 1.x (rhapsody) with MySQL 3.23.27 to Mac OS X 10.1 (darwin) with MySQL 3.23.47. Due to an oversight the only db backups after the move were in non-gzip'd tarballs that were ftp'd in ASCII mode -- ugh. Lots of missing and corrupt files. All tables are ISAM. Using the standard procedures (isamchk -r/-o; regenerating index files; regenerating description files), I've managed to rescue all but two of the tables. For one I have only the ISD/frm files, so I regenerated the index file by moving the data file, cranking up mysql, issuing `delete from [table]', replacing the data file, and trying isamchk again. No dice: isamchk -r/-o both end up with a "wrong bytesec" error and an empty data file. For the other table I have only the ISD/ISM files, which is no biggie since I have the original `create table' schema. But the best I've been able to do with this table is recover about 1750 (out of around 3500) records, only about 750 of which represent good data. Advice? I'd be happy to e-mail these files to anyone who wants to take a whack. /hw - 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: myisamchk/corrupt tables
At 04:38 -0500 2001/11/16, Jennifer Slis wrote: > when I type "myisamchk" my server claims "command not found". Check to see that myisamchk is in your path. Or go directly to the /usr/local/mysql/bin directory (or where ever it's installed) and type ./myisamchk [options] HTH /Rob -- Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] "Life's unfair - but root password helps!" - 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: myisamchk/corrupt tables
Jennifer Slis wrote: > > I am extremely new to mySQL (and the Linux environment all together). I have > been building/maintaining a mySQL/PHP site. Today, the site began giving me > errno 145 (cannot open file) errors. I found that meant I had at least one > corrupt table, so I went into mySQL, found I had two corrupt tables and ran > REPAIR TABLE. That fixed one of the tables, but the other one kept giving > the error msg text as "69 when writing to datafile" as a result of REPAIR > TABLE. So after much searching, I found a more extensive repair mechanism in > myisamchk. However, everything I have found assumes I have this utility > running. I am running mySQL 3.23.38, but when I type "myisamchk" my server > claims "command not found". So (a) is there a different way to repair my > table? (without having to wipe all data and rebuild the table -- it has a > ton of records) or (b) how do I get this myisamchk utility running on my > server so the command means something?? Also, I tried mysqld --myism-recover > but it printed out a few lines, then said "ready for connections" and just > sat there. I left it alone for a while thinking maybe it was just taking > forever, but it never finished until I hit Ctrl C. ANY help would be much > appreciated!!! > > Jenn > [EMAIL PROTECTED] > > _ Jennifer: What are the chances you're out of disk space? df The above command will tell you if you've used up all your disk space on your db partition. Van -- = Linux rocks!!! http://www.dedserius.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
myisamchk/corrupt tables
I am extremely new to mySQL (and the Linux environment all together). I have been building/maintaining a mySQL/PHP site. Today, the site began giving me errno 145 (cannot open file) errors. I found that meant I had at least one corrupt table, so I went into mySQL, found I had two corrupt tables and ran REPAIR TABLE. That fixed one of the tables, but the other one kept giving the error msg text as "69 when writing to datafile" as a result of REPAIR TABLE. So after much searching, I found a more extensive repair mechanism in myisamchk. However, everything I have found assumes I have this utility running. I am running mySQL 3.23.38, but when I type "myisamchk" my server claims "command not found". So (a) is there a different way to repair my table? (without having to wipe all data and rebuild the table -- it has a ton of records) or (b) how do I get this myisamchk utility running on my server so the command means something?? Also, I tried mysqld --myism-recover but it printed out a few lines, then said "ready for connections" and just sat there. I left it alone for a while thinking maybe it was just taking forever, but it never finished until I hit Ctrl C. ANY help would be much appreciated!!! Jenn [EMAIL PROTECTED] _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: Frequently corrupt tables
I realized that I was using an older version of libmysqlclient. So I recompiled and linked the msql-mysql-modules against mysql-3.23.41... Bill Adams wrote: > o If there is no call to 'flush tables', even a small data load will cause > myisamcheck to report "warning: 1 clients is using or hasn't closed the table > properly" when I know there is no client accessing it. In this case myisamcheck > does fix the problem. I still get this error. However, it does not seem like my tables are getting corrupted anymore. For now I am done looking for the source of this bug as the 'flush tables' takes care of them all. --Bill - 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: Frequently corrupt tables
Bill Adams wrote: > Spoiler: You may be right about the bad libs... [snip] > *** OMG *** > But haha I cannot believe this, I was just looking at the libraries linked by > mysqld with ldd and it is using the informix libpthread.so. Hmm, crap. *me > slaps head* Small Update: o If there is no call to 'flush tables', even a small data load will cause myisamcheck to report "warning: 1 clients is using or hasn't closed the table properly" when I know there is no client accessing it. In this case myisamcheck does fix the problem. o If I call 'flush tables' even at the program exit, I do not get the warning. o Using the statically linked 4.0 binary from mysql.com had no effect on the results. o Upgrading DBI to the latest version had no effect on the results. o Upgrading to the latest msql-mysql-moudles did not effect the results. I was playing around with the 'flush tables' and managed to quickly corrupt a table without any flush call. I saved a copy of the table if the mysql folks should want to take a look for some unknown reason. [bill@host ~/dev]$ /usr/local/mysql-4.0/bin/myisamchk -e ../bad-tables/pcm_test_site_200105.MYI Checking MyISAM file: ../bad-tables/pcm_test_site_200105.MYI Data records: 58923 Deleted blocks: 0 /usr/local/mysql-4.0/bin/myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references /usr/local/mysql-4.0/bin/myisamchk: error: Record-count is not ok; is 58328 Should be: 58923 /usr/local/mysql-4.0/bin/myisamchk: warning: Found595 deleted blocks Should be: 0 MyISAM-table '../bad-tables/pcm_test_site_200105.MYI' is corrupted Fix it using switch "-r" or "-o" But running myisamchk without the -e just gives the 'clients using' warning. Also, '-r' will repair the table in this case and adding the flush back in seems to prevent the error from happening. Well, it is almost beer o'clock. I will try this on other machines on Monday. --Bill - 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: Frequently corrupt tables
Spoiler: You may be right about the bad libs... Kyle Hayes wrote: > On Thursday 18 October 2001 12:31, Bill Adams wrote: > Hmm, 2.2 doesn't do SMP really well. However, its drawbacks are limited to > underuse of the CPUs rather than any kind of corruption or other issue. You > would get much better performance with 2.4, but 2.2 is probably a little more > stable. 2.4 is not an option for me because: o Right not I use Informix as my production database. Until they officially support 2.4 or I 'upgrade' to MySQL I am stuck in the 2.2.x series. o Until the VM crap is worked out, I am not installing the 2.5, er. 2.4 kernels on any production machines unless it comes with the distribution. > Is this a DAC960 or something similar? If so, make sure you have the > absolute latest drivers. We have some dual processor machines with those > controllers (or something closely related) and had to do many driver updates > before it stabilized. And, we're still not totally convinced. If this is a > big SCSI RAID card, I would definitely check the drivers and make sure that > there isn't something newer/more stable out there. I have a Mylex DAC1164P for the /, /home, etc. using RAID5. All of the MySQL tables are on an "Adaptec AIC-7899 Ultra 160/m SCSI host adapter" which is a dual channel UW controller. > > Statistics: > > > > (scsi0:0:0:0) > > Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31 > > Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2) > > Total transfers 36738885 (18761976 reads and 17976909 writes) > > Waiter! I'll have two of what that gentleman over there is having. :) > > > What filesystem are you running? > > > > ext2. At least that is what linux sees. The disks are actually hardware > > raid0 winchester flashdisks. > > Flash? I.e. these are solid state disks? If that is true, then maybe that > is part of the problem. Flash is different from "normal" disk. No, that is the product name. http://www.winsys.com/products/ Basically, it is a box with 12 drives in it and a dual channel scsi controller (in my model). As far as Linux is concerned, each box appears as two very large, very fast drives on two channels. You can partition in different ways and get them with one channel, etc.. > Can these disks correct for bad sectors? If so, the usual method to force > remapping of bad sectors is to use dd: AFAIK, the flash controller corrects for that. But then again I am running RAID0 and winchester systems does not officially support that level (they do 0+1, 5, others) because part of what they are selling besides blazingly fast raid boxes is data security and integrity. Obviously you do not get that with RAID0. For my application that is not an issue. I care only about speed and volume: my raw data is backed up elsewhere. But I digress > dd if=/dev/zero of=/dev/XXX bs=1M count=YYY > > Where XXX is the RAID device and YYY is the number of megabytes of storage. > > Please make a backup of your data first :-) > > On a "normal" disk, this causes a write to each sector on the whole drive. > That in turn causes the firmware on the drive to remap any bad sectors found > this way. If your disks support this, you might be unpleasantly surprized > how many problems go away after this. Most newer drives do this > automatically, but it can still trash your data. By doing the line above, > you force the issue before you have valid data on the disk. I in my case I just 'login' to the controller on the flashdisk to get statistics such as bad sectors and such. Not to sound too much like an advertisement for Winchester Systems but these people have been around for a long time and the controllers I have have been too and are well tested and used by many other companies/people with much more critical needs than I have. I also do not have problems with the Informix tables on the same disks using the same dataloader under the same conditions. And it happens on different enclosures/disks/etc.. > We've done 7M rows in one single input file (just a hair under the 2GB limit > for the older ext2 filesystem we have on that particular machine). No > problems at all. That was with MySQL 3.23.26 or something close to that. > We've done tests much larger than this that were either driver via Perl and > DBI, or from a flat file. Well, I am running an ancient version of DBI. I will upgrade to a more modern version of DBI and msql-mysql-modules; reload data; and report back. > > > Is the data getting mangled or the index? If myisamchk can fix the > > > problem, > > > > That is the funny thing, I had to do a mysqldump > file; mysql > the table. myisamchk would report the table was bad, I would try to repair > > with -o (and just about every other level). then myisamchk would report it > > was good (even with -e). When I continued to load the data, it would > > quickly become corrupted again. Even rebuilding all of the indexes would > > n
Re: Frequently corrupt tables
On Thu, 18 Oct 2001, Kyle Hayes wrote: > On Thursday 18 October 2001 09:45, Bill Adams wrote: > > Matthew Bloch wrote: > > > I'm running several MySQL installation (all version 3.23.37 under Linux) > > > under what I presume are some fairly harsh conditions, and wondered what > > > circumstances cause tables to be corrupted and need fixing with > > > myisamchk. This is happening once every few days and it's becoming a > > > pain. I have a multithreaded process which is constantly opening and > > > closing connections to the database and trying to increase its > > > concurrency until the load average reaches something comfortable like 15, > > > and the network connection is saturated. I've had to throttle it back to > > > stop it opening more than 32 simultaenous DB connections but otherwise it > > > works fine. Until I start getting errors from the table handler, that > > > is, and the whole thing grinds to a halt until I fix the table manually. > > > > > > Can anybody shed some light on this? I can't believe I'm putting it > > > under more load than something like Slashdot would, and they don't > > > (appear to) have half the troubles I've had. > > > > I found yesterday (at the advice of this list) that adding an occasional > > call to "FLUSH TABLES" fixed my corruption problems. I would do that right > > before the disconnect or program exit. > > What kernel are you using? Some of the 2.4 series have... odd... behavior > with regards to caching. Happens evenly between three machines: one running 2.2.16-22.0RS (from Redhat 7.0), the others running 2.4.3-12.2RS & 2.4.2-2 from Redhat 7.1 so I'm not convinced it's kernel-specific. We're running whatever hardware Rackspace provided us with, not sure, but I think they're all IDE and definitely all running ext2. The only thing that's common is the MySQL version apparently. re: Steve's suggestion, we don't shut the machines down, or at least the corruptions haven't co-incided with the odd reboot we've done. Nor have there been power failures we've been aware of; Rackspace are quite good at telling us about that kind of thing. A few people suggested FLUSH TABLES, but it sounds like a stop-gap, and I didn't realise converting to InnoDB was as simple as stopping, backing up, starting and issuing ALTER TABLE Blah TYPE=INNODB; so I'll probably end up doing that. Nor did I realise that the MySQL version I had on the boxes had InnoDB compiled in, so that sounds like the best solution so far. If it's good enough for Slashdot... In general though, is database corruption really such an occupational hazard to watch for? I was floored that any database might be even occasionally expected to corrupt its data, particularly one that's used so widely as MySQL, but I suppose even with someone like Redhat taking care of compilation, the random combination of kernel & database versions might cause some friction. Thanks for the help anyhow, guys, will be migrating to InnoDB ASAP and see if that sorts it out. -- Matthew > http://www.soup-kitchen.net/ > ICQ 19482073 - 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: Frequently corrupt tables
Hi Matthew, We had a similar problem that caused us to need to run myisamchk much more than we wanted to. It turned out that MySQL was not being shutdown when Unix was. Symptom: The MySQL server error log did NOT show normal shutdown messages. Cause 1: The normal method of shutting down MySQL uses kill to send a signal to the process. That was NOT working on SCO Open Server 5. Cause 2: We did not properly understand where to put the Unix shutdown scripts. Solution: Created a correct script: /etc/rc0.d/K95mysql AND changed it to use "mysqladmin ... shutdown" rather than kill. Result: MySQL now shuts down nicely in all cases except server power cuts. Normal shutdown messages on its log. More robust data. Stephen > -Original Message- > From: Matthew Bloch [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 18, 2001 3:34 AM > To: [EMAIL PROTECTED] > Cc: Peter Taphouse; Alec O'Donnell > Subject: Frequently corrupt tables > > > Hello all; > > I'm running several MySQL installation (all version 3.23.37 under Linux) > under what I presume are some fairly harsh conditions, and wondered what > circumstances cause tables to be corrupted and need fixing with myisamchk. > This is happening once every few days and it's becoming a pain. I have a > multithreaded process which is constantly opening and closing connections > to the database and trying to increase its concurrency until the load > average reaches something comfortable like 15, and the network connection > is saturated. I've had to throttle it back to stop it opening more than > 32 simultaenous DB connections but otherwise it works fine. Until I start > getting errors from the table handler, that is, and the whole thing grinds > to a halt until I fix the table manually. > > Can anybody shed some light on this? I can't believe I'm putting it under > more load than something like Slashdot would, and they don't (appear to) > have half the troubles I've had. > > cheers, > > -- > Matthew > http://www.soup-kitchen.net/ - 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: Frequently corrupt tables
On Thursday 18 October 2001 12:31, Bill Adams wrote: > Kyle Hayes wrote: > > > I found yesterday (at the advice of this list) that adding an > > > occasional call to "FLUSH TABLES" fixed my corruption problems. I > > > would do that right before the disconnect or program exit. > > > > What kernel are you using? Some of the 2.4 series have... odd... > > behavior with regards to caching. > > Linux host 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown > 2GB Memory, 4 CPUs. > (It happened on other systems with different kernel versions too.) Hmm, 2.2 doesn't do SMP really well. However, its drawbacks are limited to underuse of the CPUs rather than any kind of corruption or other issue. You would get much better performance with 2.4, but 2.2 is probably a little more stable. > > Are you using SCSI or IDE. We've run many tests with both and not had > > any corruption problems unless we did something whacked like pull the > > power for the machine while it was running the test. > > SCSI. (Had problem with different controllers on different systems) > > Three dual channel controllers, all the same: > > [bill@host ~/dev]$ cat /proc/scsi/aic7xxx/0 > Adaptec AIC7xxx driver version: 5.1.33/3.2.4 > Compile Options: > TCQ Enabled By Default : Disabled > AIC7XXX_PROC_STATS : Disabled > AIC7XXX_RESET_DELAY: 5 > > Adapter Configuration: >SCSI Adapter: Adaptec AIC-7899 Ultra 160/m SCSI host adapter >Ultra-160/m LVD/SE Wide Controller Channel A at Is this a DAC960 or something similar? If so, make sure you have the absolute latest drivers. We have some dual processor machines with those controllers (or something closely related) and had to do many driver updates before it stabilized. And, we're still not totally convinced. If this is a big SCSI RAID card, I would definitely check the drivers and make sure that there isn't something newer/more stable out there. > Statistics: > > (scsi0:0:0:0) > Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31 > Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2) > Total transfers 36738885 (18761976 reads and 17976909 writes) Waiter! I'll have two of what that gentleman over there is having. > > What filesystem are you running? > > ext2. At least that is what linux sees. The disks are actually hardware > raid0 winchester flashdisks. Flash? I.e. these are solid state disks? If that is true, then maybe that is part of the problem. Flash is different from "normal" disk. Can these disks correct for bad sectors? If so, the usual method to force remapping of bad sectors is to use dd: dd if=/dev/zero of=/dev/XXX bs=1M count=YYY Where XXX is the RAID device and YYY is the number of megabytes of storage. Please make a backup of your data first :-) On a "normal" disk, this causes a write to each sector on the whole drive. That in turn causes the firmware on the drive to remap any bad sectors found this way. If your disks support this, you might be unpleasantly surprized how many problems go away after this. Most newer drives do this automatically, but it can still trash your data. By doing the line above, you force the issue before you have valid data on the disk. > > Just running FLUSH TABLES sounds like it is only going to make the > > problem less common, not fix it. Something is corrupting your > > indexes/data. > > I loaded three big tables last night with no problems (after adding the > occasional $dbh->do( "FLUSH TABLES" ). Before it would happen at least > once when doing a large (re)load of data. We've done 7M rows in one single input file (just a hair under the 2GB limit for the older ext2 filesystem we have on that particular machine). No problems at all. That was with MySQL 3.23.26 or something close to that. We've done tests much larger than this that were either driver via Perl and DBI, or from a flat file. > > Is the data getting mangled or the index? If myisamchk can fix the > > problem, > > That is the funny thing, I had to do a mysqldump > file; mysql the table. myisamchk would report the table was bad, I would try to repair > with -o (and just about every other level). then myisamchk would report it > was good (even with -e). When I continued to load the data, it would > quickly become corrupted again. Even rebuilding all of the indexes would > not fix it. Running the mysqldump, mysql fixed it much better. There was a bug in myisamchk for a while that would cause data loss in certain circumstances unless you used the -v flag with -r. This should have been fixed a while ago (over a year?). If MySQL handled the input file resulting from the original dump then it was probably happy with the data itself. You might want to dump out a flat file (tab delimited data) with mysqldump. Then, go into the MySQL command line and run LOAD DATA INFILE ... to load the data in. This will tell you how many warnings y
Re: Frequently corrupt tables
Kyle Hayes wrote:
> > I found yesterday (at the advice of this list) that adding an occasional
> > call to "FLUSH TABLES" fixed my corruption problems. I would do that right
> > before the disconnect or program exit.
>
> What kernel are you using? Some of the 2.4 series have... odd... behavior
> with regards to caching.
Linux host 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown
2GB Memory, 4 CPUs.
(It happened on other systems with different kernel versions too.)
> Are you using SCSI or IDE. We've run many tests with both and not had any
> corruption problems unless we did something whacked like pull the power for
> the machine while it was running the test.
SCSI. (Had problem with different controllers on different systems)
Three dual channel controllers, all the same:
[bill@host ~/dev]$ cat /proc/scsi/aic7xxx/0
Adaptec AIC7xxx driver version: 5.1.33/3.2.4
Compile Options:
TCQ Enabled By Default : Disabled
AIC7XXX_PROC_STATS : Disabled
AIC7XXX_RESET_DELAY: 5
Adapter Configuration:
SCSI Adapter: Adaptec AIC-7899 Ultra 160/m SCSI host adapter
Ultra-160/m LVD/SE Wide Controller Channel A at PCI
2/6/0
PCI MMAPed I/O Base: 0xf9dfa000
Adapter SEEPROM Config: SEEPROM found and used.
Adaptec SCSI BIOS: Disabled
IRQ: 21
SCBs: Active 0, Max Active 1,
Allocated 15, HW 32, Page 255
Interrupts: 36738969
BIOS Control Word: 0xb8f8
Adapter Control Word: 0x7c5d
Extended Translation: Enabled
Disconnect Enable Flags: 0x
Ultra Enable Flags: 0x
Tag Queue Enable Flags: 0x
Ordered Queue Tag Flags: 0x
Default Tag Queue Depth: 8
Tagged Queue By Device array for aic7xxx host instance 0:
{255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255}
Actual queue depth per device for aic7xxx host instance 0:
{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}
Statistics:
(scsi0:0:0:0)
Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31
Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2)
Total transfers 36738885 (18761976 reads and 17976909 writes)
> What filesystem are you running?
ext2. At least that is what linux sees. The disks are actually hardware raid0
winchester flashdisks.
> Just running FLUSH TABLES sounds like it is only going to make the problem
> less common, not fix it. Something is corrupting your indexes/data.
I loaded three big tables last night with no problems (after adding the
occasional $dbh->do( "FLUSH TABLES" ). Before it would happen at least once
when doing a large (re)load of data.
> Is the data getting mangled or the index? If myisamchk can fix the problem,
That is the funny thing, I had to do a mysqldump > file; mysql
> it is likely that the index is the problem. MySQL will cache the index in
> memory, but not the data. Thus, if you see data mangling problems and
> possibly index problems, I would look at the kernel, disk etc. If you are
> only see index problems, but the data looks OK, then the version of MySQL
> might be a problem or maybe you have a bad build. MySQL builds more cleanly
It happened with 3.23.41.
> than most OSS projects, but it is a big complex beastie and can build
> incorrectly without obvious errors sometimes in our experience. Bad library
> versions can also be a factor.
I did build/run this on a RH6.2 system.
> We've run tests with 1000 hits per second on a database on a cheasy IDE drive
> without a problem. We've run those tests for hours at a time with no
> problems. SCSI definitely works better than IDE, but the newer IDE drives
> aren't that bad anymore. They still use a lot of CPU.
It is not the selects that cause the problems, it is lots of inserts. Again, it
only seems to happen on large loads. I have three main tables and a large load
means:
mysql> select count(*) from pcm_test_header_200109;
+--+
| count(*) |
+--+
| 5844 |
+--+
1 row in set (0.07 sec)
mysql> select count(*) from pcm_test_summary_200109;
+--+
| count(*) |
+--+
| 840413 |
+--+
1 row in set (0.04 sec)
mysql> select count(*) from pcm_test_site_200109;
+--+
| count(*) |
+--+
| 7248366 |
+--+
1 row in set (0.02 sec)
mysql>
Any of the three tables can have problems but it is usually the site table.
> If your drives to write caching, that can be a problem if you have a power
> drop. Most IDE drives (all?) will cache writes to allow the disk firmware to
This is not a power or crash problem. It happens WHILE the loader is running.
It could be a DBI/DBD bug. I [try to] insert all of the above records with a
single database handle (connection).
b.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To reque
Re: Frequently corrupt tables
Hi! At 03:03 PM 10/18/01 -0400, you wrote: >Being an *NIX developer for longer than I care to recall and using Linux >since .99r, I would disagree about a buggy io subsystem. There may be some But some disk drivers may be buggy. That would explain why some machines run totally stable while others show file system corruption or database page checksum errors. >issues with marshalling and /or threads (usually a coding/synchronization >issue not a thread issue), but I would suspect it is relating to db caching. >I would try committing the inserts to the drive by using the --flush >parameter upon invocation of mysqld. I don't know what type of performance >hit this may cause you, but I would suspect that all of your corruption >issues would disappear. > >Respectfully, > >Pat... > >BTW the only OS's that I have experience table corruption has been with MS >(NT and 98) due to OS freezes and blue screens. --flush cured those. An operating system crash should really cause table corruption in MyISAM because its index cache may be only partially flushed to disk. But it does not show that the i/o system of the OS is buggy, because the OS may crash for some other reason. But the file system corruption and checksum errors my friend and I have observed cannot be due to OS crashes because Linux did not crash in those cases. But please users tell your experiences from Linux and other operating systems. It may help to give advice to other users about what OS versions are the most stable. Regards, Heikki >- Original Message - >From: "Heikki Tuuri" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Thursday, October 18, 2001 12:53 PM >Subject: RE: Frequently corrupt tables > > >> Hi! >> >> >Well, for one, I believe that Slashdot uses InnoDB tables, which tend to >> handle >> >a little better under very high load. >> >Steve Meyers >> > -Original Message- >> >> From: Matthew Bloch [mailto:[EMAIL PROTECTED]] >> >> Sent: Thursday, October 18, 2001 3:34 AM> To: [EMAIL PROTECTED] >> >> Cc: Peter Taphouse; Alec O'Donnell >> > Subject: Frequently corrupt tables >> > > >> >> Hello all; >> > >> >> I'm running several MySQL installation (all version 3.23.37 under >Linux) >> >> under what I presume are some fairly harsh conditions, and wondered >what >> >> circumstances cause tables to be corrupted and need fixing with >myisamchk. >> >> This is happening once every few days and it's becoming a pain. I have >a >> >> multithreaded process which is constantly opening and closing >connections >> >> to the database and trying to increase its concurrency until the load >> >> average reaches something comfortable like 15, and the network >connection >> >> is saturated. I've had to throttle it back to stop it opening more >than >> >> 32 simultaenous DB connections but otherwise it works fine. Until I >start >> >> getting errors from the table handler, that is, and the whole thing >grinds >> >> to a halt until I fix the table manually. >> > >> >> Can anybody shed some light on this? I can't believe I'm putting it >under >> >> more load than something like Slashdot would, and they don't (appear >to) >> >> have half the troubles I've had. >> > > cheers, >> > > -- >> >> Matthew >> >http://www.soup-kitchen.net/ >> > >> > ICQ 19482073 >> >> There seem to be i/o bugs in Linux. What is >> your Linux kernel version? 2.2.19 is believed >> to be the most stable. >> >> My current hypothesis is that many disk drivers >> in Linux are buggy. My development platform >> 2.4.4 has been totally stable while another >> computer with 2.2.14 expressed file read errors >> and crashes. >> >> High load should not cause table corruption, >> but it may make some bugs surface in the OS. >> Actually, has anybody seen table corruption >> on Win NT/2000 or Solaris? >> >> Regards, >> >> Heikki >> http://www.innodb.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 >> > > - 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: Frequently corrupt tables
On Thursday 18 October 2001 09:45, Bill Adams wrote: > Matthew Bloch wrote: > > I'm running several MySQL installation (all version 3.23.37 under Linux) > > under what I presume are some fairly harsh conditions, and wondered what > > circumstances cause tables to be corrupted and need fixing with > > myisamchk. This is happening once every few days and it's becoming a > > pain. I have a multithreaded process which is constantly opening and > > closing connections to the database and trying to increase its > > concurrency until the load average reaches something comfortable like 15, > > and the network connection is saturated. I've had to throttle it back to > > stop it opening more than 32 simultaenous DB connections but otherwise it > > works fine. Until I start getting errors from the table handler, that > > is, and the whole thing grinds to a halt until I fix the table manually. > > > > Can anybody shed some light on this? I can't believe I'm putting it > > under more load than something like Slashdot would, and they don't > > (appear to) have half the troubles I've had. > > I found yesterday (at the advice of this list) that adding an occasional > call to "FLUSH TABLES" fixed my corruption problems. I would do that right > before the disconnect or program exit. What kernel are you using? Some of the 2.4 series have... odd... behavior with regards to caching. Are you using SCSI or IDE. We've run many tests with both and not had any corruption problems unless we did something whacked like pull the power for the machine while it was running the test. What filesystem are you running? Just running FLUSH TABLES sounds like it is only going to make the problem less common, not fix it. Something is corrupting your indexes/data. Is the data getting mangled or the index? If myisamchk can fix the problem, it is likely that the index is the problem. MySQL will cache the index in memory, but not the data. Thus, if you see data mangling problems and possibly index problems, I would look at the kernel, disk etc. If you are only see index problems, but the data looks OK, then the version of MySQL might be a problem or maybe you have a bad build. MySQL builds more cleanly than most OSS projects, but it is a big complex beastie and can build incorrectly without obvious errors sometimes in our experience. Bad library versions can also be a factor. We've run tests with 1000 hits per second on a database on a cheasy IDE drive without a problem. We've run those tests for hours at a time with no problems. SCSI definitely works better than IDE, but the newer IDE drives aren't that bad anymore. They still use a lot of CPU. If your drives to write caching, that can be a problem if you have a power drop. Most IDE drives (all?) will cache writes to allow the disk firmware to reorder the writes to get better speed. This means that your carefully flushed data will be written in an order _DIFFERENT_ from what you and the OS thought it was written in. This can completely screw up filesystems and definitely could have some "interesting" effects on your database. Best, Kyle -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MicroTelco Services saves money on every Fax: - Fax to email (FREE) - Fax to PSTN based Fax (Up to 95% Savings) - Fax Broadcasting: Send 100s of faxes to fax machines and email addresses in the time it takes to send just one! === So send a fax today and let us know what you think! For more info. visit: www.internetfaxjack.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
Re: Frequently corrupt tables
Being an *NIX developer for longer than I care to recall and using Linux since .99r, I would disagree about a buggy io subsystem. There may be some issues with marshalling and /or threads (usually a coding/synchronization issue not a thread issue), but I would suspect it is relating to db caching. I would try committing the inserts to the drive by using the --flush parameter upon invocation of mysqld. I don't know what type of performance hit this may cause you, but I would suspect that all of your corruption issues would disappear. Respectfully, Pat... BTW the only OS's that I have experience table corruption has been with MS (NT and 98) due to OS freezes and blue screens. --flush cured those. - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 18, 2001 12:53 PM Subject: RE: Frequently corrupt tables > Hi! > > >Well, for one, I believe that Slashdot uses InnoDB tables, which tend to > handle > >a little better under very high load. > >Steve Meyers > > -Original Message- > >> From: Matthew Bloch [mailto:[EMAIL PROTECTED]] > >> Sent: Thursday, October 18, 2001 3:34 AM> To: [EMAIL PROTECTED] > >> Cc: Peter Taphouse; Alec O'Donnell > > Subject: Frequently corrupt tables > > > > >> Hello all; > > > >> I'm running several MySQL installation (all version 3.23.37 under Linux) > >> under what I presume are some fairly harsh conditions, and wondered what > >> circumstances cause tables to be corrupted and need fixing with myisamchk. > >> This is happening once every few days and it's becoming a pain. I have a > >> multithreaded process which is constantly opening and closing connections > >> to the database and trying to increase its concurrency until the load > >> average reaches something comfortable like 15, and the network connection > >> is saturated. I've had to throttle it back to stop it opening more than > >> 32 simultaenous DB connections but otherwise it works fine. Until I start > >> getting errors from the table handler, that is, and the whole thing grinds > >> to a halt until I fix the table manually. > > > >> Can anybody shed some light on this? I can't believe I'm putting it under > >> more load than something like Slashdot would, and they don't (appear to) > >> have half the troubles I've had. > > > cheers, > > > -- > >> Matthew > >http://www.soup-kitchen.net/ > > > > ICQ 19482073 > > There seem to be i/o bugs in Linux. What is > your Linux kernel version? 2.2.19 is believed > to be the most stable. > > My current hypothesis is that many disk drivers > in Linux are buggy. My development platform > 2.4.4 has been totally stable while another > computer with 2.2.14 expressed file read errors > and crashes. > > High load should not cause table corruption, > but it may make some bugs surface in the OS. > Actually, has anybody seen table corruption > on Win NT/2000 or Solaris? > > Regards, > > Heikki > http://www.innodb.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 > - 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: Frequently corrupt tables
Hi! >Well, for one, I believe that Slashdot uses InnoDB tables, which tend to handle >a little better under very high load. >Steve Meyers > -Original Message- >> From: Matthew Bloch [mailto:[EMAIL PROTECTED]] >> Sent: Thursday, October 18, 2001 3:34 AM> To: [EMAIL PROTECTED] >> Cc: Peter Taphouse; Alec O'Donnell > Subject: Frequently corrupt tables > > >> Hello all; > >> I'm running several MySQL installation (all version 3.23.37 under Linux) >> under what I presume are some fairly harsh conditions, and wondered what >> circumstances cause tables to be corrupted and need fixing with myisamchk. >> This is happening once every few days and it's becoming a pain. I have a >> multithreaded process which is constantly opening and closing connections >> to the database and trying to increase its concurrency until the load >> average reaches something comfortable like 15, and the network connection >> is saturated. I've had to throttle it back to stop it opening more than >> 32 simultaenous DB connections but otherwise it works fine. Until I start >> getting errors from the table handler, that is, and the whole thing grinds >> to a halt until I fix the table manually. > >> Can anybody shed some light on this? I can't believe I'm putting it under >> more load than something like Slashdot would, and they don't (appear to) >> have half the troubles I've had. > > cheers, > > -- >> Matthew >http://www.soup-kitchen.net/ > > ICQ 19482073 There seem to be i/o bugs in Linux. What is your Linux kernel version? 2.2.19 is believed to be the most stable. My current hypothesis is that many disk drivers in Linux are buggy. My development platform 2.4.4 has been totally stable while another computer with 2.2.14 expressed file read errors and crashes. High load should not cause table corruption, but it may make some bugs surface in the OS. Actually, has anybody seen table corruption on Win NT/2000 or Solaris? Regards, Heikki http://www.innodb.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
Re: Frequently corrupt tables
Matthew Bloch wrote: > I'm running several MySQL installation (all version 3.23.37 under Linux) > under what I presume are some fairly harsh conditions, and wondered what > circumstances cause tables to be corrupted and need fixing with myisamchk. > This is happening once every few days and it's becoming a pain. I have a > multithreaded process which is constantly opening and closing connections > to the database and trying to increase its concurrency until the load > average reaches something comfortable like 15, and the network connection > is saturated. I've had to throttle it back to stop it opening more than > 32 simultaenous DB connections but otherwise it works fine. Until I start > getting errors from the table handler, that is, and the whole thing grinds > to a halt until I fix the table manually. > > Can anybody shed some light on this? I can't believe I'm putting it under > more load than something like Slashdot would, and they don't (appear to) > have half the troubles I've had. I found yesterday (at the advice of this list) that adding an occasional call to "FLUSH TABLES" fixed my corruption problems. I would do that right before the disconnect or program exit. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Frequently corrupt tables
Well, for one, I believe that Slashdot uses InnoDB tables, which tend to handle a little better under very high load. Steve Meyers > -Original Message- > From: Matthew Bloch [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 18, 2001 3:34 AM > To: [EMAIL PROTECTED] > Cc: Peter Taphouse; Alec O'Donnell > Subject: Frequently corrupt tables > > > Hello all; > > I'm running several MySQL installation (all version 3.23.37 under Linux) > under what I presume are some fairly harsh conditions, and wondered what > circumstances cause tables to be corrupted and need fixing with myisamchk. > This is happening once every few days and it's becoming a pain. I have a > multithreaded process which is constantly opening and closing connections > to the database and trying to increase its concurrency until the load > average reaches something comfortable like 15, and the network connection > is saturated. I've had to throttle it back to stop it opening more than > 32 simultaenous DB connections but otherwise it works fine. Until I start > getting errors from the table handler, that is, and the whole thing grinds > to a halt until I fix the table manually. > > Can anybody shed some light on this? I can't believe I'm putting it under > more load than something like Slashdot would, and they don't (appear to) > have half the troubles I've had. > > cheers, > > -- > Matthew > http://www.soup-kitchen.net/ > > ICQ 19482073 > > > - > 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
Frequently corrupt tables
Hello all; I'm running several MySQL installation (all version 3.23.37 under Linux) under what I presume are some fairly harsh conditions, and wondered what circumstances cause tables to be corrupted and need fixing with myisamchk. This is happening once every few days and it's becoming a pain. I have a multithreaded process which is constantly opening and closing connections to the database and trying to increase its concurrency until the load average reaches something comfortable like 15, and the network connection is saturated. I've had to throttle it back to stop it opening more than 32 simultaenous DB connections but otherwise it works fine. Until I start getting errors from the table handler, that is, and the whole thing grinds to a halt until I fix the table manually. Can anybody shed some light on this? I can't believe I'm putting it under more load than something like Slashdot would, and they don't (appear to) have half the troubles I've had. cheers, -- Matthew > http://www.soup-kitchen.net/ > ICQ 19482073 - 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: Corrupt Tables??
Michael Blood wrote: > > Hello everyone. > > I am running a 2.2 Debian OS with 3.23.34a-log.. > I had an entire database which was 2GB installed on a 2GB partition. > Stupidly the database is not backed up and when I attempt to access the > database an error is returned when accessing certain tables > > errno: 145 > > Saying that it can not open the table. > > I have looked up some infomration about repairing databases on the mysql > site and found isamchk. however this only seems to work when you have *.ISM > files. All of my files are .MYD .MYI and .frm. > > Does any one have any suggestions on how I can repair these tables? > > Michael Blood > --:-- > Hi, isamchk is for the 'old' (ISAM) table format. Your table is in MyISAM format (this format appeared in 3.23); you should use myisamchk. Regards -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Corrupt Tables??
Hello everyone. I am running a 2.2 Debian OS with 3.23.34a-log.. I had an entire database which was 2GB installed on a 2GB partition. Stupidly the database is not backed up and when I attempt to access the database an error is returned when accessing certain tables errno: 145 Saying that it can not open the table. I have looked up some infomration about repairing databases on the mysql site and found isamchk. however this only seems to work when you have *.ISM files. All of my files are .MYD .MYI and .frm. Does any one have any suggestions on how I can repair these tables? Michael Blood --:-- - 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: Corrupt tables after long queries (fwd)
Peter Skipworth writes:
> Hi all,
>
> Has anyone experienced anything like the following ?
>
> I have several queries which, admittedly, are quite complex and operate on
> several million rows of indexed data. The tables are naturally locked
> while the query occurs, which is fine, but what is *not* fine is that
> occasionally, completely unrelated tables end up needing a repair after
> the long query has completed.
>
> The only pattern I can see is that towards the end of the query, I see a
> hell of a lot of "Opening table" & "Closing table" status messages in the
> processlist - as soon as I see this, I can assume that the mentioned
> tables will have become corrupted - see a sample processlist below.
> Any ideas ?
>
> Cheers,
>
> P
>
>
>
> 164804 | hot | localhost | hot | Query | 6| closing tables |
> select * from tbl_hotclients limit 0
> |
> | 164805 | hot | localhost | hot | Query | 6| closing tables |
> select * from tbl_hotclients limit 0
> |
> | 164808 | hot | localhost | hot | Query | 14 | closing tables |
> select job_id from tbl_job_events where session='98760270421222662058432'
> and event_type='view' and |
> | 164809 | hot | localhost | hot | Query | 14 | closing tables |
> select tbl_jobs.refresh_date, tbl_jobs.job_id, tbl_jobs.title,
> tbl_jobs.region_id, tbl_jobs.hl_regio |
> | 164811 | hot | localhost | hot | Query | 5| Opening tables |
> select * from tbl_homepage_crazyjobs limit 0
> |
> | 164823 | hot | localhost | hot | Query | 12 | closing tables |
> select * from tbl_hours where job_hours_id = 7
> |
> | 164824 | hot | localhost | hot | Query | 6| closing tables |
> select * from tbl_banner_server limit 0
> |
> | 164825 | hot | localhost | hot | Query | 54 | Locked |
> insert into tbl_webevents_temp
> (processtime,event_time,cookie,parent,referer,duration,newuser,a,job_ |
> | 164832 | hot | localhost | hot | Query | 10 | closing tables |
> select * from tbl_applications limit 0
> |
> | 164833 | hot | localhost | hot | Query | 5| Opening tables |
> select * from tbl_applications limit 0
> |
> | 164834 | hot | localhost | hot | Query | 6| closing tables |
> select * from tbl_applications limit 0
> |
> | 164841 | hot | localhost | hot | Query | 10 | closing tables |
> select * from tbl_application_emails limit 0
> |
> | 164842 | hot | localhost | hot | Query | 5| Opening table |
> insert into tbl_job_events (job_id, session, event_type, user_type,
> recorded, event_time, uni_id) va |
>
>
> ---
> Peter Skipworth Perl Developer/Unix Systems Administrator
> [EMAIL PROTECTED]
> +44 795 055 0029
> ---
> 4c 61 20 6c 6f 79 52 75 62 20 73 41 20 65 52 61
> 20 45 65 62 4f 4c 47 6e 74 20 20 4f 53 75 00 0a
>
Hi!
What we need is a repeatable test case, i.e. a set of tables and
script that will always lead to table corruption.
If you can come up with such a test case, forward it to
[EMAIL PROTECTED]
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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
Corrupt tables after long queries (fwd)
Hi all, Has anyone experienced anything like the following ? I have several queries which, admittedly, are quite complex and operate on several million rows of indexed data. The tables are naturally locked while the query occurs, which is fine, but what is *not* fine is that occasionally, completely unrelated tables end up needing a repair after the long query has completed. The only pattern I can see is that towards the end of the query, I see a hell of a lot of "Opening table" & "Closing table" status messages in the processlist - as soon as I see this, I can assume that the mentioned tables will have become corrupted - see a sample processlist below. Any ideas ? Cheers, P 164804 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_hotclients limit 0 | | 164805 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_hotclients limit 0 | | 164808 | hot | localhost | hot | Query | 14 | closing tables | select job_id from tbl_job_events where session='98760270421222662058432' and event_type='view' and | | 164809 | hot | localhost | hot | Query | 14 | closing tables | select tbl_jobs.refresh_date, tbl_jobs.job_id, tbl_jobs.title, tbl_jobs.region_id, tbl_jobs.hl_regio | | 164811 | hot | localhost | hot | Query | 5| Opening tables | select * from tbl_homepage_crazyjobs limit 0 | | 164823 | hot | localhost | hot | Query | 12 | closing tables | select * from tbl_hours where job_hours_id = 7 | | 164824 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_banner_server limit 0 | | 164825 | hot | localhost | hot | Query | 54 | Locked | insert into tbl_webevents_temp (processtime,event_time,cookie,parent,referer,duration,newuser,a,job_ | | 164832 | hot | localhost | hot | Query | 10 | closing tables | select * from tbl_applications limit 0 | | 164833 | hot | localhost | hot | Query | 5| Opening tables | select * from tbl_applications limit 0 | | 164834 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_applications limit 0 | | 164841 | hot | localhost | hot | Query | 10 | closing tables | select * from tbl_application_emails limit 0 | | 164842 | hot | localhost | hot | Query | 5| Opening table | insert into tbl_job_events (job_id, session, event_type, user_type, recorded, event_time, uni_id) va | --- Peter Skipworth Perl Developer/Unix Systems Administrator [EMAIL PROTECTED] +44 795 055 0029 --- 4c 61 20 6c 6f 79 52 75 62 20 73 41 20 65 52 61 20 45 65 62 4f 4c 47 6e 74 20 20 4f 53 75 00 0a - 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
Corrupt tables after long queries
Hi all, Has anyone experienced anything like the following ? I have several queries which, admittedly, are quite complex and operate on several million rows of indexed data. The tables are naturally locked while the query occurs, which is fine, but what is *not* fine is that occasionally, completely unrelated tables end up needing a repair after thw long query has completed. The only pattern I can see is that towards the end of the query, I see a hell of a lot of "Opening table" & "Closing table" status messages in the processlist - as soon as I see this, I can assume that the mentioned tables will have become corrupted - see a sample processlist below. Any ideas ? Cheers, P 164804 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_hotclients limit 0 | | 164805 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_hotclients limit 0 | | 164808 | hot | localhost | hot | Query | 14 | closing tables | select job_id from tbl_job_events where session='98760270421222662058432' and event_type='view' and | | 164809 | hot | localhost | hot | Query | 14 | closing tables | select tbl_jobs.refresh_date, tbl_jobs.job_id, tbl_jobs.title, tbl_jobs.region_id, tbl_jobs.hl_regio | | 164811 | hot | localhost | hot | Query | 5| Opening tables | select * from tbl_homepage_crazyjobs limit 0 | | 164823 | hot | localhost | hot | Query | 12 | closing tables | select * from tbl_hours where job_hours_id = 7 | | 164824 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_banner_server limit 0 | | 164825 | hot | localhost | hot | Query | 54 | Locked | insert into tbl_webevents_temp (processtime,event_time,cookie,parent,referer,duration,newuser,a,job_ | | 164832 | hot | localhost | hot | Query | 10 | closing tables | select * from tbl_applications limit 0 | | 164833 | hot | localhost | hot | Query | 5| Opening tables | select * from tbl_applications limit 0 | | 164834 | hot | localhost | hot | Query | 6| closing tables | select * from tbl_applications limit 0 | | 164841 | hot | localhost | hot | Query | 10 | closing tables | select * from tbl_application_emails limit 0 | | 164842 | hot | localhost | hot | Query | 5| Opening table | insert into tbl_job_events (job_id, session, event_type, user_type, recorded, event_time, uni_id) va | --- Peter Skipworth Perl Developer/Unix Systems Administrator [EMAIL PROTECTED] +44 795 055 0029 --- 4c 61 20 6c 6f 79 52 75 62 20 73 41 20 65 52 61 20 45 65 62 4f 4c 47 6e 74 20 20 4f 53 75 00 0a - 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
