1 2 Previous Next 15 Replies Latest reply: Apr 23, 2013 11:39 AM by jschellSomeoneStoleMyAlias RSS

    How insert into table select from table works in jdbc driver?

    914264

      Hi, Supposing one table has two LOB fields, one LOB field is BLOB type while the other is CLOB type, I use the following sql statement to copy a row into a new row:
      ---------------------------------------------------
      insert into table (id, file_body, file_content) select new_id, file_body, file_content from table where id = '111';
      ---------------------------------------------------

      After commit on the connection, I can see the copied record in the table and both LOB fields are not null and this's the expected behavior.
      However after some days later, the copy function becomes to be a problem, the BLOB field named file_body can be null when the copy job is done, while other fields copy successfully.
      The issue can not be reproduced every time.
      I suppose the jdbc driver may try to allocate byte buffer in the heap to perform copy operation for BLOB fields,if there is no enough memory available in the heap the copy operation may fail but the commit on the connection can be successful.b/c I can see a lot of OOM errors in the log files and I believe this can contribute to the issue.

      Hope someone can give me a hint or comments.


      Thanks,
      SuoNayi

        • 1. Re: How insert into table select from table works in jdbc driver?
          914264
          I want to figure out what's memory leak point and I have tried the following solutions but none worked:
          1.I have tried to dump the memory of the JVM but failed,I can see the following errors :
          ------------------------------------------------
          [root@localhost xxx]# jmap -heap:format=b 3027
          Attaching to process ID 3027, please wait...
          Debugger attached successfully.
          Server compiler detected.
          JVM version is 1.5.0_16-b02
          Unknown oop at 0x00002b21a24cd550
          Oop's klass is null
          Finding object size using Printezis bits and skipping over...
          Unknown oop at 0x00002b21a3634380
          Oop's klass is null
          Finding object size using Printezis bits and skipping over...

          ------------------------------------------------
          2.and the thread stack can not be dumped successfully as well:
          -----------------------------------------------------------
          Thread 3046: (state = BLOCKED)
          - java.lang.Object.wait(long) @bci=0 (Compiled frame; information may be imprecise)
          Error occurred during stack walking:
          ----------------------------------------------
          the version of java is:
          -------------------------------------------------
          java version "1.5.0_16"
          Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_16-b02)
          Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_16-b02, mixed mode)
          -------------------------------------------------

          I have to added dump thread stack option in JVM arguments, -XX:+HeapDumpOnOutOfMemoryError.

          If there are other solutions please let me know, thanks.
          • 2. Re: How insert into table select from table works in jdbc driver?
            gimbal2
            I will assume you are talking about Oracle here, since you make no mention yourself which DBMS this is. And since you are running a shockingly old version of Java, I must assume you are also using a shockingly old driver. Try a newer driver release.
            • 3. Re: How insert into table select from table works in jdbc driver?
              Tolls
              SuoNayi wrote:
              Hi, Supposing one table has two LOB fields, one LOB field is BLOB type while the other is CLOB type, I use the following sql statement to copy a row into a new row:
              ---------------------------------------------------
              insert into table (id, file_body, file_content) select new_id, file_body, file_content from table where id = '111';
              ---------------------------------------------------
              If that is your actual query, and the only thing that is possibly bound to this is the id at the end, then all the LOB work is done in the database, so...
              I suppose the jdbc driver may try to allocate byte buffer in the heap to perform copy operation for BLOB fields,if there is no enough memory available in the heap the copy operation may fail but the commit on the connection can be successful.b/c I can see a lot of OOM errors in the log files and I believe this can contribute to the issue.
              This cannot be the case, as the LOBs will nto get anywhere near your Java code.
              So something else is going on with your code that you haven't mentioned here, and it's possible that "something else" is the cause of your null BLOBs.
              • 4. Re: How insert into table select from table works in jdbc driver?
                914264
                Hi, Tolls, are you telling me that the LOB copy is happening in the database so it's not the problem of the application?
                But what can be reasons that the CLOB field is copied while the BLOB field is null with a successful commit?
                I ensure that the BLOB field of the original row is not null as I can export it's content and open it with Microsoft office word.
                • 5. Re: How insert into table select from table works in jdbc driver?
                  914264
                  Try a new version driver release may not help me to figure out what's the memory leak point...
                  • 6. Re: How insert into table select from table works in jdbc driver?
                    Tolls
                    SuoNayi wrote:
                    Hi, Tolls, are you telling me that the LOB copy is happening in the database so it's not the problem of the application?
                    insert into table (id, file_body, file_content) select new_id, file_body, file_content from table where id = '111';
                    That SQL there, if it is what is being used in a Java Statement or PreparedStatement, is run in the database.
                    Note, I am having to make some assumtpions here about exactly what your code looks like as you haven't actually shown us any Java code.
                    But what can be reasons that the CLOB field is copied while the BLOB field is null with a successful commit?
                    No idea.
                    You'll need to do some debugging.
                    I ensure that the BLOB field of the original row is not null as I can export it's content and open it with Microsoft office word.
                    • 7. Re: How insert into table select from table works in jdbc driver?
                      gimbal2
                      SuoNayi wrote:
                      Try a new version driver release may not help me to figure out what's the memory leak point...
                      No but it may take away the leak itself. That's your ultimate goal isn't it? And if upgrading the driver doesn't help then at least you have more reason to suspect it is something you're doing wrong yourself.

                      Although so far I see not so much reason to assume that the query problem and the out of memory problem are the same problem.
                      • 8. Re: How insert into table select from table works in jdbc driver?
                        914264
                        Yes, that SQL is executed in the Java Statement.
                        I have read the source code many times and it works very well in the test environment always.
                        It only happens in the production environment and I found may OOM erros in the log files.
                        Seems my assumption is not right now I have no idea what's up...
                        :(
                        • 9. Re: How insert into table select from table works in jdbc driver?
                          Tolls
                          For the OOM then (IMO) your first port of call is analysing a heap dump.
                          Until you've done that then you'll be simply guessing as to what's leaking.
                          • 10. Re: How insert into table select from table works in jdbc driver?
                            rp0428
                            >
                            Yes, that SQL is executed in the Java Statement.
                            >
                            Then Java is NOT involved at all.

                            You have STILL not mentioned the name and full version of the database you are using or provided the actual Java code you are using.
                            • 11. Re: How insert into table select from table works in jdbc driver?
                              914264
                              The full version of the database is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.
                              I can ensure that sql is executed in the Java Statement but I can not provide the source code since the platform is xml-oriented.
                              • 12. Re: How insert into table select from table works in jdbc driver?
                                914264
                                Thank you all.I have figure out what's the cause of the null value Blob field.
                                But I have not succeed in finding out what's the memory leak point.
                                Since the current JDK will output above errors when I perform a jmap or jstack command,I will consider upgrade the JDK from 1.5.0_16 to 1.5.0_22.
                                • 13. Re: How insert into table select from table works in jdbc driver?
                                  rp0428
                                  >
                                  I have figure out what's the cause of the null value Blob field.
                                  >
                                  Then post what the solution was.

                                  This forum is to help everyone; not just you. Do your part to help others.
                                  • 14. Re: How insert into table select from table works in jdbc driver?
                                    914264
                                    There is a unsafe modification on the row in the other thread that will update the Blob field to null.
                                    It's a bug of the application and I'm sorry for making incorrect assumptions and thank for your help.
                                    1 2 Previous Next