Ecto SQL

v3.1.1

Ecto SQL v3.1.1 Ecto.Adapters.MySQL View Source

Adapter module for MySQL.

It uses Mariaex for communicating to the database. Currently it supports old MySQL versions but upcoming Ecto releases will require 5.7+.

Options

MySQL options split in different categories described below. All options can be given via the repository configuration:

Connection options

  • :hostname - Server hostname
  • :socket_dir - Connect to MySQL via UNIX sockets in the given directory The socket name is derived based on the port. This is the preferred method for configuring sockets and it takes precedence over the hostname. If you are connecting to a socket outside of the Postgres convention, use :socket instead;
  • :socket - Connect to MySQL via UNIX sockets in the given path. This option takes precedence over the :hostname and :socket_dir
  • :port - Server port (default: 3306)
  • :username - Username
  • :password - User password
  • :database - the database to connect to
  • :pool - The connection pool module, defaults to DBConnection.ConnectionPool
  • :ssl - Set to true if ssl should be used (default: false)
  • :ssl_opts - A list of ssl options, see Erlang's ssl docs
  • :parameters - Keyword list of connection parameters
  • :connect_timeout - The timeout for establishing new connections (default: 5000)
  • :socket_options - Specifies socket configuration
  • :cli_protocol - The protocol used for the mysql client connection (default: tcp). This option is only used for mix ecto.load and mix ecto.dump, via the mysql command. For more information, please check MySQL docs

The :socket_options are particularly useful when configuring the size of both send and receive buffers. For example, when Ecto starts with a pool of 20 connections, the memory usage may quickly grow from 20MB to 50MB based on the operating system default values for TCP buffers. It is advised to stick with the operating system defaults but they can be tweaked if desired:

socket_options: [recbuf: 8192, sndbuf: 8192]

We also recommend developers to consult the Mariaex.start_link/1 documentation for a complete listing of all supported options.

Storage options

  • :charset - the database encoding (default: "utf8")
  • :collation - the collation order
  • :dump_path - where to place dumped structures

After connect callback

If you want to execute a callback as soon as connection is established to the database, you can use the :after_connect configuration. For example, in your repository configuration you can add:

after_connect: {Mariaex, :query!, ["SET variable = value", []]}

You can also specify your own module that will receive the Mariaex connection as argument.

Limitations

There are some limitations when using Ecto with MySQL that one needs to be aware of.

Engine

Since Ecto uses transactions, MySQL users running old versions (5.1 and before) must ensure their tables use the InnoDB engine as the default (MyISAM) does not support transactions.

Tables created by Ecto are guaranteed to use InnoDB, regardless of the MySQL version.

UUIDs

MySQL does not support UUID types. Ecto emulates them by using binary(16).

Read after writes

Because MySQL does not support RETURNING clauses in INSERT and UPDATE, it does not support the :read_after_writes option of Ecto.Schema.field/3.

DDL Transaction

MySQL does not support migrations inside transactions as it automatically commits after some commands like CREATE TABLE. Therefore MySQL migrations does not run inside transactions.

usec in datetime

Old MySQL versions did not support usec in datetime while more recent versions would round or truncate the usec value.

Therefore, in case the user decides to use microseconds in datetimes and timestamps with MySQL, be aware of such differences and consult the documentation for your MySQL version.

If your version of MySQL supports microsecond precision, you will be able to utilize Ecto's usec types.