Database transaction not working with Lucid relation

[sunxyw]

I am trying to create a user and his auth information.
This is my code:

await Database.transaction(async (trx) => {
  const uuid = uuidv4()
  let user = new User()
  user.uuid = uuid
  user.nickname = data.nickname
  user.roleId = 1
  user = user.useTransaction(trx)
  await user.save()

  const auth = new UserAuth()
  auth.fill({
    userUuid: '3x', // throw duplicate key error
    type: data.auth_type,
    identifier: data.identifier,
    credential: data.credential,
  })
  auth.useTransaction(trx)
  await auth.save()
})

My User model: https://pastebin.com/gDDLQHxM
My UserAuth model: https://pastebin.com/4BQgrTch

The error happened as expected, but the user record didn't rollback.

Is there any mistake in my code? Thanks for your help.

[thetutlage]

Just by looking at the code, it should work. Couple of questions?

• Which version of @adonisjs/lucid you are using?
• Can you turn on query debugging and share the console output?

[sunxyw]

I created a project to reproduce this issue.
Please check https://github.com/sunxyw/adonisjs-transaction

[thetutlage]

Just tried. Works like a charm for me. https://res.cloudinary.com/adonis-js/video/upload/v1595147217/transactions-check_l6gldv.mp4

[sunxyw]

However, It doesn't work on my computer, https://res.cloudinary.com/sunxywcloud/image/upload/v1595147706/fe8af96d-1b42-4096-b471-2fadf489f205_ge7daz.gif.
Maybe I should check my MySQL setting.

Anyway, thanks for your help.

[sunxyw]

Well.....I guess I find the point.
It should be a bug of HeidiSQL (Database Manage Tool).

The record is rolled back actually, but the overview in HeidiSQL doesn't change. It seems that it used Cache.
That's why this happened.

When I enter the detail page to confirm, everything works fine.

[thetutlage]

Glad, you found it :)

[sunxyw]

Btw, I find something interesting.

When inserted about 5-6 records, it will be rollback.

For example, now have 35 users records, after I send 5-6 requests to API(create user), It will be increased to 40.
After one more request, It will roll back to 35.

I have no idea why it will happen.

I know my English is quite poor, hope you can understand what I mean.

[popovicieduard]

I have the same issue, I cloned the repo provided by @sunxyw , and the users are insterted, while the user auths are not.

postman call
https://prnt.sc/toc9k3

insterted users:
https://prnt.sc/toc9sl

user auths:
https://prnt.sc/toc9ze

db log query:

"mysql" User (4.19 ms) insert into `users` (`created_at`, `nickname`, `role_id`, `updated_at`, `uuid`) values (?, ?, ?, ?, ?) [
  '2020-07-25 14:59:00',
  'test',
  1,
  '2020-07-25 14:59:00',
  '3efd924f-9d10-4646-9bdc-4639c9980f9e'
]
"mysql" UserAuth (31 ms) insert into `user_auths` (`created_at`, `credential`, `identifier`, `type`, `updated_at`, `user_uuid`) values (?, ?, ?, ?, ?, ?) [
  '2020-07-25 14:59:00',
  'test',
  'foo',
  'login',
  '2020-07-25 14:59:00',
  '3x'
]

[popovicieduard]

The expected behaviour would be that the transaction rolls back and the user is not insterted, right?, like @thetutlage showed: https://res.cloudinary.com/adonis-js/video/upload/v1595147217/transactions-check_l6gldv.mp4

[sunxyw]

Yes, both user_auths and users should be rollback.

[popovicieduard]

Right, but for some reason, users are insterted and user auths are not, users are insterted even if it throws duplicate error

[sunxyw]

oh, I got your point.
However, I have no idea about this issue.
Because my question is actually just a mistake because I forgot to check the detailed data page.

Therefore, I think it will be better to create a new thread to solve the issue, because this thread is already marked as resolved (And it seems cant be reopen)

[popovicieduard]

Found out the issue, Transactions in mysql only work with some storage type: https://stackoverflow.com/questions/42436932/transactions-not-working-for-my-mysql-db .