MySQL

Karol Bucek edited this page Jun 25, 2015 · 6 revisions

Profiling SQL

production:
  adapter: mysql
  database: borg
  host: localhost
  properties: # specific for MySQL
    profileSQL: true # will profile/explain each SQL operation
    #autoSlowLog: true # alternatively (less logging) use this

NOTE: logging won't use ActiveRecord::Base.logger but should instead print to Java's System.err by default.

Troubleshooting

Sample configuration (properties) to start with :

production:
  adapter: mysql
  database: borg
  properties:
    dumpQueriesOnException: true
    includeThreadDumpInDeadlockExceptions: true # on exceptions a system thread-dump will be taken
    #includeInnodbStatusInDeadlockExceptions: true # automatically "innodb status" on potential locks
    #includeThreadNamesAsStatementComment: true # Java thread names will be visible with "innodb status"
    #useUsageAdvisor: true # lots off output

Reference for all supported properties: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

SSL

Configuring SSL is a bit different than on MRI, since we're not using the "native" MySQL client library. Usually, instead one sets up the JVM with the desired key/trust stores, preferably using the JVM's native key-store format. Please read the official documentation: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-using-ssl.html

Another (older) guide of a MySQL Connector/J setup with Tomcat http://albertolarripa.com/2012/06/02/how-to-enable-data-encryption-ssl-for-a-jdbc-driver/

Fail-Over

Since AR-JDBC relies on JDBC we inherit whatsoever the driver shoots at us, for the official MySQL Connector/J among others it's fail-over support (multi-host connections). This might turn out useful for a number of high-availability scenarios. The support is very simple and you can use it any-time you use the MySQL adapter, just specify multiple hosts e.g. :

production
  adapter: mysql
  host: db1.host.org,db2.host.org
  database: zoo
  username: mia
  properties:
    # autoReconnect: false
    failOverReadOnly: true # default value
    secondsBeforeRetryMaster: 30 # default

Whenever the first (master) host goes down the following hosts will be used (in read-only mode by default) for queries. Please consult the official documentation for how fail-over behaves and what are its costs (only fails-over during connection initialization as long as the autoReconnect property is not set to true) http://dev.mysql.com/doc/connector-j/en/connector-j-config-failover.html

NOTE: Please be aware that these features are available using the official (jdbc-mysql) driver, since we also support MariaDB's (jdbc-mariadb) driver.