1

I'm running an integration test that executes some Hibernate code within a single transaction (managed by Spring). The test is failing with a duplicate key violation and I'd like to hit a breakpoint just before this and inspect the table contents. I can't just go into MySQL Workbench and run a SELECT query as it would be outside the transaction. Is there another way?

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • Did you try remote debugging ? – Alexander Petrov Jul 21 '16 at 15:41
  • Sure I can debug and hit a breakpoint in Eclipse - but what then? – Steve Chambers Jul 21 '16 at 15:42
  • If it is throwing with duplicate key this mean it is not in the same transaction that the insert has happened. Are you sure that this ID does not exist already ? – Alexander Petrov Jul 21 '16 at 15:43
  • The thing is that lets say you have Entity address and you make a change you call Persist within your transaction. The entity becomes managed. You make another change you call persist again, the entity is still managed. Then the session is flushed and that results in a single INSERT. It is highly unlikely that the Exception is caused by insert that has happened from within the same transaction. – Alexander Petrov Jul 21 '16 at 15:47
  • Not saying impossible, just saying unlikely. – Alexander Petrov Jul 21 '16 at 15:47
  • Can you post your ID generator ? And the entity that is failing. Is it possible that your ID generator is generating key that already exists ? – Alexander Petrov Jul 21 '16 at 15:49
  • Think you may be assuming too much about the root cause - there are a wide variety of reasons a primary key violation might occur. This is a general question about how to go about inspecting database tables when within a transaction. – Steve Chambers Jul 21 '16 at 15:50
  • Sure if you want a simple answer here it is. Reduce your database isolation level to READ_UNCOMMITED happy ? :) This way you will be able to see what is in the database when the breakpoint hits :) – Alexander Petrov Jul 21 '16 at 15:51
  • Sure I guess that's an option but could be a bit of an extreme one. There may be good reasons not to want to do this, e.g. to preserve the data in the database. Was hoping there might be some kind of "Transactions spy" tool, ideally a GUI client that allows you to see all transactions on a particular database and then drill down into them. But am guessing from the lack of responses so far there may be technical reasons this isn't possible... – Steve Chambers Jul 21 '16 at 15:57
  • Steve as long as you are working in a single thread and you are just debugging. This is not an issue. Plus you can set it on the DB connection. There is nothing to set on the server. There is absolutely nothing extreme in setting it as long as you know what you are doing. Which means no parallelism (or at least controlled one). – Alexander Petrov Jul 21 '16 at 15:59

2 Answers2

1

After reading your comments, my impression that predominantly you are interested in how to hit a breakpoint and at the same time be able to examine database contents. Under normal circumstances I would just offer you to log the SQLs. Having the breakpoint in mind my suggestion is:

Reduce isolation level to READ_UNCOMMITED for the integration test.

Reducing the isolation level will allow you to see the uncommitted values in the database during the debugging. As long as you don't have parallel activity within the integration test. It should be fine.

Isolation level can be set up on per connection basis. There is no need for anything to be done on the server.

One side note. If you are using Hibernate even the parallel activities may work fine when you reduce the ISOLATION LEVEL because largely Hibernate behaves as it is in REPEATABLE_READ because of the transactional Level 1 cache.

Alexander Petrov
  • 9,204
  • 31
  • 70
  • Thanks for the answer but this does make some assumptions about the nature of the testing and wouldn't be desirable in all circumstances. (E.g. what if there were multiple concurrent transactions in a test simulating concurrency of multiple users?) – Steve Chambers Jul 21 '16 at 16:35
  • Steve I am not completely sure what you are looking for. Give me some hints here. You don't want to log SQLs, at the same time you re saying you want to use your debugger. Obviously you have something in mind that I am not able to see. Give me some more hints. – Alexander Petrov Jul 21 '16 at 16:38
  • Maybe I'm looking for the impossible but it would be nice to be able to see tabular data in a GUI, ideally in a similar vein to how it is presented in MySQL workbench. – Steve Chambers Jul 21 '16 at 16:39
  • Have now accepted the answer. Was previously misunderstanding and thinking a code change would be needed e.g. on `@Transactional` attributes. But have now realised it works with a simple `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`. – Steve Chambers Dec 22 '16 at 11:28
0

The following can be run from Eclipse's "Display" view:

java.util.Arrays.deepToString(
  em.createNativeQuery("SELECT mystuff FROM mytable").getResultList().toArray())
  .replace("], ", "]\n");

This displays all the data, albeit not in a very user-friendly way - e.g. will need to work out which columns the comma-separated fields correspond to.

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • You realize that this will not work on a test server ? – Alexander Petrov Jul 21 '16 at 16:42
  • Not sure what you mean - but it's working for the integration test running on my local machine (which is all I require). – Steve Chambers Jul 22 '16 at 07:30
  • No display view in Eclipse Version: 2022-03 (4.23.0). But the debug shell seems to do the same as described here. See https://stackoverflow.com/a/52175408/2136633. Certainly I was able to interrogate the db from within the current transaction. – cmgharris Apr 11 '22 at 07:55