MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
Incorrect key file for table '#sql_43b6_0.MYI'; try to repair it
July 15, 2011 06:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.