Re: how to drop index if exists
Fantastic, thanks very much! Adam Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
Sorry I got carried away in my former response. When I tried this, ALTER IGNORE TABLE tbl_name DROP INDEX seems to fail if the index does not exist. The DROP INDEX statement is part of a larger script, what I want is for the script to conue to execute if this index does not exist. e.g. ALTER IGNORE TABLE table_name DROP INDEX index_name; ALTER IGNORE TABLE table_name ADD INDEX UNIQUE index_name(column_1,column_2); Thanks - Ada Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- Adam Lipscombe T: 01872 575083 M: 07957 548686 E: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
On Dec 5, 2007 7:03 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Sorry I got carried away in my former response. When I tried this, ALTER IGNORE TABLE tbl_name DROP INDEX seems to fail if the index does not exist. Right. ALTER IGNORE means that rows that violate unique indexes won't be copied to the new table during the ALTER. It has nothing to do with what you're looking for. There is no statement that does what you're looking for, as far as I know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to drop index if exists
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
I actually suggested that last night, and thought better of it because
the alter ignore was so much simpler...
On Dec 3, 2007 11:18 AM, Rolando Edwards [EMAIL PROTECTED] wrote:
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your
goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to drop index if exists
Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
