Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.

Hanging on SQL Queries

johnyma22johnyma22 Member Posts: 17 Contributor II
edited October 2019 in Help
Every now and again (1 in 3 times) Rapidminer will hang on an SQL query.  Usually my process will take 5 minutes.

By hang I mean the timer just keeps going, for hours, it never resolves.

The data in the database is static, im not updating/writing new rows to it.

is this a common issue?  Should I think about doing a large extract from the database to my local machine and working with that?

I sometimes get this: Feb 4, 2012 6:12:29 PM WARNING: Caught exception in concurrent execution of Read Database (Read Database): com.rapidminer.operator.UserError: Database error occurred: Operation not allowed after ResultSet closed
Tagged:

Answers

  • haddockhaddock Member Posts: 849 Maven
    Hi,
    Please post the XML.
  • johnyma22johnyma22 Member Posts: 17 Contributor II 

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.2.000">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.2.000" expanded="true" name="Process">
        <parameter key="parallelize_main_process" value="true"/>
        <process expanded="true" height="386" width="835">
          <operator activated="true" class="read_database" compatibility="5.2.000" expanded="true" height="60" name="Read Database (2)" width="90" x="45" y="30">
            <parameter key="connection" value="slave2"/>
            <parameter key="query" value="SELECT label, data, school_list_holiday_sources.id FROM school_list_holiday_data INNER JOIN school_list_holiday_sources ON school_list_holiday_data.id=school_list_holiday_sources.id WHERE school_list_holiday_sources.label = &quot;true&quot; OR isGood = -2 AND school_list_holiday_sources.label = &quot;false&quot; LIMIT 0,200"/>
            <enumeration key="parameters"/>
          </operator>
          <operator activated="true" class="nominal_to_text" compatibility="5.2.000" expanded="true" height="76" name="Nominal to Text" width="90" x="179" y="30">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="data"/>
          </operator>
          <operator activated="true" class="text:process_document_from_data" compatibility="5.1.004" expanded="true" height="76" name="Process Documents from Data" width="90" x="313" y="30">
            <parameter key="keep_text" value="true"/>
            <parameter key="prune_method" value="absolute"/>
            <parameter key="prune_below_absolute" value="2"/>
            <parameter key="prune_above_absolute" value="999"/>
            <list key="specify_weights"/>
            <process expanded="true" height="480" width="815">
              <operator activated="true" class="web:extract_html_text_content" compatibility="5.1.004" expanded="true" height="60" name="Extract Content" width="90" x="45" y="120"/>
              <operator activated="true" class="text:transform_cases" compatibility="5.1.004" expanded="true" height="60" name="Transform Cases" width="90" x="179" y="120"/>
              <operator activated="true" class="text:tokenize" compatibility="5.1.004" expanded="true" height="60" name="Tokenize" width="90" x="313" y="120"/>
              <operator activated="true" class="text:filter_stopwords_english" compatibility="5.1.004" expanded="true" height="60" name="Filter Stopwords (English)" width="90" x="447" y="120"/>
              <operator activated="true" class="text:stem_snowball" compatibility="5.1.004" expanded="true" height="60" name="Stem (Snowball)" width="90" x="581" y="120"/>
              <operator activated="true" class="text:filter_by_length" compatibility="5.1.004" expanded="true" height="60" name="Filter Tokens (by Length)" width="90" x="715" y="120">
                <parameter key="min_chars" value="2"/>
              </operator>
              <connect from_port="document" to_op="Extract Content" to_port="document"/>
              <connect from_op="Extract Content" from_port="document" to_op="Transform Cases" to_port="document"/>
              <connect from_op="Transform Cases" from_port="document" to_op="Tokenize" to_port="document"/>
              <connect from_op="Tokenize" from_port="document" to_op="Filter Stopwords (English)" to_port="document"/>
              <connect from_op="Filter Stopwords (English)" from_port="document" to_op="Stem (Snowball)" to_port="document"/>
              <connect from_op="Stem (Snowball)" from_port="document" to_op="Filter Tokens (by Length)" to_port="document"/>
              <connect from_op="Filter Tokens (by Length)" from_port="document" to_port="document 1"/>
              <portSpacing port="source_document" spacing="90"/>
              <portSpacing port="sink_document 1" spacing="90"/>
              <portSpacing port="sink_document 2" spacing="0"/>
            </process>
          </operator>
          <operator activated="true" class="read_database" compatibility="5.2.000" expanded="true" height="60" name="Read Database" width="90" x="45" y="210">
            <parameter key="connection" value="slave2"/>
            <parameter key="query" value="SELECT data, label, isGood, school_list_holiday_sources.id FROM school_list_holiday_data INNER JOIN school_list_holiday_sources ON school_list_holiday_data.id=school_list_holiday_sources.id WHERE label != &quot;true&quot; AND isGood = 0 LIMIT 0,2000"/>
            <enumeration key="parameters"/>
          </operator>
          <operator activated="true" class="nominal_to_text" compatibility="5.2.000" expanded="true" height="76" name="Nominal to Text (2)" width="90" x="179" y="210">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="data"/>
          </operator>
          <operator activated="true" class="text:process_document_from_data" compatibility="5.1.004" expanded="true" height="76" name="Process Documents from Data (2)" width="90" x="313" y="210">
            <parameter key="keep_text" value="true"/>
            <parameter key="prune_method" value="absolute"/>
            <parameter key="prune_below_absolute" value="2"/>
            <parameter key="prune_above_absolute" value="999"/>
            <list key="specify_weights"/>
            <process expanded="true" height="340" width="803">
              <operator activated="true" class="web:extract_html_text_content" compatibility="5.1.004" expanded="true" height="60" name="Extract Content (2)" width="90" x="45" y="30"/>
              <operator activated="true" class="text:transform_cases" compatibility="5.1.004" expanded="true" height="60" name="Transform Cases (2)" width="90" x="180" y="30"/>
              <operator activated="true" class="text:tokenize" compatibility="5.1.004" expanded="true" height="60" name="Tokenize (2)" width="90" x="315" y="30"/>
              <operator activated="true" class="text:filter_stopwords_english" compatibility="5.1.004" expanded="true" height="60" name="Filter Stopwords (2)" width="90" x="450" y="30"/>
              <operator activated="true" class="text:stem_snowball" compatibility="5.1.004" expanded="true" height="60" name="Stem (2)" width="90" x="585" y="30"/>
              <operator activated="true" class="text:filter_by_length" compatibility="5.1.004" expanded="true" height="60" name="Filter Tokens (2)" width="90" x="703" y="30">
                <parameter key="min_chars" value="2"/>
              </operator>
              <connect from_port="document" to_op="Extract Content (2)" to_port="document"/>
              <connect from_op="Extract Content (2)" from_port="document" to_op="Transform Cases (2)" to_port="document"/>
              <connect from_op="Transform Cases (2)" from_port="document" to_op="Tokenize (2)" to_port="document"/>
              <connect from_op="Tokenize (2)" from_port="document" to_op="Filter Stopwords (2)" to_port="document"/>
              <connect from_op="Filter Stopwords (2)" from_port="document" to_op="Stem (2)" to_port="document"/>
              <connect from_op="Stem (2)" from_port="document" to_op="Filter Tokens (2)" to_port="document"/>
              <connect from_op="Filter Tokens (2)" from_port="document" to_port="document 1"/>
              <portSpacing port="source_document" spacing="0"/>
              <portSpacing port="sink_document 1" spacing="0"/>
              <portSpacing port="sink_document 2" spacing="0"/>
            </process>
          </operator>
          <operator activated="true" class="set_role" compatibility="5.2.000" expanded="true" height="76" name="Set Role (2)" width="90" x="447" y="210">
            <parameter key="name" value="label"/>
            <parameter key="target_role" value="label"/>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="set_role" compatibility="5.2.000" expanded="true" height="76" name="Set Role" width="90" x="447" y="30">
            <parameter key="name" value="label"/>
            <parameter key="target_role" value="label"/>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="naive_bayes" compatibility="5.2.000" expanded="true" height="76" name="Naive Bayes" width="90" x="581" y="30">
            <parameter key="laplace_correction" value="false"/>
          </operator>
          <operator activated="true" class="apply_model" compatibility="5.2.000" expanded="true" height="76" name="Apply Model (2)" width="90" x="715" y="120">
            <list key="application_parameters"/>
          </operator>
          <connect from_op="Read Database (2)" from_port="output" to_op="Nominal to Text" to_port="example set input"/>
          <connect from_op="Nominal to Text" from_port="example set output" to_op="Process Documents from Data" to_port="example set"/>
          <connect from_op="Process Documents from Data" from_port="example set" to_op="Set Role" to_port="example set input"/>
          <connect from_op="Process Documents from Data" from_port="word list" to_op="Process Documents from Data (2)" to_port="word list"/>
          <connect from_op="Read Database" from_port="output" to_op="Nominal to Text (2)" to_port="example set input"/>
          <connect from_op="Nominal to Text (2)" from_port="example set output" to_op="Process Documents from Data (2)" to_port="example set"/>
          <connect from_op="Process Documents from Data (2)" from_port="example set" to_op="Set Role (2)" to_port="example set input"/>
          <connect from_op="Set Role (2)" from_port="example set output" to_op="Apply Model (2)" to_port="unlabelled data"/>
          <connect from_op="Set Role" from_port="example set output" to_op="Naive Bayes" to_port="training set"/>
          <connect from_op="Naive Bayes" from_port="model" to_op="Apply Model (2)" to_port="model"/>
          <connect from_op="Apply Model (2)" from_port="labelled data" to_port="result 1"/>
          <connect from_op="Apply Model (2)" from_port="model" to_port="result 2"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_result 1" spacing="90"/>
          <portSpacing port="sink_result 2" spacing="0"/>
          <portSpacing port="sink_result 3" spacing="0"/>
        </process>
      </operator>
    </process>
  • haddockhaddock Member Posts: 849 Maven
    Hi there,

    You're getting an SQL error - "Operation not allowed after ResultSet closed" - the question is why? I Googled that error and followed this link 

    http://stackoverflow.com/questions/5840866/getting-java-sql-sqlexception-operation-not-allowed-after-resultset-closed

    The bit that caught my eye was..
    A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
    because there is a fair amount of common ground between the two SQL statements. I could imagine scenarios where the second request kicks off at the wrong time, especially if the main process is parallelised http://rapid-i.com/rapidforum/index.php/topic,2162.msg8510.html#msg8510.

    As an aside, if we look at the SQL.. 
    SELECT label, data, school_list_holiday_sources.id FROM school_list_holiday_data INNER JOIN school_list_holiday_sources ON school_list_holiday_data.id=school_list_holiday_sources.id WHERE school_list_holiday_sources.label = &quot;true&quot; OR isGood = -2 AND school_list_holiday_sources.label = &quot;false&quot; LIMIT 0,200"/>

    SELECT data, label, isGood, school_list_holiday_sources.id FROM school_list_holiday_data INNER JOIN school_list_holiday_sources ON school_list_holiday_data.id=school_list_holiday_sources.id WHERE label != &quot;true&quot; AND isGood = 0 LIMIT 0,2000"/>
    In each you are joining the same two tables, in the same way , on school_list_holiday_data.id=school_list_holiday_sources.id ; that could therefore be done in one statement, because you can filter out your training and test sets explicitly in RM. In fact this is a pretty good example of how it is easier to debug SQL using el rapido. Actually I'd do the join in RM as well  8)

  • johnyma22johnyma22 Member Posts: 17 Contributor II
    Ahhh, okay..  Well I will try to figure out how to filter out my training data.  Any pointers?

  • haddockhaddock Member Posts: 849 Maven
    Don't want to sound grumpy, but you really need to work through help->tutorial.
  • johnyma22johnyma22 Member Posts: 17 Contributor II
    Agreed.  I hadn't spotted this before, just the Video tutorials.  I have the book to read next week when I'm on holiday.  Will go through these tomorrow.  Good shout!
  • haddockhaddock Member Posts: 849 Maven
    Cool.
Sign In or Register to comment.