Pq: value is out of range

When running drone server with postgres DB, we get the following error after logging in (we use bitbucket server):

cannot create user: pq: value \"-62135596800\" is out of range for type integer

The database is empty or newly initialized when this error occurs. Any ideas?

hmm … unfortunately I am unable to reproduce the error with 1.0.0-rc.2. We are using postgres for cloud.drone.io. Do you have any way to know which particular field is causing the problem (anything in your posgres logs)?

weird, I can persistently reproduce it, but only with bitbucket server (my github organization works fine with postgres 11 and drone 1.0.0-rc.2.

Withouth having the possibility to look at the code, the number -62135596800 catches my eye as this number represents the difference between Unix Epoch 0 (1970-01-01T00:00:00Z) and 0001-01-01T00:00:00Z .

In golang time.Time default / zero value is 0001-01-01T00:00:00Z and time.Timestamp defaults to Unix Epoch 0 (1970-01-01T00:00:00Z)

I guess in the bitbucket server connector is somewhere in the user creation execution flow a wrong type used or wrongly converted, but I’m just blindly guessing here. Maybe the user_updated field?

OK I can reproduce -62135596800 for Bitbucket server. It is the oauth expiration date, which is an empty date value for Bitbucket server due to the fact that it uses oauth1, which is then converted to a unix timestamp (just as you suspected, bravo). I use sqlite in my staging environments, which is why I did not detect this sooner.

I already have a fix locally and will try to snapshot rc.3 over the weekend.


Amazing! Looking forward testing rc.3 next week. Great effort and thanks a lot for your help!

I consistently get this while trying to setup rc2 with a local gitea, too.

The fix does not seem to be in 1.0.0-rc.3 yet, can you confirm that?

This error still exists in rc3 :frowning:

there is an updated patch for 1.0.0-rc.3 that was snapshotted 30 minutes ago that you can test. I had previously fixed the invalid date issue for updates, but not for inserts. Both should work now.

I can confirm that authentication now works as expected :slight_smile: Thank you!

Well, we are a step further, but I get a new error:

"msg": "cannot update user: pq: value \"-62135596800\" is out of range for type integer",

The user is now properly logged in but the Repository Sync stays in the syncing state. I assume we have a similar issue with the last user_synced field…

Can you provide more debug logging context (logs that appeared before and after this entry) so I can better target where such an error might occur? I am pretty sure we use time.Now().Unix() to set all dates throughout the codebase. The only exception I am aware of is the oauth2 code.

Actually I think all of these issues seem to surround oauth2 tokens having a nil or empty date value, which should not be the case for Bitbucket. The reason you are getting an error in the sync is probably because the oauth2 expires date was empty which in turn caused Drone to fetch a new token, which returned an empty expires date

Bitbucket uses refresh tokens and should never return an empty expires date. It would be awesome if someone could take a look at the bitbucket implementation of https://github.com/drone/go-login and dig a bit deeper, and see why the expires date is empty.

I should otherwise be able to dig a bit deeper early next week.

Yes I can have a look at the go-login code, but I think you are mixing up bitbucket and bitbucket server with oauth1. we are experiencing the error with bitbucket server aka stash.

Unfortunately, there is not more in the drone logs. But I‘ll check also the DB logs. Would you be able to grant me read access tonthe 1.0.0-rc code so that I can trace the error through the entire codebase?

Ahhh, if you are using Bitbucket Server (Stash) then nevermind go-login … I really with Bitbucket didn’t use the same name for two completely different products. So confusing :slight_smile:

Let me run a quick test with Stash to see if I can reproduce.

ok I patched rc.3 and removed the line of code causing the problem. This impacted any drone instance using a non-oauth2 provider (gogs, gitea, stash) and a postgres database backend.

- 		user.Expiry = tok.Expires.Unix()	
 		if !tok.Expires.IsZero() {
 			user.Expiry = tok.Expires.Unix()