NO JOY Missing CDR Reports - mysql error 3055 - after move and restore

[pixel]

Hello,

I recently moved an install from a hosted install (Lylix) to an in house install (VMWare ESXi). To do this I installed a fresh copy of PiaF purple and updated all the modules. I then ran a full backup (free-pbx backup module) on the old server and restored it to the new one. After pointing the phones and the trunks to the new server all seemed well.

Today I noticed a couple of items:
First, if I run asterisk -r -vvv, I will see the following error at the end of every call.

[2013-11-14 18:44:40] ERROR[3035]: cdr_mysql.c:203 mysql_log: Cannot connect to database server localhost: (1045) Access denied for user 'asteriskuser'@'localhost' (using password: YES)
Second: If I run a CDR Report, the data ends at the time of the switch to the new server. There are call logs for everything that happened on the old server but nothing from the new.
I tried granting permission to asteriskuser@localhost to all tables which seemed to have no effect. I then ran passwd-master with the same password that has always been used on this install and it killed asterisk. So I restored back to a snapshot from before I started tinkering with permissions and passwords.
I googled around a bit and found the same error message asked about several times on other forums, all had recently done a backup and restore or move. None had any replies.
Any ideas??
I have attached the output of status below.
Thanks,
Jeff
-----
-----
Asterisk = ONLINE | Dahdi = ONLINE | MySQL = ONLINE │
│ SSH = ONLINE | Apache = ONLINE | Iptables = ONLINE │
│ Fail2ban = ONLINE | Internet = ONLINE | Ip6Tables = ONLINE │
│ Disk Free = ADEQUATE| Mem Free = ADEQUATE| NTPD = ONLINE │
│ SendMail = ONLINE | Samba = OFFLINE | Webmin = ONLINE │
│ Ethernet0 = ONLINE | Ethernet1 = N/A | Wlan0 = N/A │
│ │
│ PIAF Installed Version = 2.0.6.4 under *VMWARE* │
│ FreePBX Version = 2.11.0.9 │
│ Running Asterisk Version = 1.8.24.0 │
│ Asterisk Source Version = 1.8.24.0 │
│ Dahdi Source Version = 2.7.0.1 │
│ Libpri Source Version = 1.4.14 │
│ IP Address = 172.30.30.250 on eth0 │
│ Operating System = CentOS release 6.4 (Final) <> │
│ Kernel Version = 2.6.32-358.6.2.el6.i686 - 32 Bit

 

[pixel]

Wanted to give an update on this. I still have the issue but have found a couple of pieces of information that may be a clue for someone.

When looking for the mysql error, someone recommend upgrading mysql. So I tried that and got the following output.

root@pbx:~ $ mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
FATAL ERROR: Upgrade failed
root@pbx:~ $
When using phpMyAdmin, I can select any database and try to view the Privileges. I get the following error:
Error

SQL query: Edit
(SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, `Db` FROM `mysql`.`db` WHERE 'asterisk' LIKE `Db` AND NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` = 'N')) UNION (SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `A[...]
MySQL said:
#1054 - Unknown column 'Event_priv' in 'field list'
Thanks in advance for any thoughts you may have.
Jeff

 

[sawgood]

Your fix for this might be to:

vi /etc/asterisk/cdr_mysql.conf

For the [global] context:

Make sure the 'password' field has the 'new' password details (this does not come over for some reason(s) during a backup/restore), and also that the 'userfield' entry is commented out.

Example:
==============================
[global]
hostname = localhost
dbname=asteriskcdrdb
table=cdr
password = 567YnrZ (put your new DB password here) (see if this is different)
user = asteriskuser
;port=3306
;sock=/tmp/mysql.sock
;userfield=1 (also I've see this as NOT be commented out) (and it works if you comment it out)
===============================

I've had success not running amportal restart and amportal chown (after the save), but for good measure you might need to run them.
Make any call (even a feature code call), and use the CDR Reports FPBX module to see the new call attempt is now listed.

I've faced the same situation you are facing, and the above was the fix most every time.

I hope this helps.

Shawn