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

Using Views, Triggers, Stored Procedures, and Routines

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

However, some advanced features that enable finer and more complex data manipulation, such as…

• “triggers”
• stored procedures
• routines
• and the creation of functions

… are only available on Cloud Servers.

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

Troubleshooting MySQL/MariaDB dump import issues

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

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

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

To avoid this issue, you can correct the backup file (dump.sql or dump.sql.gz) before importing it by replacing the definer definitions with CURRENT_USER. This will automatically associate the triggers and views with the current user at the time of import.

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

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 way temporary users function during the dump/restore process. No changes to the export/import process are planned in the short term, but the issue is still being evaluated on the infrastructure side.

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

• MySQL
• MariaDB