TypeORM Synchronize Error: QueryFailedError: coercion should not try to coerce this value: BIT("")
#590
-
|
I'm encountering a very similar problem to #483 with TypeORM and PlanetScale, but a different error message. Offending SQL is further below.
Notes
VersionsEntities (Simple OneToMany / ManyToOne)
import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from 'typeorm';
import { Workspace } from '~entities/workspace.entity';
@Entity()
export class Organization {
@PrimaryGeneratedColumn('increment')
organizationId: number;
@Column()
organizationName: string;
@OneToMany(() => Workspace, (workspace) => workspace.organization, {
createForeignKeyConstraints: false,
})
workspaces: Workspace[];
}
import {
Column,
Entity,
JoinColumn,
ManyToOne,
PrimaryGeneratedColumn,
} from 'typeorm';
import { Organization } from '~entities/organization.entity';
@Entity()
export class Workspace {
@PrimaryGeneratedColumn('increment')
workspaceId: number;
@Column()
workspaceName: string;
@ManyToOne(() => Organization, (organization) => organization.workspaces, {
createForeignKeyConstraints: false,
})
@JoinColumn({ name: 'organizationId' })
organization: Organization;
}ErrorRaw SQL and Error (MySQL Workbench)I really enjoy PlanetScale, but this is pretty crappy. Hoping there's some movement on this, unfortunately I'm going to need to look at Neon or DigitalOcean, or even just stick with RDS. Prisma works great with PlanetScale, but performance-wise, TypeORM is demonstrably faster. |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 9 replies
-
|
Hello @mikechabot, A TypeORM user receiving that specific coerce message is new to me as the errors mentioned in discussions#483 have been the more common ones reported. The remaining panic issue mentioned in that discussion was recently fixed last week: I am currently waiting on this fix to make its way into PlanetScale databases to provide an update to the users in the other discussion item so I'm hoping that this week's weekly Vitess release includes it so I can then share that with the others. Assuming that this last fix also helps to address the coerce issue you recently experienced too will be great but since it's a new report it's unclear whether this is something different. I've pulled up some details for your database, and have some things we can try out, but in order to simplify the back and forth required could you please write in to https://support.planetscale.com/ for me and we can begin to work more closely together on the issue you reported here? |
Beta Was this translation helpful? Give feedback.
-
|
Thanks @mikechabot! After seeing your update I went back and double-checked on the retest I completed yesterday and observed my mistake and retrying again today does confirm the same behavior is present as you shared even in the newer images so the issue is still active. I'll check back in on the issue I created for our Vitess team to see if we can get this one looked at soon as I feel it is the last remaining issue that might be present for our TypeORM users! To quickly summarize my minimal reproduction it can be recreated without TypeORM simply by creating one real table within a database and then running a query that performs a -- At least one real table needs to exist and be used after the `UNION` operator in the test query:
CREATE TABLE `real_table` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE InnoDB,
CHARSET utf8mb4,
COLLATE utf8mb4_unicode_ci;-- Example problematic query:
SELECT `TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COMMENT`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = '<REPLACE_WITH_YOUR_SCHEMA_NAME>'
AND `TABLE_NAME` = 'nonexistent_table'
UNION
SELECT `TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COMMENT`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = '<REPLACE_WITH_YOUR_SCHEMA_NAME>'
AND `TABLE_NAME` = 'real_table';My mistake when retesting yesterday was that I forgot to complete the I guess this additional variation might be simpler in this case since it avoids that extra step and also leads to the same error: SELECT `TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COMMENT`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_NAME` = 'nonexistent_table'
UNION
SELECT `TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COMMENT`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_NAME` = 'real_table'; |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for following up with the team on this, I really appreciate it! The silver lining is this forced me to consider migration files from the onset, which has actually been helpful. And likewise, here's the reproduction in MySQL workbench from the original simple schema. I'm glad you were able to nail down the problematic statement -- my google fu failed me on this one.
|
Beta Was this translation helpful? Give feedback.
-
|
Today I encountered the same problem when I wanted to generate migrations using TypeORM, when will it be fixed? |
Beta Was this translation helpful? Give feedback.
-
|
@tup4ienko, Thank you for sharing you are also encountering this issue too! I'll ask and see if there is any update from our end and share back here once I have some news. |
Beta Was this translation helpful? Give feedback.
-
|
@mikechabot and @tupchienko, I received an update from our Vitess team on the issue I had created for this and it looks like the underlying cause should be resolved now as we weren't able to reproduce the issue again using my reproduction steps so it looks like other recent fixes within Vitess may have also helped to resolve the coercion issue that was being experienced here. I hope this can be confirmed on your end too and if so then this may help allow for TypeORM users to have a smoother experience now with their PlanetScale databases! |
Beta Was this translation helpful? Give feedback.
Hello @mikechabot,
A TypeORM user receiving that specific coerce message is new to me as the errors mentioned in discussions#483 have been the more common ones reported.
The remaining panic issue mentioned in that discussion was recently fixed last week:
vitessio/vitess#14906
I am currently waiting on this fix to make its way into PlanetScale databases to provide an update to the users in the other discussion item so I'm hoping that this week's weekly Vitess release includes it so I can then share that with the others.
Assuming that this last fix also helps to address the coerce issue you recently experienced too will be great but since it's a new report it's unclear whether this is somet…