Unable to authenticate, "The provided value for the column is too long for the column's type"

[markflorkowski]

Environment

System:
    OS: macOS 12.3.1
    CPU: (16) x64 Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
    Memory: 47.16 GB / 64.00 GB
    Shell: 5.8 - /bin/zsh
  Binaries:
    Node: 16.13.0 - ~/.nvm/versions/node/v16.13.0/bin/node
    Yarn: 1.22.17 - /usr/local/bin/yarn
    npm: 8.1.0 - ~/.nvm/versions/node/v16.13.0/bin/npm
  Browsers:
    Brave Browser: 99.1.36.119
    Chrome: 102.0.5005.115
    Firefox: 99.0.1
    Safari: 15.4
  npmPackages:
    next: 12.1.5 => 12.1.5 
    next-auth: ^4.3.4 => 4.3.4 
    react: 17.0.2 => 17.0.2 

Reproduction URL

http://ping.gg

Describe the issue

We had a user try to log in this morning and they were unable, and kept hitting an auth error.

Upon checking the logs, we found this error:

https://next-auth.js.org/errors#adapter_error_linkaccount The provided value for the column is too long for the column's type. Column: for {
  message: "The provided value for the column is too long for the column's type. Column: for",
  stack: "Error: The provided value for the column is too long for the column's type. Column: for\n" +
    '    at RequestHandler.request (/var/task/node_modules/@prisma/client/runtime/index.js:49022:15)\n' +
    '    at async PrismaClient._request (/var/task/node_modules/@prisma/client/runtime/index.js:49919:18)',
  name: 'Error'
}

The only thing out of the ordinary about this user's email was that it had a pretty long / multi-part domain (@history.ucla.edu).

How to reproduce

We have been unable to reproduce this since the user ran into the issue in production this morning. We would greatly appreciate any assistance in sorting out what happened here.

Expected behavior

User should be able to log in.

[zenflow]

@markflorkowski I came into this issue yesterday, and was digging into it already..

So here's what I know:

1. Basic issue is that the default schemas for most of the adapters use VARCHAR(255) (length limited to 255 characters) instead of TEXT (unlimited length) for string fields
2. This seems to be a frequent issue with the id_token field when using the Google provider:
   • https://github.com/nextauthjs/next-auth/issues/3825
   • https://github.com/nextauthjs/next-auth/issues/4665
   • https://github.com/nextauthjs/next-auth/issues/3254
   • https://github.com/nextauthjs/next-auth/issues/3822
3. 255 characters may not always be enough in some other cases
   • access_token and or refresh_token /w Google provider (see https://developers.google.com/identity/protocols/oauth2#size) (does anyone know where in the next-auth schema we find the "Authorization codes" that the linked page talks about?)
   • scope if you're requesting more scopes than the default ones (e.g. with Google provider and default scopes, the scope field is already 102 bytes: openid https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile)
   • session_state if used (if your session strategy is "database) can easily be more than 255 bytes if you're adding properties to your session object
4. PR to fix the issue, but just for the TypeORM adapter and just for the id_token field: #336

The workaround has been presented: Change the field type of id_token (or whatever field doesn't have enough room.. I suggest doing it for all string fields) from VARCHAR(255) to TEXT.

• For the next-auth ORM models: Refer to docs for your adapter and look for a heading like "Custom models".
• For your underlying db schema: Do some db migration to ALTER the fields

@markflorkowski Can I ask, what Provider(s) and what Adapter you are using?

[markflorkowski]

Provider was Google for this particular user, and we are using the Prisma adapter.

We have already made the id_token field a TEXT column using Prisma's @db.text directive prior to experiencing this issue, will try with the other fields as well.

[LOGOLFGODORD]

Make sure its to my credential as my token under Apache code Justin IrishSent from Mail for Windows From: Mark R. FlorkowskiSent: Monday, June 20, 2022 11:26 PMTo: nextauthjs/next-authCc: SubscribedSubject: Re: [nextauthjs/next-auth] Unable to authenticate, "The provided value for the column is too long for the column's type" (Issue #4734) Provider was Google for this particular user, and we are using the Prisma adapter.We have already made the id_token field a TEXT column using Prisma's @db.text directive prior to experiencing this issue :/—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***> 

[balazsorban44]

The error message comes directly from Prisma, so the issue is likely with the model, what we can do is explore if we don't accidentally hide some details about the stack (like which column this is about).

[hussamkhatib]

I also had the same issue with google auth despite having Text type on id_token as you had too. The logs helped me where it broke. line 18

linkAccount: (data) => {
  console.log(data);
  return p.account.create({ data });
},

I found out the access_token is above 200 characters, I made it to Text type as well and it solved the issue for me.

model Account {
  access_token       String? @db.Text
  id_token           String? @db.Text
}

[clipartinc]

I'm having this same issue using mysql and the TypeORMLegacyAdapter adapter. If I change the data type to TEXT manually it changes back once a new call is made to the database. I've also updated the lib/entities.ts to use TEXT but id_token keeps changing back to VARCHAR (255).

Anyone know where this VARCHAR is coming from?

Also If I use synchronize: false I get an error [next-auth][error][adapter_error_getUserByAccount] https://next-auth.js.org/errors#adapter_error_getuserbyaccount metadata.findEmbeddedWithPropertyPath is not a function

[statusunknown418]

this error is still happening, for google provider at least

[clipartinc]

My solution was to change the varchar to mediumtext in the /node_modules/@next-auth/typeorm-legacy-adapter/dist/entities.js file for the id_token, but you also have to remove all data from the database columns users, sessions, and accounts to start fresh. Restart the app and it should keep the mediumtext setting and issues resolved :)

[genyrosk]

Found the same issue when using @auth/core with the DrizzleAdapter paired with a MySQL database and Google Authentication.

The solution was to simply change the id_token field from varchar('id_token', { length: 255 }) to just text('id_token').

Seems like a simple correction in the docs is needed.

[zenflow]

@genyrosk Where is the correction in docs needed? And isn't a correction in the ORM's schema definition needed too? That definition is provided by nextauth (unless this adapter is different from the rest) so that would be a breaking change I believe.

[genyrosk]

@genyrosk Where is the correction in docs needed? And isn't a correction in the ORM's schema definition needed too? That definition is provided by nextauth (unless this adapter is different from the rest) so that would be a breaking change I believe.

@zenflow

Here's the docs: https://authjs.dev/reference/adapter/drizzle#mysql

The following line:

export const accounts = mysqlTable(
  "account",
  {
    [...]
    id_token: varchar("id_token", { length: 255 }),
    [...]
  },
  [...]
)

I just changed it to:

export const accounts = mysqlTable(
  "account",
  {
    [...]
    id_token: text("id_token"),
    [...]
  },
  [...]
)

And everything worked fine. The original error was due to the Google jwt token being a lot longer than 255 characters.

[genyrosk]

@zenflow Just a quick follow up, seems like this can be found in the following files

https://github.com/search?q=repo%3Anextauthjs%2Fnext-auth%20id_token%3A%20varchar(%22id_token%22%2C%20%7B%20length%3A%20255%20%7D)%20%2C&type=code