crystal-lang / crystal-mysql Goto Github PK

View Code? Open in Web Editor NEW

106.0 13.0 37.0 166 KB

MySQL connector for Crystal

License: MIT License

Crystal 100.00%

crystal database mysql driver

crystal-mysql's Introduction

crystal-mysql

MySQL driver implement natively in Crystal, without relying on external libraries.

Check crystal-db for general db driver documentation. crystal-mysql driver is registered under mysql:// uri.

Why

Using a natively implemented library has a significant performance improvement over working with an external library, since there is no need to copy data to and from the Crystal space and the native code. Initial tests with the library have shown a 2x-3x performance boost, though additional testing is required.

Also, going through the MySQL external library blocks the Crystal thread using it, thus imposing a significant penalty to concurrent database accesses, such as those in web servers. We aim to overcome this issue through a full Crystal implementation of the MySQL driver that plays nice with non-blocking IO.

Status

This driver is a work in progress. It implements mysql's binary protocol to create prepared statements. Contributions are most welcome.

Installation

Add this to your application's shard.yml:

dependencies:
  mysql:
    github: crystal-lang/crystal-mysql

Usage

require "mysql"

# connect to localhost mysql test db
DB.open "mysql://root@localhost/test" do |db|
  db.exec "drop table if exists contacts"
  db.exec "create table contacts (name varchar(30), age int)"
  db.exec "insert into contacts values (?, ?)", "John Doe", 30

  args = [] of DB::Any
  args << "Sarah"
  args << 33
  db.exec "insert into contacts values (?, ?)", args: args

  puts "max age:"
  puts db.scalar "select max(age) from contacts" # => 33

  puts "contacts:"
  db.query "select name, age from contacts order by age desc" do |rs|
    puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
    # => name (age)
    rs.each do
      puts "#{rs.read(String)} (#{rs.read(Int32)})"
      # => Sarah (33)
      # => John Doe (30)
    end
  end
end

When running this example, if you get the following exception:

Unhandled exception: Client does not support authentication protocol requested by server; consider upgrading MySQL client (Exception)

You have two options, set a password for root, or (most recommended option) create another user with access to test database.

CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

Then use the example above changing the DB.open line to

DB.open "mysql://test:yourpassword@localhost/test" do |db|

Connection URI

The connection string has the following syntax:

mysql://[user[:[password]]@]host[:port][/schema][?param1=value1&param2=value2]

Connection query params:

encoding: The collation & charset (character set) to use during the connection. If empty or not defined, it will be set to utf8_general_ci. The list of available collations is defined in MySql::Collations::COLLATIONS_IDS_BY_NAME

crystal-mysql's People

Contributors

Stargazers

Watchers

crystal-mysql's Issues

Register custom decoder?

Are there any docs on how to register a custom decoder, similar to the PG adapter shard? Or is that not a thing?

Use case being wanting to control how a custom type gets saved and read from db.

Not pulling in latest crystal-lang/db library?

I'm having a problem using crystal-mysql:

Error in ./libs/db/db/result_set.cr:60: abstract def DB::ResultSet#column_type(index : Int32) must be implemented by MySql::ResultSet

It seems that the abstract DB::ResultSet has been removed from the latest version of crystal-lang/db however, when I install this via the shard.yml and crystal deps it is pulling an out of date version of the db code.

Is this an issue with shard or with the specific version requirement in crystal-mysql's shard.yml file?

This is the same exact issue that is causing TravisCI to report that the BuildFailed

Runtime error: Index out of bounds (IndexError)

I'm getting an IndexError while trying to connect to my local MySQL 5.1.73 server.

Index out of bounds (IndexError)
[4457383] *CallStack::unwind:Array(Pointer(Void)) +87
[4457274] *CallStack#initialize:Array(Pointer(Void)) +10
[4457226] *CallStack::new:CallStack +42
[4427528] *raise<IndexError>:NoReturn +24
[4478993] *Slice(UInt8) +161
[4585921] *MySql::Protocol::HandshakeV10::read<MySql::ReadPacket>:MySql::Protocol::HandshakeV10 +817
[4571449] *MySql::Connection#read_packet<MySql::Protocol::HandshakeV10:Class>:MySql::Protocol::HandshakeV10 +41
[4570542] *MySql::Connection#initialize<DB::Database>:UInt8 +686
[4569827] *MySql::Connection::new<DB::Database>:MySql::Connection +131
[4569693] *MySql::Driver#build_connection<DB::Database>:MySql::Connection +13
[4569665] *DB::Database#initialize<DB::Driver+, URI>:MySql::Connection +33
[4569603] *DB::Database::new<DB::Driver+, URI>:DB::Database +115
[4536132] *DB::build_database<URI>:DB::Database +36
[4536094] *DB::build_database<String>:DB::Database +14
[4536070] *DB::open<String>:DB::Database +6
[4402599] ???
[4437977] main +41
[140094784847133] __libc_start_main +253
[4398601] ???

I've traced the error back to this line:

https://github.com/crystal-lang/crystal-mysql/blob/master/src/mysql/packets.cr#L27

When the variable auth_plugin_data_length is set to 0, subtracting 8 from it results in the value 248 and not -8. I think because it is unsigned and wraps around? Should auth_plugin_data_length be a signed integer?

The evaluation of {13, auth_plugin_data_length - 8}.max looks at {13, 248} and returns 248, rather than 13. And we try to read auth_data[8,247] which throws the IndexError.

Hardcoding that line to packet.read(auth_data[8,12]) avoids the crash and everything looks good after that.

I could submit a pull request to just hard-code that [8,12], but I'm sure it's in there for a good reason. Thanks for taking a look!

Release a new version

Hi @bcardiff

v0.3.2 was released on Mar 21, at least 10 commit and fixes has been published on master.

Would be good to have a new release

Thanks you for maintain this shard! <3

Is it safe to use crystal-mysql?

Is it safe to use crystal-mysql against sql injection etc?
or how can i filter the input?

DB::PoolRetryAttemptsExceeded / DB::PoolTimeout

I've created a small repo here to explain and recreate the bug

https://github.com/crisward/mysql_bug

This may be a bug in the connection pool of crystal-db, but I have no idea how to recreate it in a test without a concrete db driver. Also it could be mysql driver bug. Either way, hope this helps.

Connect to database with collation/character other than UTF8

In some cases other collations are needed in order to accept Emojis and/or non-latin characters.

It seems that as of today, this project do not provide a way to connect to MySQL database with any other option than UTF8, which leads to database errors when trying to add those "special" new characters.

With the wide use of smartphones, its basically impossible to build any application that do not support emojis. Its used anywhere, and its popularizing even more each year, so I believe the project should support some way to handle this.

Converting MySql::ResultSet to JSON::any

I'm trying to run a simple code and return a JSON with the results:

DB.open("mysql://root@localhost:3306/mydb") do |db|
  db.query "SELECT name FROM users" do |result|
    result.to_json
  end
end

But I get this error when trying to use .to_json on the results:

no overload matches 'MySql::ResultSet#to_json' with type JSON::Builder

Any idea on how can I convert my query results to JSON? I can't find any documentation describing it, and its literally the only thing keeping me from releasing a small Crystal API module into production.

MySQl Error Code

This is not an issues, let's say a feature request.

Is there a way to get mysql error code when there's an exception?
This would be easier when app needs to translate the error message to another language.

Support JSON data type

When trying to read the JSON column type as a string, the following error is thrown:

Missing hash key: 245_u8

Related issue: amberframework/granite#193

down

(DB::ConnectionRefused)
0x4b278e: *MySql::Connection#initializeDB::Database:UInt8 at /workspace/myapp/lib/mysql/src/mysql/connection.cr 32:7
0x4b2063: *MySql::Connection::newDB::Database:MySql::Connection at /workspace/myapp/lib/mysql/src/mysql/connection.cr 4:3
0x4b1fcd: *MySql::Driver#build_connectionDB::Database:MySql::Connection at /workspace/myapp/lib/mysql/src/mysql/driver.cr 4:5
0x4c4f33: *DB::Pool(DB::Connection+) at /workspace/myapp/lib/db/src/db/pool.cr 123:13
0x4c4ea6: *DB::Pool(DB::Connection+) at /workspace/myapp/lib/db/src/db/pool.cr 17:34
0x4c3ddc: *DB::Pool(T)::new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay<Int32, Int32, Int32, Float64, Int32, Float64, &Proc(DB::Connection+)>:DB::Pool(DB::Connection+) at ??
0x4ac1bd: *DB::Database#initialize<DB::Driver+, URI>:DB::Pool(DB::Connection+) at /workspace/myapp/lib/db/src/db/database.cr 49:7
0x4abfb5: *DB::Database::new<DB::Driver+, URI>:DB::Database at /workspace/myapp/lib/db/src/db/database.cr 42:5
0x4a4564: *DB::build_database:DB::Database at /workspace/myapp/lib/db/src/db.cr 122:5
0x4a453e: *DB::build_database:DB::Database at /workspace/myapp/lib/db/src/db.cr 118:5
0x458ef9: ??? at /workspace/myapp/lib/db/src/db.cr 108:5
0x469679: main at /opt/crystal/src/main.cr 12:15
0x7f713c399830: __libc_start_main at ??
0x457dd9: _start at ??
0x0: ??? at ??

fields too many ? bug

when fields num too many , about 23 。then

Unexpected EOF (Exception)
0x5f59ef: *MySql::ReadPacket#read_byte!:UInt8 at /home/lgphp/myapp/crystalapp/duobaocwgl/lib/mysql/src/mysql/read_packet.cr 37:18
0x5f5c81: *MySql::ReadPacket#read_byte_array<Int32>:Array(UInt8) at /home/lgphp/myapp/crystalapp/duobaocwgl/lib/mysql/src/mysql/read_packet.cr 130:9
0x5f6531: *MySql::ReadPacket#read_blob:Slice(UInt8) at /home/lgphp/myapp/crystalapp/duobaocwgl/lib/mysql/src/mysql/read_packet.cr 95:5
0x676081: *MySql::Type::Blob::read<MySql::ReadPacket>:Slice(UInt8) at /home/lgphp/myapp/crystalapp/duobaocwgl/lib/mysql/src/mysql/types.cr 220:7
0x6736bb: *MySql::ResultSet#read:(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | Int8 | Slice(UInt8) | String | Time | Nil) at /home/lgphp/myapp/crystalapp/duobaocwgl/lib/mysql/src/mysql/result_set.cr 81:13

if fields have 22 , then program is runing


self.each do
            row = {} of String => DB::Any
      
            row["col"] = self.read(DB::Any)
            row["col2"] = self.read(DB::Any)
            row["col3"] = self.read(DB::Any)
            row["col4"] = self.read(DB::Any)
            row["col5"] = self.read(DB::Any)
            row["col6"] = self.read(DB::Any)
            row["col7"] = self.read(DB::Any)
            row["col8"] = self.read(DB::Any)
            row["col9"] = self.read(DB::Any)
            row["col10"] = self.read(DB::Any)
            row["col11"] = self.read(DB::Any)
            row["col12"] = self.read(DB::Any)
            row["col13"] = self.read(DB::Any)
            row["col14"] = self.read(DB::Any)
            row["col15"] = self.read(DB::Any)
            row["col16"] = self.read(DB::Any)
            row["col17"] = self.read(DB::Any)
            row["col18"] = self.read(DB::Any)
            row["col19"] = self.read(DB::Any)
            row["col20"] = self.read(DB::Any)
            row["col21"] = self.read(DB::Any)
            row["col22"] = self.read(DB::Any)
            row["col23"] = self.read(DB::Any)

          rows << row
        end

support TINYINT(1)

I think that's what generates this anyway:

not supported (Exception)
[4387954258] *CallStack::unwind:Array(Pointer(Void)) +82
[4387954161] *CallStack#initialize:Array(Pointer(Void)) +17
[4387954120] *CallStack::new:CallStack +40
[4387860777] *raise:NoReturn +25
[4387860737] *raise:NoReturn +17
[4389068958] *MySql::Type+@mysql::Type::read:NoReturn +30
[4389072710] *MySql::ResultSet#read:(Float32 | Float64 | Int32 | Int64 | Slice(UInt8) | String | Time | Nil) +1238

(the current error message doesn't say "what" isn't supported, as it were).

Inconsistent return value types

The data type of the return value seems to be dependent on the context it the value originates from. For example:

# Where value1 is `INT` column
db.scalar("select value1 from datapoints limit 1").as(Int32) # => Correct Int32

# Where value2 is `BIGINT` column
db.scalar("select value2 from datapoints limit 1").as(Int64) # => Correct Int64

db.scalar("select 100 - 75").as(Int32) # => Error, cannot cast Int64 to Int32

db.scalar("select sum(value1) from datapoints").as(Int64) # => Error, cannot cast Float64 to Int64

Shouldn't select 100 - 75 be returned as an Int32 and the last query be an Int64 in this case since the sum is greater than the max of Int32 and are all integers.

It seems it will return an Int64 on any select that involves math as an Int64. However i'm not sure this is by design or unintentional...

This makes it harder to work with aggregations, since the return type is going to be different than the Postgres shard would return.

Queries from multiple spawns crash

When I want to query from different spawns using the same connection pool, it crashes for mysql when queries run at the same time. However this works fine for postgres.

require "pg"
require "mysql"
require "db"

DB.open("postgres://localhost/postgres") do |db|

  spawn do
    db.query("select 'foo', pg_sleep(1)") do |rs|
      rs.each do
        foo =  rs.read(String)
        timer =  rs.read(Bytes)
      end
    end
    pp "PG foo"
  end

  spawn do
    db.query("select 'bar'") do |rs|
      rs.each do
        bar =  rs.read(String)
      end
    end
    pp "PG bar"
  end
  sleep 2
end

DB.open("mysql://localhost") do |db|
  spawn do
    db.query("select 'foo', sleep(1)") do |rs|
      rs.each do
        foo =  rs.read(String)
        timer =  rs.read(Bytes)
        pp foo, timer
      end
      puts "called 1"
    end
    puts "Not called"
  end
  spawn do
    db.query("select 'bar'") do |rs|
      rs.each do
        bar =  rs.read(String)
        pp bar
      end
      puts "called 2"
    end
    puts "called 3"
  end
  sleep 2
end

Output:

"PG bar" # => "PG bar"
"PG foo" # => "PG foo"
bar # => "bar"
called 2
called 3
Unhandled exception in spawn:
Closed stream (IO::Error)
0x10943c945: *CallStack::unwind:Array(Pointer(Void)) at ??
0x10943c8e1: *CallStack#initialize:Array(Pointer(Void)) at ??
0x10943c8b8: *CallStack::new:CallStack at ??
0x10942dfd1: *raise<IO::Error>:NoReturn at ??
0x1094e9196: *TCPSocket+@IO#check_open:Nil at ??
0x1094e902b: *TCPSocket+@IO::Buffered#read<Slice(UInt8)>:Int32 at ??
0x1094e8f75: *TCPSocket+@IO#read_fully?<Slice(UInt8)>:(Int32 | Nil) at ??
0x1094e8ec3: *TCPSocket+@IO#read_fully<Slice(UInt8)>:Int32 at ??
0x1094c1584: *MySql::ReadPacket#initialize<TCPSocket+, MySql::Connection>:UInt8 at ??
0x1094c150b: *MySql::ReadPacket::new<TCPSocket+, MySql::Connection>:MySql::ReadPacket at ??
0x1094e187c: *MySql::Connection#build_read_packet:MySql::ReadPacket at ??
0x1095212f0: *MySql::ResultSet#move_next:Bool at ??
0x109521a8e: *MySql::ResultSet#do_close:Nil at ??
0x109524922: *DB::ResultSet+@DB::Disposable#close:(Bool | Nil) at ??
0x10943acb0: ~procProc(Nil)@worksheets/test.cr:29 at ??
0x1094559e4: *Fiber#run:(IO::FileDescriptor | Nil) at ??
0x109433899: ~proc2Proc(Fiber, (IO::FileDescriptor | Nil))@/usr/local/Cellar/crystal-lang/0.22.0/src/fiber.cr:29 at ??

Support for MariaDB?

Hello folks,

Recently had the need to migrate from MySQL 5.7 to MariaDB (10.3 at first, 10.5 at some point), and wanted to confirm crystal-mysql adapter will work against it.

When running specs, encountered only 2 failures:

  1) as a db select 1 as bigint as literal

       MySql::ResultSet#read returned a Int32. A Int64 was expected. (Exception)
         from lib/db/src/db/result_set.cr:80:7 in 'read'
         from /spec.cr:5:3 in 'assert_single_read'
         from lib/db/src/spec.cr:164:13 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/example.cr:33:16 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:18:23 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:330:7 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:18:23 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:147:7 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/dsl.cr:274:7 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/crystal/main.cr:45:14 in 'main'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/crystal/main.cr:119:3 in 'main'
         from __libc_start_main
         from _start
         from ???


  2) as a db select -1 as bigint as literal

       MySql::ResultSet#read returned a Int32. A Int64 was expected. (Exception)
         from lib/db/src/db/result_set.cr:80:7 in 'read'
         from /spec.cr:5:3 in 'assert_single_read'
         from lib/db/src/spec.cr:164:13 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/example.cr:33:16 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:18:23 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:330:7 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:18:23 in 'internal_run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/context.cr:147:7 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/spec/dsl.cr:274:7 in '->'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/primitives.cr:255:3 in 'run'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/crystal/main.cr:45:14 in 'main'
         from /home/luis/.asdf/installs/crystal/1.0.0/share/crystal/src/crystal/main.cr:119:3 in 'main'
         from __libc_start_main
         from _start
         from ???

Before I commit some time to this, I would like to know if MariaDB should be considered supported (given the current MySQL 8 situation about authentication plugins) and if so, then I could contribute the required fixes.

Thank you in advance.
❤️ ❤️ ❤️

Cannot read query_one result as Bool

require "mysql"

DB.open "mysql://user:pass@localhost:3306/test" do |db|
  pp db.query_one "SELECT EXISTS(SELECT 1 FROM articles WHERE id = 1)", as: Bool
end

Unhandled exception: cast from Int64 to Int8 failed, at ~/dev/git/granite/lib/mysql/src/mysql/result_set.cr:92:28:92 (TypeCastError)
  from lib/mysql/src/mysql/result_set.cr:0:28 in 'read'
  from lib/db/src/db/query_methods.cr:116:9 in 'query_one:as'
  from src/granite.cr:14:6 in '__crystal_main'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:97:5 in 'main_user_code'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:86:7 in 'main'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:106:3 in 'main'
  from __libc_start_main
  from _start
  from ???

Where the table looks like:

id	articlebody
1	The Article Body

Unprepared statements reading strings is broken

I was trying to reduce the amount of prepared statements in order to work around this issue
crystal-lang/crystal-db#60

I ran into bugs with using unprepared statements. I added the following spec and it fails.
If you change db.unprepared.query to db.query it uses a prepared statement and that works fine.

  it "allows unprepared statement queries" do |db|
    db.exec %(create table if not exists a (i int not null, str text not null);)
    db.exec %(insert into a (i, str) values (23, "bai bai");)

    2.times do |i|
      DB.open db.uri do |db|
        begin
          db.unprepared.query("SELECT i, str FROM a WHERE i = 23") do |rs|
            rs.each do
              rs.read(Int32).should eq 23
              rs.read(String).should eq "bai bai"
            end
          end
        rescue e
          fail("Expected no exception, but got \"#{e.message}\"")
        end
      end
    end
  end

Failures:

  1) as a db allows unprepared statement queries
     Failure/Error: fail("Expected no exception, but got \"#{e.message}\"")

       Expected no exception, but got "Expected: "bai bai"
            got: "\u0007b""

     # spec/db_spec.cr:194

Invalid row data

Hi,

I was having some problems with query results when using specific data.
When reading a table with 1.3 million records it was retrieving all the information correctly when using a limited amount of data per row. When the data per row was larger, it returned the wrong results.

It was able to trace it back to the Packet reader

Currently is says:

  def read(slice : Bytes)
    return 0 unless @remaining > 0
    read_bytes = @io.read(slice)
    @remaining -= read_bytes
    read_bytes
  rescue IO::EOFError
    raise DB::ConnectionLost.new(@connection)
  end

When I change the @io.read(slice) to @io.read_fully(slice) like below, my query run correctly.

  def read(slice : Bytes)
    return 0 unless @remaining > 0
    read_bytes = @io.read_fully(slice)
    @remaining -= read_bytes
    read_bytes
  rescue IO::EOFError
    raise DB::ConnectionLost.new(@connection)
  end

I'm not sure if the fix is the correct fix.

Random exception by reading wrong position

When I read many records (10,000 rows with 20 fields), crystal-mysql randomly raise exceptions when I call rs.read(). Stacktrace also changes randomly from the following 2 patterns in my case.

Invalid time (ArgumentError)
0x505cc7: *CallStack::unwind:Array(Pointer(Void)) at ??
0x5cfec4: initialize at /opt/crystal/src/time.cr 133:7
0x5d05f5: initialize at /opt/crystal/src/time.cr 125:3
0x5d0559: *Time::new<Int32, Int32, Int32, Int32, Int32, Int32>:Time at /opt/crystal/src/time.cr 500:5
0x64b4b2: read at /vagrant/workspace/mycode/lib/mysql/src/mysql/types.cr 178:14
0x648fb4: read at /vagrant/workspace/mycode/lib/mysql/src/mysql/result_set.cr 83:13
0x64a5aa: read at /vagrant/workspace/mycode/lib/db/src/db/result_set.cr 79:15

Invalid Int32:  (ArgumentError)
0x505cc7: *CallStack::unwind:Array(Pointer(Void)) at ??
0x525a4b: to_i32 at /opt/crystal/src/string.cr 418:5
0x52593c: to_i at /opt/crystal/src/string.cr 319:5

I tried to find the reason and it seems that sometimes crystal-mysql forgets to read some bytes from IO and reads bytes from wrong place.
For example, after fetching a record with id = 221, it then try to fetch the content of the row of id = 222. But then rs.read(Int32) returns 56832 which equals 222 * 256 which indicates one byte shift of reading position.
In another case there was two bytes shift (1975 expected but got 129433683 = 1975 * 65536 + 83).
If this happens it soon ends up with the exceptions above.

It starts to happen at somewhat random place of the response stream.
Sometimes it happens at 222th row, sometimes at 1975th row.
But it's not totally at random. If I run my code 10 times,
5 times at 222th row, 2 times at 1975th row, ...

I suspected GC but even if I GC.disable, the problem still occurs.

$ crystal -v
Crystal 0.23.1 [e2a1389] (2017-07-13) LLVM 3.8.1
$ uname -a 
Linux ubuntu-xenial 4.4.0-87-generic #110-Ubuntu SMP Tue Jul 18 12:55:35 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Any help? I continue to find the reason but if anybody suggest me anything I'm willing to do (printf debug in io.cr, for example).
Maybe it is related to #39 .

If I limit the number of rows to 10, it does not happen in my environment.
So my workaround is to divide 10,000 records into 1,000 sql to read.

Add support for bit(1) reading

Was reading over #12 but am not sure if it's related :P

I have a bit(1) field in MYSQL called hardcore:

The query_all:
db.query_all "select hardcore from rpg_characters where user_id = ? ", client.user_id, as: {Bool}

The error:
not supported read

I've tried UInt8, Int8, and BitArray.new(1) with no prevail. I'm not sure if this is a suggestion (if it can be done already, then please close). But figured I'd make an issue to shed some light on it. Thanks for reading

Error with shards loading v0.1.2

Hello,
I got this error on the README example:

Error in ./libs/db/db/result_set.cr:70: abstract `def DB::ResultSet#read()` must be implemented by MySql::ResultSet

    abstract def read

My shard.yml:

dependencies:
  mysql:
    github: crystal-lang/crystal-mysql

But it does work when I specify the branch master in shard.yml:

dependencies:
  mysql:
    github: crystal-lang/crystal-mysql
    branch: master

The implementation of DB::ResultSet differs from master to tag 0.1.2. In the version cloned by shards (tag 0.1.2) DB::ResultSet#read() is not implemented.

Is it just me or did I miss something ?

Thanks

mariadb 10.0.22 problem

hello,

I have issue using the driver, as I run the readme code exemple it work, but when I run that:

require "mysl"
DB.open "mysql://user:pass@host/rtx" do |db|
  sql ="set names utf8"
  db.exec sql

  db.exec "drop table if exists stx"

  db.exec "CREATE TABLE `stx` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(30)  NOT NULL DEFAULT '0',
  `description` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) "

  sql ="select id from stx limit 1"
  db.query sql do |row|
        puts "#{row.column_name(0)}"

        puts row.read(Int32)

  end
end

I have :

id
Nil assertion failed (Exception)
[4491223] *CallStack::unwind:Array(Pointer(Void)) +87
[4491114] *CallStack#initialize:Array(Pointer(Void)) +10
[4491066] *CallStack::new:CallStack +42
[4455400] *raise<Exception>:NoReturn +24
[4455374] ???
[4541680] *Nil#not_nil!:NoReturn +16
[4663121] *MySql::ResultSet#read?<Int32:Class>:(Int32 | Nil) +65
[4662980] *MySql::ResultSet +36
[4433049] ???
[4468553] main +41
[140332662660805] __libc_start_main +245
[4428537] ???
[0] ???

using Crystal 0.18.7 68783f1 on ubuntu 14.4, mariadb 10.0.22

Generic `Exception`s raised in `MySql::Connection`

Working with this shard there are specific exceptions I'd like to rescue from, but it looks like for the most part Mysql::Connection raises generic Exceptions.

I'd like to throw out the idea of using more specific exception types and see what the maintainer and/or community thinks. I'm also willing to open a PR for the change if anyone's interested.

values of UInt8 data types get converted to 125

Ran into this little quirk today. i checked with gitter, and pretty sure it's a bug, not entirely sure though

query:

        items2 = db.query_all "select i_hp from rpg_user_items where rpg_character_id = ? and user_id = ? and in_stash = 0", client.selected_characterid, client.user_id, as: {UInt8}
          puts items2[0]

storage type in mysql, set as tinyint, 255, unsigned

if you don't set the length to 255, but keep it unsigned, it will give you a read error:

MySql::ResultSet#read returned a Int8. A UInt8 was expected.

if you set the length to 255, that error is gone. however, now your value gets converted to 125

thanks for reading! special thanks to @dscottboggs_gitlab in gitter for the help

connection failure to mysql 8

Exception: packet 254 not implemented (Exception)
from lib/mysql/src/mysql/connection.cr:29:9 in 'initialize'
from lib/mysql/src/mysql/connection.cr:4:3 in 'new'

connecting to mysql 8 instance...

packet 254 not implemented -- still struggling with

I know that this is not a new issue, but I'm trying to connect to a MySQL 8 server, which has been set to use legacy (v5.7) authentication. This is working in other languages that have an issue with the new authentication process (e.g. Nim), but I can't get it to work in Crystal. I've tried it on both OSX and Ubuntu. No difference.

I've tried the published solutions like CREATE/ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword'; and the user is definitely using the native_password auth.

Short of switching languages or databases, can anyone suggest something to try?

Thanks in advance for any suggestions!

[Exception.txt] (https://github.com/crystal-lang/crystal-mysql/files/12506665/Exception.txt)

Saving Emoji's

Emojis, and other some other utf8 characters need utf8mb4 character set to be used in the connection. At the moment this looks like it may be hard coded as utf8_general_ci?

It's be nice to be able to configure this to allow for other encodings/collations.

Release 0.8.0 missing

Hi @bcardiff Can you tag the 0.8.0 release? thx!

Broken again

Crystal 20.3 broken again :(

"Client does not support authentication protocol requested by server; consider upgrading MySQL client (Exception)"

I get the below error when I try to run the example test code. I am not sure what is causing this. Any help would be greatly appreciated. Just as a note, I have the latest version of MySql.

Missing block in decl_type *Blob ?

The query SELECT COALESCE(username, '') FROM radcheck was causing the exception below:

Unhandled exception: not supported read (Exception)
  from lib/mysql/src/mysql/types.cr:86:5 in 'read'
  from lib/mysql/src/mysql/result_set.cr:85:10 in 'read'
  from lib/db/src/db/result_set.cr:79:15 in 'read'
  from src/user_radius.cr:37:8 in 'read'
  from src/user.cr:32:11 in 'read'
  from src/command_auth_copy.cr:9:13 in 'run'
  from src/crystalville.cr:48:3 in '__crystal_main'
  from /snap/crystal/600/share/crystal/src/crystal/main.cr:110:5 in 'main_user_code'
  from /snap/crystal/600/share/crystal/src/crystal/main.cr:96:7 in 'main'
  from /snap/crystal/600/share/crystal/src/crystal/main.cr:119:3 in 'main'
  from __libc_start_main
  from _start
  from ???

The same query without COALESCE worked: SELECT username FROM radcheck.

Copying the block in types.cr decl_type Blob to the decl_type MediumBlob made it work:

  decl_type MediumBlob, 0xfau8, ::Bytes do
    def self.write(packet, v : ::Bytes)
      packet.write_blob v
    end

    def self.write(packet, v : ::StaticArray(T, N)) forall T, N
      packet.write_blob v.to_slice
    end

    def self.read(packet)
      packet.read_blob
    end

    def self.parse(str : ::String)
      str.to_slice
    end
  end

Versions:

Crystal 0.36.1 [c3a3c1823] (2021-02-02)

LLVM: 10.0.0
Default target: x86_64-unknown-linux-gnu

10.3.23-MariaDB-0+deb10u1

shard.lock:
  mysql:
    git: https://github.com/crystal-lang/crystal-mysql.git
    version: 0.13.0

CREATE TABLE `radcheck` (
  `username` text NOT NULL DEFAULT ''

I'm new to Crystal and don't know anything about the MySQL protocol, but why only type Blob has that block?

shards issue

Not sure why but I'm getting this error in my ci.

Updating https://github.com/crystal-lang/crystal-mysql.git
Error resolving db (*, ~> 0.2.0)

Also happens locally when I run crystal deps

Shards Issue : install does not work

Crystal 0.30.0 (2019-08-01)

LLVM: 8.0.0
Default target: x86_64-apple-macosx

dependencies:
  db:
    github: crystal-lang/crystal-db
  sqlite3:
    github: crystal-lang/crystal-sqlite3  
  mysql:
    github: crystal-lang/crystal-mysql

Fetching https://github.com/crystal-lang/crystal-mysql.git
git fetch --all --quiet
git tag --list --column=never
versions: 0.7.0, 0.6.0, 0.5.1, 0.5.0, 0.4.0, 0.3.3, 0.3.2, 0.3.1, 0.3.0, 0.2.2, 0.2.1, 0.2.0, 0.1.2, 0.1.1, 0.1.0
git ls-tree -r --full-tree --name-only v0.7.0 -- shard.yml
git show v0.7.0:shard.yml
git ls-tree -r --full-tree --name-only v0.7.0 -- shard.yml
git show v0.7.0:shard.yml
git ls-tree -r --full-tree --name-only v0.6.0 -- shard.yml
git show v0.6.0:shard.yml
**Error resolving db (*, ~> 0.6.0, ~> 0.5.0)**

Error retrieving data

I'm reading many rows from database table, but ALWAYS stop execution in this record.

my crystal code:

require "mysql"
require "yaml"

config = YAML.parse File.read "config.yml"

db = DB.open config["database"].to_s

index = 0

rs = db.query "SELECT * FROM destinations"

rs.each do
	puts	"#{ index += 1 } => #{ rs.read() }, #{ rs.read() }, #{ rs.read() }, #{ rs.read() }, #{ rs.read() }, #{ rs.read() }, #{ rs.read() }"
end

rs.close

db.close

Output:

1 => 10000, 10000, Andorra, 0.016224, 0.048672, 2017-07-13 18:14:08, 2017-07-13 18:17:51
2 => 10001, 10000, Andorra-Mobile, 0.1404, 0.4212, 2017-07-13 18:14:08, 2017-07-13 18:19:11
3 => 10002, 10004, Anguilla, 0.122616, 0.367848, 2017-07-13 18:14:08, 2017-07-13 18:17:51
4 => 10003, 10003, Antigua Barbuda, 0.248508, 0.745524, 2017-07-13 18:14:08, 2017-07-13 18:14:08
5 => 10004, 10010, Argentina, 0.02106, 0.06318, 2017-07-13 18:14:08, 2017-07-13 18:17:51
... omit verbose ...
351 => 10351, 10179, Portugal-Freephone, 0.01426, 0.04278, 2017-07-13 18:14:08, 2017-07-13 18:19:12
352 => 10352, 10179, Portugal-Mobile-Optimus, 0.012948, 0.038844, 2017-07-13 18:14:08, 2017-07-13 18:19:12
353 => 10353, 10179, Portugal-Mobile-Others, 0.01482, 0.04446, 2017-07-13 18:14:08, 2017-07-13 18:19:12
354 => 10354, 10179, Portugal-Mobile-TMN, 0.01482, 0.04446, 2017-07-13 18:14:08, 2017-07-13 18:19:12
355 => 10355, 10179, Portugal-Mobile-Vodafone, 0.012948, 0.038844, 2017-07-13 18:14:08, 2017-07-13 18:19:12
Invalid Float64: Portugal-OLO-1 (ArgumentError)
0x490a97: *CallStack::unwind:Array(Pointer(Void)) at ??
0x4aa477: to_f64 at /opt/crystal/src/string.cr 666:56
0x4ab6eb: to_f64 at /opt/crystal/src/string.cr 665:3
0x513cae: read at /home/javier/projects/crystal/facturalia-pms/lib/mysql/src/mysql/types.cr 190:7
0x513786: read at /home/javier/projects/crystal/facturalia-pms/lib/mysql/src/mysql/result_set.cr 81:13
0x4780b8: __crystal_main at /home/javier/projects/crystal/facturalia-pms/facturalia-pms.cr 24:78
0x48b229: main at /opt/crystal/src/main.cr 12:15
0x7f52a954eb45: __libc_start_main at ??
0x4767ed: ??? at ??
0x0: ??? at ??

I try to modify query to:
SELECT * FROM destinations WHERE id >= 10350
Now can see this record:

1 => 10350, 10179, Portugal, 0.002986, 0.008958, 2017-07-13 18:14:08, 2017-07-13 18:17:52
2 => 10351, 10179, Portugal-Freephone, 0.01426, 0.04278, 2017-07-13 18:14:08, 2017-07-13 18:19:12
3 => 10352, 10179, Portugal-Mobile-Optimus, 0.012948, 0.038844, 2017-07-13 18:14:08, 2017-07-13 18:19:12
4 => 10353, 10179, Portugal-Mobile-Others, 0.01482, 0.04446, 2017-07-13 18:14:08, 2017-07-13 18:19:12
5 => 10354, 10179, Portugal-Mobile-TMN, 0.01482, 0.04446, 2017-07-13 18:14:08, 2017-07-13 18:19:12
6 => 10355, 10179, Portugal-Mobile-Vodafone, 0.012948, 0.038844, 2017-07-13 18:14:08, 2017-07-13 18:19:12
7 => 10356, 10179, Portugal-OLO-1, 0.004858, 0.014574, 2017-07-13 18:14:08, 2017-07-13 18:19:12
8 => 10357, 10179, Portugal-OLO-2, 0.004858, 0.014574, 2017-07-13 18:14:08, 2017-07-13 18:19:12
9 => 10358, 10179, Portugal-Personal Number, 0.0138, 0.0414, 2017-07-13 18:14:08, 2017-07-13 18:19:12
10 => 10359, 10179, Portugal-Shared Cost, 0.0138, 0.0414, 2017-07-13 18:14:08, 2017-07-13 18:19:12
... omit verbose ...

Program end normally

crystal-db 0.4.0 release?

Hi @bcardiff. Will there be a release for 0.4.0 soon? I prefer not pointing to master.

MemoryIO error

Warning: MemoryIO is deprecated and will be removed after 0.20.0, use IO::Memory instead

how to connect to mysql via unix_socket?

BINARY and VARBINARY columns can only be read as string

Currently we are able to insert Bytes but when reading must specify read(String) not read(Bytes) as there will be an error otherwise. I believe it might be better to support reading a slice for these fields.

DB.open "mysql://root@localhost/test" do |db|
  db.exec "drop table if exists contacts"
  db.exec "create table contacts (name varchar(30), age int, btest VARBINARY(16))"
  db.exec "insert into contacts values (?, ?, ?)", "John Doe", 30, Bytes.new(3, 90_u8)

  args = [] of DB::Any
  args << "Sarah"
  args << 33
  args << Bytes.new(3, 90_u8)
  db.exec "insert into contacts values (?, ?, ?)", args

  puts "max age:"
  puts db.scalar "select max(age) from contacts" # => 33

  puts "contacts:"
  db.query "select name, age, btest from contacts order by age desc" do |rs|
    puts "#{rs.column_name(0)} (#{rs.column_name(1)}) (#{rs.column_name(2)})"
    # => name (age)
    rs.each do
      puts "#{rs.read(String)} (#{rs.read(Int32)}) (#{rs.read(Bytes)})"
      # => Sarah (33) (ZZZ)
      # => John Doe (30) (ZZZ)
    end
  end
end

max age:
33
contacts:
name (age) (btest)
MySql::ResultSet#read returned a String. A Slice(UInt8) was expected. (Exception)
from lib/db/src/db/result_set.cr:0:9 in 'read'
from src/dbtest.cr:28:57 in '__crystal_main'
from /usr/local/Cellar/crystal-lang/0.24.2_1/src/crystal/main.cr:11:3 in '_crystal_main'
from /usr/local/Cellar/crystal-lang/0.24.2_1/src/crystal/main.cr:112:5 in 'main_user_code'
from /usr/local/Cellar/crystal-lang/0.24.2_1/src/crystal/main.cr:101:7 in 'main'
from /usr/local/Cellar/crystal-lang/0.24.2_1/src/crystal/main.cr:135:3 in 'main'

Unable to exec with Int8 mixed with others DB::Any args

Extracted from crystal-lang/crystal-db#26 (comment)

Exhibit in v0.2.1

Support for secure connections (TLS)

Hello!

First want to thank you for working on the native protocol. I've compared the new version of the adapter against the old libmysql one and had incredible speedup on our scripted tasks! 😄

However, noticed that due the migration to native protocol, we lost the ability to use a secure connection (was hacky then, now not possible), which is essential when establishing connections between our app and the DB provider across regions when no VPN can be established (for example, when using Compose).

Was wondering if there are plans to integrate such support in the future?

Thank you in advance for your time
❤️ ❤️ ❤️

Binary strings

Having trouble reading binary strings, ie (column type text).

I can handle this in my application with text = String.new(bytes) but this is something which should be handled in the driver. Mysql returns text as type blob, not sure if there is a way to auto detect text?

If you have any pointers how to add this I have no problem taking a look.

Crystal 0.24

There's a Crystal 0.24 pre-release that introduces some breaking changes. Do you have any plans to update for these changes and maybe make a 0.24 branch available?

connection error

Using crystal 0.20.3 on a mac, sierra.

Getting this error

Error in src/app/database/database.cr:11: instantiating 'DB:Module#open(String)'

    @@db : DB::Database = DB.open(DBURL + "?max_pool_size=10&initial_pool_size=2&max_idle_pool_size=2")
                             ^~~~

in lib/db/src/db.cr:103: instantiating 'build_database(String)'

    build_database(uri)
    ^~~~~~~~~~~~~~

in lib/db/src/db.cr:117: instantiating 'build_database(URI)'

    build_database(URI.parse(connection_string))
    ^~~~~~~~~~~~~~

in lib/db/src/db.cr:121: instantiating 'DB::Database:Class#new(DB::Driver+, URI)'

    Database.new(driver_class(uri.scheme).new, uri)
             ^~~

in lib/db/src/db/database.cr:50: instantiating 'DB::Driver+#build_connection(DB::Database)'

        conn = @driver.build_connection(self).as(Connection)
                       ^~~~~~~~~~~~~~~~

in lib/mysql/src/mysql/driver.cr:3: instantiating 'MySql::Connection:Class#new(DB::Database)'

    MySql::Connection.new(db)
                      ^~~

in lib/mysql/src/mysql/connection.cr:22: instantiating 'read_packet(MySql::Protocol::HandshakeV10:Class)'

      handshake = read_packet(Protocol::HandshakeV10)
                  ^~~~~~~~~~~

in lib/mysql/src/mysql/connection.cr:57: instantiating 'read_packet()'

    read_packet do |packet|
    ^~~~~~~~~~~

in lib/mysql/src/mysql/connection.cr:47: instantiating 'build_read_packet()'

    packet = build_read_packet
             ^~~~~~~~~~~~~~~~~

in lib/mysql/src/mysql/connection.cr:65: instantiating 'MySql::ReadPacket:Class#new(TCPSocket+, MySql::Connection)'

    ReadPacket.new(@socket, self)
               ^~~

instance variable '@remaining' of MySql::ReadPacket must be Int32, not Nil

Error: instance variable '@remaining' is initialized inside a begin-rescue, so it can potentially be left uninitialized if an exception is raised and rescued

I can fix it by initilizing the variables inside initialize of read_packet.cr ie.

  def initialize(@io : IO, @connection : Connection)
    @remaining = 0
    @length = 0
    @seq = 0u8
    begin
      header = uninitialized UInt8[4]
      io.read_fully(header.to_slice)
      @length = @remaining = header[0].to_i + (header[1].to_i << 8) + (header[2].to_i << 16)
      @seq = header[3]
    rescue IO::EOFError
      raise DB::ConnectionLost.new(@connection)
    end
  end

Not done a pull request as your tests appear to be passing. Not sure why mine won't compile with the same version of crystal.

Support for MySQL MediumInt (Int24, int3)

Looks like we need Int24 type and read/write to mysql int3 (MEDIUMINT).

Unexpected EOF on ubuntu aarch64

I host my mysql server on ubuntu aarch64, and when i try connect it throws:

backend:dev: Unhandled exception: Unexpected EOF (Exception)
backend:dev:   from lib/mysql/src/mysql/read_packet.cr:44:18 in 'read_byte!'
backend:dev:   from lib/mysql/src/mysql/read_packet.cr:69:5 in 'read_int'
backend:dev:   from lib/mysql/src/mysql/packets.cr:13:7 in 'read'
backend:dev:   from lib/mysql/src/mysql/connection.cr:70:14 in 'read_packet'
backend:dev:   from lib/mysql/src/mysql/connection.cr:22:19 in 'initialize'
backend:dev:   from lib/mysql/src/mysql/connection.cr:4:3 in 'new'
backend:dev:   from lib/mysql/src/mysql/driver.cr:3:5 in 'build_connection'
backend:dev:   from lib/db/src/db/database.cr:57:9 in '->'
backend:dev:   from lib/db/src/db/pool.cr:218:27 in 'build_resource'
backend:dev:   from lib/db/src/db/pool.cr:47:34 in 'initialize'
backend:dev:   from lib/db/src/db/pool.cr:40:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
backend:dev:   from lib/db/src/db/database.cr:56:15 in 'initialize'
backend:dev:   from lib/db/src/db/database.cr:49:5 in 'new'
backend:dev:   from lib/db/src/db.cr:155:5 in 'build_database'
backend:dev:   from lib/db/src/db.cr:151:5 in 'build_database'
backend:dev:   from lib/db/src/db.cr:119:5 in 'open'
backend:dev:   from src/hypilus.cr:16:14 in '~Hypilus::Database:init'
backend:dev:   from src/hypilus.cr:17:3 in '__crystal_main'
backend:dev:   from /usr/lib/crystal/crystal/main.cr:115:5 in 'main_user_code'
backend:dev:   from /usr/lib/crystal/crystal/main.cr:101:7 in 'main'
backend:dev:   from /usr/lib/crystal/crystal/main.cr:127:3 in 'main'
backend:dev:   from /usr/lib/libc.so.6 in '??'
backend:dev:   from /usr/lib/libc.so.6 in '__libc_start_main'
backend:dev:   from ../sysdeps/x86_64/start.S:117 in '_start'
backend:dev:   from ???

tried with mysql and also mariadb

call Store procedure

how to call mysql store procedure and get results ?