Error reading data from MySQL table 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Question

I am connected to the MySQL database in Python using the (MySQL Connector) Python module in jupyter notebook. I can perform operations for example creating a table or reading data from a table. In the following code, I want to create a view but before creating a view first I want to make sure that the database doesn't have the view if it has I want to drop it.

import mysql.connector as con
try:
    connection = con.connect(host = 'localhost',
                             database='mydb',
                             user='***',      # my user name is also correct
                             password='****') # my password is correct
    
    create_view_query = """
    drop view if exists chicago_public;
    create view chicago_public as
    select NAME_OF_SCHOOL as school_name,Safety_Icon as afety_rating,
    Family_Involvement_Icon as family_rating,
    Environment_Icon as environment_rating,
    Instruction_Icon as instruction_rating,
    Leaders_Icon as leaders_rating,
    Teachers_Icon as teacher_rating
    from CHICAGO_PUBLIC_SCHOOLS;
     """
    
    cursor = connection.cursor()
    cursor.execute(create_view_query)
    print("View created succussfully.")
    
except con.Error as e:
    print("Error reading data from MySQL table",e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()
        print("MySQL connection is closed")

But when I run this query from jupyter notebook I am getting the following error.

Error reading data from MySQL table 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Please help me with this how to solve this problem. Thank you in advance.

Answer 1

You have multiple queries so you need to use

cursor.execute(create_view_query,(), multi=True)

Answer 2

Use CREATE OR REPLACE to atomically replace the view.

create_view_query = """
create or replace view chicago_public as
...

So you don't need to enable multi=True for the query.