Error: (1304, 'PROCEDURE etl already exists')

[SWS-5007]

Hi, When I run alembic upgrade head command, I am getting Error below.

... ... File "C:\Users\Home\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error err.raise_mysql_exception(self._data) File "C:\Users\Home\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1304, 'PROCEDURE etl already exists') [SQL: CREATE DEFINER=root@%% PROCEDURE etl(block_start INT(11), block_end INT(11), update_status INT(1)) BEGIN DECLARE idx INT; SET idx = block_start; SET @update_status = update_status; label1: WHILE idx <= block_end DO CALL etl_range(idx,idx,update_status); SET idx = idx + 1; END WHILE label1; END ] (Background on this error at: https://sqlalche.me/e/14/e3q8)

How can I solve this problem? Please help me. Best.

[arjanz]

Probably after a failed migration the procedures still exist and you will have to delete them first

[SWS-5007]

How should I delete this? I can't see any existed procedures on my local database.

As you can see here, here is no etl related database. Best

[arjanz]

etl is a procedure, not a database.. Maybe your interface only shows tables and not existing procedures.

Try executing DROP PROCEDURE etl

[SWS-5007]

So can I delete existed procedures by using this DROP PROCEDURE etl command?

[SWS-5007]

But when I run DROP PROCEDURE etl command, I am getting error too.

'DROP' is not recognized as an internal or external command, operable program or batch file.

What is wrong?

[arjanz]

You know it's a SQL command right? You should enter it in a SQL console.

Perhaps easier to drop an recreate the databases

[SWS-5007]

Oh, I see.

And then, Please check this. https://github.com/polkascan/harvester/issues/6 I didn't any solutions for this yet.

[SWS-5007]

I could solve this problem by adding IF NOT EXISTS on https://github.com/polkascan/harvester/blob/main/db/versions/861750430061_etl_procedures.py query like below.

    op.execute(f"""
        CREATE DEFINER=`{settings.DB_USERNAME}`@`%` PROCEDURE IF NOT EXISTS `etl`(`block_start` INT(11), `block_end` INT(11), `update_status` INT(1))
        BEGIN
            DECLARE `idx` INT;
            SET `idx` = `block_start`;
            SET @update_status = `update_status`;
            label1: 
            WHILE `idx` <= `block_end` DO
                CALL `etl_range`(`idx`,`idx`,`update_status`);
                SET `idx` = `idx` + 1;
            END WHILE label1;
        END
                    """)

Thanks.