Skip to content

Commit 76bb95b

Browse files
sakurai-ryosakurai-ryo
authored
fix(adapter-mariadb,adapter-planetscale): return strings for text columns with binary collation in raw queries (#29238)
Fixes #29237 ## Summary ### adapter-mariadb Replace the `BINARY_FLAG` check with a collation index check (`collation.index === 63`), matching the approach used by `mariadb-connector-nodejs`. https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/3.5.1/lib/cmd/decoder/text-decoder.js#L44 ### adapter-planetscale Add a charset-based collation check (`field.charset !== 63`) in `fieldToColumnType` to distinguish text columns from true binary columns. Vitess converts CHAR/VARCHAR/TEXT columns with a binary collation to BINARY/VARBINARY/BLOB respectively, so the charset value is used to detect this conversion. https://github.com/planetscale/database-js/blob/de78eebfaec8cd88c670b8c644fc5a3fd69e664c/src/cast.ts#L92 ## Root cause Because the `mapColumnType` function in `adapter-mariadb` returns `ColumnTypeEnum.Bytes` when a column has a Binary Collation, https://github.com/prisma/prisma/blob/7.4.1/packages/adapter-mariadb/src/conversion.ts#L86 and the `fieldToColumnType` function in `adapter-planetscale` does not inspect the charset at all for BLOB/BINARY/VARBINARY types, https://github.com/prisma/prisma/blob/7.4.1/packages/adapter-planetscale/src/conversion.ts#L85 the `deserializeValue` function unintentionally converts even string data into a `Uint8Array`, treating it as a base64 string. https://github.com/prisma/prisma/blob/7.4.1/packages/client/src/runtime/utils/deserializeRawResults.ts#L20 Since a Binary Collation can also apply to string data types like `CHAR`, the type determination logic in both `adapter-mariadb` and `adapter-planetscale` needs to be updated. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Bug Fixes** * Improved binary collation detection in MariaDB and PlanetScale database adapters. Columns with binary collation now correctly return as text data. * **Tests** * Added MySQL column type tests to validate binary collation handling in query results. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
1 parent 6db8a18 commit 76bb95b

6 files changed

Lines changed: 98 additions & 9 deletions

File tree

‎packages/adapter-mariadb/src/conversion.ts‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,8 @@ import { ArgType, ColumnType, ColumnTypeEnum, ResultValue } from '@prisma/driver
22
import * as mariadb from 'mariadb'
33

44
const UNSIGNED_FLAG = 1 << 5
5-
const BINARY_FLAG = 1 << 7
5+
// https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/be72ebf9fee6e0bd153b6ff6e0bb252f794dbf0e/lib/const/collations.js#L150
6+
const BINARY_COLLATION_INDEX = 63
67

78
const enum MariaDbColumnType {
89
DECIMAL = 'DECIMAL',
@@ -83,7 +84,10 @@ export function mapColumnType(field: mariadb.FieldInfo): ColumnType {
8384
// https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/1bbbb41e92d2123948c2322a4dbb5021026f2d05/lib/cmd/column-definition.js#L27
8485
if (field['dataTypeFormat'] === 'json') {
8586
return ColumnTypeEnum.Json
86-
} else if (field.flags.valueOf() & BINARY_FLAG) {
87+
}
88+
// The Binary flag of column definition applies to both text and binary data. To distinguish them, check if collation == 'binary' instead of checking the flag.
89+
// https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/be72ebf9fee6e0bd153b6ff6e0bb252f794dbf0e/lib/cmd/decoder/text-decoder.js#L44
90+
else if (field.collation.index === BINARY_COLLATION_INDEX) {
8791
return ColumnTypeEnum.Bytes
8892
} else {
8993
return ColumnTypeEnum.Text

‎packages/adapter-planetscale/src/conversion.ts‎

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,13 @@
1-
import { cast as defaultCast } from '@planetscale/database'
1+
import { cast as defaultCast, type Field } from '@planetscale/database'
22
import { ArgType, type ColumnType, ColumnTypeEnum } from '@prisma/driver-adapter-utils'
33

44
import { decodeUtf8 } from './text'
55

6+
// https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_character_set.html
7+
const BINARY_COLLATION_INDEX = 63
8+
69
// See: https://github.com/planetscale/vitess-types/blob/06235e372d2050b4c0fff49972df8111e696c564/src/vitess/query/v16/query.proto#L108-L218
7-
export type PlanetScaleColumnType =
10+
type PlanetScaleColumnType =
811
| 'NULL'
912
| 'INT8'
1013
| 'UINT8'
@@ -46,8 +49,9 @@ export type PlanetScaleColumnType =
4649
* module to see how other attributes of the field packet such as the field length are used to infer
4750
* the correct quaint::Value variant.
4851
*/
49-
export function fieldToColumnType(field: PlanetScaleColumnType): ColumnType {
50-
switch (field) {
52+
export function fieldToColumnType(field: Field): ColumnType {
53+
const type = field.type as PlanetScaleColumnType
54+
switch (type) {
5155
case 'INT8':
5256
case 'UINT8':
5357
case 'INT16':
@@ -85,6 +89,14 @@ export function fieldToColumnType(field: PlanetScaleColumnType): ColumnType {
8589
case 'BLOB':
8690
case 'BINARY':
8791
case 'VARBINARY':
92+
// vitess converts CHAR/VARCHAR/TEXT columns with a binary collation to BINARY/VARBINARY/BLOB respectively before returning them to @planetscale/database driver.
93+
// https://github.com/vitessio/vitess/blob/a94fa13f2ab53c98aad07a56eb15fe20b5ea7ade/go/sqltypes/type.go#L269
94+
// Therefore, we check the collation to distinguish between text and binary data.
95+
// https://github.com/planetscale/database-js/blob/de78eebfaec8cd88c670b8c644fc5a3fd69e664c/src/cast.ts#L92
96+
if (field.charset && field.charset !== BINARY_COLLATION_INDEX) {
97+
return ColumnTypeEnum.Text
98+
}
99+
return ColumnTypeEnum.Bytes
88100
case 'BIT':
89101
case 'BITNUM':
90102
case 'HEXNUM':
@@ -95,7 +107,7 @@ export function fieldToColumnType(field: PlanetScaleColumnType): ColumnType {
95107
// Fall back to Int32 for consistency with quaint.
96108
return ColumnTypeEnum.Int32
97109
default:
98-
throw new Error(`Unsupported column type: ${field}`)
110+
throw new Error(`Unsupported column type: ${type}`)
99111
}
100112
}
101113

‎packages/adapter-planetscale/src/planetscale.ts‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ import { Debug, DriverAdapterError } from '@prisma/driver-adapter-utils'
1818
import { Mutex } from 'async-mutex'
1919

2020
import { name as packageName } from '../package.json'
21-
import { cast, fieldToColumnType, mapArg, type PlanetScaleColumnType } from './conversion'
21+
import { cast, fieldToColumnType, mapArg } from './conversion'
2222
import { createDeferred, Deferred } from './deferred'
2323
import { convertDriverError } from './errors'
2424

@@ -54,7 +54,7 @@ class PlanetScaleQueryable<ClientT extends planetScale.Client | planetScale.Tran
5454
const columns = fields.map((field) => field.name)
5555
return {
5656
columnNames: columns,
57-
columnTypes: fields.map((field) => fieldToColumnType(field.type as PlanetScaleColumnType)),
57+
columnTypes: fields.map((field) => fieldToColumnType(field)),
5858
rows: rows as SqlResultSet['rows'],
5959
lastInsertId,
6060
}

‎packages/client/tests/functional/raw-queries/mysql-column-type/_matrix.ts‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
import { defineMatrix } from '../../_utils/defineMatrix'
2+
import { Providers } from '../../_utils/providers'
3+
4+
export default defineMatrix(() => [[{ provider: Providers.MYSQL }]])

‎packages/client/tests/functional/raw-queries/mysql-column-type/prisma/_schema.ts‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
import { idForProvider } from '../../../_utils/idForProvider'
2+
import testMatrix from '../_matrix'
3+
4+
export default testMatrix.setupSchema(({ provider }) => {
5+
return /* Prisma */ `
6+
generator client {
7+
provider = "prisma-client-js"
8+
output = "../generated/prisma/client"
9+
}
10+
11+
datasource db {
12+
provider = "${provider}"
13+
}
14+
15+
model User {
16+
id ${idForProvider(provider)}
17+
char_bin_collation String @db.Char(191)
18+
varchar_bin_collation String @db.VarChar(191)
19+
text_bin_collation String @db.Text
20+
}
21+
`
22+
})

‎packages/client/tests/functional/raw-queries/mysql-column-type/test.ts‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
import { Providers } from '../../_utils/providers'
2+
import testMatrix from './_matrix'
3+
// @ts-ignore
4+
import type { PrismaClient } from './generated/prisma/client'
5+
6+
declare let prisma: PrismaClient
7+
8+
testMatrix.setupTestSuite(
9+
() => {
10+
beforeAll(async () => {
11+
// Prisma Schema does not support specifying column collation
12+
await prisma.$executeRaw`ALTER TABLE \`User\` MODIFY \`char_bin_collation\` CHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL`
13+
await prisma.$executeRaw`ALTER TABLE \`User\` MODIFY \`varchar_bin_collation\` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL`
14+
await prisma.$executeRaw`ALTER TABLE \`User\` MODIFY \`text_bin_collation\` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL`
15+
})
16+
17+
beforeEach(async () => {
18+
await prisma.user.deleteMany()
19+
})
20+
21+
test('columns with _bin collation return strings, not Uint8Array', async () => {
22+
await prisma.user.create({
23+
data: {
24+
char_bin_collation: 'hello',
25+
varchar_bin_collation: 'hello',
26+
text_bin_collation: 'hello',
27+
},
28+
})
29+
30+
const result =
31+
(await prisma.$queryRaw`SELECT \`char_bin_collation\`, \`varchar_bin_collation\`, \`text_bin_collation\` FROM \`User\``) as Array<
32+
Record<string, unknown>
33+
>
34+
35+
expect(result).toHaveLength(1)
36+
expect(result[0].char_bin_collation).toBe('hello')
37+
expect(result[0].varchar_bin_collation).toBe('hello')
38+
expect(result[0].text_bin_collation).toBe('hello')
39+
})
40+
},
41+
{
42+
optOut: {
43+
from: [Providers.POSTGRESQL, Providers.SQLITE, Providers.MONGODB, Providers.COCKROACHDB, Providers.SQLSERVER],
44+
reason: 'This test is for MySQL-specific column type detection',
45+
},
46+
},
47+
)

0 commit comments

Comments
 (0)