Drop CDR table. Any way to re-create it?

pgkm · December 13, 2023, 9:03am

So I wanted to clear CDR recordings and instead of cleaning it (delete from) I think I’ve drop the entire table (or database? was long ago - not sure). Everything worked though, except of CDR ofc. But now I want CDR back, and asking for the way to manually recreate CDR structure without reinstalling the whole FreePBX.

If there would be someone willing to help, here are observations I can share to start:

Reports > CDR Reports: when I press Search button nothing happens
Reports > Call Event Logging: red bar at the top of the page appears, showing the text:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘asteriskcdrdb.cel’ doesn’t exist
File:/var/www/html/admin/modules/cel/Cel.class.php:485

VoipMC · December 13, 2023, 9:11am

structure database

asteriskcdrdb
→ CDR
—> CEL
—> Replicate_cdr (dismiss)
----> Transient_CDR

/*
SQLyog Ultimate
MySQL - 5.5.65-MariaDB : Database - asteriskcdrdb

*/

/*!40101 SET NAMES utf8 */;

/!40101 SET SQL_MODE=‘’/;

/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;
CREATE DATABASE /!32312 IF NOT EXISTS/asteriskcdrdb /*!40100 DEFAULT CHARACTER SET latin1 */;

USE asteriskcdrdb;

/*Table structure for table cdr */

DROP TABLE IF EXISTS cdr;

CREATE TABLE cdr (
calldate datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’,
clid varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
src varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dcontext varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
channel varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dstchannel varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastapp varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastdata varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
duration int(11) NOT NULL DEFAULT ‘0’,
billsec int(11) NOT NULL DEFAULT ‘0’,
disposition varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
amaflags int(11) NOT NULL DEFAULT ‘0’,
accountcode varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
uniqueid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
userfield varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
did varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
recordingfile varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnum varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnam varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnum varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnam varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst_cnam varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
linkedid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
peeraccount varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
sequence int(11) NOT NULL DEFAULT ‘0’,
KEY calldate (calldate),
KEY dst (dst),
KEY accountcode (accountcode),
KEY uniqueid (uniqueid),
KEY did (did),
KEY recordingfile (recordingfile(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table cdr */

/*Table structure for table cel */

DROP TABLE IF EXISTS cel;

CREATE TABLE cel (
id int(11) NOT NULL AUTO_INCREMENT,
eventtype varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
eventtime datetime NOT NULL,
cid_name varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_num varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_ani varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_rdnis varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_dnid varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
exten varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
context varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
channame varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
appname varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
appdata varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
amaflags int(11) NOT NULL,
accountcode varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
uniqueid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
linkedid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
peer varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
userdeftype varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
extra varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
KEY uniqueid_index (uniqueid),
KEY linkedid_index (linkedid),
KEY context_index (context)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table cel */

/*Table structure for table replicate_cdr */

DROP TABLE IF EXISTS replicate_cdr;

CREATE TABLE replicate_cdr (
calldate datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’,
clid varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
src varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dcontext varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
channel varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dstchannel varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastapp varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastdata varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
duration int(11) NOT NULL DEFAULT ‘0’,
billsec int(11) NOT NULL DEFAULT ‘0’,
disposition varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
amaflags int(11) NOT NULL DEFAULT ‘0’,
accountcode varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
uniqueid varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
userfield varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
did varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
recordingfile varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnum varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnum varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst_cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
linkedid varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
peeraccount varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
sequence int(11) NOT NULL DEFAULT ‘0’,
KEY calldate (calldate),
KEY dst (dst),
KEY uniqueid (uniqueid),
KEY did (did)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table replicate_cdr */

/*Table structure for table transient_cdr */

DROP TABLE IF EXISTS transient_cdr;

CREATE TABLE transient_cdr (
calldate datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’,
clid varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
src varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dcontext varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
channel varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dstchannel varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastapp varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
lastdata varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
duration int(11) NOT NULL DEFAULT ‘0’,
billsec int(11) NOT NULL DEFAULT ‘0’,
disposition varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
amaflags int(11) NOT NULL DEFAULT ‘0’,
accountcode varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
uniqueid varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
userfield varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
did varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
recordingfile varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnum varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnum varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
outbound_cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
dst_cnam varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
linkedid varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
peeraccount varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
sequence int(11) NOT NULL DEFAULT ‘0’,
KEY calldate (calldate),
KEY dst (dst),
KEY uniqueid (uniqueid),
KEY did (did)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table transient_cdr */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

pgkm · December 13, 2023, 10:07am

@VoipMC
Thank you very much, I’ve added these tables via DBeaver, then restarted asterisk via putty (core restart now) and now it works!
i.imgur. com/dIGwRfu.png

system · December 20, 2023, 10:07am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.