Gitea fails to Install using CockroachDB (PostgreSQL replacement) #1682

@mqudsi commented on GitHub (Apr 15, 2018):

@knz any ideas?

@mqudsi commented on GitHub (Apr 15, 2018): @knz any ideas?

GiteaMirror commented

@lunny commented on GitHub (Apr 15, 2018):

~~I think this is blocked by https://github.com/go-xorm/xorm/issues/802~~
resolved by xorm side in https://gitea.com/xorm/xorm

@lunny commented on GitHub (Apr 15, 2018): ~I think this is blocked by https://github.com/go-xorm/xorm/issues/802~ resolved by xorm side in https://gitea.com/xorm/xorm

GiteaMirror commented

@knz commented on GitHub (Apr 15, 2018):

Please file on the cockroach github, link to this issue and cc me there. Thanks

Sent from my Android device with K-9 Mail. Please excuse my brevity.

@knz commented on GitHub (Apr 15, 2018): Please file on the cockroach github, link to this issue and cc me there. Thanks -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

GiteaMirror commented

@techknowlogick commented on GitHub (Apr 16, 2018):

Just submitted a ticket on the cockroach github: cockroachdb/cockroach#24846

@techknowlogick commented on GitHub (Apr 16, 2018): Just submitted a ticket on the cockroach github: cockroachdb/cockroach#24846

GiteaMirror commented

As per https://github.com/cockroachdb/cockroach/issues/24846#issuecomment-381760597:

@knz commented on GitHub (Apr 16, 2018):

Unfortunately the column numeric_precision_radix is implemented for information_schema.sequences but not information_schema.columns which your query is using. This is why the query fails.

In CockroachDB the only type that uses the radix 10 is decimal (also called numeric) and every other type uses radix 2. Is there any way you can use a CASE expression to determine this in your query instead of using numeric_precision_radix?

@knz commented on GitHub (Apr 16, 2018): As per https://github.com/cockroachdb/cockroach/issues/24846#issuecomment-381760597: > Unfortunately the column numeric_precision_radix is implemented for information_schema.sequences but not information_schema.columns which your query is using. This is why the query fails. > In CockroachDB the only type that uses the radix 10 is decimal (also called numeric) and every other type uses radix 2. Is there any way you can use a CASE expression to determine this in your query instead of using numeric_precision_radix?

GiteaMirror commented

@knz commented on GitHub (Apr 16, 2018):

(we'll probably add the missing column to a later version of CockroachDB. My comment above is a suggestion for a workaround until then.)

@knz commented on GitHub (Apr 16, 2018): (we'll probably add the missing column to a later version of CockroachDB. My comment above is a suggestion for a workaround until then.)

GiteaMirror commented

@knz commented on GitHub (Apr 16, 2018):

Actually maybe other types other than decimal also use radix 10. We can check together.

@knz commented on GitHub (Apr 16, 2018): Actually maybe other types other than `decimal` also use radix 10. We can check together.

GiteaMirror commented

@lunny commented on GitHub (Apr 17, 2018):

I created a pull request https://github.com/go-xorm/xorm/pull/896 to add cockroach support for xorm. The numeric_precision_radix problem has been ignored but it encountered other problem. The tests could be PASS on postgres

@lunny commented on GitHub (Apr 17, 2018): I created a pull request https://github.com/go-xorm/xorm/pull/896 to add cockroach support for xorm. The `numeric_precision_radix` problem has been ignored but it encountered other problem. The tests could be PASS on postgres

GiteaMirror commented

@stale[bot] commented on GitHub (Jan 28, 2019):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

@stale[bot] commented on GitHub (Jan 28, 2019): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

GiteaMirror commented

@fire commented on GitHub (Feb 19, 2021):

I have interest in this.

Edited:

What can I do to help?

@fire commented on GitHub (Feb 19, 2021): I have interest in this. Edited: What can I do to help?

GiteaMirror commented

@lunny commented on GitHub (Feb 19, 2021):

There is a PR #11875

@lunny commented on GitHub (Feb 19, 2021): There is a PR #11875

GiteaMirror commented

@efossas commented on GitHub (Feb 27, 2022):

installed gitea 1.15.10 (helm chart 5.0.1) with cockroachdb v21.2.6.

when provisioning the database, make sure this cluster setting is on:
sql.defaults.serial_normalization: 'sql_sequence'

currently seeing 2 errors that cause 500s on Dashboards, Issues, & Pull Requests pages:

GetUserHeatmapDataByUserTeam: pq: unknown function: to_timestamp()
userRepoIDs: ctxUser.GetAccessRepoIDs: pq: could not determine data type of placeholder $1

the first error is that cockroachdb does not have the to_timestamp() function. not sure what the second error is about.

i smoke tested a bunch of stuff in Gitea, and so far have only ran into those two errors.

@efossas commented on GitHub (Feb 27, 2022): installed gitea 1.15.10 (helm chart 5.0.1) with cockroachdb v21.2.6. when provisioning the database, make sure this cluster setting is on: `sql.defaults.serial_normalization: 'sql_sequence'` currently seeing 2 errors that cause 500s on Dashboards, Issues, & Pull Requests pages: ``` GetUserHeatmapDataByUserTeam: pq: unknown function: to_timestamp() userRepoIDs: ctxUser.GetAccessRepoIDs: pq: could not determine data type of placeholder $1 ``` the first error is that cockroachdb does not have the to_timestamp() function. not sure what the second error is about. i smoke tested a bunch of stuff in Gitea, and so far have only ran into those two errors.

GiteaMirror commented

@lunny commented on GitHub (Feb 27, 2022):

Yes, since xorm have declared it supports cockroachdb. For Gitea, there is less issues to be handle. Could you paste the second error's generated SQL so that we can find something.

@lunny commented on GitHub (Feb 27, 2022): Yes, since [xorm](https://gitea.com/xorm/xorm) have declared it supports `cockroachdb`. For Gitea, there is less issues to be handle. Could you paste the second error's generated SQL so that we can find something.

GiteaMirror commented

@efossas commented on GitHub (Feb 27, 2022):

absolutely, happy to help in any way i can. here are all the sql queries generated for the GET request to /issues:

2022/02/27 21:58:44 Started GET /issues for 127.0.0.1:37450
2022/02/27 21:58:44 models/user.go:1340:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "public"."user" WHERE "id"=$1 LIMIT 1 [1] - 3.617285ms
2022/02/27 21:58:44 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "public"."stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 2.728579ms
2022/02/27 21:58:44 models/user.go:651:GetOrganizations() [I] [SQL] SELECT "user".*, count(repo_id) as org_count FROM "public"."user" INNER JOIN "public"."org_user" ON "org_user".org_id="user".id LEFT JOIN (SELECT id as repo_id, owner_id as repo_owner_id FROM repository WHERE ("repository".is_private=$1 AND "repository".owner_id NOT IN (SELECT id FROM "user" WHERE type=$2 AND visibility IN ($3))) OR "repository".id IN (SELECT repo_id FROM "access" WHERE user_id=$4 AND mode>$5) OR "repository".owner_id=$6 OR "repository".id IN (SELECT "team_repo".repo_id FROM team_repo INNER JOIN team_user ON "team_user".team_id = "team_repo".team_id WHERE "team_user".uid=$7) OR ("repository".is_private=$8 AND "repository".owner_id IN (SELECT "org_user".org_id FROM org_user WHERE "org_user".uid=$9))) repository ON "repository".repo_owner_id = "org_user".org_id WHERE ("org_user".uid=$10) GROUP BY "user".id,"user".lower_name,"user".name,"user".full_name,"user".email,"user".keep_email_private,"user".email_notifications_preference,"user".passwd,"user".passwd_hash_algo,"user".must_change_password,"user".login_type,"user".login_source,"user".login_name,"user".type,"user".location,"user".website,"user".rands,"user".salt,"user".language,"user".description,"user".created_unix,"user".updated_unix,"user".last_login_unix,"user".last_repo_visibility,"user".max_repo_creation,"user".is_active,"user".is_admin,"user".is_restricted,"user".allow_git_hook,"user".allow_import_local,"user".allow_create_organization,"user".prohibit_login,"user".avatar,"user".avatar_email,"user".use_custom_avatar,"user".num_followers,"user".num_following,"user".num_stars,"user".num_repos,"user".num_teams,"user".num_members,"user".visibility,"user".repo_admin_change_team_access,"user".diff_view_style,"user".theme,"user".keep_activity_private ORDER BY "user"."name" ASC [false 1 private 1 0 1 1 false 1 1] - 12.826551ms
2022/02/27 21:58:44 models/user.go:529:GetActiveRepositoryIDs() [I] [SQL] SELECT "repository"."id" FROM "public"."repository" INNER JOIN "public"."repo_unit" ON repository.id = repo_unit.repo_id WHERE "repo_unit"."type" IN ($1) AND is_archived=$2 AND (owner_id = $3) GROUP BY repository.id [UnitTypeIssues false 1] - 4.578558ms
2022/02/27 21:58:44 models/user.go:564:GetActiveOrgRepositoryIDs() [I] [SQL] SELECT "repository"."id" FROM "public"."repository" INNER JOIN "public"."team_user" ON repository.owner_id = team_user.org_id INNER JOIN "public"."team_repo" ON ($1 != $2 and repository.is_private != $3) OR (team_user.team_id = team_repo.team_id AND repository.id = team_repo.repo_id) WHERE (team_user.uid = $4) AND is_archived=$5 GROUP BY repository.id [true false true 1 false] - 1.199347ms
2022/02/27 21:58:44 routers/user/home.go:423:buildIssueOverview() [E] userRepoIDs: ctxUser.GetAccessRepoIDs: pq: could not determine data type of placeholder $1
2022/02/27 21:58:44 ...dels/notification.go:711:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "public"."notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 2.824677ms
2022/02/27 21:58:44 Completed GET /issues 500 Internal Server Error in 30.288457ms

@efossas commented on GitHub (Feb 27, 2022): absolutely, happy to help in any way i can. here are all the sql queries generated for the GET request to /issues: ``` 2022/02/27 21:58:44 Started GET /issues for 127.0.0.1:37450 2022/02/27 21:58:44 models/user.go:1340:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "public"."user" WHERE "id"=$1 LIMIT 1 [1] - 3.617285ms 2022/02/27 21:58:44 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "public"."stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 2.728579ms 2022/02/27 21:58:44 models/user.go:651:GetOrganizations() [I] [SQL] SELECT "user".*, count(repo_id) as org_count FROM "public"."user" INNER JOIN "public"."org_user" ON "org_user".org_id="user".id LEFT JOIN (SELECT id as repo_id, owner_id as repo_owner_id FROM repository WHERE ("repository".is_private=$1 AND "repository".owner_id NOT IN (SELECT id FROM "user" WHERE type=$2 AND visibility IN ($3))) OR "repository".id IN (SELECT repo_id FROM "access" WHERE user_id=$4 AND mode>$5) OR "repository".owner_id=$6 OR "repository".id IN (SELECT "team_repo".repo_id FROM team_repo INNER JOIN team_user ON "team_user".team_id = "team_repo".team_id WHERE "team_user".uid=$7) OR ("repository".is_private=$8 AND "repository".owner_id IN (SELECT "org_user".org_id FROM org_user WHERE "org_user".uid=$9))) repository ON "repository".repo_owner_id = "org_user".org_id WHERE ("org_user".uid=$10) GROUP BY "user".id,"user".lower_name,"user".name,"user".full_name,"user".email,"user".keep_email_private,"user".email_notifications_preference,"user".passwd,"user".passwd_hash_algo,"user".must_change_password,"user".login_type,"user".login_source,"user".login_name,"user".type,"user".location,"user".website,"user".rands,"user".salt,"user".language,"user".description,"user".created_unix,"user".updated_unix,"user".last_login_unix,"user".last_repo_visibility,"user".max_repo_creation,"user".is_active,"user".is_admin,"user".is_restricted,"user".allow_git_hook,"user".allow_import_local,"user".allow_create_organization,"user".prohibit_login,"user".avatar,"user".avatar_email,"user".use_custom_avatar,"user".num_followers,"user".num_following,"user".num_stars,"user".num_repos,"user".num_teams,"user".num_members,"user".visibility,"user".repo_admin_change_team_access,"user".diff_view_style,"user".theme,"user".keep_activity_private ORDER BY "user"."name" ASC [false 1 private 1 0 1 1 false 1 1] - 12.826551ms 2022/02/27 21:58:44 models/user.go:529:GetActiveRepositoryIDs() [I] [SQL] SELECT "repository"."id" FROM "public"."repository" INNER JOIN "public"."repo_unit" ON repository.id = repo_unit.repo_id WHERE "repo_unit"."type" IN ($1) AND is_archived=$2 AND (owner_id = $3) GROUP BY repository.id [UnitTypeIssues false 1] - 4.578558ms 2022/02/27 21:58:44 models/user.go:564:GetActiveOrgRepositoryIDs() [I] [SQL] SELECT "repository"."id" FROM "public"."repository" INNER JOIN "public"."team_user" ON repository.owner_id = team_user.org_id INNER JOIN "public"."team_repo" ON ($1 != $2 and repository.is_private != $3) OR (team_user.team_id = team_repo.team_id AND repository.id = team_repo.repo_id) WHERE (team_user.uid = $4) AND is_archived=$5 GROUP BY repository.id [true false true 1 false] - 1.199347ms 2022/02/27 21:58:44 routers/user/home.go:423:buildIssueOverview() [E] userRepoIDs: ctxUser.GetAccessRepoIDs: pq: could not determine data type of placeholder $1 2022/02/27 21:58:44 ...dels/notification.go:711:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "public"."notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 2.824677ms 2022/02/27 21:58:44 Completed GET /issues 500 Internal Server Error in 30.288457ms ```

GiteaMirror commented