Can not find stored procedure - terminal shows it exists

Question

I'm trying to dip my toe into web app development. I am working on a simple app that allows people to sign up, create user credentials and then fill out a bucket list. Right now, I'm having trouble adding new users to the MySQL database. When I select the database I want, I use SHOW CREATE PROCEDURE and my procedure seems to print within the terminal. However, when I run my app and test signup - the browser console keeps saying :

"{"error": "(1305, u'PROCEDURE tbl_user.sp_createUser does not exist')"}.

I see it in terminal, but can't seem to get my app to find it. I have a hunch the stored procedure is in the wrong database, but I can't find any clear instructions on how to confirm which DB my procedure is in.

I've tried looking at all the databases I have. I even tried resetting my connection and making sure I could find the procedure in terminal before trying to test my app. I find it, but am unable to get my app to work.

Python App code excert:

'''python 
mysql = MySQL()

#MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'peace2you'
app.config['MYSQL_DATABASE_DB'] = 'BucketList'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

@app.route("/")
def main():
    return render_template('index.html')

@app.route("/showSignup")
def showSignup():
    return render_template('signup.html')

@app.route("/signUp",methods=['POST','GET'])

def signUp():
    try:
    # read the posted values from the UI
        _name = request.form['inputName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']


        if _name and _email and _password:

        #all good, lets call MySQL
            with closing(mysql.connect()) as conn:
                with closing(conn.cursor()) as cursor:
                    _hashed_password = generate_password_hash(_password)
                    cursor.callproc('tbl_user.sp_createUser',(_name,_email,_hashed_password))
                    data = cursor.fetchall()

                    if len(data) is 0:
                        conn.commit()
                        return json.dumps({'message':'User created successfully !'})
                    else:
                        return json.dumps({'error':str(data[0])})
        else:
            return json.dumps({'html':'<span>Enter the required fields</span>'})

    except Exception as e:
        return json.dumps({'error':str(e)})

'''

'''mysql from commandline
show create procedure `tbl_user.sp_createUser`
    -> ;
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure              | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | character_set_client | collation_connection | Database Collation |
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| tbl_user.sp_createUser | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `tbl_user.sp_createUser`(
    IN p_name VARCHAR(20),
    IN p_username VARCHAR(20),
    IN p_password VARCHAR(20)
)
BEGIN
    if ( select exists (select 1 from tbl_user where user_username = p_username) ) THEN

        select 'Username Exists !!';

    ELSE

        insert into tbl_user
        (
            user_name,
            user_username,
            user_password
        )
        values
        (
            p_name,
            p_username,
            p_password
        );

    END IF;
END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)


'''

I'm expecting the console to show 'User Created Successfully' but I keep getting :

{"error": "(1305, u'PROCEDURE tbl_user.sp_createUser does not exist')"}.

Any help or understanding you could provide would be fantastic! Thanks

Answer 1

In a comment, you mentioned that you want to create a procedure called sp_createUser in the table tbl_user. That's not how it works.

You may be thinking of triggers, which is code that belongs to a specific table, but those cannot be called like a procedure, and instead gets executed automatically when you update your table data, e.g. insert a row into your table.

A procedure just sits in a database and can be called without reference to a table. You just need to specify the database if it differs from your current database, in the form databasename.procedurename. This is what you did in your python call: you tried to execute a procedure called sp_createUser in a database tbl_user, which appearently does not exist.

In MyQSL Workbench, you used backticks to create and verify the procedure, show create procedure `tbl_user.sp_createUser`. This way, you told MySQL to take the string literally, e.g. create a procedure with a dot in the name. And you basically never want to do this.

To sum up: create the procedure using create procedure sp_createUser ... and call it using cursor.callproc('sp_createUser', ....

You can include the database name, e.g. create procedure BucketList.sp_createUser ... and/or cursor.callproc('BucketList.sp_createUser', ..., although if you do this, it might get more complicated if you want to run your application on a different database, e.g. a test installation with a different name.