mysql plugin auth error

[foolbear]

11:10:00.176 <0.18327.0> [info] Client(CocoaMQTT-Sheep-1374-83@101.231.197.86:61741): RECV CONNECT(Q0, R0, D0, ClientId=CocoaMQTT-Sheep-1374-83, ProtoName=MQTT, ProtoVsn=4, CleanSess=true, KeepAlive=90, Username=foolbear, Password=******, Will(Q1, R0, Topic=/will, Msg=dieout))
11:10:00.176 <0.13355.0> [error] gen_server <0.13355.0> terminated with reason: no case clause matching {error,closed} in mysql_protocol:fetch_response/5 line 307
11:10:00.177 <0.13355.0> [error] CRASH REPORT Process <0.13355.0> with 0 neighbours exited with reason: no case clause matching {error,closed} in mysql_protocol:fetch_response/5 line 307 in gen_server:terminate/7 line 826
11:10:00.177 <0.18327.0> [error] Client(CocoaMQTT-Sheep-1374-83@101.231.197.86:61741): Username 'foolbear' login failed for {{{case_clause,{error,closed}},[{mysql_protocol,fetch_response,5,[{file,"src/mysql_protocol.erl"},{line,307}]},{mysql,handle_call,3,[{file,"src/mysql.erl"},{line,556}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,629}]},{gen_server,handle_msg,5,[{file,"gen_server.erl"},{line,661}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]},{gen_server,call,[<0.13355.0>,{query,"select password from mqtt_user where username = 'foolbear' limit 1"},infinity]}}
11:10:00.178 <0.18327.0> [info] Client(CocoaMQTT-Sheep-1374-83@101.231.197.86:61741): SEND CONNACK(Q0, R0, D0, AckFlags=0, RetainCode=4)

[emqplus]

hi @foolbear, did you configure the mysql parameters in plugins/emqttd_plugin_mysql/etc/plugin.config?

[foolbear]

this is my config file:

[root@iZ25ziwfw0bZ foolbear]# less emqttd/plugins/emqttd_plugin_mysql/etc/plugin.config
[

  {emqttd_plugin_mysql, [

    {mysql_pool, [
        %% ecpool options
        {pool_size, 8},
        {auto_reconnect, 3},

        %% mysql options
        {host,     "localhost"},
        {port,     3306},
        {user,     "root"},
        {password, "VeryComplex99"},
        {database, "emqtt"},
        {encoding, utf8}
    ]},

    %% select password only
    {authquery, "select password from mqtt_user where username = '%u' limit 1"},

    %% hash algorithm: plain, md5, sha, sha256, pbkdf2?
    {password_hash, sha256},

    %% select password with salt
    %% {authquery, "select password, salt from mqtt_user where username = '%u'"},

    %% sha256 with salt prefix
    %% {password_hash, {salt, sha256}},

    %% sha256 with salt suffix
    %% {password_hash, {sha256, salt}},

    %% comment this query, the acl will be disabled
    {aclquery, "select allow, ipaddr, username, clientid, access, topic from mqtt_acl where ipaddr = '%a' or username = '%u' or username = '$all' or clientid = '%c'"},

    %% If no rules matched, return...
    {acl_nomatch, allow}

  ]}

].

[kenpeter]

select password from mqtt_user where username = '%u' limit 1, is the password in sha256?

[foolbear]

Yes, password field in mysql table mqtt_user is encoded by sha256.
Error occurs only occasionally, in most cases it works correctly.
For example, error almost occurs every-time when the first client connect to broker.
If auth mod changed from mysql to redis, error never happened.

[emqplus]

hi @huangdan, please check the issue.

[foolbear]

thanks, @emqplus @huangdan

[huangdan]

@foolbear fix the bug in the next version

[foolbear]

@huangdan thanks！

[emqplus]

@foolbear, @kenpeter, the mysql driver has been upgraded to 1.2.0, please pull the last master branch and build.

[foolbear]

@emqplus @kenpeter thanks！