Sync fails diagnostics/boot with Amazon RDS #127

New issue

Closed

opened 2020-04-01 12:02:21 +02:00 by mixian · 5 comments

mixian commented

2020-04-01 12:02:21 +02:00

(Migrated from github.com)

Hi,
i have EC2 Instance connected to Mysql RDS instance on Amazon AWS.
The configuration is ok, but when i launch the sync operation the script return "privilege error" on mysql server :

Starting IMAP sync in interactive mode
[1-Apr-20 10:00:24] DEBUG: Starting sync engine
[1-Apr-20 10:00:24] NOTICE: Process ID: 27491
[1-Apr-20 10:00:24] NOTICE: SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation
[1-Apr-20 10:00:24] CRITICAL: System encountered an un-recoverable database error. Going to halt now, please see the log file for info.
#0 /home/*************public/ext_lib/libremail-master/sync/sync(222): App\Diagnostics::checkDatabaseException(Object(Pimple\Container), Object(PDOException), true, true)
#1 {main}

Now, rds is managed service and not permit SUPER user privilege as decripted below.

https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

There are a method for bypass this problem?

I'm on DEBIAN 9, Mysql 5.6 on RDS, Php 7.3 and last version of this application.

Hi, i have EC2 Instance connected to Mysql RDS instance on Amazon AWS. The configuration is ok, but when i launch the sync operation the script return "privilege error" on mysql server : Starting IMAP sync in interactive mode [1-Apr-20 10:00:24] DEBUG: Starting sync engine [1-Apr-20 10:00:24] NOTICE: Process ID: 27491 [1-Apr-20 10:00:24] NOTICE: SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation [1-Apr-20 10:00:24] CRITICAL: System encountered an un-recoverable database error. Going to halt now, please see the log file for info. #0 /home/*************public/ext_lib/libremail-master/sync/sync(222): App\Diagnostics::checkDatabaseException(Object(Pimple\Container), Object(PDOException), true, true) #1 {main} Now, rds is managed service and not permit SUPER user privilege as decripted below. https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/ There are a method for bypass this problem? I'm on DEBIAN 9, Mysql 5.6 on RDS, Php 7.3 and last version of this application.

mikegioia commented

2020-04-01 18:17:09 +02:00

(Migrated from github.com)

Thanks for the writeup @mixian. My hunch is that your MySQL user doesn't have access to run some of the SQL commands at boot. The sync app tries to run the following queries:

SET GLOBAL max_allowed_packet = [size]; (usually 16Mb)
SET SESSION wait_timeout = 28800;
SET NAMES [charset];

Would you mind running ./sync -D from within the sync folder. This just runs the diagnostic checks and doesn't load anything else. If this throws an error it's almost guaranteed to be the max_allowed_packet command. If it succeeds we'll have to do some more testing.

If that above command fails, I would then ask you to please comment out line 187 of the script /sync/sync. The line that should be commented out is $di['diagnostics']->run(). I'm curious to see if the rest of the application runs with the diagnostics disabled.

Thanks for the writeup @mixian. My hunch is that your MySQL user doesn't have access to run some of the SQL commands at boot. The sync app tries to run the following queries: ``` SET GLOBAL max_allowed_packet = [size]; (usually 16Mb) SET SESSION wait_timeout = 28800; SET NAMES [charset]; ``` Would you mind running `./sync -D` from within the sync folder. This just runs the diagnostic checks and doesn't load anything else. If this throws an error it's almost guaranteed to be the `max_allowed_packet` command. If it succeeds we'll have to do some more testing. If that above command fails, I would then ask you to please comment out line 187 of the script `/sync/sync`. The line that should be commented out is `$di['diagnostics']->run()`. I'm curious to see if the rest of the application runs with the diagnostics disabled.

mikegioia commented

2020-04-01 18:20:11 +02:00

(Migrated from github.com)

Also, you might be able to circumvent this error entirely if you can somehow set the max_allowed_packet setting in your MySQL server directly to 16mb or higher. The query would look like SET GLOBAL max_allowed_packet = 16777216;.

The reason for this is that large emails will cause the SQL connection to drop unless the SQL query packet size is big enough to handle the whole email part in one query.

Also, you might be able to circumvent this error entirely if you can somehow set the `max_allowed_packet` setting in your MySQL server directly to 16mb or higher. The query would look like `SET GLOBAL max_allowed_packet = 16777216;`. The reason for this is that large emails will cause the SQL connection to drop unless the SQL query packet size is big enough to handle the whole email part in one query.

🎉 1

mixian commented

2020-04-01 21:34:56 +02:00

(Migrated from github.com)

Hello! Thanks a lot for the directions. In fact, the problem was the "max_allowed_packet" parameter which on AWS RDS had only 1mb by default.
These parameters can also be changed without being a SUPER user by creating a "customized parameter group" in the RDS admin panel-.
Now I'm syncing. I hope your code can help me.
Thanks again.

Hello! Thanks a lot for the directions. In fact, the problem was the "max_allowed_packet" parameter which on AWS RDS had only 1mb by default. These parameters can also be changed without being a SUPER user by creating a "customized parameter group" in the RDS admin panel-. Now I'm syncing. I hope your code can help me. Thanks again.

mikegioia commented

2020-04-01 21:54:56 +02:00

(Migrated from github.com)

Greaet! I'm going to leave this open so that I remember to add this RDS info to the documentation page about the max_allowed_packet/SQL stuff.

mikegioia commented

2020-04-05 00:36:31 +02:00

(Migrated from github.com)

Documentation updated in 8348813

Documentation updated in 8348813