Foreign Key problem upgrading from 8.3 to 8.5.14

Installation
1

I updated my local development server from 8.3.2 to 8.5.14 without any problem. Unfortunately when I upgraded one of my production websites the upgrade ran into Foreign Key Errors with Express and Oauth.

Here is the problem that I discovered. During the upgrade most of the tables were successfull converted to utf8mb4 character sets with utf8mb4_unicode_ci collations. Unfortunately, in some of the express tables there are columns that are hard coded with utf8 character sets when the tables were originally created. For example:

CREATE TABLE ExpressEntityAssociations (
id char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT ‘(DC2Type:guid)’,
source_entity_id char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘(DC2Type:guid)’,
target_entity_id char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘(DC2Type:guid)’,
…

The upgrade tries to add a foreign key constraint to (‘id’) in a table where id is create with a utf8mb4 character set referencing a second table where (‘id’) was created with the utf8 character set (i.e. see example above) This causes the Foreign Key error.

I know that everyone has said the way around this is to export the entire database, edit the resulting SQL file to carefully change the character set references to utf8mb4 and then import the editted file. I got around the problem by dropping all the foreign key constraints for the Express and Oauth tables, modifying the table columns with the character set hardcoded to utf8 to utf8mb4 in MariaDB and then used the alter table commands from the export file to recreate the foreign keys for the Express and Oauth tables. Seemed to work fine. I preferred this option since I have a very large database and did not want to run into problems on the import of a massive file.

Question – I still have one more production server to update and I am trying to minimize downtime. Does anyone see a problem with modifying the express and Oauth tables prior to the upgrade – i.e. dropping the foreign keys, changing the charater sets on the applicable rows and then recreating the foreign keys for the express and oauth tables before running the upgrade. Will there be any problems if the Express and Oauth tables are setup with utf8mb4 character and utf8mb4_unicode_ci collations in versions of concrete below 8.5?

Sorry, but I am not a SQL maven so I don’t understand all the implications of changing the character sets of specific tables on Concrete versions below 8.5

Thanks in advance for any help with this problem.

2

*odifying the character sets of specific tables prior to upgrading can be a valid approach to minimize downtime and avoid Foreign Key errors during the upgrade process. However, there are several factors to consider to ensure that this process goes smoothly:

  • Backup Your Data: Before making any modifications to your database schema, it’s crucial to create a backup of your data. This ensures that you have a fallback option in case anything goes wrong during the modification process.

  • Check Dependencies: Ensure that there are no dependencies on the columns you plan to modify. Changing the character set of a column might impact the application’s functionality if there are dependencies or assumptions based on the existing character set.

  • Test in a Staging Environment: If possible, test the modification process in a staging environment that closely resembles your production setup. This allows you to identify any potential issues or conflicts before applying the changes to your production server.

  • Consider Database Size and Performance: Since you mentioned that you have a very large database, consider the potential impact on database performance during the modification process. Modifying columns and recreating foreign keys can be resource-intensive operations, so plan accordingly to minimize downtime and performance impact.

  • Verify Compatibility: Ensure that the modifications you plan to make are compatible with the version of the software you’re using (Concrete versions below 8.5). While upgrading to utf8mb4 character sets is generally recommended for better Unicode support, it’s essential to verify compatibility with your specific software versions.

  • Document Changes: Document the modifications you make to the database schema, including any changes to column character sets and foreign keys. This helps ensure consistency and facilitates future troubleshooting.

*Overall, modifying the character sets of specific tables prior to the upgrade can be a valid approach to mitigate Foreign Key errors and minimize downtime.

Problem WARN
Problem WARN1895×986 202 KB

However, it’s essential to proceed with caution, thoroughly test in a staging environment, and ensure compatibility with your software versions before applying changes to your production server.*

Running Code Correction:

– Backup your database
mysqldump -u username -p dbname > dbname_backup.sql

– Connect to your database
USE dbname;

– Drop foreign keys for Express and Oauth tables
ALTER TABLE ExpressEntityAssociations DROP FOREIGN KEY constraint_name;
– Repeat for other tables as needed

– Modify character sets of specific columns
ALTER TABLE ExpressEntityAssociations MODIFY COLUMN id CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
– Repeat for other columns as needed

– Recreate foreign keys for Express and Oauth tables
ALTER TABLE ExpressEntityAssociations ADD FOREIGN KEY (id) REFERENCES other_table_name (id);
– Repeat for other tables and foreign keys as needed

– Verify that the modifications were successful
SHOW CREATE TABLE ExpressEntityAssociations;
– Repeat for other tables as needed

Replace dbname, username, ExpressEntityAssociations, constraint_name, other_table_name, and other placeholders with the actual names from your database schema.

Please make sure to thoroughly test these changes in a staging environment before applying them to your Kroger feedback entry code production server. Additionally, consider consulting with a database administrator or experienced developer if you’re not familiar with SQL or database management best practices.

3

Thank you for the detailed reply and examples. I will backup the database and test on a development environment. Sincerely appreciate the help.

4

Another approach is to export your database, update it, then create a new database and import the updated SQL. Then edit the config/db to point to the new database. The disadvantage is that any changes between the export and import will be lost, so no good for an eCommerce site. But if feasible, you have zero downtime.

A large database can often be historical garbage rather than just size of site. Extreme Clean may help.

5

Thank you for an alternative way to address the problem I will also look at your addon. The site has been around for quite some time. I have not doubts that the database could be made more efficient and smaller.