List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:August 10 2010 10:03pm
Subject:Re: Dropping ALL indexes from a database / not just a table?
View as plain text  
auto_increment is only allowed on primary-keyed columns.  I expect it
is not allowing you to drop the primary key because that column has
the auto_increment attribute.  Drop that manually, and the primary key
should be able to let go.

 - md

On Tue, Aug 10, 2010 at 5:58 PM, Nunzio Daveri <nunziodaveri@stripped> wrote:
> Hi Micheal and all, ok so I did some digging around and I still can't find
> why I cant drop the last few indexes.
>
> mysql> SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema
> = 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY';
> +----------+
> | COUNT(1) |
> +----------+
> |        1 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> DESCRIBE dbt_Fruit;
> +------------------+------------------+------+-----+---------+----------------+
> | Field            |
> Type             | Null | Key
> | Default |
> Extra          |
> +------------------+------------------+------+-----+---------+----------------+
> | dbf_UID          | int(10) unsigned |
> NO   | PRI | NULL    |
> auto_increment |
> | dbf_Vendor       |
> varchar(30)      | NO   |     |
>
> |               
> |
> | dbf_Code         |
> varchar(30)      | NO   |     |
>
> |               
> |
> | dbf_Notes        |
> text             | YES 
> |     | NULL
>
> |               
> |
> +------------------+------------------+------+-----+---------+----------------+
>
> mysql> ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID;
> Query OK, 2947 rows affected (0.05 sec)
> Records: 2947  Duplicates: 0  Warnings: 0
>
> mysql> ALTER TABLE dbt_Fruit DROP PRIMARY KEY;
> ERROR 1075 (42000): Incorrect table definition; there can be only one auto
> column and it must be defined as a key
>
> mysql> ALTER TABLE dbt_Fruit DROP PRIMARY;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '' at line 1
>
>
> Any ideas???  I am wondering if it has something to do with the fact that
> dbf_UID is a primary AND auto_increment?
>
> TIA...
>
> Nunzio
>
>
>
> ________________________________
> From: Michael Dykman <mdykman@stripped>
> To: Nunzio Daveri <nunziodaveri@stripped>
> Cc: Anirudh Sundar <sundar.anirudh@stripped>; mysql@stripped
> Sent: Tue, August 10, 2010 4:10:37 PM
> Subject: Re: Dropping ALL indexes from a database / not just a table?
>
> It's not a completely solution and will need some tweaking..  You
> might have to run the PRIMARY KEYS distinctly from the rest.
>
> - michael dykman
>
>
> On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri <nunziodaveri@stripped>
> wrote:
>> Hello Michael, thanks for the one liner.  I ran it BUT I started to get
>> errors after I ran it the first time, this is what I got the 2nd time I
>> ran
>> it (first time I ran it I had 63 rows in the query, the 2nd time I have
>> 9).
>> I ran it twice to make sure it got rid of the indexed.  I verified the
>> index
>> size dropped from 850 mb to 65 mb.
>>
>>
>> +-------------------------------------------------------------------------+
>> | concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';')
>> |
>>
>> +-------------------------------------------------------------------------+
>> | ALTER TABLE dbt_Fruits DROP INDEX
> PRIMARY;                        
> |
>> | ALTER TABLE dbt_Veggies DROP INDEX PRIMARY;
>> |
>> .
>> .
>> .
>> | ALTER TABLE dbt_Logs DROP INDEX
>>
> PRIMARY;                                    
> |
>>
>> +-------------------------------------------------------------------------+
>> 9 rows in set (0.01 sec)
>>
>> mysql> ALTER TABLE dbt_Fruits DROP INDEX PRIMARY;
>> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
>> that corresponds to your MySQL server version for the right syntax to use
>> near 'PRIMARY' at line 1
>> mysql> ALTER TABLE dbt_Logs DROP INDEX PRIMARY;
>> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
>> that corresponds to your MySQL server version for the right syntax to use
>> near 'PRIMARY' at line 1
>> mysql>
>>
>> Thanks again...
>>
>> Nunzio
>> ________________________________
>> From: Michael Dykman <mdykman@stripped>
>> To: Nunzio Daveri <nunziodaveri@stripped>
>> Cc: Anirudh Sundar <sundar.anirudh@stripped>; mysql@stripped
>> Sent: Tue, August 10, 2010 3:17:48 PM
>> Subject: Re: Dropping ALL indexes from a database / not just a table?
>>
>> This should give you a good starting point (not tested):
>>
>> select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ',
>> CONSTRAINT_NAME,';')
>> from information_schema.key_column_usage where
>> TABLE_SCHEMA='<mydatabase>';
>>
>> - md
>>
>> On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri <nunziodaveri@stripped>
>> wrote:
>>> Thanks for the feedback.  What I am trying to do is two things:
>>>
>>> 1. Remove all indexes and make the database smaller to copy and move to
>>> another
>>> prod box. Currently my indexes are in the double digit GB! Yikes ;-)
>>>
>>> 2. Remove all indexes so I can find out which ones are needed then tell
>>> mysql to
>>> recreate them and apparently it lessen data fragmentation if it starts
>>> from
>>> scratch vs. turning on and off.
>>>
>>> I was hoping to just remove all and then start from scratch so I know the
>>> data
>>> is not fragmented on the drives.
>>>
>>> Thanks again...
>>>
>>> Nunzio
>>>
>>>
>>>
>>>
>>> ________________________________
>>> From: Anirudh Sundar <sundar.anirudh@stripped>
>>> To: Nunzio Daveri <nunziodaveri@stripped>
>>> Cc: mysql@stripped
>>> Sent: Tue, August 10, 2010 1:06:41 AM
>>> Subject: Re: Dropping ALL indexes from a database / not just a table?
>>>
>>> Hello Nunzio,
>>>
>>> Instead of Dropping a index, you can disable the indexes and get the work
>>> done
>>> and re-enable them.
>>>
>>> If you are ok with this then run the below as a shell script :-
>>>
>>> MUSER="username"
>>> MPASS="password"
>>> DATABASE="dbname"
>>>
>>> for db in $DATABASE
>>> do
>>>  echo "starting disabling indexes for database -- $db"
>>> echo "----------------------------------------------------------"
>>> TABLES=`mysql -u $MUSER -p$MPASS $db -e "show tables"`
>>> for table in $TABLES
>>> do
>>> mysql -u $MUSER -p$MPASS $db -e "Alter table $table disable keys"
>>> done
>>>
>>> echo "completed disabling indexes for database -- $db"
>>> done
>>>
>>> Cheers,
>>> Anirudh Sundar
>>>
>>>
>>>
>>> On Tue, Aug 10, 2010 at 1:33 AM, Nunzio Daveri
> <nunziodaveri@stripped>
>>> wrote:
>>>
>>> Hello Gurus, is there a way / script that will let me DROP ALL the
>>> indexes
>>> in a
>>>>single database?  for example, lets say my database is call
> db_Animals,
>>>> and
>>>>inside db_Animals there are 97 tables, is there a SINGLE command or a
>>>> perl
>>>>script of some kind that can read all the MYI files, remove the .MYI from
>>>> the
>>>>file name then proceed to deleting whatever indexes it finds?  I am
> doing
>>>> this
>>>>to debug a server that seems to be slow and sluggish.  After I am
> done
>>>> deleting
>>>>I will review the slow query logs and then re-index to get the best
>>> performance?
>>>>
>>>>TIA...
>>>>
>>>>Nunzio
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdykman@stripped
>>
>>  May the Source be with you.
>>
>>
>
>
>
> --
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.
>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
Dropping ALL indexes from a database / not just a table?Nunzio Daveri9 Aug
  • Re: Dropping ALL indexes from a database / not just a table?Anirudh Sundar10 Aug
    • Re: Dropping ALL indexes from a database / not just a table?mos10 Aug
    • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
      • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman10 Aug
        • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
          • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman10 Aug
            • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
              • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman11 Aug
                • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri11 Aug