Drop & Rebuild Index Failure

[Rod Davis]

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?

[bobb]

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?