Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UpdateDateColumn: multiple assignments to same column #2651

Closed
felixle236 opened this issue Aug 12, 2018 · 26 comments
Closed

UpdateDateColumn: multiple assignments to same column #2651

felixle236 opened this issue Aug 12, 2018 · 26 comments

Comments

@felixle236
Copy link

felixle236 commented Aug 12, 2018

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[x] 0.2.7

Steps to reproduce or a small repository showing the problem:

I'm getting an error when updating with postgres using UpdateDateColumn.

Columns of entity:

@CreateDateColumn({type: 'timestamptz'})
createdAt: Date;

@UpdateDateColumn({type: 'timestamptz'})
updatedAt: Date;

Update function:

async update(id: number, data: any): Promise<boolean> {
    if (!id || !validator.isInt(id) || !data)
        throw new ValidationError(1);

    let user = await this.userRepository.createQueryBuilder('user')
        .whereInIds([id])
        .andWhere('user.deletedAt IS NULL')
        .getOne();

    if (!user)
        throw new ValidationError(104, 'user');

    DataHelper.filterDataInput(user, data, [
        'firstName',
        'lastName',
        'gender',
        'birthday',
        'phone',
        'address',
        'culture',
        'currency'
    ]);
    user.validate();

    let result = await this.userRepository.update(id, user);
    console.log(result);
    return true;
}

Logging:

query failed: UPDATE "user" SET "id" = $2, "roleId" = $3, "firstName" = $4, "lastName" = $5, "email" = $6, "password" = $7, "avatar" = $8, "gender" = $9, "birthday" = $10, "phone" = $11, "address" = $12, "culture" = $13, "currency" = $14, "accessToken" = $15, "tokenExpire" = $16, "createdAt" = $17, "updatedAt" = $18, "deletedAt" = $19, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" = $1 -- PARAMETERS: [1,1,1,"Admin","Local","admin@localhost.com","818d6f2daf098f9043b7cc5a167c883d",null,1,null,null,null,null,null,null,null,"2018-08-12T08:07:04.099Z","2018-08-12T08:07:04.099Z",null]

Error:

QueryFailedError: multiple assignments to same column "updatedAt"

We can see "updatedAt" = $18 and "updatedAt" = CURRENT_TIMESTAMP WHERE has appeared tiwce. I just want to suggest that we should ignore CreateDateColumn and UpdateDateColumn when generate query string?

Also I have tried to update successfully, but I cannot get the status update into UpdateResult:

UpdateResult {generatedMaps: Array(0), raw: Array(0)}

Thanks so much.

@zerox12311
Copy link

I faced a same problem...

@ematipico
Copy link

Same problem here

@akosasante
Copy link
Contributor

I am also facing this issue currently.

Job Entity with UpdateDate column:

@Entity()
export class Job {
    public static isValid(jobObj: JobObject): boolean {
        return !!jobObj.startLocation && !!jobObj.endLocation;
    }
...
    @CreateDateColumn()
    public dateCreated: Date;

    @UpdateDateColumn()
    public dateModified: Date;

    @ManyToOne(type => Customer, (customer: Customer) => customer.jobs, {cascade: true})
    public customer: Customer;
...
}

I encountered this error when trying to write a unit test for updating the customer relation of the job entity:

it("should change customer if different customer passed with job", async () => {
        const newCustomer: CustomerObject = {companyName: "Brand New Company"};
        const oldJobWithCustomer = await dbJob.getJobById(2, true);
        oldJobWithCustomer.customer = newCustomer;
        const newJob = await dbJob.updateJob(2, jobObj);
});

The above returns the error:
error: multiple assignments to same column "dateModified"

and this is the failed query:

UPDATE "test"."job" SET "id" = $1, "description" = $2, "startLocation" = $3, "endLocation" = $4, "status" = $5, "jobCost" = $6, "dateCreated" = $7, "dateModified" = $8, "customerId" = $9, "dateModified" = CURRENT_TIMESTAMP WHERE "id" = $10 RETURNING *;
               -- PARAMETERS: 2,,{"city":"Testopia","street":"9 Test St.","province":"Ontario","postalCode":"X0X0X0"},{"city":"Fakeville","street":"1 Fake Ave.","province":"Alberta","postalCode":"P9P1X0"},Ready,,Sat Oct 20 2018 23:09:19 GMT-0400 (Eastern Daylight Time),,,2

As with OP, the dateModified column is getting set twice, once with what I assume is the time the customer property was changed, and once with CURRENT TIMESTAMP. What is the recommended workaround here?

@akosasante
Copy link
Contributor

So far I've worked around (or perhaps this is what's expected) by parsing the JobObject and making sure it doesn't have it's own dateModified property when I pass it to the .updateJob method.

I encountered the same thing with foreign keys/relations. Where if I update the job.customerId value but leave the job.customer object then it comes up with the same error. So I just delete job.customer before saving.

@sgomanf
Copy link

sgomanf commented Dec 25, 2018

same issue, not happen with save method.

@pleerock pleerock added the bug label Dec 25, 2018
@pleerock
Copy link
Member

its a bug. We need to exclude adding update column into the list if its already was set in the list provided by set method.

@ykartoev
Copy link

ykartoev commented Jan 9, 2019

I guess you've meant exclude "updated" column, because bug not only in update column. As @akosasante, I'm getting multiple assignments to foreign key, while saving relation entities through cascades.

@danielbischoff
Copy link

Any progress here, as I have the same problem?

@felixle236
Copy link
Author

Any progress here, as I have the same problem?

It's still there, I must exclude that field before execute:

delete user.updatedAt;

There seems to be some reason that this error has not been fixed.

@pleerock
Copy link
Member

pleerock commented Mar 1, 2019

Do not manually assign updatedAt column value, exclude this property from the assignment list. Value of the column decorated by @UpdatedColumn is controlled by orm, its the whole point of this decorator.

@knownasilya
Copy link

knownasilya commented May 30, 2019

I'm seeing a similar issue:

"pg": "^7.11.0",
"typeorm": "^0.2.17",

Screen Shot 2019-05-30 at 3 06 20 PM

The sql generated is : "UPDATE "discover_result" SET "result" = $2, "profileId" = $3, "profileId" = $4, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" IN ($1) RETURNING "updatedAt"" but as you noticed in the screenshot, there are only 3 parameters. profileId is mentioned twice in the sql for some reason.

Code looks like so:

    let discover = await DiscoverResult.findOneOrFail(discoverId);
    // later
    await discover.update({
      profileId: profile && profile.id,
      userEmail: !user ? email : undefined,
      result
    });

Where update is:

  async update(data: object) {
    Object.assign(this, data);
    await this.save();
    return this;
  }

@knownasilya
Copy link

knownasilya commented May 30, 2019

Looks like two profileId column names are generated, one automatically for profile

  @ManyToOne(_type => Profile, { eager: true, nullable: true })
  @Field(_type => Profile)
  profile: Profile;

  @Column({ nullable: true })
  profileId: string;

Changing the update to this, solved my problem.

    await discover.update({
      profile,
      userEmail: !user ? email : undefined,
      result
    });

@matomesc
Copy link

matomesc commented Sep 7, 2019

I have a simple save this.messageRepository.save(message) which sometimes generates this incorrect query:

UPDATE "message" 
SET "customerId" = $2, 
  "checkoutId" = $3, 
  "customerId" = $4, 
  "checkoutId" = $5, 
  "updatedAt" = CURRENT_TIMESTAMP 
WHERE "id" IN ($1) RETURNING "updatedAt"

As you can see customerId and checkoutId are set twice.

Note: The parameters array is always correct - it has exactly 3 params (id, customerId and checkoutId)

The entity relationships are many-to-one and look like:

@ManyToOne(_type => Checkout, checkout => checkout.messages, {
  onDelete: 'SET NULL',
  onUpdate: 'RESTRICT'
})
@JoinColumn({ name: 'checkoutId' })
@Index()
public checkout: Checkout | null;

@Column('text', { nullable: true })
public checkoutId: string | null;

The error occurs quite rarely and I'm having a hard time reproducing but I do see it in my production app.

@Ginden
Copy link
Collaborator

Ginden commented Dec 4, 2019

I have code that always reproduce this issue:

const p: Post = Post.findOne({id: 42});
await connection.getRepository(Post).update({id: 42}, p);

@felixle236
Copy link
Author

To resolve this issue, please use update option:

import { Column, CreateDateColumn, Entity, Index, OneToMany, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
import { Permission } from './Permission';
import { RoleCode } from '../../config/Enums';
import { RoleSchema } from '../schemas/RoleSchema';
import { User } from './User';

@Entity(RoleSchema.TABLE_NAME)
@Index((role: Role) => [role.code, role.deletedAt], { unique: true })
@Index((role: Role) => [role.name, role.deletedAt], { unique: true })
export class Role {
    @PrimaryGeneratedColumn({ name: RoleSchema.COLUMNS.ID })
    id: number;

    @CreateDateColumn({ name: RoleSchema.COLUMNS.CREATED_AT, type: 'timestamptz', update: false })
    createdAt: Date;

    @UpdateDateColumn({ name: RoleSchema.COLUMNS.UPDATED_AT, type: 'timestamptz', update: false })
    updatedAt: Date;

    @Column('timestamptz', { name: RoleSchema.COLUMNS.DELETED_AT, nullable: true })
    deletedAt?: Date;

    @Column('smallint', { name: RoleSchema.COLUMNS.CODE })
    code: RoleCode;

    @Column({ name: RoleSchema.COLUMNS.NAME, length: 50 })
    name: string;

    @Column('smallint', { name: RoleSchema.COLUMNS.LEVEL })
    level: number;

    /* Relationship */

    @OneToMany(() => User, user => user.role)
    users?: User[];

    @OneToMany(() => Permission, permission => permission.role)
    permissions?: Permission[];
}

But I still got issue about foreign key, I want to define foreign key by myself. Maybe we will have update option for JoinColumn like above?

@Column({ name: UserSchema.COLUMNS.ROLE_ID })
roleId: number;

@ManyToOne(() => Role, role => role.users)
@JoinColumn({ name: UserSchema.COLUMNS.ROLE_ID })
role?: Role;

@foloinfo
Copy link

foloinfo commented Mar 2, 2020

It seems like this error happens when you define some relationship and FK at the same time.

  @OneToOne(type => File, { eager: true, nullable: true })
  @JoinColumn({ name: 'imageId' })
  @IsOptional()
  image?: File

  @Column({ type: 'bigint', nullable: true })
  @IsOptional()
  imageId?: string

A definition like above caused multiple assignments to the same column error when updating image with null like this.image = null.
It seems like typeorm will set image and imageId as null, so it will cause multiple assignments to imageId when issuing UPDATE query.

I could avoid this error by adding this.imageId = undefined before saving data. It will prevent setting imageId but it will set image correctly.
ex.

this.image = null // or maybe some image model
this.imageId = undefined
// do whatever to save data.

pleerock added a commit that referenced this issue Mar 2, 2020
@felixle236
Copy link
Author

@pleerock awesome 👏
@UpdatedColumn decorator has been resolved. But I still got the issue about foreign key above, so can we have an option like update to resolve it?

@swymmwys
Copy link

@pleerock
any updates on the same issue but with the foreign key column?

@ghost
Copy link

ghost commented May 18, 2020

Used repository.save() instead of repository.update() and it worked.
Verified SQL logs and I can see both Select and Update statements executed.

@evgenytk
Copy link

@pleerock
any updates on the same issue but with the foreign key column?

The problem is in the eager loaded relations. Updating an FK manually while the relation property is loaded causes multiple assignments to the join column.

The solution is either don't use the eager option, e.g:
@OneToOne(type => Profile, { eager: false, nullable: true })
@JoinColumn()
profile: Profile;
@Column({ nullable: true })
profileId: string;

either set the relation property to undefined or delete it, e.g.:
const user = await userRepositofy.find();
user.profileId = '4288fb3f-b1d9-40cb-acb8-01ef52709b1c';
user.profile = undefined; or delete user.profile;

@imnotjames imnotjames changed the title QueryFailedError: multiple assignments to same column UpdateDateColumn: multiple assignments to same column Oct 17, 2020
@imnotjames
Copy link
Contributor

Agreed on that one. If anyone still believes the FK issue is a real bug and not user error please open a new issue with more info. For this issue it's off topic.

Closing this issue as the original problem has been solved.

@knownasilya
Copy link

Yes it's still a bug. At the least a better error message is required. Has anyone opened a new issue?

@joaohenriquesouza80
Copy link

The workaround of @evgenytk works for me.
So many tks.

@KoushikKintali
Copy link

@pleerock awesome 👏 @UpdatedColumn decorator has been resolved. But I still got the issue about foreign key above, so can we have an option like update to resolve it?

@pleerock any update on this, we are still facing the issue?

@SebaBoler
Copy link

Yes, issue is actual.

@shahen94
Copy link

+1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests