cemcakirlar
commented
1 year ago Similar to https://github.com/prisma/prisma/issues/6167 But I hope my issue have more information for the team to inspect. Thanks.
Open cemcakirlar opened 1 year ago
cemcakirlar
commented
1 year ago Similar to https://github.com/prisma/prisma/issues/6167 But I hope my issue have more information for the team to inspect. Thanks.
janpio
commented
1 year ago Internal Note: The temporary table here the user is talking about is @generated_keys that is created inside the query. Later when the query tries to join the temporary table and the source table, this fails because of a collation mismatch. Reproduction would need to figure out if this happens in a default configuration, or if collations need to be adapted in some way to trigger this. (https://github.com/prisma/prisma/issues/6167 indeed looks like the same problem, just with different collations.)
janpio
commented
1 month ago It unfortunately does not reproduce in a default setup:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
}
model BS_Inbound {
id String @id(map: "PK_BS_Inbound") @db.VarChar(32)
sql_label String? @db.NVarChar(250)
RecId String @db.Char(32)
LastModDateTime DateTime? @db.DateTime
LastModBy String? @db.VarChar(200)
CreatedDateTime DateTime? @db.DateTime
CreatedBy String? @db.VarChar(200)
Path String? @db.NVarChar(250)
Status String? @db.NVarChar(100)
Source String? @db.NVarChar(100)
CustomerLink_RecID String? @db.VarChar(32)
CustomerLink_Category String? @db.VarChar(61)
CustomerLink_VRecID String? @db.VarChar(32)
VoxconRefId String? @db.NVarChar(50)
InboundNumber Decimal? @db.Decimal(11, 0)
StatusText String? @db.NVarChar(100)
Status_Valid String? @db.VarChar(32)
Source_Valid String? @db.VarChar(32)
Phone String? @db.NVarChar(50)
FullAge Boolean?
MinBirthDate DateTime? @db.DateTime
IsFinalState Boolean?
Junkpressed Boolean?
CallDateTime DateTime? @db.DateTime
CalledNumber String? @db.VarChar(25)
PhoneReadOnly Boolean?
Notes String?
ParentLink_RecID String? @db.VarChar(32)
ParentLink_Category String? @db.VarChar(61)
ParentLink_VRecID String? @db.VarChar(32)
Bayi String? @db.VarChar(100)
NextRecID String? @db.VarChar(32)
Soru1 String? @db.NVarChar(400)
Cevap1 String? @db.NVarChar(20)
Soru2 String? @db.NVarChar(400)
Cevap2 String? @db.NVarChar(20)
Soru3 String? @db.NVarChar(400)
Cevap3 String? @db.NVarChar(20)
Soru4 String? @db.NVarChar(400)
Cevap4 String? @db.NVarChar(20)
Soru5 String? @db.NVarChar(400)
Cevap5 String? @db.NVarChar(20)
Cevap1_Valid String? @db.VarChar(32)
Cevap2_Valid String? @db.VarChar(32)
Cevap3_Valid String? @db.VarChar(32)
Cevap4_Valid String? @db.VarChar(32)
Cevap5_Valid String? @db.VarChar(32)
ClosedBy String? @db.NVarChar(100)
ClosedDateTime DateTime? @db.DateTime
ToplamSure Decimal? @db.Decimal(7, 2)
}const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient({ log: ['query'] })
async function main() {
const id = "foobar"
const Notes = "This is a note for the inbound with id 'foobar'"
const inbound = await prisma.bS_Inbound.upsert({
where: {
id
},
create: {
id,
RecId: id,
Notes
},
update: {
Notes
},
select: {
id: true
}
})
}
main()
.then(async () => {
await prisma.$disconnect()
})
.catch(async (e) => {
console.error(e)
await prisma.$disconnect()
process.exit(1)
})Runs successfully both for create and update case of the upsert():
> node .\script.js
prisma:query BEGIN TRAN
prisma:query SELECT [dbo].[BS_Inbound].[id] FROM [dbo].[BS_Inbound] WHERE ([dbo].[BS_Inbound].[id] = @P1 AND 1=1)
prisma:query DECLARE @generated_keys table([id] NVARCHAR(32)) INSERT INTO [dbo].[BS_Inbound] ([id],[RecId],[Notes]) OUTPUT [Inserted].[id] INTO @generated_keys VALUES (@P1,@P2,@P3) SELECT [t].[id] FROM @generated_keys AS g INNER JOIN [dbo].[BS_Inbound] AS [t] ON [t].[id] = [g].[id] WHERE @@ROWCOUNT > 0
prisma:query SELECT [dbo].[BS_Inbound].[id] FROM [dbo].[BS_Inbound] WHERE [dbo].[BS_Inbound].[id] = @P1 ORDER BY 1 OFFSET @P2 ROWS FETCH NEXT @P3 ROWS ONLY
prisma:query COMMIT> node .\script.js
prisma:query BEGIN TRAN
prisma:query SELECT [dbo].[BS_Inbound].[id] FROM [dbo].[BS_Inbound] WHERE ([dbo].[BS_Inbound].[id] = @P1 AND 1=1)
prisma:query UPDATE [dbo].[BS_Inbound] SET [Notes] = @P1 WHERE ([dbo].[BS_Inbound].[id] IN (@P2) AND ([dbo].[BS_Inbound].[id] = @P3 AND 1=1))
prisma:query SELECT [dbo].[BS_Inbound].[id] FROM [dbo].[BS_Inbound] WHERE [dbo].[BS_Inbound].[id] = @P1 ORDER BY 1 OFFSET @P2 ROWS FETCH NEXT @P3 ROWS ONLY
prisma:query COMMITThis is the SQL to migrate the table, it also does not define a collation (similar how the DECLARE @generated_keys table ... above did not either):
BEGIN TRY
BEGIN TRAN;
-- CreateTable
CREATE TABLE [dbo].[BS_Inbound] (
[id] VARCHAR(32) NOT NULL,
[sql_label] NVARCHAR(250),
[RecId] CHAR(32) NOT NULL,
[LastModDateTime] DATETIME,
[LastModBy] VARCHAR(200),
[CreatedDateTime] DATETIME,
[CreatedBy] VARCHAR(200),
[Path] NVARCHAR(250),
[Status] NVARCHAR(100),
[Source] NVARCHAR(100),
[CustomerLink_RecID] VARCHAR(32),
[CustomerLink_Category] VARCHAR(61),
[CustomerLink_VRecID] VARCHAR(32),
[VoxconRefId] NVARCHAR(50),
[InboundNumber] DECIMAL(11,0),
[StatusText] NVARCHAR(100),
[Status_Valid] VARCHAR(32),
[Source_Valid] VARCHAR(32),
[Phone] NVARCHAR(50),
[FullAge] BIT,
[MinBirthDate] DATETIME,
[IsFinalState] BIT,
[Junkpressed] BIT,
[CallDateTime] DATETIME,
[CalledNumber] VARCHAR(25),
[PhoneReadOnly] BIT,
[Notes] NVARCHAR(1000),
[ParentLink_RecID] VARCHAR(32),
[ParentLink_Category] VARCHAR(61),
[ParentLink_VRecID] VARCHAR(32),
[Bayi] VARCHAR(100),
[NextRecID] VARCHAR(32),
[Soru1] NVARCHAR(400),
[Cevap1] NVARCHAR(20),
[Soru2] NVARCHAR(400),
[Cevap2] NVARCHAR(20),
[Soru3] NVARCHAR(400),
[Cevap3] NVARCHAR(20),
[Soru4] NVARCHAR(400),
[Cevap4] NVARCHAR(20),
[Soru5] NVARCHAR(400),
[Cevap5] NVARCHAR(20),
[Cevap1_Valid] VARCHAR(32),
[Cevap2_Valid] VARCHAR(32),
[Cevap3_Valid] VARCHAR(32),
[Cevap4_Valid] VARCHAR(32),
[Cevap5_Valid] VARCHAR(32),
[ClosedBy] NVARCHAR(100),
[ClosedDateTime] DATETIME,
[ToplamSure] DECIMAL(7,2),
CONSTRAINT [PK_BS_Inbound] PRIMARY KEY CLUSTERED ([id])
);
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN;
END;
THROW
END CATCH
Any idea how your database can actually end up in a situation where the collation of the temporary table and the normal tables are conflicting with Prisma @cemcakirlar? I think that information will be required for us to continue here.
SevInf
commented
2 weeks ago Hey @cemcakirlar ,
Did you get a chance to have a look at my collegues comment? It'd be helpful for us to move your issue forward.
Without further information, we'll have to close this issue. Thank you 🙏
Bug description
prisma..insert({...})orupsert({...})fails at database level with collation conflict. The generated sql statement is as follows.DECLARE @generated_keys table([id] NVARCHAR(32)) INSERT INTO [pullable].[BS_Inbound] ([id],[RecId],[Notes]) OUTPUT [Inserted].[id] INTO @generated_keys VALUES (@P1,@P2,@P3) SELECT [t].[id] FROM @generated_keys AS g INNER JOIN [pullable].[BS_Inbound] AS [t] ON [t].[id] = [g].[id] WHERE @@ROWCOUNT > 0The problem lies in this part of the generated sql statement
[t].[id] = [g].[id]Eventhough the sqlserver, database and table collations are allSQL_Latin1_General_CP1_CI_AS, the temporary table variables column is somehow created asLatin1_General_CP1_CI_AS, which I myself could not figure out why.But this is a common setup, I believe, and the solution seems to be adding collation information to the string field joins at this particular case. This approach should/may also solve other issues with collations, Maybe someone has COLLATION XX on tempdb but COLLLATION YY on a table. Testing will be required though.
[t].[id] COLLATE database_default = [g].[id] COLLATE database_defaultMore info as error message in console.
How to reproduce
It may be hard to reproduce but a prisma client with sqlserver connection and an update({}) should suffice. It may depend on the sql servers collation settings also.
Expected behavior
The .upsert({...}) should succeed.
Prisma information
Environment & setup
Prisma Version