Upgrading to New System

The first link is the guide for the migration tool, which I just completed.
The second link - what is CDR? Do I need to copy that over?

I’ll take a good try at that second link to copy the other stuff. Thanks for that!

I have done a ton. Let me know if you have any issues. Don’t forget to export your endpoint to csv

I ran the script and it seemed to work fine except for this part; I presume I will just move these file manually. These lines are from the script output:

Directory /var/spool/asterisk/monitor? (1.67GB) [yN] y
Directory /var/spool/asterisk/voicemail? (576.58MB) [yN] y
Directory /tftpboot? (1.51GB) [yN] y

Creating encrypted backup for new machine. This may take some time.
Backing up the following:
du: cannot access `5': No such file or directory
du: cannot access `t': No such file or directory

Then for the post-migration part:
Following the directions here, I was able to import the first sql db (cdr.sql) just fine, but when I go to import the second one, I get this error:

[myuser@myserver ~]# mysql asteriskcdrdb < /var/bgm/cel.sql
ERROR 1054 (42S22) at line 24: Unknown column 'src' in 'field list'

That db is 4.5gb in size. What do I do now?

did you export them first on the previous PBX?

Yes, I followed the instructions just like it said. The only thing I changed was the location of the output and input directories. I get the db import error when I run the last import command. Here is what it said to do:

  1. Transfer of CDR Export / Import

OLD PBX:
mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cdr > cdr.sql
mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cel > cel.sql

Transfer the sql files to the new server (WinSCP, etc)

Run on new PBX:
mysql asteriskcdrdb < cdr.sql
mysql asteriskcdrdb < cel.sql

Can you confirm the files are there on the new and old? Use what I have exactly and it should work

The files - you mean the sql files? I just created the folders yesterday; and just dumped the sql files an hour or so ago. Everything is fresh. I’ve never done this before today.
The only thing I changed was this when I made them:

mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cdr > cdr.sql
mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cel > cel.sql

to

mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cdr > /var/myfolder/cdr.sql
mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cel > /var/myfolder/cel.sql

And this when I imported them:

mysql asteriskcdrdb < cdr.sql
mysql asteriskcdrdb < cel.sql

to

mysql asteriskcdrdb < /var/myfolder/cdr.sql
mysql asteriskcdrdb < /var/myfolder/cel.sql

I tried several times to import the second db, but it gives the same error every time. I’ll try and export it again, copy it and import it again. Maybe it will work better the second time?

Okay, I tried it all again, fresh, and have the same error.

That’s saying ‘Don’t create the tables’.

That’s saying ‘The tables don’t match’.

I’m unsure why someone would say NOT to create the tables. Just use mysqldump asteriskcrdb cel > cel.sql

Should I redo the cdr, too? Although, I’ve already imported it… The cdr db didn’t give any error, though.

No errors, no problems. Just do the CEL.

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.