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