Validating iOS in-app purchase receipts fails

Runtime Framework
,
1

We have a game written in Unity running against a development setup with Nakama 3.21 and CockroachDB 23.2.4. We have Android and iOS clients. All receipts for any IAP:s are validated by the server using the functionality built into Nakama. For any purchases made with the Android client against the Play Store the receipts validate ok, but any receipts validated against the App Store fail. The code does seem to work ok when deployed into production on Heroic Cloud, but for some reason it does not work on the development server. All shared passwords for Apple IAP:s etc are ok and the overall flow is ok.

The call is simply:

validationResponse, err = nk.PurchaseValidateApple(ctx, user.id, payload, true)

The payload is a long base64 encoded string (at least it looks like base64) like MIIV5gYJKoZIhvcNAQcCoIIV1zCCFdMCAQExDzANBglghkgBZQMEAgEFADCCBRwGCSqGSIb3DQEHAaCCBQ0EggUJ.....

The call however fails with:

ERROR: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time (SQLSTATE 21000)

Nothing gets saved into the purchase table in the nakama database on CockroachDB. We have tried to delete all old purchases, dropping the entire database, upgrading CockroachDB etc, but everything gives the same error.

A corresponding PurchaseValidateGoogle call to validate Play Store receipts work fine on the development system and everything also seems to work fine on Heroic Cloud.

Googling for the error seems to indicate a CockroachDB bug related to conflict handling, but there’s not much information out there.

2

To clarify, the error happens even on a just initialised empty database that has no previous stored receipts/purchases.

3

Hello @chakie,

Which version of Nakama Enterprise (NE) are you using? I believe this issue has been fixed on master and backported to NE, so if you update your builder to the latest v3.21.1-r9 it should resolve it.

Let us know if it still persists after the upgrade.

Also, going forward, for bugs or issues on Heroic Cloud please contact us at support@heroiclabs.com.

Regards.

4

This is not an issue on Heroic Cloud, at least we’re not aware of any problems. It only affects a separate development server. It runs the v3.21.1 release.

5

I think this worked ok with 3.20. We updated to 3.21 about two weeks ago and I have a feeling this started happening after that. IAP:s don’t get tested too much in development. So this commit seems to be related:

6

That commit is the change I was referring to that was backported to NE.

To clarify: the commit above is already in Nakama master and on NE v3.21.1-r9, it may resolve this issue on CRDB, however you won’t be able to test it outside of HC until the next OSS Nakama release will include it (unless you build Nakama from source and run it locally with CRDB).

Since your HC deployment uses Postgres, I’d also suggest you use it as your dev environment database too to better mimic the production environment on HC.

If the issue persists for CRDB on the Nakama master branch it’s something we’ll have to investigate.

7

I read your reply a bit hastily. So this seems to be a Cockroach related issue.

8

We’ll move to PostgreSQL for the development server tomorrow too. Will report back if the issue persists.

9

Tried to move to PostgreSQL, but it’s not going too well. I set up the database for Nakama and create the needed user and set up the database connection in local.yml. When connecting I get:

# ./nakama/nakama -config local.yml migrate up
{"level":"info","ts":"2024-05-08T07:46:06.863Z","caller":"server/config.go:90","msg":"Successfully loaded config file","path":"local.yml"}
...
{"level":"info","ts":"2024-05-08T07:46:06.865Z","caller":"v3/main.go:113","msg":"Nakama starting"}
{"level":"info","ts":"2024-05-08T07:46:06.865Z","caller":"v3/main.go:114","msg":"Node","name":"nakama","version":"3.0.0+dev","runtime":"go1.21.5","cpu":1,"proc":1}
{"level":"info","ts":"2024-05-08T07:46:06.865Z","caller":"v3/main.go:115","msg":"Data directory","path":"/home/towerpop/deploy/data"}
{"level":"info","ts":"2024-05-08T07:46:06.865Z","caller":"v3/main.go:126","msg":"Database connections","dsns":["nakama:xxxxx@127.0.0.1:5432/nakama"]}
{"level":"debug","ts":"2024-05-08T07:46:06.866Z","caller":"server/db.go:74","msg":"Complete database connection URL","raw_url":"postgres://nakama:password@127.0.0.1:5432/nakama?sslmode=prefer"}
{"level":"info","ts":"2024-05-08T07:46:06.875Z","caller":"v3/main.go:132","msg":"Database information","version":"PostgreSQL 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit"}
{"level":"fatal","ts":"2024-05-08T07:46:06.878Z","caller":"migrate/migrate.go:83","msg":"DB schema outdated, run `nakama migrate up`","migrations":11}

# sudo -u postgres psql nakama
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1))
Type "help" for help.

nakama=# \d
            List of relations
 Schema |      Name      | Type  | Owner
--------+----------------+-------+--------
 public | migration_info | table | nakama
(1 row)

nakama=# select * from migration_info;
 id | applied_at
----+------------
(0 rows)

It connects fine and creates one table and then complains about migrations. No matter how many times I run the migration I get the same error. I tried dropping the table and trying again and it’s created fine. So the connection and permissions are ok.

database:
  address:
    - "nakama:password@127.0.0.1:5432/nakama"

So now I’m at a loss as to what to do here.

10

Just to rule out any bug in Nakama where our local.yml would interfere I tried running it in a directory which did not contain such a file:

# ../nakama -database.address "nakama:password@127.0.0.1:5432/nakama" migrate up
{"level":"warn","ts":"2024-05-08T08:01:29.539Z","caller":"server/config.go:321","msg":"WARNING: insecure default parameter value, change this for production!","param":"console.username"}
{"level":"warn","ts":"2024-05-08T08:01:29.540Z","caller":"server/config.go:325","msg":"WARNING: insecure default parameter value, change this for production!","param":"console.password"}
{"level":"warn","ts":"2024-05-08T08:01:29.540Z","caller":"server/config.go:329","msg":"WARNING: insecure default parameter value, change this for production!","param":"console.signing_key"}
{"level":"warn","ts":"2024-05-08T08:01:29.540Z","caller":"server/config.go:333","msg":"WARNING: insecure default parameter value, change this for production!","param":"socket.server_key"}
{"level":"warn","ts":"2024-05-08T08:01:29.540Z","caller":"server/config.go:337","msg":"WARNING: insecure default parameter value, change this for production!","param":"session.encryption_key"}
{"level":"warn","ts":"2024-05-08T08:01:29.540Z","caller":"server/config.go:341","msg":"WARNING: insecure default parameter value, change this for production!","param":"session.refresh_encryption_key"}
{"level":"warn","ts":"2024-05-08T08:01:29.541Z","caller":"server/config.go:345","msg":"WARNING: insecure default parameter value, change this for production!","param":"runtime.http_key"}
{"level":"info","ts":"2024-05-08T08:01:29.541Z","caller":"v3/main.go:113","msg":"Nakama starting"}
{"level":"info","ts":"2024-05-08T08:01:29.541Z","caller":"v3/main.go:114","msg":"Node","name":"nakama","version":"3.0.0+dev","runtime":"go1.21.5","cpu":1,"proc":1}
{"level":"info","ts":"2024-05-08T08:01:29.541Z","caller":"v3/main.go:115","msg":"Data directory","path":"/home/towerpop/deploy/nakama/server/data"}
{"level":"info","ts":"2024-05-08T08:01:29.541Z","caller":"v3/main.go:126","msg":"Database connections","dsns":["nakama:xxxxx@127.0.0.1:5432/nakama"]}
{"level":"info","ts":"2024-05-08T08:01:29.557Z","caller":"v3/main.go:132","msg":"Database information","version":"PostgreSQL 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit"}
{"level":"fatal","ts":"2024-05-08T08:01:29.562Z","caller":"migrate/migrate.go:83","msg":"DB schema outdated, run `nakama migrate up`","migrations":11}

Same issue. It seems like the actual parameters given to Nakama are ignored? I can add whatever gibberish at the end of the line and I get no errors:

# ../nakama -database.address "nakama:password@127.0.0.1:5432/nakama" migrate udsfasdf asdf sadfasdf
...
{"level":"info","ts":"2024-05-08T08:04:34.981Z","caller":"v3/main.go:126","msg":"Database connections","dsns":["nakama:xxxxx@127.0.0.1:5432/nakama"]}
{"level":"info","ts":"2024-05-08T08:04:34.999Z","caller":"v3/main.go:132","msg":"Database information","version":"PostgreSQL 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit"}
{"level":"fatal","ts":"2024-05-08T08:04:35.004Z","caller":"migrate/migrate.go:83","msg":"DB schema outdated, run `nakama migrate up`","migrations":11}
11

Looking at the code, it seems this can never work for PostgreSQL, only CockroachDB.

func main() {
	semver := fmt.Sprintf("%s+%s", version, commitID)
	// Always set default timeout on HTTP client.
	http.DefaultClient.Timeout = 1500 * time.Millisecond

	tmpLogger := server.NewJSONLogger(os.Stdout, zapcore.InfoLevel, server.JSONFormat)

	if len(os.Args) > 1 {
		switch os.Args[1] {
		case "--version":
			fmt.Println(semver)
			return
		case "migrate":
			migrate.Parse(os.Args[2:], tmpLogger)
			return
...

The code requires that migrate is the first argument. This means that I can not supply a -config or a -database.address on the command line. It will only work if Nakama can connect to the database using the compiled in defaults. Or am I reading this wrong?

12

Aha, the migrate command seems to accept a -database.address parameter. The docs say database.address which does not seem to work. So much seems to default to CockroachDB. The DB schema outdated, run nakama migrate up error shown in the log could maybe mention this flag especially if the used database is not the default one. It is pretty confusing that nakama -database.address XXX migrate up does not work.

13

Alas, still no go. The database is now PostgreSQL and everything else runs fine.

{"level":"info","ts":"2024-05-08T09:00:23.157Z","caller":"v3/main.go:114","msg":"Node","name":"nakama","version":"3.0.0+dev","runtime":"go1.21.5","cpu":1,"proc":1}
...
{"level":"info","ts":"2024-05-08T09:00:23.174Z","caller":"v3/main.go:132","msg":"Database information","version":"PostgreSQL 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit"}

But validating gives:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)

The version shown by Nakama is still pretty pointless.

14

Testing with the master version works. The Apple receipts validate correctly.

15

Glad the issue is resolved, thank you for checking and reporting back.