[Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Sat Aug 31 22:40:15 EDT 2019

Hi Matthias!

I tried your patch and now I don’t get any message.

There is a log for this?

Thank you!

De: Hohl Matthias <matthias.hohl at telematica.at> 
Enviado el: sábado 31 de agosto del 2019 11:31
Para: 'Marcos Pytel' <marcos.pytel at cotesma.com.ar>; 'Spce-user' <Spce-user at lists.sipwise.com>
Asunto: AW: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Did you try my patch?

Von: Marcos Pytel <marcos.pytel at cotesma.com.ar <mailto:marcos.pytel at cotesma.com.ar> > 
Gesendet: Freitag, 30. August 2019 14:04
An: 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Cc: 'Hohl Matthias' <matthias.hohl at telematica.at <mailto:matthias.hohl at telematica.at> >
Betreff: RE: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Hi!

I’m using mr7.4.1 and I have the same problem! The script doesn’t work.

Regards,
Marcos.

De: Spce-user <spce-user-bounces at lists.sipwise.com <mailto:spce-user-bounces at lists.sipwise.com> > En nombre de Hohl Matthias
Enviado el: martes 27 de agosto del 2019 18:56
Para: 'Rene Krenn' <rkrenn at sipwise.com <mailto:rkrenn at sipwise.com> >; 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Asunto: Re: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Hello,

after today’s upgrade to latest 6.5.5 the acc-cleanup failure is still there.

Failure:

In /usr/share/perl5/NGCP/Cleanup.pm at line 109 and 110 to refer to

            my $user = $self->env('user');

            my $pass = $self->env('pass');

but the /etc/ngcp-cleanup-tools <http://acc-cleanup.conf/> /acc-cleanup.conf

just have the variable “username” and “password”.

So it doesn’t work out of the box. At line 534 and 535 you fixed it in the latest version but not at line 109 and 110.

Work-a-Round:

# cp /etc/ngcp-config/templates/etc/ngcp-cleanup-tools/acc-cleanup.conf.tt2 /etc/ngcp-config/templates/etc/ngcp-cleanup-tools/acc-cleanup.conf.customtt.tt2

# nano /etc/ngcp-config/templates/etc/ngcp-cleanup-tools/acc-cleanup.conf.customtt.tt2

Add there:

user = [% credentials.mysql.cleanuptools.u %]

pass = [% credentials.mysql.cleanuptools.p %]

that it looks like this:

username = [% credentials.mysql.cleanuptools.u %]

password = [% credentials.mysql.cleanuptools.p %]

user = [% credentials.mysql.cleanuptools.u %]

pass = [% credentials.mysql.cleanuptools.p %]

# ngcpcfg apply 'Cleanup Fix’

Von: Spce-user <spce-user-bounces at lists.sipwise.com <mailto:spce-user-bounces at lists.sipwise.com> > Im Auftrag von Hohl Matthias
Gesendet: Dienstag, 27. August 2019 03:19
An: 'Rene Krenn' <rkrenn at sipwise.com <mailto:rkrenn at sipwise.com> >; 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Betreff: Re: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

So after a long night it is done:

In the Cleanup.pm at line 534 and 535 was still a bug with usename/password… so have to change this 

From

    my $user = $self->env('username');

    my $pass = $self->env('password');

to

    my $user = $self->env('user');

    my $pass = $self->env('pass');

Also note:

Ngcp-fraud-daily-lock & ngcp-fraud-auto-lock & ngcp-cdr-exporter was sending data and locked the db, so the cleanup tool can’t complete his work until we killed the processes.

show processlist;

=> kill from user "soap"

kill [ID]

Von: Spce-user <spce-user-bounces at lists.sipwise.com <mailto:spce-user-bounces at lists.sipwise.com> > Im Auftrag von Hohl Matthias
Gesendet: Dienstag, 27. August 2019 00:41
An: 'Rene Krenn' <rkrenn at sipwise.com <mailto:rkrenn at sipwise.com> >; 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Betreff: Re: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

This did it:

> use accounting;
> select count(*) from cdr where start_time < UNIX_TIMESTAMP('2019/01/01')
> create table cdr_backup_until_20181231 like cdr;
> insert into cdr_backup_until_20181231 select * from cdr where start_time < UNIX_TIMESTAMP('2019/01/01');
> select count(*) from cdr_backup_until_20181231;
> delete from cdr where start_time < UNIX_TIMESTAMP('2019/01/01');

Von: Spce-user <spce-user-bounces at lists.sipwise.com <mailto:spce-user-bounces at lists.sipwise.com> > Im Auftrag von Hohl Matthias
Gesendet: Montag, 26. August 2019 18:21
An: 'Rene Krenn' <rkrenn at sipwise.com <mailto:rkrenn at sipwise.com> >; 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Betreff: Re: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Hello Rene,

yah… i think last time the script was ruinning was before the upgrade from 4.5.5 to 5.5.2 on January 2018. There was my last backup.

Hmm ok I will try this.. thanks.

Von: Rene Krenn <rkrenn at sipwise.com <mailto:rkrenn at sipwise.com> > 
Gesendet: Montag, 26. August 2019 18:15
An: 'Hohl Matthias' <matthias.hohl at telematica.at <mailto:matthias.hohl at telematica.at> >; 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Betreff: AW: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Seems your cleanup was not running for long time, not just since the upgrade.

Move out all cdrs (and accompanying tables that have a cdr_start_time column) older than 24 months into separate tables.

1.create table accounting.xy_old like accounting.xy;

2.insert into accounting.xy_old select from accounting.xy where start_time < unix_timestamp(now() - interval ..

3.delete from accounting.xy where start_time < unix_timestamp(now() - interval ..

br

Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von Hohl Matthias
Gesendet: Montag, 26. August 2019 17:42
An: 'Spce-user' <Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> >
Betreff: [Spce-user] Acc Cleanup - Cannot Update partitions for table=cdr the gap is too large

Hello,

After fixing the cleanup script manually (thanks to the hint from Henk Plessius) I have a new error now:

root at spce:~# /usr/sbin/acc-cleanup.pl

ERROR error: Cannot update partitions for table=cdr,the gap between min/max record is too large (more than 24 months). at /usr/share/perl5/NGCP/Cleanup.pm line 361.

How can I solve this?

Mit freundlichen Grüßen,
Matthias Hohl

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20190831/e5df1e37/attachment-0002.html>