Hi I can’t self host the cockroach database on my pc so I created a free cockroachdb instance on cockroach cloud but when I try to start nakama with this command: nakama migrate up --database.address "myusername:mypassword@mylink:myport/defaultdb?sslmode=verify-full&sslrootcert=cc-ca.crt&options=options=--cluster=testcluster" (with my own data)
it fails with this error:
{"level":"fatal","ts":"2021-04-12T18:19:23.053+0200","caller":"migrate/migrate.go:148","msg":"Error pinging database","error":"ERROR: cannot access virtual schema in anonymous database (SQLSTATE 42704)"}
Thank you for your help
P.S if I only try to run nakama --database.address "myusername:mypassword@mylink:myport/defaultdb?sslmode=verify-full&sslrootcert=cc-ca.crt&options=options=--cluster=testcluster" (with my own data) i will get {"level":"fatal","ts":"2021-04-12T18:26:51.343+0200","caller":"migrate/migrate.go:80","msg":"DB schema outdated, run nakama migrate up","migrations":5}
@andrea-miele You’ll need to adjust the container start command to not run the "migrate" subcommand at the same time. It requires database user privileges to set up the schema for the Nakama database.
Based on the restrictions in that cloud environment you will have to run the schema migration manually with the right privileges for the database user and can then run the game server start up with a more restrictive user.
Hope that helps.
@novabyte I can access the database and create users ,tables and databases, i can grant all permission to a user how do I do the migration manually?
P.S.:I tried to import the sql files into my database but it always shows “db schema outdated please run nakama migrate up”. Now if I run nakama migrate up it shows “msg”:“Error pinging database”,“error”:“EOF”}
@andrea-miele You can run the migrations manually with a subcommand of the server:
nakama migrate up --database.address "someuser:password@ip:port/dbname"
Have a read of the docs here for more information.
@novabyte I ALREADY DID! It returns error EOF or error cannot access virtual schema in anonymous database .Now I created a new database nakama with a new user nakama with all permission and it not work. It returns the same error .Please help me
I have the same problem.
I see the error “cannot access virtual schema in anonymous database” when I try to migrate nakama db into a cockroach free tier database. Using a local cockroach db it works.
Anyone know how can we fix it?
@novabyte, as we talked, I created an issue related to this problem: Nakama migrate problem using Cockroach Cloud Free Plan · Issue #602 · heroiclabs/nakama · GitHub
Meanwhile, I would like to find an alternative in order to create the nakama db manually. Could you help us?
I would like to know what are the steps to create and fill the db manually.
I saw these sql files: nakama/migrate/sql at master · heroiclabs/nakama · GitHub
So, I created a new database named “nakama” and I tried to execute these sql files manually.
Result: the first script (20180103142001_initial_schema.sql) worked, but the others not, due to dependency issues.
So… the question is:
- Are these sql files all the files that we should execute or there are other sql files to execute?
- What should be the order of execution of the files?
Thanks
@novabyte @tranthor I tried to run the files in this order( initial_schema, tournaments, facebook instant games, apple, console, purchase ) removing the part named --migrate down. The files are imported without any error but I cannot run the server for the error db schema outdated run nakama migrate up
I got the same error.
I ran only 20180103142001_initial_schema.sql without the last line ("–migrate Down"). It worked. But when I try to connect to nakama pointing to the database, I get the error:
{“level”:“fatal”,“ts”:“2021-04-27T13:49:37.288Z”,“caller”:“migrate/migrate.go:79”,“msg”:"DB schema outdated, run nakama migrate up",“migrations”:4}
I have a similar problem:
nakama_1 | {"level":"fatal","ts":"2021-04-27T19:26:51.394Z","caller":"migrate/migrate.go:182","msg":"Error pinging database","error":"strconv.ParseUint: parsing \"\": invalid syntax"} test_nakama_nakama_1 exited with code 1
@cata0309 Your problem is not related to this issue. You can fix removing the postgres:// from the database url
I’ve looked into this and it seems to be an issue with how Nakama handles the CRDB URL in the migration. We’ll try to include a fix within the next release.
@sesposito thank you, but… meanwhile… can we know how to migrate it manually executing sql files? what sql files we have to execute, what’s the order of the execution, etc.
currently i’m using nakama 2.14.1 and i don’t want to upgrade to the latest version to fix this problem, so i would like to do the migration manually… please 
You should create the nakama database, ensure the user you’ll use to connect has the correct privileges, execute the sql files in migrate/sql in the order of the timestamps and you’ll also need to add the migration_info table manually:
Table "public.migration_info"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
id | text | | not null |
applied_at | timestamp with time zone | | |
Indexes:
"migration_info_pkey" PRIMARY KEY, btree (id)
And then add the following rows:
id | applied_at
-------------------------------------------+-------------------------------
20180103142001_initial_schema.sql | 2021-04-16 12:35:14.419253+00
20180805174141-tournaments.sql | 2021-04-16 12:35:14.435163+00
20200116134800-facebook-instant-games.sql | 2021-04-16 12:35:14.442356+00
20200615102232-apple.sql | 2021-04-16 12:35:14.449562+00
20201005180855-console.sql | 2021-04-16 12:35:14.476456+00
20210416090601-purchase.sql | 2021-04-16 12:35:14.493215+00
EDIT: This is if you’re running the latest Nakama, @tranthor for 2.14.1 you should run these sql files instead, and add the corresponding rows accordingly.
Also we strive to keep backwards compatibility with every release so we recommend developers to upgrade when possible, a lot of improvements have been made since 2.14.1.
It worked!
Thank you!.
CREATE TABLE migration_info (id text NOT NULL PRIMARY KEY, applied_at timestamp with time zone);
CREATE INDEX ON migration_info (id);
INSERT INTO migration_info (id, applied_at) VALUES (‘20180103142001_initial_schema.sql’,‘2021-04-16 12:35:14.419253+00’);
INSERT INTO migration_info (id, applied_at) VALUES (‘20180805174141-tournaments.sql’,‘2021-04-16 12:35:14.435163+00’);
INSERT INTO migration_info (id, applied_at) VALUES (‘20200116134800-facebook-instant-games.sql’,‘2021-04-16 12:35:14.442356+00’);
INSERT INTO migration_info (id, applied_at) VALUES (‘20200615102232-apple.sql’,‘2021-04-16 12:35:14.449562+00’);
Probably the creation of the index is wrong. But it doesn’t matter, it works for nakama 2.14.1 
This issue has been fixed on master and will be part of the next release. For the fix to work on cockroach cloud’s free tier, the database must be created before executing the migrate up command.