5 Replies Latest reply: Nov 28, 2012 4:23 AM by 671265 RSS

    metadata with defining Derived Associations valided failed.

    671265
      Hey all,

      I am developing a MySQL plugin, and want to use "Derived Associations" to make a topology to display the relationship between mysql objects and its cluster group. However I run into some trouble. Could anyone help me out? I am really confused.

      Here is the code to define Derived Associations
      <?xml version="1.0" encoding="UTF-8"?>
      <Rules xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <Rule name="mysql_cluster_contains">
         <query> 
            select 'runs_on_cluster' as assoc_type, 
                    c.target_guid as source_me_guid, 
                    b.target_guid as dest_me_guid
                    HEXTORAW(NULL) as derivation_target_guid
            from MGMT_EMX_MYSQL_DBVAR a,MGMT_ECM_GEN_SNAPSHOT b,MGMT_TARGETS c
            where a.ecm_snapshot_id=b.SNAPSHOT_GUID 
              and a.VARIABLE_NAME ='wsrep_cluster_name' 
              and b.IS_CURRENT='Y'
              and c.TARGET_NAME=a.VALUE
              and c.TARGET_TYPE='oracle_mysql_cluster'
         </query>
          <trigger>
              <targetType>oracle_mysql_database</targetType>
              <snapshotType>MYSQL_MYSQL_DB</snapshotType>
              <table>MGMT_EMX_MYSQL_DBVAR</table>
              <idColumn>destination</idColumn>
          </trigger>
        </Rule>
      </Rules>
      After I get the file under folder metadata\derivedAssocs, and try to pack the plugin, it failed with message as below.
      Violation type: NON SDK Access detected
      Validation Type: Meta data Embedded SQL
      Artifact being accessed: TABLE MGMT_ECM_GEN_SNAPSHOT
      Accessed by: SQL String sql0 in /DATA/mysql_cluster_plugin/mysql_cluster/oms/metadata/derivedAssocs/mysql_cluster_assoc_rules.xml
      The SQL query embedded in the above file or class is accessing a non SDK DB object. For legibility only SQL IDs are mentioned here. The actual SQL Strings corresponding to these IDs are mentioned towards the end of the report.
      
      Violation type: NON SDK Access detected
      Validation Type: Meta data Embedded SQL
      Artifact being accessed: TABLE MGMT_TARGETS
      Accessed by: SQL String sql0 in /DATA/mysql_cluster_plugin/mysql_cluster/oms/metadata/derivedAssocs/mysql_cluster_assoc_rules.xml
      The SQL query embedded in the above file or class is accessing a non SDK DB object. For legibility only SQL IDs are mentioned here. The actual SQL Strings corresponding to these IDs are mentioned towards the end of the report.
      
      
      SQL IDs to Strings mapping:
      sql0:  
            select 'runs_on_cluster' as assoc_type, 
                    c.target_guid as source_me_guid, 
                    b.target_guid as dest_me_guid
                    HEXTORAW(NULL) as derivation_target_guid
            from MGMT_EMX_MYSQL_DBVAR a,MGMT_ECM_GEN_SNAPSHOT b,MGMT_TARGETS c
            where a.ecm_snapshot_id=b.SNAPSHOT_GUID 
              and a.VARIABLE_NAME ='wsrep_cluster_name' 
              and b.IS_CURRENT='Y'
              and c.TARGET_NAME=a.VALUE
              and c.TARGET_TYPE='oracle_mysql_cluster'
      I am sure the SQL is right to display the data. I don't know what "NON SDK Access" is and why the code is wrong. :(
      Thank you in advance!


      Best wishes,
      Satine
        • 1. Re: metadata with defining Derived Associations valided failed.
          973836
          Please see most recent derived associations guide. There are at least 3 issues I see:

          (1) Your query is wrong - if you try to simply run it on any repository, you should get an error because you have do not have all commas in your SELECT clause
          (2) "HEXTORAW(NULL) as derivation_target_guid" is not needed
          (3) List of objects accessible by your query is listed in the guide. You cannot access all objects in sysman schema but only allowed ones. For example, objects from framework must be SDK objects (since framework and plugins are only communicating via SDK objects like SDK views). You are relying on non-SDK table MGMT_ECM_GEN_SNAPSHOT. Also, MGMT_EMX_MYSQL_DBVAR does not look like an SDK table / view but might be ok if your derived assoc is in the same plugin as table definition.

          For more questions, feel free to email me at vitaliy.khizder@oracle.com

          Regards,

          Vitaliy
          • 2. Re: metadata with defining Derived Associations valided failed.
            671265
            Hey Vitaliy,

            Thank you very much for your help!
            I will try to post questions here, in case other people have the same problem can also reference your reply. :)

            After I change SQL as this, I can package the plugin.
            <?xml version="1.0" encoding="UTF-8"?>
            <Rules xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <Rule name="mysql_cluster_contains">
               <query> 
                  select 'runs_on_cluster' as assoc_type, 
                          a.target_guid as source_me_guid, 
                          b.target_guid as dest_me_guid
                  from MGMT$TARGET_PROPERTIES a,MGMT$TARGET b
                  where a.PROPERTY_NAME ='version' 
                    and a.PROPERTY_VALUE=b.TARGET_NAME
                    and b.TARGET_TYPE='oracle_mysql_cluster'
               </query>
              </Rule>
            </Rules>
            However, after I deploy it to OMS and added targets for it. There were still no topology display on EM interface (Target -> Configuration -> Topology). I am sure the SQL has data returned, and the GC$ECM_CONFIG.run_assoc_deriv_rule was executed successfully.
            SQL>       select 'runs_on_cluster' as assoc_type, 
              2                a.target_guid as source_me_guid, 
              3                b.target_guid as dest_me_guid
              4        from MGMT$TARGET_PROPERTIES a,MGMT$TARGET b
              5        where a.PROPERTY_NAME ='version' 
              6          and a.PROPERTY_VALUE=b.TARGET_NAME
              7          and b.TARGET_TYPE='oracle_mysql_cluster';
            
            ASSOC_TYPE                     SOURCE_ME_GUID                   DEST_ME_GUID
            ------------------------------ -------------------------------- --------------------------------
            runs_on_cluster                309C19DEEDC84BA78748C22F6E56D29F BCBF699C02E8984F79B180A14A1D1501
            runs_on_cluster                69482E4EDEC000BDD7B8E7567A484860 BCBF699C02E8984F79B180A14A1D1501
            
            SQL> DECLARE                                                                                            
              2      temp GC$DERIV_ASSOC_CHANGE_LIST := GC$DERIV_ASSOC_CHANGE_LIST();                               
              3  BEGIN                                                                                              
              4      GC$ECM_CONFIG.run_assoc_deriv_rule(                                                            
              5        p_target_guid => hextoraw('309C19DEEDC84BA78748C22F6E56D29F'),                               
              6        p_rule_name => 'mysql_cluster_contains',                                                     
              7        p_column_flag => 'S',                                                                        
              8        p_change_list => temp);                                                                      
              9        COMMIT;                                                                                      
             10  END;                                                                                               
             11  /                                                                                                  
            PL/SQL procedure successfully completed.
            
            SQL> DECLARE                                                                                            
              2      temp GC$DERIV_ASSOC_CHANGE_LIST := GC$DERIV_ASSOC_CHANGE_LIST();                               
              3  BEGIN                                                                                              
              4      GC$ECM_CONFIG.run_assoc_deriv_rule(                                                            
              5        p_target_guid => hextoraw('69482E4EDEC000BDD7B8E7567A484860'),                               
              6        p_rule_name => 'mysql_cluster_contains',                                                     
              7        p_column_flag => 'S',                                                                        
              8        p_change_list => temp);                                                                      
              9        COMMIT;                                                                                      
             10  END;                                                                                               
             11  /                                                                                                  
            PL/SQL procedure successfully completed.
            
            SQL> DECLARE                                                                                            
              2      temp GC$DERIV_ASSOC_CHANGE_LIST := GC$DERIV_ASSOC_CHANGE_LIST();                               
              3  BEGIN                                                                                              
              4      GC$ECM_CONFIG.run_assoc_deriv_rule(                                                            
              5        p_target_guid => hextoraw('BCBF699C02E8984F79B180A14A1D1501'),                               
              6        p_rule_name => 'mysql_cluster_contains',                                                     
              7        p_column_flag => 'S',                                                                        
              8        p_change_list => temp);                                                                      
              9        COMMIT;                                                                                      
             10  END;                                                                                               
             11  /                                                                                                  
            PL/SQL procedure successfully completed.
            Would please let me know what I did wrong?


            Best wishes,
            Satine
            • 3. Re: metadata with defining Derived Associations valided failed.
              973836
              Hi Satine,

              Your query is confusing:

              a.PROPERTY_NAME ='version' and a.PROPERTY_VALUE=b.TARGET_NAME

              conditions indicate that you have some property named "version" whose value is a target name...? There appears to be something wrong with that - version should never be a target name.

              Further, it might be good if you bound a.target_type to limit target types considered for the query and so that more indexes can be used during runtime.

              Now, to answer your question, first you need to determine where the issue is:
              (a) is your association derived and actually present in repository? Even though you manually invoked some things, it's not clear if they get invoked in your environment with right parameters. Normally, things get invoked when
              - you run emctl command to install the rule (if the rule query changed in some way). Then all rule's associations are recomputed in background job
              - when your triggers fire
              - when you call manual API (run_assoc_deriv_rule)
              So, first, you need to check if assocs you expect between your targets are actually present in repository; e.g. via query on MGMT_ASSOC_INSTANCES table.

              (b) if above mentioned query DOES show the assocs in repository, check with topology folks on why these assocs don't appear (you can email me for a contact), or if (more likely), the assocs DO NOT appear in repository, follow debugging / troubleshooting section in the derived assoc guide to get additional messages and to check for additional things to see why your assocs are not being derived.

              Edited by: user705864 on Nov 20, 2012 7:24 AM

              Edited by: user705864 on Nov 20, 2012 7:26 AM

              Edited by: user705864 on Nov 20, 2012 7:31 AM
              • 4. Re: metadata with defining Derived Associations valided failed.
                671265
                Hey Vitaliy,

                Thank you for your kind help!

                There is no expeced assocs data exists in MGMT_ASSOC_INSTANCES. Then I turned on debugging while call run_assoc_deriv_rule.
                SQL> DECLARE                                                                                            
                  2      temp GC$DERIV_ASSOC_CHANGE_LIST := GC$DERIV_ASSOC_CHANGE_LIST();                               
                  3  BEGIN    
                  4      emdw_log.set_trace_level(null, GC$OBJ_LOG.LEVEL_INFO); 
                  5      commit;                                                                                          
                  6      
                  7      GC$ECM_CONFIG.run_assoc_deriv_rule(                                                            
                  8        p_target_guid => hextoraw('CE84FC2F2C20355BE043E19D0B0A8409'),                               
                  9        p_rule_name => 'mysql_cluster_contains',                                                     
                 10        p_column_flag => 'S',                                                                        
                 11        p_change_list => temp);                                                                      
                 12        COMMIT;   
                 13       
                 14      dbms_output.put_line(emdw_log.get_trace_level(null,null));
                 15                                                                                   
                 16  END;                                                                                               
                 17  / 
                
                PL/SQL procedure successfully completed.
                
                SQL> SELECT log_message FROM emdw_trace_data WHERE module = 'EM.deriv' ORDER BY log_timestamp asc;
                
                LOG_MESSAGE
                -----------------------------------------------------------------------------------------------------------------------
                get_assoc_changes(executing with target/rule: ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database/mysql_cluster_contains from plugin sohudba.mysql.clus
                # of action-generated rows merged: 0
                make_changes: Number of changes = 1
                "  Change 1: A runs_on_cluster ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database ==> sohu_cluster_test_on_phy:oracle_mysql_cluster
                ; Derivation targets: , , 
                Rule: mysql_cluster_contains from plugin sohudba.mysql.clus"
                "Parent assoc row not found for runs_on_cluster: ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database => sohu_cluster_test_on_phy:oracle_mysql_cluster
                  Rule CE6D1C18FC6F4FC9E043E19D0B0A8529; Derivation targets: , , "
                "*** Error: Failed on insert of origin row for runs_on_cluster ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database ==> sohu_cluster_test_on_phy:oracle_mysql_cluster
                Derivation targets: , , 
                Rule: mysql_cluster_contains from plugin sohudba.mysql.clus: ORA-20624: Specified assoc does not match any constraint assoc type: runs_on_cluster source ME type: oracle_mysql_database dest ME type: oracle_mysql_cluster
                "
                "*** Error: ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 620
                ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 2013
                ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 2230
                ORA-06512: 在 ""SYSMAN.EM_DERIV"", line 3634
                ORA-06512: 在 ""SYSMAN.EM_DERIV"", line 3845
                
                ----- PL/SQL Call Stack -----
                  object      line  object
                  handle    number  name
                0x1df7102d0       405  package body SYSMAN.EM_DERIV
                0x1df7102d0      3853  package body SYSMAN.EM_DERIV
                0x1df7102d0      3954  package body SYSMAN.EM_DERIV
                0x1df7102d0      4511  package body SYSMAN.EM_DERIV
                0x1df7102d0      4575  package body SYSMAN.EM_DERIV
                0x1e21645d8        31  package body SYSMAN.GC$ECM_CONFIG
                0x1d4f4dd40         7  anonymous block
                "
                make_changes: Number of changes = 0
                From error message, it seems the assoc_type I defied made some constraint violation. Then I change assoc_type from "runs_on_cluster" to "runs_on_mysql_cluster". Re-call run_assoc_deriv_rule with debugging, it also failed with error "Invalid assoc type".
                get_assoc_changes(executing with target/rule: ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database/mysql_cluster_contains from plugin sohudba.mysql.clus
                # of action-generated rows merged: 0
                make_changes: Number of changes = 1
                "  Change 1: A runs_on_mysql_cluster ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database ==> sohu_cluster_test_on_phy:oracle_mysql_cluster
                ; Derivation targets: , , 
                Rule: mysql_cluster_contains from plugin sohudba.mysql.clus"
                "Parent assoc row not found for runs_on_mysql_cluster: ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database => sohu_cluster_test_on_phy:oracle_mysql_cluster
                  Rule CE6D1C18FC6F4FC9E043E19D0B0A8529; Derivation targets: , , "
                "*** Error: Failed on insert of origin row for runs_on_mysql_cluster ph-10_10_59_228.cp.no.sohu.com_mysql_5000:oracle_mysql_database ==> sohu_cluster_test_on_phy:oracle_mysql_cluster
                Derivation targets: , , 
                Rule: mysql_cluster_contains from plugin sohudba.mysql.clus: ORA-20624: Invalid assoc type runs_on_mysql_cluster
                "
                "*** Error: ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 154
                ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 2010
                ORA-06512: 在 ""SYSMAN.EM_ASSOC_NG"", line 2230
                ORA-06512: 在 ""SYSMAN.EM_DERIV"", line 3634
                ORA-06512: 在 ""SYSMAN.EM_DERIV"", line 3845
                
                ----- PL/SQL Call Stack -----
                  object      line  object
                  handle    number  name
                0x1df7102d0       405  package body SYSMAN.EM_DERIV
                0x1df7102d0      3853  package body SYSMAN.EM_DERIV
                0x1df7102d0      3954  package body SYSMAN.EM_DERIV
                0x1df7102d0      4511  package body SYSMAN.EM_DERIV
                0x1df7102d0      4575  package body SYSMAN.EM_DERIV
                0x1e21645d8        31  package body SYSMAN.GC$ECM_CONFIG
                0x1d4f4dd40         7  anonymous block
                "
                make_changes: Number of changes = 0
                Thanks,
                Satine
                • 5. Re: metadata with defining Derived Associations valided failed.
                  671265
                  Hey Vitaliy,

                  I code up a new file “allowed_pairs.xml”, put it under folder “metadata\assoc”, and then package all files into a new version plugin, after deploy it to EM, the topology appears under targets.

                  I can’t do it without you. Thank you so much for your generous help!

                  Have a nice vacation! :)


                  Best wishes,
                  Satine