Table errors?

  1. When I go to “Reports” tab is shows the below. Why? How do I fix?

SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE UNIX_TIMESTAMP(calldate) >= UNIX_TIMESTAMP(‘2009-12-01’) GROUP BY substring(calldate,1,10) [nativecode=1146 ** Table ‘asteriskcdrdb.cdr’ doesn’t exist]

  1. When I go to “Recording” tab, log-in and then click on the the “call monitor” link it says, DB Error: no such table
    Why is this is the case? hod do I fix?

  2. Why would, “Flash Operator Panel” show a BOLD ‘s’ one extension. What does this mean?

So here is my story what happened. All the above started to occur by itself. It started with the entire UI not working.

FreePBX System Status
FreePBX Notices

  Error There are 1 bad destinations
  Ignore this
  Ringroup: Michael Wayne Fail Over (601)

  Added 1 week, 2 days, 23 hours, 46 minutes ago

show all
SELECT extension,name,voicemail FROM users ORDER BY extension [nativecode=1146 ** Table ‘asterisk.users’ doesn’t exist]SQL - SELECT extension,name,voicemail FROM users ORDER BY extension

After awhile now we get this

DELETE FROM notifications WHERE module = ‘core’ AND id = ‘AMPDBPASS’ [nativecode=1146 ** Table ‘asterisk.notifications’ doesn’t exist]SQL - DELETE FROM notifications WHERE module = ‘core’ AND id = ‘AMPDBPASS’

So we logged into the backend to check the databases, and it seems that our asterisk databases are gone. I don’t know if it’s coincidental to what appears to be an openfire jabber server installation or not. Openfire Jabber was installed for many weeks and never caused a problem. It appears that the DB files still exist, but are in what appears to be a temporary format, like something was working on them in the backend and the system was rebooted during the process.

So we found at the end of each file was symbol ~.
The next we removed it.

cd /var/lib/mysql/asterisk
for i in $list; do echo $i; newi=${i/~/}; echo $newi; mv $i $newi; echo; done

… but we still have the problems above. Also not sure how we can avoid this or what caused this issue.

I would recommend doing a clean install.

I have a similar issue, with missing tables in a database, due to an error when I was adding a table to the asterisk database. By clean install, what do you mean?

Is there anything I can do to get the asterisk database back without starting completely from scratch? A coworker and I worked literally all day trying to get our system up and running to a point where we could begin to learn how to use it. How does one restore the database to its original glory? Is there a script to run to reset the database to its default state?

I’m using AsteriskNow (CentOS 5.3), with Asterisk 1.4.28 (installed/updated by yum).

Please note: I’m a total newbie to Linux and Asterisk.

I don’t mean to hijack this, but I can’t find a solution anywhere else online. This post is the closest hit I’ve gotten to a solution.

See this thread: