I really don't know what's wrong with my MySQL installation. Everything was working fine, and then one day, it just exploded.

My triggers no longer work. I changed nothing to the database schema or anything.

A lot of results are permanently stale. For example, in one of my sites I have it so that users have a link to see all replies to their comments. It no longer shows new comments there, yet it still shows older ones. It doesnt' affect all queries, but some of them it does.

Everything seems to work perfectly fine on my local development server.

Has anyone ever heard of this before? I'm pretty sure it's not my application and it's a problem with mySQL or my LAMP stack.

Running:

mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper

Ubuntu 9.10

apache 2.2.12

php 5.2.10

Any help is greatly appreciated. If you need log files or anything let me know. I've googled this, haven't found anything, and have no idea where to start.

Corrupt MyISAM tables maybe?

CHECK TABLE table_name

mysqlcheck -A -u root -p -r -a -o

That'll repair, analyze and optimize all tables in all databases using the mysql root user prompting for a password, try that.

that didn't fix it. It does seem like something is corrupt or something. I mean, I even exported the database from my server and uploaded it to my local, and it worked fine.

If I were to wipe my ubuntu install and start over, what would I need to save? Clearly the database, website files, and sites-enabled, but is there anything else?

Have you implemented any kind of caching for apache?

If you run a mysql query (one that returns stale results) at the mysql command line, does it return correct results?

Now that you mention it I did install APC. But I put the query into mysql directly on my linode and it still returned messed up results.

I don't know why triggers aren't working either. I have a comment system. One table for comments, another for posts. When someone inserts a comment into the database, the trigger goes to the post table and updates the comment count field. That doesn't work either for some bizarre reason.

Should I start disabling extensions and see what happens?

Probably wouldn't hurt to post this puzzler on the MySQL forum as well.

http://forums.mysql.com/