cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
3695
Views
5
Helpful
11
Replies

SCE 2000 / CM / MySQL - Policy string translations in CM DB not found

dcamarotto
Level 1
Level 1

I need more space to store reports that the CM generates. Then I am changing Sybase database that is bundle to the CM and placing the MySql. Everything has  been configured as the Cisco documentation indicates. I understand that the  communication with the database is ok   because all the tables were created in  MySQL, but, when I generate a report with the SCA BB Reporter the error message  appears saying that   still lack apply some policy.

SCABB_Reporter.png

error message SCABB_Reporter.png

I check in MySQL logs errors and notthing is found, but all tables is create by CM, but its totally empty.

I check Mysql LOG operations, I see what data is input in database, but in the   final, it place a command FALLBACK. I belive this a test, not   production.

Check out below.

MySQL Workbech.png

This is a privileges to pqp_admin in MySQL.

privilegies on MySQL.png

     Everybody have any idea how I fix this problem?

11 Replies 11

mmiletic
Cisco Employee
Cisco Employee

Hi there,

I would suggest you first to apply the pqb config, to make sure in console in SCA BB you see that your CM was updated sucessfully.

After that close SCA BB, reopen it, try to connect to the DB.

(you should see in your My SQL apricot DB table value_ini and you should see your SCE IP there)

Also you can find value.ini file on your PC where SCA BB is installed, delete that file, close/open SCA BB, and check if you can connect to the DB).

Let me know if this works.

Thanks,

Minja

Hi Minja,

             I make all procedures what you ask me and same problem ocurr

  1. a) I would suggest you first to apply the pqb config, to make sure in  console in SCA BB you see that your CM was updated sucessfully. b) After that close SCA BB, reopen it, try to connect to the DB.(you should see in your My SQL apricot DB table value_ini and you should see your SCE IP there)

R: a) New PQB file create and apply in SCEs. b) Not works. c) I belive what correct key is "ini_value" and not "value_ini". This is a possible problem, diferent key name?

            2. Also you can find value.ini file on your PC where SCA BB is installed,  delete that file, close/open SCA BB, and check if you can connect to the  DB).

R: A change name file, but either not works.

Hello again,

you are right about table name. it was a typo. for your reference here you can find all table names:

http://www.cisco.com/en/US/docs/cable/serv_exch/serv_control/broadband_app/rel38x/scabbrg/04_SCA_BB_RG.html#wp1001308

I do not see that you have any data in INI_VALUES. Is that the case?

If so, that is probably the reaosn for REPORTER to fail.

So for now let's check that one. Can you find SCE IP in INI_VALUES or not?

Also:

1) Check on SCE CLI if you have RDR-formatter configured pointing to your CM.

2) apply pqb abd verify console logs in SCA BB saying that your CM was updated sucessfully.

3) go on CM and execute script dbconf.sh verifying if connection is up.

you need to make sure that in INI_VALUES you have your SCE IP address populated.

That is the first step.

Thanks,

Minja

Hi,

          Sorry for the delay. Follow below, the informations what ask:

1) I do not see that you have any data in INI_VALUES. Is that the case? If so, that is probably the reaosn for REPORTER to fail.

R:Yes. Is Correct. I´m consult MySQL database and tables are same equal of document what you send me, but is empty.

2) So for now let's check that one. Can you find SCE IP in INI_VALUES or not?

R: All tables is empty in tables but CM connection is alive with MySQL.

3) Check on SCE CLI if you have RDR-formatter configured pointing to your CM.

R:

SCE-1-MTEL#>sh RDR-formatter counters

RDR:    queued:     0 ,sent:  567, thrown:     0, format-mismatch:     0

UM:     queued:     0 ,sent:     0, thrown:     0

Logger: queued:     0 ,sent:     0, thrown:     0

Errors: thrown:     0

Last time these counters were cleared: 16:25:45  BRZ  MON  April  15  2013

SCE-1-MTEL#>

SCE-1-MTEL#>sh RDR-formatter counters

RDR:    queued:     0 ,sent:   731, thrown:     0, format-mismatch:     0

UM:     queued:     0 ,sent:     0, thrown:     0

Logger: queued:     0 ,sent:     0, thrown:     0

Errors: thrown:     0

Last time these counters were cleared: 16:25:45  BRZ  MON  April  15  2013

SCE-1-MTEL#>                        

This information are in CM machine

-bash-3.2$ rdr-rate.sh

4.0

Command terminated successfully

-bash-3.2$ rdr-rate.sh

0.0

Command terminated successfully

4) apply pqb abd verify console logs in SCA BB saying that your CM was updated sucessfully.

+ 4/15/13 2:05:36 PM BRT | INFO  | Connecting to SCE 10.0.29.250 ...

+ 4/15/13 2:05:37 PM BRT | INFO  | Validating password ...

+ 4/15/13 2:05:37 PM BRT | INFO  | Password is valid

+ 4/15/13 2:05:37 PM BRT | INFO  | Retrieving service configuration ...

+ 4/15/13 2:05:39 PM BRT | INFO  | Opening service configuration editor ...

+ 4/15/13 2:05:55 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Connecting to element

+ 4/15/13 2:05:58 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Connecting to element

+ 4/15/13 2:06:02 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Reading SCE platform data ...

+ 4/15/13 2:06:05 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Preparing configuration script for SCE2000 - 4xGBE ...

+ 4/15/13 2:06:06 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Sending configuration to SCE ...

+ 4/15/13 2:06:07 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Executing configuration script on SCE ...

+ 4/15/13 2:06:08 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Reading SCE platform data ...

+ 4/15/13 2:06:11 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Preparing configuration script for SCE2000 - 4xGBE ...

+ 4/15/13 2:06:11 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Sending configuration to SCE ...

+ 4/15/13 2:06:12 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Executing configuration script on SCE ...

+ 4/15/13 2:07:20 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": Updating configuration registry ...

+ 4/15/13 2:07:21 PM BRT | INFO  | Updating CM at 10.0.29.249 with service configuration values ...

+ 4/15/13 2:07:21 PM BRT | INFO  | Applying service configuration at "SCE-1-MTEL [10.0.29.250]": completed successfully

+ 4/15/13 2:07:21 PM BRT | INFO  | Updating the CM at 10.0.29.249 completed.

+ 4/15/13 2:07:24 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": Updating configuration registry ...

+ 4/15/13 2:07:25 PM BRT | INFO  | Updating CM at 10.0.29.249 with service configuration values ...

+ 4/15/13 2:07:25 PM BRT | INFO  | Applying service configuration at "SCE-2-MTEL [10.0.29.251]": completed successfully

+ 4/15/13 2:07:25 PM BRT | INFO  | Updating the CM at 10.0.29.249 completed.

This PQP is applied from Wizard

+ 4/15/13 2:14:32 PM BRT | INFO  | Adding devices to the Network Navigator

+ 4/15/13 2:14:32 PM BRT | INFO  | Connecting to SCE 10.0.29.250

+ 4/15/13 2:14:32 PM BRT | INFO  | Connecting to SCE 10.0.29.251

+ 4/15/13 2:14:32 PM BRT | INFO  | Verifying that the SCE platforms are installed properly

+ 4/15/13 2:14:33 PM BRT | INFO  | Verifying that the CM is installed properly

+ 4/15/13 2:14:37 PM BRT | WARN  | Found anonymous group in SCE 10.0.29.250, not setting a new anonymous group

+ 4/15/13 2:14:42 PM BRT | WARN  | Found anonymous group in SCE 10.0.29.251, not setting a new anonymous group

+ 4/15/13 2:14:42 PM BRT | INFO  | Applying configuration to SCE 10.0.29.250 (1 out of 2 SCE platforms)

+ 4/15/13 2:14:42 PM BRT | INFO  | Reading SCE platform data ...

+ 4/15/13 2:14:45 PM BRT | INFO  | Preparing configuration script for SCE2000 - 4xGBE ...

+ 4/15/13 2:14:45 PM BRT | INFO  | Sending configuration to SCE ...

+ 4/15/13 2:14:46 PM BRT | INFO  | Executing configuration script on SCE ...

+ 4/15/13 2:15:58 PM BRT | INFO  | Updating configuration registry ...

+ 4/15/13 2:15:59 PM BRT | INFO  | Updating CM at 10.0.29.249 with service configuration values ...

+ 4/15/13 2:15:59 PM BRT | INFO  | Applying configuration to SCE 10.0.29.250 completed successfully

+ 4/15/13 2:15:59 PM BRT | INFO  | Updating the CM at 10.0.29.249 completed.

+ 4/15/13 2:15:59 PM BRT | INFO  | Applying configuration to SCE 10.0.29.251 (2 out of 2 SCE platforms)

+ 4/15/13 2:15:59 PM BRT | INFO  | Reading SCE platform data ...

+ 4/15/13 2:16:03 PM BRT | INFO  | Preparing configuration script for SCE2000 - 4xGBE ...

+ 4/15/13 2:16:03 PM BRT | INFO  | Sending configuration to SCE ...

+ 4/15/13 2:16:04 PM BRT | INFO  | Executing configuration script on SCE ...

+ 4/15/13 2:17:17 PM BRT | INFO  | Updating configuration registry ...

+ 4/15/13 2:17:18 PM BRT | INFO  | Updating CM at 10.0.29.249 with service configuration values ...

+ 4/15/13 2:17:18 PM BRT | INFO  | Applying configuration to SCE 10.0.29.251 completed successfully

+ 4/15/13 2:17:18 PM BRT | INFO  | Updating the CM at 10.0.29.249 completed.

+ 4/15/13 2:17:18 PM BRT | INFO  | Connecting the Reporter to the database

+ 4/15/13 2:17:23 PM BRT | ERROR | Verifying that the database is connected and configured correctly:

SCA BB Templates:

  [PASS] A database connection is defined and available.

  [PASS] All mandatory preferences have settings.

  [FAIL]Policy string translations in CM DB not found - apply a policy to create them (table empty).

  [SKIP] Timezone information in CM DB.

+ 4/15/13 2:17:23 PM BRT | ERROR | Configuration failed

5) go on CM and execute script dbconf.sh verifying if connection is up.

-bash-3.2$ dbconf.sh --rdr

Enter the DB type:

1 - Oracle

2 - MySQL

3 - Sybase

Enter your choice: 2  

Enter MySQL server host (current is 10.0.29.248) :

Enter MySQL server listening port (current is 3306) :

Enter MySQL server instance id (current is apricot) :

Enter CM schema user name (current is pqb_admin) :

Enter CM schema user password (current is pqb_admin) :

Do you want to test the DB connection? (yes/no): yes

PASS:db is up

DB connection succeeded.

Note: restart the CM for the change to take place.

-bash-3.2$ cm --stop

-bash: cm: command not found

-bash-3.2$ cm restart

-bash: cm: command not found

-bash-3.2$ ~scmscm/cm/bin/cm restart

Starting CM:RDR...

-bash-3.2$

Hello,

Ok until we have INI_VALUES populated you should not check SCA BB anymore since this is a root case.

Looking at SCE that all RDRs are thrown, and CM connection is UP, I believe that something is "wrong" with CM.

You can check the logs on CM (cm.log, adapter-JDBC, etc...) and try to see some well known human readable message, or to open a TAC case and we will investigate.

Maybe you can try to paste here some errors or warns from those logs.

Also I am not sure if you check all prerequisites for My SQL and configuration:

http://www.cisco.com/en/US/docs/cable/serv_exch/serv_control/broadband_app/rel37x/cmug/Installing_the_Collection_Manager.html#wp1095056

Thanks,

Minja

Good Morning Minja,

     I use MySQL 5.6 and permissions in MySQL database, I put in first screen, in the opening this case. Believe me, I checked my setup with a specialist of MySQL but, if you think that  we should do something more in MySQL, i'll be back to reopen this case with him.

     I attached 3 logs: cm.log, adapter-JDBCAdapter.log and adapter-TAAdapter.

-bash-3.2$ more adapter-TAAdapter.log

2013-04-16 04:00:08,573 [TAAdapter:execCodeWithTimeout] ERROR com.cisco.scmscm.db.JdbcConnection.RPT_TOPS_PERIOD0 - psExecuteBatch - got SQL

Exception

java.sql.BatchUpdateException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one

table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTE

D or READ UNCOMMITTED.

        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)

        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)

        at com.cisco.scmscm.db.JdbcConnection.psExecuteBatch(JdbcConnection.java:398)

        at com.cisco.scmscm.db.DbInserter.executeBatch(DbInserter.java:982)

        at com.cisco.scmscm.adapters.topper.TAAdapter$2.run(TAAdapter.java:1364)

        at java.lang.Thread.run(Unknown Source)

-bash-3.2$ more adapter-JDBCAdapter.log.1

2013-04-16 04:00:26,900 [cmmtr/RPT_ZUR] ERROR com.cisco.scmscm.db.JdbcConnection.RPT_ZUR - psExecuteBatch - got SQLException

java.sql.BatchUpdateException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one

table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTE

D or READ UNCOMMITTED.

        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)

        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)

        at com.cisco.scmscm.db.JdbcConnection.psExecuteBatch(JdbcConnection.java:398)

        at com.cisco.scmscm.db.DbInserter$Committer.run(DbInserter.java:939)

-bash-3.2$ more cm.log  

2013-01-26 03:56:09,469 [rdrconn/10.0.29.250/1787] INFO com.cisco.scmscm.server.RDRServer - Cumulative results = 1166 RDRs, 0 bad RDRs, rate

= 0.0 peak = 10.617623

2013-01-26 03:56:09,469 [rdrconn/10.0.29.250/1787] INFO com.cisco.scmscm.server.RDRServer - Connection from 10.0.29.250:1787 closed on 33000

after 192 sec.

2013-01-26 03:56:09,470 [rdrconn/10.0.29.250/1787] INFO com.pcube.logger.userlog.UserLog - RDR Server: Connection from 10.0.29.250:1787 clos

ed.

2013-01-26 03:56:09,626 [rdr lsnr/33000] INFO com.cisco.scmscm.server.RDRServer - Connection from 10.0.29.250:1788 on port 33000 opened.

2013-01-26 03:56:09,626 [rdr lsnr/33000] INFO com.pcube.logger.userlog.UserLog - RDR Server: Connection from 10.0.29.250:1788 opened.

2013-01-26 04:02:56,517 [rdrconn/10.0.29.251/1737] WARN com.cisco.scmscm.server.RDRProcessor - Client socket timed out waiting.

java.net.SocketTimeoutException: Read timed out

        at java.net.SocketInputStream.socketRead0(Native Method)

        at java.net.SocketInputStream.read(Unknown Source)

        at java.io.BufferedInputStream.fill(Unknown Source)

        at java.io.BufferedInputStream.read1(Unknown Source)

        at java.io.BufferedInputStream.read(Unknown Source)

        at java.io.DataInputStream.readFully(Unknown Source)

        at java.io.DataInputStream.readFully(Unknown Source)

        at com.cisco.scmscm.server.RDRProcessor.getRdrFromStream(RDRProcessor.java:102)

        at com.cisco.scmscm.server.RDRConnection.run(RDRConnection.java:78)

2013-01-26 04:02:56,517 [rdrconn/10.0.29.251/1737] INFO com.cisco.scmscm.server.RDRServer - Cumulative results = 13 RDRs, 0 bad RDRs, rate =

0.0 peak = 2.4472892

2013-01-26 04:02:56,517 [rdrconn/10.0.29.251/1737] INFO com.cisco.scmscm.server.RDRServer - Connection from 10.0.29.251:1737 closed on 33000

after 406 sec.

2013-01-26 04:02:56,517 [rdrconn/10.0.29.251/1737] INFO com.pcube.logger.userlog.UserLog - RDR Server: Connection from 10.0.29.251:1737 clos

ed.

2013-01-26 04:02:56,644 [rdr lsnr/33000] INFO com.cisco.scmscm.server.RDRServer - Connection from 10.0.29.251:1738 on port 33000 opened.

2013-01-26 04:02:56,644 [rdr lsnr/33000] INFO com.pcube.logger.userlog.UserLog - RDR Server: Connection from 10.0.29.251:1738 opened.

2013-01-26 04:06:09,626 [rdrconn/10.0.29.250/1788] WARN com.cisco.scmscm.server.RDRProcessor - Client socket timed out waiting.

java.net.SocketTimeoutException: Read timed out

        at java.net.SocketInputStream.socketRead0(Native Method)

        at java.net.SocketInputStream.read(Unknown Source)

        at java.io.BufferedInputStream.fill(Unknown Source)

        at java.io.BufferedInputStream.read1(Unknown Source)

        at java.io.BufferedInputStream.read(Unknown Source)

        at java.io.DataInputStream.readFully(Unknown Source)

        at java.io.DataInputStream.readFully(Unknown Source)

        at com.cisco.scmscm.server.RDRProcessor.getRdrFromStream(RDRProcessor.java:102)

        at com.cisco.scmscm.server.RDRConnection.run(RDRConnection.java:78)

2013-01-26 04:06:09,627 [rdrconn/10.0.29.250/1788] INFO com.cisco.scmscm.server.RDRServer - Cumulative results = 1166 RDRs, 0 bad RDRs, rate

= 0.0 peak = 10.617623

      Apparently seems to be a problem with the script done in Java. What do you  think?

Hello (I am not sure about your name) dcamarotto

I think this is My SQL issue preventing CM to write data to the DB.

From the logs we have:

SQLException

java.sql.BatchUpdateException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one

table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTE

D or READ UNCOMMITTED.

THis is error related to myqsl and has nothing to do with CM.

Searching on google I could find some explanations and how to fix it, but I suggest to check with your DB admin I guess he shuld be familiar with the error and to understand how to fix it.

Let me know if this solved your issues.

Thanks,

Minja

Hi Minja,

             I finally solved problem with Policy String Transaltions with link on: Oracle.

Solution

There are two solutions:

  1. Change the binlog_format to MIXED or ROW.
  2. Change the transaction isolation level to REPEATABLE READ

1. Change the binlog_format

The MIXED format will cause all statements using the READ COMMITTED or READ UNCOMMITTED transaction levels to be logged using the row-based format.

If you do not have a PRIMARY KEY on all the  tables, using the row-based format can make replaying the binary log  (e.g. on the slave) very slow.


Changing the binary log format to MIXED can be done by updating the binlog_format variable:

SET GLOBAL binlog_format = MIXED;

Note the change will only take effect for new connections.


and update the MySQL configuration file to ensure the change persists after a restart of MySQL:

[mysqld]
binlog_format = MIXED


See also Mixed Binary Logging Format in the Reference Manual for a complete list of rules used to determine  whether an update will be logged using the statement-based or row-based  format.

2. Change the Transaction Isolation Level

Using REPEATABLE-READ (or SERIALIZABLE)  as the transaction isolation level makes InnoDB take additional locks  which ensure that using statement-based replication is safe - provided  the query itself is deterministic.

You can change the transaction isolation level using the SET TRANSACTION statement. This can be done both for all new connections or just the current connection.

To change for all new connections use:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;


You will also have to update the MySQL configuration file setting the transaction-isolation option to ensure the change persists when restarting MySQL:

[mysqld]
transaction-isolation = REPEATABLE-READ


In order to change the transaction isolation level just for the  session. This can be done in different ways depending on the scope  intended for the change:

  1. For the session:

    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

  2. Just for the next (unstarted) transaction:

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;


    You are not allowed to use SET TRANSACTION without the SESSION or GLOBAL modifier if you are already inside a transaction.

You can verify the current transaction isolation level using the tx_isolation variable:

mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| REPEATABLE-READ        | READ-COMMITTED        |
+------------------------+-----------------------+
1 row in set (0.00 sec)

                Now, I have this menssage:

I see instalation guide and put TZ in etc/TIMEZONE, but not resolved yet. Where I run this jselect-se-tz-to?

Hi Minja,

        I've got the command and ran and it seems to me that everything is working now.  I will monitor ...

-bash-3.2$ ~scmscm/cm/bin/cm restart

Starting CM:RDR...

-bash-3.2$ ~scmscm/cm/bin/jselect-sce-tz.sh --rdr --offset=-180

                    TIME_STAMP|                    OFFSET_MIN|

------------------------------+------------------------------+

         2013-04-16 17:19:15.0|                          -180|

-bash-3.2$

-bash-3.2$ ~scmscm/cm/bin/cm restart

Starting CM:RDR...

-bash-3.2$

]

Hi,

I am glad issue has been resolved.

Thanks,

Minja

Can I suggest to put this procedure in MySQL on the CM installation document, when we use an external database?

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: