CREATE ALGORITHM = UNDEFINED DEFINITION

Question

I am backing up some database from a remote server and I had a problem on the local server when trying to import this backup. I get an error on this line:

CREATE ALGORITHM=UNDEFINED DEFINER=root@ % SQL SECURITY DEFINER VIEW tematics_field AS select....

Both servers have mysql 5.5.2x. And users differ on two servers.

Answer 1

You need to put the hostname (or wildcard in this case) in single quotes:

 CREATE ALGORITHM=UNDEFINED DEFINER=root@ '%' SQL SECURITY DEFINER VIEW tematics_field AS select..... 

Answer 2

I am only trying:

 CREATE VIEW tematics_field AS select.... 

And everything works fine, and the import is well done.

Answer 3

  CREATE ALGORITHM=UNDEFINED DEFINER=root@ % SQL SECURITY DEFINER VIEW tematics_field AS select..... Please remove "ALGORITHM=UNDEFINED DEFINER=root@ % SQL SECURITY DEFINER" and keep like "CREATE VIEW tematics_field AS select..... it will work while importing or direct pasting under sql tab 

Answer 4

Error importing the database with CREATE ALGORITHM = NOT SPECIFIED IDENTIFIER error: # 1227 - Access denied - Fixed

CREATE ALGORITHM = UNDEFINED DEFINER = root @ % SQL SECURITY INVOKER VIEW ... When you import an SQL file from a local host to a remote server, if the server is shared or does not have root access to your database, a MySQL permission error will occur.

The problem is because the database you are recovering does not have the correct permissions for the VIEWS recovery database. Look at the permissions for the database for the user. They must have the permission "CREATE VIEW", "CREATE ROUTINE" and "TRIGGERS".

MySql error: # 1227 - access denied; you need (at least one of) SUPER privileges for this operation

Read the full revised article at https://www.mazzitorch.com/import-failure-create-algorithm-1227/

Answer 5

MySql error: # 1227 - Access denied; you need (at least one of) SUPER privileges for this operation

 CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY INVOKER VIEW 'inventory_stock_1' AS SELECT DISTINCT 'legacy_stock_status'.'product_id' AS 'product_id','legacy_stock_status'.'website_id' AS 'website_id','legacy_stock_status'.'stock_id' AS 'stock_id','legacy_stock_status'.'qty' AS 'quantity','legacy_stock_status'.'stock_status' AS 'is_salable','product'.'sku' AS 'sku' FROM ('cataloginventory_stock_status' 'legacy_stock_status' JOIN 'decg_catalog_product_entity' 'product' ON('legacy_stock_status'.'product_id' = 'product'.'entity_id')) ; 

Fixed Solution:

The problem is that you installed the determinant as root, which is not your current user, so you need to have the SUPER privilege . You can create a user as root in RDS and use root to run the command, or simply

 CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY INVOKER 

change to:

 CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER SQL SECURITY INVOKER 

learn more about CURRENT_USER The final SQL query looks

 CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW 'inventory_stock_1' AS SELECT DISTINCT 'legacy_stock_status'.'product_id' AS 'product_id','legacy_stock_status'.'website_id' AS 'website_id','legacy_stock_status'.'stock_id' AS 'stock_id','legacy_stock_status'.'qty' AS 'quantity','legacy_stock_status'.'stock_status' AS 'is_salable','product'.'sku' AS 'sku' FROM ('cataloginventory_stock_status' 'legacy_stock_status' JOIN 'decg_catalog_product_entity' 'product' ON('legacy_stock_status'.'product_id' = 'product'.'entity_id')) ; 

Thanks. From: MazziTorch