MySQL views always work on Acquia Cloud on dumped and restored databases, provided both that you use the Acquia Cloud user interface or API to copy them and that the views are valid. The script behind the user interface strips out the DEFINER clauses, which are the root cause of these issues.
Dumps or restores on the same same server and database do not cause issues, but restoring a database either to a different server or to a different database on either the same or different server will cause issues if the Cloud UI is not used. If the views are valid, but contain an invalid SELECT statement that refers to tables that do not exist, the views won't work and will also interfere with copying or backing up the database. Note that this isn't a Cloud restriction — it's just how an invalid view works.
For the procedures on backing up your database in Acquia Cloud, see Backing up your site.
Copying a database manually
When a database with views is dumped or restored incorrectly, it can manifest itself in a manner of ways that seem unrelated to views. First, it will cause backups of the restored database to fail because there is now an invalid view in the database. Secondly, when you are logging into MySQL using the command-line tool, it will cause errors and disconnects. These issues are most commonly caused by someone manually creating a mysqldump of a database, and then restoring it to a different location without sed stripping out the DEFINER clauses.
It's easy to manually dump a database to copy or move it. Ensure that the DEFINER clauses are stripped out of the database during the dump process. This prevents broken views when the database is imported into the new site.
When copying a database containing views from one site or environment to another, you must pipe the mysqldump output through a command to strip the DEFINER clauses. For example:
mysqldump some_db | LANG=C sed -e 's|^/[*]!50001 CREATE ALGORITHM=UNDEFINED [*]/|/*!50001 CREATE */|' -e '/^[/][*]!50013 DEFINER=/d' | mysql some_other_db
If you don't do this, the database copy displays the following issues:
- If you're the mysql root user - The database copy succeeds, but the resulting target database has a views problem.
- If you're not the mysql root user - (for example, if you're a customer running
drush sql-dump | drush sql-cli) The copy fails with a permission denied error when it tries to create the view in the target database.
Do this only if you are copying between one site or environment to another, and only when views are present. On Acquia Cloud, all databases in a single environment share the same username and password, but each environment in a site has its own username and password, and environments in different sites have different usernames and passwords also.
When you're copying in an environment (such as a backup and restore to the same database) the user accounts are the same, so the original DEFINER clauses line up with the user account being used.
Working around a broken view
When you encounter a database containing a view that does not work because it was copied by the root user without removing the DEFINER clauses, use the mysql -A command-line option to have MySQL not preload information about all tables. This avoids the problem of "MySQL server has gone away" errors when you try to touch the broken views in any way.
Performance implications with MySQL views
Because views cannot be indexed, any SELECT statement issued against a MySQL view performs a full table scan, which can result in poor website performance.