Remote_cdr?

After upgrade module there is a new table in mysql

asteriskcdrdb
|----cdr
|—remote_cdr

remote_cdr is a syncro copy 1:1 of cdr, is a duplicate, why ?

i’m using also cdr on remote mysql, and remote_cdr table is created only in local myslq…so my CDR after module update, stop to works…only fix, manually create remote-cdr table also in remote Mysql

But …why ?
why there is 2 tanle with same data ? is a bug

There is a table is called replicate_cdr created by FreePBX, is that the one you’re talking about?

yes before today there was not , all the data were written in asteriskcdrdb → CDR now in all pabx with remote Mysql (Advanced setting → Remote CDR Database) after updating the modules no data was written in CDR table, and I had to manually create the replicate_cdr table. Right now I have 2 identical tables that write the same data! (cdr and remote_cdr) and I repeat … if you use Mysql Remote, this table must be created manually to have the data in the original cdr table again.

see attached file (can you remove previous attached file ? )

https://community.freepbx.org/uploads/default/original/3X/1/e/1eed885c1a36da3ea2462d05e8be5e933947ad76.jpeg

It is a shortened list of CDR data, helpful for cases where the CDR table has gotten quite large. It helps to shorten query times for pieces of FreeBPX that only need a fairly recent view of call logs and other things that require the CDR table. It caps entries to the past two months or so (as I recall).

Tnx

some considerations

  1. Until a few days ago all the data were written in the CDR (mysql: asteriskcdrdb–>cdr) . We have always used a REMOTE Mysql without any problems and this for about 2 years. We have a cdr table with more 100 millions record.

  2. After the update of the freepbx Modules no data was written in the CDR and we have seen from cli that the problem was a NEW table called replicate_cdr, which is NOT created, unless the CDR is on local Mysql on the same server where there is freepbx (same problem in 5 pabx after upgrade) . So in order not to lose any data we manually created a copy of the CDR table and renamed it to replicate_cdr and now everything works … this is therefore a BUG, ​​on systems that use a REMOTE Mysql the new table is NOT created, leading to the loss of telephone data. IF Mysql (CDR) and freepbx are on the same server, the new table is created!

  3. From what we have seen, this table is also used by new Sangoma Desktop for the call history (we use also Zulu and Sangoma Mobile, but we need to check if now they behave the same way … and from some online searches it seems that it stores data only for the last 2 months … FreePBX / cdr / 24f9cfb73ca - FreePBX GIT
    however this information in addition to not being documented or confirmed, to our opinion has serious problems.
    In the first place the replicate_cdr table does not have the same indexes as the original one (cdr), moreover the CDR Reports in freepbx (https://…/admin/config.php?display=cdr) now reads only from remote_cdr, as you can perform searches in the internal cdr as it did before, if this contains only the data of 2 months ?

  4. Furthermore, the Sangoma Destktop call history also reads this table … we have 1000 licenses, or 1000 users who will run thousands of queries to see the call history! finally, if this table contains only 2 months, who or how does the elimination of the old data take place? if limiting the data to two months was done for performance reasons, didn’t you think that there are customers even in just 2 months who have tens and tens of millions of data stored and the new table doesn’t speed anything?

Well, this seems like FreePBX is being used in a way that isn’t really thought about when designing a business PBX. That is using it in a telecom/provider setting either by deploying multiple boxes or setting up clients on a single box.

Now in regard to the changes to the CDRS, @mattf things like this need to be announced before the module is just put out there for people to download. This is a major change to how CDRs are handled in FreePBX and the users should be aware to avoid things like this. That being said, @voipmc why in the world would you deploy these updates on multiple boxes without testing the updates first? Based on your posts, this sounds like a large operation managing multiple FreePBX systems with your own backend databases for things. Why in the world are you pushing updates out to systems you haven’t reviewed or tested?

Let us now address the skewed view on numbers. The idea that you have 1000 users and all 1000 will be digging through their call histories on a daily or even sparsely daily basis is just fantasy. Not going to happen, almost 25 years in telecom providers/LECs/ITSPs shows this isn’t going to be what you think.

As for a single deployment of FreePBX generated “tens and tens of millions of data”, again you are not really paying attention to reality on this. Let’s just go with the first 10 million for funsies.

  • 10 Million calls in a 60 day period = 166,666 Calls per Day each day.
  • 166,666 Calls Per Day = 6,944 calls Per Hour over 24 hours.
  • 166,666 Calls Per Day = 20,833 calls Per Hour over an 8 Hour period (normal office day)

Now this just accounts for calls being made/connected and not accounting for duration. Because even to handle every call with a 5 minute duration would mean a massive amount of employees or calls going to voicemail/automated systems. The sheer amount of incoming callers would be amazing not to mention the sheer amount of employees/people needed to make those calls. To do these numbers in calls there would either have to be automated calling involved or at least 500K to 1M in multi-user subscribers (ie. 1 subscriber/client = 20 end users).

This pretty means the answer is “No, Sangoma doesn’t expect customers to be doing 10 million+ calls ever 2 months because that’s not logically possible” because the only ones doing that type of traffic are major/high volume carriers/providers.

Also, if any of your numbers were actually true and had merit. It would then beg the question, WTF are you doing using FreePBX for this when there are way better solutions to manage a more provider/telecom carrier model?

You’re right, that is, we never update a very thorough test until first, but this time it was a mistake, it happens!

For the numbers of the cdr, you must consider that when dealing with queues and other systems you have hundreds and hundreds of records in the cdr, that is, a line is not a single phone call but an event, and in addition to having 2500 contact center operators scattered throughout Europe on a Google Cloud architecture, we also have a hundred offices scattered throughout Europe that perform both in and out traffic. Of course we have reached 100 million records in the CDR in a few years and we do not expect to query the CDR from the freepbx gui, we have applied data mart and data warehouse logics to these data, but we need greater transparency, especially in business systems whether they are used by large companies as well as small ones.

Finally you are right, we are migrating the core infrastructure, already on Google Cloud, interfacing it with Twilio and DialogFlow for AI systems, this as a curiosity, if it can concern you.

This is not a change in the CDR module. A recent change in the Phone Apps module creates this table to cache a few months of CDR records. We have tested this change using the Distro configured with a remote database setup as a result of this thread, and are unable to reproduce it. Testing shows the CDR schema changes happening to database configured in FreePBX.

CDRs track the destination points of the call. So, an inbound call routed directly to an extension will generate one CDR. At the same time, CEL will track the actual events of the call such as answering, entering bridges, exiting bridges, parking, holding/unhold and a slew of other events for a very granule reporting. That single inbound call while generating a single CDR will generate at least a half dozen call events that happen during that call.

If I get up to a couple hundred thousand CDRs in Asterisk in a 30-day period, I see over 1 million CEL records in that same time period.

correct, but there is other information that as records increase, such as if a user has multiple endpoints such as Zulu, a Grandstream phone, or sangoma connect you have dozens and determines additional records. However and the point is to inform about changes of this type that are invasive to me, I understand the logic of having a “cache” table but the fact that if the remaining parts of the cdr are also missing, they are rendered inactive, that is, no more data are recorded, to my humble notice doesn’t make much sense. Also I would like to know how this temporary table is managed, if the number of information is temporary is there some process that deletes them at expiration? generally we provide through store procedures and events in mysql to autormatize the management of data in cdr, queue_log and cel and this information would be useful.

1 Like

There is a cron task that runs to shorten the replicate_cdr table every so often.

The data in the replicate_cdr table should not normally be used for displaying CDR data, only when it’s explicitly needed (setting fromAPI=true when getCalls() is executed).

It’s still not clear to me how that table was not automatically created even if you were running a remote mysql database unless the mysql user account being used by FreePBX does not have create table privileges. Is that possible in your use case?

is the hypothesis we were considering, which is a permissions problem, but the user has full access

GRANT ALL PRIVILEGES ON . TO ‘this_is_a_test’@’%’ WITH GRANT OPTION;

from a check in /var/spool/cron there is no dedicated cron

Tnx :slight_smile:

I ran into a similar problem. After the trigger was created, CDR records stopped being inserted. The replicate_cdr table existed but the schema was missing the columns linkedid, peeraccount, and sequence. So when the trigger attempted to insert into replicate_cdr, it would fail. This caused the entire transaction to fail so that no CDR records were stored in the cdr table either.

This was after a migration we had performed, so it’s possible that replicate_cdr table existed previously. When the code created the trigger cdrTrigger, I’m guessing it only checks for the existence of the replicate_cdr table and doesn’t verify the schema matches the trigger SQL statement.

Dropping the trigger fixed the issue. I then also dropped the replicate_cdr table, so that whatever code creates that can do so again if needed.

This comment is for anyone else who sees their CDR records stop working.

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