Exception in Mysql query

[dangari]

My query below has been working okay for 1 year now and suddenly it is giving an exception:

(SELECT distinct(R.imei),R.mobile,S.msg,S.reply,M.model,
left(S.receiveTime,10) as receiveDate,
right(S.receiveTime,8) as receiveTime,'' as 'Reason'
from dbSAMSUNGmain.tREGDATA R,dbSAMSUNGmain.tMODELS M,dbSAMSUNGmain.tSMS_TRANSACTIONS S where
S.mobile=R.mobile and
replace(replace(S.msg,'/',''),' ','') LIKE concat('%',R.imei,'%') and
(S.reply like '%Registration successful%' || S.reply like '%Reg SUCCESSFUL%' ) and M.id=
(SELECT P.model_id FROM
dbSAMSUNGmain.tPRODUCTSmain P WHERE
P.imei=R.imei)
UNION
SELECT 0,SM.mobile,SM.msg,SM.reply,'N/A',
left(SM.receiveTime,10) as receiveDate,
right(SM.receiveTime,8) as receiveTime,'possibly grey'
 FROM dbSAMSUNGmain.tSMS_TRANSACTIONS SM
where msg LIKE '%reg%' and reply like '%grey%'
UNION
SELECT 0,SM.mobile,SM.msg,SM.reply,'N/A',
left(SM.receiveTime,10) as receiveDate,
right(SM.receiveTime,8) as receiveTime,'invalid date of birth'
 FROM dbSAMSUNGmain.tSMS_TRANSACTIONS SM
where msg LIKE '%reg%' and reply like '%invalid date of birth%'
UNION
SELECT 0,SM.mobile,SM.msg,SM.reply,'N/A',
left(SM.receiveTime,10) as receiveDate,
right(SM.receiveTime,8) as receiveTime,'phone previously registered'
 FROM dbSAMSUNGmain.tSMS_TRANSACTIONS SM
where msg LIKE '%reg%' and reply like '%Phone previously registered%'
UNION
SELECT 0,SM.mobile,SM.msg,SM.reply,'N/A',
left(SM.receiveTime,10) as receiveDate,
right(SM.receiveTime,8) as receiveTime,'other'
 FROM dbSAMSUNGmain.tSMS_TRANSACTIONS SM
where msg LIKE '%reg%' and reply not like '%has successfully been registered%' and reply not like '%Registration successful%'  and reply not like '%Reg SUCCESSFUL%'  and reply not like '%invalid date of birth%'
and reply not like '%grey%' and reply not like '%Phone previously registered%') as TABLE3 WHERE receiveDate >= left('2009-10-25',10) AND receiveDate <= left('2009-12-31',10) ORDER BY mobile ASC;

Here is the exception:

java.sql.SQLException: Incorrect key file for table '/tmp/#sql_7cd_0.MYI'; try to repair it
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1166)
        at loyaltyviewbean.customreports.getArray(customreports.java:439)
        at loyaltyviewbean.customreports.getActualReport(customreports.java:392)
        at org.apache.jsp.customizedrpt_jsp._jspService(customizedrpt_jsp.java:361)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at loyaltyviewbean.SessionTimeoutFilter.doFilter(SessionTimeoutFilter.java:60)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
        at java.lang.Thread.run(Thread.java:619)

Can anyone tell me what is the issue and how best to resolve it.
Your help will be much appreciated, and I thank you in advance...In case this needs to be posted elsewhere, please direct me and I will duly oblige.

[smantscheff]

Your table and/or index file(s) are corrupt. Restore them from a backup or try to repair them. Have a look at http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html

[dangari]

Your table and/or index file(s) are corrupt. Restore them from a backup or try to repair them. Have a look at http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html

Hi.
Much appreciated.However, I am bit fuzzy on the details because I have selected all relevant tables as regards this query and repaired them (both Quick and Extended) and still the problem persists. I have also connected to the MySQL server, USEd the database relevant and typed

myisamchk --recover tPRODUCTSmain

(this being my MYISAM table) and it gives me the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myisamchk --recover tPRODUCTSmain' at line 1

Kindly assist here.Thank you.

[dangari]

Hi.
Much appreciated.However, I am bit fuzzy on the details because I have selected all relevant tables as regards this query and repaired them (both Quick and Extended) and still the problem persists. I have also connected to the MySQL server, USEd the database relevant and typed

myisamchk --recover tPRODUCTSmain

(this being my MYISAM table) and it gives me the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myisamchk --recover tPRODUCTSmain' at line 1

Kindly assist here.Thank you.

i have just done:

mysql> REPAIR  TABLE tPRODUCTSmain EXTENDED;
+-----------------------------+--------+----------+----------+
| Table                       | Op     | Msg_type | Msg_text |
+-----------------------------+--------+----------+----------+
| dbSAMSUNGmain.tPRODUCTSmain | repair | status   | OK       |
+-----------------------------+--------+----------+----------+
1 row in set (26.47 sec)

mysql> REPAIR  TABLE tMODELS EXTENDED;
+-----------------------+--------+----------+---------------------------------------------------------+
| Table                 | Op     | Msg_type | Msg_text                                                |
+-----------------------+--------+----------+---------------------------------------------------------+
| dbSAMSUNGmain.tMODELS | repair | note     | The storage engine for the table doesn't support repair |
+-----------------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> REPAIR  TABLE tREGDATA EXTENDED;
+------------------------+--------+----------+----------+
| Table                  | Op     | Msg_type | Msg_text |
+------------------------+--------+----------+----------+
| dbSAMSUNGmain.tREGDATA | repair | status   | OK       |
+------------------------+--------+----------+----------+
1 row in set (0.90 sec)

mysql> REPAIR  TABLE tSMS_TRANSACTIONS EXTENDED;
+---------------------------------+--------+----------+----------+
| Table                           | Op     | Msg_type | Msg_text |
+---------------------------------+--------+----------+----------+
| dbSAMSUNGmain.tSMS_TRANSACTIONS | repair | status   | OK       |
+---------------------------------+--------+----------+----------+
1 row in set (8.05 sec)

And still no change in the situation. Anyone with insight, please help here.

[smantscheff]

What I would try:
- Dump the table to a text file, drop it and recreate it from the dump.
- Restart the server.

[dangari]

What I would try:
- Dump the table to a text file, drop it and recreate it from the dump.
- Restart the server.

Thanks a lot.
I think your idea is superb.However, I found out that the disk space for my application was being used up and this was actually the genesis of this entire mess.So cleaning up by deleting old files from the partition, did it for me!
Nevertheless, I think you solved this exception and would be glad to mark it as solved once you revert smantscheff.

[smantscheff]

What do you mean - once I revert --?
I once ran into this bug, too. I think it really is a bug, MySQL not properly handling situations with low disk space. But this was once in 15 years, so I forget.

[dangari]

I did not mean that you retract your advice but that for you to gain the point, you had to have the last reply. Nothing bad

[mickjsn]

Well, thanks a lot! it's is really helpful... but i think, if exception comes due to the database corruption in MySQL then one should use MySQL repair tool. It's a great tool for fixing exceptions.