Migrating Pimcore databases

Paul January 5th 2015

Migrating databases quickly in MySQL can be a pain when there's views involved, especially when you're copying data from one server to another, and additionally each server has it's own database users and therefore definers of views.

 

In Pimcore's case, theres a view for every object class created and therfore if you've done a quick mysqldump of the database you'll most likely have 'DEFINER' issues when you come to import the tables again. These definers look a little bit like the following:

 

/*!50014 DEFINER=CURRENT_USER */

To get round this, I run the following line in the console, replacing the DEFINER with the CURRENT USER.

$ mysqldump pimcore_database > pimcore_database_nonportable.sql $ sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' pimcore_database_nonportable.sql > pimcore_database.sql

thanks to the original src: http://xplus3.net/2013/10/10/remove-definer-clause-mysqldump/ - I've put this page up here as an archive to this very useful snippet.

Author Paul Technical Director

Technical Director & Co-founder of Gather. Paul is a Manchester born computer scientist with broad full stack web & mobile application skillset, he has helped shaped Gather's technology, and build many large scale web applications.