MariaDB driver should not use prepared statement protocol when oneshot is true

[joseferben]

First of all, thanks for this great project!

I am running into an issue while running migrations for following query on MariaDB 10.1.41:

let create_uuid_of = connection => {
  module Connection = (val connection: Caqti_lwt.CONNECTION);
  let request =
    Caqti_request.exec(
      ~oneshot=true,
      Caqti_type.unit,
      "
CREATE OR REPLACE
  FUNCTION uuid_of(uuid BINARY(16))
  RETURNS VARCHAR(36)
  RETURN LOWER(CONCAT(
  SUBSTR(HEX(uuid), 1, 8), '-',
  SUBSTR(HEX(uuid), 9, 4), '-',
  SUBSTR(HEX(uuid), 13, 4), '-',
  SUBSTR(HEX(uuid), 17, 4), '-',
  SUBSTR(HEX(uuid), 21)
));
",
    );
  Connection.exec(request, ());
};

Error 1295, This command is not supported in the prepared statement protocol yet

I tried to track down the issue, but I am not sure what the underlying MariaDB library is doing.

In caqti_driver_mariadb.ml:

...
      (match Caqti_request.query_id req with
       | None ->
          let templ = Caqti_request.query req driver_info in
          let query = linear_query_string templ in
          Mdb.prepare db query >>=
...

Even though the query id is None (due to oneshot being true), Mdb.prepare is called. I assume this is using the prepared statement protocol?

[paurkedal]

I didn't realise there were such restrictions. Yes, None means oneshot here, which is implemented in the Caqti driver by creating a prepared statement which is closed after usage. The reason is that the mariadb client library only supports prepared queries. According to the API docs for the Mariadb module:

These bindings are restricted to MariaDB's prepared statement APIs, as they provide support for typed query parameters and results.

which seems reasonable, though in light of the limitations of the prepared statements, it would be good to find a solution for non-prepared statements.

CC @andrenth

[andrenth]

Hello

I don't currently have the resources to work on supporting non-prepared statements, but I would gladly accept a PR implementing them.

[paurkedal]

I haven't had time either yet, but I hope to make a PR soon, though probably not the coming two weeks.

[apeschar]

MariaDB supports all syntax in prepared statements from version 10.6.2 (docs), making this issue irrelevant.

[paurkedal]

It may be too early to expect people to upgrade their database servers. At least Ubuntu 20.04 LTS and RHEL 7 and 8, which are still under support, distribute older versions.