mysql - mqtt 代理 1.4.1 带有 auth 插件，后端 mysql，无法 sub/pub，用户名或密码错误

Question

我正在使用身份验证插件开发mqtt代理1.4.1，后端是mysql。

 /etc/mosquitto $ sudo /usr/local/sbin/mosquitto -c mosquitto.conf

1435208131: mosquitto version 1.4.1 (build date 2015-05-07 17:19:21+0800) starting

1435208131: Config loaded from mosquitto.conf.

1435208131: |-- *** auth-plug: startup

1435208131: |-- ** Configured order: mysql

1435208131: |-- }}}} MYSQL

1435208131: Opening ipv4 listen socket on port 1883.

1435208131: Opening ipv6 listen socket on port 1883.

1435208131: Warning: Address family not supported by protocol

1435208142: New connection from 127.0.0.1 on port 1883.

1435208142: |-- mosquitto_auth_unpwd_check(Leo)

1435208142: |-- ** checking backend mysql

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '▒▒Leo’' at line 1

1435208142: |-- getuser(Leo) AUTHENTICATED=0 by none

1435208142: Sending CONNACK to 127.0.0.1 (0, 4)

1435208142: Socket error on client , disconnecting.

在mysql中，我使用INSERT INTO users (username, pw) VALUES ('Leo', 'PBKDF2$sha256$901$...$...');和INSERT INTO acls (username, topic, rw) VALUES ('Leo', 'Leo/#', 2);用于创建测试用户及其主题控件的命令。

当我运行sudo mosquitto_sub -h localhost -p 1883 -u Leo -P Leo -t Leo/#时我总是收到以下错误:

Connection Refused: bad user name or password.

此错误有任何提示吗？谢谢，利奥

----这是Mosquitto配置文件

sudo nano /etc/mosquitto/mosquitto.conf

autosave_interval 1800

persistence true

persistence_file m2.db

persistence_location /var/lib/mosquitto/

connection_messages true

log_timestamp true

log_dest stderr

log_type error

log_type warning

log_type debug

log_type notice

log_type information

log_type all

auth_plugin /etc/mosquitto/auth-plug.so

auth_opt_backends mysql

auth_opt_host localhost

auth_opt_port 3306

auth_opt_dbname sql_iocaremqtt

auth_opt_user pi

auth_opt_pass rpi

auth_opt_userquery SELECT pw FROM users WHERE username = ‘%s’

auth_opt_superquery SELECT COUNT(*) FROM users WHERE username = ‘%s’ AND super = 1

auth_opt_aclquery SELECT topic FROM acls WHERE (username = '%s') AND (rw >= %d)

port 1883

protocol mqtt

这是 mysql 中的用户和表:

mysql> select * from users;

+----+----------+----------------------------------------------------+-------+

| id | username | pw | super |

+----+----------+----------------------------------------------------+-------+

| 1 | Leo | PBKDF2$sha256$901$...$... | 0 |

+----+----------+----------------------------------------------------+-------+

mysql> select * from acls;

+----+----------+----------------+----+

| id | username | topic | rw |

+----+----------+----------------+----+

| 1 | Leo | Leo/# | 2 |

+----+----------+----------------+----+

Answer 1

检查 %s 周围的单引号。其中一些是“花哨的”，例如“％s”，而另一些则很简单(因为它们应该如此)，例如“％s”。在 stackoverflow 编辑器中差异非常明显。将它们全部设为纯单引号 '.