elixir-ecto / myxql Goto Github PK

View Code? Open in Web Editor NEW

258.0 258.0 63.0 692 KB

MySQL 5.5+ driver for Elixir

License: Apache License 2.0

Elixir 99.78% Shell 0.22%

database elixir mysql

myxql's People

Contributors

Stargazers

Watchers

Forkers

schultzer qrede clessg k-cross shayan-taheri maartenvanvliet seeekr sabudaye meerkatdev zoldar ludwikbukowski dbhobbs kyleaa reggieboz kyleparisi luisfmcalado dbii hkrutzer jackjoe thesquad ewoutquax swingcloud buffalobuffalo rliebling adrianomitre iwarshak bnjunge erssebaggala hiagomeels bschmeck midigofrank raphsfeir sound2gd naaano tschnibo zmaril m0rt3nlund cristopher-rodrigues stjordanis rtiweb timothyvanderaerden mreyk greg-rychlewski 0xax monkeyisnull plastic-forks jonatanklosko watsy0007 pinkdiamond1 sonic182 vorce mmedoune washingtons2 wnoonan angularify gshuohu sharpycobra danschultzer rsamdev elbow-jason iagocavalcante

myxql's Issues

Move prepared statements from connection state to ets table

Currently we store prepared statements ref => statement id mapping on a map in connection state. By moving it to ets table we'll avoid copying data between client and connection process.

Why is this repo?

Out of curiosity, why does this repo exist? Is there a problem with Mariaex?

Improve error codes

Currently we only return integer error code e.g.: 1062 for uniqueness constraint violation, we should return an atom as well: :er_dup_entry [1]. Need to investigate how often error list changes.

[1]
https://dev.mysql.com/doc/refman/5.5/en/server-error-reference.html#error_er_dup_entry

Add `:columns` to MyXQL.Query struct

Mirror Postgrex.

Investigate SQL modes

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-important

cannot use with ProxySQL

I found that my app is talking to mysql via ProxySQL, myxql could not connect to the database, while mariaex could.

Background

ecto URL: ecto://username:[email protected]:3136/dbname?pool_size=15
the database user has minimal read-only access and requires "mysql_native_password"
the endpoint is behind of ProxySQL (1.4.12 from severalnines/proxysql)
mysql server (behind proxysql): 8.0.11

Deps

ecto: 3.1.4
ecto_sql: 3.1.1
myxql: d5b4d8b

Output of SHOW VARIABLES LIKE "%version%"; from Sequel Pro

innodb_version	8.0.11
protocol_version	10
slave_type_conversions	
tls_version	TLSv1,TLSv1.1,TLSv1.2
version	8.0.11
version_comment	MySQL Community Server - GPL
version_compile_machine	x86_64
version_compile_os	Linux
version_compile_zlib	1.2.11

When connected with mysql CLI:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 8.0.11 (ProxySQL)

Error:

10:55:04.639 [error] MyXQL.Connection (#PID<0.361.0>) failed to connect: ** (MyXQL.Error) MyXQL requires MySQL server 5.7.10+

If I made the app directly talk to mysql slave (not via ProxySQL), it works well :)

I'm not sure whether it's our ProxySQL configuration issue, or ProxySQL actually does not support some features. Still investigating..

Consider changing the default character set to utf8mb4

Please consider changing the default character set here:

  character_sets = %{
    utf8_general_ci: 0x21
  }

to:

  character_sets = %{
    utf8mb4_general_ci: 0x2d
  }

Otherwise characters off the basic multilingual plane (e.g., emoticons and many Asian characters) will, depending on the SQL mode, either silently be discarded or error:

> MyXQL.query(pid, "CREATE TABLE t (i INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, c CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci)") 
{:ok,
 %MyXQL.Result{
   columns: nil,
   connection_id: 5,
   last_insert_id: 0,
   num_rows: 0,
   num_warnings: 0,
   rows: nil
 }}

> MyXQL.query(pid, "select @@character_set_results, @@character_set_client, @@character_set_connection;")
{:ok,
 %MyXQL.Result{
   columns: ["@@character_set_results", "@@character_set_client",
    "@@character_set_connection"],
   connection_id: 5,
   last_insert_id: nil,
   num_rows: 1,
   num_warnings: 0,
   rows: [["utf8", "utf8", "utf8"]]
 }}

> MyXQL.query(pid, "set session sql_mode = '';")                          
{:ok,                          
 %MyXQL.Result{
   columns: nil,
   connection_id: 5,
   last_insert_id: 0,
   num_rows: 0,
   num_warnings: 1,
   rows: nil
 }}

> MyXQL.query(pid, "insert into t (c) values (?);", ["!!!😃!!!"])         
{:ok,                                                              
 %MyXQL.Result{
   columns: nil,
   connection_id: 5,
   last_insert_id: 1,
   num_rows: 1,
   num_warnings: 1,
   rows: nil
 }}

> MyXQL.query(pid, "select * from t where i = ?;", [1])                   
{:ok,                                                              
 %MyXQL.Result{
   columns: ["i", "c"],
   connection_id: 5,
   last_insert_id: nil,
   num_rows: 1,
   num_warnings: 0,
   rows: [[1, "!!!????!!!"]]  # <--- No emoticon
 }}

> MyXQL.query(pid, "set session sql_mode = 'TRADITIONAL';")               
{:ok,                                                              
 %MyXQL.Result{
   columns: nil,
   connection_id: 5,
   last_insert_id: 0,
   num_rows: 0,
   num_warnings: 1,
   rows: nil
 }}

> MyXQL.query(pid, "insert into t (c) values (?);", ["!!!😃!!!"])
{:error,
 %MyXQL.Error{
   connection_id: 5,
   message: "(1366) (ER_TRUNCATED_WRONG_VALUE_FOR_FIELD) Incorrect string value: '\\xF0\\x9F\\x98\\x83!!...' for column 'c' at row 1",
   mysql: %{code: 1366, name: :ER_TRUNCATED_WRONG_VALUE_FOR_FIELD},
   statement: "insert into t (c) values (?);"
 }}

Here's what that sequence looks like after making the change:

> MyXQL.query(pid, "select @@character_set_results, @@character_set_client, @@character_set_connection;")
{:ok,
 %MyXQL.Result{
   columns: ["@@character_set_results", "@@character_set_client",
    "@@character_set_connection"],
   connection_id: 6,
   last_insert_id: nil,
   num_rows: 1,
   num_warnings: 0,
   rows: [["utf8mb4", "utf8mb4", "utf8mb4"]]
 }}

> MyXQL.query(pid, "insert into t (c) values (?);", ["!!!😃!!!"]) 
{:ok,
 %MyXQL.Result{
   columns: nil,
   connection_id: 6,
   last_insert_id: 2,
   num_rows: 1,
   num_warnings: 0,
   rows: nil
 }}

> MyXQL.query(pid, "select * from t where i = ?;", [2]) 
{:ok,
 %MyXQL.Result{
   columns: ["i", "c"],
   connection_id: 6,
   last_insert_id: nil,
   num_rows: 1,
   num_warnings: 0,
   rows: [[2, "!!!😃!!!"]]
 }}

(The SQL mode no longer matters since there's no longer a problem.)

In the meantime a workaround (or an alternative approach if you don't think utf8mb4 should be the default) is to issue this query after connecting:

"SET NAMES utf8mb4;"

Change handle_rollback to send one query

Currently, we send "ROLLBACK TO SAVEPOINT" and "RELEASE SAVEPOINT" as separate queries as we don't fully support multiple statements yet.

Disconnect on read-only transaction error

elixir-ecto/postgrex#414 (comment)

Ensure packets do not come out of order

Its highly unlikely as servers should guarantee it, but it should be relatively easy to do in recv_packets/3 by looking at packet sequence ids.

Return multiple results from text query

MySQL allows us to make this work:

[
  %MySQL.Result{rows: [[1]]},
  %MySQL.Result{rows: [[2]]}
] = MyXQL.query_multi!(c.conn, "SELECT 1; SELECT 2")

Thus, we could do "INSERT INTO this (...); INSERT INTO that (...)", so we could even replace calling mysql CLI when running migrations. This is only possible using the text protocol.

Add :socket_options configuration option

What's the key issue preventing 5.6 compatibility?

Amazon Aurora Serverless only supports MySQL 5.6. What's the key issue preventing 5.6 compatibility? (I'm trying to figure out whether or not adding compatibility is within my reach.)

Handle stored procedures in MyXQL.query/4

Currently, we only handle them in MyXQL.stream.

describe "stored procedures" do
  setup [:connect, :truncate]

  test "multi-resultset in text protocol", c do
    MyXQL.query!(c.conn, "CALL multi();", [])
    |> IO.inspect()
  end

  test "multi-resultset in binary protocol", c do
    MyXQL.query!(c.conn, "CALL multi();", [])
    |> IO.inspect()
  end
end

Server can send an ERR packet rather than an Initial Handshake Packet

The code here assumes that the server will always send an initial handshake packet, but as documented here the server might send an ERR packet instead.

As the page notes, an initial ERR packet will be sent before capabilities are negotiated and therefore won't include the conditional server_state fields.

LOCAL INFILE

https://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::LOCAL_INFILE_Data

Receiving packets larger than 16mb

https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html

Close unnamed queries after executing them

Represent MySQL timestamp as Elixir DateTime

Currently, both timestamp and datetime are represented as NaiveDateTime however DateTime however when encoding them we can use DateTime too.

This change will be closer to Postgrex's semantics (timestamptz represented as DateTime) however it would be kinda confusing that timestamp <> DateTime, datetime <> NaiveDateTime.

Connect using unix domain socket

Investigate using CLIENT_SESSION_TRACK & SERVER_SESSION_STATE_CHANGED capabilities

Improve query repreparation

extract common parts
return statement_id without additional lookup. Maybe store statement_in on %Query{}?

Storing a larger binary causes `FunctionClauseError`

I have a table that stores a mediumblob in mysql 5.7.25 and it is blowing up when I try to store a larger erlang_term directly into the DB.

     ** (FunctionClauseError) no function clause matching in MyXQL.Protocol.Values.decode_string_lenenc/4

     The following arguments were given to MyXQL.Protocol.Values.decode_string_lenenc/4:

         # 1
         <<253, 71, 60, 1, 131, 116, 0, 0, 0, 5, 100, 0, 10, 95, 95, 115, 116, 114, 117, 99, 116, 95, 95, 100, 0, 24, 69, 108, 105, 120, 105, 114, 46, 83, 102, 83, 101, 114, 118, 105, 99, 101, 115, 46, 83, 99, 104, 101, 109, 97, ...>>

         # 2
         0

         # 3
         [:uint8, :binary]

         # 4
         ["I-9 (Sample Workflow)", "hg11ZxuZwBGiCV57", ~N[2019-04-04 17:20:25], ~N[2019-04-04 17:20:25], 761]

     Attempted function clauses (showing 4 out of 4):

         defp decode_string_lenenc(<<n::integer()-size(8), v::binary()-size(n), r::bitstring()>>, null_bitmap, t, acc) when n < 251
         defp decode_string_lenenc(<<252::integer(), n::integer()-little()-size(16), v::binary()-size(n), r::bitstring()>>, null_bitmap, t, acc)
         defp decode_string_lenenc(<<253::integer(), n::integer()-little()-size(32), v::binary()-size(n), r::bitstring()>>, null_bitmap, t, acc)
         defp decode_string_lenenc(<<254::integer(), n::integer()-little()-size(64), v::binary()-size(n), r::bitstring()>>, null_bitmap, t, acc)

     code: assert :ok == DraftServices.Seeder.seed_team(team_guid)
     stacktrace:
       (myxql) lib/myxql/protocol/values.ex:480: MyXQL.Protocol.Values.decode_string_lenenc/4
       (myxql) lib/myxql/protocol/messages.ex:400: MyXQL.Protocol.Messages.decode_resultset/4
       (myxql) lib/myxql/client.ex:167: MyXQL.Client.recv_packets/5
       (myxql) lib/myxql/connection.ex:95: MyXQL.Connection.handle_execute/4
       (ecto_sql) lib/ecto/adapters/sql/sandbox.ex:369: Ecto.Adapters.SQL.Sandbox.Connection.proxy/3
       (db_connection) lib/db_connection/holder.ex:277: DBConnection.Holder.holder_apply/4
       (db_connection) lib/db_connection.ex:1255: DBConnection.run_execute/5
       (db_connection) lib/db_connection.ex:1342: DBConnection.run/6
       (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
       (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
       (myxql) lib/myxql.ex:199: MyXQL.query/4
       (ecto_sql) lib/ecto/adapters/sql.ex:571: Ecto.Adapters.SQL.execute!/4
       (ecto_sql) lib/ecto/adapters/sql.ex:538: Ecto.Adapters.SQL.execute/5
       (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
       (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

I've been digging around to try and find some more context, but I'm not very strong on this topic. Please let me know if there's something that I can do to help, I'm not unwilling.

Decoding doesn't appear to take various flags into account

Here is the code that decodes initial handshake packets:

  def decode_initial_handshake(payload) do
    protocol_version = 10
    <<^protocol_version, rest::binary>> = payload
    {server_version, rest} = take_string_nul(rest)

    <<
      conn_id::uint4,
      auth_plugin_data1::string(8),
      0,
      capability_flags1::uint2,
      character_set::uint1,
      status_flags::uint2,
      capability_flags2::uint2,
      auth_plugin_data_length::uint1,
      _::uint(10),
      rest::binary
    >> = rest

    take = max(13, auth_plugin_data_length - 8)
    <<auth_plugin_data2::binary-size(take), auth_plugin_name::binary>> = rest
    auth_plugin_data2 = decode_string_nul(auth_plugin_data2)
    auth_plugin_name = decode_string_nul(auth_plugin_name)
    <<capability_flags::uint4>> = <<capability_flags1::uint2, capability_flags2::uint2>>
    auth_plugin_data = auth_plugin_data1 <> auth_plugin_data2

    initial_handshake(
      server_version: server_version,
      conn_id: conn_id,
      auth_plugin_name: auth_plugin_name,
      auth_plugin_data: auth_plugin_data,
      capability_flags: capability_flags,
      character_set: character_set,
      status_flags: status_flags
    )
  end

Here is an outline of the packet structure:

  ...
  if more data in the packet:
  ...
  if capabilities & CLIENT_PLUGIN_AUTH {
    ...
  } else {
    ...
  }
  ...
  if capabilities & CLIENT_SECURE_CONNECTION {
    ...
    if capabilities & CLIENT_PLUGIN_AUTH {
      ...
    }
  }

Notice that decoding doesn't check the capability flags the server just sent. (Imagine, for example, connecting to an older server.)

Other decoding follows the same pattern. For example, OK packet decoding implicitly assumes CLIENT_PROTOCOL_41 and !CLIENT_SESSION_TRACK. (I understand that the current code implements a specific set of client capabilities, but ultimately the client and server have to negotiate and agree; I don't think you want to just assume client capabilities everywhere.)

The implementation of the capability negotiation appears to be wrong

According to the docs:

The client should only announce the capabilities in the Handshake Response Packet that it has in common with the server.

In effect, in the response packet the client should AND its capabilities with the server's.

Instead, here, the client is just announcing its own capabilities.

Add socket receive timeout when idle

Currently the timeout is :infinity and cannot be configured. Since socket is always in passive mode this is OK for operations in client process, not OK for handshake which is connection process and it's actually not OK for ping because it's in connection process too.

Note it'd be an option on MyXQL.start_link, similar to Postgrex and Mariaex.

@josevalim @fishcakez should we mirror Postgrex in calling this :timeout? We have the following there:

:timeout - Socket receive timeout when idle in milliseconds (default: 15000);

FWIW Mariaex has:

:timeout - Connect timeout in milliseconds (default: 15000);

Or maybe we should have something more explicit like :idle_timeout (not to be confused with :idle_interval from dbconnection!)

MyXQL with Ecto: Error in MyXQL.Messages.decode_com_stmt_prepare_response when performing a SELECT

Description

I've decided to check how MyXQL+Ecto work with my staging DB. Error below appears each time I'm trying to select, via Repo.get, from any table. There are no errors when using Ecto.Adapters.MySQL (while still using wojtekmach/ecto_sql).

How to reproduce

Use MyXQL with Ecto SQL:

in mix.exs set:

{:myxql, "~> 0.1"},
{:mariaex, ">= 0.0.0"},
{:ecto_sql, github: "wojtekmach/ecto_sql", branch: "wm-myxql", override: true}

set adapter:
adapter: Ecto.Adapters.MyXQL

Create a simple table and schema

sql
CREATE TABLE `dummy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

defmodule Importer.Identity.Dummy do
  use Ecto.Schema

  schema "dummy" do
    field :name, :string
  end
end

... and add some records

Run:
Importer.Identity.Dummy |> Importer.Repo.get(1)

-> Error Appears
Switch adapter:
adapter: Ecto.Adapters.MySQL
Run:
Importer.Identity.Dummy |> Importer.Repo.get(1)

-> No error - you get results back

Example error

iex> Importer.Identity.User |> Importer.Repo.get(34)

a) With Ecto.Adapters.MyXQL

  [debug] QUERY ERROR source="phpbb_users" db=112.7ms queue=44.9ms
  SELECT p0.`user_id`, p0.`username` FROM `phpbb_users` AS p0 WHERE (p0.`user_id` = ?) [34]

** (MatchError) no match of right hand side value: <<60, 0, 0, 5, 3, 100, 101, 102, 9, 110, 119, 100, 95, 115, 109, 111, 107, 105, 2, 112, 48, 11, 112, 104, 112, 98, 98, 95, 117, 115, 101, 114, 115, 8, 117, 115, 101, 114, 110, 97, 109, 101, 8, 117, 115, 101, 114, 110, 97, 109, ...>>
    (myxql) lib/myxql/messages.ex:380: MyXQL.Messages.decode_com_stmt_prepare_response/3
    (myxql) lib/myxql/protocol.ex:329: MyXQL.Protocol.recv_packets/4
    (myxql) lib/myxql/protocol.ex:682: MyXQL.Protocol.prepare/2
    (myxql) lib/myxql/protocol.ex:89: MyXQL.Protocol.handle_prepare/3
    (db_connection) lib/db_connection/holder.ex:270: DBConnection.Holder.holder_apply/4
    (db_connection) lib/db_connection.ex:1180: DBConnection.prepare/4
    (db_connection) lib/db_connection.ex:1173: DBConnection.run_prepare/4
    (db_connection) lib/db_connection.ex:1185: DBConnection.run_prepare_execute/5
    (db_connection) lib/db_connection.ex:1282: DBConnection.run/6
    (db_connection) lib/db_connection.ex:504: DBConnection.parsed_prepare_execute/5
    (db_connection) lib/db_connection.ex:497: DBConnection.prepare_execute/4
    (ecto_sql) lib/ecto/adapters/sql.ex:546: Ecto.Adapters.SQL.execute!/4
    (ecto_sql) lib/ecto/adapters/sql.ex:538: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (ecto) lib/ecto/repo/queryable.ex:66: Ecto.Repo.Queryable.one/3

Displaying the list with IO.puts gives me

IO.puts([60, 0, 0, 5, 3, 100, 101, 102, 9, 110, ...)
<^@^@^E^Cdef    nwd_smoki^Bp0^Kphpbb_users^Husername^Husernam

where nwd_smoki is the DB(and DB User) name and phpbb_users is the table name.

b) With Ecto.Adapters.MySQL

[debug] QUERY OK source="phpbb_users" db=51.5ms decode=16.6ms queue=44.2ms
SELECT p0.`user_id`, p0.`username` FROM `phpbb_users` AS p0 WHERE (p0.`user_id` = ?) [34]

%Importer.Identity.User{
  __meta__: #Ecto.Schema.Metadata<:loaded, "phpbb_users">,
  user_id: 34,
  username: "test"
}

My DB, Table and Schema

Error appears even with a very simple table/schema:
The only unusual thing about my DB is the encoding (but error appears also when working with UTF-8 tables like the one above):

The implementation of sha256_password appears to be wrong

Here the handshake code appears to take the auth_plugin_data from the initial handshake and pass it into auth_response. For sha256_password and caching_sha2_password, auth_response then passes that data into sha256_password here.

As far as I can tell, that isn't right. Here are the docs for sha256_password. They say:

If the password is non-empty SHA256 auth method

XORs the clients password with the scramble buffers content

RSA encrypts that with the server's public key using RSA_PKCS1_OAEP_PADDING as padding

Empty passwords are not hashed, but sent as empty string.

It appears that the client is either expected to already have the public key or to ask for it. Here the docs explain how to ask for it. When asked the server sends the key in an AuthMoreData packet; I don't think MyXQL implements that?

The docs have worked examples of sha256_password logins here.

Add :json_library start_link option

Currently we use application environment for this configuration, following Mariaex & Postgrex. If we're gonna do this, we could leave a deprecation warning for folks that were moving off Mariaex here.

Thoughts @josevalim?

Test against MariaDB

Support ENUM data type

Try not to go over the max prepared statements count

See: xerions/mariaex#182

TODO:

Disconnect the connection on MySQL error code 1461 (ER_MAX_PREPARED_STMT_COUNT_REACHED). This needs double-checking but we expect that when connection is disconnected, the server will clean up these statements. This doesn't solve the unlikely case where there's one rogue connection that took up most/all prepared statements and never made another one (which would force it to disconnect)
Add prepare: :named (default) | :unnamed connection option. :unnamed will force query name to be "" which means it will be closed after executing

Add :after_connect option

Disconnect connection on `:closed` and similar tcp errors

Improve error message when using UNIX domain socket that doesn't exist

Currently it's:

11:51:23.027 [error] MyXQL.Protocol (#PID<0.314.0>) failed to connect: ** (MyXQL.Error) no such file or directory

it isn't clear what's the file we attempted to use.

Cursors

Handle unsigned types

Set unsigned_flag in com_stmt_execute, add tests to types_test.exs

Decode results in MyXQL.Query.decode/3

This way DBConnection.LogEntry.decode_time will accurately represent how much time was spent on that.

Add `:messages` to Result struct

Mirror Postgrex.

Add :charset option to start_link

Note: test with Utf8mb4 and emojis

The code in recv_packets doesn't verify the sequence_id

The code here ignores rather than verifies the sequence_id.

Default socket options

Currently, MyXQL defaults to protocol: :socket (UNIX domain socket), which should yield better performance than going over tcp.

However, I noticed the default recbuf is much smaller:

iex> {:ok, sock} = :gen_tcp.connect('localhost', 3306, [:binary, packet: :raw, active: false])
iex> :inet.getopts(sock, [:buffer, :sndbuf, :recbuf])
{:ok, [buffer: 1460, sndbuf: 146988, recbuf: 408300]}

iex> {:ok, sock} = :gen_tcp.connect({:local, "/tmp/mysql.sock"}, 0, [:binary, packet: :raw, active: false])
iex> :inet.getopts(sock, [:buffer, :sndbuf, :recbuf])
{:ok, [buffer: 1460, sndbuf: 8192, recbuf: 8192]}

which yields worse performance on bigger datasets compared to connecting over tcp.

(Btw, we do the same trick as Postgrex & Mariaex when we set internal socket buffer based on sndbuf/recbuf: 384b9ba)

@josevalim @fishcakez do you have any ideas why domain socket starts with smaller buffers? From what I could read, sndbuf and rebuf set by VM should be a reasonable default. Should we try to bump them anyway? Or default to connecting over TCP like Postgrex and Mariaex does?