How to run openboxes in Apache Netbeans?

chandara · May 27, 2022, 6:30pm

@jmiranda Im reaching the last step.

What am I missing for the error below?
Here is my connectionstring:

////////
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.GeneratedConstructorAccessor87.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2030)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:778)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor84.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.sql.SQLException: Unknown system variable ‘storage_engine’
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.mysql.jdbc.ConnectionImpl.setSessionVariables(ConnectionImpl.java:5030)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3202)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2068)
… 18 more
2022-05-28 01:26:58,742 [C3P0PooledConnectionPoolManager[identityToken->1hge160ap1veldcsvyjli|3af7ee3e]-HelperThread-#1] WARN resourcepool.BasicResourcePool - Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5edd8648 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2022-05-28 01:26:58,838 [main] ERROR context.GrailsContextLoader - Error executing bootstraps: Error creating bean with name ‘messageSource’: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager’: Cannot resolve reference to bean ‘sessionFactory’ while setting bean property ‘sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’: Cannot resolve reference to bean ‘lobHandlerDetector’ while setting bean property ‘lobHandler’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘lobHandlerDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘messageSource’: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager’: Cannot resolve reference to bean ‘sessionFactory’ while setting bean property ‘sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’: Cannot resolve reference to bean ‘lobHandlerDetector’ while setting bean property ‘lobHandler’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘lobHandlerDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
at org.grails.tomcat.InlineExplodedTomcatServer.doStart(InlineExplodedTomcatServer.groovy:112)
at org.grails.tomcat.InlineExplodedTomcatServer$doStart.callCurrent(Unknown Source)
at org.grails.tomcat.TomcatServer.start(TomcatServer.groovy:97)
at grails.web.container.EmbeddableServer$start.call(Unknown Source)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy:158)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy)
at _GrailsSettings_groovy$_run_closure10.doCall(_GrailsSettings_groovy:280)
at _GrailsSettings_groovy$_run_closure10.call(_GrailsSettings_groovy)
at _GrailsRun_groovy$_run_closure5.doCall(_GrailsRun_groovy:149)
at _GrailsRun_groovy$_run_closure5.call(_GrailsRun_groovy)
at _GrailsRun_groovy.runInline(_GrailsRun_groovy:116)
at _GrailsRun_groovy.this$4$runInline(_GrailsRun_groovy)
at _GrailsRun_groovy$_run_closure1.doCall(_GrailsRun_groovy:59)
at RunApp$_run_closure1.doCall(RunApp.groovy:33)
at gant.Gant$_dispatch_closure5.doCall(Gant.groovy:381)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy:415)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy)
at gant.Gant.withBuildListeners(Gant.groovy:427)
at gant.Gant.this$2$withBuildListeners(Gant.groovy)
at gant.Gant$this$2$withBuildListeners.callCurrent(Unknown Source)
at gant.Gant.dispatch(Gant.groovy:415)
at gant.Gant.this$2$dispatch(Gant.groovy)
at gant.Gant.invokeMethod(Gant.groovy)
at gant.Gant.executeTargets(Gant.groovy:590)
at gant.Gant.executeTargets(Gant.groovy:589)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager’: Cannot resolve reference to bean ‘sessionFactory’ while setting bean property ‘sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’: Cannot resolve reference to bean ‘lobHandlerDetector’ while setting bean property ‘lobHandler’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘lobHandlerDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
… 25 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’: Cannot resolve reference to bean ‘lobHandlerDetector’ while setting bean property ‘lobHandler’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘lobHandlerDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
… 25 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘lobHandlerDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
… 25 more
Caused by: org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
… 25 more
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
… 25 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:692)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
… 25 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1469)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
… 26 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2030)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:778)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.sql.SQLException: Unknown system variable ‘storage_engine’
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.mysql.jdbc.ConnectionImpl.setSessionVariables(ConnectionImpl.java:5030)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3202)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2068)
… 15 more

jmiranda · May 27, 2022, 6:41pm

Set the database configuration in openboxes-config.properties.

You will need to create the file if you haven’t already done so.

See the README for instructions

jmiranda · May 27, 2022, 6:45pm

Once it does connect to the database and you can see that the application is running through database migrations please be patient. This process can take a long time especially if you haven’t followed the instructions carefully (hint: make to increase heap size and permgen).

chandara · May 28, 2022, 4:32am

Thank you @jmiranda

I created in this location. is it a correct place?

Thanks,
Chandara

jmiranda · May 28, 2022, 12:18pm

No it’ should be created under your user home directory. On A Mac that would be something like

/Users/username/.grails

If you encounter any more issues send me the full output from the grails run-app command.

chandara · May 28, 2022, 1:03pm

@jmiranda all is working for me now.
but it is taking more time on table migration.

Thanks,
Chandara

chandara · May 28, 2022, 1:12pm

@jmiranda Here are some error during migrate.
//////////

Blockquote
NFO liquibase - Changeset views/changelog.xml::1580848680306-26::awalkowiak::(MD5Sum: 63b4ffde924d6b393e249803ac4fef9)
2022-05-28 20:08:47,007 [main] INFO liquibase - Changeset views/changelog.xml::1580848680306-27::pmuchowski::(MD5Sum: 376b802ad2080d148d2dc37bc44f024)
2022-05-28 20:08:47,040 [main] INFO liquibase - Release Database Lock
2022-05-28 20:08:47,042 [main] INFO liquibase - Successfully released change log lock
2022-05-28 20:08:55,317 [main] ERROR context.GrailsContextLoader - Error executing bootstraps: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes.product_demand_details’ doesn’t exist
org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes.product_demand_details’ doesn’t exist
at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
at grails.util.Environment.executeForEnvironment(Environment.java:244)
at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)
at org.grails.tomcat.InlineExplodedTomcatServer.doStart(InlineExplodedTomcatServer.groovy:112)
at org.grails.tomcat.InlineExplodedTomcatServer$doStart.callCurrent(Unknown Source)
at org.grails.tomcat.TomcatServer.start(TomcatServer.groovy:97)
at grails.web.container.EmbeddableServer$start.call(Unknown Source)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy:158)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy)
at _GrailsSettings_groovy$_run_closure10.doCall(_GrailsSettings_groovy:280)
at _GrailsSettings_groovy$_run_closure10.call(_GrailsSettings_groovy)
at _GrailsRun_groovy$_run_closure5.doCall(_GrailsRun_groovy:149)
at _GrailsRun_groovy$_run_closure5.call(_GrailsRun_groovy)
at _GrailsRun_groovy.runInline(_GrailsRun_groovy:116)
at _GrailsRun_groovy.this$4$runInline(_GrailsRun_groovy)
at _GrailsRun_groovy$_run_closure1.doCall(_GrailsRun_groovy:59)
at RunApp$_run_closure1.doCall(RunApp.groovy:33)
at gant.Gant$_dispatch_closure5.doCall(Gant.groovy:381)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy:415)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy)
at gant.Gant.withBuildListeners(Gant.groovy:427)
at gant.Gant.this$2$withBuildListeners(Gant.groovy)
at gant.Gant$this$2$withBuildListeners.callCurrent(Unknown Source)
at gant.Gant.dispatch(Gant.groovy:415)
at gant.Gant.this$2$dispatch(Gant.groovy)
at gant.Gant.invokeMethod(Gant.groovy)
at gant.Gant.executeTargets(Gant.groovy:590)
at gant.Gant.executeTargets(Gant.groovy:589)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes.product_demand_details’ doesn’t exist
at liquibase.ChangeSet.execute(ChangeSet.java:238)
at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
at liquibase.Liquibase.update(Liquibase.java:112)
at liquibase.Liquibase$update.call(Unknown Source)
at BootStrap$closure1.doCall(BootStrap.groovy:469)
… 28 more
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
at liquibase.ChangeSet.execute(ChangeSet.java:214)
… 33 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘openboxes.product_demand_details’ doesn’t exist
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
… 37 more
Application context shutting down…
2022-05-28 20:08:55,346 [Thread-20] INFO support.DefaultLifecycleProcessor - Stopping beans in phase 2147483647
2022-05-28 20:08:55,354 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-28 20:08:55,393 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED shutting down.
2022-05-28 20:08:55,393 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-28 20:08:55,394 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$_NON_CLUSTERED shutdown complete.

jmiranda · May 28, 2022, 5:25pm

What branch are you running on?

chandara · May 29, 2022, 3:47am

@jmiranda i download this version: openboxes-0.8.17-hotfix5

Thanks,
Chandara

jmiranda · May 29, 2022, 2:47pm

This is a bug that was fixed in the develop branch.

jmiranda · May 29, 2022, 2:50pm

To elaborate, we have a view that depends on a derived data table that is created at runtime. If the view definition is executed before that table is run, we’ll run into this bug.

The temporary fix was to allow the view creation to fail as it would be recreated on the next restart.
https://github.com/openboxes/openboxes/blob/develop/grails-app/migrations/views/changelog.xml#L81

I’m not super happy with that solution, so I would recommend manually executing the following SQL script on your database before restarting the application.

DROP TABLE IF EXISTS product_demand_details_tmp;
CREATE TABLE product_demand_details_tmp AS
    SELECT
        request_id,
        request_status,
        request_number,
        date_created,
        date_requested,
        date_issued,
        origin_id,
        origin_name,
        destination_id,
        destination_name,
        request_item_id,
        product_id,
        product_code,
        product_name,
        quantity_requested,
        quantity_canceled,
        quantity_approved,
        quantity_modified,
        quantity_picked,
        quantity_demand,
        reason_code,
        reason_code_classification
    FROM product_demand;
DROP TABLE IF EXISTS product_demand_details;
CREATE TABLE IF NOT EXISTS product_demand_details LIKE product_demand_details_tmp;
TRUNCATE product_demand_details;
INSERT INTO product_demand_details SELECT * FROM product_demand_details_tmp;
ALTER TABLE product_demand_details ADD INDEX (product_id, origin_id, destination_id, date_issued, date_requested);

chandara · May 29, 2022, 3:47pm

Thank you @jmiranda.
I will try the tmp solution.

Thanks,
Chandara

chandara · May 30, 2022, 1:44pm

@jmiranda it is possible that I’m able to download sample database.
I faced this issue in my local machine like out of memory.
//
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
2022-05-30 00:04:51,299 [Thread-20] WARN support.DefaultLifecycleProcessor - Failed to shut down 1 bean with phase value 2147483647 within timeout of 30000: [quartzScheduler]
2022-05-30 00:04:53,998 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler_$NON_CLUSTERED shutting down.
2022-05-30 00:04:53,999 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-30 00:04:53,999 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$_NON_CLUSTERED shutdown complete.
2022-05-30 00:04:55,019 [Thread-20] INFO ehcache.EhCacheManagerFactoryBean - Shutting down EHCache CacheManager
2022-05-30 00:04:55,047 [Thread-20] INFO impl.SessionFactoryImpl - closing
2022-05-30 00:04:55,047 [Thread-20] INFO util.NamingHelper - JNDI InitialContext properties:{}
Application context shutdown.

//
Thanks,
Chandara

jmiranda · May 30, 2022, 2:23pm

The installation instructions mention this issue and how to resolve it.

How much memory do you have on your computer? And how much are you allocating to Grails?

chandara · May 30, 2022, 2:33pm

@jmiranda my machine has only 8GB.
Allocating to Grails, I haven’t configured it yet. I leave it by default

jmiranda · May 31, 2022, 1:58am

The installation instructions mention this issue and how to resolve it.

Apologies. I was wrong about this. We only mention memory settings in the official installation instructions, not the developer installation instructions.

On Ubuntu, I add the following to my .bashrc

export GRAILS_OPTS="-XX:PermSize=512m -Xms2048m -Xmx2048m -server"

I would recommend doing the equivalent on a Mac.

Note: I have 32 GB on this laptop and generally try to run Grails with either 2GB or 4GB. But you can lower the values if you don’t think you’ll be able to allocate that much.

The following would probably be the minimum though.

export GRAILS_OPTS="-XX:PermSize=128m -Xms1024m -Xmx1024m -server"

chandara · May 31, 2022, 1:56pm

@jmiranda It is seem good for me now but it gave me below error:
//
2022-05-31 20:51:59,644 [main] ERROR context.GrailsContextLoader - Error executing bootstraps: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_4.product_demand_details’ doesn’t exist
org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_4.product_demand_details’ doesn’t exist
at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
at grails.util.Environment.executeForEnvironment(Environment.java:244)
at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)
at org.grails.tomcat.InlineExplodedTomcatServer.doStart(InlineExplodedTomcatServer.groovy:112)
at org.grails.tomcat.InlineExplodedTomcatServer$doStart.callCurrent(Unknown Source)
at org.grails.tomcat.TomcatServer.start(TomcatServer.groovy:97)
at grails.web.container.EmbeddableServer$start.call(Unknown Source)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy:158)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy)
at _GrailsSettings_groovy$_run_closure10.doCall(_GrailsSettings_groovy:280)
at _GrailsSettings_groovy$_run_closure10.call(_GrailsSettings_groovy)
at _GrailsRun_groovy$_run_closure5.doCall(_GrailsRun_groovy:149)
at _GrailsRun_groovy$_run_closure5.call(_GrailsRun_groovy)
at _GrailsRun_groovy.runInline(_GrailsRun_groovy:116)
at _GrailsRun_groovy.this$4$runInline(_GrailsRun_groovy)
at _GrailsRun_groovy$_run_closure1.doCall(_GrailsRun_groovy:59)
at RunApp$_run_closure1.doCall(RunApp.groovy:33)
at gant.Gant$_dispatch_closure5.doCall(Gant.groovy:381)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy:415)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy)
at gant.Gant.withBuildListeners(Gant.groovy:427)
at gant.Gant.this$2$withBuildListeners(Gant.groovy)
at gant.Gant$this$2$withBuildListeners.callCurrent(Unknown Source)
at gant.Gant.dispatch(Gant.groovy:415)
at gant.Gant.this$2$dispatch(Gant.groovy)
at gant.Gant.invokeMethod(Gant.groovy)
at gant.Gant.executeTargets(Gant.groovy:590)
at gant.Gant.executeTargets(Gant.groovy:589)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_4.product_demand_details’ doesn’t exist
at liquibase.ChangeSet.execute(ChangeSet.java:238)
at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
at liquibase.Liquibase.update(Liquibase.java:112)
at liquibase.Liquibase$update.call(Unknown Source)
at BootStrap$closure1.doCall(BootStrap.groovy:469)
… 28 more
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
at liquibase.ChangeSet.execute(ChangeSet.java:214)
… 33 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘openboxes_0817_4.product_demand_details’ doesn’t exist
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
… 37 more
Application context shutting down…
2022-05-31 20:51:59,741 [Thread-20] INFO support.DefaultLifecycleProcessor - Stopping beans in phase 2147483647
2022-05-31 20:51:59,745 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-31 20:51:59,782 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED shutting down.
2022-05-31 20:51:59,782 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-31 20:51:59,783 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$_NON_CLUSTERED shutdown complete.
2022-05-31 20:52:00,800 [Thread-20] INFO ehcache.EhCacheManagerFactoryBean - Shutting down EHCache CacheManager
2022-05-31 20:52:00,829 [Thread-20] INFO impl.SessionFactoryImpl - closing
2022-05-31 20:52:00,831 [Thread-20] INFO util.NamingHelper - JNDI InitialContext properties:{}
Application context shutdown.
(base) macbookpro@MacBookPro openboxes-0.8.17-hotfix4 %

///

Thanks,
Chandara

jmiranda · May 31, 2022, 2:29pm

You’re still running on the openboxes-0.8.17-hotfix4 branch. As I mentioned a few responses ago, you need to use the develop branch and/or run that script I sent to create the product demand tables manually.

chandara · May 31, 2022, 3:26pm

@jmiranda in dev brance also has the same error.

Here same error I have:

///
2022-05-31 22:19:17,076 [main] ERROR context.GrailsContextLoader - Error executing bootstraps: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_5.product_demand_details’ doesn’t exist
org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_5.product_demand_details’ doesn’t exist
at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
at grails.util.Environment.executeForEnvironment(Environment.java:244)
at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)
at org.grails.tomcat.InlineExplodedTomcatServer.doStart(InlineExplodedTomcatServer.groovy:112)
at org.grails.tomcat.InlineExplodedTomcatServer$doStart.callCurrent(Unknown Source)
at org.grails.tomcat.TomcatServer.start(TomcatServer.groovy:97)
at grails.web.container.EmbeddableServer$start.call(Unknown Source)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy:158)
at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy)
at _GrailsSettings_groovy$_run_closure10.doCall(_GrailsSettings_groovy:280)
at _GrailsSettings_groovy$_run_closure10.call(_GrailsSettings_groovy)
at _GrailsRun_groovy$_run_closure5.doCall(_GrailsRun_groovy:149)
at _GrailsRun_groovy$_run_closure5.call(_GrailsRun_groovy)
at _GrailsRun_groovy.runInline(_GrailsRun_groovy:116)
at _GrailsRun_groovy.this$4$runInline(_GrailsRun_groovy)
at _GrailsRun_groovy$_run_closure1.doCall(_GrailsRun_groovy:59)
at RunApp$_run_closure1.doCall(RunApp.groovy:33)
at gant.Gant$_dispatch_closure5.doCall(Gant.groovy:381)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy:415)
at gant.Gant$_dispatch_closure7.doCall(Gant.groovy)
at gant.Gant.withBuildListeners(Gant.groovy:427)
at gant.Gant.this$2$withBuildListeners(Gant.groovy)
at gant.Gant$this$2$withBuildListeners.callCurrent(Unknown Source)
at gant.Gant.dispatch(Gant.groovy:415)
at gant.Gant.this$2$dispatch(Gant.groovy)
at gant.Gant.invokeMethod(Gant.groovy)
at gant.Gant.executeTargets(Gant.groovy:590)
at gant.Gant.executeTargets(Gant.groovy:589)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set views/changelog.xml::1580848680306-27::pmuchowski:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand:
Caused By: Table ‘openboxes_0817_5.product_demand_details’ doesn’t exist
at liquibase.ChangeSet.execute(ChangeSet.java:238)
at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
at liquibase.Liquibase.update(Liquibase.java:112)
at liquibase.Liquibase$update.call(Unknown Source)
at BootStrap$closure1.doCall(BootStrap.groovy:469)
… 28 more
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE OR REPLACE VIEW product_expiry_summary AS
SELECT
p_a.product_id,
p_a.location_id,
i.expiration_date,
SUM(p_a.quantity_on_hand) AS quantity_on_hand,
IFNULL(demand.average_daily_demand, 0) AS average_daily_demand
FROM product_availability AS p_a
JOIN inventory_item i ON i.id = p_a.inventory_item_id
LEFT JOIN (
SELECT
product_id,
origin_id AS location_id,
SUM(quantity_demand) / datediff(CURRENT_DATE, MIN(COALESCE(date_issued, date_requested))) AS average_daily_demand
FROM product_demand_details
GROUP BY product_id, location_id
) demand ON demand.product_id = p_a.product_id AND demand.location_id = p_a.location_id
WHERE i.expiration_date IS NOT NULL
GROUP BY p_a.product_id, p_a.location_id, i.expiration_date, demand.average_daily_demand
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
at liquibase.ChangeSet.execute(ChangeSet.java:214)
… 33 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘openboxes_0817_5.product_demand_details’ doesn’t exist
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
… 37 more
Application context shutting down…
2022-05-31 22:19:17,676 [Thread-20] INFO support.DefaultLifecycleProcessor - Stopping beans in phase 2147483647
2022-05-31 22:19:17,679 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-31 22:19:17,704 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED shutting down.
2022-05-31 22:19:17,707 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$NON_CLUSTERED paused.
2022-05-31 22:19:17,708 [Thread-20] INFO core.QuartzScheduler - Scheduler DefaultPluginScheduler$_NON_CLUSTERED shutdown complete.
2022-05-31 22:19:18,727 [Thread-20] INFO ehcache.EhCacheManagerFactoryBean - Shutting down EHCache CacheManager
2022-05-31 22:19:18,749 [Thread-20] INFO impl.SessionFactoryImpl - closing
2022-05-31 22:19:18,750 [Thread-20] INFO util.NamingHelper - JNDI InitialContext properties:{}
Application context shutdown.
(base) macbookpro@MacBookPro openboxes-0.8.17-hotfix5 %

Thanks,
Chandara

chandara · May 31, 2022, 3:50pm

@jmiranda thank you for the support, I finally have it.
Screen Shot 2022-05-31 at 22.49.32
Thanks,
Chandara