MySQL/MariaDB strict mode disabled by setting sql_mode=ANSI_QUOTES

[julianbrost]

Looking into Icinga/icinga2#9794 (comment) showed that this line has unintended side effects:

icingadb/pkg/config/database.go

Line 64 in 78fa223

config.Params = map[string]string{"sql_mode": "ANSI_QUOTES"}

Add the following code to log the sql_mode after connecting to the database:

diff --git a/cmd/icingadb/main.go b/cmd/icingadb/main.go
index 1c22afc..f26a0a4 100644
--- a/cmd/icingadb/main.go
+++ b/cmd/icingadb/main.go
@@ -68,6 +68,10 @@ func run() int {
                if err != nil {
                        logger.Fatalf("%+v", errors.Wrap(err, "can't connect to database"))
                }
+
+               var sqlMode string
+               err = db.Get(&sqlMode, "SELECT @@sql_mode")
+               logger.Infow("sql mode", zap.String("sql_mode", sqlMode), zap.Error(err))
        }
 
        if err := db.CheckSchema(context.Background()); err != nil {

This will log:

sql mode        {"sql_mode": "ANSI_QUOTES"}

Now additionally comment out the line mentioned above that sets sql_mode, run it again and now it logs (and crashes shortly afterwards as MariaDB no longer understands the queries):

sql mode        {"sql_mode": "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"}

So setting sql_mode=ANSI_QUOTES made MariaDB (and probably MySQL, but I didn't perform any extra tests with that) less strict. This will make it silently "fix" out-of-range values for numeric columns for example.

[lippserd]

I'll fix that.