FYI FreePBX 15 Restore Module

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
One other note, I couldn't scp the file because of security issues between the two hosts. I used WinSCP to copy the file to the 16-15.1 system.

Did you whitelist the IP address of 16-15 on your 13-13 server??
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
I recopied the CDR data to the 16-15.1 system, redownloaded the scripts and ran it. It says it ran this time but I have no data:

Code:
root:~ $ ./import-cdr1313
gzip: asteriskcdr1313.sql already exists; do you wish to overwrite (y or n)? y

CDR and CEL data from Incredible PBX 13-13 has been imported.
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
Did you whitelist the IP address of 16-15 on your 13-13 server??
Yes, but I use a private/public key and no password access for SSH on the system. Unless I generate system keys and distribute on all systems, scp won't work. This is my issue. I doubt many people are configuring sshd_config the way I do.
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
I recopied the CDR data to the 16-15.1 system, redownloaded the scripts and ran it. It says it ran this time but I have no data:

Code:
root:~ $ ./import-cdr1313
gzip: asteriskcdr1313.sql already exists; do you wish to overwrite (y or n)? y

CDR and CEL data from Incredible PBX 13-13 has been imported.

Sorry there was one screw up on my end. gzip your 13-13 sql file again so you don't have to redownload. Then edit your import script and remove the line immediately above rm -f cdr1313.txt. Restoring asteriskcdr1615new.sql was cleaning out the tables.Fixed in latest, latest, latest.
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
Looking at the two files for asteriskcdrdb, it appears the data is in them along with the sql:
Code:
-rw-r--r-- 1 root root 56796264 Jul 29 08:52 asteriskcdr1313.sql
-rw-r--r-- 1 root root  1144510 Jul 29 09:39 asteriskcdr1615.sql
-rw-r--r-- 1 root root     5401 Jul 29 09:39 asteriskcdr1615new.sql
-rw-r--r-- 1 root root     5402 Jul 29 09:13 asteriskcdrdb.sql
Data is there:
Code:
INSERT INTO `cdr` VALUES ('2018-12-17 21:46:24','\"Joe Office\" <2008>','2008','70','from-internal','SIP/2008-00000000','','Park','default:71',3,3,'ANSWERED',3,'','1545101184.0','','','','','','','',''),('2018-12-17 21:48:06','\"Joe Office\" <2008>','2008','*2010','from-internal','SIP/2008-00000001','','VoiceMail','2010@default,sug(12)',4,4,'ANSWERED',3,'','1545101286.1','','','','2008','Joe Office','','',''),('2018-12-17 21:53:40','\"Joe Office\" <2008>','2008','2061','ext-local','SIP/2008-00000003','SIP/2061-00000004','Dial','SIP/2061,,TtrIb(func-apply-sipheaders^s^1)',0,0,'BUSY',3,'','1545101620.3','','','','2008','Joe Office','','',''),('2018-12-17 21:53:45','\"Joe Office\" <2008>','2008','2062','ext-local','SIP/2008-00000005','SIP/2062-00000006','Dial','SIP/2062,15,TtrIb(func-apply-sipheaders^s^1)',0,0,'BUSY',3,'','1545101625.5','','','','2008','Joe Office','','',''),('2018-12-17 21:54:35','\"Joe Office\" <2008>','2008','2062','ext-local','SIP/2008-00000007','SIP/2062-00000008','Dial','SIP/2062,15,TtrIb(func-apply-sipheaders^s^1)',0,0,'BUSY',3,'','1545101675.7','','','','2008','Joe Office','','',''),('2018-12-17 22:07:26','\"Joe Office\" <2008>','2008','2061','ext-local','SIP/2008-00000009','SIP/2061-0000000a','Dial','SIP/2061,,TtrIb(func-apply-sipheaders^s^1)',0,0,'BUSY',3,'','1545102446.9','','','','2008','Joe Office','','',''),('2018-12-17 22:07:30','\"Joe Office\" <2008>','2008','20

It just isn't winding up in the 16-15 database.

The asteriskcdr1615new.sql has no data contained in the file, just the sql.
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
@kenn10: Download the newbackup-16-15.tar.gz again just to be sure we're on the same page. The latest import-cdr1313 should have a time stamp of 09:38.
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
Sorry there was one screw up on my end. gzip your 13-13 sql file again so you don't have to redownload. Then edit your import script and remove the line immediately above rm -f cdr1313.txt. Restoring asteriskcdr1615new.sql was cleaning out the tables.Fixed in latest, latest, latest.

OK. I wiped out all the the SQL, re-downloaded your new sciprts. Copied the CDR data onto the 16-15 system again. Ran the import but no dice.

Code:
 ll import*
-rwxr-xr-x 1 root root 1835 Jul 29 09:38 import-cdr1313
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
@kenn10: No errors, but no data??

Look in asteriskcdr1615.sql. Is there data shown for both CDR and CEL tables??

Look in asteriskcdr1313.sql. Is there data shown for both CDR and CEL tables??

What do you get with this command: mysql -u root -ppassw0rd asteriskcdrdb -e "select * from cdr"

How about this one: mysql -u root -ppassw0rd asteriskcdrdb -e "desc cdr" ?? Do you get 26 rows or only 23? sequence should be last field.
 
Last edited:

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
I have deconstructed the import-cdr1313 and run each command one line at a time. Each executes without error but still no data in the CDR report.
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
Look in asteriskcdr1615.sql. Is there data shown for both CDR and CEL tables??

Look in asteriskcdr1313.sql. Is there data shown for both CDR and CEL tables??

The data should be in lines starting with:

INSERT INTO `cdr` VALUES...

INSERT INTO `cel` VALUES...
 
Last edited:

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
First command is blank. Second command gives this. Note duplicate fields: Sorry. no duplicate fields.
2377
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
Look in asteriskcdr1615.sql. Is there data shown for both CDR and CEL tables??

Look in asteriskcdr1313.sql. Is there data shown for both CDR and CEL tables??

The data should be in lines starting with:

INSERT INTO `cdr` VALUES...

INSERT INTO `cel` VALUES...

The asteriskcdr1615new has data but the asteriskcdr1615 does not. Asterisckcdr1313 has CDR and CEL tables.
2378
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
Ward, there is some data but it looks like the date field is incorrect. Below, the first is from the 16-15 system. Note no current July data and the day of week is in the data. The next one is from the 13-13 system for comparison. I think the date formatting for the new load is incorrect.

Code:
Call Date     Recording     System     CallerID     Outbound CallerID     DID     App     Destination     Disposition     Duration     Userfield     Account     CDR Table     CDR Graph
Fri, 10 May 2019 12:41        1557506512.102    3135090777        8285263174    BackGround    s [ivr-3]    ANSWERED    00:10             
Fri, 10 May 2019 10:01        1557496913.101    6822047444

This is from the 13-13 system:
Code:
Call Date     Recording     System     CallerID     Outbound CallerID     DID     App     Destination     Disposition     Duration     Userfield     Account     CDR Table     CDR Graph
2019-07-27 15:56:03        1564257363.208    "Joes Office 2017" <2017>            Park    70    ANSWERED    00:07             
2019-07-27 15:55:43        1564257343.207    "Joes Office 2017" <2017>            Park    70    ANSWERED    00:07
 
Last edited:

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
@kenn10: If asteriskcdr1615new.sql has data, that means it got it from your existing 16-15 asteriskcdrdb database. Is that what should be happening? Do you actually have calls in there, or is this from a previous import??

If INSERT INTO `cdr` VALUES... line is missing in asteriskcdr1615.sql, then we have a problem with the sed commands assuming there is an INSERT INTO `cdr` VALUES... line in asteriskcdr1313.sql. Does this sound like the correct situation??

Try commenting out the two rm lines for cdr1313.txt and cel1313.txt. Run the script again and see if those two files get populated. If so, then there's definitely a problem with sed or your INSERT INTO commands don't look the same as mine.
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
Ward, there is some data but it looks like the date field is incorrect. Below, the first is from the 16-15 system. Note no current July data and the day of week is in the data. The next one is from the 13-13 system for comparison. I think the date formatting for the new load is incorrect.

Code:
Call Date     Recording     System     CallerID     Outbound CallerID     DID     App     Destination     Disposition     Duration     Userfield     Account     CDR Table     CDR Graph
Fri, 10 May 2019 12:41        1557506512.102    3135090777        8285263174    BackGround    s [ivr-3]    ANSWERED    00:10            
Fri, 10 May 2019 10:01        1557496913.101    6822047444

This is from the 13-13 system:
Code:
Call Date     Recording     System     CallerID     Outbound CallerID     DID     App     Destination     Disposition     Duration     Userfield     Account     CDR Table     CDR Graph
2019-07-27 15:56:03        1564257363.208    "Joes Office 2017" <2017>            Park    70    ANSWERED    00:07            
2019-07-27 15:55:43        1564257343.207    "Joes Office 2017" <2017>            Park    70    ANSWERED    00:07

I think you're noticing this difference in the FreePBX GUI CDR reports. Correct? It's just a different CDR module that formatted the data differently. The first field in BOTH .sql files should show calldate as a datetime type field. In the actual data in the .sql files, there shouldn't be a day of the week, e.g. INSERT INTO `cdr` VALUES ('2019-07-17 15:50:58'...
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
@wardmundy I was using the FreePBX CDR report to determine if there was data. My range was 7/1/2019 thru 7/31/2019. It brings up no data. There is data for the month of July in the original database, the original asteriskcdr1313.sql on 13-13 and on the unzipped asteriskcdr1313.sql on 16-15.1.

After running the importcdr-1313 script, the output files do not contain data for June or July 2019. The cdr1313.txt does not have anything beyond May. I don't get it. My cdr database is not huge. The latest entry on the report is for May 10, 2019 but the original database is current to today.

Does the dump limit how many bytes or records can be in the file during conversion?
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
@wardmundy please look at the difference in files size with the original asteriskcdr1313.sql and the resulting 1516 files: 56Mb vs 1Mb.

Code:
-rw-r--r-- 1 root root 56820170 Jul 29 13:51 asteriskcdr1313.sql
-rw-r--r-- 1 root root  1168114 Jul 29 13:56 asteriskcdr1615.sql
-rw-r--r-- 1 root root  1175237 Jul 29 13:56 asteriskcdr1615new.sql
-rw-r--r-- 1 root root     5402 Jul 29 09:13 asteriskcdrdb.sql
 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,170
Reaction score
5,199
@kenn10: Search in asteriskcdr1313.sql and see if there are more than 2 occurrences of "INSERT INTO." The first and last are what we are using to pull out the CDR and CEL data. Could it be that the mysqldump is creating more than two?? That's the only thing I can think of that would limit the extraction size.
 

kenn10

Well-Known Member
Joined
Dec 16, 2007
Messages
3,764
Reaction score
2,173
@wardmundy I sent you a PM with the zipped database in case you'd like to analyze it.
 
Get 3CX - Absolutely Free!

Link up your team and customers Phone System Live Chat Video Conferencing

Hosted or Self-managed. Up to 10 users free forever. No credit card. Try risk free.

3CX
A 3CX Account with that email already exists. You will be redirected to the Customer Portal to sign in or reset your password if you've forgotten it.
Top