Support

Akeeba Solo

#26052 Can't backup Views, Triggers and Stored Procs in Secondary DB

Posted in ‘Akeeba Solo (standalone)’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

PHP version
n/a
Akeeba Solo version
n/a

Latest post by on Saturday, 08 October 2016 17:20 CDT

[email protected]
I have an Application based on Joomla that uses a Secondary data base for all the users business data. This data base has Triggers, Functions, Views and Stored Procedures that are needed by the application. I am using a Joomla backup script and I have added the secondary database to the profile. I have specifically set the "Dump Views ... " option to true. In my (limited) testing it looks like the setting is respected only in the primary database and ignored in the secondary.

nicholas
Akeeba Staff
Manager
I am afraid that this is not true. Procedures, triggers and functions will be dumped in all configured databases or none at all. Please note that there is no option which controls whether VIEWs are dumped. VIEWs are just like tables as far as MySQL is concerned. I assume you meant the "Dump PROCEDUREs, FUNCTIONs and TRIGGERs" option instead.

Most likely you are using a database user with insufficient privileges. That's why you see dumping the entities work in one database but not the other. I have run tests with 3 different database, using 3 different users, each one having access only to one specific database. So User A can't dump entities from Database B just like User B can't dump entities from Database A. Only User A can dump entities from Database A and only User B can dump entities from Database B. I ran several tests confirming that using the correct user for each database does work as intended. Moreover I confirmed that using the wrong user for each database indeed results in no entities being dumped, exactly as it should happen (because MySQL won't let you violate its security model).

Let's recap. In order for procedures, triggers and functions to be dumped all of the following MUST be true:
  • Use the Native MySQL backup engine as your Database backup engine. The reverse engineering engine does not have support for anything besides simple tables and views.
  • Dump PROCEDUREs, FUNCTIONs and TRIGGERs must be checked. Obviously :)
  • No dependency tracking must be unchecked (cleared). If you have told the backup engine to not perform dependency tracking it won't attempt to dump advanced entities which reference other tables / entities because your database would probably be impossible to restore (e.g. if the entity references a table or entity not yet created during restoration, something the dependency tracking is designed to fix).
  • The database user you are using to dump the database MUST be either the owner of the PROCEDURE / FUNCTION or have SELECT privileges to the mysql.proc table per the MySQL documentation. This is a low level security feature of MySQL and cannot be circumvented.
  • The database user you are using to dump the database MUST have the TRIGGER privilege on the table the TRIGGER is associated with per the MySQL documentation. This is a low level security feature of MySQL and cannot be circumvented.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

[email protected]
Thank you for the comprehensive response. I will review all of the above and confirm if the issue is resolved. It will take me about 24 hours before I can get to this.

System Task
system
This ticket has been automatically closed. All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!