Check and repair mysql db
mysqlcheck -A -r
Asterisk prints mysql.column errors
[ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
Cause:
MySQL Definition Tables are not up to date with currently installed packages.
Recovery:
mysql_upgrade
[ERROR] mysqld: Table './mysql/servers' is marked as crashed and last (automatic?) repair failed
myisamchk -r /var/lib/mysql/*/*.MYI
myisamchk -r /var/lib/mysql/mysql/db.MYI
or
service mariadb stop
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
REPAIR TABLE mysql.plugin;
REPAIR TABLE mysql.servers;
REPAIR TABLE mysql.db;
FLUSH PRIVILEGES;
service mariadb restart
These commands will backup existing CDR and queue_log data using mysqldump
mysqldump scopserv cdr > cdr.sql
mysqldump scopserv queue_log > queuelog.sql
These commands will restore mysqldump CDR and queue_log data using mysqldump
mysqldump scopserv cdr > cdr.sql
mysqldump scopserv queue_log > queuelog.sql
Rebuild CDR and queue_log data from Master.csv file located in /var/log/asterisk/cdr-csv/Master.csv
/var/www/scopserv/realtime/queue2sql --recover /var/log/asterisk/cdr-csv/Master.csv
Rebuild CDR and queue_log data from Master.csv file located in custom directory
/var/www/scopserv/realtime/queue2sql --recover YOUR_FILE
Backup Only Telephony configuration using mysqldump
mysqldump scopserv > asterisk_extensions.sql
Restore Only Telephony configuration using mysqldump
mysql scopserv < asterisk_extensions.sql
R8 upgrade fails to update CDR or ACD Detailed Reports
DB structures requires update and lock file needs to be removed.
rm all .lock file in ~scopserv/telephony/scripts/upgrade/ directory using:
rm -f /var/www/scopserv/telephony/scripts/upgrade/*.lock
then run update script:
php /var/www/scopserv/telephony/scripts/update
Command that should fix SQL data / recordings:
/bin/sh /var/www/scopserv/telephony/scripts/scopserv_fix_cdr.sh
Check if there is data in cdr_pre SQL table:
SELECT COUNT(*) FROM cdr_pre;
Check if there is data in cdr_post SQL table:
SELECT COUNT(*) FROM cdr_post;
If no data = then run the FIX scripts
If data = restart CRON script
Command to import missing data from CSV
/bin/sh /var/www/scopserv/telephony/scripts/scopserv_import_cdr.sh [CSV file (optional)]
To restore blank CDR
kill -KILL [PROCESS ID] ; php -q /var/www/scopserv/telephony/scripts/crond-reports-realtime.php
To find the PROCESS ID use:
ps aux |grep crond-reports-realtime
Damaged mysql db:
cd /var/lib/mysql/scopserv
myisamchk -r cdr
To remove old reports:
MariaDB [scopserv]> delete from cdr where calldate < '2019-01-01 00:00:00';
-> delete from queue_log where time < '2019-01-01 00:00:00';
-> delete from cel where eventtime < '2019-01-01 00:00:00';
show tables LIKE 'cel%';
then : drop table cel_;
Updating from C5 to C6+
/var/www/scopserv/realtime/queue2sql --migrate
Force telephony25 switch (pre telephony25 systems)
scopserv-switch --25
service asterisk restart
service scopserv_fastagi restart
After updating telephony package to telephony25 versions or executing a telephony25 switch from earlier release:
php -q /var/www/scopserv/telephony/scripts/update --force
scopserv-switch --25
service asterisk restart
service scopserv_fastagi restart
Upgrading from asterisk pre 18 version or toggling to asterisk 18 from any other version...
asterisk-switch --18
service asterisk restart
service scopserv_fastagi restart