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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
UmeshSenior Principal 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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
UmeshSenior Principal 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshSenior Principal Technical Support EngineerCommented:
Did you try?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.