xTuple ERP+CRM 5.0.0 is a big release. A lot of features have been added that should make life better for application users. The price we pay for that added richness is that the internals have changed. Power users and administrators need to do work to upgrade to 5.0.0 successfully. We strongly recommend piloting the 5.0.0 release candidate.
This article describes how to prepare for the upgrade to xTuple ERP+CRM 5.0.0. The goals are to help you prepare for the upgrade over time, improve your production database as you go, give you practice for the production database upgrade, and minimize last-minute work. Keep checking back; we'll improve this article as we get feedback from customers and partners [updated June 14, 2019].
- Back up your production database
- Create a pilot database
- Upgrade the pilot
- Document and correct any data problems
- Document and correct any customizations
- Review and document new privilege assignments
- Test customization changes
- Final Pilot
The steps for upgrading to version 5.0.0 are essentially the same. The difference is in the details and the order because of the number and kinds of change required. This article describes these differences and why they're helpful. It is distilled from the experience of upgrading pilot copies of two real xTuple databases, summarized here:
|Version before upgrade to 5.0.0-alpha||4.9||4.11|
|Type of business||manufacturing||services|
|Number of gltrans records (approximate)||706K||225K|
|Number of item records (approximate)||5000||400|
|Number of extensions installed||17||44|
|Number of extensions enabled||16||37|
|Number of report definitions||503||593|
|Number of unique report names||379||410|
|Number of MetaSQL queries||479||795|
|Number of unique MetaSQL group-name||437||630|
|Number of scripts||277||523|
|Number of scripts enabled||276||500|
|Notes to document 5.0.0 pilot setup||~100 lines||~275 lines|
|Extra scripts written to patch data||1||5|
|Scripts, Reports, and MetaSQL statements needing fixes for bad database references||17 files updated for pilot
35 reports ignored for the pilot
|lost count, plus fixed several database functions (created 6 new extensions)|
The first one was easy to manage and took about 16 hours of effort. I'll refer to this one as the
simple database. The second database took much longer so I'll call it the
hard database. Note that the
simple database is much much less heavily customized than the
hard database, even though it's bigger. That's important — most of the work in moving to 5.0.0 involves changing customizations. Most of the data can be migrated automatically, as always.
Let's get started.
Back up Your Production Database
Back up your databases early and often. Keep good records of when the backup was done. If you have several databases, such as production and pilot copies, keep extra careful records. I datestamp backups, or even timestamp them, in the file name. Copying and moving files from one directory to another can sometimes change the operating system or file system's timestamp on the file so I've learned not to rely on that.
Create a Pilot Database
Create a new database with a new name and restore a copy of your production database to it. I strongly recommend making some simple visual changes to make it look different from your production database:
- Log in to your pilot database with your current version of the desktop client
- System > Setup > Database
- Change the Database Description to something like "5.0.0 Pilot"
- If you use the credit card processing features, consider setting that to test mode
- System > Preferences or xTuple > Preferences > Application
- Change the setting of Use Native Application Styling
- Change the setting of Alternating Row Colors
- If you're not using the xTuple Desktop extension then set or change the background image
- Log out and log back in again
You don't have to do this but these visual cues reduce the risk of working in the wrong database.
Document and Correct Data Problems — Fix Countries, Part 1
Normally at this point you would upgrade the pilot database. Don't. It probably won't work. First you need to clean up the names of countries used in the tables that contain addresses.
Why: Integration with AvaTax from Avalara is one of the main features we added in version 5.0.0. Correct address information is essential for proper tax calculation so we needed to start enforcing stricter rules on addresses, particularly country names. We introduced the concept of strict country checking in v3.4.0 but it's been optional until now.
Practice with it until you understand how it works:
- CRM > Utilities > Fix Countries
- Click QUERY (1)
- To fix all records that share a common bad country name (2)
- Click BULK REPLACE to open a simple window with two comboboxes (AKA dropdown lists)
- Select the bad value from the first list
- Select the good value from the second list
- SAVE ALL
- To fix records one at a time (3)
- Select a record
- Click EDIT
- Set the country to its correct value
- Stop when the list is empty
If your database has credit card records in it you'll need the encryption key to fix the credit card countries.
Warning: Do not click the FINISH MIGRATION button yet. This button makes database changes that might not be compatible with 4.12, 4.11, and earlier xTuple ERP clients. Only run this immediately before upgrading to 5.0.0 (4).
Once you get the hang of it, you can do all of this in your production database, too. The application will work fine and you'll have cleaner data. Just do not FINISH MIGRATION yet.
Normally you would document and correct customizations later in the process. This time do some cleanup first.
Why: For purposes of xTuple ERP+CRM 5.0.0, you might need to change a lot of customizations because the database structure and script toolbox changed. You have a choice:
- Remove customizations you don't need and fix what's left
- Fix everything
- Fix what you use and try to ignore the stuff you don't
If you've used xTuple ERP+CRM for any length of time, you probably have a few spare copies of reports, scripts that you got from tech support, or an extension that you tried and didn't find useful. Get rid of those. The less cruft there is in your database, the less work later to fix script toolbox calls and outdated database references.
How big a problem is this? Look at the table above: The
simple database had more than 100 unused report definitions and the
hard database had almost 200. The application only uses the highest graded version and you should keep grade 0 for comparison, plus versions added by extensions. Some extensions install triggers; disabling the extension does not disable those triggers, so they can drag the system down.
The first pass at cleaning up is really easy. It'll take some time but it's worth it. Keep notes of what you remove. If you make a mistake, you'll be able to fix it by copying from your production database backup. You are working in a pilot, right?
Start with extensions you don't use. Go to System > Design > Packages and review the list. Remove any package that is not enabled. If it's disabled then you're not using it. If it defined any triggers, it might even be slowing down the application with absolutely no benefit.
Next visit System > Design > Reports
- Clear the Organize By Package checkbox
- Make sure the Package column is visible (if not, right-click on the table header and select Package)
- Make sure the list is sorted by report name and, if you're running v4.11.x, by grade
- If there's a report without a name, remove it — it's never going to be used
- For each report name
- If the report is in a package, remove all copies except the highest and lowest grade in the package
- If the report is not in a package, remove all copies except the highest grade and grade 0
Repeat that process for the MetaSQL queries at System > Design > MetaSQL Statements. Be careful about names here, as both
name are meaningful, not just
name. As with reports, only the highest grade copy of a MetaSQL query is used. Keep the others only for reference if they're part of an extension or are the grade 0 version outside an extension.
System > Design > Scripts is a little different. When an application window opens, all enabled scripts for that window get run. The cleanup task here is to remove the disabled scripts. That's really easy:
- Clear the Organize By Package checkbox
- Make sure the Enabled column is visible
- Click on the Enabled column to sort by it
- Remove all of the disabled scripts
System > Design > Screens has both
enabled columns. Start with the Scripts strategy — remove the disabled screens — then switch to the Report strategy of comparing grades.
The next phase is harder — figuring out what's installed and enabled but not actually used or useful. For example, both the
hard databases had the
pre380 extension installed. That extension was designed to help people migrate from xTuple ERP+CRM v3.7.x to v3.8.0. If you're running version 3.8.0 or later, you don't need it. Remove it.
How much effort you expend here is up to you. Anything you can remove now won't get in your way later. Anything that's left you will need to fix. The sure but painful way to find out what's used is to not fix it; someone will eventually use that customization and it won't work. Yuck.
Run through your basic business processes to make sure things still work as expected. If everything works as expected then you can remove this same trash from your production database. That'll simplify life whether you upgrade to 5.0 now or later.
This step is optional here. You might have to do it again later. Doing it now will:
- Make the next step, fixing script toolbox calls, easier
- Give you the benefit of testing the extension upgrades with your current xTuple ERP+CRM release
Why: If you upgrade the extensions to versions that are compatible with both your current xTuple version and 5.0.0, and things test cleanly, you can apply them to your production database. Your users can get the benefits of the new features and bug fixes while you'll have less work to do later. Check the compatibility matrix.
xTuple ERP+CRM has been easy to extend with scripting since version 3.0.0. The details of scripting recommendations have changed over time and now it's time to finish that transition.
Why: We've learned a lot in the past 10 years about how to write scripts and how to make the application scriptable, yet our toolbox class looks much as it did in 2009. We documented what we learned and warned that the toolbox would change. It's time to reduce our own cruft, if only a little bit.
The script toolbox is not going away. The task at hand is to replace old toolbox calls with newer, better methods. We've written a tool to help with this — the
deprecatedtb extension (see Resources below):
- Make a list of all scripts that you have customized. If they're in an extension (recommended), keep track of the extension.
- Export all of your scripts from the database to disk files, grouped by extension if appropriate
deprecatedtbinto your pilot database with the Updater
- Log in to your pilot database with the desktop client matching your current database version
- System > Design > Find and Fix Deprecated Toolbox Calls
- Click QUERY (1)
At this point you should see a list of scripts and potential problems. When you click on a line in the list, you'll see suggestions on how to fix that line (2). If you click on multiple lines, suggestions for all selected lines should appear. Clicking the EDIT button will open the script editor for each selected script (3).
- Edit each reported script
- Fix the reported problems (4)
- If it's in a comment, consider simply removing the comment
- Look for multiple instances of the problem and fix all of them
- When asked whether to save to the database, a file, or both, click DATABASE AND FILE and overwrite the file you exported a few minutes ago
- Test the modified script and re-edit if necessary
- Click QUERY
Repeat these steps until the list is clear. If there's an entry in the list from an xTuple-supplied extension, check the compatibility matrix again; it's probably in a version that's not compatible with xTuple ERP+CRM v5.0.0.
Keep these disk files. You'll need to apply them to your production database, either now or during the upgrade. In a perfect world these would be committed to a source code repository somewhere and incorporated into an xTuple extension for private use.
Run through your basic business processes to make sure things still work as expected. If everything works as expected then you can apply these same changes to your production database. Doing this now will help the production upgrade will go more smoothly.
Upgrade the Pilot — For Real
It's finally time to upgrade the pilot database.
Fix countries, part 2 — Finish the country migration
If you have credit card data, now is the time to load up the Fix Country window and finish the job. If you don't have credit card data, the upgrade packages from 5.0.0-beta and later can do finish the country data migration for you. The distinction is necessary because the Updater cannot decrypt and encrypt your credit card records.
This last step makes permanent changes to the data that are not backward compatible with xTuple ERP+CRM 4.x, which is why you have to wait until you're absolutely ready to upgrade this database to 5.0.0.
- CRM > Utilities > Fix Countries
- Click FINISH MIGRATION
Upgrade the core and extensions
Now go through the standard Updater process with the .gz file for 5.0.0 and each of the extensions you didn't load earlier. This might take a while, depending on your hardware and the size of your database. In my testing, upgrades took between 5 and 30 minutes. Don't be surprised if the Updater runs for several hours on some large databases. The amount of RAM on the server and the type of storage -- solid state drives vs. spinning hard disks -- can make a huge difference, too.
Every database is different and the problems that will arise are very hard to predict. Over the years of running an ERP system, any system, data problems will inevitably arise — bad or incorrect data, or the right data entered in the wrong place. Two early pilots (not the two described here) during the 5.0.0-alpha stage had problems with the fixCountry utility — some country values needed to be updated manually. Another pilot database had mismatches between the CRM Accounts list and the sales reps. Someone else had problems with dates, where incorrect data entry created records dating back to before the foundation of xTuple (one or two went back to the Pax Romana). As we improve the software, more of these problems will be revealed and fewer of them will recur.
Try the upgrade. Find the problems. Figure out and document or script a fix. If possible, correct the data problems in your production database so you don't have to fix them again. Then try the upgrade again.
Several people have reported running out of shared memory during the upgrade. This is a server configuration issue, not a bug in the upgrade scripts or the Updater itself. The most consistent solution to this error has been to increase the
max_locks_per_transaction setting in
postgresql.conf and restart the database server. After the upgrade finishes, drop this setting back down to its previous value; the change is only necessary for the upgrade itself, which does a lot of data processing in a single transaction.
As mentioned earlier we made significant enhancements to the database structure to support changes to both the CRM module and tax handling. This had repercussions throughout the application and, for our purposes here, on customizations. This is the hardest part of the upgrade to 5.0. The bulk of the time spent preparing the
hard database went to this phase.
Why wait until after upgrading the pilot instead of doing this before the upgrade? The problems we're looking for in this Database Updates phase cannot be tested before the upgrade because they don't exist before the upgrade — they aren't problems in xTuple ERP+CRM 4.x.
We wrote the
updatetodb extension to help find problems and suggest solutions for these outdated database references. The suggestions are only rough recommendations. The best fix will depend on context. Another caveat: There will be a lot of false positives — reports of problems that don't need to be fixed. The tool helps with those but it's not perfect.
- Install the
- Log out of the desktop client and log back in
- System > Utilities > Find and Fix Outdated Table References and wait — it's running a really big query. It might take several minutes for the window to open the first time.
- Sort the contents in an order that makes sense to you (I used extension, document type, name)*
- Decide what to do for each reported problem:
- View the problematic document using an appropriate tool
- Scripts — script editor or dump to disk and use a text editor
- Report — OpenRPT
- MetaSQL — MetaSQL editor or dump to disk and use a text editor
- Stored procedure — pgAdmin or dump to disk and use a text editor
- There are some "problems" that do not need to be fixed. This can happen if a script has been written to work with versions 4.x and 5.x, for example. In these cases make sure just the one line is selected in the utility window and click IGNORE** (then wait for the query to run again). This hides the "problem" until either you click the RESET button or quit the application and log back in.
- If the problem does need to be fixed
- Fix the problem
- Save the fixed document (script/report/...) to a disk file, just like you did above, so you can reapply the changes during the final upgrade
- Load the fix into the database
- View the problematic document using an appropriate tool
- It's a problem so fix it
- It looks like a problem but really isn't
For example, the
cntct_phonecolumn has been removed from the
cntcttable. The following change converts a bad query to a good one:
SELECT cntct_id, cntct_name, cntct_phone FROM cntct; -- old SELECT cntct_id, cntct_name, getcontactphone(cntct_id) AS cntct_phone FROM cntct -- new
Both queries contain
cntct_phone. The old query needed to be fixed. The new query can be ignored.
- It is a bad database reference that will never be used
For example, the xTuple Connect extension 3.9.0RC2 will work with both 4.x and 5.x xTuple databases. The scripts switch automatically, either choosing different queries to run or passing different parameters to shared MetaSQL queries.
- The "problem" is in a comment
Remove the comment or reword it to avoid the bad reference. If you can't change the comment, ignore it.
Make sure to keep disk files for anything you change. You'll need to reapply these changes during the final upgrade of your production database. Keep them separate from the script toolbox changes. The difference is that the script toolbox changes are backwards compatible but the database reference changes are not.
Test Customization Changes
Test every script, every query, every report that you changed. Run through every business process to make sure it works as expected. Keep a careful eye out for database errors. I found the Database Log window very helpful for catching script and query bugs (System > View Database Log). Just keep that window open and look for problems. Occasionally click CLEAR so you don't have to scroll back to far.
Now go through all of this again, starting with the backup of the production database and running all the way through testing your customization changes. Load the customization changes from the disk files and use the utility extensions to double-check for script toolbox problems and bad database references. Test.
The success of this upgrade will depend directly on the amount of effort you put into piloting and remediation. Don't skimp here.
Upgrade Your Production Database
The hard work is done. All that's left is the plug-n-chug, albeit stressful, upgrade of your production database. That's OK. You've rehearsed this at least twice now. You've documented the exact steps it'll take to get there. So, one last time:
- Back up your production database
- Fix countries, part 2 — Finish the country migration
- Upgrade the core xTuple ERP+CRM
- Upgrade the extensions
- Load your customizations
- Sanity check
- Go live!
fixCountry extension adds a single menu item — CRM > Utilities > Fix Countries. This opens a window that helps you standardize the names of countries used throughout the database. It tests and helps update about a dozen tables. You can download it from GitHub.
I saw a lot to write about while working on these two pilot databases. I've tried to stay focused here on getting one job done — upgrading to 5.0.0. Keep an eye out for blog posts on technical topics that may help simplify your database, your code, and your database administration.