February 6, 2017

Preparing for Fishbowl 2017

by Israel Lopez in Articles

FB2017

Preparing for Fishbowl 2017

Every new “year” release of Fishbowl has included some massive changes. This year is no different, and we have a big change to talk about: MySQL.

Here is what is changing, in the following areas Infrastructure, Fixes, and Features.

Changes

Infrastructure

  • Database Engine Changing from Firebird SQL to MySQL. See “What is MySQL?” below.

Fixes

As we saw in the 2016.14 release, there were some fixes that needed to get pushed out; mostly related to Accounting workflows that took priority on the update cycle. Most of the fixes left over are not super critical, thankfully. But they are important to those who include those specific workflows. I still recommend reading the Fishbowl Release Notes Wiki page to get a full update.

HOWEVER. There is a major bug fix that is included in this release.

  • Price / Cost Discrepancy [PCD]
    • AKA: Quintillion Error
    • Example, sometimes amounts that were $7.00 were being malformed to $7,000,000,000,000,000.00
    • This is something I have been tracking since 2012, but it has been fixed, and re-fixed since then.
      • Fishbowl has now confirmed this is fixed permanently. [Mark my words!]

Features

We are seeing changes to Fishbowl Functionality such as:

  • Improved Imports from QuickBooks Desktop
  • Improved Vendor Module Search Parameters
  • Improved Import Exports for Location CSVs
  • A tooltip to indicate the maximum number of characters in a Fishbowl Field. [Ex max 41 Characters for Names etc,.]
  • More Plugin Improvements for Troubleshooting
  • Fishbowl GO Improvements
  • Transfer Order CSV Imports
    • Commentary: Really cool for automating POS Integrations.
  • Audit Tables
    • Tables in Fishbowl will now track changes.
    • Expect databases to be larger overall after 2017.1

Those are some of the feature improvements seen in Fishbowl 2017, and we may dedicate some blog posts on key features as we understand them better.

For the rest of this post we are going to talk about MySQL, why the change is necessary, and how this will improve Fishbowl for the Fishbowl Community.

What is MySQL?

MySQL is a very popular database engine.  If your company has any kind of web-presence using software like Magento, WordPress etc,. You are already using MySQL on your website. MySQL is one of the most installed/discussed databases, more influential than Firebird for example. See ranking site http://db-engines.com/en/ranking.

What are the benefits to switching to MySQL?

The Executive Perspective

  • Performance & Speed
  • More Reliability Options
  • Increased Access to Technical Expertise
  • Expanded Platform Capabilities

However it must be explained, that none of these are “free” and none of these you will get with just installing Fishbowl 2017, they are merely stategic opportunities.

The IT Manager / Provider Perspective

  • Performance
    • Improving the performance with Firebird previously was difficult to improve, or not well understood.
    • There are vast resources out there to improve MySQL performance, and many more options.
  • Reliability
    • Firebird had no ability for clustering, in Firebird 3.0 there are some work being done for that, but it wont matter for us now.
    • MySQL has commercial & free options for clustering and hot-standby fail-over capabilities.
      • Setting this up however will depend on your operational budget.
  • Disaster Recovery
    • Similar to the reliability standpoint; Firebird simply had no ‘hot-standby’ options
    • There are way more backup options for MySQL
      • If you really wanted to; one could build a Disaster Recovery Site.
        • With Firebird this was not a ready option.
  • Experts
    • MySQL experts will be plentiful, and more likely than not local to your office.
    • Firebird experts were mostly in Eastern Europe/Brazil
      • Sometimes they were difficult to get a hold of if a Database Corrupted.
      • As an aside, I’ve caused my fair share of Database Corruptions.
        • Once by creating a table twice, and being unable to delete it. Bugs!
  • Apps
    • Firebird supplied by Fishbowl was designed to serve just Fishbowl
    • MySQL now could be used to support more than one app, including Fishbowl.
    • So expect to see some apps utilizing this to improve integration/data-sharing between Fishbowl and other MySQL apps.
      • For example, cross-schema (database) queries
  • API/SDK
    • No changes to the API/SDK are expected

However: This and everything above is highly dependent on your Technical Support Contract with Fishbowl.

Clarify with them before making any decisions.

Areas of Concern & Downsides?

I cannot stress enough how much of a big change MySQL is to Fishbowl. Not preparing for the upgrade will surely leave you with a broken Fishbowl Implementation, customer’s must prepare. Below are key areas that I would identify prior to the 2017 upgrade.

Data

The 2017 upgrade will convert your Fishbowl Firebird Database to MySQL, but it is not perfect. All Fishbowl data structures will be migrated for you. If you know you have something custom in your own database, keep in mind that will not migrate. See *Workarounds* below.

Characters

Fishbowl in the past has had a very difficult time handling characters, this has changed in 2017. Fishbowl handles all characters correctly now. However, previous data will prove to be a problem for almost all Fishbowl Customers.

Early on Fishbowl only supported a character set called ISO-8859-1, because that is the character set that QuickBooks Desktop Supported.

When Fishbowl 2017 converts a database, any non UTF8 Characters will be converted to this Character “☐”.  Keep this in mind as these characters come from systems like Word/Excel/Office usually via Copy and Paste.

Reports

Reports will mostly be unaffected, the same SQL used to generate reports will mostly be the same in MySQL. However, expect a few complex reports to be affected requiring adaptation from Firebird SQL to MySQL formats. In the end both are ANSI SQL Compliant, but there are slight syntax differences that could be a simple replacement of keywords, and the report will work the same.

Additionally, custom Bill of Material reports using a feature called “recursive sql” will not be able to be converted. It appears the answer to the missing “recursive sql” needs will be Custom Java code that traverses the Bill Of Material structure in FB2017. Contact us for more information.

See our future blog post about Queries and Reports for Fishbowl 2017.

Integrations

Integrations will most likely have the highest risk of having upgrade problems.  Most integrations have two connections to Fishbowl; the API/SDK and the Database connection.  Since we know the API/SDK wont be changing functionally in this upgrade, that side of connectivity is unchanged.  However, most of these integrations are using the Firebird SQL driver in .NET and Java languages.

Integration providers will need to upgrade their historical and latest code bases to support a MySQL connection.  Reach out to your integration provider early to find out what it will take to upgrade.  As far as our upgrade policy, please find that here.

Workarounds

It is time to come clean on triggers, stored procedures, views. It was an open secret that certain customers & consultants would have access to the Fishbowl Firebird sysdba password.  Most of the time this access was used to augment Fishbowl functionality at the database level.

Triggers for example allows changes at the database level when data changes. For example, used to fix data errors, or prevent activities in Fishbowl.

Views would help with exposing Fishbowl Data as a singular “Virtual Table” for users and applications; Excel or UPS Worldship for example.

Stored Procedures allow a level of database programming, manipulating data, updating records etc,.

These “workarounds” I would call them, would need to be converted/translated to MySQL.  Evaluate if they are still necessary in Fishbowl 2017, or if you need help with their conversion.

When should I upgrade?

Only when you are sure all four key risk-areas are addressed. But for most customers they have minimal risk. Here is some guidance for when you can upgrade, others will need to address the key risk-areas before they can upgrade.

  • Upgrade Immediately if you have all of the following:
    • No Custom Reports
    • No Custom Integrations
    • No Custom Plugins
    • No Custom Views
    • No Custom Queries

How can ILC Help?

We can assist a company with the upgrade process by ensuring all possible risks with upgrading is mitigated/addressed. We can be highly involved in your upgrade process, or we can assist in key areas to support your local staff.

However we do have schedules to keep and because of the nature of the upgrade we ask customers to reach out to us early in order to prepare an upgrade plan and communicate a schedule.