CockroachDB

[Wulfklaue]

Testing the Crystal PG driver with CockroachDB ( uses a PostgreSQL compatible interface ).

After a series of tests, it always comes down to:

 (DB::ConnectionRefused)
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/pg/src/pg/connection.cr:16:9 in 'initialize'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/pg/src/pg/connection.cr:7:5 in 'new'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db/pool.cr:17:34 in 'initialize'
  from 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+)
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db/database.cr:50:7 in 'initialize'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db/database.cr:43:5 in 'new'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db.cr:139:5 in 'build_database'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db.cr:135:5 in 'build_database'
  from /mnt/d/Programming/Crystal/PostgreSQL/lib/db/src/db.cr:103:5 in 'open'
  from /mnt/d/Programming/Crystal/PostgreSQL/src/www.cr:7:1 in '__crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:11:3 in '_crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:112:5 in 'main_user_code'
  from /usr/share/crystal/src/crystal/main.cr:101:7 in 'main'
  from /usr/share/crystal/src/crystal/main.cr:135:3 in 'main'

Different combination keep resulting in the same error.

#db = DB.open "postgres://root@xxx.xxx.xxx.xxx:26257/startrek?sslmode=disable"
#db = DB.open "postgres://xxx.xxx.xxx.xxx:26257/startrek?sslmode=disable"
#db = DB.open "postgres://xxx.xxx.xxx.xxx:26257/"
db = DB.open "postgres://xxx.xxx.xxx.xxx:26257/startrek"

Note: compiled under Windows WSL but run on a online server that has CockroachDB.

Same tests under PHP and Go results in active and working connections.

Go:

	connStr := "host=xxx.xxx.xxx.xxx: port=26257 dbname=startrek  sslmode=disable"
	db, err := sql.Open("postgres", connStr)

PHP:

$dbconn = pg_pconnect ("host=xxx.xxx.xxx.xxx: dbname=startrek port=26257 sslmode=disable") or die('Could not connect: ' . pg_last_error());

Any clue as what may be wrong?

[RX14]

By adding an inspect_with_backtrace in the right place, we get the real exception:

Expected PQ::Frame::BackendKeyData but got PQ::Frame::ReadyForQuery(@transaction_status=Idle) (Exception)
  from lib/pg/src/pq/connection.cr:290:7 in 'expect_frame'
  from lib/pg/src/pq/connection.cr:288:5 in 'expect_frame'
  from lib/pg/src/pq/connection.cr:232:18 in 'connect'
  from lib/pg/src/pg/connection.cr:14:9 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from 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+)
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from test.cr:3:1 in '__crystal_main'
  from /usr/lib/crystal/crystal/main.cr:11:3 in '_crystal_main'
  from /usr/lib/crystal/crystal/main.cr:112:5 in 'main_user_code'
  from /usr/lib/crystal/crystal/main.cr:101:7 in 'main'
  from /usr/lib/crystal/crystal/main.cr:135:3 in 'main'
  from __libc_start_main
  from _start
  from ???

Looks like the backendkeydata frame data isn't actually really used by crystal-pg, and it's optional according to the spec, so it should be possible just to ignore if the frame isn't sent.

I just tested commenting out these lines and it connected to cockroach and worked just fine.

[Wulfklaue]

@RX14 , mind telling where one adds the inspect_with_backtrace? Still seeing issues even with the code in PQ connect disabled.

[RX14]

@Wulfklaue add it here: https://github.com/will/crystal-pg/blob/master/src/pg/connection.cr#L16 before the raise

rescue ex
  ex.inspect_with_backtrace(STDERR)

[Wulfklaue]

Found the issue...

sslmode disable not supported (ArgumentError)

Thanks for the code.

rescue ex
  ex.inspect_with_backtrace(STDERR)

Had already written it but was not able figure out why i was not getting the backtrace. Forgot the "STDERR". 👎 Note: Manual really is not useful on this part.

Its working perfectly now. Thanks for the help.

[RX14]

@Wulfklaue yeah just leave off sslmode, the default for crystal-pg is to try with and fall back to without ssl. There's no mode to force disable ssl at the client-side (you can still turn it off at the server) currently. Should be easy to add though (simple patch).