Where are the database changes for v8 listed?

pjdm · 6 April 2023 15:41

Hi, this is about the tables changing between versions and not seeing where that happens in the notes.

I’m running a quick test of v8.2.4 on a bitnami/docker container. I’m comparing to my server version which is now v8.2 but has always had permission problems, discussed in other topics. The docker version works very well for testing so far. I imported my V8.2 database to v8.2.4. I checked to see if any fields changed in the DB through v8.24 and only one field shows an “ALTER…” in the notes from the developers in the upgrade discussion. There are a lot more changes and maybe they could be listed with each version in the future.

When I ran 8.24 in a test scenario, I found 8.24 had new fields in tables that changed somewhere yet I can’t find where that happens. The effect is there will be errors. If you try to use your existing DB name during an upgrade you will lose the entire DB of course since the installation process DROPS the old table then builds the current table with new fields. This is great for new users but previous users have their DB wiped. So, our recourse seems to be to import the old DB to the v8.2.4 DB but the fields are different.

I can’t find the changes listed in the notes. Typically, in my limited experience, other mysql systems provide a simple SQL script to check for new fields or modified fields and then alter the tables and import the data to the new format. That is done as part of the upgrade process and it doesn’t seem too difficult. I don’t see that here yet we have millions of users so some of us must be scripting the new table layouts? I’d like to see the script or help create one for upgrades but unless we have published DB formats and table fields that are changing between versions we cannot know what changed and how to easily upgrade the previous DBs.

Has anyone run into this issue or are people just using new DBs each time. Here is an example of the table “inbound_ email” fields in early V8 and latest 8.24 with major additions. This will fail if you try to use your existing DB. Is there an easy way to assess field changes in these new versions so we can script some fixes? Thanks for the latest work.

Early V8 inbound_email table:

--
-- Table structure for table `inbound_email` with 21 fields
--
CREATE TABLE `inbound_email` (
  `id` varchar(36) NOT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT 'Active',
  `server_url` varchar(100) DEFAULT NULL,
  `email_user` varchar(100) DEFAULT NULL,
  `email_password` varchar(100) DEFAULT NULL,
  `port` int(5) DEFAULT NULL,
  `service` varchar(50) DEFAULT NULL,
  `mailbox` text DEFAULT NULL,
  `delete_seen` tinyint(1) DEFAULT 0,
  `mailbox_type` varchar(10) DEFAULT NULL,
  `template_id` char(36) DEFAULT NULL,
  `stored_options` text DEFAULT NULL,
  `group_id` char(36) DEFAULT NULL,
  `is_personal` tinyint(1) DEFAULT 0,
  `groupfolder_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the latest inbound_email table with a lot more fields and nothing can be found showing these changes. Someone is deciding to modify the DB so why not publish it with the new version so we can write a script to update existing DB from V8.23 to V8.24 (for example)?

--
-- V8.2.4 Table structure for table `inbound_email` with 33 fields
--

DROP TABLE IF EXISTS `inbound_email`;
CREATE TABLE `inbound_email` (
  `id` varchar(36) NOT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT 'Active',
  `server_url` varchar(100) DEFAULT NULL,
  `connection_string` varchar(255) DEFAULT NULL,
  `email_user` varchar(100) DEFAULT NULL,
  `email_password` varchar(100) DEFAULT NULL,
  `port` int(5) DEFAULT 143,
  `service` varchar(50) DEFAULT NULL,
  `mailbox` text DEFAULT NULL,
  `sentFolder` varchar(255) DEFAULT NULL,
  `trashFolder` varchar(255) DEFAULT NULL,
  `delete_seen` tinyint(1) DEFAULT 0,
  `move_messages_to_trash_after_import` tinyint(1) DEFAULT 0,
  `mailbox_type` varchar(10) DEFAULT NULL,
  `template_id` char(36) DEFAULT NULL,
  `stored_options` text DEFAULT NULL,
  `group_id` char(36) DEFAULT NULL,
  `is_personal` tinyint(1) DEFAULT 0,
  `groupfolder_id` char(36) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `auth_type` varchar(255) DEFAULT 'basic',
  `protocol` varchar(255) DEFAULT 'imap',
  `is_ssl` tinyint(1) DEFAULT 0,
  `distribution_user_id` char(36) DEFAULT NULL,
  `outbound_email_id` char(36) DEFAULT NULL,
  `create_case_template_id` char(36) DEFAULT NULL,
  `external_oauth_connection_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

pgr · 6 April 2023 20:49

You’re taking a complicated route… why?

You should never migrate and upgrade at the same time, which is what you’re trying to do. You’re missing out on any changes that the upgrade scripts run. Sometimes they need to do adaptive work.

Just install the same version you have on a new server; then move the database; then upgrade with the upgrade packages, normally.

pjdm · 7 April 2023 05:00

I see your point(s) and understand there are embedded updates in the version upgrades that are needed. I checked and my DB schema is from v8.2 so I would need to go back and update to 8.2.4 to incorporate those DB schema changes and possibly other changes you spoke about.

My unique issue is that my Centos7 server has never worked with SuiteCrm and I’ve had lots of people and consultants try for half a year. The stability is not there primarily with cache and permissions. I gave up 6 months ago. It won’t even open up any longer. I just have my DB.

Most recently, I was able to run a very simple docker version in seconds on my win11 machine and Suitecrm runs perfectly v8.2.4. That’s docker and Bitnami/Suitecrm. I could go backwards and run 8.2 easily in docker with one change. The bitnami notes for Suitecrm only talk about updating the version in docker. They do not provide any knowledge about your points that the DB needs to be updated by the upgrade process. I do not know how to upgrade a docker image from 8.2 to 8.2.4. I think that will be another test of time and patience.

My easy way is spin up a new server and try to get it running on something better than Centos7. For now, I think I will go back to v8.2 on my docker container and keep operating until I figure out how to easily complete the upgrade process.

Thanks for setting me straight.

pgr · 7 April 2023 06:53

Although I like Bitnami, they’re a cool project with nice support, I gave up on it for SuiteCRM many years ago. They’re good to start, but overly complicated to do PHP upgrades and other changes in the stack.

SuiteCRM is a breeze to set up and use on Ubuntu. One command to set up a lamp server. Permissions are straightforward. Tutorials abound.

pjdm · 7 April 2023 17:59

Understood. I used the docker simply to test out the 8.2.4 on my win11 machine and it was flawless and motivated me to try and get all my last months of materials into the CRM.

I will take your advice and run the Ubuntu server, SuiteCRM and use that for ongoing work.

Thanks very much, I know you do a ton of work on this site and I know many people appreciate the time and effort to steer us in the right direction (quickly and accurately).

pjdm · 8 April 2023 03:44

Further investigation: I checked the releases (docker carries them all) and any version and OS combo can be spun up in about a minute for quick testing.

Checking my single table (inbound_email) which has 21 fields to see where that specific change was made:

Suitecrm v8.1.0 has 21 fields in inbound_email table
Suitecrm v8.1.2 has 33 fields in the same table indicating mods occurred to the DB

Conclusion is that the upgrade process did a lot of work on the db. I’ll have to figure out how to preserve the DB and properly upgrade it. It is too big to start over.

pgr · 8 April 2023 08:45

this is the strategy

What’s keeping you from doing that?

pjdm · 8 April 2023 20:40

Nothing now. Until you pointed it out and I determined that the DB schema was modified, I had to figure out which version of SuiteCRM to go back to. How would anyone know which version their SuiteCRM DB was developed from?

I’ll do the work to ensure my DB is the right version for the SuiteCRM version going forward.

As a note to users: There is nothing to help figure out which version a DB was created from. If you mess up the upgrade or do what I did (unintentionally) you will be in the same position and possibly have errors that are related to the DB having a old or corrupt schema.

I do have suggestions that could help based on my limited experience. Typically, DBs that get modified have markers showing the version. I do not see that in my SuiteCRM DB. Generally, I’ve seen upgrade scripts available when developers issue upgrades that bring older DBs up to speed or at least verify the structure. Embedding the changes in hidden code without expressly printing out verification and notifying users of invalid schema details is going to only cause problems and a lot of frustration that can be avoided.

I think my DB should have a marker saying V8.1. My installation program at the beginning should verify that the incoming DB matches the expected incoming schema. The upgrade should not erase and DROP all my tables without an express warning and input from the user. That’s how this all got started.

I probably am not the only novice user with a DB that doesn’t have the expected schema for the version being used. To help, I will try to script my own schema checker for my existing version and the new version. If it works then maybe others can check if they wish. If a sample of people have no problems then my issue is unique. If problems show up then we have something possibly to act on.

Thanks again.

pjdm · 27 April 2023 14:37

Update: When doing a repair, SuiteCRM automatically detected the database variances and offered to update the tables with a click command. once that was done, the database table referred to above and a few others were updated.

This is a clever feature and one of the reasons to do a “Repair” early on when using V8 versions.