Hello there,
Currently i’m running version 0.8.17 on a server (yeah I know upgrade time 
). So I installed 0.8.22 on a new machine. I want to migrate/import everything from the old (0.8.17) to the new (0.8.22). What is the best way?
Copying over the Database seems not the correct way?
Should I install 0.8.17 on the new server, copy the files over en database and then upgrade?
Thanks!
Hi @David_Douma, I believe you could simply copy the database and simply restart the server and all new migrations should be applied automatically. I suggest doing it first on a test copy of your DB.
Let me know if you run into any trouble with that.
Hello Arthur,
Isn’t it better to install same version on new server and then upgrade?
I think database structure has changed in the newer version?
Isn’t it better to install same version on new server and then upgrade?
You can do that but it’s not necessary.
We use Liquibase to incrementally update the database structure with each new release. So what @Artur is suggesting is that you just need to deploy the new version of the application and it will handle updating the database schema by applying a DDL changelog in the form of incremental changesets.
For example, here is the most recent schema change (adding a nullable quantity column to the requisition item table):
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
<changeSet author="AlanNadolny" id="170520231453-0">
<preConditions onFail="MARK_RAN">
<not>
<columnExists tableName="requisition_item" columnName="quantity_counted"/>
</not>
</preConditions>
<addColumn tableName="requisition_item">
<column name="quantity_counted" type="INT" />
</addColumn>
</changeSet>
</databaseChangeLog>
Artur said “I suggest doing it first on a test copy of your DB.” because there’s a possibility that any of those DDL changesets might fail (usually due to invalid data that we did not foresee) and cause downtime while you figure out what’s wrong with the schema/data.
So in order to make sure that doesn’t happen it’s a good idea to deploy the latest version of the application to a staging server that uses a recent copy of your production database.
By the way, I don’t think we’ve addressed this in much detail in the documentation so thank you for asking the question @David_Douma.
TL;DR:
Please let us know if anyone has additional questions about the process.
Hello! Not yet tested, will do this weekend :). Ill keep you informed, thanks for the reply.
Hmmm, It doesnt want to start.
See attached log.
What I did:
catalina.out (95.1 KB)
So here’s the important bits from the catalina.out log file.
2023-06-02 22:05:19,131 [localhost-startStop-1] INFO bootstrap.BootStrap - Running liquibase changelog(s) ...
2023-06-02 22:05:19,202 [localhost-startStop-1] INFO bootstrap.BootStrap - Liquibase running: false
2023-06-02 22:05:19,205 [localhost-startStop-1] INFO bootstrap.BootStrap - Setting default schema to openboxes
2023-06-02 22:05:19,205 [localhost-startStop-1] INFO bootstrap.BootStrap - Product Version: 5.5.5-10.6.13-MariaDB
2023-06-02 22:05:19,205 [localhost-startStop-1] INFO bootstrap.BootStrap - Database Version: 5.5
2023-06-02 22:05:19,206 [localhost-startStop-1] INFO liquibase - Reading from `DATABASECHANGELOG`
2023-06-02 22:05:19,273 [localhost-startStop-1] INFO liquibase - Lock Database
2023-06-02 22:05:19,275 [localhost-startStop-1] INFO liquibase - Successfully acquired change log lock
2023-06-02 22:05:20,594 [localhost-startStop-1] INFO liquibase - Changeset views/drop-all-views.xml::1633402273161-1::jmiranda::(MD5Sum: ebfa3e42ad3db93c8931a490b5e71ecd)
2023-06-02 22:05:20,594 [localhost-startStop-1] INFO liquibase - Views will be recreated after database migrations are executed.
2023-06-02 22:05:20,609 [localhost-startStop-1] INFO liquibase - Changeset 0.8.x/changelog-2023-04-14-1200-create-table-product-merge-logger.xml::1404202312000-1::awalkowiak::(MD5Sum: dedb980ffe6e8d12ed2854e384024c7)
2023-06-02 22:05:22,266 [localhost-startStop-1] INFO liquibase - Release Database Lock
2023-06-02 22:05:22,266 [localhost-startStop-1] INFO liquibase - Successfully released change log lock
2023-06-02 22:05:22,290 [localhost-startStop-1] ERROR context.ContextLoader - Context initialization failed
org.springframework.beans.factory.access.BootstrapException: Error executing bootstraps; nested exception is org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set 0.8.x/changelog-2023-04-14-1200-create-table-product-merge-logger.xml::1404202312000-1::awalkowiak:
Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`product_merge_logger` ADD CONSTRAINT `fk_product_merge_logger_primary_product` FOREIGN KEY (`primary_product_id`) REFERENCES `openboxes`.`product`(`id`):
Caused By: Error executing SQL ALTER TABLE `openboxes`.`product_merge_logger` ADD CONSTRAINT `fk_product_merge_logger_primary_product` FOREIGN KEY (`primary_product_id`) REFERENCES `openboxes`.`product`(`id`):
Caused By: Can't create table `openboxes`.`product_merge_logger` (errno: 150 "Foreign key constraint is incorrectly formed")
at org.codehaus.groovy.grails.web.context.GrailsContextLoader.createWebApplicationContext(GrailsContextLoader.java:87)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5128)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5653)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:1007)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:983)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:639)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1128)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:2020)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:473)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set 0.8.x/changelog-2023-04-14-1200-create-table-product-merge-logger.xml::1404202312000-1::awalkowiak:
Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`product_merge_logger` ADD CONSTRAINT `fk_product_merge_logger_primary_product` FOREIGN KEY (`primary_product_id`) REFERENCES `openboxes`.`product`(`id`):
Caused By: Error executing SQL ALTER TABLE `openboxes`.`product_merge_logger` ADD CONSTRAINT `fk_product_merge_logger_primary_product` FOREIGN KEY (`primary_product_id`) REFERENCES `openboxes`.`product`(`id`):
Caused By: Can't create table `openboxes`.`product_merge_logger` (errno: 150 "Foreign key constraint is incorrectly formed")
at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
at grails.util.Environment.executeForEnvironment(Environment.java:244)
at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)
... 5 more
In particular, the following error is causing the problem problem.
Caused By: Can't create table `openboxes`.`product_merge_logger`
(errno: 150 "Foreign key constraint is incorrectly formed")
At first glance, I thought maybe this was an issue with the table character set or the columns being different data types.
And then I saw what MySQL version you are running and thought “oh goodness”.
2023-06-02 22:05:19,205 [localhost-startStop-1] INFO bootstrap.BootStrap - Product Version: 5.5.5-10.6.13-MariaDB
2023-06-02 22:05:19,205 [localhost-startStop-1] INFO bootstrap.BootStrap - Database Version: 5.5
Unfortunately, we stopped supporting MySQL 5.5 in 2019 because we started using MySQL 5.7 features. I don’t recall which features in particular, but I’d recommend upgrading to at least MySQL 5.7 (MariaDB 10.2 - 10.4).
If you can upgrade, then I think we may end up running into the same issue since I think the tables created under MySQL 5.5 might be in a weird state with respect to their engine, character set, etc. But I have some ideas on how to tackle that once we get there.
If you can’t upgrade for some reason, then stick with 0.8.17 for now.
Also, if you’d like some help with any of this I can ask our development and support partner Soldevelo if they could lend a hand.
Justin
Hello Justin,
LOL @ the oh god part.
But thats a bug I think, I use version:
mysql Ver 15.1 Distrib 10.6.13-MariaDB, for Linux (x86_64) using readline 5.1
Because a blanco install works…
Anyway,
I delete the Database, copyed over the one on the current running server. And then the errors appeared.
Update! I exported the DB with PhpMyAdmin and imported it, works…!
mysql Ver 15.1 Distrib 10.6.13-MariaDB, for Linux (x86_64) using readline 5.1
Ok, phew.
Update! I exported the DB with PhpMyAdmin and imported it, works…!
Awesome.
That doesn’t necessarily make me feel better nor answer the question of why it was happening but I don’t think I’ll be able to troubleshoot any further … so I guess that’s good enough.
My guess is that PhpMyAdmin adds some arguments to the mysqldump command that side steps the issue. So if we need to investigate further that might be one angle we look into.
For anyone else who may run into this issue in the future, what I was going to suggest was to dig into the details of the tables involved.
Product
MariaDB [openboxes]> show create table product \G;
*************************** 1. row ***************************
Table: product
Create Table: CREATE TABLE `product` (
`id` char(38) NOT NULL DEFAULT '',
`version` bigint(20) NOT NULL,
`category_id` char(38) DEFAULT NULL,
`date_created` datetime NOT NULL,
`description` mediumtext DEFAULT NULL,
`last_updated` datetime NOT NULL,
`name` varchar(255) NOT NULL,
`product_code` varchar(255) DEFAULT NULL,
`unit_of_measure_id` char(38) DEFAULT NULL,
`cold_chain` bit(1) DEFAULT NULL,
`manufacturer` varchar(255) DEFAULT NULL,
`manufacturer_code` varchar(255) DEFAULT NULL,
`ndc` varchar(255) DEFAULT NULL,
`upc` varchar(255) DEFAULT NULL,
`unit_of_measure` varchar(255) DEFAULT NULL,
`created_by_id` char(38) DEFAULT NULL,
`updated_by_id` char(38) DEFAULT NULL,
`default_uom_id` char(38) DEFAULT NULL,
`brand_name` varchar(255) DEFAULT NULL,
`vendor` varchar(255) DEFAULT NULL,
`vendor_code` varchar(255) DEFAULT NULL,
`package_size` int(11) DEFAULT NULL,
`model_number` varchar(255) DEFAULT NULL,
`manufacturer_name` varchar(255) DEFAULT NULL,
`vendor_name` varchar(255) DEFAULT NULL,
`active` bit(1) DEFAULT NULL,
`controlled_substance` bit(1) DEFAULT NULL,
`essential` bit(1) DEFAULT NULL,
`hazardous_material` bit(1) DEFAULT NULL,
`lot_control` bit(1) DEFAULT NULL,
`serialized` bit(1) DEFAULT NULL,
`price_per_unit` decimal(19,4) DEFAULT NULL,
`reconditioned` bit(1) DEFAULT NULL,
`product_type_id` char(38) DEFAULT NULL,
`abc_class` varchar(255) DEFAULT NULL,
`cost_per_unit` decimal(19,4) DEFAULT NULL,
`gl_account_id` char(38) DEFAULT NULL,
`lot_and_expiry_control` bit(1) DEFAULT NULL,
`product_family_id` char(38) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `FKED8DCCEFEF4C770D` (`category_id`),
KEY `FKED8DCCEF217F5972` (`created_by_id`),
KEY `FKED8DCCEFEEB2908D` (`default_uom_id`),
KEY `FKED8DCCEF426DD105` (`updated_by_id`),
KEY `FKED8DCCEFABD88AC6` (`product_type_id`),
KEY `fk_product_gl_account` (`gl_account_id`),
KEY `fk_product_product_family` (`product_family_id`),
CONSTRAINT `FKED8DCCEF217F5972` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`),
CONSTRAINT `FKED8DCCEF426DD105` FOREIGN KEY (`updated_by_id`) REFERENCES `user` (`id`),
CONSTRAINT `FKED8DCCEFABD88AC6` FOREIGN KEY (`product_type_id`) REFERENCES `product_type` (`id`),
CONSTRAINT `FKED8DCCEFEEB2908D` FOREIGN KEY (`default_uom_id`) REFERENCES `unit_of_measure` (`id`),
CONSTRAINT `FKED8DCCEFEF4C770D` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`),
CONSTRAINT `fk_product_gl_account` FOREIGN KEY (`gl_account_id`) REFERENCES `gl_account` (`id`),
CONSTRAINT `fk_product_product_family` FOREIGN KEY (`product_family_id`) REFERENCES `product_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
1 row in set (0.000 sec)
Product Merge Logger
MariaDB [openboxes]> show create table product_merge_logger \G;
*************************** 1. row ***************************
Table: product_merge_logger
Create Table: CREATE TABLE `product_merge_logger` (
`id` char(38) NOT NULL,
`primary_product_id` char(38) NOT NULL,
`obsolete_product_id` char(38) NOT NULL,
`related_object_id` char(38) NOT NULL,
`related_object_class_name` varchar(255) NOT NULL,
`date_merged` datetime NOT NULL,
`date_reverted` datetime DEFAULT NULL,
`comments` varchar(255) DEFAULT NULL,
`date_created` datetime NOT NULL,
`last_updated` datetime NOT NULL,
`created_by_id` char(38) NOT NULL,
`updated_by_id` char(38) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_product_merge_logger_primary_product` (`primary_product_id`),
KEY `fk_product_merge_logger_obsolete_product` (`obsolete_product_id`),
KEY `fk_product_merge_logger_created_by` (`created_by_id`),
KEY `fk_product_merge_logger_updated_by` (`updated_by_id`),
CONSTRAINT `fk_product_merge_logger_created_by` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`),
CONSTRAINT `fk_product_merge_logger_obsolete_product` FOREIGN KEY (`obsolete_product_id`) REFERENCES `product` (`id`),
CONSTRAINT `fk_product_merge_logger_primary_product` FOREIGN KEY (`primary_product_id`) REFERENCES `product` (`id`),
CONSTRAINT `fk_product_merge_logger_updated_by` FOREIGN KEY (`updated_by_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
1 row in set (0.000 sec)
And then maybe take a look at what MySQL/MariaDB thinks the tables look like with particular interest in the table_collation and engine.
MariaDB [openboxes]> select * from information_schema.tables where table_schema = 'openboxes' and table_name in ('product', 'product_merge_logger') \G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: openboxes
TABLE_NAME: product_merge_logger
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 31
AVG_ROW_LENGTH: 528
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 65536
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2023-05-22 18:11:20
UPDATE_TIME: 2023-05-24 09:35:38
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
TEMPORARY: N
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: openboxes
TABLE_NAME: product
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 16236
AVG_ROW_LENGTH: 422
DATA_LENGTH: 6864896
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 16908288
DATA_FREE: 4194304
AUTO_INCREMENT: NULL
CREATE_TIME: 2023-05-22 18:11:20
UPDATE_TIME: 2023-06-05 15:45:27
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
TEMPORARY: N
2 rows in set (0.001 sec)
As well as server variables related to character set.
MariaDB [openboxes]> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
Eek. That’s not good.
Aside: We do a substantial amount of QA during the development and release process. This kind of error would be a huge showstopper bug that would not get through our amazing QA team, and in particular Katarzyna Krawczyk, our lead QA engineer. Since I’ve never seen this issue in any of our testing or production environments, I’m inclined to believe that this is an issue with your production environment.
To confirm that, here’s what we need to check.
Is that file still in our code base? Check.
Is that file in the WAR file that we published to the GitHub Releases pages? Check.
So it seems like this is probably an issue with your server and I have a few thoughts on what it might be.
Here are a few questions to troubleshoot …
and some suggestions
Check the exploded WAR directory under your Tomcat home directory to see if the file is there.
$ sudo ls -al /opt/tomcat/webapps/openboxes/WEB-INF/grails-app/views/product/_summary.gsp
-rw-r----- 1 tomcat tomcat 7689 Jul 18 04:04 /var/lib/tomcat7/webapps/openboxes/WEB-INF/grails-app/views/product/_summary.gsp
If the file is there, make sure the file is owned by the tomcat user.
If that’s not the case, then run the following commands
$ sudo chown -rf tomcat:tomcat /opt/tomcat/webapps/openboxes
$ sudo service tomcat restart
If the file is not there, let me know what the permissions are for the exploded WAR directory
$ sudo ls -al /opt/tomcat/webapps/openboxes
… and then delete the exploded WAR directory and try to redeploy.
$ sudo service tomcat stop
$ sudo cd /opt/tomcat/webapps
$ sudo rm -rf openboxes*
$ sudo wget https://github.com/openboxes/openboxes/releases/download/v0.8.22/openboxes.war
$ sudo chown tomcat:tomcat openboxes.war
$ sudo service tomcat restart
Followup:
Did a full wipe and downloaded the War file again, now it works… 
Thanks for the update, David. Glad you got it fixed.