This discussion is archived
5 Replies Latest reply: Nov 28, 2012 2:23 AM by 671265 RSS

metadata with defining Derived Associations valided failed.

671265 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points