Issues with Mariadb and chilli service

[francysuri]

I'm running the script in my RPi3b The aim is to stream wlan0 internet (default) with wlan1 (Ralink 5370)

Actual behavior

I can see successfully the network, access the captive, but I have troubles with login and registration

Expected behavior

Normal interaction with database

Configuration parameters (if changed in the script)

{MYSQL_PASSWORD="pihotspot"

• Name of the hotspot that will be visible for users/customers HOTSPOT_NAME="SIGMA_Free"
• IP of the hotspot HOTSPOT_IP="192.168.10.1" WIFI_COUNTRY_CODE="IT"
• Use HTTPS to connect to web portal
• Set value to Y or N HOTSPOT_HTTPS="Y"
• Network where the hotspot is located HOTSPOT_NETWORK="192.168.10.0"
• Secret word for FreeRadius FREERADIUS_SECRETKEY=cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 32 | head -n 1
• WAN interface (the one with Internet - default 'eth0' or long name for Debian 9+) WAN_INTERFACE="wlan0"
• LAN interface (the one for the hotspot) LAN_INTERFACE="wlan1"
• Wifi driver LAN_WIFI_DRIVER="nl80211"
• Install Haserl (required if you want to use the default Coova Portal)
• Set value to Y or N HASERL_INSTALL="N"
• Install web frontend of Kupiki Hotspot
• Set value to Y or N INSTALL_KUPIKI_ADMIN=N

Logs

This is what happens when I run debug on chilli root@SIGMAFree:/home/pi# chilli --debug --fg chilli[1903]: (Re)processing options [/var/run/chilli.1903.cfg.bin] chilli[1904]: running chilli_opt on /var/run/chilli.1903.cfg.bin chilli[1903]: PID 1903 rereading binary file /var/run/chilli.1903.cfg.bin chilli[1903]: Loading modules chilli[1903]: PID 1903 reloaded binary options file chilli[1903]: CoovaChilli 1.4. Copyright 2002-2005 Mondru AB. Licensed under GPL. Copyright 2006-2012 David Bird (Coova Technologies). Licensed under GPL. See http://coova.github.io/ for details. chilli[1903]: chilli_main(7416): clock realtime sec 1576606034 nsec 502108578 chilli[1903]: chilli_main(7423): clock monotonic sec 2251 nsec 298294238 chilli[1903]: TX queue length set to 100 chilli[1903]: Running /etc/chilli/up.sh chilli[1903]: Hashlog 8 253 256 chilli[1903]: Net SNDBUF 163840 chilli[1903]: Net RCVBUF 163840 chilli[1903]: device wlan1 ifindex 4 chilli[1903]: lo address family: 17 (AF_PACKET) chilli[1903]: eth0 address family: 17 (AF_PACKET) chilli[1903]: wlan0 address family: 17 (AF_PACKET) chilli[1903]: wlan1 address family: 17 (AF_PACKET) chilli[1903]: docker0 address family: 17 (AF_PACKET) chilli[1903]: lo address family: 2 (AF_INET) chilli[1903]: wlan0 address family: 2 (AF_INET) chilli[1903]: tun0 address family: 2 (AF_INET) chilli[1903]: docker0 address family: 2 (AF_INET) chilli[1903]: tun1 address family: 2 (AF_INET) chilli[1903]: dhcp_hashinit(490): hash table size 64 (56) chilli[1903]: dhcp_sendGARP(338): GARP: Replying to broadcast chilli[1903]: dhcp_new(1191): dhcpif (wlan1) IPv6 address :: chilli[1903]: RADIUS client 0.0.0.0:3799 chilli[1903]: Address already in use: bind() failed! chilli[1903]: Failed to create radius

And this is what happens when I run mariadb check

root@SIGMAFree:/home/pi# service mariadb status ● mariadb.service - MariaDB 10.3.17 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2019-12-17 17:29:48 GMT; 38min ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 523 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 536 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 540 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=/usr/bin/galera_recovery; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code Process: 804 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS) Process: 806 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 660 (mysqld) Status: "Taking your SQL requests now..." Tasks: 32 (limit: 2200) Memory: 80.4M CGroup: /system.slice/mariadb.service └─660 /usr/sbin/mysqld

Dec 17 17:29:43 SIGMAFree systemd[1]: Starting MariaDB 10.3.17 database server... Dec 17 17:29:46 SIGMAFree mysqld[660]: 2019-12-17 17:29:46 0 [Note] /usr/sbin/mysqld (mysqld 10.3.17-MariaDB-0+deb10u1) starting as process 660 ... Dec 17 17:29:48 SIGMAFree systemd[1]: Started MariaDB 10.3.17 database server. Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: Looking for 'mysql' as: /usr/bin/mysql Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: Version check failed. Got the following error when calling the 'mysql' command line client Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[811]: FATAL ERROR: Upgrade failed Dec 17 17:29:48 SIGMAFree /etc/mysql/debian-start[1074]: Checking for insecure root accounts.

Your environment

• Hardware : RaspberryPi 3B
• Operating system: Raspbian Buster Lite

Additional comments

I have already fixed networking problems such as dhcpcd conflicts and so on

Thanks for the hard work, is a wonderful project

[pihomeserver]

RADIUS client 0.0.0.0:3799 It seems that something is already running on the port. Did you stop it before ?

[francysuri]

Before rebooting?

[pihomeserver]

no before starting chilli in debug mode

[francysuri]

did "service freeradius stop"

[pihomeserver]

Sure but it stops freeradius, not chilli Stop both and start them in debug mode

[francysuri]

same error on chilli --debug --fg

Running freeradius -X I get this

rlm_sql (sql): Opening additional connection (0), 1 of 32 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Couldn't connect to MySQL server radius@localhost:radius rlm_sql_mysql: MySQL error: Access denied for user 'radius'@'localhost' (using password: YES) rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Opening connection failed (0) rlm_sql (sql): Removing connection pool /etc/freeradius/3.0/mods-enabled/sql[20]: Instantiation failed for module "sql"

[pihomeserver]

user radius does not have access rights set. Did you get any error message while installing ? Else grant all with GRANT USAGE ON *.* TO 'radius'@'localhost'; with root user

[francysuri]

Yes I got

ERROR 1133 (28000) at line 1: Can't find any matching row in the user table

After Creating freeradius database

[pihomeserver]

Try the grant

[HoM3r17]

Hi, same issue here, here is the end of the install script :

::: Congratulation ! You now have your hotspot ready !
:::
::: - Wifi Hotspot available : Test_Hotspot
::: - For the user management, please connect to http://192.168.1.23/ or http://Test_Hotspot.local/
:::   (login : administrator / password : radius)
root@hotspot:~/Kupiki-Hotspot-Script# ::: Creating freeradius database
-bash: :::: command not found
root@hotspot:~/Kupiki-Hotspot-Script# ERROR 1133 (28000) at line 1: Can't find any matching row in the user table
-bash: syntax error near unexpected token `('
root@hotspot:~/Kupiki-Hotspot-Script# ERROR 1396 (HY000) at line 1: Operation DROP USER failed for 'radius'@'localhost'
-bash: syntax error near unexpected token `('
root@hotspot:~/Kupiki-Hotspot-Script# root@hotspot:~/Kupiki-Hotspot-Script# ERROR 1133 (28000) at line 1: Can't find any matching row in the user table
-bash: syntax error near unexpected token `('

Here is my script parameters :

# Name of the log file
LOGNAME="kupiki_hotspot.log"
# Path where the logfile will be stored
# be sure to add a / at the end of the path
LOGPATH="/var/log/"
# Password for user root (MySql/MariaDB not system)
MYSQL_PASSWORD="#mypasswd#"
# Name of the hotspot that will be visible for users/customers
HOTSPOT_NAME="Test_Hotspot"
# IP of the hotspot
HOTSPOT_IP="192.168.10.1"
# Wi-fi code country. Use above link to find yours
# https://www.cisco.com/c/en/us/td/docs/wireless/wcs/3-2/configuration/guide/wcscfg32/wcscod.html
WIFI_COUNTRY_CODE="FR"
# Use HTTPS to connect to web portal
# Set value to Y or N
HOTSPOT_HTTPS="Y"
# Network where the hotspot is located
HOTSPOT_NETWORK="192.168.10.0"
# Secret word for FreeRadius
FREERADIUS_SECRETKEY=`cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 32 | head -n 1`
# WAN interface (the one with Internet - default 'eth0' or long name for Debian 9+)
WAN_INTERFACE=`ip link show | grep '^[1-9]' | awk -F ':' '{print $2}' | awk '{$1=$1};1' | grep '^e'`
# LAN interface (the one for the hotspot)
LAN_INTERFACE="wlan0"
# Wifi driver
LAN_WIFI_DRIVER="nl80211"
# Install Haserl (required if you want to use the default Coova Portal)
# Set value to Y or N
HASERL_INSTALL="Y"
# Password used for the generation of the certificate
CERT_PASSWORD="#mypasswd#"
# Number of days to certify the certificate for (default 2 years)
CERT_DAYS="9000"
# Make Avahi optional
# Set value to Y or N
AVAHI_INSTALL="Y"
# Install Daloradius Portal (compatible with FR2 only in theory)
# Set value to Y or N
DALORADIUS_INSTALL="Y"
# Enable/Disable Bluetooth
# Set value to Y or N
BLUETOOTH_ENABLED="N"
# Enable/Disable fail2ban to protect server from unwanted access
# Set value to Y or N
FAIL2BAN_ENABLED="N"
# Enable/Disable Netflow logs to log all traffic requests. Must be crossed checked with assigned IP in the radius ta$
# Set value to Y or N
NETFLOW_ENABLED="Y"
# Define how long Netflow logs will be stored
# Sets the max life time for files generated for Netflow monitoring. The supplied maxlife_time accepts values such a$
# Accepted time scales are w (weeks) d (days) H (hours).
# A value of 0 disables the max lifetime limit. If no scale is given, H (hours) are assumed.
# By default data are stored 365 days (value set to 365d)
NETFLOW_LOGS_DAYS="365d"
# Enable/Disable MAC authentication
# Set value to Y or N
MAC_AUTHENTICATION_ENABLED="N"
# Password for MAC authentication. Could/Should be changed within the web administration interface
MAC_AUTHENTICATION_PASSWORD="123456"
# Install web frontend of Kupiki Hotspot
# Set value to Y or N
INSTALL_KUPIKI_ADMIN=N
# Install Cron job for the hotspot updater. Will be executed every sunday at 8am (system time)
# Set value to Y or N
ADD_CRON_UPDATER=Y
# Install additional counters
# Set value to Y or N
KUPIKI_SQL_COUNTERS=Y
# Allow users to register in the Portal
# Set value to Y or N
KUPIKI_ALLOW_REGISTER=Y

Captive portal seems stuck on a blank page, and i can't access the admin page, i've tried using different methods, all of them are returning different errors :

• http://<lan_ip_of_the_pi> 502 Bad Gateway nginx/1.14.2

• http://<lan_ip_of_the_pi>/daloradius 404 Not Found nginx/1.14.2

• http://test_hotspot.local/daloradius DNS_PROBE_FINISHED_NXDOMAIN

[pihomeserver]

First errors seem to be a paste of text in the command line What is the status of following services : freeradius, chilli and mysql ? Up and running ? If yes stop freeradius and chilli and start each of them in a dedicted session and in debug mode. You should see the errors