How do I drop a table, and recreate it?

[Mr.Yuck]

OK, I know opencaching.us is the old OCPL code, but this should be a pretty easy MySQL question for some of you out there. Last week, seemingly randomly, 13 GB+ error logs were being written to the OCUS server, filling up our disk a few times. We have FINALLY patched this. Our database was corrupted when the disk got full, and the server crashed a couple of times. 

We get 100 or so OKAPI error emails per day. Very early on, when we thought we patched the error log issue (but we had not), Wrygiel told me I should drop the table, and recreate it based on  http://opencaching.pl/okapi/devel/dbstruct  When I told him I didn't know how to do that, he said "that's OK, I just did it for you". It worked fine, until the large error logs came back and filled the disk.

So I know I need to do this, but how do I do it? Wrygiel is offline for several days, in his words, and I haven't heard from SP2ONG. I would like our admins to not receive 300 OKAPI error emails over the weekend. 

[bohrsty]

[quote="Mr.Yuck"]
OK, I know opencaching.us is the old OCPL code, but this should be a pretty easy MySQL question for some of you out there. Last week, seemingly randomly, 13 GB+ error logs were being written to the OCUS server, filling up our disk a few times. We have FINALLY patched this. Our database was corrupted when the disk got full, and the server crashed a couple of times. 

We get 100 or so OKAPI error emails per day. Very early on, when we thought we patched the error log issue (but we had not), Wrygiel told me I should drop the table, and recreate it based on  http://opencaching.pl/okapi/devel/dbstruct  When I told him I didn't know how to do that, he said "that's OK, I just did it for you". It worked fine, until the large error logs came back and filled the disk.

So I know I need to do this, but how do I do it? Wrygiel is offline for several days, in his words, and I haven't heard from SP2ONG. I would like our admins to not receive 300 OKAPI error emails over the weekend. 
[/quote]

have you tried to repair the corrupted table(s)?

assuming your database-tables are myiasam...
if you logon on the server-console, cd into the database-folder (usually /var/lib/mysql/<database-name>) check the MYI-file of the corrupted table with "myisamchk" i.e. "myisamchk <table-name>.MYI", if that reports error you can try to repair it with the -r option (myisamchk -r <table-name>.MYI)...

check more than one time after repairing... and/or shutdown the mysql-server before, start after repair and check again... and check the /var/log/my.log (or similar), there you maby find logs about which tables are corrupt...

i dont know exactly which tables of the okapi could be dropped, so i would not suggest anything, that make it maybe worse...

[Mr.Yuck]

Thanks! I will try to repair per your advice, but I was given this link: [url=http://stackoverflow.com/questions/4582832/repair-all-tables-in-one-go]http://stackoverflow.com/questions/4582832/repair-all-tables-in-one-go[/url] and it wouldn't work, because there was a missing file. Usually this one; here's an excerpt from one of my about 100 OKAPI error emails today:

===== ERROR MESSAGE =====
SQL Error 1017: Can't find file: 'okapi_cache_reads' (errno: 2)

The query was:

insert into okapi_cache_reads (`cache_key`)
values ('tile/a7d645183cc90e6ccaeb6fc8cfbad650')
=========================

[bohrsty]

ok, this table is empty in our okapi installation... and "cache" sounds like "will be filled again if needed"... so try to delete/drop and recreate it using the table the table definitions in http://opencaching.pl/okapi/devel/dbstruct:

Code: Alles auswählen

--
-- Table structure for table `okapi_cache_reads`
--

DROP TABLE IF EXISTS `okapi_cache_reads`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `okapi_cache_reads` (
  `cache_key` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

copy that code into the "sql" box of phpmyadmin or put in a temporary sql-file and import it in the database...

[Mr.Yuck]

If I click the SQL tab on the main screen of phpmyadmin after I log in, an "sql box" comes up that says "Run SQL query/queries on server "localhost":"

So you are of the opinion I should copy that code into that box, and click "go"? Is there anything else I need to do, or is that it? Thanks for taking the time to attempt to help out here!

[bohrsty]

yes, execute that sql script and you are done (it is taken from the link above)...

the first line drops the table and the following not starting with "/*!" will create it again...
if that produces no errors the table is repaired and the "mail terror" should end if there are no other corrupted tables. if there are more emails, we have to look what the tables are and do and try to repair them...

[Mr.Yuck]

Well, I learned something, thank you!! I did not need to do that though, wrygiel from .pl came back online Sunday morning (after about a 5 day absence), and fixed it for us. Did he do exactly what you said to do? We'll never know, I guess. This forum may be hearing from me again soon, I seem to have broken the "Länderseiten" and "Spenden" sections on the left hand menu of our site. But I'm going to get the file off of opencaching.org.uk first, and maybe I can figure out what went wrong from that.