Our solution needs to keep track of all updates to the database, and servoy’s logging (tracking, audit trail) feature is very handy for this. However there are a few mysteries.
For a new record added, we find a weird value in the ‘old_data’ field of the log table. There shouldn’t be any old data when adding a new record, should there? This seems to be some kind of an ID. When deleting a record, the ‘new_data’ field is empty, as expected. Is there a meaning to this unknown value, or should we just ignore it?
We would like to list the updates the user has made to the database before she commits a transaction. But the log table seems to be written to only after commiting the transaction. Is there a way to keep track of updates during a transaction?
As I mentioned, we are using a transaction combined with servoy’s logging feature. Now we found out that a random number of log rows are LOST every time a transaction is committed. Sometimes log_id:s are skipped, sometimes not. We are using MySQL 4.1 and Servoy 2.1.1.
The tables that are being tracked to log contain composite primary keys. So for every row added or deleted, there appear n rows in the log table (n = the number of fields in a composite key).
Has anyone tried out a combination of logging (tracking), composite keys and transactions in Servoy?
Ok, I wrote a test solution to see if I can reproduce there problems, and it seems that this might be a bug of Servoy. Rows of tracking information (log table) are disappearing randomly. At first everything seems to work but soon we get unpredictable behaviour. Can someone at Servoy review this issue? This is related to a very important feature in our solution.
Attached is a sample solution with three records. Tracking needs to be turned on at the test_form table. For testing I kept repeating a procedure where I started a transaction, deleted one of the records, duplicated one and modified the new one. After commiting the transaction there appear always a different amount of rows in the log table (i read the log straight from database monitor).
i have made some small changes and i never get strange row data in the record for old data with a new row. Also i always get 10 rows if i do delete a record, dup a record and change 3 values in it.
about point 2. this is currently not possible, if you want this you have to track it somehow youreself.
Thanks for your help! The strange IDs seem to have disappeared now, and the PK data is clearer. But we are still missing log lines especially in the complex solution we are working on. I can send you a log dump to demonstrate what I’m talking about.
Did a bit more testing and found also that I get some errors in Java console. The more log lines there are to be created during a transaction, the more probable it is to cause this error. I can reproduce this also with the previously attached test solution just by doing at least two record deletions, two insertions, a few updates, all in a transaction.
The following appears in java console just at the moment when the transaction is commited. The key 3876 seems to be log_id.
java.sql.SQLException: no transaction in progress
java.sql.SQLException: no transaction in progress
at com.servoy.j2db.persistence.datasource.TransactionConnection.rollback(Unknown Source)
at com.servoy.j2db.util.Utils.rollback(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.access$2(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine$c.run(Unknown Source)
at com.servoy.j2db.util.aq.run(Unknown Source)
at java.lang.Thread.run(Thread.java:534)
java.sql.SQLException: Duplicate key or integrity constraint violation, message from server: "Duplicate entry '3876' for key 1"
java.sql.SQLException: Duplicate key or integrity constraint violation, message from server: "Duplicate entry '3876' for key 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1588)
at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy0.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.access$2(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine$c.run(Unknown Source)
at com.servoy.j2db.util.aq.run(Unknown Source)
at java.lang.Thread.run(Thread.java:534)
java.sql.SQLException: no transaction in progress
java.sql.SQLException: no transaction in progress
at com.servoy.j2db.persistence.datasource.TransactionConnection.rollback(Unknown Source)
at com.servoy.j2db.util.Utils.rollback(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.access$2(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine$c.run(Unknown Source)
at com.servoy.j2db.util.aq.run(Unknown Source)
at java.lang.Thread.run(Thread.java:534)
java.sql.SQLException: Duplicate key or integrity constraint violation, message from server: "Duplicate entry '3876' for key 1"
java.sql.SQLException: Duplicate key or integrity constraint violation, message from server: "Duplicate entry '3876' for key 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1588)
at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy0.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.access$2(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine$c.run(Unknown Source)
at com.servoy.j2db.util.aq.run(Unknown Source)
at java.lang.Thread.run(Thread.java:534)
I think at this point this thread should be moved to bug track forum!