Hanging on SQL Queries

johnyma22

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?

我有时会:2012年2月4日下午6:12:29 WARNING: Caught exception in concurrent execution of Read Database (Read Database): com.rapidminer.operator.UserError: Database error occurred: Operation not allowed after ResultSet closed

haddock

Hi,
Please post the XML.

johnyma22

<运营商激活= " true "班ss="process" compatibility="5.2.000" expanded="true" name="Process">


<运营商激活= " true "班ss="read_database" compatibility="5.2.000" expanded="true" height="60" name="Read Database (2)" width="90" x="45" y="30">


<枚举关键= "参数"/>

<运营商激活= " true "班ss="nominal_to_text" compatibility="5.2.000" expanded="true" height="76" name="Nominal to Text" width="90" x="179" y="30">



<运营商激活= " true "班ss="text:process_document_from_data" compatibility="5.1.004" expanded="true" height="76" name="Process Documents from Data" width="90" x="313" y="30">






<运营商激活= " true "班ss="web:extract_html_text_content" compatibility="5.1.004" expanded="true" height="60" name="Extract Content" width="90" x="45" y="120"/>
<运营商激活= " true "班ss="text:transform_cases" compatibility="5.1.004" expanded="true" height="60" name="Transform Cases" width="90" x="179" y="120"/>
<运营商激活= " true "班ss="text:tokenize" compatibility="5.1.004" expanded="true" height="60" name="Tokenize" width="90" x="313" y="120"/>
<运营商激活= " true "班ss="text:filter_stopwords_english" compatibility="5.1.004" expanded="true" height="60" name="Filter Stopwords (English)" width="90" x="447" y="120"/>
<运营商激活= " true "班ss="text:stem_snowball" compatibility="5.1.004" expanded="true" height="60" name="Stem (Snowball)" width="90" x="581" y="120"/>
<运营商激活= " true "班ss="text:filter_by_length" compatibility="5.1.004" expanded="true" height="60" name="Filter Tokens (by Length)" width="90" x="715" y="120">














<运营商激活= " true "班ss="read_database" compatibility="5.2.000" expanded="true" height="60" name="Read Database" width="90" x="45" y="210">


<枚举关键= "参数"/>

<运营商激活= " true "班ss="nominal_to_text" compatibility="5.2.000" expanded="true" height="76" name="Nominal to Text (2)" width="90" x="179" y="210">



<运营商激活= " true "班ss="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">






<运营商激活= " true "班ss="web:extract_html_text_content" compatibility="5.1.004" expanded="true" height="60" name="Extract Content (2)" width="90" x="45" y="30"/>
<运营商激活= " true "班ss="text:transform_cases" compatibility="5.1.004" expanded="true" height="60" name="Transform Cases (2)" width="90" x="180" y="30"/>
<运营商激活= " true "班ss="text:tokenize" compatibility="5.1.004" expanded="true" height="60" name="Tokenize (2)" width="90" x="315" y="30"/>
<运营商激活= " true "班ss="text:filter_stopwords_english" compatibility="5.1.004" expanded="true" height="60" name="Filter Stopwords (2)" width="90" x="450" y="30"/>
<运营商激活= " true "班ss="text:stem_snowball" compatibility="5.1.004" expanded="true" height="60" name="Stem (2)" width="90" x="585" y="30"/>
<运营商激活= " true "班ss="text:filter_by_length" compatibility="5.1.004" expanded="true" height="60" name="Filter Tokens (2)" width="90" x="703" y="30">














<运营商激活= " true "班ss="set_role" compatibility="5.2.000" expanded="true" height="76" name="Set Role (2)" width="90" x="447" y="210">




<运营商激活= " true "班ss="set_role" compatibility="5.2.000" expanded="true" height="76" name="Set Role" width="90" x="447" y="30">




<运营商激活= " true "班ss="naive_bayes" compatibility="5.2.000" expanded="true" height="76" name="Naive Bayes" width="90" x="581" y="30">


<运营商激活= " true "班ss="apply_model" compatibility="5.2.000" expanded="true" height="76" name="Apply Model (2)" width="90" x="715" y="120">

haddock

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 parallelisedhttp://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 = "true" OR isGood = -2 AND school_list_holiday_sources.label = "false" 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 != "true" 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)

johnyma22

Ahhh, okay.. Well I will try to figure out how to filter out my training data. Any pointers?

haddock

Don't want to sound grumpy, but you really need to work through help->tutorial.

johnyma22

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!

haddock

酷。