@jmiranda all is working for me now.
but it is taking more time on table migration.
Thanks,
Chandara
@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.
What branch are you running on?
This is a bug that was fixed in the develop branch.
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);
@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
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?
@jmiranda my machine has only 8GB.
Allocating to Grails, I haven’t configured it yet. I leave it by default
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"
@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
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.
@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
