Hi, Jeremy! On Nov 13, Jeremy Cole wrote:
Bar made a point about sysvars being difficult for replication.
I'd say, it depends on your use case. If it's important to run CREATE TABLE on the master and have a temporal column to be created on the slave using exactly the same binary on-disk format (especially, if one expects to change this sysvar often) - then sysvar changes must be replicated, and it would need special support from the replication code.
Otherwise - if the above is not the goal - then sysvar is a good and clean approach, agree.
I think there are two cases here with different goals perhaps:
1. The on-disk storage format
This is really in the purview of the DBA, not the user. I don't think using sql_mode or allowing the user to choose is appropriate. The DBA must decide whether they want compatibility with older MariaDB or compatibility with newer MySQL, and the user should not be able to compromise that. Additionally, we should not follow the master's choice here, as divergence in this is a valid (and maybe desired) upgrade path -- so I think replicating this is unnecessary. I would opt for a global + session sysvar.
Okay, then sysvar is good.
2. The rounding behavior
This is unfortunate and not easy to solve; I think it is in both the DBA and the user's purview in two different and slightly conflicting ways:
On a second thought, I'd go with a sysvar too. It wouldn't be the only sysvar that affects the results, and must be replicated. For example, @@div_precision_increment is also one of those, if the master and slave have is set differently, they'll start to diverge. So, I'd rather have a separate solution that covers replication and all such sysvars, instead of trying to put everything into sql_mode. Regards, Sergei