Help, can't add index

[cvonrabe]

I have a fairly sizeable table and would like to add another index to it to help my queries. When I attempt to: ALTER TABLE 'tlog' ADD INDEX ('Transdate'), it runs for quite some time and the comes back with the following error:

ERROR 1: Can't create/write to file '/root/tmp/STbRK3pe' (Errcode: 13)

I have tried several things to get around this. I have used another user w/all permissions and still get the same error. I have tried running mysql as 'root' and still get the same error. I am using Mandrake 9.0 and version 4.0.12-standard. I placed my installation in a non-standard location due to my /var/ partition size. Everything else has worked quite well, execpt in this case. Any ideas or solutions would be greatly appreciated.

cvonrabe

 

[maxwolf]

recreate the table with the index.
This procedure will require some downtime of your sql-server. However - this should work.
Follow the steps to backup/drop/create and restore your table:

1) Create a backup of your table:
bash> mysqldump mydb large_table > large_table.sql
2) Modify the backup-file:
bash> vi large_table.sql
(sure you can use emacs or wordpad on windows-systems or whatever you like to edit the file, but vi actually can
handle (i.e. open and save) very large files)
in the "large_table.sql" you will find the sql-statements to
create the table "large_table" and "INSERT INTO" statements
to restore the data.
2.1) insert the sql-Statement "DROP TABLE large_table;"
on the top of the file, this will remove the table
b4 it tries to create it again.
2.2) Add your "CREATE INDEX"-statement right after the
"CREATE TABLE"-statement, before the first "INSERT INTO"
is done.
2.3) :wq (<- save and quit)
3) Drop-and-recreate-table
use simple cat command redirecting it to mysql top recreate
your large table:
bash> cat large_table.sql | mysql mydb
---
Because you're creating the index on an empty table,
it should be quite an easy task for mySQL-Server.
You insert all the data AFTER you have created all indexes,
so that all should be fine. However, the dump and restore
might take a while.

Hope this helps,
Good Luck.

 

[QatQat]

Isn't the case that you have not named your index?


ALTER TABLE TLOG ADD INDEX idxname('fieldName')


This should work.


Bye

Qatqat

I have been happy throughout my life in thinking that samba was I kind of dance; now I live with Linux and all I do is working.