By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

Drop & Rebuild Index Failure

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.