<div dir="ltr"><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">---------- Forwarded message ---------<br>From: <strong class="gmail_sendername" dir="auto">Andy Clark</strong> <span dir="auto"><<a href="mailto:andyclark05251978@gmail.com">andyclark05251978@gmail.com</a>></span><br>Date: Thu, Jun 13, 2019 at 5:36 AM<br>Subject: Re: [Spce-user] after db restore - DB provisioning access denied<br>To: Alex Lutay <<a href="mailto:alutay@sipwise.com">alutay@sipwise.com</a>><br></div><br><br><div dir="ltr"><div>Hi Alex,</div><div>Still getting the same error after</div><div><br></div><div>ngcp-sync-constants -root<br></div><div>ngcp-sync-constants -verbose -root<br></div><div>ngcpcfg apply<br></div><div><br></div><div>below are the details</div><div><br></div>---------- backup version 6.5.4 using
----------<div>1. ngcp-update</div><div>2. ngcpcfg apply<br><div>3. mysqldump --all-databases --single-transaction --quick --lock-tables=false > /buckup/full-backup-$(date +%F).sql -u root -p<br></div><div>4. tar -zcvf etc_mysql_sipwise.tar.gz /etc/mysql/sipwise.cnf<br></div><div><br></div><div>---------- restore to new server version 6.5.4 ----------</div></div><div>1. copied full-backup-2019-06-12.sql from old server to new server</div><div>2. mysql -u root -p < /backup/full-backup-2019-06-12.sql<br></div><div>3. vim /etc/mysql/sipwise.cnf<br></div><div> # SIPWISE_DB_PASSWORD='XXXXXXXXXXXXXXXXX' existing<br> SIPWISE_DB_PASSWORD='YYYYYYYYYYYYYYYYYYY' # from backup server<br></div><div>4. ngcpcfg apply<br></div><div>---------- --------------------------------------------------</div><div><br></div><div> </div><div>--------------------then got this error message--------------------</div><div><br></div><div>DBI connect('database=provisioning;host=localhost;port=3306','sipwise',...) failed: Access denied for user 'sipwise'@'localhost' (using password: YES) at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.<br> Error: Could not connect to database 'provisioning' at 'localhost:3306' as 'sipwise': Access denied for user 'sipwise'@'localhost' (using password: YES)<br></div><div><br></div><div>----------this morning it tried as you suggested----------</div><div>ngcp-sync-grants --verbose --recreate-user<br>ngcp-sync-constants -verbose -root<br></div><div>ngcpcfg apply <br></div><div><br></div><div><br></div><div><br></div><div>----------after running----------</div><div><br></div><div>ngcp-sync-grants --verbose --recreate-user<br>--> connected to localhost:3306 as sipwise<br>--> host: spce(self)<br>--> drop: asterisk@localhost<br>--> flush privileges<br>--> [defaults.asterisk.localhost]<br>--> flush privileges<br>--> revoke all from: asterisk@localhost<br>--> grant all privileges on kamailio.voicemail_users to asterisk@localhost<br>--> grant select on billing.v_subscriber_timezone to asterisk@localhost<br>--> grant all privileges on kamailio.voicemail_spool to asterisk@localhost<br>--> grant select on provisioning.voip_dbaliases to asterisk@localhost<br>--> grant select on provisioning.voip_subscribers to asterisk@localhost<br>--> drop: dbcleaner@localhost<br>--> flush privileges<br>--> [defaults.dbcleaner.localhost]<br>--> flush privileges<br>--> revoke all from: dbcleaner@localhost<br>--> grant all privileges on accounting.* to dbcleaner@localhost<br>--> grant all privileges on kamailio.* to dbcleaner@localhost<br>--> drop: debian-sys-maint@localhost<br>--> flush privileges<br>--> [defaults.debian-sys-maint.localhost]<br>--> flush privileges<br>--> revoke all from: debian-sys-maint@localhost<br>--> grant all privileges on *.* to debian-sys-maint@localhost<br>--> drop: exporter@localhost<br>--> flush privileges<br>--> [defaults.exporter.localhost]<br>--> flush privileges<br>--> revoke all from: exporter@localhost<br>--> grant select on billing.* to exporter@localhost<br>--> grant select on provisioning.* to exporter@localhost<br>--> grant select on accounting.events_tag_data to exporter@localhost<br>--> grant select on accounting.cdr_provider to exporter@localhost<br>--> grant select on accounting.cdr_cash_balance to exporter@localhost<br>--> grant select,update on accounting.events to exporter@localhost<br>--> grant select,update on accounting.cdr to exporter@localhost<br>--> grant select on accounting.cdr_relation to exporter@localhost<br>--> grant select on billing.billing_zones_history to exporter@localhost<br>--> grant select on accounting.cdr_cash_balance_data to exporter@localhost<br>--> grant select on accounting.events_tag to exporter@localhost<br>--> grant select on accounting.cdr_relation_data to exporter@localhost<br>--> grant select on accounting.cdr_export_status to exporter@localhost<br>--> grant select on accounting.cdr_tag to exporter@localhost<br>--> grant select on accounting.cdr_tag_data to exporter@localhost<br>--> grant select on billing.resellers to exporter@localhost<br>--> grant select,insert,update on accounting.mark to exporter@localhost<br>--> grant select,insert,update on accounting.cdr_export_status_data to exporter@localhost<br>--> grant select on billing.voip_subscribers to exporter@localhost<br>--> grant select on accounting.cdr_direction to exporter@localhost<br>--> grant select on accounting.cdr_time_balance to exporter@localhost<br>--> grant select on accounting.events_relation to exporter@localhost<br>--> grant select on accounting.events_relation_data to exporter@localhost<br>--> grant select on ngcp.timezone to exporter@localhost<br>--> grant select on accounting.cdr_time_balance_data to exporter@localhost<br>--> drop: fileshare@localhost<br>--> flush privileges<br>--> [defaults.fileshare.localhost]<br>--> flush privileges<br>--> revoke all from: fileshare@localhost<br>--> grant all privileges on fileshare.* to fileshare@localhost<br>--> grant select on provisioning.voip_domains to fileshare@localhost<br>--> grant select on provisioning.voip_subscribers to fileshare@localhost<br>--> drop: kamailio@localhost<br>--> flush privileges<br>--> [defaults.kamailio.localhost]<br>--> flush privileges<br>--> revoke all from: kamailio@localhost<br>--> grant all privileges on kamailio.* to kamailio@localhost<br>--> grant select on billing.ncos_lnp_list to kamailio@localhost<br>--> grant select on provisioning.voip_sound_files to kamailio@localhost<br>--> grant select on provisioning.voip_cf_periods to kamailio@localhost<br>--> grant select on provisioning.voip_cf_destinations to kamailio@localhost<br>--> grant select on billing.ncos_pattern_list to kamailio@localhost<br>--> grant select on provisioning.voip_fax_preferences to kamailio@localhost<br>--> grant select on provisioning.voip_cf_bnumbers to kamailio@localhost<br>--> grant select on provisioning.voip_contract_location_blocks to kamailio@localhost<br>--> grant select on provisioning.voip_cf_mappings to kamailio@localhost<br>--> grant select on provisioning.voip_dbaliases to kamailio@localhost<br>--> grant select on provisioning.voip_cf_bnumber_sets to kamailio@localhost<br>--> grant select on provisioning.voip_contract_locations to kamailio@localhost<br>--> grant select on provisioning.voip_cf_destination_sets to kamailio@localhost<br>--> grant select on billing.lnp_numbers to kamailio@localhost<br>--> grant select on provisioning.voip_sound_sets to kamailio@localhost<br>--> grant select on provisioning.voip_peer_hosts to kamailio@localhost<br>--> grant select on provisioning.voip_cf_time_sets to kamailio@localhost<br>--> grant select on provisioning.voip_sound_handles to kamailio@localhost<br>--> grant select on provisioning.voip_cf_source_sets to kamailio@localhost<br>--> grant select on billing.ncos_levels to kamailio@localhost<br>--> grant select on provisioning.voip_cf_sources to kamailio@localhost<br>--> grant select on provisioning.voip_subscribers to kamailio@localhost<br>--> drop: kamailioro@localhost<br>--> flush privileges<br>--> [defaults.kamailioro.localhost]<br>--> flush privileges<br>--> revoke all from: kamailioro@localhost<br>--> grant select on kamailio.* to kamailioro@localhost<br>--> grant select on billing.ncos_lnp_list to kamailioro@localhost<br>--> grant select on provisioning.voip_peer_inbound_rules to kamailioro@localhost<br>--> grant select on provisioning.voip_peer_groups to kamailioro@localhost<br>--> grant select on provisioning.voip_domains to kamailioro@localhost<br>--> grant select on provisioning.voip_sound_files to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_periods to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_destinations to kamailioro@localhost<br>--> grant select on provisioning.emergency_mappings to kamailioro@localhost<br>--> grant select on billing.v_subscriber_timezone to kamailioro@localhost<br>--> grant select on billing.ncos_pattern_list to kamailioro@localhost<br>--> grant select on billing.lnp_providers to kamailioro@localhost<br>--> grant select on billing.billing_profiles to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_bnumbers to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_mappings to kamailioro@localhost<br>--> grant select on billing.voip_subscribers to kamailioro@localhost<br>--> grant select on provisioning.voip_dbaliases to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_bnumber_sets to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_destination_sets to kamailioro@localhost<br>--> grant select on billing.billing_fees to kamailioro@localhost<br>--> grant select on billing.lnp_numbers to kamailioro@localhost<br>--> grant select on provisioning.voip_sound_sets to kamailioro@localhost<br>--> grant select on provisioning.voip_peer_hosts to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_time_sets to kamailioro@localhost<br>--> grant select on provisioning.voip_sound_handles to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_source_sets to kamailioro@localhost<br>--> grant select on provisioning.voip_cf_sources to kamailioro@localhost<br>--> grant select on billing.ncos_levels to kamailioro@localhost<br>--> grant select on provisioning.voip_subscribers to kamailioro@localhost<br>--> grant execute on function billing.get_billing_profile_by_uuid_network to kamailioro@localhost<br>--> grant execute on function billing.get_billing_fee to kamailioro@localhost<br>--> drop: ldap@localhost<br>--> flush privileges<br>--> [defaults.ldap.localhost]<br>--> flush privileges<br>--> revoke all from: ldap@localhost<br>--> grant select on ldap.* to ldap@localhost<br>--> grant select on billing.contracts to ldap@localhost<br>--> grant select on provisioning.voip_preferences to ldap@localhost<br>--> grant select on provisioning.voip_usr_preferences to ldap@localhost<br>--> grant select on provisioning.voip_subscribers to ldap@localhost<br>--> drop: licensed@localhost<br>--> flush privileges<br>--> [defaults.licensed.localhost]<br>--> flush privileges<br>--> revoke all from: licensed@localhost<br>--> grant select on billing.products to licensed@localhost<br>--> grant select on billing.contracts to licensed@localhost<br>--> grant select on billing.voip_subscribers to licensed@localhost<br>--> grant select on kamailio.location to licensed@localhost<br>--> grant select on billing.billing_mappings to licensed@localhost<br>--> grant select on provisioning.voip_subscribers to licensed@localhost<br>--> drop: mediator@localhost<br>--> flush privileges<br>--> [defaults.mediator.localhost]<br>--> flush privileges<br>--> revoke all from: mediator@localhost<br>--> grant all privileges on accounting.* to mediator@localhost<br>--> grant select on provisioning.* to mediator@localhost<br>--> grant select on billing.* to mediator@localhost<br>--> grant select,insert,update on stats.* to mediator@localhost<br>--> drop: nagios@localhost<br>--> drop: nagios@sp1<br>--> drop: nagios@sp2<br>--> flush privileges<br>--> [defaults.nagios.localhost]<br>--> flush privileges<br>--> revoke all from: nagios@localhost<br>--> grant reload,process,super,replication slave,replication client on *.* to nagios@localhost<br>--> grant select on sipstats.* to nagios@localhost<br>--> grant select on mysql.* to nagios@localhost<br>--> grant select on stats.* to nagios@localhost<br>--> grant select on provisioning.voip_peer_groups to nagios@localhost<br>--> grant select on kamailio.dialog to nagios@localhost<br>--> grant select on billing.voip_subscribers to nagios@localhost<br>--> grant select on kamailio.location to nagios@localhost<br>--> grant select on provisioning.voip_peer_hosts to nagios@localhost<br>--> grant select on provisioning.voip_subscribers to nagios@localhost<br>--> drop: prosody@localhost<br>--> flush privileges<br>--> [defaults.prosody.localhost]<br>--> flush privileges<br>--> revoke all from: prosody@localhost<br>--> grant all privileges on prosody.* to prosody@localhost<br>--> grant select on provisioning.voip_domains to prosody@localhost<br>--> grant select on provisioning.voip_pbx_groups to prosody@localhost<br>--> grant select on billing.contracts to prosody@localhost<br>--> grant select on billing.contacts to prosody@localhost<br>--> grant select on kamailio.subscriber to prosody@localhost<br>--> grant select on kamailio.domain to prosody@localhost<br>--> grant select on billing.voip_subscribers to prosody@localhost<br>--> grant select on provisioning.voip_dbaliases to prosody@localhost<br>--> grant select on provisioning.voip_preferences to prosody@localhost<br>--> grant select on provisioning.voip_rewrite_rule_sets to prosody@localhost<br>--> grant select on provisioning.voip_rewrite_rules to prosody@localhost<br>--> grant select on provisioning.voip_usr_preferences to prosody@localhost<br>--> grant select on billing.domains to prosody@localhost<br>--> grant select on kamailio.dbaliases to prosody@localhost<br>--> grant select on provisioning.voip_subscribers to prosody@localhost<br>--> grant select on provisioning.voip_dom_preferences to prosody@localhost<br>--> drop: rateomat@localhost<br>--> flush privileges<br>--> [defaults.rateomat.localhost]<br>--> flush privileges<br>--> revoke all from: rateomat@localhost<br>--> grant select,insert,update on accounting.* to rateomat@localhost<br>--> grant select,insert,update,execute on billing.* to rateomat@localhost<br>--> grant select,update on stats.* to rateomat@localhost<br>--> grant select on ngcp.date_range_helper to rateomat@localhost<br>--> grant delete on accounting.prepaid_costs to rateomat@localhost<br>--> grant select on provisioning.voip_preferences to rateomat@localhost<br>--> grant select,insert,update,delete on provisioning.voip_usr_preferences to rateomat@localhost<br>--> grant select on provisioning.voip_subscribers to rateomat@localhost<br>--> drop: rsyslog@localhost<br>--> flush privileges<br>--> [defaults.rsyslog.localhost]<br>--> flush privileges<br>--> revoke all from: rsyslog@localhost<br>--> grant reload on *.* to rsyslog@localhost<br>--> grant all privileges on syslog.* to rsyslog@localhost<br>--> drop: rtcengine@localhost<br>--> flush privileges<br>--> [defaults.rtcengine.localhost]<br>--> flush privileges<br>--> revoke all from: rtcengine@localhost<br>--> grant all privileges on rtcengine.* to rtcengine@localhost<br>--> drop: rtpengine@localhost<br>--> flush privileges<br>--> [defaults.rtpengine.localhost]<br>--> flush privileges<br>--> revoke all from: rtpengine@localhost<br>--> grant all privileges on provisioning.recording_metakeys to rtpengine@localhost<br>--> grant all privileges on provisioning.recording_streams to rtpengine@localhost<br>--> grant all privileges on provisioning.recording_calls to rtpengine@localhost<br>--> drop: sems_prepaid@localhost<br>--> flush privileges<br>--> [defaults.sems_prepaid.localhost]<br>--> flush privileges<br>--> revoke all from: sems_prepaid@localhost<br>--> grant select,insert,update,execute on billing.* to sems_prepaid@localhost<br>--> grant select on provisioning.voip_peer_groups to sems_prepaid@localhost<br>--> grant select on provisioning.voip_domains to sems_prepaid@localhost<br>--> grant select on ngcp.date_range_helper to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_reminder to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_cf_destinations to sems_prepaid@localhost<br>--> grant select on provisioning.voip_subscriber_profile_attributes to sems_prepaid@localhost<br>--> grant select,insert,update,delete on accounting.malicious_calls to sems_prepaid@localhost<br>--> grant select,insert,update on accounting.prepaid_costs to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_speed_dial to sems_prepaid@localhost<br>--> grant select on provisioning.voip_peer_preferences to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_cf_mappings to sems_prepaid@localhost<br>--> grant select on provisioning.voip_pbx_autoattendants to sems_prepaid@localhost<br>--> grant select,insert,update,delete on kamailio.sems_registrations to sems_prepaid@localhost<br>--> grant select on kamailio.location to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_cf_destination_sets to sems_prepaid@localhost<br>--> grant select on provisioning.voip_preferences to sems_prepaid@localhost<br>--> grant select on provisioning.voip_rewrite_rule_sets to sems_prepaid@localhost<br>--> grant select on provisioning.voip_peer_hosts to sems_prepaid@localhost<br>--> grant select on provisioning.voip_rewrite_rules to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_dom_preferences to sems_prepaid@localhost<br>--> grant select,insert,update,delete on provisioning.voip_usr_preferences to sems_prepaid@localhost<br>--> grant select on provisioning.voip_sound_handles to sems_prepaid@localhost<br>--> grant select on provisioning.voip_sound_files to sems_prepaid@localhost<br>--> grant select on provisioning.voip_cc_mappings to sems_prepaid@localhost<br>--> grant select on kamailio.dbaliases to sems_prepaid@localhost<br>--> grant select on provisioning.voip_subscribers to sems_prepaid@localhost<br>--> drop: sipwise@localhost<br>--> flush privileges<br>--> [defaults.sipwise.localhost]<br>--> flush privileges<br>--> revoke all from: sipwise@localhost<br>--> grant all privileges on *.* to sipwise@localhost with grant option<br>--> drop: soap@localhost<br>--> flush privileges<br>--> [defaults.soap.localhost]<br>--> flush privileges<br>--> revoke all from: soap@localhost<br>--> grant all privileges on billing.* to soap@localhost<br>--> grant all privileges on kamailio.* to soap@localhost<br>--> grant all privileges on sipstats.* to soap@localhost<br>--> grant all privileges on accounting.* to soap@localhost<br>--> grant all privileges on carrier.* to soap@localhost<br>--> grant all privileges on provisioning.* to soap@localhost<br>--> grant all privileges on prosody.* to soap@localhost<br>--> drop: voisniff@localhost<br>--> flush privileges<br>--> [defaults.voisniff.localhost]<br>--> flush privileges<br>--> revoke all from: voisniff@localhost<br>--> grant all privileges on sipstats.* to voisniff@localhost<br>--> grant select on billing.voip_intercept to voisniff@localhost<br>--> flush privileges<br>--> Warning: Recreated users are without passwords,<br> please consider running 'ngcp-sync-constants' to update passwords for them.<br><br></div><div>root@edge:~# ngcp-sync-constants -verbose -root<br>--> nodename => spce<br>--> connected to localhost:3306 as root<br>--> soap => aaaaaaaaaaaaa<br>--> rateomat => bbbbbbbbbbb<br>--> fileshare => cccccccccccccc<br>--> asterisk => dddddddddddddd<br>--> licensed => eeeeeeeeeeeeee<br>--> rsyslog => fffffffffffffffffff<br>--> prosody => ggggggggggggggg<br>--> exporter => hhhhhhhhhhhh<br>--> rtpengine => iiiiiiiiiiiiiiiii<br>--> nagios => jjjjjjjjjjjjjjjjjjjjjj<br>--> mediator => kkkkkkkkkkkkkkkkkkkk<br>--> voisniff => lllllllllllllllllllll<br>--> rtcengine => mmmmmmmmmmmmmmm<br>--> ldap => nnnnnnnnnnnnnn<br>--> dbcleaner => oooooooooooooooooo<br>--> kamailio => pppppppppppppppp<br>--> kamailioro => qqqqqqqqqqqqqqqqqqqq<br>--> sems_prepaid => rrrrrrrrrrrrrrrrrrrrrrrrrrrrr<br>--> debian-sys-maint => sssssssssssssssssssss<br>--> sipwise => tttttttttttttttttttttttttttt<br>--> flush priveleges<br></div><div><br></div><div> ngcpcfg apply<br></div><div><br></div><div>2019-06-13 04:51:34 spce: Executing sync-db:<br> DBI connect('database=provisioning;host=localhost;port=3306','sipwise',...) failed: Access denied for user 'sipwise'@'localhost' (using password: YES) at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.<br> Error: Could not connect to database 'provisioning' at 'localhost:3306' as 'sipwise': Access denied for user 'sipwise'@'localhost' (using password: YES)<br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jun 13, 2019 at 3:34 AM Alex Lutay <<a href="mailto:alutay@sipwise.com" target="_blank">alutay@sipwise.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
It is hard to say something from this side.<br>
Maybe 'mysql.user' table was not backuped/restored and<br>
there is no proper user 'sipwise' inside?<br>
Maybe you missed "flush hosts; flush privileges"?<br>
<br>
You can share your exact commands to backup/restore procedures,<br>
I will try this on my side. BTW did you use our manuals?<br>
Which release do you use?<br>
<br>
Did it happen once or you still see this error on 'ngcpcfg apply'?<br>
If so, consider to execute:<br>
> ngcp-sync-grants --verbose --recreate-user<br>
> ngcp-sync-constants -verbose -root<br>
<br>
It should recreate all the necessary sipwise users and sync their<br>
passwords with constants.yml<br>
<br>
I hope it will help you.<br>
Cannot trace it further from here.<br>
<br>
Have fun!<br>
<br>
On 6/12/19 5:52 PM, Andy Clark wrote:<br>
> 2019-06-12 08:42:25 spce: /var has no support of .services<br>
> 2019-06-12 08:42:25 spce: Executing sync-db:<br>
> DBI<br>
> connect('database=provisioning;host=localhost;port=3306','sipwise',...)<br>
> failed: Access denied for user 'sipwise'@'localhost' (using password:<br>
> YES) at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.<br>
> Error: Could not connect to database 'provisioning'<br>
> at 'localhost:3306' as 'sipwise': Access denied for user<br>
> 'sipwise'@'localhost' (using password: YES)<br>
> 2019-06-12 08:42:25 spce: OK: nothing to commit.<br>
> 2019-06-12 08:42:25 spce: Synchronizing MySQL grants/credentials<br>
> <br>
> <br>
> restored database (full restore except for accounting.cdr)<br>
> restored /etc/ngcp-config<br>
> restored /etc/mysql/sipwise.conf<br>
<br>
<br>
-- <br>
Alex Lutay<br>
_______________________________________________<br>
Spce-user mailing list<br>
<a href="mailto:Spce-user@lists.sipwise.com" target="_blank">Spce-user@lists.sipwise.com</a><br>
<a href="https://lists.sipwise.com/listinfo/spce-user" rel="noreferrer" target="_blank">https://lists.sipwise.com/listinfo/spce-user</a><br>
</blockquote></div>
</div></div>