crystal-lang / crystal-mysql Goto Github PK
View Code? Open in Web Editor NEWMySQL connector for Crystal
License: MIT License
MySQL connector for Crystal
License: MIT License
(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 ??
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 |
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.
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
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
how to connect to mysql via unix_socket?
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
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 ??
Hi @bcardiff. Will there be a release for 0.4.0 soon? I prefer not pointing to master.
Is it safe to use crystal-mysql against sql injection etc?
or how can i filter the input?
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
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.
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
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.
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.
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...
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
Crystal 20.3 broken again :(
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.
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
❤️ ❤️ ❤️
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.
❤️ ❤️ ❤️
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
Extracted from crystal-lang/crystal-db#26 (comment)
Exhibit in v0.2.1
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'
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)**
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!
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.
how to call mysql store procedure and get results ?
Hi @bcardiff Can you tag the 0.8.0 release? thx!
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?
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
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.
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).
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 Exception
s.
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.
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.
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
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
Warning: MemoryIO is deprecated and will be removed after 0.20.0, use IO::Memory instead
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.
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)
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
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
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.
Looks like we need Int24 type and read/write to mysql int3 (MEDIUMINT).
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?
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.