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
vijji_lakshmiAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
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
 
UmeshMySQL Principle Technical Support EngineerCommented:
Did you try?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.