i am working on mqtt broker 1.4.1 with authentication plugin, backend is 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.
in mysql, i use INSERT INTO users (username, pw) VALUES ('Leo', 'PBKDF2$sha256$901$...$...');
and INSERT INTO acls (username, topic, rw) VALUES ('Leo', 'Leo/#', 2);
commands to create test user and its topic control.
when i run sudo mosquitto_sub -h localhost -p 1883 -u Leo -P Leo -t Leo/# i always get below error:
Connection Refused: bad user name or password.
any hints of this error? thanks,Leo
----here is Mosquitto configuration file
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
and here is user and table in 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 |
+----+----------+----------------+----+
Check the single quotes around your %s. Some of them are "fancy", like ‘%s’ and some of them are plain (as they should be) like '%s'. The difference is very obvious in the stackoverflow editor. Make them all plain single quotes
'.