search

gogs / gogs

Gogs is a painless self-hosted Git service
https://gogs.io
MIT License
44.97k stars 4.86k forks source link

500 error when viewing organization with PostgreSQL #2727

Closed johnnylee closed 8 years ago

johnnylee commented 8 years ago

When I attempt to view a newly created organization, I get a 500 error. I see the following in the log file: 

Mar  1 17:47:58 ip-172-30-1-235 gogs[28856]: 2016/03/01 17:47:58 #033[1;31m[...routers/user/home.go:115 Dashboard()] [E] GetUserRepositories: GetUserRepositories: get repositories: pq: invalid input syntax for integer: "1,2"#033[0m

This is a fresh install with one user and one organization.

neolit123 commented 8 years ago

i haven't seen this with sqlite3 or at try.gogs.io, so it must be a PostgreSQL issue. GetUserRepositories() accepts a int64 and is given a bad value. (EDIT: the value could be fine..)

what are the user names and organization names, any special characters?

johnnylee commented 8 years ago

Hi. Thanks for the response. The user and organization names are "david" and "Readmore" respectively. No special characters.

Judging by the error this is a string interpolation issue - postgres is getting "1,2" where it's expecting an integer.

neolit123 commented 8 years ago

my suspicion is that the IDs in the tables are fine, but it might be the access operation (with go-xorm) that's causing the error for PostgreSQL.

a text dump of the SQL table might help (e.g. for sqlite3 that's in gogs/data/gogs.db), or uploading the database file itself, somewhere.

johnnylee commented 8 years ago

I dumped the database. I only modified email addresses and removed password hashes. Sorry. db.zip

neolit123 commented 8 years ago

thanks, i don't see anything wrong on a quick glance, so it could be one of the go-xorm abstracted operations for PostgreSQL breaking somehow: https://github.com/gogits/gogs/blob/aa12135b975ff2ebf04acb12cf3b3fd52b6c024a/models/org.go#L1079

TMK, MySQL seems to be the most stable choice ATM.

unknwon commented 8 years ago

Hi, I just pushed a fix to develop branch, please help test again!

neolit123 commented 8 years ago

@johnnylee

here is download link for the binary of the current develop branch. we are both on Debian based distros so this should work (ermm "should"...and unless you aren't on a 32bit that is): https://dl.dropboxusercontent.com/u/1627980/gogs/gogs_0.8.50.0301_linux_amd64.zip

$ ldd ./gogs
    linux-vdso.so.1 =>  (0x00007fff719ff000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fd5c8535000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fd5c8318000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fd5c7f58000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fd5c874e000)

might be a good idea to start from a clean DB, also make sure you backup the original gogs binary.

unknwon commented 8 years ago

@neolit123 thanks for the binary!

johnnylee commented 8 years ago

Hi @neolit123 - Thanks for the binary. I get an error when using it, though: 

Mar  1 20:40:57 ip-172-30-1-235 gogs[29818]: 2016/03/01 20:40:57 #033[1;31m[...gits/gogs/cmd/web.go:77 checkVersion()] [E] Binary and template file version does not match, did you forget to recompile?#033[0m

OK - just build from source on the develop branch, and it gives me the same error.

neolit123 commented 8 years ago

@johnnylee, i was considering including the ./templates/.VERSION but i neglected it.

make a backup of the file and copy paste the version of the new binary (e.g. from the zip name) in there.

neolit123 commented 8 years ago

@johnnylee

OK - just build from source on the develop branch, and it gives me the same error.

i assume you started with a clean DB, recreating the user and organization?

johnnylee commented 8 years ago

I started again with a clean DB, and I have the same problem, but I observed something along the way that might be useful. The organization page was working fine until I added a team with two members. When the team had one member I could view the page, but when it had two members it didn't work.

neolit123 commented 8 years ago

hello and thanks for the further feedback. if the error is somehow different make sure you post it here. @Unknwon, still seems to be PostgreSQL specific.

unknwon commented 8 years ago

Thanks for the comments, but my question is: have you tested latest develop branch? And post new errors if any.

johnnylee commented 8 years ago

Hi Unknwon - I built and ran the develop branch last night after the provided binary didn't work for me (because I'm on a 64-bit Linux system).

unknwon commented 8 years ago

didn't work is a vague word.

johnnylee commented 8 years ago

I'm sorry - The report that I made above (7 hours ago) refers to the develop branch.

The error message hasn't changed.

unknwon commented 8 years ago

I started again with a clean DB, and I have the same problem, but I observed something along the way that might be useful. The organization page was working fine until I added a team with two members. When the team had one member I could view the page, but when it had two members it didn't work.

didn't work are vague words... error message?

johnnylee commented 8 years ago

I apologize. The error hasn't changed since the first message in the thread. I get a 500 error when I attempt to view the organization's page - that is, I click on the organization name. See the original description above for the log message.

When trying to reproduce the bug (using the develop branch) I found that it only started to happen after I added a team with two members to the organization.

unknwon commented 8 years ago

I found that it only started to happen after I added a team with two members to the organization.

Thanks, I'll try to locate this problem.

Schroedingers-Cat commented 8 years ago

I just updated to build 0.8.49.0229 and experience at least a related problem. Whenever I try to access the Dashboard or the team page, I get "500" in the browser. The error in the log is different, however:

2016/03/03 10:49:48 [...routers/user/home.go:57 retrieveFeeds()] [E] GetFeeds: GetUserRepositories: get teams: Error 1054: Unknown column 'team`.`id' in 'field list'

I'm using PostgreSQL on Ubuntu Server 14.04 (Linaro/ARM). I can view my personal repositories, issues and so on. Anything that tries to read something team-related fails.

okket commented 8 years ago

Same problem here, I am using MariaSQL:

An error has occurred : GetUserRepositories: get teams: Error 1054: Unknown column 'team.id' in 'field list'

unknwon commented 8 years ago

Looks like I have to install PostreSQL...

unknwon commented 8 years ago

Whoever encounters team.id thing, please follow on https://github.com/gogits/gogs/issues/2743, that is irrelevant to this thread.

unknwon commented 8 years ago

Please test again with master branch!

johnnylee commented 8 years ago

EDIT #1: Sorry, I was still on the develop branch. I'm trying now with master.

EDIT #2: The story is the same on the master branch.

I updated gogs and xorm but I'm still getting the identical error: 

Mar  5 12:26:16 ip-172-30-1-235 gogs[839]: 2016/03/05 12:26:16 #033[1;31m[...routers/user/home.go:336 showOrgProfile()] [E] GetUserRepositories: get repositories: pq: invalid input syntax for integer: "1,2"#033[0m
unknwon commented 8 years ago

@johnnylee please dump the xorm.log here right after you hit this error.

unknwon commented 8 years ago

@xuanduc987 how do you know this line of log cause the problem?

And please state your Gogs version, database and system.

johnnylee commented 8 years ago

@Unknwon here are the xorm log entries that show up when attempting to render the page: 

[xorm] [info]  2016/03/06 08:22:11.362113 [sql] SELECT "id", "lower_name", "name", "full_name", "email", "passwd", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "created", "updated", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "allow_git_hook", "allow_import_local", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "description", "num_teams", "num_members" FROM "public"."user" WHERE "id" = $1 LIMIT 1 [args] [1]
[xorm] [debug] 2016/03/06 08:22:11.363163 empty zone key[created] : 2016-03-02 07:52:22 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [debug] 2016/03/06 08:22:11.363186 empty zone key[updated] : 2016-03-03 12:24:18 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [info]  2016/03/06 08:22:11.363308 [sql] SELECT "id", "lower_name", "name", "full_name", "email", "passwd", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "created", "updated", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "allow_git_hook", "allow_import_local", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "description", "num_teams", "num_members" FROM "public"."user" WHERE "id" = $1 LIMIT 1 [args] [1]
[xorm] [debug] 2016/03/06 08:22:11.363867 empty zone key[created] : 2016-03-02 07:52:22 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [debug] 2016/03/06 08:22:11.363941 empty zone key[updated] : 2016-03-03 12:24:18 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [info]  2016/03/06 08:22:11.364171 [sql] SELECT "id", "lower_name", "name", "full_name", "email", "passwd", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "created", "updated", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "allow_git_hook", "allow_import_local", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "description", "num_teams", "num_members" FROM "public"."user" WHERE "lower_name" = $1 LIMIT 1 [args] [readmore]
[xorm] [debug] 2016/03/06 08:22:11.364970 empty zone key[created] : 2016-03-02 07:53:05 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [debug] 2016/03/06 08:22:11.364991 empty zone key[updated] : 2016-03-04 14:05:30 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [info]  2016/03/06 08:22:11.365097 [sql] SELECT "id", "lower_name", "name", "full_name", "email", "passwd", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "created", "updated", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "allow_git_hook", "allow_import_local", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "description", "num_teams", "num_members" FROM "public"."user" WHERE "lower_name" = $1 LIMIT 1 [args] [readmore]
[xorm] [debug] 2016/03/06 08:22:11.365547 empty zone key[created] : 2016-03-02 07:53:05 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [debug] 2016/03/06 08:22:11.365567 empty zone key[updated] : 2016-03-04 14:05:30 +0000 +0000 | zone:  | location: {name: zone:[{name: offset:0 isDST:false}] tx:[{when:-9223372036854775808 index:0 isstd:false isutc:false}] cacheStart:-9223372036854775808 cacheEnd:9223372036854775807 cacheZone:0xc820c44760}
[xorm] [info]  2016/03/06 08:22:11.365644 [sql] SELECT "id", "org_id", "lower_name", "name", "description", "authorize", "num_repos", "num_members" FROM "public"."team" WHERE org_id=$1 [args] [3]
[xorm] [info]  2016/03/06 08:22:11.366003 [sql] SELECT team.id FROM team 
INNER JOIN team_user ON team_user.team_id = team.id
WHERE team_user.org_id = $1 AND team_user.uid = $2 [args] [3 1]
[xorm] [info]  2016/03/06 08:22:11.366464 [sql] SELECT repository.* FROM repository
INNER JOIN team_repo ON team_repo.repo_id = repository.id
WHERE (repository.owner_id = $1 AND repository.is_private = $2) OR team_repo.team_id IN ($3)
GROUP BY repository.id [args] [3 false 1,2]
lunny commented 8 years ago

It seems there are no error occupation? @johnnylee

johnnylee commented 8 years ago

@lunny I'm not sure I understand.

Looking at this information, I think that the most likely cause of the error is in the last query, where it appears that xorm is passing the array 1,2 to postgres as a string.

lunny commented 8 years ago 
func (org *User) GetUserRepositories(userID int64) (err error) {
    teams := make([]*Team, 0, org.NumTeams)
    if err = x.Sql(`SELECT team.id FROM team
INNER JOIN team_user ON team_user.team_id = team.id
WHERE team_user.org_id = ? AND team_user.uid = ?`, org.Id, userID).Find(&teams); err != nil {
        return fmt.Errorf("get teams: %v", err)
    }

    args := make([]interface{}, 2+len(teams))
    marks := make([]string, len(teams))
    args[0], args[1] = org.Id, false
    for i := range teams {
        args[i+2] = com.ToStr(teams[i].ID)
        marks[i] = "?"
    }
    if len(teams) == 0 {
        // user has no team but "IN ()" is invalid SQL
        args = append(args, "-1") // there is no repo with id=-1
        marks = append(marks, "?")
    }

    repos := make([]*Repository, 0, 5)
    if err = x.Sql(fmt.Sprintf(`SELECT repository.* FROM repository
INNER JOIN team_repo ON team_repo.repo_id = repository.id
WHERE (repository.owner_id = ? AND repository.is_private = ?) OR team_repo.team_id IN (%s)
GROUP BY repository.id`, strings.Join(marks, ", ")), args...).Find(&repos); err != nil {
        return fmt.Errorf("get repositories: %v", err)
    }
    org.Repos = repos

    // FIXME: should I change this value inside method,
    // or only in location of caller where it's really needed?
    org.NumRepos = len(org.Repos)
    return nil
}
unknwon commented 8 years ago

@lunny any explanation for the patch?

juhanima commented 8 years ago

I suppose the explanation would be something like this:

The SQL error is caused by the fact that a list of integers as a string ('1,2,3') cannot be used as a bind variable value in PostgreSQL for a statement like this: ...where x in (?). There might be some conversion function for PostgreSQL, but that would make the SQL PostgreSQL specific, which is not nice.

As @lunny suggested, an easy way out is to use fmt.Printf to embed the list if integers as part of the SQL statement and not use a bind variable at all. This may cause SQL injection issues though, so I would suggest exploring the option of supporting array types at the xorm layer.

Here is my version which is a bit simpler. Seems to work fine on top of the current master. Provided just for reference and for anyone who wants to work around the problem while waiting for the final fix.

diff --git a/models/org.go b/models/org.go
index 1fee4f5..f750822 100644
--- a/models/org.go
+++ b/models/org.go
@@ -1071,11 +1071,11 @@ WHERE team_user.org_id = ? AND team_user.uid = ?`, org.Id, userID).Find(&teams);
    }

    repos := make([]*Repository, 0, 5)
-   if err = x.Sql(`SELECT repository.* FROM repository
+   if err = x.Sql(fmt.Sprintf(`SELECT repository.* FROM repository
 INNER JOIN team_repo ON team_repo.repo_id = repository.id
-WHERE (repository.owner_id = ? AND repository.is_private = ?) OR team_repo.team_id IN (?)
-GROUP BY repository.id`,
-       org.Id, false, strings.Join(teamIDs, ",")).Find(&repos); err != nil {
+WHERE (repository.owner_id = ? AND repository.is_private = ?) OR team_repo.team_id IN (%s)
+GROUP BY repository.id`, strings.Join(teamIDs, ",")),
+       org.Id, false).Find(&repos); err != nil {
        return fmt.Errorf("get repositories: %v", err)
    }
    org.Repos = repos
-- 
2.1.4
unknwon commented 8 years ago

I think it could be a problem PostgreSQL version too low... my PostgreSQL 9.5 instance could handle x where in (1,2) without error...

juhanima commented 8 years ago

That's quite possible. My environment is Ubuntu server 15.04 with PostgreSQL 9.4. Good to hear that 9.5 is better.

unknwon commented 8 years ago

Push a fix with suggested change by @lunny and @juhanima .

Please help test develop again!

unknwon commented 8 years ago

Close as fixed.

choucavalier commented 8 years ago

I stumbled upon this issue today after installing gogs and setting things up.

Do you plan on releasing the fix soon? :smiley:

Thanks a lot @Unknwon

unknwon commented 8 years ago

@toogy early next week I think...