I’ve got a pretty simple graph:
DBINPUT → Reformat → MysqlDataWriter
The reformatter doesn’t do much except map source fields to destination fields (there’s one integer that’s converted to a boolean with a function). Datatypes are virtually all the same or compatible.
When I view debug info on either of the 2 edges, I see it report 7,077 records. Good. That’s what’s in the source database.
But when I run the graph I end up with over 26,211 records in my destination table. (I truncate/empty the table before running the graph, this is not an “append” issue.)
I get some other really funky stuff as well (like two records with negative primary keys IDs, something that does not exist in the source data) don’t know if that’s related to what’s going on here.
Log-output and xml-graph is pasted below for reference.
Any pointers about where to start?
Thanks!
- Gary
INFO [main] - *** CloverETL framework/transformation graph, (c) 2002-2012 Javlin a.s, released under GNU Lesser General Public License ***
INFO [main] - Running with CloverETL library version 3.3.0.M1 build#063 compiled 07/03/2012 18:39:35
INFO [main] - Running on 2 CPU(s), OS Mac OS X, architecture x86_64, Java version 1.6.0_29, max available memory for JVM 126912 KB
INFO [main] - Loading default properties from: defaultProperties
INFO [main] - Graph definition file: graph/SptmPerson_to_BrigstonContact.grf
INFO [main] - Graph revision: 1.46 Modified by: gary Modified: Thu Apr 05 13:18:02 PDT 2012
INFO [main] - Checking graph configuration...
INFO [main] - Graph configuration is valid.
INFO [main] - Graph initialization (LoadCustomers)
INFO [main] - Initializing connection:
INFO [main] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@552a66ea]:jndi[null]:url[jdbc:mysql://localhost:3306/]:user[root] ... OK
INFO [main] - [Clover] Initializing phase: 0
INFO [main] - [Clover] phase: 0 initialized successfully.
INFO [main] - register MBean with name:org.jetel.graph.runtime:type=CLOVERJMX_1333510965501_0
INFO [WatchDog] - Pre-execute initialization of connection:
INFO [WatchDog] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@552a66ea]:jndi[null]:url[jdbc:mysql://localhost:3306/]:user[root] ... OK
INFO [WatchDog] - Starting up all nodes in phase [0]
INFO [WatchDog] - Successfully started all nodes in phase!
INFO [WatchDog] - [Clover] Post-execute phase finalization: 0
INFO [WatchDog] - [Clover] phase: 0 post-execute finalization successfully.
INFO [WatchDog] - ----------------------** Final tracking Log for phase [0] **---------------------
INFO [WatchDog] - Time: 05/04/12 13:22:30
INFO [WatchDog] - Node ID Port #Records #KB aRec/s aKB/s
INFO [WatchDog] - ---------------------------------------------------------------------------------
INFO [WatchDog] - DBInputTable DB_INPUT_TABLE0 FINISHED_OK
INFO [WatchDog] - %cpu:.. Out:0 7077 5684 2359 1894
INFO [WatchDog] - Reformat REFORMAT1 FINISHED_OK
INFO [WatchDog] - %cpu:.. In:0 7077 5684 2359 1894
INFO [WatchDog] - Out:0 7077 5774 2359 1924
INFO [WatchDog] - MysqlDataWriter MYSQL_DATA_WRITER0 FINISHED_OK
INFO [WatchDog] - %cpu:.. In:0 7077 5774 2359 1924
INFO [WatchDog] - ---------------------------------** End of Log **--------------------------------
INFO [WatchDog] - Execution of phase [0] successfully finished - elapsed time(sec): 3
INFO [WatchDog] - Post-execute finalization of connection:
INFO [WatchDog] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@552a66ea]:jndi[null]:url[jdbc:mysql://localhost:3306/]:user[root] ... OK
INFO [WatchDog] - -----------------------** Summary of Phases execution **---------------------
INFO [WatchDog] - Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
INFO [WatchDog] - 0 FINISHED_OK 3 17941
INFO [WatchDog] - ------------------------------** End of Summary **---------------------------
INFO [WatchDog] - WatchDog thread finished - total execution time: 3 (sec)
INFO [main] - Freeing graph resources.
INFO [main] - Execution of graph successful !
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="gary" created="Tue Apr 03 19:53:37 PDT 2012" guiVersion="3.3.0.M1" id="1333510965501" licenseCode="CLDEXU13901FD86D2EX" licenseType="Evaluation" modified="Thu Apr 05 13:18:02 PDT 2012" modifiedBy="gary" name="LoadCustomers" revision="1.46" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter=";" name="Brigston_contact" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field eofAsDelimiter="false" name="id" nullable="true" shift="0" size="0" type="long"/>
<Field eofAsDelimiter="false" name="version" nullable="true" shift="0" size="0" type="long"/>
<Field eofAsDelimiter="false" name="address" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="address2" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="cell" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="city" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="company" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="created_by" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd HH:mm:ss" name="date_created" nullable="true" shift="0" size="0" type="date"/>
<Field eofAsDelimiter="false" name="email" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="fax" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="first_name" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd HH:mm:ss" name="follow_up_date" nullable="true" shift="0" size="0" type="date"/>
<Field eofAsDelimiter="false" name="follow_up_user" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="is_active" nullable="true" shift="0" size="0" type="boolean"/>
<Field eofAsDelimiter="false" name="last_name" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd HH:mm:ss" name="last_updated" nullable="true" shift="0" size="0" type="date"/>
<Field eofAsDelimiter="false" name="max_deal_size" nullable="true" shift="0" size="0" type="long"/>
<Field eofAsDelimiter="false" name="memo" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="migrated_key" nullable="true" shift="0" size="0" type="integer"/>
<Field eofAsDelimiter="false" name="min_deal_size" nullable="true" shift="0" size="0" type="long"/>
<Field eofAsDelimiter="false" name="notes" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="phone" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="phone_alt" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="state_id" nullable="true" shift="0" size="0" type="long"/>
<Field eofAsDelimiter="false" name="title" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="type" nullable="true" shift="0" size="0" type="integer"/>
<Field eofAsDelimiter="false" name="updated_by" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="website" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="zipcode" nullable="true" shift="0" size="0" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata2" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter=";" name="SPTM_Person" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field eofAsDelimiter="false" name="personid" nullable="true" shift="0" size="10" type="integer"/>
<Field eofAsDelimiter="false" name="firstname" nullable="true" shift="0" size="30" type="string"/>
<Field eofAsDelimiter="false" name="lastname" nullable="true" shift="0" size="30" type="string"/>
<Field eofAsDelimiter="false" name="type" nullable="true" shift="0" size="10" type="integer"/>
<Field eofAsDelimiter="false" name="company" nullable="true" shift="0" size="255" type="string"/>
<Field eofAsDelimiter="false" name="title" nullable="true" shift="0" size="100" type="string"/>
<Field eofAsDelimiter="false" name="address" nullable="true" shift="0" size="255" type="string"/>
<Field eofAsDelimiter="false" name="address2" nullable="true" shift="0" size="255" type="string"/>
<Field eofAsDelimiter="false" name="city" nullable="true" shift="0" size="100" type="string"/>
<Field eofAsDelimiter="false" name="stateid" nullable="true" shift="0" size="10" type="integer"/>
<Field eofAsDelimiter="false" name="zipcode" nullable="true" shift="0" size="10" type="string"/>
<Field eofAsDelimiter="false" name="phone" nullable="true" shift="0" size="30" type="string"/>
<Field eofAsDelimiter="false" name="cell" nullable="true" shift="0" size="30" type="string"/>
<Field eofAsDelimiter="false" name="phonealt" nullable="true" shift="0" size="20" type="string"/>
<Field eofAsDelimiter="false" name="fax" nullable="true" shift="0" size="20" type="string"/>
<Field eofAsDelimiter="false" name="email" nullable="true" shift="0" size="80" type="string"/>
<Field eofAsDelimiter="false" name="notes" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="website" nullable="true" shift="0" size="100" type="string"/>
<Field eofAsDelimiter="false" name="isactive" nullable="true" shift="0" size="1" type="integer"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd HH:mm:ss" name="added" nullable="true" shift="0" size="19" type="date"/>
<Field eofAsDelimiter="false" name="addedby" nullable="true" shift="0" size="15" type="string"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd HH:mm:ss" name="modified" nullable="true" shift="0" size="19" type="date"/>
<Field eofAsDelimiter="false" name="modifiedby" nullable="true" shift="0" size="15" type="string"/>
<Field eofAsDelimiter="false" name="mindealsize" nullable="true" shift="0" size="20" type="long"/>
<Field eofAsDelimiter="false" name="maxdealsize" nullable="true" shift="0" size="20" type="long"/>
<Field eofAsDelimiter="false" format="yyyy-MM-dd" name="followupdate" nullable="true" shift="0" size="10" type="date"/>
<Field eofAsDelimiter="false" name="followupuser" nullable="true" shift="0" size="50" type="string"/>
<Field eofAsDelimiter="false" name="memo" nullable="true" shift="0" size="0" type="string"/>
<Field eofAsDelimiter="false" name="migratedkey" nullable="true" shift="0" size="10" type="integer"/>
</Record>
</Metadata>
<Connection database="MYSQL" dbURL="jdbc:mysql://localhost:3306/" id="JDBC0" jdbcSpecific="MYSQL" name="root @ localhost" password="" type="JDBC" user="root"/>
<Property fileURL="workspace.prm" id="GraphParameter0"/>
<Dictionary/>
</Global>
<Phase number="0">
<Node dbConnection="JDBC0" enabled="enabled" guiHeight="65" guiName="DBInputTable" guiWidth="128" guiX="17" guiY="15" id="DB_INPUT_TABLE0" sqlQuery="select * from \`sptm\`.\`person\`" type="DB_INPUT_TABLE"/>
<Node database="brigston" enabled="enabled" guiHeight="65" guiName="MysqlDataWriter" guiWidth="128" guiX="398" guiY="198" host="localhost" id="MYSQL_DATA_WRITER0" mysqlPath="/usr/local/mysql/bin/mysql" password="temppass" table="contact" type="MYSQL_DATA_WRITER" username="root"/>
<Node enabled="enabled" guiHeight="65" guiName="Reformat" guiWidth="128" guiX="200" guiY="99" id="REFORMAT1" type="REFORMAT">
<attr name="transform"><![CDATA[//#CTL2
// Transforms input record into output record.
function integer transform() {
$out.0.id = $in.0.personid;
$out.0.first_name = $in.0.firstname;
$out.0.last_name = $in.0.lastname;
$out.0.type = $in.0.type;
$out.0.company = $in.0.company;
$out.0.title = $in.0.title;
$out.0.address = $in.0.address;
$out.0.address2 = $in.0.address2;
$out.0.city = $in.0.city;
$out.0.state_id = $in.0.stateid;
$out.0.zipcode = $in.0.zipcode;
$out.0.phone = $in.0.phone;
$out.0.cell = $in.0.cell;
$out.0.phone_alt = $in.0.phonealt;
$out.0.fax = $in.0.fax;
$out.0.email = $in.0.email;
$out.0.notes = $in.0.notes;
$out.0.website = $in.0.website;
$out.0.is_active = num2bool($in.0.isactive);
$out.0.date_created = $in.0.added;
$out.0.created_by = $in.0.addedby;
$out.0.last_updated = $in.0.modified;
$out.0.updated_by = $in.0.modifiedby;
$out.0.min_deal_size = $in.0.mindealsize;
$out.0.max_deal_size = $in.0.maxdealsize;
$out.0.follow_up_date = $in.0.followupdate;
$out.0.follow_up_user = $in.0.followupuser;
$out.0.memo = $in.0.memo;
$out.0.migrated_key = $in.0.migratedkey;
return OK;
}
// Called during component initialization.
// function boolean init() {}
// Called during each graph run before the transform is executed. May be used to allocate and initialize resources
// required by the transform. All resources allocated within this method should be released
// by the postExecute() method.
// function void preExecute() {}
// Called only if transform() throws an exception.
// function integer transformOnError(string errorMessage, string stackTrace) {}
// Called during each graph run after the entire transform was executed. Should be used to free any resources
// allocated within the preExecute() method.
// function void postExecute() {}
// Called to return a user-defined error message when an error occurs.
// function string getMessage() {}
]]></attr>
</Node>
<Edge debugMode="true" fromNode="DB_INPUT_TABLE0:0" guiBendpoints="" guiRouter="Manhattan" id="Edge3" inPort="Port 0 (in)" metadata="Metadata2" outPort="Port 0 (out)" toNode="REFORMAT1:0"/>
<Edge debugLastRecords="true" debugMode="true" debugSampleData="false" fromNode="REFORMAT1:0" guiBendpoints="" guiRouter="Manhattan" id="Edge4" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="MYSQL_DATA_WRITER0:0"/>
</Phase>
</Graph>