FreePBX Distro Upgrade from 5.x to 6.x CDR Problem

Hello,

Using http://upgrades.freepbxdistro.org/stable/6.12.65/upgrade-5.211.65-to-6.12.65-20.sh I updated from 5.211.65-21 to the 6.12.65-20. Since the update I’ve been unable to access the CDR Reports instead I received

FATAL ERROR SQLSTATE[HY000][2005] Unknown MySQL server host ‘localhost:3306’ (1)

Trace Back
/var/www/html/admin/libraries/BMO/Database.class.php:70->_construct()
0: mysql:host=localhost:3306;dbname=asteriskcdrdp
1:freepbxuser
[2]: (password that works logging in from commandline, mysql -u freepbx -p)

/var/www/html/admin/modules/cdr/Cdr.class.php:35 Database->_construct()
same as above etc.

I’ve verified /etc/hosts has localhost and freepbxuser has access to asteriskcdrdb.

I thought uninstalling and reinstalling the CDR Reports would help, but it will not install again. After I confirm the module install the popup status just has,

Please wait while module actions are performed
Installing cdr

FATAL ERROR
SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1)
SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1)
Trace Back/var/www/html/admin/libraries/BMO/Database.class.php:70 PDO->__construct()

[2]: (freepbxuser password)

/var/www/html/admin/modules/cdr/Cdr.class.php:35 Database->__construct()

[2]: (freepbxuser password)

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:116 Cdr->__construct()
0:

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:60 Self_Helper->autoLoad()

/var/www/html/admin/libraries/modulefunctions.class.php:2251 Self_Helper->injectClass()

/var/www/html/admin/libraries/modulefunctions.class.php:1821 module_functions->_runscripts()
0: cdr
1: install
[2]:
/var/www/html/admin/page.modules.php:283 module_functions->install()
0: cdr

/var/www/html/admin/config.php:341 include()
0: /var/www/html/admin/page.modules.php

I have verified MYSQL is running and listening on 3306. Not sure what else to do except go to my archive created prior to doing this. But then I my EndPoint manager is no longer licensed cause I’m using a cloned VM of my live machine.

Thought I was doing this right using the script to upgrade. Anyone else had this problem?

Thank you,

I had a similar problem. I just tried to downgrade CDR Reports but to no avail.
I understand it has something to do with the version of MySQL no longer supporting connections using localhost:3306. Just using localhost without the port should work, but I guess the only way is hacking your way through this.

Does anyone else have input?

Regards
Dan

Do you remember if this was an Asterisk change you made? I’ve been going through all the .conf in /etc/asterisk and I find nothing with localhost 3306. I removed the entries under Remote CDR Database in amportal.conf as it looks like fresh installs leave them blank and must pickup the db info from another config. I noticed the res_odbc_additional.conf is empty and the file says not to edit it by hand. Not sure where, the doc says go to http://freepbx.org/configuration_files but that is a broken link. I added text manually and it made no difference.

It would appear moving to the Distro to gain access to the commercial modules leaves you wide open to upgrade errors in the scripts.

I don’t think this is an upgrade error in the scripts. The problem is that local host is not resolving to 127.0.0.1

You can go into /etc/freepbx.conf and change local host to 127.0.0.1

But you should first figure out why local host isn’t resolving correct. Look in /etc/hosts and see if local host is in there

I don’t think it’s that it can’t resolve localhost host, I think it can’t resolve localhost:3306. I checked /etc/hosts, I can ping localhost with 127.0.0.1 responding, I did make the change to freepbx.conf as you suggested and the error is the same. Installing the module from command line or Gui gives same error.
[[email protected] etc]# amportal a ma install cdr

Fetching FreePBX settings with gen_amp_conf.php…

[FATAL] SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1) SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1)

Trace Back:

/var/www/html/admin/libraries/BMO/Database.class.php:70 PDO->__construct()
[0]: mysql:host=localhost:3306;dbname=asteriskcdrdb
[1]: freepbxuser
[2]:

/var/www/html/admin/modules/cdr/Cdr.class.php:35 Database->__construct()
[0]: mysql:host=localhost:3306;dbname=asteriskcdrdb
[1]: freepbxuser
[2]:

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:116 Cdr->__construct()
[0]:

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:60 Self_Helper->autoLoad()
[0]: Cdr

/var/www/html/admin/libraries/modulefunctions.class.php:2251 Self_Helper->injectClass()
[0]: Cdr
[1]: /var/www/html/admin/modules/cdr/Cdr.class.php

/var/www/html/admin/libraries/modulefunctions.class.php:1821 module_functions->_runscripts()
[0]: cdr
[1]: install
[2]:

/var/lib/asterisk/bin/module_admin:68 module_functions->install()
[0]: cdr
[1]:

/var/lib/asterisk/bin/module_admin:847 doInstall()
[0]: cdr
[1]:

Got it, Jasonmel!

What I ended up doing was:

  1. I created a new SQL user with full privileges for the hostname of the server
  2. I find all occurrences of asteriskuser and replaced it with the newly created username, change the localhost to the hostname, and changed the password of course
  3. I went to the database asterisk, table freepbx_settings and changed the same occurrences (only needed for FreePBX), also changed the port 3306 to an empty string

Now I could reinstall the CDR component.

As I said earlier, PHP seems to have changed something that won’t allow the port number in the connection string.
Probably changing the hostname and SQL user wasn’t needed, but I am just explaining what I did.

Good luck

Dan

Words along can’t express my gratitude, so I’ll add a :smiley:

The solution to my problem was running
update freepbx_settings set value="" where name=‘Remote CDR DB Port’;

Thank you VERY much, after an entire Saturday on google and the hours this morning I was able to correct the problem thanks to your post.

Regards,

Jason

2 Likes

Nice work gentlemen, I got bit by this issue as well. If anyone else runs into this and needs exact steps, this is what I did:

mysql -u freepbxuser -p
(enter password when prompted)
use asterisk;
update freepbx_settings set value="" where name='Remote CDR DB Port';
exit;

After that, all good. Thanks for the info, saved me a bunch of time.

1 Like

A couple of things here.

The default value for CDRDB Port is empty. Something set this to 3306. Nothing in any of our scripts does this. I can only assume that both of you did that in the past while on 2.11 which leads me to my next point.

There is a bug in CDR that doesn’t set the port correctly. Instead of manually modifying the database you could have also gone to advanced settings and reset CDR DB Port as it would have shown up different from the normal setting of nothing and it would have given you a revert command as well.

I am fixing CDR reports to define the port correctly moving forward as well.

The GUI fix:

  1. go into Settings, Advanced Settings
  2. enable display read only setting and enable override read only settings
  3. Locate Remote CDR DB Port and remove the value from this field

save and apply settings.

2 Likes

Or update to the new CDR reports:

  • cdr v12.0.22
  • cdr v13.0.9

I disabled it and now have the following error when I hit ‘Apply Config’:

exit: 1
[FATAL] SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1) SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306’ (1)

Trace Back:

/var/www/html/admin/libraries/BMO/Database.class.php:70 PDO->__construct()
[0]: mysql:host=localhost:3306;dbname=asteriskcdrdb
[1]: freepbxuser
[2]: 68e05d5f933e

/var/www/html/admin/modules/cdr/Cdr.class.php:35 Database->__construct()
[0]: mysql:host=localhost:3306;dbname=asteriskcdrdb
[1]: freepbxuser
[2]: 68e05d5f933e

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:116 Cdr->__construct()
[0]:

/var/www/html/admin/libraries/BMO/Self_Helper.class.php:36 Self_Helper->autoLoad()
[0]: Cdr

/var/www/html/admin/libraries/BMO/Hooks.class.php:163 Self_Helper->__get()
[0]: Cdr

/var/www/html/admin/libraries/BMO/Hooks.class.php:37 Hooks->preloadBMOModules()

/var/lib/asterisk/bin/retrieve_conf:26 Hooks->updateBMOHooks()

Your /etc/hosts file is wrong. Please make sure localhost resolves correctly to 127.0.0.1

Thanks, and pardon my stupidity…but where exactly do I find that in the GUI?

Can anybody point me in the right direction? I can’t apply new update configs without this resolved.

Update CDR reports and you will be good to go

1 Like

Had a similar problem with this the other day. I found the issue was the old CDR password was stored in the mysql database.

Easy fix for it would be to SSH into the PBX and log into mysql:

mysql -u freepbxuser -p

Then select the freepbx database:

use `asterisk`;

Now update the old password to the new one:

update `freepbx_settings` SET `value` = '[New Password]' WHERE `value` = '[Old Password]';

Things should work after this. If not you may need to reload amportal and then try to update the CDR module.

Disclaimer: It’s certainly worth making a backup of your Database before doing this, especially if you don’t understand what you’re doing. You could break something.

You can just do this in the GUI. Quicker and safer that way

THANKS!!!
Took me hours and could not solve it. Migrated from a FreePBX-Asterisk-1.8 to FreePBX-Asterisk-11.
You saved my day…or should I say my night :wink:

Ciao
Reinhard