Incorrect key file for table '#sql_43b6_0.MYI'; try to repair it
Posted by:
aaoz gull
Date: July 15, 2011 06:44AM
hi,
i'm facing problem while execute a query on 120K records. if in remove the number of columns with keywords 'CANCAT', 'ifnull' and 'CONVERT' in select statement. it will work fine. here is my query.
SELECT YEARWEEK(creation_time), WORKER.worker_id, USER.user_id, creation_time, last_login, 0 AS activation_time, transport_type, carrier, GROUP_CONCAT(DISTINCT CONCAT(phone, ' (', phone_type, ')') SEPARATOR ', ') phoneNumbers, ((USER.sms_subscription & 1) = 1) AS 3733Subscription, USER.email AS emailAddress,USER.name AS firstName, USER.lastname AS lastName, CONVERT(CONCAT(USER.name, ' ', USER.lastname) USING utf8) AS fullName, CONCAT(ADDRESS.district, ', ', ADDRESS.region) AS AddrInfo, district, region, GROUP_CONCAT(CONCAT(enumeration_text, ' at ', company_name, ' [', job_type_code, ']') ORDER BY start_date SEPARATOR '; ' ) AS JobHistory, ((WORKER.premium_job_tips = 1) AND (transport_type = 'SMS')) AS pendingJobTipSubscription, (WORKER.premium_job_tips = 2) AS confirmedJobTipSubscription, marketing_source AS marketingSource, IFNULL(COUNT(worker_reference_id) > 0, 0) AS resumeComplete, USER.verified
FROM USER, USER_PHONE, WORKER
LEFT JOIN ADDRESS ON ADDRESS.worker_id = WORKER.worker_id
LEFT JOIN WORKER_JOB_HISTORY ON WORKER_JOB_HISTORY.worker_id = WORKER.worker_id
LEFT JOIN ENUMERATION_MAPPING ON (ENUMERATION_MAPPING.enumeration_value = CONVERT(WORKER_JOB_HISTORY.job_type_code USING utf8) AND ENUMERATION_MAPPING.enumeration_type = 'JobType' AND language_code = 'es')
LEFT JOIN WORKER_REFERENCE ON WORKER_REFERENCE.worker_id = WORKER.worker_id
WHERE USER_PHONE.user_id = USER.user_id AND WORKER.user_id = USER.user_id AND USER.active = TRUE GROUP BY WORKER.worker_id ORDER BY creation_time, USER.name
when i execute this. I got this error. Incorrect key file for table '#sql_43b6_0.MYI'; try to repair it
I read many forums and reason behind this problem is less memory while SQL make temporary table and no more memory at server. How can i resolve this problem :( :(
MY system's configuration
OS: Linux
MYSQL: mysql-5.0.45
RAM: 1.43GB
HD: total 6.89GB and used 5.95
I read lot abot my.cnf My initial my.cnf configuration was
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
##innodb_buffer_pool_size = 1M
key_buffer_size = 1M
myisam_sort_buffer_size = 1M
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
i've replace it with sample file of SQL named 'my-huge.cnf' which are placed at
File Locations: /usr/share/doc/mysql-server-?.??.?? at linux
but can't get the result
Now i had changed my.cnf again, add tmp_table_size, max_heap_table_size variables and below are the detail
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512M
sort_buffer_size = 50M
read_buffer_size = 50M
read_rnd_buffer_size = 50M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 50M
#new added varibale
tmp_table_size=256M
max_heap_table_size=256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
:( still not get result. Any help will be apreciated
thanx
aaoz