Can't restore a sql backup

[yklis]

magento version 1.9.2.3  site www.arborlab.com.au

I have been doing backups of the website and decided to test the restore of the database by creating a staging site on the shared hosting. 

I did a database dump via ssh then a database import to the staging site after creating a database and naming it staging.

During the restore I received this error

ERROR 1227 (42000) at line 823: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I contacted our hosting and received this reply. We apologize for the delayed response. 
As you are on a shared environment, we will need to further investigate into "super privileges". 
We will be in touch!

I believe the problem is some old Codisto code that is in the database that has quite a few trigger events. After contacting Codisto I was referred to this url to remove it

https://github.com/CodistoConnect/CodistoConnect-Shell

MY QUESTION IS

if I make a copy of my database via php and then use the code to remove codisto (we now use m2Epro for listing on eBay) from my working database and it breaks the website can I rename my working database and then rename my copy database to the same as the original working database. 

And will my website still be working?

The other question is are there any ideas how to restore the database without "Super Priveleges" ?

[Damian Culotta]

Hi @yklis,

Maybe you can use the linux console to replace the old trigger definer with the new database username?

For example:

cat database_backup_file.sql | grep DEFINER

The output will be something like:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_update
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_update
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_decimal_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_decimal_after_update

So now you will use this command to replace the data into the dump:

find . -type f -name "database_backup_file.sql" -exec sed -i 's/DEFINER=`root`@`localhost`/DEFINER=`new_user`@`new_host`/g' {} \;

After the command has finished you can check again with:

cat database_backup_file.sql | grep DEFINER

And now the output should be:

/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_update
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_update
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_datetime_after_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_decimal_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`new_user`@`new_host`*/ /*!50003 TRIGGER trg_catalog_category_entity_decimal_after_update

Now you can try to restore the database into the new database.

Remember that the new_user should be the same you'll use for the new database.

In that way you shouldn't have those issues you've mentioned. (I guess)

[yklis]

Hi Damien - thanks for taking the time to reply. Your solution is a little bit beyond me as I have just figured out how to use ssh to dump and reload as I couldn't restore the database via cPanel.

I believe that removing the Codisto triggers from the database will solve the problem so still wondering if a php copy of the database can be renamed and attached to the database if I crash the working database trying to remove the codisto code.