github-starred/better-auth
2
0
Fork 0
mirror of https://github.com/better-auth/better-auth.git synced 2026-05-22 14:21:55 -05:00
Code Issues 2.5k Packages Projects Releases 112 Wiki Activity

BLOB/TEXT column 'id' used in key specification without a key length #13

New Issue
Closed
opened 2026-03-13 07:26:41 -05:00 by GiteaMirror · 2 comments
GiteaMirror commented 2026-03-13 07:26:41 -05:00
Owner
Copy Link

Originally created by @joesoeph on GitHub (Sep 29, 2024).

I am getting an error when running the command npx better-auth migrate on MySQL Database. This is an example of the auth.ts I am using.

export const auth = betterAuth({
  database: {
    provider: 'mysql',
    url: 'mysql://root:@127.0.0.1:3306/superOceanDB',
  },
  emailAndPassword: {
    enabled: true,
  },
  user: {
    modelName: 'users',
  },
  account: {
    modelName: 'accounts',
  },
  session: {
    modelName: 'sessions',
  },
});

And this is the error that appears in the terminal window:

...at async CreateTableBuilder.execute (file:///Users/yusuf/Documents/sandbox/next-js/super-ocean/node_modules/kysely/dist/esm/schema/create-table-builder.js:350:9) {
  code: 'ER_BLOB_KEY_WITHOUT_LENGTH',
  errno: 1170,
  sqlState: '42000',
  sqlMessage: "BLOB/TEXT column 'id' used in key specification without a key length",
  sql: 'create table `users` (`id` text primary key, `name` text not null, `email` text not null, `emailVerified` boolean not null, `image` text, `createdAt` date not null, `updatedAt` date not null)'
}

Node.js v20.14.0

The error does not occur when running with an SQLite database.

When I debug the generated query,

CREATE TABLE `users` (
	`id` text PRIMARY KEY,
	`name` text NOT NULL,
	`email` text NOT NULL,
	`emailVerified` boolean NOT NULL,
	`image` text,
	`createdAt` date NOT NULL,
	`updatedAt` date NOT NULL)

I get the same error

Query 1 ERROR at Line 1: : BLOB/TEXT column 'id' used in key specification without a key length

I suspect this is due to the table schema generator.

Originally created by @joesoeph on GitHub (Sep 29, 2024). I am getting an error when running the command `npx better-auth migrate` on MySQL Database. This is an example of the `auth.ts` I am using. ```typescript export const auth = betterAuth({ database: { provider: 'mysql', url: 'mysql://root:@127.0.0.1:3306/superOceanDB', }, emailAndPassword: { enabled: true, }, user: { modelName: 'users', }, account: { modelName: 'accounts', }, session: { modelName: 'sessions', }, }); ``` And this is the error that appears in the terminal window: ```bash ...at async CreateTableBuilder.execute (file:///Users/yusuf/Documents/sandbox/next-js/super-ocean/node_modules/kysely/dist/esm/schema/create-table-builder.js:350:9) { code: 'ER_BLOB_KEY_WITHOUT_LENGTH', errno: 1170, sqlState: '42000', sqlMessage: "BLOB/TEXT column 'id' used in key specification without a key length", sql: 'create table `users` (`id` text primary key, `name` text not null, `email` text not null, `emailVerified` boolean not null, `image` text, `createdAt` date not null, `updatedAt` date not null)' } Node.js v20.14.0 ``` The error does not occur when running with an SQLite database. When I debug the generated query, ```sql CREATE TABLE `users` ( `id` text PRIMARY KEY, `name` text NOT NULL, `email` text NOT NULL, `emailVerified` boolean NOT NULL, `image` text, `createdAt` date NOT NULL, `updatedAt` date NOT NULL) ``` I get the same error `Query 1 ERROR at Line 1: : BLOB/TEXT column 'id' used in key specification without a key length` I suspect this is due to the table schema generator.
GiteaMirror commented 2026-03-13 07:26:45 -05:00
Author
Owner
Copy Link

@baraich commented on GitHub (Sep 29, 2024):

@Bekacru Bro, what have you done, my exam was tomorrow and for contribution I neglected it and you merged another pull request. And you can also see that I made a commit earlier to you.

@baraich commented on GitHub (Sep 29, 2024): @Bekacru Bro, what have you done, my exam was tomorrow and for contribution I neglected it and you merged another pull request. And you can also see that I made a commit earlier to you.
GiteaMirror commented 2026-03-13 07:26:46 -05:00
Author
Owner
Copy Link

@jericopulvera commented on GitHub (Apr 20, 2025):

I encountered this issue with mysql using drizzleAdapter.

  code: 'ER_BLOB_KEY_WITHOUT_LENGTH',
  errno: 1170,
  sql: '\n' +
    'ALTER TABLE `account` ADD CONSTRAINT `account_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;',
  sqlState: '42000',
  sqlMessage: "BLOB/TEXT column 'user_id' used in key specification without a key length"

below is the sql file generated from drizzle db generate.

CREATE TABLE `account` (
	`id` varchar(36) NOT NULL,
	`account_id` text NOT NULL,
	`provider_id` text NOT NULL,
	`user_id` text NOT NULL,
	`access_token` text,
	`refresh_token` text,
	`id_token` text,
	`access_token_expires_at` timestamp,
	`refresh_token_expires_at` timestamp,
	`scope` text,
	`password` text,
	`created_at` timestamp NOT NULL,
	`updated_at` timestamp NOT NULL,
	CONSTRAINT `account_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `session` (
	`id` varchar(36) NOT NULL,
	`expires_at` timestamp NOT NULL,
	`token` varchar(255) NOT NULL,
	`created_at` timestamp NOT NULL,
	`updated_at` timestamp NOT NULL,
	`ip_address` text,
	`user_agent` text,
	`user_id` text NOT NULL,
	`impersonated_by` text,
	CONSTRAINT `session_id` PRIMARY KEY(`id`),
	CONSTRAINT `session_token_unique` UNIQUE(`token`)
);
--> statement-breakpoint
CREATE TABLE `user` (
	`id` varchar(36) NOT NULL,
	`name` text NOT NULL,
	`email` varchar(255) NOT NULL,
	`email_verified` boolean NOT NULL,
	`image` text,
	`created_at` timestamp NOT NULL,
	`updated_at` timestamp NOT NULL,
	`role` text,
	`banned` boolean,
	`ban_reason` text,
	`ban_expires` timestamp,
	CONSTRAINT `user_id` PRIMARY KEY(`id`),
	CONSTRAINT `user_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE TABLE `verification` (
	`id` varchar(36) NOT NULL,
	`identifier` text NOT NULL,
	`value` text NOT NULL,
	`expires_at` timestamp NOT NULL,
	`created_at` timestamp,
	`updated_at` timestamp,
	CONSTRAINT `verification_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
ALTER TABLE `account` ADD CONSTRAINT `account_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `session` ADD CONSTRAINT `session_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;
@jericopulvera commented on GitHub (Apr 20, 2025): I encountered this issue with mysql using drizzleAdapter. ``` code: 'ER_BLOB_KEY_WITHOUT_LENGTH', errno: 1170, sql: '\n' + 'ALTER TABLE `account` ADD CONSTRAINT `account_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;', sqlState: '42000', sqlMessage: "BLOB/TEXT column 'user_id' used in key specification without a key length" ``` below is the sql file generated from drizzle db generate. ```sql CREATE TABLE `account` ( `id` varchar(36) NOT NULL, `account_id` text NOT NULL, `provider_id` text NOT NULL, `user_id` text NOT NULL, `access_token` text, `refresh_token` text, `id_token` text, `access_token_expires_at` timestamp, `refresh_token_expires_at` timestamp, `scope` text, `password` text, `created_at` timestamp NOT NULL, `updated_at` timestamp NOT NULL, CONSTRAINT `account_id` PRIMARY KEY(`id`) ); --> statement-breakpoint CREATE TABLE `session` ( `id` varchar(36) NOT NULL, `expires_at` timestamp NOT NULL, `token` varchar(255) NOT NULL, `created_at` timestamp NOT NULL, `updated_at` timestamp NOT NULL, `ip_address` text, `user_agent` text, `user_id` text NOT NULL, `impersonated_by` text, CONSTRAINT `session_id` PRIMARY KEY(`id`), CONSTRAINT `session_token_unique` UNIQUE(`token`) ); --> statement-breakpoint CREATE TABLE `user` ( `id` varchar(36) NOT NULL, `name` text NOT NULL, `email` varchar(255) NOT NULL, `email_verified` boolean NOT NULL, `image` text, `created_at` timestamp NOT NULL, `updated_at` timestamp NOT NULL, `role` text, `banned` boolean, `ban_reason` text, `ban_expires` timestamp, CONSTRAINT `user_id` PRIMARY KEY(`id`), CONSTRAINT `user_email_unique` UNIQUE(`email`) ); --> statement-breakpoint CREATE TABLE `verification` ( `id` varchar(36) NOT NULL, `identifier` text NOT NULL, `value` text NOT NULL, `expires_at` timestamp NOT NULL, `created_at` timestamp, `updated_at` timestamp, CONSTRAINT `verification_id` PRIMARY KEY(`id`) ); --> statement-breakpoint ALTER TABLE `account` ADD CONSTRAINT `account_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE `session` ADD CONSTRAINT `session_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action; ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
adapter
astro
awaiting external contributor
blocked
breaking
breaking change
bug
c-devops
core
credentials
database
dependencies
devops
devtools
docs
documentation
duplicate
elysia
enhancement
enterprise
expo
express
fastify
good first issue
help wanted
hono
identity
infra
integration
invalid
javascript
locked
maintenance
need-more-information
needs: info
needs: repro
nextjs
nuxt
oauth
organization
P0
payments
perf
platform
plugin
pull-request
Mirrored from GitHub Pull Request
 question
ready
regression
remix
security
social-provider
solid
stale
svelte
tanstack-start
tracking
version-bump
vue
wontfix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/better-auth#13
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?