Hello Jackson,

I am sorry for the delay of this reply.

Regarding your issue, most likely there are some mis-configuration from your MySQL server which prevented you to execute create function statement in MySQL. Can you contact your hosting provider and request them to execute the below statement? This statement required MySQL root user to execute.

mysql_upgrade -u root -p

For more information about this issue, please see http://mattiasgeniar.be/2012/06/25/mysql-show-function-status-where-db-name-cannot-load-from-mysql-proc-the-table-is-probably-corrupted/

Please advise.

Hi Sam,

That's what I suspected earlier, so I went ahead and did what you suggested and everything went well.

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

This installation of MySQL is already upgraded to 5.6.16, use --force if you still need to run mysql_upgrade

I got that, running it again just to show you that I already run mysql_upgrade

So, I tried to install v1.2.17 again I'm getting the same Failed error on Initializing Database Stored Functions.

Any other tip?

Thanks!

Jackson

hey jackson,
if the upgrade still fails you can try to use the "full" package, available in your dashboard, and install it via the extensions manager.
others that reported the same issue were able to install 1.2.17 just fine with this package.

and of course if everything goes wrong just let us know and we can then perform the upgrade for you on the site.

Hi Alex,

Yes, I tried that too I got the same thing.

Can you try doing it for me please? And if you ever get it to work, please let me know how you did it I will be moving the live site to the same server as the staging and I don't want to have this kind of issue once the live site is in the same server as the staging right now.

Thanks,

Jackson

Hi Jackson,

I am sorry for the delay of this reply.

I've checked your staging site and seems like the db user for your Joomla do not have the required privilege to create stored function. Below are the error I received when attempt to create the stored function manually.

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

You might want to take a look at this page http://dev.mysql.com/doc/refman/5.0/en/stored-routines-privileges.html

Once you've granted the required privilege, you will need to re-install EasySocial again
Hope this help and have a nice day

I am having the same problem and I need help ASAP. See Screenshot. I updated my EasySocial 1.2.16 to 1.2.17 and cannot pass beyond the "Initializing Database Stored Functions. I need Help please. I then go back to my originally installed 1.2.16 easysocial and all files are uninstalled and I did not initiate the uninstallation. Please let me know if you would like for me to provide you access to my site so that you can take a look. As for now my Easysocial is no longer installed on my site since I made the upgrade which failed.

I re-installed version 1.2.16 and all seems to be well. I think that updating to 1.2.17 is the issue here and I am not sure why that is the case.

Hi Olguy Charles,

I am sorry for the delay of this reply.

Regarding your issue with upgrading to 1.2.17, can you make sure your db user has granted the privilege to create / execute db stored function? If your db user already granted the necessary privilege and the upgrade process still failed at 'Initializing Database Stored Functions', then most likely you will need to execute below command from your mysql.

mysql_upgrade -u root -p

Hope this help and have a nice day

Hi Sam,

Thanks for that error, it lead me me to where to find the issue.

Anyways, I am using a cPanel account on this and I already gave my DB user all privileges to the DB including CREATE ROUTINE. I am getting hits from google of people having the same issue with cPanel as well.

Can you do me a favor and paste the Stored Routine so I can manually run it in CLI to find what's causing it? Or you can just point me to where in the installation file can I find it. If it cannot be run because of parameters, please please can you create a simple script that pretty much do the same as the Stored Routine in ES so I can trace. Thank a lot!

Jackson

Hello LAC Infosys,

I am really sorry for the delay of this reply as it is a weekend for us here.
May i know did your database user already granted the required privilege? If yes, can you run this as Sam mentioned in previous reply and see what you get?

mysql_upgrade -u root -p

Please advise.

Arlex,

Like I said I already granted ALL PREVILEGES in cPanel to the DB user and I'm still getting an error and I also did what Sam suggested.

I am asking you guys a favor if you can provide me the Stored Routine command that is used in the installation so I can recreate the ERROR in CLI. Why do I need it? Because I am working with cPanel tech support to resolve this issue, and I cannot show them where the issue is coming from without running the STORED FUNCTION in CLI. Running ES installer does not help cPanel Tech Support, they need to run the SQL command in CLI to see the error themselves. That is also the reason why I asked Sam that if the Stored Function needs parameters to run then to please give me a simple SQL command that somewhat do what the Store Function to just to see where the error is coming from.

If I resolve this, this may help other people as well, and I think most people who are having this issue is using cPanel. If you can do me a favor and provide me the code I am asking instead of asking me what Sam already asked, that would help me a lot.

I hope I am explaining things right.

Thank you!

Jackson

Hi, im having the same issues installing 1.2.17 , it fails while updating the database, i also have granted permissions for the db user, i will have to downgrade to the previous version, i was wondering ,is there a way to download 1.2.16 full package from you, since the last update i did was from the admin panel and i don't have the full package zip to install anymore.

regards
-a

Hi Alfonso,

You can download the FULL package zip under your account (stackideas.com) Dashboard if you click on Other Packages.

Are you using cPanel for your hosting account?

Thanks,

Jackson

Hi Jackson,

I am sorry for the delay of this reply and the confusion. EasySocial 1.2.17 required below two stored functions to be created in your MySQL:

1. es_isfriend

CREATE FUNCTION `es_isfriend` (p_source int, p_target int)

RETURNS INT DETERMINISTIC

BEGIN

    DECLARE cnt INT;

    select count(1) into cnt from `jos_social_friends` where ( `actor_id` = p_source and `target_id` = p_target) OR (`target_id` = p_source and `actor_id` = p_target) and `state` = 1;

    RETURN cnt;

END

If you are running the above with phpmyadmin or some other mysql client, you might need to specify the delimiter. e.g.

DELIMITER $$

CREATE FUNCTION `es_isfriend` (p_source int, p_target int)

RETURNS INT DETERMINISTIC

BEGIN

    DECLARE cnt INT;

    select count(1) into cnt from `jos_social_friends` where ( `actor_id` = p_source and `target_id` = p_target) OR (`target_id` = p_source and `actor_id` = p_target) and `state` = 1;

    RETURN cnt;

END

$$

DELIMITER ;

2. es_hasmutualfriend

CREATE FUNCTION `es_hasmutualfriend` (p_source int, p_target int)

RETURNS INT DETERMINISTIC

BEGIN

    DECLARE cnt INT;

    select count(1) into cnt from (

        SELECT ( CASE f2.`actor_id` WHEN CASE f1.`actor_id` WHEN p_source THEN f1.`target_id` ELSE f1.`actor_id` END THEN f2.`target_id` ELSE f2.`actor_id` END ) as fid

        FROM    `jos_social_friends` f1

        JOIN    `jos_social_friends` f2

        ON      f2.`actor_id` = CASE f1.`actor_id` WHEN p_source THEN f1.`target_id` ELSE f1.`actor_id` END

                OR f2.`target_id` = CASE f1.`actor_id` WHEN p_source THEN f1.target_id ELSE f1.`actor_id` END

        WHERE   (f1.`actor_id` = p_source OR f1.`target_id` = p_source)

                AND f1.`state` = 1

                AND f2.`state` = 1

                AND NOT (f1.`actor_id`, f1.`target_id`) = (f2.`actor_id`, f2.`target_id`) ) as ff where ff.`fid` = p_target;

    RETURN cnt;

END

Same thing, you might need to add the delimiter to above statement. Also, remember to replace jos_ to your own Joomla db table prefix.

Once you've successfully created the above two stored function, please go to EasySocial backend under maintenance tab and run the below two scripts ( you might need to filter the version to 1.2.17 )

1. Sync cluster access in stream table.
2. Sync privacy access in stream table.

Let me know if the above fix your issues or not.
Hope this help and have a nice day

Hi Alfonso.

I am really sorry for the delay of this reply.

Regarding your issue, can you pass me your Joomla backend admin access and your phpmyadmin access so that I can re-install EasySocial 1.2.17 for you?

Hi Sam and Jackson,

im using cPannel from the ipage website, i managed to download the latest Full package from the dashboard, alto not the older/previous versions.

Sam find all the info on the site details, let me know if everything works, Thank you !

ps. i really did try with the Db as you said, i just didn't manage to make it work..
regards
-a

i have the same Problem

Hi Sam,

I am getting an error running the first script at:

 DECLARE cnt INT;

http://screencast.com/t/uix64xXL

Thanks for posting the create function script, I will be able to ask cPanel Tech support to figure out what this is happening. If you noticed most of people that are having this issue is using cPanel.

Thanks,

Jackson

Hi Sam and all,

Thank you so much Sam for posting that MySQL code. I didn't use it in CLI since it returns an error but being able to see the script I was able to create a test CREATE FUNCTION to test out mysql in CLI and I'm getting this error:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Our cPanel server is running MySQL v5.6.16 and I don't know if this is a bug in this version of MySQL or changes in the feature. So I tried to enable MySQL System Variable safe log_bin_trust_function_creators and everything just worked. I checked our our live website which runs MySQL v5.1.x and log_bin_trust_function_creators is not enabled and it still work.

Reading further, I come across this MySQL documentation:

http://dev.mysql.com/doc/refman/5.6/en/stored-programs-logging.html

It says:

 When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.



By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:



ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

And that is my exact error, Sam, would you consider checking your CREATE FUNCTION declaration and test it out? I'm willing to test it with you if you like.

I'm still waiting for cPanel Tech Support reply regarding enabling MySQL system variable. As for other people who are using shared hosting, I don't think their hosting provider would enable the system variable since it will add an overhead to their servers.

Thanks,

Jackson

Hi Sam,

Ok I got what's the culprit that is causing the issue with creating stored function in MySQL using cPanel it was the:

log-bin = mysql-bin

in my.cnf, for some reason it is enabled. I turned off log-bin, restarted MySQL and error is gone but I would still suggest to change the CREATE FUNCTION script to include: DETERMINISTIC and READS SQL DATA as the documentation says in the link above for those who are using shared hosting, just to test if that solves the issue. It so happened that I own the entire server so I can play around with the settings. Like I said Sam, I am willing to test this out with you, just let me know

Thank you!

Jackson

Hi Alfonso,

I am sorry for the delay of this reply and thanks for the access info to your site

I've tried running the create function sql with your phpMyAdmin but it seems like your phpMyAdmin doesn't support the DELIMITER command Please see below link for more information about this DELIMITER command.

http://stackoverflow.com/questions/9017269/mysql-delimiter-syntax-error

Anyway, can you check with your web-hosting provider and see if your db user has granted the SUPER privilege? If not, your db user will need to grant with SUPER privilege in order to execute the create function or drop function statement.

Also, is there a way for me to connect directly to your MySQL database using other MySQL client since your phpMyAdmin is not supporting the DELIMITER command. Im using Sequel Pro program so if I can remotely access to your MySQL db, I might be able to create the stored function for you

Please advise.

Ralf Riebe wrote:

i have the same Problem

Hi Rafl, can you pass me Joomla backend admin access, your ftp access and your phpMyAdmin access so that I can check your issue?
Please advise.

Hi Jackson,

Thanks for the heads up. Your finding does give us a big help

Anyway, if you don't mind, in your staging server, can you re-enable back the binary log and then try the below create function statement?

DELIMITER $$

CREATE FUNCTION `es_isfriend_test` (p_source int, p_target int) 

RETURNS INT 

DETERMINISTIC 

READS SQL DATA

BEGIN

    DECLARE cnt INT;

    select count(1) into cnt from `jos_social_friends` where ( `actor_id` = p_source and `target_id` = p_target) OR (`target_id` = p_source and `actor_id` = p_target) and `state` = 1;

    RETURN cnt;

END $$

DELIMITER ;

The above will should create a new stored function called 'es_isfriend_test'. After the test, you may drop this function
And, remember to replace the jos_ to your Joomla db table prefix.

Thanks again

Hi Sam,

Confirmed! with log-bin enabled the new script does not return an error anymore, this is via CLI.

And I just tried to upgrade v1.2.17 t0 v1.2.18 and I got "Initializing Database Stored Functions - Failed", so if you point me to which file to modify and add this new modification you created I will gladly test it or better yet send me a test package to my account email address.

Thanks,

Jackson

Hi Jackson,

Thanks for the heads up and glad to know the new script worked

so if you point me to which file to modify and add this new modification you created I will gladly test it

This will be a little bit tricky because the stored function creation script will get deleted after the installation. So in order to test again, I will need to repackage the 1.2.18 installer and include the new scripts. Can i have your email address so that I can send you the repackaged version? The installer is about 15mb so your email account must be able to accept large file

Please advise.

Hi Sam,

My email address is in this account. And I think my mailbox can manage 15mb

Thanks,

Jackson

Hi Jackson,

Thanks. I've sent the installer of re-packaged version to your email address
Let me know if this installer work for your or not

Thanks again for your effort and your offer to help on testing this out
Have a nice day

Hi Jackson,

Look like the email didn't get through. I receive this message:

The error that the other server returned was:
551 Message contains ZIP file that is too deep (4) (Mode: normal)

Please advise.

Hi Sam,

Hmmm.. can you try the email I added to Optional Info in Site Details. Google can take 25Mb file attachment.

Thanks,
Jackson

Hi Jackson,

Sure, I've sent another email to your mentioned email address. Let see if this time my message reach you or not
Thanks again.

Our provider does not support stored procedures. What to do?

Андрей Чесноков wrote:

Our provider does not support stored procedures. What to do?

Hello,

Em, the one we need is actually stored function. But not sure if stored function and stored procedure required the same privilege or not.

Anyway, maybe for now you want to stay with 1.2.16 first as we are now looking for a solution to address this issue. We might want to remove the use of stored function since quite a lot of users having issue installing stored function on their hosting

Where can i get 1.2.16

Hi Sam,

I just tested the file that you provided and unfortunately, it failed So I setup a sandbox for you so you can test it yourself if you like.

Thanks,

Jackson

Hi Jackson,

Thanks for setting up a sandbox for me. Look like is still failed at 'Initializing Database Stored Functions'. Okay, can you send me your cPanel access so that I can further investigate this issue?

Thank and have a nice day

Андрей Чесноков wrote:

Where can i get 1.2.16

Hello,

Can you create another thread and pass me your Joomla backend admin access and your FTP account and I will install 1.2.16 for you
Please advise.

Hope this help and have a nice day

Hi Sam,

Check out Site Details.

Jsandackson

Hi Jackson,

Thanks for the cPanel details. I've tried running the function creation script via phpMyAdmin and I hit this error:

#1419 - You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Look like if your MySQL enabled the binary log, then your db user must have the SUPER privilege granted in order to execute the create function statement. Please see here for more information:

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

Please advise.

Hope this help and have and have a nice day

Hi Sam,

Thanks for the docs, MySQL documentation can be vague it just explains briefly what it does and give you choices but never explains best practices.

Looks like I made a mistake when I run your script in CLI I didn't realize I was using root so definitely MySQL allowed me to run it without errors.

So I turned ON log_bin_trust_function_creators variable, based on this:

http://serverfault.com/questions/55752/mysql-super-privilege-vs-log-bin-trust-function-creators

it is safer than SUPER PRIVILEGES in a shared hosting server, Amazon RDS allows that variable, so I guess it is safer

http://stackoverflow.com/questions/8919907/can-i-create-trigger-in-an-rds-db

Please try again.

Thanks,

Jackson

Sam wrote:

Hi Alfonso,

I am sorry for the delay of this reply and thanks for the access info to your site

I've tried running the create function sql with your phpMyAdmin but it seems like your phpMyAdmin doesn't support the DELIMITER command Please see below link for more information about this DELIMITER command.

http://stackoverflow.com/questions/9017269/mysql-delimiter-syntax-error

Anyway, can you check with your web-hosting provider and see if your db user has granted the SUPER privilege? If not, your db user will need to grant with SUPER privilege in order to execute the create function or drop function statement.

Also, is there a way for me to connect directly to your MySQL database using other MySQL client since your phpMyAdmin is not supporting the DELIMITER command. Im using Sequel Pro program so if I can remotely access to your MySQL db, I might be able to create the stored function for you

Please advise.

Thank you Sam,

im in contact with my web hosting trying to find a solution for the db.

I noticed that you installed v 1.2.18 on the website, (which is down at the moment..) do you think i will be able to update versions from now on ? or you managed to install it "manually" and i still will need to fix my DB ?
Would it be possible to download 1.2.16 full package from some where so to restore the site in the meantime?

Thanks again for the help .

regards
-a

LAC Infosys wrote:

Hi Sam,

Thanks for the docs, MySQL documentation can be vague it just explains briefly what it does and give you choices but never explains best practices.

Looks like I made a mistake when I run your script in CLI I didn't realize I was using root so definitely MySQL allowed me to run it without errors.

So I turned ON log_bin_trust_function_creators variable, based on this:

http://serverfault.com/questions/55752/mysql-super-privilege-vs-log-bin-trust-function-creators

it is safer than SUPER PRIVILEGES in a shared hosting server, Amazon RDS allows that variable, so I guess it is safer

http://stackoverflow.com/questions/8919907/can-i-create-trigger-in-an-rds-db

Please try again.

Thanks,

Jackson

Hi Jackson,

Thanks for the heads up. Yes, enabled log_bin_trust_function_creators fixed the stored function creation issue. I can now install EasySocial without hitting this issue. Please see http://screencast.com/t/DYVRpxSNy6He

Thank you for your time and your effort to go through this with me. I really appreciate your helps on this matter!

Thanks again and have a nice day

Hi Alfonso,

I am sorry for the delay of this reply.

im in contact with my web hosting trying to find a solution for the db.

I noticed that you installed v 1.2.18 on the website, (which is down at the moment..) do you think i will be able to update versions from now on ? or you managed to install it "manually" and i still will need to fix my DB ?
Would it be possible to download 1.2.16 full package from some where so to restore the site in the meantime?

Thanks again for the help .

I am sorry but currently you can't download the older version of EasySocial from our site
And I am sorry but the installation of 1.2.18 is not completed

If you want, I can install 1.2.16 for you. I will need your Joomla backend admin access so that I can install EasySocial for you

Please advise.

Hi Sam,

yes please, since otherwise the website is down.

find the site details in the reply.

regards

-a

Hi Sam,
this is the response from my hosting support :
********************************************************************************************************************************************************
Our hosting is a shared hosting and you cannot create and drop tables in database of shared hosting account. .
It is not possible to connect directly to my MySQL database using other MySQL client.

********************************************************************************************************************************************************

No solution then other then going back to a previous verison?

regards
-a

Hello Alfonso,

I am really sorry for the delay of this reply as I am away from work for few days

Thanks for the access detail and we've installed ES 1.2.16 on your site and now your site is back online

Anyway, for your information, we've decided to remove the MySQL stored function usage from EasySocial and we are now in the stage of fine tuning the SQL performance. These changes will most likely added into 1.2.19 so maybe you should wait for this version instead

Again, I apologise for any inconvenience that may have caused to you
Hope this help and have a nice day

Thank you Sam! is back up and running, will definitely wait for 1.2.19 !

no worries ,and thank you for the prompt support as usual!!


regards
-a

Hi Alfonso,

You are welcome and thank you for your patience and your understanding
Have a nice day

Anyway, for your information, we've decided to remove the MySQL stored function usage from EasySocial and we are now in the stage of fine tuning the SQL performance. These changes will most likely added into 1.2.19 so maybe you should wait for this version instead

Sam, Mark & Team,

I appreciate you guys are thinking of those customers that are using your product on a shared hosting. When I first saw that Stored Procedure error in cPanel updating to v1.2.17, told myself, finally, a Joomla component that utilizes MySQL power rather than doing all the processes in PhP side.

I personally have high hopes and expectation for EasySocial and hoping again that not because there are customers that have issues with more efficient code it would stop you guys from moving ES into having better code in the future.

Thanks,

Jackson

Hello Jackson,

Yeah, we totally understand you here and I too wish that that hosting providers step up to their game and start allowing stored functions but the number of support tickets we received are just too many and many hosting providers are reluctant to allow this

Hi Mark,

I don't think hosting companies will ever change their hosting setup, it is just crazy how they kill each other with their cheap hosting prizes. It is like, you want it cheap, here's what you get and live with it. But I don't think EasySocial or any of your product has to be that way or Joomla in a broader perspective, don't you think? It is nice to hear that Joomla or ES can run my software in a minimal setup but what are being sacrificed?

You guys have just made EasySocial, if not the best Joomla component for Community building, a way much better product than any other available in JED in my opinion. Not to mention your support model. I just hate thinking that because of hosting issues, you guys are forced to code it in a way that might hinders its progress to have a better and efficient code.

I don't know what to say, for me using MySQL server's processing power with an application like ES which is data intensive is an ideal way to go. Well, just a thought, what about 2 versions - ES for Shared Hosting and ES for Dedicated Servers where Shared Hosting version will have relaxed Privacy and ACL features and of course more processes are run via PhP and designed for smaller community. Where Dedicated Server version is more robust, have more Privacy and ACL features, and you guys can go all the way on code efficiency, and designed for bigger community. just throwing out ideas.

Thanks,

Jackson

Hello Jackson,

Yeah, you have a point there and unfortunately, we need to go with this route otherwise we would spend 3/4 of our time just installing EasySocial for their site