Asterisk 14, Freepbx 14 - Installation fails - databases with have no tables

  1. Asterisk 14 install and runs without issue.
  2. Installing freepbx 14 creates the databases ‘asterisk’ & ‘asteriskcdrdb’ but does not create tables in neither.

I suspect that there is a problem that is being ignored (or difficult for me to identify) during installation and the installation script is failing silently before the tables are actually created.

Setup:
debian stretch
mysql 5.7.19
asterisk 14
apache2 v2.4.25
freepbx 14

executing…
mybox:/usr/src/freepbx# ./install -r -f -vvvv
Database engine [mysql]:
Database name [asterisk]:
CDR Database name [asteriskcdrdb]:
Database username [root]:
Database password:
File owner user [asterisk]:
File owner group [asterisk]:
Filesystem location from which FreePBX files will be served [/var/www/html]:
Filesystem location from which Asterisk configuration files will be served [/etc/asterisk]:
Filesystem location for Asterisk modules [/usr/lib/asterisk/modules]:
Filesystem location for Asterisk lib files [/var/lib/asterisk]:
Filesystem location for Asterisk agi files [/var/lib/asterisk/agi-bin]:
Location of the Asterisk spool directory [/var/spool/asterisk]:
Location of the Asterisk run directory [/var/run/asterisk]:
Location of the Asterisk log files [/var/log/asterisk]:
Location of the FreePBX command line scripts [/var/lib/asterisk/bin]:
Location of the FreePBX (root) command line scripts [/usr/sbin]:
Location of the Apache cgi-bin executables [/var/www/cgi-bin]:
Directory for FreePBX html5 playback files [/var/lib/asterisk/playback]:
Force Install. This will reset everything!
Assuming you are Database Root
Checking if SELinux is enabled…Its not (good)!
Reading /etc/asterisk/asterisk.conf…Done
Writing /etc/asterisk/asterisk.conf…Done
Checking if Asterisk is running and we can talk to it as the ‘asterisk’ user…Yes. Determined Asterisk version to be: 14.6.0
Preliminary checks done. Starting FreePBX Installation
Checking if this is a new install…Yes (No /etc/freepbx.conf file detected)
Database Root installation checking credentials and permissions…Connected!

[Exception]
SQLSTATE[HY000] [1045] Access denied for user ‘freepbxuser’@‘localhost’ (using password: YES)::SQLSTATE[HY000] [1045] Access den
ied for user ‘freepbxuser’@‘localhost’ (using password: YES)

[PDOException]
SQLSTATE[HY000] [1045] Access denied for user ‘freepbxuser’@‘localhost’ (using password: YES)

I believe the above error messages are a red herring.

When I run

mybox:/usr/src/freepbx# mysql -u root -p -e "GRANT ALL PRIVILEGES ON asterisk.* TO [email protected] IDENTIFIED BY ‘SOMEPASSWORD’;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 48. The table is probably corrupted
mybox:/usr/src/freepbx#

desc myuser.sql
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum(‘N’,‘Y’) | NO | | N | |
| Insert_priv | enum(‘N’,‘Y’) | NO | | N | |
| Update_priv | enum(‘N’,‘Y’) | NO | | N | |
| Delete_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_priv | enum(‘N’,‘Y’) | NO | | N | |
| Drop_priv | enum(‘N’,‘Y’) | NO | | N | |
| Reload_priv | enum(‘N’,‘Y’) | NO | | N | |
| Shutdown_priv | enum(‘N’,‘Y’) | NO | | N | |
| Process_priv | enum(‘N’,‘Y’) | NO | | N | |
| File_priv | enum(‘N’,‘Y’) | NO | | N | |
| Grant_priv | enum(‘N’,‘Y’) | NO | | N | |
| References_priv | enum(‘N’,‘Y’) | NO | | N | |
| Index_priv | enum(‘N’,‘Y’) | NO | | N | |
| Alter_priv | enum(‘N’,‘Y’) | NO | | N | |
| Show_db_priv | enum(‘N’,‘Y’) | NO | | N | |
| Super_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_tmp_table_priv | enum(‘N’,‘Y’) | NO | | N | |
| Lock_tables_priv | enum(‘N’,‘Y’) | NO | | N | |
| Execute_priv | enum(‘N’,‘Y’) | NO | | N | |
| Repl_slave_priv | enum(‘N’,‘Y’) | NO | | N | |
| Repl_client_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_view_priv | enum(‘N’,‘Y’) | NO | | N | |
| Show_view_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_routine_priv | enum(‘N’,‘Y’) | NO | | N | |
| Alter_routine_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_user_priv | enum(‘N’,‘Y’) | NO | | N | |
| Event_priv | enum(‘N’,‘Y’) | NO | | N | |
| Trigger_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_tablespace_priv | enum(‘N’,‘Y’) | NO | | N | |
| ssl_type | enum(’’,‘ANY’,‘X509’,‘SPECIFIED’) | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum(‘N’,‘Y’) | NO | | N | |
| is_role | enum(‘N’,‘Y’) | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum(‘N’,‘Y’) | NO | | N | |
±-----------------------±----------------------------------±-----±----±----------------------±------+

I would be very greatful if someone might point me in the right direction.

  1. Is the mysql.user schema appropriate for freepbx 14?
  2. might it be something totally different and what I’m cluing into?
  3. Are there additional debugging methods that I can apply to the installation script to help more accurately identify the culprit?

Thank you all in advance.
-Ben

I finally worked it out. At some point the mysql.user database was modified with three additional columns. is_role, default_role, and max_statement_time.

I found what the schema should be for mysql 5.7 here: (https://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx)

I removed the three offending columns and the installation continued.

I did run into another problem. mysql 5.7 doesn’t like the default ‘0000-00-00 00:00:00’ for date time. I modified the modules.xml file to default to ‘1901-01-01 00:00:00’ and the installation was successful!

Now I need to figure out why apache2 doesn’t start up my freepbx installation. But I don’t feel it has everything to with with configuring apache!

Hope this helps someone.

Cheers!
-Ben

What exactly are you talking about

While installing from source freepbx 14 on a debian stretch box with asterisk 14, mysql 5.7 and php 5.6. The installation script “./install -n” was failing silently preventing the installation from completing.

As of mysql 5.7 setting default ‘0000-00-00 00:00:00’ to a datetime column is invalid. Take a look at the ~/freepbx/module.xml file and specifically the table name freepbx_log

original:
<table name="freepbx_log"> .... <field name="time" type="datetime" default="0000-00-00 00:00:00"/> .... </table>

modified and working
<table name="freepbx_log"> ... <field name="time" type="datetime" default="1901-01-01 00:00:00"/> ... </table>

-Ben

That’s the “datetime” representation of a blank field. I’ve run into a similar problem with a couple of unrelated databases. You can try turning off “Not NULL” and removing the default in the database definition and see if that also solves your problem.

Also, this is something that should be documented in a Feature Request, since the distro version of the system isn’t using 5.7 yet.

Cant do it cynjut. The issue just came back when I tried to upgrade the framework to 14.0.1.14. In this upgrade, the module.xml is specifying the use of the default time value to ‘0000-00-00 00:00:00’ during the upgrade which kills the upgrade.

So to fix it I modify the modules.xml and change the default time value to the new minimum of ‘1000-01-01 00:00:00’ which completes the install but then asterisk complains that the upgrade files have been tampered with.

The real temporary fix is to default to the new default value of 1000-01-01 00:00:00 in the upgrade code.

Now I need to go figure out how to keep asterisk from complaining about tampered files.

You should be opening up a ticket about this or asking a developer to fix it.

Fixed in framework 14.0.1.18

For sure Andrew. Thanks for the note. Next time I will search first and open a ticket.

When will 14.0.1.18 be out? I just upgraded to 14.0.1.14; it was the latest notification regarding an upgrade to framework.

Thanks again