Jump to content

[SOLVED] Incorrect key file for table...


MrBaseball34
 Share

Recommended Posts

When selecting the sort by "price: lowest first" option in my categories page, I'm getting this

Incorrect key file for table '/tmp/#sql_15b5_0.MYI'; try to repair it

       SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new,
           (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice
       FROM `ps_category_product` cp
       LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
       LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
       LEFT JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1)
       LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
       LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
       LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
       LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 1)
       LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
       WHERE cp.`id_category` = 14 AND p.`active` = 1
        ORDER BY `orderprice` ASC
           LIMIT 0,10



Obviously it is a temp table but how do I remedy this?

Share this post


Link to post
Share on other sites

Ok, after further examination and tests, the process by which I was optimizing the query will not work as desired.

Here was the code to optimize the query above:

SELECT * FROM (
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new,
      (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice
FROM `ps_category_product` cp
LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1)
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 1)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE cp.`id_category` = 14 AND p.`active` = 1
LIMIT 0,10
) ORDER BY `orderprice` ASC



However, when modifying the query in Category::getProducts(), the meaning of the orderby would be defeated because this only orders the 10 that is returned, not the entire catalog before limiting to 10. (does that make sense?)

The calculation, however, is a problem that I have reported as a bug to the team.
If you don't have a tax, the t.`rate` returns NULL so none of your calculations work as planned.
The t.`rate` in the calculation should be replaced with IFNULL(t.`rate`, 0.0)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More