Table Crash sys_messages; cron job run

OK, the report I get is that the table sys_messages crashed.  And this query.

Query:

SELECT `ID`, `Sender`, `Type` FROM `sys_messages` WHERE ( `Recipient` = 5 AND NOT FIND_IN_SET('Recipient', `Trash`) ) AND `New` = '1' ORDER BY `Date`  

I understand the query, what I don't understand is why that would crash the sys_messages table.

I think I may have a clue as to what happened.  I had a member to quit around the same time this happened.

Here is a question for the coders; When users sends messages to each other, what happens when one of the members quit.  In normal emails, the emails I get are sent to me and become files on my server.  If the sender of the emails deletes emails on their side; or deletes their email account and has all emails wiped, it does not affect me.  The same should be for Dolphin.  The emails I have in my account from other members should still be there.  I have a feeling that if a member quits, then all emails are gone, even the ones that are sitting in my account; because the emails are not really sitting in my account, they are in the sys_messages table.  I think I have noticed this on a live site that I maintain.  I think the sys_messages table is crashing at cron job run because it isn't finding the messages or the members IDs or something along those lines. 

In other words, I think this is a coding bug that is showing up on my site.  I will have to do some digging to see.

That query should not cause a table to crash.

I would check hard disk space. Especially on Linux systems with multiple partitions. Disk space problems are the number one cause of table crashes.

On some Linux systems the main tmp folder is in it's own partition. If that is low on space, it can cause problems when the database server is under load which it would be during cron runs.

Separate partitions for /var/lib where database are normally stored that are low on space could cause it.

Even hardware problems such as faulty memory or hard drives can be a possible cause.

I have never known a simple query to be the cause of a table crash.

MyISAM tables crash more frequently than InnoDB does.

In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table. If you start up mysql or something caused it to suddenly restart, and the number in the header does not match the number of actual file handles, mysqld treats the table as crashed.

You could try changing the table to InnoDB if it's a MyISAM table.

But my first suspected area would be partition space. Second i would suspect bad drive sectors. Especially if it has been happening frequently.

It isn't a space issue.  As far as the physical disk is concern, that I am not sure.  It is this one table; sys_messages.  I made some changes to the database config, increasing resources and the table went for over a week without crashing.  I monitor the database server for errors and such.  The database server is not shutting down or restarting.  Last night I had a member to quit and then join again about the time the cron job ran.  I have a feeling that the messages are not being properly cleared.

I will have to check the hard drives; they are two in a RAID configuration.  Before doing anything, I need to backup.

I had a similar thing to happen with a module; one particular table of that module would crash when the cron job for it ran.  It may be that the table is not actually crashed; I am using MariaDB; which should be using the same error codes that mySQL uses.

The engine type is what Boonex used:

CREATE TABLE `sys_messages` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Sender` int(10) unsigned NOT NULL DEFAULT '0',
  `Recipient` int(10) unsigned NOT NULL DEFAULT '0',
  `Text` mediumtext NOT NULL,
  `Subject` varchar(255) NOT NULL DEFAULT '',
  `New` enum('0','1') NOT NULL DEFAULT '1',
  `Type` enum('letter','greeting') NOT NULL DEFAULT 'letter',
  `Trash` set('sender','recipient') NOT NULL,
  `TrashNotView` set('sender','recipient') NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Pair` (`Sender`,`Recipient`),
  KEY `TrashNotView` (`TrashNotView`),
  KEY `Trash` (`Trash`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Seems very weird that a query like that would cause a table to crash...

SELECT `ID`, `Sender`, `Type` FROM `sys_messages` WHERE ( `Recipient` = 5 AND NOT FIND_IN_SET('Recipient', `Trash`) ) AND `New` = '1' ORDER BY `Date`

I have Cron job task running that execute a lot more advanced queries on the sys_messages table and my sys_messages table also has additional indexed columns and foreign keys.

However, I have previously had tables crash due to memory exhaustion or low disk space just as deano92964  has correctly pointed out as a possible reason.

The report you got, was there any more info?

If I understand the query; it is executed to report all new messages from sender to recipient.  It does this for each member each night as a cron job.  I don't have a huge member base.  The number of records in the sys_messages is around 8914.

It just occurred to me I am chasing the wrong rabbit.  It isn't the query above, or the script that is running it; it is what is happening before that.  When the query above is executed, the table has already crashed; the query finds a crashed table and thus the error report showing the query that was trying to be executed.

aha goody, am glad you found the issue  

I haven't found the issue; just making guesses as where to look.  Tonight the table did not crash.  Too busy today to look around at the scripts.

Optimise table command was the cause of the issue.  When you run optimise table it locks the table.  It then makes a copy of the table, copying each item to the new copy.  When it is finished, it then destroys the original table and replaces it with the copy.  It is possible that it screws up the open handles of the table or that some other cron job running can't access the table during this operation and thus it is reported as crashed.  I don't know.

aha, thanks gg for sharing that info, now I finally know the cause of this mysterious "to many locked tables" issues that has been lurking, have not found any time to investigate and fortunately, thanks to your post, it's now already clear what caused this annoying little bastard ¤&"#%(/!%¤/#¤%  error when for example doing mysqldump, found easy quick way around it, but regardless it's been an irritating occurrence, that's now been solved  so cheers to you, have a nice day!