update does not follow the same semantics of jdbc-client in regards to generated ids

[pmlopes]

When an insert into a table with generated ids, auto_increment on mysql/mariadb the generated id's are not returned automatically like it happens on jdbc-client.

This means that the async driver is not a drop-in replacement for jdbc-client.

[cescoffier]

Is it something fixable in the MySQL client or it's an issue in the scala async driver ? (And another reason to get rid of it)

On 28 sept. 2015, at 15:28, Paulo Lopes notifications@github.com wrote:

When an insert into a table with generated ids, auto_increment on mysql/mariadb the generated id's are not returned automatically like it happens on jdbc-client.

This means that the async driver is not a drop-in replacement for jdbc-client.

—
Reply to this email directly or view it on GitHub.

[pmlopes]

i think it is something specific to the driver since i could not quickly find a way to specify that it should return ids, like we do on JDBC.

True that this is vendor specific like on postgres one would do: insert into ... returning id but the returning is added by the driver usually...

From what i can see in the driver itself it does not do this...

[pmlopes]

To fix it on our side we would need to parse the SQL and add the returning action. I don't know if this is a good idea since it can become tricky to parse SQL for example once someone uses common table expressions for example:

with inserted as (
  insert into things(name)
  values('Rob') returning *
)
insert into thing_logs(thing_id,entry)
select inserted.id, 'New thing created: ' || inserted.name from inserted;

should we return ids, which ones, how can you parse this correctly? and trust me this CTE is quite simple :)

[pmlopes]

see: https://groups.google.com/d/topic/vertx/ZFUSx3_HNsw/discussion

[Narigo]

After investigating this issue a bit further, I need to look more closely at the MySQL driver and why it doesn't yield the keys yet. They should be in the binary protocol of MySQL and I could see that Mauricio's driver does something with it. Need to check that further.

In PostgreSQL, the JDBC driver itself adds a RETURNING * to the SQL statement by changing the command String manually. We can do something similar.

[PhilLehmann]

So you say that for Postgres, this should work already?

I am using .updateWithParams as there seems to be no insert method.

It returns {"updated": 1, "keys":[]}. The PK column is of type UUid, may that be the reason?

[remi128]

I did open an issue at mauricio and got some replies from them. It seems, that the async driver supports it for both, postgres and mysql, and they described both. Unfortunately i'm an idiot regarding Scala integration.
mauricio/postgresql-async#155 (comment)

[Narigo]

@remi128 as I understand it, this is not correct: The MySQL driver supports it, the PostgreSQL doesn't. In JDBC, the PostgreSQL driver adds a RETURNING * after every SELECT query it finds. That means, that the JDBC driver takes your statement, analyses it and modifies it - which isn't even always fine to do under some circumstances. I'll try to get the example from my coworker, where he had to append -- to a query in order to get rid of it when using JDBC...

So AFAIK in Mauricios PostgreSQL driver it isn't supported and the MySQL driver supports it. In our Vert.x wrapper, we are currently not returning the last inserted id, to be consistent between both.

[cescoffier]

Postpone the issue to the next release.