Question: Configuration of AWS RDS sql_mode

Forum - Software
#1

Hi,

I have successfully set up Eramba on AWS, but using RDS means that I have to follow AWS’ guidelines for MySQL/MariaDB parameters. This is fine as you can set most of them properly, but sql_mode is an issue because a blank entry is not accepted here.

Does anyone else have experience with this? What settings did you end up using? Mine currently looks like this, but I don’t want to enter prod with potential issues in the SQL configuration. The settings here were configured by me, originally it seemed to be in standard strict mode.

image
image937×145 7.73 KB

I can, of course, set up another instance for mySQL, but I would rather use RDS if possible.

#2

Hi Stian,

Can you try NO_ENGINE_SUBSTITUTION and let us know the result?

Regards,
Kristijan

2 Likes
#3

Thanks a lot, that worked!

image
image914×145 6.97 KB

That’s the last piece of the puzzle to make it work in AWS.

#4

You changed the RDS parameter in this case? I am trying to use a shared, already configured RDS instance. And it has very different settings for sql_mode. No idea what the impact might be.

So why is this specific setting required?

I am getting errors like

triggering handler | Create eramba schema
    10.0.196.95 | FAILED | 26695ms
    {
      - msg: mysql: [Warning] Using a password on the command line interface can be insecure.
             ERROR 1227 (42000) at line 9597: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Which is for this statement

/*!50003 CREATE*/ /*!50017 DEFINER=`eramba`@`localhost`*/ /*!50003 TRIGGER `user_fields_groups_after_insert` AFTER INSERT ON `user_fields_groups` FOR EACH ROW INSERT INTO `user_fields_objects` 
            SET
                model = NEW.model,
                foreign_key = NEW.foreign_key,
                field = NEW.field,
                object_id = NEW.group_id,
                object_key = concat('Group-', NEW.group_id),
                object_model = 'Group',
                created = NEW.created,
                modified = NEW.modified */;;

Isn’t this commented out?

…

On my RDS instance it does have NO_ENGINE_SUBSTITUTION set, but it also has lots of other settings:

ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH, PIPES_AS_CONCAT, REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES, ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL

When provisioning eramba in a VM with local mysql it works great. But after a few days still no luck with RDS.

…

I create a top level post for this: