Incorrect key file for temporary table error
Hi
I am running a query and receiving the following error:
Error Code : 126
Incorrect key file for table '/tmp/#sql_4cb3_1.MYI'; try to repair it
My query is:
SELECT p.product_id, p.product_link, p.product_price, p.product_price_spidered, p.product_instock, p.product_model, m.* FROM products AS p
INNER JOIN merchants AS m WHERE (m.regexp_price!='' OR m.regexp_stock!='')
AND m.network=p.network AND m.merchant_id=p.merchant_id AND p.product_checking=0
AND (p.product_lastcheck IS NULL OR p.product_lastcheck<=DATE_SUB(NOW(), INTERVAL 12 HOUR))
GROUP BY p.product_id ORDER BY p.product_lastcheck
LIMIT 100
my.cnf:
[mysqld]
local-infile=0
set-variable = max_connections=500
safe-show-database
query_cache_size = 32M
query_cache_type=1
query_cache_limit=1M
key_buffer_size=256M
join_buffer_size=2M
max_allowed_packet=32M
#the following is used for ALTER TABLE speedup. Should be commented out normally
sort_buffer_size=64M
innodb_log_buffer_size=3M
Table formats:
CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`network` enum('aw','ba','am','cj','td','wg','direct','manual') NOT NULL DEFAULT 'manual',
`product_model` varchar(255) NOT NULL DEFAULT '',
`merchant_id` int(10) unsigned NOT NULL DEFAULT '0',
`category_id` int(10) unsigned NOT NULL DEFAULT '0',
`product_merchant_category` varchar(255) NOT NULL DEFAULT '',
`product_additional_model` varchar(255) NOT NULL DEFAULT '',
`product_name` varchar(255) NOT NULL DEFAULT '',
`product_desc` text NOT NULL,
`product_promo` varchar(255) NOT NULL DEFAULT '',
`product_warranty` varchar(255) NOT NULL DEFAULT '',
`product_image` varchar(255) NOT NULL DEFAULT '',
`product_thumbnail` varchar(255) NOT NULL DEFAULT '',
`product_link` varchar(255) NOT NULL DEFAULT '',
`product_afflink` varchar(255) NOT NULL DEFAULT '',
`product_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`product_delivery_price` decimal(10,2) DEFAULT NULL,
`product_instock` tinyint(1) NOT NULL DEFAULT '1',
`product_brand` varchar(255) DEFAULT NULL,
`product_lastcheck` datetime DEFAULT NULL,
`product_checking` tinyint(1) NOT NULL DEFAULT '0',
`product_disabled` tinyint(1) NOT NULL DEFAULT '0',
`product_name_manual` varchar(255) DEFAULT NULL,
`save_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`product_free_shipping` tinyint(1) NOT NULL DEFAULT '0',
`product_missing_times` tinyint(3) unsigned NOT NULL DEFAULT '0',
`product_feed_id` int(13) NOT NULL DEFAULT '0',
`price_bug` tinyint(1) NOT NULL DEFAULT '0',
`product_price_spidered` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`product_id`),
UNIQUE KEY `network_2` (`network`,`product_model`,`merchant_id`),
KEY `category_id` (`category_id`),
KEY `product_instock` (`product_instock`),
KEY `merchant_id` (`merchant_id`),
KEY `product_disabled` (`product_disabled`),
KEY `network` (`network`,`product_additional_model`),
KEY `product_feed_id` (`product_feed_id`),
KEY `price_bug` (`price_bug`),
KEY `product_checking` (`product_checking`),
KEY `product_lastcheck` (`product_lastcheck`)
) ENGINE=MyISAM AUTO_INCREMENT=20048486 DEFAULT CHARSET=latin1;
CREATE TABLE `merchants` (
`network` enum('aw','ba','am','cj','td','wg','direct','manual') NOT NULL DEFAULT 'manual',
`merchant_id` int(10) unsigned NOT NULL DEFAULT '0',
`merchant_name` varchar(255) NOT NULL DEFAULT '',
`merchant_image` varchar(255) NOT NULL DEFAULT '',
`regexp_price` varchar(255) NOT NULL DEFAULT '',
`regexp_price2` varchar(255) NOT NULL DEFAULT '',
`regexp_nostock` varchar(255) NOT NULL DEFAULT '',
`regexp_stock` varchar(255) NOT NULL DEFAULT '',
`free_shipping` enum('none','default','value','text') NOT NULL DEFAULT 'default',
`free_shipping_text` varchar(255) NOT NULL DEFAULT '',
`update_success_date` datetime DEFAULT NULL,
PRIMARY KEY (`merchant_id`,`network`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
If you need any further information please let me know.
Many thanks
Laurence