elixir-ecto/myxql

Using loops in MySQL stored procedures

ibooking-rolf opened this issue · 2 comments

ibooking-rolf commented

I am having issues using stored-procedures containing loops in MySQL.

When trying to call the following procedure:

DROP PROCEDURE IF EXISTS test_proc;

DELIMITER $$

CREATE PROCEDURE test_proc()
BEGIN
    DECLARE loop_var_exit BOOLEAN DEFAULT FALSE;

    DECLARE temp_test INT;

    DECLARE test_cursor CURSOR FOR SELECT 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_var_exit = TRUE;

    OPEN test_cursor;

    read_loop: LOOP
        FETCH test_cursor INTO temp_test;

        IF loop_var_exit THEN
            LEAVE read_loop;
        END IF;
    END LOOP;

    CLOSE test_cursor;

    SELECT 1 AS result;
END$$

DELIMITER ;

using:

Ecto.Adapters.SQL.query!(MyRepo.Repo,  "CALL test_proc()", [])

I get the following error:

iex(1)> Ecto.Adapters.SQL.query!(MyRepo.Repo, "CALL test_proc()", [])

18:45:09.326 [info]  QUERY ERROR db=24.3ms queue=1.0ms idle=802.3ms
CALL test_proc() []
** (MatchError) no match of right hand side value: <<10, 0, 0, 4, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 5, 254, 0, 0, 202, 0>>
    (myxql 0.4.5) lib/myxql/protocol.ex:506: MyXQL.Protocol.decode_resultset/4
    (myxql 0.4.5) lib/myxql/client.ex:204: MyXQL.Client.recv_packets/6
    (myxql 0.4.5) lib/myxql/connection.ex:103: MyXQL.Connection.handle_execute/4
    (db_connection 2.4.1) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
    (db_connection 2.4.1) lib/db_connection.ex:1333: DBConnection.run_execute/5
    (db_connection 2.4.1) lib/db_connection.ex:1428: DBConnection.run/6
    (db_connection 2.4.1) lib/db_connection.ex:593: DBConnection.parsed_prepare_execute/5
    (db_connection 2.4.1) lib/db_connection.ex:585: DBConnection.prepare_execute/4

18:45:09.321 [error] GenServer #PID<0.357.0> terminating
** (DBConnection.ConnectionError) client #PID<0.392.0> stopped: ** (MatchError) no match of right hand side value: <<10, 0, 0, 4, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 5, 254, 0, 0, 202, 0>>
    (myxql 0.4.5) lib/myxql/protocol.ex:506: MyXQL.Protocol.decode_resultset/4
    (myxql 0.4.5) lib/myxql/client.ex:204: MyXQL.Client.recv_packets/6
    (myxql 0.4.5) lib/myxql/connection.ex:103: MyXQL.Connection.handle_execute/4
    (db_connection 2.4.1) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
    (db_connection 2.4.1) lib/db_connection.ex:1333: DBConnection.run_execute/5
    (db_connection 2.4.1) lib/db_connection.ex:1428: DBConnection.run/6
    (db_connection 2.4.1) lib/db_connection.ex:593: DBConnection.parsed_prepare_execute/5
    (db_connection 2.4.1) lib/db_connection.ex:585: DBConnection.prepare_execute/4

    (db_connection 2.4.1) lib/db_connection/connection.ex:198: DBConnection.Connection.handle_cast/2
    (connection 1.1.0) lib/connection.ex:810: Connection.handle_async/3
    (stdlib 3.13.2) gen_server.erl:680: :gen_server.try_dispatch/4
    (stdlib 3.13.2) gen_server.erl:756: :gen_server.handle_msg/6
    (stdlib 3.13.2) proc_lib.erl:236: :proc_lib.wake_up/3
Last message: {:"$gen_cast", {:stop, #Reference<0.3573120068.4004904964.201146>, %DBConnection.ConnectionError{message: "client #PID<0.392.0> stopped: ** (MatchError) no match of right hand side value: <<10, 0, 0, 4, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 5, 254, 0, 0, 202, 0>>\n    (myxql 0.4.5) lib/myxql/protocol.ex:506: MyXQL.Protocol.decode_resultset/4\n    (myxql 0.4.5) lib/myxql/client.ex:204: MyXQL.Client.recv_packets/6\n    (myxql 0.4.5) lib/myxql/connection.ex:103: MyXQL.Connection.handle_execute/4\n    (db_connection 2.4.1) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4\n    (db_connection 2.4.1) lib/db_connection.ex:1333: DBConnection.run_execute/5\n    (db_connection 2.4.1) lib/db_connection.ex:1428: DBConnection.run/6\n    (db_connection 2.4.1) lib/db_connection.ex:593: DBConnection.parsed_prepare_execute/5\n    (db_connection 2.4.1) lib/db_connection.ex:585: DBConnection.prepare_execute/4\n", reason: :error, severity: :error}, %MyXQL.Connection{client: %MyXQL.Client{connection_id: 892, sock: {:gen_tcp, #Port<0.23>}}, cursors: %{}, disconnect_on_error_codes: [:ER_MAX_PREPARED_STMT_COUNT_REACHED], last_ref: #Reference<0.3573120068.4004773890.202638>, ping_timeout: 15000, prepare: :unnamed, queries: #Reference<0.3573120068.4004904964.201132>, transaction_status: :idle}}}
State: {MyXQL.Connection, %MyXQL.Connection{client: %MyXQL.Client{connection_id: 892, sock: {:gen_tcp, #Port<0.23>}}, cursors: %{}, disconnect_on_error_codes: [:ER_MAX_PREPARED_STMT_COUNT_REACHED], last_ref: #Reference<0.3573120068.4004773890.202638>, ping_timeout: 15000, prepare: :unnamed, queries: #Reference<0.3573120068.4004904964.201132>, transaction_status: :idle}}

If I remove the loop from the procedure, everything works as expected.

Info:
MySQL server: Community Server 5.7.31
myxql: v0.4.5
ecto: v3.7.1
elixir: v1.11.2-otp-23
erlang: v23.1.5

The Repo is also configured with prepare: :unnamed.

I have tried updating myxql to version 0.5.1 and downgrading to version 0.4.0 but to no avail. Is this a known error, or am I doing something wrong? Thank you!

josevalim commented

I believe it is returning a multiple results set and we don’t parse those. We would generate to implement it according to the binary protocol. A PR is welcome!

wojtekmach commented

Closing in favour of PR.

  • 🎉1