search

dns3l / dns3l-core

Core functionality of dns3l written in Go
1 stars 3 forks source link

Set supported SQL mode during DB create job #41

Closed lnobach closed 1 year ago

iaean commented 1 year ago

Sure? DEFAULT 0 and DEFAULT NULL seems to have different semantics.

https://mariadb.com/kb/en/timestamp/

iaean commented 1 year ago

And much more important... How altering the schema is supported for existing databases and deployments? #8

lnobach commented 1 year ago

And much more important... How altering the schema is supported for existing databases and deployments? #8

This change is supposed to be backwards-compatible, i.e. old databases which could be initialized with the old schema work with the new code. I am still confirming this (that's why this is still a draft).

lnobach commented 1 year ago

Sure? DEFAULT 0 and DEFAULT NULL seems to have different semantics.

https://mariadb.com/kb/en/timestamp/

Yes, but the different semantics should not have an effect in our situation. I am trying to confirm this before removing the "Draft" status.

iaean commented 1 year ago

Why not simply inject fitting mode into the bootstrapping session?

SELECT @@SQL_MODE;
SET sql_mode = 'modes';

https://mariadb.com/kb/en/sql-mode/

lnobach commented 1 year ago

Why not simply inject fitting mode into the bootstrapping session?

SELECT @@SQL_MODE;
SET sql_mode = 'modes';

https://mariadb.com/kb/en/sql-mode/

This makes things easier (I thought this is not allowed by the server for some reason). Just append e.g. &sql_mode=STRICT_TRANS_TABLES to your SQL DB URL. Please check if this workaround solves your issues.

I will implement and test something stable + better tomorrow.

lnobach commented 1 year ago

I changed the approach.

The current code is tested with both the MariaDB default of: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and the restrictive STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

There is no guarantee that it works with any sql_mode combinations, because the sql_mode is only overwritten for the bootstrapping session, not for day-to-day requests. I think this is sufficient if the sql_mode your DB provider has set can be considered as "stable". If more guarantees are needed, please tell me, then we can additionally introduce a flag to inject the sql_mode on any request, I don't know the performance impact.