Error while inserting db record in Nakama 2.6.0

Local Setup
1

Getting the below error when I upgraded my local nakama server from v 2.5.1 to latest v 2.6.0.
Cockroach db - build: CCL v2.1.5 @ 2019/02/11 21:56:18 (go1.10.7)

{"level":"warn","ts":"2019-08-07T19:54:20.435+0530",
"msg":"Stopping match after error from match_loop execution","mid":"f674e5e8-acd1-4248-a71f-12ee9aa09581","tick":54,
"error":"D:\\UnityProjects\\data\\modules\\db\\match_data.lua:108: sql exec error: cannot convert 1 to Int8\nstack traceback:\n\t[G]: in function 'sql_exec'\n\t
D:\\UnityProjects\\data\\modules\\db\\match_data.lua:108: in function 'write_match_rounds_info'\n\t
D:\\UnityProjects\\data\\modules\\util\\game_logs.lua:628: in function 'write_game_rounds_info'\n\t
D:\\UnityProjects\\data\\modules\\util\\game_logs.lua:237: in function 'update_rounds_log_data'\n\t
D:\\UnityProjects\\data\\modules\\match_handlers\\turn_handler.lua:49: in function 'play_poker_blinds'\n\t
D:\\UnityProjects\\data\\modules\\match_handlers\\match_data_handler.lua:62: in function 'update_to_play_state'\n\t
D:\\UnityProjects\\data\\modules\\match_handlers\\match_loop.lua:71: in function 'handle_state_updates'\n\t
D:\\UnityProjects\\data\\modules\\match_handlers\\match_loop.lua:28: in function 'process_game_state'\n\t
D:\\UnityProjects\\data\\modules\\match_handlers\\poker_match_handler.lua:128: in main chunk\n\t[G]: ?"}

What can be the issue with cannot convert 1 to Int8

The field of my database table which will store value 1 is this -
game_no INT NOT NULL,

It’s working fine with 2.5.1 but getting this error in 2.6.0

2

Nakama 2.6.0 switched to a faster database driver but some differences in behaviour are possible.

Can you share the full schema for the table and the full query and parameters you’re trying to insert?

3

@zyro here’s the table schema -

root@127.0.0.1:26229/nakama> show create table cs_match_rounds_info;
       table_name      |                                                           create_statement
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------+
  cs_match_rounds_info | CREATE TABLE cs_match_rounds_info (
                       |     match_id STRING(128) NOT NULL,
                       |     game_no INT NOT NULL,
                       |     round_no INT NOT NULL,
                       |     user_id STRING(128) NOT NULL,
                       |     custom_id STRING(128) NOT NULL,
                       |     name STRING(128) NOT NULL,
                       |     amount_in_hand INT8 NOT NULL,
                       |     action STRING(50) NOT NULL,
                       |     amount INT8 NOT NULL,
                       |     pot_amount INT8 NOT NULL,
                       |     "timestamp" TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
                       |     CONSTRAINT "primary" PRIMARY KEY ("timestamp" ASC),
                       |     CONSTRAINT fk_match_id_ref_cs_match_tables FOREIGN KEY (match_id) REFERENCES cs_match_tables (match_id) ON DELETE CASCADE,
                       |     INDEX match_id_game_no_round_no_user_id_custom_id_idx (match_id ASC, game_no ASC, round_no ASC, user_id ASC, custom_id ASC),
                       |     FAMILY "primary" (match_id, game_no, round_no, user_id, custom_id, name, amount_in_hand, action, amount, pot_amount, "timestamp")
                       | )
(1 row)

Time: 85.1321ms

Here’s the Query and parameters I’m trying to insert -

local query = [[insert into cs_match_rounds_info (match_id, game_no, round_no, user_id, custom_id, name, amount_in_hand, action, amount, pot_amount) 
					values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING NOTHING]]
	local parameters = {match_id, game_no, round_no, user_id, custom_id, name, amount_in_hand, action, amount, pot_amount}
    local status , error = pcall(nk.sql_exec, query, parameters)

Inserting something like this gives error on Nakama 2.6.0, (actual row values inserted with Nakama 2.5.1) -

062f0a9c-9216-4cca-8d94-acd0ec2e0bae.nakama |       1 |        1 | LqNnzZDAfQ     | f02bc46cca10fe2565b4b3b40c53ac904d5e0700 | Astha Mannan     |             38 | CALL   |    -10 |         15
4

Also, this Issue is happening with the latest version of cockroach db v19.1.3 on Nakama v2.6.0

5

According to the Postgres spec, INT8 is an extension which may not work well with different database engines / drivers:

https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-INT

I’d suggest trying out BIGINT which is the same as INT64, and the same as INT8. Moreover potentially a cast in your custom query could be useful like this:

[[insert into cs_match_rounds_info (match_id, ...) values($1::BIGINT,...) RETURNING NOTHING]]
6

@mofirouz, same issue with the cast to BIGINT.
I even changed the type of these fields from INT to BIGINT in table schema, but no luck.

7

The issue was SQL parameter parsing when passing your query arguments to the database driver. The fix has been committed to master and will be part of the next release.

Thanks for reporting this!

1 Like