Upgrading to New System

Thanks! I’m doing it right now.

Holy Cow! what? It gave me an error the size of Montana! I’ve clipped the first few lines here…

[myuser@myserver ~]# mysql asteriskcdrdb < /var/bgm/cel.sql
ERROR at line 326: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'INSERT INTO `cel` VALUES (1457194,'CHAN_END','2015-03-29 10:10:07','1','1','','','','s','tc-maint','Local/s@tc-maint-0002305d;2','','','','','','',3,'','1427641807.316942','1427641807.316941','','','',''),(1457195,'LINKEDID_END','2015-03-29 10:10:07','1','1','','','','s','tc-maint','Local/s@tc-maint-0002305d;2','','','','','','',3,'','1427641807.316942','1427641807.316941','','','',''),(1457196,'CHAN_START','2015-03-29 10:11:00','','','','','','s','tc-maint','Local/s@tc-maint-0002305e;1','','','','','','',3,'','1427641860.316943','1427641860.316943','','','',''),(1457197,'CHAN_START','2015-03-29 10:11:00','','','','','','s','tc-maint','Local/s@tc-maint-0002305e;2',''

Allright. That didn’t work either. I think I liked the first error better.

Your OLD database contains corrupt data. It says the line number (326) - you can either edit that line and remove the bad data, or just use --force to make it continue ignoring errors.

mysql --force asteriskcdrdb < cel.sql

The cel database is 4.5 gb. Is there something I can do to trim it? I don’t know how to edit line 326, and am not sure how to tell good data from bad data anyway. Is it safe to ignore errors like that? I mean, will it mess up anything (if I haven’t already)?

Well, I assumed you are importing it for a reason - maybe I made an incorrect assumption. Are you sure you want to import it at all? If it’s 4.5gb of data, that’s a LOT of ancient calls. That line you pasted was from 2015.

Hmmmm… well, in order to know that, I’d have to talk to the boss. Is it possible from the admin interface to erase history like that according to a certain date? Or would each user have to clear their own (impossible feat to achieve here).

It looks to me like there is no way to do this from the interface.
And for a user in the UCP (at least on version 12), there is also no way; you have to delete each one individually - and that’s for voicemail. I don’t see any way to clear the call history in UCP.

I guess that’s why we have so much call history. No way to delete it.

Maybe I could do it this way; and maybe it would remove the “corrupt data” and shrink my cel. Is this a good method?

And if I do that, will it cause a problem with my already having imported the cdr db?

The problem I’ve run into is that in later versions of FreePBX, the cdr/cel tables have new fields that don’t exist in previous versions and don’t have other fields that did once exist. I have not found a really good way to account for this, and the rare case it comes up for me, we just discard the CEL data which many admins are not overly worried about.

Perhaps if you export data without the flags to drop and recreate tables and then reinstall the CDR/CEL modules post restore to add back the lost fields that might work.

CEL data is just Call History, right? That list of placed and received calls that appears in your UCP? I’m not sure if my boss will let me clear it. I’m waiting on him to say whether I can exclude the CEL db completely (keeping a backup somewhere for legal purposes).

I logged into my UCP and can see my call history there; so it must have imported at least part of the CEL even despite the errors. Just saying.

Okay, I’ve completed all of the migration except for moving the deployment id and checking the commercial module configurations. Thanks for all your help, everyone. Sorry if I’ve been a pain.

There are effectively two records of call history on a current system, the CDR (call detail record) and the much newer CEL (channel event log). When you browse to Reports → CDR Reports you are viewing the CDR data from the cdr table. When you click a link in the system column for a CDR record, you are viewing the CEL data from the cel table for that call.

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