Can"t drop index

Hello:

When I try to drop an index from an InnoDB table:

drop index company_n56 on company;

I get this error:

ERROR 1025 (HY000): Error on rename of './thymeleweb/#sql-788_1218' to
'./thymeleweb/company' (errno: 150)

Look at the index using: show keys from company\G
gives me this info for that key:

Table: company
Non_unique: 1
Key_name: company_n56
Seq_in_index: 1
Column_name: client_manager_person_id_oid
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:

The field is a varchar field that holds the id of a record in
the person table:

Field: client_manager_person_id_oid
Type: varchar(256)
Null: YES
Key: MUL
Default: NULL
Extra:

When I look at the person table, I do not see an index that would
refer back to this table.

Any ideas why I can't drop this index?

Thanks,
Neil

--
Neil Aggarwal, (281)846-8957
MySQL pre-installed on a virtual private server for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Can"t drop index

--001636ed7614373bb90483441c15
Content-Type: text/plain; charset=ISO-8859-1

Hi Neil,

Just check it up that any active transaction is still there in innodb
internal data dictionary.

Execute show engine innodb status\G

Check for active transaction related to the current table. If it's there
kill that transaction id and try again.

_Krishna



On Fri, Apr 2, 2010 at 10:35 PM, Neil Aggarwal wrote:

> Hello:
>
> When I try to drop an index from an InnoDB table:
>
> drop index company_n56 on company;
>
> I get this error:
>
> ERROR 1025 (HY000): Error on rename of './thymeleweb/#sql-788_1218' to
> './thymeleweb/company' (errno: 150)
>
> Look at the index using: show keys from company\G
> gives me this info for that key:
>
> Table: company
> Non_unique: 1
> Key_name: company_n56
> Seq_in_index: 1
> Column_name: client_manager_person_id_oid
> Collation: A
> Cardinality: 2
> Sub_part: NULL
> Packed: NULL
> Null: YES
> Index_type: BTREE
> Comment:
>
> The field is a varchar field that holds the id of a record in
> the person table:
>
> Field: client_manager_person_id_oid
> Type: varchar(256)
> Null: YES
> Key: MUL
> Default: NULL
> Extra:
>
> When I look at the person table, I do not see an index that would
> refer back to this table.
>
> Any ideas why I can't drop this index?
>
> Thanks,
> Neil
>
> --
> Neil Aggarwal, (281)846-8957
> MySQL pre-installed on a virtual private server for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--001636ed7614373bb90483441c15--

RE: Can"t drop index

Krishna:

> Just check it up that any active transaction is still there in innodb
> internal data dictionary.

Acutally I figured it out.
There was a constraint on that column as well.
Once I dropped the constraint, I was able to drop the index.

Thanks for the help,
Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/mysql
MySQL pre-installed on a virtual private server for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org