Stored Procedure error - no function clause matching in Mariaex.Protocol.dispatch/2

[burmudar]

I get the following error when making a stored procedure call CALL boabab.sp_get_invoice_customer_list(2016,1) ** (FunctionClauseError) no function clause matching in Mariaex.Protocol.dispatch/2 (mariaex) lib/mariaex/protocol.ex:100: Mariaex.Protocol.dispatch({:packet, 43, 6, {:ok_resp, 0, 0, 167, 2, 0, <<0, 0, 0, 32, 50, 52, 47, 55, 32, 83, 101, 99, 117, 114, 105, 116, 121, 32, 83, 101, 114, 118, 105, 99, 101, 115, 32, 40, 80, 84, 89, 41, 32, 76, 116, 100>>}, <<0, 0, 167, 2, 0, 0, 0, 0, 0, 0, 32, 50, 52, 47, 55, 32, 83, 101, 99, 117, 114, 105, 116, 121, 32, 83, 101, 114, 118, 105, 99, 101, 115, 32, 40, 80, 84, 89, 41, 32, 76, 116, 100>>}, %{backend_key: nil, cache: {100, 155667}, catch_eof: true, handshake: %{salt: {"!ky?m+(*", "7vmA;E*??\\50"}, seqnum: 0}, keepalive: {60000, 5000}, keepalive_send: nil, last_answer: {1453, 901962, 753263}, opts: [sock_mod: Mariaex.Connection.Tcp, username: "some_user", password: "some_pass", database: "", hostname: "192.168.0.1", port: 3306], parameter_types: [{"customer_id", 8}, {"company_name", 253}], parameters: [], protocol57: false, queue: {[], []}, results: [], rows: [], seqnum: 0, sock: {Mariaex.Connection.Tcp, #Port<0.7082>}, sock_mod: Mariaex.Connection.Tcp, state: :running, state_data: {0, 0}, statement: "CALL baobab.sp_get_invoice_customer_list(2016,1)", statement_id: nil, substate: nil, tail: "", types: [{"company_name", 253}, {"customer_id", 8}]}) (mariaex) lib/mariaex/connection.ex:286: Mariaex.Connection.process/2 (mariaex) lib/mariaex/connection.ex:244: Mariaex.Connection.handle_info/2 (connection) lib/connection.ex:812: Connection.handle_async/3 (stdlib) gen_server.erl:615: :gen_server.try_dispatch/4 (stdlib) gen_server.erl:681: :gen_server.handle_msg/5 (stdlib) proc_lib.erl:240: :proc_lib.init_p_do_apply/3 Last message: {:tcp, #Port<0.7082>, <<1, 0, 0, 1, 2, 64, 0, 0, 2, 3, 100, 101, 102, 6, 98, 97, 111, 98, 97, 98, 2, 116, 99, 12, 116, 109, 112, 95, 99, 117, 115, 116, 111, 109, 101, 114, 11, 99, 117, 115, 116, 111, 109, 101, 114, 95, 105, 100, ...>>} State: %{backend_key: nil, cache: {100, 155667}, catch_eof: false, handshake: %{salt: {"!ky?m+(*", "7vmA;E*??\\50"}, seqnum: 0}, keepalive: {60000, 5000}, keepalive_send: nil, last_answer: nil, opts: [sock_mod: Mariaex.Connection.Tcp, username: "some_user", password: "some_pass", database: "", hostname: "192.168.0.1", port: 3306], parameter_types: [], parameters: [], protocol57: false, queue: {[{{:query, "CALL baobab.sp_get_invoice_customer_list(2016,1)", [], []}, {#PID<0.338.0>, #Reference<0.0.3.293>}}], []}, results: [], rows: [], seqnum: 0, sock: {Mariaex.Connection.Tcp, #Port<0.7082>}, sock_mod: Mariaex.Connection.Tcp, state: :execute_send, state_data: {0, 0}, statement: "CALL baobab.sp_get_invoice_customer_list(2016,1)", statement_id: 1, substate: :column_count, tail: "", types: :types_removed} The error only occurs when the stored proc returns a result.

MySQL version: 5.6.21-70.1 Mariaex version: 0.6.2 Elixir verison: 1.2.1

[liveforeverx]

Hi! Can you provide minimal reproducible example, which stored procedure do you use? Would be nice to build some tests with stored procedures, as I'm doing refactoring for upcoming ecto and may broke some functionality related to stored procedures. Would be really nice to have some tests. Thanks in before!

[burmudar]

Hey! Sure thing. I'll start tomorrow. I have been trying to trace through the code to see where the issue occurs. I'll be happy to share my findings, don't know if it will be constructive.

I am keen to contribute so any pointers would be helpful :)

[burmudar]

See below for the stored procedure which recreates the issue.

Interestingly enough, the same error occurs if you return multiple result sets from the stored procedure. As far as I can tell in the test stored procedure nothing except the last select statement returns results.

Note: I tried as far as possible recreating what our internal procedure is doing

USE test;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_test //
CREATE PROCEDURE sp_test
(
)
BEGIN

    CREATE TEMPORARY TABLE fruit
    (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
        ,name VARCHAR(255)
    );

    INSERT INTO fruit
    (name)
    VALUES
    ('Apple'),
    ('Orange'),
    ('Pearê'),
    ('Watermelon'),
    ('Grapes'),
    ('Avocado'),
    ('Banana'),
    ('Peach'),
    ('Lemon'),
    ('Prune'),
    ('Tangarine'),
    ('Cucumber'),
    ('Pineapple'),
    ('Strawberry'),
    ('Gooseberry'),
    ('Tomato'),
    ('Grapefruit'),
    ('Pomogranate'),
    ('Guava'),
    ('Kiwifruit'),
    ('Cranberry'),
    ('Rasberry'),
    ('Blackcurrent'),
    ('Blackberry'),
    ('Fig'),
    ('BlueBerry'),
    ('Coconut'),
    ('Mango'),
    ('Plum'),
    ('Passion Fruit'),
    ('Papayas'),
    ('Apricot'),
    ('Cherry'),
    ('Pawpaw');

    CREATE TEMPORARY TABLE fruit2
    (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
        ,name VARCHAR(255)
    );

    INSERT INTO fruit2(name)
    SELECT name from fruit;

    SELECT * from fruit2;

    DROP TEMPORARY TABLE fruit;
    DROP TEMPORARY TABLE fruit2;

END //

DELIMITER ;