This guide details the features available for managing relational databases on Infomaniak hosting.

Use of Views, Triggers, Stored Procedures, and Routines

Regarding the management of relational databases, the view functionality ("views") is available by default, allowing users to create views to simplify data management and presentation.

However, some advanced features that allow for more precise and complex data manipulation, such as…

• “triggers (triggers)
• stored procedures ("stored procedures")
• routines
• and the creation of functions

… are only available on Managed Cloud Servers.

This restriction is mainly due to potential risks to the stability of the infrastructure. Poor configuration or excessive use of these features could create infinite loops or significant overloads, affecting not only the performance of the affected server but also the experience of all clients hosted on the same infrastructure.

Resolving a MySQL/MariaDB dump import issue

When exporting and then re-importing a MySQL or MariaDB database via the Infomaniak hosting interface, it may happen that the operation fails due to errors related to the DEFINER of triggers or views. This occurs when the database objects were created with a specific user (called definer) who no longer exists at the time of import.

Concretely, the export and import process uses a temporary user, used only during these operations. After this user is deleted, the views or triggers defined with this account as DEFINER become invalid, causing errors of the type:

General error: 1449 The user specified as a definer ('xxxx_temp_1'@'%') does not exist

To avoid this problem, it is possible to correct the backup file (dump.sql or dump.sql.gz) before importing it by replacing the definer definitions with CURRENT_USER. This automatically attaches the triggers and views to the current user at the time of import.

Here is an example of a command to modify the dump before import:

sed -E 's/DEFINER=`[^`][^`]*`@`[^`][^`][^`]*`/DEFINER=CURRENT_USER/g' dump.sql > dump-corrected.sql

Once this replacement is done, the corrected file can be imported normally via the Infomaniak Manager. This behavior is known and related to the operation of temporary users during dump/restore. No changes to the export/import process are planned in the short term, but the subject remains under evaluation on the infrastructure side.

For more information about the CURRENT_USER variable, refer to the official documentation of:

• MySQL
• MariaDB