Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Fri Mar 29, 2024 12:38 am
Auto Dialer Software


All times are UTC




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 5:16 am 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
Hi,

I just updated my installation of A2Billing 1.4 Beta from the trunk. The revision I'm currently using is 2091.

I now have two problems:

1. When I try to edit a customer, I get what appears to be a new customer form instead of a form with my current customer data.

2. When I try to place a call through A2Billing, it gets rejected due to failed authentication even though I can see that the account code that I am using on an extension is correct in the asterisk CLI with debugging turned on. See below

a2billing.php|1: [agi_priority] => 1
a2billing.php|1: [agi_enhanced] => 0.0
a2billing.php|1: [agi_accountcode] => 3951911692
a2billing.php|1: )
a2billing.php|1: ::> 4
a2billing.php|1: file:Class.A2Billing.php - line:692 - uniqueid:1244177314.1330 - get_agi_request_parameter = 405 ; SIP/405-b740c8e8 ; 1244177314.1330 ; 3951911692 ; 5197485220 ::> 3
a2billing.php|1: file:a2billing.php - line:145 - uniqueid:1244177314.1330 - [NO ANSWER CALL] ::> 3
a2billing.php|1: file:Class.A2Billing.php - line:2166 - uniqueid:1244177314.1330 - - Account code - 3951911692 ::> 4
a2billing.php|1: file:Class.A2Billing.php - line:2185 - uniqueid:1244177314.1330 - PREPAID-AUTH-FAIL ::> 4
-- Playing 'prepaid-auth-fail' (escape_digits=#) (sample_offset 0)
a2billing.php|1: file:a2billing.php - line:157 - uniqueid:1244177314.1330 - [TRY : callingcard_ivr_authenticate] ::> 4
a2billing.php|1: file:a2billing.php - line:320 - uniqueid:1244177314.1330 - [AUTHENTICATION FAILED (cia_res:-2)] ::> 2
-- AGI Script a2billing.php completed, returning 0


My suspicions are that I have an issue with the mya2billing database and that I need to update it now that I have a newer revision. If someone thinks otherwise or has had the same problem and solved it without updating the database, please let me know.

My question is can I use the sql file UPDATE-a2billing-v1.4.0-to-v1.4.1.sql which is under trunk/Database/mysql-5x to do the update? If so, can I use the following command:

mysql -u root -p"root password" < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

What are the risks in doing this? will I clobber my existing data or is this what this file and command are meant to do (ie. allow updating of the existing database when newer revisions become available)

Any guidance would be appreciated.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 12:32 pm 
Offline

Joined: Tue Mar 17, 2009 4:00 pm
Posts: 153
Location: Where the sun shines
Hi

something similara happened to me yesterday. I hadnt run the UPDATE 1.4.0 to 1.4.1 script - after loading the db schema, this small script is required.

J.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 3:12 pm 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
cerlen,

Thanks for the response. It looks like I should do the Database update.

I have a question on that. Can you or anybody else tell me whether I should run the update as the MySQL root user or as the A2Billing user? That is:

# mysql -u root -p"root password" < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

or

# mysql mya2billing -u a2billinguser -ppassword < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

The second option seems to be safer to me, but does the update have to done by the MySQL root user?

Dave


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 3:14 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
I believe the v1.4.0-v1.4.1 upgrade file actually contains changes since the _RC1 release (so a better name would be v1.4.0_RC1-v1.4.0).

If you've upgraded from the BETA (rather than RC1) release then you'll need to do much more work than just applying the 1.4.0-1.4.1 upgrade. Unless you're comfortable writing SQL statements I would suggest it would be much less effort to re-install the SQL schema afresh. Hopefully, from this point on, the SQL schema will be updated in such a way as to remove this difficulty in upgrading.

Edited to add:
I wish I'd managed to post a few moments earlier. How many errors messages did the v1.4.0-v1.4.1 upgrade give you?
Quote:
The second option seems to be safer to me, but does the update have to done by the MySQL root user?
On MySQL only root can create triggers; A2B makes use of triggers, so it's best to install/upgrade the A2B schema as root. On Postgres it's fine to upgrade as the regular A2B user.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 3:17 pm 
Offline

Joined: Tue Mar 17, 2009 4:00 pm
Posts: 153
Location: Where the sun shines
stavros comment is right...

the way to get a proper db running is to download latest svn, run the install_db.sh, then run the update 1.4.0 1.4.1 script.

I run it as "mysql mya2billing -u a2billinguser -ppassword < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql" and it works like a charm...

only thing, I had to add a 'IF EXISTS' in the statement
DROP TABLE IF EXISTS cc_subscription_signup ;


otherwise, the script doesnt execute the following statements

j.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 3:35 pm 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
Thanks stavros & cerlen for getting back to me so soon! I am needing to get this fixed quickly to complete install of PBX

stavros, I haven't run the upgrade yet, so I can't comment on the error messages.

From your replys, my understanding is that I should run the install_db.sh script as root and then run the database update as the mysql root user as well. That is

# mysql -u root -p"root password" < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

cerlen, I will check out and make the correction you suggest to the UPDATE-a2billing-v1.4.0-to-v1.4.1.sql script.

Thanks,

Dave


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 5:01 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
dbenwit wrote:
From your replys, my understanding is that I should run the install_db.sh script as root and then run the database update as the mysql root user as well.
Yes, for a fresh install that is what's necessary. As you have already installed it, first you'll need to drop and recreate the A2B database. Sadly there is no easy way to upgrade from 1.4.0BETA to 1.4.0_RC1.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 5:39 pm 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
stavros,

Thanks for the heads-up.

So this means that I will lose all my data currently in A2Billing ie. rates, trunks, customer etc. Well if that's what it takes then that's fine. The only issue is will this fix my problems mentioned in an earlier post. Am I to understand that I have been caught in the movement from the Beta version to RC1 version in the trunk? I have A2Billing 1.4 Beta working properly on another box and I had done an update only a week or so ago and didn't need to do a database upgrade. Does this mean that before I checkout any more revisions from the trunk that I should upgrade the database from 1.4.0Beta to 1.4.0RC1?

OK, I am going to go ahead and do this. One more question if you don't mind as my SQL skills are not very advanced. I should run the following commands:

# mysqladmin -u root -p"root password" drop mya2billing
# install_db.sh (in the proper directory)
# mysql -u root -p"root password" < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

Let me know

And again, thanks for all your help
Dave


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Fri Jun 05, 2009 6:08 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
dbenwit wrote:
The only issue is will this fix my problems mentioned in an earlier post.
I can't promise it will, but it's likely. The database schema and the code must be kept in synch with one another.
Quote:
Am I to understand that I have been caught in the movement from the Beta version to RC1 version in the trunk?
Yes, that's pretty much it. It was an avoidable situation, but it would have been more work for the developers. Hopefully now more folks are using v1.4 an easy upgrade path will be maintained.
Quote:
I have A2Billing 1.4 Beta working properly on another box and I had done an update only a week or so ago and didn't need to do a database upgrade.
You did need to. Running a different schema than the code is designed for will cause problems.
Quote:
Does this mean that before I checkout any more revisions from the trunk that I should upgrade the database from 1.4.0Beta to 1.4.0RC1?
Yes, but we're back to my previous answer: doing this is non-trivial and will require you to rewrite some SQL statements from CREATEs/INSERTs to ALTERs/UPDATEs.

Quote:
# mysqladmin -u root -p"root password" drop mya2billing
# install_db.sh (in the proper directory)
# mysql -u root -p"root password" < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql
After DROPping the database you'll need to CREATE it again.


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Sun Jun 07, 2009 5:17 pm 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
Ok

I went through the entire process of recreating the database, and doing the update from 1.4BETA to 1.4RC1. as discussed in this topic. I included the step to recreate the database after dropping it. I did change the command to do the update to

# mysql -u root -p"root password" mya2billing < UPDATE-a2billing-v1.4.0-to-v1.4.1.sql

to apply the changes to the proper database as I got an error 1046: no database selected with the original command.

I re-populated the database with all the data through the web interface. Everything seemed to go fine including the ability to edit a customer after creating one which was encouraging. I entered a couple of rates to allow test calling and this is where I ran into problems.

After adding the rates, I used the customer interface and the simulator to check that I could call a number that I was going to test. However, I could not get the simulator to check the number. I got the response, "The number you have entered is not correct". Of course, when I tried a test call, I got a message that the call was unreachable and the following output from the astersik CLI:

Executing [s@macro-dialout-trunk:16] Macro("SIP/405-b7417c50", "dialout-trunk-predial-hook|") in new stack
-- Executing [s@macro-dialout-trunk-predial-hook:1] GotoIf("SIP/405-b7417c50", "1?custom-freepbx-a2billing|5197485220|1:2") in new stack
-- Goto (custom-freepbx-a2billing,5197485220,1)
== Channel 'SIP/405-b7417c50' jumping out of macro 'dialout-trunk-predial-hook'
== Channel 'SIP/405-b7417c50' jumping out of macro 'dialout-trunk'
-- Executing [5197485220@custom-freepbx-a2billing:1] DeadAGI("SIP/405-b7417c50", "a2billing.php|1") in new stack
-- Launched AGI Script /var/lib/asterisk/agi-bin/a2billing.php
a2billing.php|1: file:a2billing.php - line:84 - uniqueid: - IDCONFIG : 1 ::> 3
a2billing.php|1: file:a2billing.php - line:85 - uniqueid: - MODE : standard ::> 3
a2billing.php|1: file:Class.A2Billing.php - line:692 - uniqueid:1244394266.1565 - get_agi_request_parameter = 405 ; SIP/405-b7417c50 ; 1244394266.1565 ; 6701783487 ; 5197485220 ::> 3
a2billing.php|1: file:a2billing.php - line:145 - uniqueid:1244394266.1565 - [NO ANSWER CALL] ::> 3
a2billing.php|1: file:a2billing.php - line:172 - uniqueid:1244394266.1565 - [CHANNEL STATUS : 4 = Line is ringing]
a2billing.php|1: [CREDIT : 20000][CREDIT MIN_CREDIT_2CALL : 0] ::> 3
a2billing.php|1: file:a2billing.php - line:201 - uniqueid:1244394266.1565 - TARIFF ID -> 1 ::> 3
a2billing.php|1: file:Class.A2Billing.php - line:843 - uniqueid:1244394266.1565 - DESTINATION ::> 5197485220 ::> 3
a2billing.php|1: file:Class.A2Billing.php - line:966 - uniqueid:1244394266.1565 - ERROR ::> RateEngine didnt succeed to match the dialed number over the ratecard (Please check : id the ratecard is well create ; if the removeInter_Prefix is set according to your prefix in the ratecard ; if you hooked the ratecard to the Call Plan) ::> 1
-- Playing 'prepaid-dest-unreachable' (escape_digits=#) (sample_offset 0)
-- AGI Script a2billing.php completed, returning 0

Now, if I go into the A2Billing admin interface, I do not see any rates created and if I try to create one, I can't.

Have you seen this problem before?

Dave


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Sun Jun 07, 2009 6:22 pm 
Offline

Joined: Sun Mar 29, 2009 9:53 pm
Posts: 25
I must apologize. I jumped the gun on the last post.

When I logged out of the A2Billing admin interface and then loged back in. The rates that I had created previously were there again under Rates. However, when I searched for rates under my call plan, there were none. A quick investigation discovered that I had not assigned my Rate card to the call plan. Once I did that, I was able to successfully make calls.

Thus, the process of re-creating the database and re-entering all my info into A2Billing through admin interface has solved my problem.

Again, thanks to stavros and to cerlen for all their help.

Dave


Top
 Profile  
 
 Post subject: Re: Updating MySQL Database
PostPosted: Sun Jun 07, 2009 9:44 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
8)
Excellent news. I'm glad you've got it working satisfactorily now.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 
Predictive Dialer


All times are UTC


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group