Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

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

Hi, iam working on mysql 5.1
i am writing a procedure to drop indexes.
my procedure is as below.

CREATE PROCEDURE dropindexes2()

BEGIN

declare viewname varchar(80);

declare NO_MORE_ROWS BOOLEAN DEFAULT FALSE;

 

 

  DECLARE cursor1 cursor  FOR

  SELECT INDEX_NAME FROM information_schema.STATISTICS S where table_schema=(select database()) and index_name not like'PRIMARY' and index_name not like '%_FK%' and index_name not like'%_unique%';

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS=true;

 

                        OPEN cursor1;

                        cursor_loop:LOOP

                            FETCH cursor1 INTO viewname;

                            IF NO_MORE_ROWS THEN

                                     LEAVE cursor_loop;

                            END IF;

 

                          SET @tmp_sql= CONCAT("DROP index ",viewname);

                          PREPARE s1 FROM @tmp_sql;

                          EXECUTE s1;

                          DEALLOCATE PREPARE s1;

             

                        END LOOP cursor_loop;

                        CLOSE cursor1;

 

        SET NO_MORE_ROWS=false;

END

when i call this procedure as

call dropindexes2();

i am getiing
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

please let me know where iam doing worng
rgds,
vijji
0
vijji_lakshmi
Asked:
vijji_lakshmi
1 Solution
 
cr4ck3rj4ckCommented:
Hi there,

It may be too simple to be true, but could it be a missing ; after END?

Give it a try,
CJ
0
 
kuknoCommented:
Hi,

I do see two things:

1.) missing ";" after last END
2.) You used double quotes for the CONCAT command, while it should be single quotes.

Regards
Kurt

0
 
UmeshCommented:
Try this...
DELIMITER |
 
CREATE PROCEDURE dropindexes2()
 
BEGIN
 
declare viewname varchar(80);
 
declare NO_MORE_ROWS BOOLEAN DEFAULT FALSE;
 
 
 
 
 
  DECLARE cursor1 cursor  FOR
 
  SELECT INDEX_NAME FROM information_schema.STATISTICS S where table_schema=(select database()) and index_name not like'PRIMARY' and index_name not like '%_FK%' and index_name not like'%_unique%';
 
 
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS=true;
 
 
 
                        OPEN cursor1;
 
                        cursor_loop:LOOP
 
                            FETCH cursor1 INTO viewname;
 
                            IF NO_MORE_ROWS THEN
 
                                     LEAVE cursor_loop;
 
                            END IF;
 
 
 
                          SET @tmp_sql= CONCAT("DROP index ",viewname);
 
                          PREPARE s1 FROM @tmp_sql;
 
                          EXECUTE s1;
 
                          DEALLOCATE PREPARE s1;
 
             
 
                        END LOOP cursor_loop;
 
                        CLOSE cursor1;
 
 
 
        SET NO_MORE_ROWS=false;
 
END;
 
|
 
call dropindexes2();

Open in new window

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
vijji_lakshmiAuthor Commented:
Hi all,
 i tried all your suggested solution but still the same problem.
i executed the same script in the database where there are no indexes.
there it is not throwing any error.
but if i execute the same procedure where there are indexes.
there it is throwing error. please help me in finding the solution for this.
rgds,
vijji
0
 
UmeshCommented:
Ok.. this works..
DELIMITER |
 
CREATE PROCEDURE dropindexes2()
 
BEGIN
 
declare viewname varchar(80);
declare tab_name varchar(100);
 
declare NO_MORE_ROWS BOOLEAN DEFAULT FALSE;
 
 
 
 
 
  DECLARE cursor1 cursor  FOR
 
  SELECT INDEX_NAME,TABLE_NAME FROM information_schema.STATISTICS S where table_schema=(select database()) and index_name not like'PRIMARY' and index_name not like '%_FK%' and index_name not like'%_unique%';
 
 
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS=true;
 
 
 
                        OPEN cursor1;
 
                        cursor_loop:LOOP
 
                            FETCH cursor1 INTO viewname,tab_name;
 
                            IF NO_MORE_ROWS THEN
 
                                     LEAVE cursor_loop;
 
                            END IF;
 
 
 
                          SET @tmp_sql= CONCAT("ALTER TABLE ",tab_name," DROP INDEX " ,viewname);
 
                          PREPARE s1 FROM @tmp_sql;
 
                          EXECUTE s1;
 
                          DEALLOCATE PREPARE s1;
 
             
 
                        END LOOP cursor_loop;
 
                        CLOSE cursor1;
 
 
 
        SET NO_MORE_ROWS=false;
 
END;
 
|
 
 
call dropindexes2();

Open in new window

0
 
UmeshCommented:
Did you try?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now