469,339 Members | 8,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,339 developers. It's quick & easy.

Drop & Rebuild Index Failure

Once a week, we run a script to drop all the indexes on tables in our
db, followed by another to add those indexes back (we insert every
day, so we do this to prevent index fragmenttation).

The database is named BX, and the script runs these two commands:

c:\mysql\bin\mysql BX < dropIndex.sql
c:\mysql\bin\mysql BX < addIndex.sql

We're running version 3.23.29 on Windows 2000.

The files 'dropIndex.sql' and 'addIndex.sql' consist of lines such as:

'ALTER TABLE TABLE_NAME DROP/INDEX INDEX_NAME'

This usually works well, but today we got this error from
'dropIndex.sql':

ERROR 7 at line 32: Error on rename of '.\bx\#sql-51c_b8a.MYD' to
'.\bx\equity_order.MYD' (Errcode: 13)

and this one from 'addIndex.sql':

ERROR 1061 at line 32: Duplicate key name 'EQ_SPX'

So it appears that the index called 'EQ_SPX' was never dropped, and
that's why it wasn't added and gave the duplicate error.

But, all the other indexes (including other indexes on the same table
as EQ_SPX) were dropped and added successfully (there are 40 in
total).

I've read that 'Errcode: 13' is a permission problem, but why would it
come up in the middle of a batch job?

How can I prevent this from re-occurring?

Also, how can I make sure the indexes and the tables are ok?
Jul 20 '05 #1
1 5229
my 2c...
Temporary disk space? (not nec. the same partition of the data)
good luck
bobb

"Rod Davis" <ro*******@mailinator.com> wrote in message
news:9c**************************@posting.google.c om...
Once a week, we run a script to drop all the indexes on tables in our
db, followed by another to add those indexes back (we insert every
day, so we do this to prevent index fragmenttation).

The database is named BX, and the script runs these two commands:

c:\mysql\bin\mysql BX < dropIndex.sql
c:\mysql\bin\mysql BX < addIndex.sql

We're running version 3.23.29 on Windows 2000.

The files 'dropIndex.sql' and 'addIndex.sql' consist of lines such as:

'ALTER TABLE TABLE_NAME DROP/INDEX INDEX_NAME'

This usually works well, but today we got this error from
'dropIndex.sql':

ERROR 7 at line 32: Error on rename of '.\bx\#sql-51c_b8a.MYD' to
'.\bx\equity_order.MYD' (Errcode: 13)

and this one from 'addIndex.sql':

ERROR 1061 at line 32: Duplicate key name 'EQ_SPX'

So it appears that the index called 'EQ_SPX' was never dropped, and
that's why it wasn't added and gave the duplicate error.

But, all the other indexes (including other indexes on the same table
as EQ_SPX) were dropped and added successfully (there are 40 in
total).

I've read that 'Errcode: 13' is a permission problem, but why would it
come up in the middle of a batch job?

How can I prevent this from re-occurring?

Also, how can I make sure the indexes and the tables are ok?

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Woody Rao | last post: by
1 post views Thread by Martial Spirit | last post: by
6 posts views Thread by jojobar | last post: by
3 posts views Thread by VB Programmer | last post: by
2 posts views Thread by Eero Tuomenoksa | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.