Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: Insert / Update Not Committing (despite saying it did)

  1. #1

    Default Insert / Update Not Committing (despite saying it did)

    My Setup
    --------------------------
    PDI version 4.1.0
    Linux
    MySQL 5.1
    Java 1.6

    My Issue
    ------------------------------------------------------------------

    Short Desc:
    Insert / Update finished successfully, but no data written?

    Long Desc:
    I have a Kettle job that does an Insert/Update.
    The job runs, shows data in the stream (I logged to check), and logs "All transactions of database connection... where committed at the end of the transformation"

    HOWEVER

    The MySQL binlog show no inserts or updates.
    The next job in my chain can not find any of those items inserted above.

    This problem is occasional, and only occurs a few times a day or even every other day.

    Any insights would be appreciated.

  2. #2
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    uncheck "use batch updates" and try again. if that fixes it then upgrade to pdi 4.3.0 where it's fixed.
    (although, the bug i'm thinking of only applied to the "update" step, not the "insert/update" step.. )

  3. #3

    Default

    Will do and report back soon.

    In your experience have you seen this bug be this inconsistent (failing 2-5 times a day out of 48 runs) ?
    If it failed everytime I would be much happier.

  4. #4

    Default

    We have rev'd up to PDI 4.2 and the problem still occurs.
    The "Insert / Update " block does not have a "use batch updates" setting, so that as a fix was not available to me.

    I am running ~100 tranformations and most of them depend on insert/update, can this block be trusted?

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I remember an odd-case bug, which might be the one codek is mentioning.

    Try setting the commit size to 1, and run one transformation (that has been failing).
    It will run *VERY* slowly.

    If this has almost all the rows, then it is the bug that I am thinking of, and you need to upgrade to a very recent (eg. 4.3.0 ) version of PDI.

  6. #6

    Default

    Yes, I am on 4.3.... that 4.2 was a typo.
    I will try commit = 1

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Are the transformations in question running "database transactional"?
    I've recently fixed a race-condition that might apply if it's the case.

  8. #8

    Default

    It is in fact marked database transactional.
    Would unchecking this prevent the bug?

    If I understand that setting correctly, I can turn it off in this job with no real effect because I have only on setp that writes/updates to the db.

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    yes, it would.

  10. #10

    Default

    SOLVED

    Unchecking "make this transaction database transactional" has worked well. All commits appear to be taking now.
    Has this bug been fixed in an available release?

    I am doing nothing but linear inserts with this job, so a failure with a non rollback doesn't worry me here, however in other situations this could be a big problem.

    Anyway, Thanks for all the help!

  11. #11
    Join Date
    Dec 2011
    Posts
    124

    Default

    Hi All,

    As of my knowledge step:-"make this transaction database transactional" is not good for all situtations. it will work for less amount of data.

    purpose of this step is commiting entire data at a time, that means it holds the data in cache and commits finally.

    let is assume if you enable this step, if you try to commit millions of records, then there is chance of connection failure to your database.

    using of "make this transaction database transactional" step is not good in all situations.

    Thanks

  12. #12
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It will be fixed in the next stable release of Kettle 4.4.0 which has a planned code-freeze in 2 weeks with a release 4-6 weeks after.

  13. #13

    Default

    Quote Originally Posted by MattCasters View Post
    It will be fixed in the next stable release of Kettle 4.4.0 which has a planned code-freeze in 2 weeks with a release 4-6 weeks after.
    Can I help test?! I am loving kettle and would like to contribute if I can.

    Thanks,
    Mark

  14. #14
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Absolutely Mark, you can always download and test a CI build: http://ci.pentaho.com/job/Kettle-4.4/

    Feedback goes here:

    http://jira.pentaho.com/browse/PDI-8298

    Thanks,

    Matt

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.