Support

Mandatory information about my setup:

Have I read the related troubleshooter articles above before posting (which pages?)? Yes
Have I searched the tickets before posting? Yes
Have I read the documentation before posting (which pages?)? Yes
Joomla! version: 2.5.11
PHP version: 5.3.13
MySQL version: 5.5.24
Host: (optional, but it helps us help you)
Akeeba Backup version: 3.7.7

EXTREMELY IMPORTANT: Please attach a ZIP file containing your Akeeba Backup log file in order for us to help you with any backup or restoration issue. If the file is over 2Mb, please upload it on your server and post a link to it.

Description of my issue:
When restoring backup that includes database functions with embedded semi-colons (';'), the database restore returns an exception.

An example function (that I extracted from the backup archive) is:

CREATE FUNCTION `fn_Maintain_Account_mail_fee_code`(primary_remote_address_id int) RETURNS varchar(4) CHARSET latin1 begin declare other_address_country_code varchar(2); declare new_mail_fee_code varchar(4); if primary_remote_address_id <> 0 and primary_remote_address_id is not NULL then set other_address_country_code = (select country_code from other_address oa where oa.other_address_id = primary_remote_address_id ); case when other_address_country_code = 'US' or other_address_country_code = '' then set new_mail_fee_code = NULL; when other_address_country_code = 'CA' then set new_mail_fee_code = 'CAMA'; else set new_mail_fee_code = 'FRGN'; end case; else set new_mail_fee_code = NULL; end if; return new_mail_fee_code; end;;

Note the 2 semi-colons at the end of the function definition. To get these to properly restore, I had to redefine the statement delimiter in mysql as:

delimiter //
CREATE FUNCTION `fn_Maintain_Account_mail_fee_code`(primary_remote_address_id int) RETURNS varchar(4) CHARSET latin1 begin declare other_address_country_code varchar(2); declare new_mail_fee_code varchar(4); if primary_remote_address_id <> 0 and primary_remote_address_id is not NULL then set other_address_country_code = (select country_code from other_address oa where oa.other_address_id = primary_remote_address_id ); case when other_address_country_code = 'US' or other_address_country_code = '' then set new_mail_fee_code = NULL; when other_address_country_code = 'CA' then set new_mail_fee_code = 'CAMA'; else set new_mail_fee_code = 'FRGN'; end case; else set new_mail_fee_code = NULL; end if; return new_mail_fee_code; end//
delimiter ;

[BTW - I hit return after entering subject for this ticket and was surprised when it was created without my adding any issue information. Sorry!]

Nicholas - Thank you for you quick response!

I installed svn1953 on a test site and successfully created a backup. The restore to a new site failed with a different exception.

I tried submitting the information (twice) in this reply only to trigger your site protection (I assume due to the included SQL statements.) I have created and uploaded an attachment with the information.

I have installed svn1955 and the "Backup Now" button on the "Start a new backup" page does not work. The page is displayed, but Joomla and firebug report exceptions:

Joomla:

JError:1

Options for callback not valid


Call stack
# Function Location
1 JAdministrator->dispatch() JROOT\administrator\index.php:46
2 JComponentHelper::renderComponent() JROOT\administrator\includes\application.php:153
3 JComponentHelper::executeComponent() JROOT\libraries\joomla\application\component\helper.php:351
4 require_once() JROOT\libraries\joomla\application\component\helper.php:383
5 AkeebaDispatcher->dispatch() JROOT\administrator\components\com_akeeba\akeeba.php:40
6 FOFDispatcher->dispatch() JROOT\administrator\components\com_akeeba\dispatcher.php:140
7 AkeebaControllerBackup->execute() JROOT\libraries\fof\dispatcher\dispatcher.php:322
8 FOFController->execute() JROOT\administrator\components\com_akeeba\controllers\backup.php:23
9 AkeebaControllerBackup->add() JROOT\libraries\fof\controller\controller.php:873
10 FOFController->display() JROOT\administrator\components\com_akeeba\controllers\backup.php:28
11 FOFViewHtml->display() JROOT\libraries\fof\controller\controller.php:967
12 FOFView->display() JROOT\libraries\fof\view\html.php:123
13 JError::setErrorHandling() JROOT\libraries\fof\view\view.php:445
14 JError::raiseError() JROOT\libraries\joomla\error\error.php:375
15 JError::raise() JROOT\libraries\joomla\error\error.php:251

Firebug:

TypeError: $.browser is undefined
...pacity=helper.parent.css("opacity");this.tooltipText=this.title;$(this).removeAt...
gui-he...4ea9513 (line 29)

TypeError: $.manageAjax is undefined
var akeeba_ajax_manager = $.manageAjax.create('akeeba_ajax_profile', {
akeeba...4ea9513 (line 2342

hmmm...getting the same errors with svn1956.

[edit] Just to be clear, the same Joomla and firebug error reports. I can not backup.

I restarted wamp and exited browsers. I am trying to install, but it takes me to the "Start a new backup" page and then can't get any further. ...button does not work...

I have found the file in the zip, but not on the site. Should I try just placing the file into the directory?

That worked. I now have a backup. Testing the restore...

It seems to work for the CMS database (has 3 triggers), but the secondary database is failing on a create view? Here is the message:

Database error processing line 0

Database server error reply:
ErrNo #0

SQL=CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `property_ownership` AS select ...

using root account on local system. has all privs...

I think there is a cockpit error in what I did. It doesn't make any sense, because there are no tables created in the database and the view is created in the last of the 50 files.

I have just blown-away the directory tree and am starting the restore from scratch.

OK. Well it is, at least, getting an 'expected' error on the second database:

Database error processing line 27

Database server error reply:
ErrNo #0

SQL=DELIMITER //

I still don't understand it though. The CMS database has 3 triggers in it which have been successfully created. It seems strange that it fails on this statement in the second database, but did not in the first?

I think I found why it fails on my second database and not the first. The first (CMS) has only triggers that have a single statement (in attachment so it passes security filters)...and these triggers are created successfully. To test this, I modified the last SQL file of the cms database restore to include a few function definitions. That fails to restore with the same error as above on the delimiter:

Database server error reply:
ErrNo #0

SQL=DELIMITER //

I think I also understand why I got the view failure reported above. I failed to capture the exception before dismissing the message. When I re-ran the restore to get the exception, it said that it failed on the view, but I think it really failed on the next line, which was the function creation.

Sorry for not being clear. The trigger in the attachment succeeds. It is the create function statements that fail.

Not sure if it helps, but this link suggests that the DELIMITER doesn't work from the php interfaces.

http://stackoverflow.com/questions/14569528/execute-mysql-create-function-statement-with-php

FWIW

I think the (original) issue has to do with whether the function is already defined in the target database. To test, I reverted to 3.7.7, created a new backup and restored to unique database names. That correctly creates the functions. Restore the backup again using the same database and it fails on the first function definition.

If I create the same function twice in the same database using the mysql client, I get an expected exception - "ERROR 1304 (42000): FUNCTION fn_get_pog_configuration already exists". If I try to create a table twice in the same database, I get an expected exception - "ERROR 1050 (42S01): Table 'pog_configuration' already exists".

I don't understand is why the create table statements in the restore seem to replace the table where the create function statements fail. Are you trapping the table 'already exists' exceptions and not the one for the functions?

I swear I was using DROP, but now I can't reproduce. Sorry to waste your time!