List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:March 23 2009 8:09pm
Subject:RE: Error -1
View as plain text  

>-----Original Message-----
>From: Jerry Schwartz [mailto:jschwartz@stripped]
>Sent: Saturday, March 21, 2009 9:57 PM
>To: 'MySql'
>Subject: Error -1
>
>I'm running 5.1.31-community on WinXP.
>
>
>ERROR 1030 (HY000): Got error -1 from storage engine
>
>from the following query:
>
>
>SELECT SQL_CALC_FOUND_ROWS customers.*, account.account_name,
>account.real_name AS `Sales Rep`, stage.stage_name, memos.note_text AS
>m_note_text
>FROM customers INNER JOIN account ON account.account_id =
>customers.account_id
>INNER JOIN stage ON customers.stage_id = stage.stage_id
>LEFT JOIN memos ON customers.customer_id = memos.customer_id AND
>(memos.note_id = (SELECT note_id FROM memos WHERE
>memos.customer_id=customers.customer_id
>
>AND (MATCH(memos.note_text) AGAINST ("frog"))
>
>ORDER BY note_timestamp DESC LIMIT 1) OR memos.note_id IS NULL) LIMIT
>0,25;
>
[JS] I should have mentioned that I can replace the MATCH with any number of
LIKE tests with no trouble.

I rewrote the whole stinking mess to look like this:

SELECT SQL_CALC_FOUND_ROWS * FROM
  (SELECT customers.*, account.account_name, account.real_name AS `Sales
Rep`, stage.stage_name,
    (SELECT GROUP_CONCAT(giiexpr_db.topic.topic_code) FROM cust_topics INNER
JOIN giiexpr_db.topic ON
      cust_topics.topic_id =   giiexpr_db.topic.topic_id WHERE
(cust_topics.customer_id = customers.customer_id)
      GROUP BY cust_topics.customer_id) AS topic_list,
    (SELECT memos.note_text FROM memos WHERE (memos.customer_id =
customers.customer_id)
      AND (MATCH(memos.note_text) AGAINST ("pork" IN NATURAL LANGUAGE MODE))

      ORDER BY note_timestamp DESC LIMIT 1) AS m_note_text
  FROM customers INNER JOIN account ON account.account_id =
customers.account_id
  INNER JOIN stage ON customers.stage_id = stage.stage_id WHERE
(customers.account_id = 10) ) AS custs
WHERE (m_note_text IS NOT NULL);

I get the same Error -1. I thought I would try to do an EXPLAIN, hoping that
MySQL would share its thoughts and feeling with me, but an EXPLAIN also
generates an Error -1.

The subquery

    SELECT memos.note_text FROM memos WHERE (memos.customer_id =
customers.customer_id)
      AND (MATCH(memos.note_text) AGAINST ("pork" IN NATURAL LANGUAGE MODE))

      ORDER BY note_timestamp DESC LIMIT 1

works fine by itself.


Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com






Thread
Error -1Jerry Schwartz22 Mar
  • RE: Error -1Jerry Schwartz23 Mar
RE: Error -1Jerry Schwartz23 Mar
  • RE: Error -1Martin Gainty23 Mar
    • RE: Error -1Jerry Schwartz23 Mar