ASTPP SQL schema is designed for MySQL v8, not MariaDB. They are not 100% compatible. I was using MariaDB once upon a time but ran into compatibility issues. I don’t remember what they were. Instead of dealing with that by maintaining my own *.sql files I just switched to MySQL 8 so I am was more in line with the project.
My understanding it MariaDB is supposed to be a “drop in replacement” for MySQL though are some compatibility issues.
MariaDB is intended to maintain high compatibility with MySQL, with library binary parity and exact matching with MySQL APIs and commands, allowing it in many cases to function as drop-in replacement for MySQL.
Freeswitch cannot access MySQL without mod_mariadb loaded. Anything you think it was doing without that module loaded was most likely from sqlite, which it uses by default if it cannot access any other database, or you are confusing what ASTPP/ODBC is doing with what Freeswitch is doing.
Here is one last tip that might be related since you are using MariaDB, although that will probably not work properly because it is not 100% compatible with MySQL 8. Debian 9/10/11 MariaDB ODBC 3 connector was broken at one time. I don’t know if it still is but the fix to get around that was to use the ODBC 2 connector.
cd /usr/src
wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-2.0.19/\
mariadb-connector-odbc-2.0.19-ga-debian-x86_64.tar.gz
tar -zxvf mariadb-connector-odbc-2.0.19*.tar.gz
cp lib/libmaodbc.so /usr/lib/x86_64-linux-gnu/odbc/
Then you need to edit /etc/odbcinst.ini and set Driver = /usr/lib/x86_64-linux-gnu/odbc/libmaodbc.so
Or just use MySQL v8 repo which is what you should be doing in the first place.
Thanks again for the tips.
I just want to see if I can get MariaDB functioning with this instead of having to depend on MySQL repo.
If I can’t get it to function, I will certainly go back to MySQL.
With that, I have done many changes to their SQL script for setting up, and installing the demo data into the database.
After doing a side-by-side comparison inside of both MySQL and MariaDB, and what ASTPP displays in the web interface, I see they are identical and no errors are presented.
So now, the only issue is the SIP profiles not loading into FreeSwitch. With your advice here, I will do a new fresh install with the changes you advised me to make.
ASTPP uses the default MySQL 8 collation utf8mb4_0900_ai_ci on several tables (cdrs, reseller_cdrs, pricelists, trunks, packages_view, view_did, view_invoices) which does not exist on MariaDB. At a minimum, you would need to change that collation on those tables to something like utf8mb4_general_ci if you were using MariaDB.
I made all those changes already, As my own script based off theirs was reporting errors about what you mention. At this point, no errors.
I have changed it to mod_mariadb and the SIP profile is not loading.
@KNERD use MySQL in place of MariaDB. We never checked ASTPP with MariaDB, so we cannot help you with MariaDB.
Without more specifics about what you are doing and what your skill/knowledge level is, I can only give you generic advice, so here goes.
Freeswitch would have defaulted to use sqlite if you didn’t have mod_mariadb loaded. You could disable mod_mariadb and see if the files in /var/lib/freeswitch/db are being updated. Just delete all of them and restart Freeswitch. They should get recreated.
I haven’t tried compiling Freeswitch from source in a long time but I seem to remember it saving files in different directories than if installing from deb packages, so I needed to make sure to tell it to save those files in the correct places, or just install Freeswitch from the DEB packages instead.
It could also be a permissions problem. This is my manual procedure to fix permissions which will also give you some idea what should be where.
I run this any time there are any changes/moves/adds/upgrades or when experiencing problems.
# Ownership www-data
chown -R www-data. /etc/freeswitch /var/lib/freeswitch \
/var/log/freeswitch /usr/share/freeswitch \
/var/log/astpp /var/log/nginx /opt/ASTPP
# Directory permissions to 755 (u=rwx,g=rx,o='rx')
find /etc/freeswitch -type d -exec chmod 755 {} \;
find /var/lib/freeswitch -type d -exec chmod 755 {} \;
find /var/log/freeswitch -type d -exec chmod 755 {} \;
find /usr/share/freeswitch -type d -exec chmod 755 {} \;
find /opt/ASTPP -type d -exec chmod 755 {} \;
find /var/log/astpp -type d -exec chmod 755 {} \;
find /var/lib/astpp -type d -exec chmod 755 {} \;
# File permissions to 777(u=rwx,g=rwx,o=rwx)
find /var/log/astpp -type f -exec chmod 777 {} \;
# File permissions to 644 (u=rw,g=r,o=r)
find /etc/freeswitch -type f -exec chmod 644 {} \;
find /var/lib/freeswitch -type f -exec chmod 644 {} \;
find /var/log/freeswitch -type f -exec chmod 644 {} \;
find /usr/share/freeswitch -type f -exec chmod 644 {} \;
find /opt/ASTPP -type f -exec chmod 644 {} \;
find /var/lib/astpp -type f -exec chmod 644 {} \;
Lastly, as stated in a previous reply, it could be your ODBC connector. I know the MariaDB ODBC 3 connector for Debian was flaky at one time. I ended up using the ODBC 2 connector instead.
Here are some basic checks to see of ODBC is working.
# Test odbc driver
odbcinst -s -q
# Test odbc connection
isql -v astpp astppuser ${MYSQL_USER_PASSWORD}
Although if that was not working then nothing would work, not just Freeswitch.
You keep saying the only thing that is not loading is sip_profiles. As far as I know that is basically everything Freeswitch loads, so still kind of sounds like a DB connection problem to me but I could be wrong.
1 Like
If there was an ODBC problem , then I would think the site would even load as you need database access to even log in. All the data is there in the web interface from the samples put in from their script after login.
Since the difference I am doing is compiling, I wonder if that could be an issue.
I decided to continue working on this for my own benefit (and a few others) since they are not in interesting in accepting pull requests.
I started to look again at this issue. Since I was not sure why the profiles were loading, I decided to try looking over the MariaDB issue again.
At first glance after an install, it seemed using MariaDB was functioning fine as all the initial demo data placed into the database. However, when I try to make a gateway then save, some of the fields are blank.
I ran your suggested tests above and failed on the second one:
# Test odbc connection
isql -v astpp astppuser ${MYSQL_USER_PASSWORD}
It failed with
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so' : file not found
[ISQL]ERROR: Could not SQLConnect
Since it was looking for libmyodbc8a.so, I see it is being included with the ASTPP GitHub repo, and tried it out then got this
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
I tried again with the gateway and got another failure.
Looking at the gateway table:
The second one is a test gateway I set up. For some reason, the information it needs seems to not be
getting placed into the field. a ‘0’ is getting placed when like the following should be getting inserted:
{"username":"USERNAME","password":"PASSWORD","proxy":"sip.provider.com","register":"false","caller-id-in-from":"true"}
Any more clues?
UPDATE: I went ahead and installed MySQL instead of MariaDB as a test. I am getting the same exact results. I wonder if PHP7.4 has something to do with this.
I am (was) running PHP 7.4 no problem. As long as you did the change required for PHP 7.4. The one I submitted a pull request on that STILL has not been merged. My advice is to just forget about this project and use something else. Community Edition is abandonware.
So the gateway info is being written to correctly? I just tried using the iNextrix installer and I still get the same problem even with PHP 7.3 with Debian 11.
@KNERD As mentioned in other thread, install ASTPPv6 vanilla; about profile reload with reboot here is the possible solution.
@devangn I already know about that, and it does not work, but this one is compiled.
If I compile & install FreeSwitch alone, then the default SIP profile will load, but once the ASTPP installation part is done, a profile will no longer load.
But for now it seems the bigger issue is the gateway info is not getting saved into the database.
I can use the SignalWire repos for now until this is figured out.
Freeswitch does not use MySQL by default. It uses SQLite. Those files are located at /var/lib/freeswitch/db or /usr/local/freeswitch/db depending how you installed it. If you browse those files using DBbrowser you will probably see the info you are looking for.
If you want to use MySQL instead you need to manually configure Freeswitch for that but SQLite is going to be a lot faster. I think people who have high call rates prefer using that. Read the Freeswitch documentation for more info.
1 Like
I’ll give that db browser a try tomorrow to see if anything is there.
The sqlitebrowser seems to be GUI only, and I have never been able to get X Forwarding functioning in Debian. Their documentation lists command line functions, but instead the GUI is launched.
Edit: I was able to get X Forwarding functioning, but you can only browse directories via their GUI browser, and it only allows you to go one level deep. Sub directories do not show up. Seems to not be a very good tool.
DBbrowser is a file browser because SQLite uses individual files as databases. It’s not the same as MySQL. Just copy the file to your desktop and open it. if you want an online GUI I believe Adminer can do that although I have never tried using it with SQLite.
I am not sure what you need to look at the files for as it’s all just internal Freeswitch activity. You should have been able to figure out by now if your server is using that instead of MySQL just by looking at your Freeswitch configuration. You can also watch the datestamps of the SQLite files. You will see that changing as Freeswitch constantly reads/writes to those files.
ASTPP FreeSWITCH is configured with mod_xml_curl to fetch configuration/profiles-gateways and directories/sip-endpoints. autoload_configs/xml_curl.conf.xml is having this configs.
1 Like