By thinkinkless on

Hi y'all,
We have reached out to some of the high-level developers but have not heard back from any of them yet. If any of you can offer help it's much appreciated. If fees are involved feel free to contact me outside the forums: suzi at thinkinkless.com and jarah at fresnofamous.com
disclosure: we don't have funding but we are going to die young if we can't get these crashes to stop. we may need a "real" drupal developer to jump in.

Here's the deal...
We launched fresnofamous.com yesterday with everything ported to drupal.
Hosting company immediately advised we move to a dedicated server. We are pushing the limits of that as well.
Granted we run a ton of blocks, and were working on tuning the server, but i think there are broader issues:

Overall the cache overhead runs extremely high. I have been monitoring all day. Today i had to drop and rebuild it twice - the last time after cron could not find it.
My instinct is that we have some sort of invalid character data in the node entries that are causing these bugs. The final error i list below looks like the teaser break is the bug. I'm in way over my head trying to troubleshoot.

during cron:

Can't open file: 'cache.MYI' (errno: 144) query: DELETE FROM cache WHERE cid = 'variables' in /usr/www/users/x/drupal/includes/database.mysql.inc on line 66.

another from cron:

Got error 127 from storage engine query: DELETE FROM cache WHERE expire != 0 AND expire < 1135987534 in /usr/www/users/x/drupal/includes/database.mysql.inc on line 66.

additionally i see a ton of errors like:

Duplicate entry 'menu:108:en' for key 1 query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('menu:108:en', 'a:3:{s:10:\"path index\";a:288:{s:16:\"admin/aggregator\";s:3:\"115\";s:26:\"admin/aggregator/edit/feed\";i:-2;s:30:\"admin/aggregator/edit/category\";i:-3;s:23:\"admin/aggregator/remove\";i:-4;s:23:\"admin/aggregator/update\";i:-5;s:21:\"admin/aggregator/list\";i:-6;s:25:\"admin/aggregator/add/feed\";i:-7;s:29:\"admin/aggregator/add/category\";i:-8;s:10:\"aggregator\";s:3:\"140\";s:18:\"aggregator/sources\";s:3:\"117\";s:21:\"aggregator/categories\";s:3:\"118\";s:20:\"aggregator/sources/6\";s:3:\"125\";s:25:\"aggregator/sources/6/view\";i:-13;s:31:\"aggregator/sources/6/categorize\";i:-14;s:30:\"aggregator/sources/6/configure\";i:-15;s:20:\"aggreg in /usr/www/users/x/drupal/includes/database.mysql.inc on line 66.

Duplicate entry 'filter:1:1ac30658eff9bbdd0c080e21b28904e7' for key 1 query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('filter:1:1ac30658eff9bbdd0c080e21b28904e7', '<p >The <a href=\"http://www.fresnobee.com/local/story/11555538p-12290198c.html\">County Board of Supervisors</a> will decide tomorrow whether or not to approve a contract with Comcast Cable. Comcast is the only cable provider in the unincorporated areas of Fresno County. At issue is PEG access: Public, Educational, and Governmental. Comcast has agreed to add four new channels for government and educational use, but none for the public. Some county residents and activists want the county to hold out and force Comcast to create a public access channel and media center.</p>\n<p>--&gt;</p>\n', 1135987426, 1136073826, '') in /usr/www/users/famous/drupalnew/includes/database.mysql.inc on line 66.

TIA for any sanity that can be lent.

⋅ Categories: Drupal 4.6.x

Comments

Zen’s picture

Zen commented

Things to try:

-If you are a PHPMyAdmin user, try repairing the table
-and/or from the commandline use myisamchk on the cache table and preferrably on all other tables as well.

You can also just delete [not truncate] the entire cache table and recreate it.

Needless to say, back up the database before you begin fooling around.

hth
-K

thinkinkless’s picture

thinkinkless commented

I have been optimizing regularly and did have to recreate the cache from scratch twice today. Also set cron to run every 1/2 hour and no difference. Something has gone seriously awry. Overhead in the cache builds from between 200,000 to roughly 700,000 Bytes every 1/2 -- 1 hour. It's ugly in there.

And yes, backups are a given. :)
It's nice to know that at least the attempts to keep this thing alive in the meanwhile are the right approach.

Thanks for the response.
Any other tips out there?

thinkinkless’s picture

thinkinkless commented

fyi - i edited the server path you see in the first few code snippets.
forgot to do the last.

they are all actually what you see in the last snippet - there's not a disagreement in what the logs are producing.

posting this comment just in case someone notices that. :)

rbrooks00’s picture

rbrooks00 commented

Did a little searching around the forums here and on google for some of the errors you are getting and everything seems to point to the need to repair mySQL tables.

http://drupal.org/node/2597

http://forums.mysql.com/read.php?88,27250,27250#msg-27250

Some people are also suggesting truncate as a solution:

http://drupal.org/node/9318 - obviously you probably would only want to do this with the cache though.

I'd suggest running a repair on all the tables where you are seeing error messages. You can do this through PhpMyAdmin.

============================================
BuyBlue.org

thinkinkless’s picture

thinkinkless commented

It turns out we've had quite a few contributing factors causing us issues and we are continuing to sort them out. I will keep this thread updated as we find solutions in case it can be of help to anyone else.

One thing for sure that was contributing to our crashes was a bad ram stick on our new server.

We got really lucky - moshe weitzman was able to create some availability for us. He discovered that we had way too many url aliases (over 4,000). We were using pathauto all over the place so the search engines would have nice urls to chew on. Evidently this can cause real problems with large sites running 4.6. After dumping these and turning off pathauto we realized a huge performance gain in load time for our pages. We plan to try pathauto again when we can migrate to 4.7.

Regarding the cache corruptions, there may be a larger issue at work here in the form of collations and charsets not matching up. Phpmyadmin may have changed some things during a recent import, or charsets in the tables may have been changed when we moved to our new server. I believe this is affecting the accuracy of our search results as well.

I have seen a couple of related threads:
http://drupal.org/node/30261
http://drupal.org/node/32829

and also read up in the mysql forum you listed - the errors look very similar.
Unfortunately I have not seen a difinitive/safe way to deal with this in drupal yet.

We'd love to hear from anyone who has successfully dealt with this issue.

thinkinkless’s picture

thinkinkless commented

Last night it was a corrupt sessions table that brought us down.

Fatal error: Can't open file: 'sessions.MYI' (errno: 145) query: SELECT u.*, s.* 
FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 
'51a8f7c146e653b8617740435f8d0409' AND u.status < 3 LIMIT 0, 1 in 
/usr/www/users/x/drupal/includes/database.mysql.inc on line 66

Does anyone know of a way to scrub the data in our tables so we can rebuild? Or of someone we can hire to do this? Any recommendations are greatly appreciated.

rbrooks00’s picture

rbrooks00 commented

You can safely truncate the sessions table, it is just going to log out anyone that happens to be logged in. You can do the same with the cache table.

The charset angle might be worth following up. In order to make sure your database was correct you'd have to check the settings for the mysql config file. You'd then have to export all your data, change the statements in the raw data to import as a different character set. We are using en-iso-8859-1 on our server, what is it set to on yours? There may be some additional mySQL documentation out there on how to write your SQL statements to do this.

============================================
BuyBlue.org

thinkinkless’s picture

thinkinkless commented

RE truncating - the tables are actually beyond repair when this happens and they have to be rebuilt. The real issue is that we've only been live running drupal for a few days and we have already this level of table corruption at least 3 times since launch.
It brings the whole site down.
This basically means we have to babysit 24\7.
We did not have these issues pre-launch or pre-server migration.

RE the charset - iso-8859-1 - i see that's a set used by drupal ages ago. Drupal mySQL charset is supposed to be UTF-8 Unicode (utf8).
Do you know why your developer used that (vs) utf8? Thanks for sharing such great details, btw.

The mysql dump our host used to migrate us had the tables set with:
ENGINE=MyISAM DEFAULT CHARSET=latin1;
I actually told them the dump looked strange to me but they insisted it was fine. I've only been drupaling in full for a few months and am afraid I missed a biggie here.

I'm not opposed to trying what you suggested above - but my instinct is that won't be sufficient. Shouldn't the data itself be re-encoded before importing? Especially given the latin1 issue? I know I can use bbedit (text editor) to change the encoding of the dump - and i can force a charset into the table rebuild. BUT I'm too scared to try without hearing from (or hiring) someone else who has successfully achieved this.

I just saw another thread that made me scratch my head...
http://drupal.org/node/2230

This is old, but has to do with utf8 and field sizes. I do know i changed some fields from 128 to 200 or 255, simply because 128 was insufficient.
Now I wonder if this also contributed to these larger issues....

We are not running a multi-language site. Can we switch everything to en-iso-8859-1 like the lovely and seemingly stable buyblue.org site?

rbrooks00’s picture

rbrooks00 commented

I ran across some documentation at the MySQL site which may be of use here.

This page describes how tables can be corrupted: http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html

Most importantly it gives you some reasons why it might happen:

- The mysqld process is killed in the middle of a write.
- Unexpected computer shutdown occurs (for example, the computer is turned off).
- Hardware failures.
- You are using an external program (such as myisamchk) on a table that is being modified by the server at the same time.
- A software bug in the MySQL or MyISAM code.

Of these I'd say that maybe some of the tables are corrupted due to the other problems you've had with the server. The last bullet suggests it could be a software bug but given that you are running 4.6 I'd think that more people would have had this problem.

The article also says this:

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation. This is a bug. You should try to create a reproducible test case that demonstrates the problem.

You should probably check the error logs for mysqld errors. You can also run that check table statement to see if there are any tables that are corrupted.

============================================
BuyBlue.org

thinkinkless’s picture

thinkinkless commented

I saw that post this morning after i looked at the other links you posted. Even though it's for MySQL 5 (we are 4.1) it makes total sense and does correspond with the bad ram.

It does not explain the sessions table corruption last night. I just ran 'check table' on our cache, which is running an overhead of nearly 1mb on a 3.7mb table and it tests fine.

I don't know what these crashes potentially did to the data in the tables, and if such broken data (say in a node table) can break the cache or sessions when an insert is attempted?

While I have full access to our mysql logs, I'm afraid I am worthless when it comes to interpreting them.

Lao Ban’s picture

Lao Ban commented

Visited your site. It seems works fine. Does problem solved?

The nav bar on the top looks cool. Whick module is that? Thanks.

thinkinkless’s picture

thinkinkless commented

Database problems not completely solved, but we are more stable and are continuing to look for solutions. I'll post fixes as we find them.

RE the navbar, that's not a module. It is hard-coded into the theme file. We are bypassing drupal's primary and secondary links functionality for the sake of design flexibility. If you view source on the pages you should be able to grab my css for the nav and the ''suckerfish menu" javascript that helps make this work.

Lao Ban’s picture

Lao Ban commented

Thank you for let me know the secrect. I will try to work it out.

I still think there should be a module out there or we need make one for the nav bar.

I do not know how to make a module. Otherwise I will make one out.

Thanks again for sharing.

thinkinkless’s picture

thinkinkless commented

Thanks again to everyone who posted suggestions for us here. We have not had a table corruption (fingers crossed) since last sunday night.

We're pretty sure the server crashes due to the bad ram caused the ongoing table corruptions. After completely rebuilding cache and sessions, and truncating accesslog, we are in 'so far, so good' land. I will continue to try to get answers on the high overheads in certain tables.

Our search has been repaired - moshe wiped the index and that brought back all of the nodes that were not being found.

My advice to new drupallers: If you are going to tackle a migration or install of any magnitude, get a 'real' developer involved from the beginning. The forums are a great tool but in the case of an emergency with your install it is really helpful to have an expert around to consult on fixes. Trial by fire is no fun.