trilogy-libraries / trilogy

Trilogy is a client library for MySQL-compatible database servers, designed for performance, flexibility, and ease of embedding.
MIT License
697 stars 68 forks source link

Add init_command option for parity with mysql2 #191

Open korbin opened 2 months ago

korbin commented 2 months ago

This PR adds the init_command feature, creating parity with the Ruby mysql2 gem:

https://github.com/brianmario/mysql2#initial-command-on-connect-and-reconnect

We use this to set sql_mode in several legacy application use cases.

I've added a test and adapted this pattern into the default test client factory (where the pattern was already being used in spirit.)

jhawthorn commented 2 months ago

What does this provide over simply calling query?

Though Trilogy is very similar to mysql2 (in part likely due to shared authorship) it is not meant to be 1:1 drop-in compatible.

korbin commented 2 months ago

This provides the ability to set SQL_MODE and other important options in config/database.yml in Rails applications - otherwise one must monkey patch the Trilogy class (a little messy) to add this functionality.

Similarly, it's convenient in connection pools where these types of context variables need to be set on reconnect/on pool expansion etc.

composerinteralia commented 2 months ago

Rails has its own config for setting sql_mode and other session variables. This is the code that makes it work: https://github.com/rails/rails/blob/770b35389610bc15bc83d20a199566acb6a1c22a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L919-L964.

For sql_mode specifically the config might look something like:

development:
  variables:
    sql_mode: 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_VALUE_ON_ZERO,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

Hopefully that helps.