GPMSWiki/DeveloperDocumentation/MigrationHelp/CompatibilityIssues

Synopsis

This page lists some new compatibility issues with MySQL and other backend software. This is important, read it /!\

MySQL

Views

MySQL and most other database engines allow to create views within a database model. Views look like normal tables with columns but their entries can be result of a more complex SQL query, or stored procedures. they are used to simplify and and improve the efficiency of the database.

In MySQL, there are two modes of security when invoking a View, invoker and creator (default). When you use the default creator mysql security to create the View, the query will be invoked using the grants of the account who created the database. When using invoker, the grants of the actual mysql account are used. If root created the database, everything is fine. If however the account was a real user account (an Admin or Chief for ex.) and his or her account gets 'promoted' into a role account, then the grants vanish and the view is inaccessible for the whole project. This can happen if the Admin who created the database or added the view before the migration had a normal account, but now has a role-account.

Therefore, it is important to (re-)define the Views using the following Create View syntax (see the MySQL documentation for details)

CREATE SQL SECURITY INVOKER VIEW YourViewsName AS
SELECT <Select definition>...