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

Multi_insert returns more results than expected in some specific conditions #384

Open
renatolond opened this issue Sep 17, 2020 · 5 comments
Labels

Comments

@renatolond
Copy link

Describe the bug

When using mysql2 as the underlying driver, dealing with a table that has a multi-column primary key, multi_insert tries to get back the primary key from the inserts. However, since this is a multi-column PK, LAST_INSERT_ID() has 0, so relation.multi_insert will give back an array of 0.
multi_insert will in turn, try to query the relation with the primary keys it got back. It uses relation.primary_key for this, which will return as far as I can understand the first field of the PK. If the PK has a varchar as the first field, this will result in a query such as SELECT * FROM relation where varchar_field in (0, 0, 0...), which in mysql will return every field in the table.

To Reproduce

Create a table which contain more than one column as primary key, such as:

CREATE TABLE `my_table` (
  `my_field1` varchar(255) NOT NULL,
  `my_field2` varchar(255) NOT NULL,
  PRIMARY KEY (`my_field1`, `my_field2`)
)

Insert random data in the created able.

Try to insert more than one value in this table using something like so:

new_values = [{my_field1: "abc", my_field2: "cde"}, {my_field1: "gde", my_field2: "zyx"}]
create_cmd = my_table.command(:create, result: :many)
d = create_cmd.call(new_values)

d will contain all rows in the table, instead of only the two inserted ones.

Expected behavior

Only the two values are returned (or optionally, no values at all)

Your environment

  • Affects my production application: No, we rolled back our change and we're going straight to the db.
  • Ruby version: 2.6
  • OS: Linux
  • Rom-sql 3.1.0
  • Rom-core 5.1.2
@renatolond renatolond added the bug label Sep 17, 2020
@solnic
Copy link
Member

solnic commented Sep 18, 2020

I reckon this is a known problem with composite primary keys. In rom 6.0 you'll have "void commands" that don't return anything and you'll be able to use whatever method you want to retrieve inserted data.

@flash-gordon maybe we should raise an error in case of a command that return results automatically + composite PKs?

@flash-gordon
Copy link
Member

I guess yes, raising an explicit exception is better

@solnic
Copy link
Member

solnic commented Sep 18, 2020

@flash-gordon cool, it's something to do in 4.0.0 I suppose

@renatolond
Copy link
Author

Is there a work around in the meantime, should I do a custom command that calls sequel directly?

@solnic
Copy link
Member

solnic commented Sep 18, 2020

@renatolond yes you could use a custom command and implement your own #execute

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

No branches or pull requests

3 participants