[jira] [Commented] (DERBY-7042) Multiple triggers with rowLocking = false causes deadlock
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808236#comment-16808236
]
Rick Hillegas commented on DERBY-7042:
--
Attaching Derby7042.java--this is the Database.java class from the git site. I
have simply renamed the class. When I run the repro, it produces the following
output:
{noformat}
Hello World
Insert time: 15 seconds
Hello World
Update time: 15 seconds
Gorilla 3 true Cincinnati
Hello World
{noformat}
Can you explain how this is evidence of the bug? Thanks.
> Multiple triggers with rowLocking = false causes deadlock
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
> Attachments: Derby7042.java
>
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes deadlock when there is an Insert or Delete
> trigger
> # Executing Insert trigger causes deadlock when there is a Delete trigger
> # Executing Delete trigger does not cause deadlock
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> # While executing issue above, none of the triggers were terminated and
> waitTimeout time was hit
> h2. Conclusions Based on Results
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> Triggers seem to get into deadlock scenarios with any trigger defined after
> itself. If this is the case, it should be documented somewhere that
> rowLocking needs to be enabled to use the trigger feature if multiple
> triggers would be used on the same database table.
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> Based on documentation, I could not find any concrete evidence of whether
> this is intended functionality.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (DERBY-7042) Multiple triggers with rowLocking = false causes deadlock
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-7042:
-
Attachment: Derby7042.java
> Multiple triggers with rowLocking = false causes deadlock
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
> Attachments: Derby7042.java
>
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes deadlock when there is an Insert or Delete
> trigger
> # Executing Insert trigger causes deadlock when there is a Delete trigger
> # Executing Delete trigger does not cause deadlock
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> # While executing issue above, none of the triggers were terminated and
> waitTimeout time was hit
> h2. Conclusions Based on Results
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> Triggers seem to get into deadlock scenarios with any trigger defined after
> itself. If this is the case, it should be documented somewhere that
> rowLocking needs to be enabled to use the trigger feature if multiple
> triggers would be used on the same database table.
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> Based on documentation, I could not find any concrete evidence of whether
> this is intended functionality.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-7041) null pointer exception when creating view based on other views
[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808221#comment-16808221
]
Rick Hillegas commented on DERBY-7041:
--
I can port the patch to the 10.14 branch if that helps you. But you are
correct, I have no plans to manage a 10.14.2 release at this time.
> null pointer exception when creating view based on other views
> --
>
> Key: DERBY-7041
> URL: https://issues.apache.org/jira/browse/DERBY-7041
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.14.2.0
> Environment: max os x , intellij
>Reporter: Manuel Rossetti
>Priority: Major
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip,
> derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff, derby-7041.sql
>
>
> I can execute a SELECT query that works but when I try to create a view on
> that select query, I simply get a java null pointer exception with no
> details. I have tested the same statements on a postgres database and they
> worked without error.
> Below, the PW_DIFF_WITHIN_REP_VIEW create does work, but the
> PW_DIFF_AR_REP_VIEW does not. Again, the SELECT clause of
> PW_DIFF_AR_REP_VIEW will work, but when used within the create clause the
> error occurs.
> I have attached the entire database creation script and an embedded instance
> that can be used for testing. It has data.
>
> -- WITHIN_REP_VIEW combines the WITHIN_REP_COUNTER_VIEW and
> WITHIN_REP_RESPONSE_VIEW into one table from which across
> -- replication or other statistical summaries by replication can be produced
> CREATE VIEW JSL_DB.WITHIN_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, VALUE) AS
> (SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, AVERAGE AS VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_STAT.ELEMENT_ID_FK
> UNION
> SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, LAST_VALUE as VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_COUNTER_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_COUNTER_STAT.ELEMENT_ID_FK);
>
> -- PW_DIFF_WITHIN_REP_VIEW computes the pairwise differences across
> difference simulation experiments
> -- doesn't work for derby, 3-28-2019, works for postgres
> --
> -- create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- as (select SIMULATION_RUN.SIM_NAME, A.SIM_RUN_ID_FK AS A_SIM_NUM,
> A.STAT_NAME, A.EXP_NAME as A_EXP_NAME, A.REP_NUM, A.VALUE as A_VALUE,
> -- B.SIM_RUN_ID_FK as B_SIM_NUM, B.EXP_NAME as B_EXP_NAME, B.VALUE as B_VALUE,
> -- '(' || A.EXP_NAME || ' - ' || B.EXP_NAME || ')' as DIFF_NAME, (A.VALUE -
> B.VALUE) as A_MINUS_B
> -- from JSL_DB.WITHIN_REP_VIEW as A, JSL_DB.WITHIN_REP_VIEW as B,
> JSL_DB.SIMULATION_RUN
> -- where A.SIM_RUN_ID_FK = JSL_DB.SIMULATION_RUN.ID
> -- and A.STAT_NAME = B.STAT_NAME
> -- and A.REP_NUM = B.REP_NUM
> -- and A.SIM_RUN_ID_FK > B.SIM_RUN_ID_FK
> -- and A.ELEMENT_NAME = B.ELEMENT_NAME);
> --
> -- create view JSL_DB.PW_DIFF_AR_REP_VIEW (SIM_NAME, STAT_NAME, A_EXP_NAME,
> B_EXP_NAME, DIFF_NAME, AVG_A, STD_DEV_A,
> -- AVG_B, STD_DEV_B, AVG_DIFF_A_MINUS_B, STD_DEV_DIFF_A_MINUS_B, STAT_COUNT)
> -- as (select SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME,
> AVG(A_VALUE) as AVG_A, STDDEV_SAMP(A_VALUE) as STD_DEV_A,
> -- AVG(B_VALUE) as AVG_B, STDDEV_SAMP(B_VALUE) as STD_DEV_B,
> -- AVG(A_MINUS_B) as AVG_DIFF_A_MINUS_B, STDDEV_SAMP(A_MINUS_B) as
> STD_DEV_DIFF_A_MINUS_B,
> -- COUNT(A_MINUS_B) as STAT_COUNT
> -- from JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- group by SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME);
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (DERBY-7042) Multiple triggers with rowLocking = false causes deadlock
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Schuetze updated DERBY-7042:
Summary: Multiple triggers with rowLocking = false causes deadlock (was:
Multiple triggers with rowLocking = false)
> Multiple triggers with rowLocking = false causes deadlock
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes deadlock when there is an Insert or Delete
> trigger
> # Executing Insert trigger causes deadlock when there is a Delete trigger
> # Executing Delete trigger does not cause deadlock
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> # While executing issue above, none of the triggers were terminated and
> waitTimeout time was hit
> h2. Conclusions Based on Results
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> Triggers seem to get into deadlock scenarios with any trigger defined after
> itself. If this is the case, it should be documented somewhere that
> rowLocking needs to be enabled to use the trigger feature if multiple
> triggers would be used on the same database table.
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> Based on documentation, I could not find any concrete evidence of whether
> this is intended functionality.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (DERBY-7042) Multiple triggers with rowLocking = false
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Schuetze updated DERBY-7042:
Description:
Repro for the bug can be found here:
[https://github.com/mjschuetze102/TriggerRepro]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
was:
Repro for the bug can be found here:
[[https://github.com/mjschuetze102/TriggerRepro||http://example.com/]
[https://github.com/mjschuetze102/TriggerRepro|http://example.com/]
[]|http://example.com/]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
> Multiple triggers with rowLocking = false
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes
[jira] [Updated] (DERBY-7042) Multiple triggers with rowLocking = false
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Schuetze updated DERBY-7042:
Description:
Repro for the bug can be found here:
[[https://github.com/mjschuetze102/TriggerRepro||http://example.com/]
[https://github.com/mjschuetze102/TriggerRepro|http://example.com/]
[]|http://example.com/]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
was:
Repro for the bug can be found here:
[https://github.com/mjschuetze102/TriggerRepro|http://example.com]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
> Multiple triggers with rowLocking = false
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
>
> Repro for the bug can be found here:
> [[https://github.com/mjschuetze102/TriggerRepro||http://example.com/]
> [https://github.com/mjschuetze102/TriggerRepro|http://example.com/]
> []|http://example.com/]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having
[jira] [Updated] (DERBY-7042) Multiple triggers with rowLocking = false
[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Schuetze updated DERBY-7042:
Description:
Repro for the bug can be found here:
[https://github.com/mjschuetze102/TriggerRepro|http://example.com]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
was:
Repro for the bug can be found here:
[https://github.com/mjschuetze102/TriggerRepro
]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
> Multiple triggers with rowLocking = false
> -
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
>Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: -- Java Information --
> Java Version:1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> - Derby Information
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
>Reporter: Michael Schuetze
>Priority: Minor
> Labels: documentation, performance
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro|http://example.com]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes deadlock when there is an Insert or Delete
> trigger
> # Executing Insert
[jira] [Created] (DERBY-7042) Multiple triggers with rowLocking = false
Michael Schuetze created DERBY-7042:
---
Summary: Multiple triggers with rowLocking = false
Key: DERBY-7042
URL: https://issues.apache.org/jira/browse/DERBY-7042
Project: Derby
Issue Type: Bug
Components: Documentation, JDBC
Affects Versions: 10.15.1.3, 10.14.2.0, 10.14.1.0, 10.13.1.1, 10.12.1.1,
10.11.1.1, 10.10.2.0, 10.10.1.1, 10.9.1.0, 10.8.3.0, 10.8.2.2
Environment: -- Java Information --
Java Version:1.8.0_201
Java Vendor: Oracle Corporation
OS name: Linux
OS architecture: i386
OS version: 4.15.0-46-generic
java.specification.name: Java Platform API Specification
java.specification.version: 1.8
java.runtime.version: 1.8.0_201-b09
- Derby Information
[.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
Reporter: Michael Schuetze
Repro for the bug can be found here:
[https://github.com/mjschuetze102/TriggerRepro
]
Includes a detailed README of steps that show effect of bug as well as two
versions of the Derby database. 10.8.1.2, the last version where the bug was
not present and version 10.14.1.0, for easy access.
This may just be an issue of not having enough documentation on database
Triggers (see Conclusions Based on Results)
h2. Summary of Issue
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
# Executing Update trigger causes deadlock when there is an Insert or Delete
trigger
# Executing Insert trigger causes deadlock when there is a Delete trigger
# Executing Delete trigger does not cause deadlock
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
# While executing issue above, none of the triggers were terminated and
waitTimeout time was hit
h2. Conclusions Based on Results
*Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
issues with deadlocking*
Triggers seem to get into deadlock scenarios with any trigger defined after
itself. If this is the case, it should be documented somewhere that rowLocking
needs to be enabled to use the trigger feature if multiple triggers would be
used on the same database table.
*{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
Based on documentation, I could not find any concrete evidence of whether this
is intended functionality.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-7041) null pointer exception when creating view based on other views
[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807939#comment-16807939
]
Manuel Rossetti commented on DERBY-7041:
It looks like your plan is to put this patch in 10.15 and not patch 10.14.2. Is
that correct?
> null pointer exception when creating view based on other views
> --
>
> Key: DERBY-7041
> URL: https://issues.apache.org/jira/browse/DERBY-7041
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.14.2.0
> Environment: max os x , intellij
>Reporter: Manuel Rossetti
>Priority: Major
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip,
> derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff, derby-7041.sql
>
>
> I can execute a SELECT query that works but when I try to create a view on
> that select query, I simply get a java null pointer exception with no
> details. I have tested the same statements on a postgres database and they
> worked without error.
> Below, the PW_DIFF_WITHIN_REP_VIEW create does work, but the
> PW_DIFF_AR_REP_VIEW does not. Again, the SELECT clause of
> PW_DIFF_AR_REP_VIEW will work, but when used within the create clause the
> error occurs.
> I have attached the entire database creation script and an embedded instance
> that can be used for testing. It has data.
>
> -- WITHIN_REP_VIEW combines the WITHIN_REP_COUNTER_VIEW and
> WITHIN_REP_RESPONSE_VIEW into one table from which across
> -- replication or other statistical summaries by replication can be produced
> CREATE VIEW JSL_DB.WITHIN_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, VALUE) AS
> (SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, AVERAGE AS VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_STAT.ELEMENT_ID_FK
> UNION
> SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, LAST_VALUE as VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_COUNTER_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_COUNTER_STAT.ELEMENT_ID_FK);
>
> -- PW_DIFF_WITHIN_REP_VIEW computes the pairwise differences across
> difference simulation experiments
> -- doesn't work for derby, 3-28-2019, works for postgres
> --
> -- create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- as (select SIMULATION_RUN.SIM_NAME, A.SIM_RUN_ID_FK AS A_SIM_NUM,
> A.STAT_NAME, A.EXP_NAME as A_EXP_NAME, A.REP_NUM, A.VALUE as A_VALUE,
> -- B.SIM_RUN_ID_FK as B_SIM_NUM, B.EXP_NAME as B_EXP_NAME, B.VALUE as B_VALUE,
> -- '(' || A.EXP_NAME || ' - ' || B.EXP_NAME || ')' as DIFF_NAME, (A.VALUE -
> B.VALUE) as A_MINUS_B
> -- from JSL_DB.WITHIN_REP_VIEW as A, JSL_DB.WITHIN_REP_VIEW as B,
> JSL_DB.SIMULATION_RUN
> -- where A.SIM_RUN_ID_FK = JSL_DB.SIMULATION_RUN.ID
> -- and A.STAT_NAME = B.STAT_NAME
> -- and A.REP_NUM = B.REP_NUM
> -- and A.SIM_RUN_ID_FK > B.SIM_RUN_ID_FK
> -- and A.ELEMENT_NAME = B.ELEMENT_NAME);
> --
> -- create view JSL_DB.PW_DIFF_AR_REP_VIEW (SIM_NAME, STAT_NAME, A_EXP_NAME,
> B_EXP_NAME, DIFF_NAME, AVG_A, STD_DEV_A,
> -- AVG_B, STD_DEV_B, AVG_DIFF_A_MINUS_B, STD_DEV_DIFF_A_MINUS_B, STAT_COUNT)
> -- as (select SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME,
> AVG(A_VALUE) as AVG_A, STDDEV_SAMP(A_VALUE) as STD_DEV_A,
> -- AVG(B_VALUE) as AVG_B, STDDEV_SAMP(B_VALUE) as STD_DEV_B,
> -- AVG(A_MINUS_B) as AVG_DIFF_A_MINUS_B, STDDEV_SAMP(A_MINUS_B) as
> STD_DEV_DIFF_A_MINUS_B,
> -- COUNT(A_MINUS_B) as STAT_COUNT
> -- from JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- group by SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME);
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-7041) null pointer exception when creating view based on other views
[
https://issues.apache.org/jira/browse/DERBY-7041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807756#comment-16807756
]
ASF subversion and git services commented on DERBY-7041:
Commit 1856824 from Richard N. Hillegas in branch 'code/branches/10.15'
[ https://svn.apache.org/r1856824 ]
DERBY-7041: Port 1856730 from trunk to the 10.15 branch.
> null pointer exception when creating view based on other views
> --
>
> Key: DERBY-7041
> URL: https://issues.apache.org/jira/browse/DERBY-7041
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.14.2.0
> Environment: max os x , intellij
>Reporter: Manuel Rossetti
>Priority: Major
> Attachments: JSLDb.sql, JSLDb.sql, JSLDb_DriveThroughPharmacy.zip,
> derby-7041-01-aa-omitDependencyOnSystemSuppliedAggregate.diff, derby-7041.sql
>
>
> I can execute a SELECT query that works but when I try to create a view on
> that select query, I simply get a java null pointer exception with no
> details. I have tested the same statements on a postgres database and they
> worked without error.
> Below, the PW_DIFF_WITHIN_REP_VIEW create does work, but the
> PW_DIFF_AR_REP_VIEW does not. Again, the SELECT clause of
> PW_DIFF_AR_REP_VIEW will work, but when used within the create clause the
> error occurs.
> I have attached the entire database creation script and an embedded instance
> that can be used for testing. It has data.
>
> -- WITHIN_REP_VIEW combines the WITHIN_REP_COUNTER_VIEW and
> WITHIN_REP_RESPONSE_VIEW into one table from which across
> -- replication or other statistical summaries by replication can be produced
> CREATE VIEW JSL_DB.WITHIN_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, VALUE) AS
> (SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, AVERAGE AS VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_STAT.ELEMENT_ID_FK
> UNION
> SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME,
> STAT_NAME, REP_NUM, LAST_VALUE as VALUE
> FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_COUNTER_STAT on
> JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK)
> JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID =
> JSL_DB.WITHIN_REP_COUNTER_STAT.ELEMENT_ID_FK);
>
> -- PW_DIFF_WITHIN_REP_VIEW computes the pairwise differences across
> difference simulation experiments
> -- doesn't work for derby, 3-28-2019, works for postgres
> --
> -- create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- as (select SIMULATION_RUN.SIM_NAME, A.SIM_RUN_ID_FK AS A_SIM_NUM,
> A.STAT_NAME, A.EXP_NAME as A_EXP_NAME, A.REP_NUM, A.VALUE as A_VALUE,
> -- B.SIM_RUN_ID_FK as B_SIM_NUM, B.EXP_NAME as B_EXP_NAME, B.VALUE as B_VALUE,
> -- '(' || A.EXP_NAME || ' - ' || B.EXP_NAME || ')' as DIFF_NAME, (A.VALUE -
> B.VALUE) as A_MINUS_B
> -- from JSL_DB.WITHIN_REP_VIEW as A, JSL_DB.WITHIN_REP_VIEW as B,
> JSL_DB.SIMULATION_RUN
> -- where A.SIM_RUN_ID_FK = JSL_DB.SIMULATION_RUN.ID
> -- and A.STAT_NAME = B.STAT_NAME
> -- and A.REP_NUM = B.REP_NUM
> -- and A.SIM_RUN_ID_FK > B.SIM_RUN_ID_FK
> -- and A.ELEMENT_NAME = B.ELEMENT_NAME);
> --
> -- create view JSL_DB.PW_DIFF_AR_REP_VIEW (SIM_NAME, STAT_NAME, A_EXP_NAME,
> B_EXP_NAME, DIFF_NAME, AVG_A, STD_DEV_A,
> -- AVG_B, STD_DEV_B, AVG_DIFF_A_MINUS_B, STD_DEV_DIFF_A_MINUS_B, STAT_COUNT)
> -- as (select SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME,
> AVG(A_VALUE) as AVG_A, STDDEV_SAMP(A_VALUE) as STD_DEV_A,
> -- AVG(B_VALUE) as AVG_B, STDDEV_SAMP(B_VALUE) as STD_DEV_B,
> -- AVG(A_MINUS_B) as AVG_DIFF_A_MINUS_B, STDDEV_SAMP(A_MINUS_B) as
> STD_DEV_DIFF_A_MINUS_B,
> -- COUNT(A_MINUS_B) as STAT_COUNT
> -- from JSL_DB.PW_DIFF_WITHIN_REP_VIEW
> -- group by SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME);
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
