CMSB 3.71 installation issue with foreign keys FAILS & FIX

5 posts by 3 authors in: Forums > CMS Builder
Last Post: July 30   (RSS)

By kitsguru - July 25

I like using data integrity whenever possible, and now, with CMSB, we can. I created a new project for a small e-commerce solution for one of my clients.

I have 4 tables:

  • products,
  • product_variants FK to product.num,
  • orders,
  • order_items FK orders_num to orders.num and FK product_variant_num to product_variants.num

When I tried to move the site from development to staging, I used the 'restore from backup' installation method. It blew up do to Foreign Keys not existing.

The problem was the order of the tables in the backup file, which is alphabetical. However, it is necessary for the tables that the FK points to be installed first.

The backup file installed order_items first but failed because neither orders nor product_variants had been installed first.

WORKAROUND:

I manually created the 4 tables in the necessary order using phpMyAdmin: products first, then product_variants followed by orders and lastly order_items. I was able to simply copy the table structure (whew) from the database editor (MySQL source) which worked flawlessly.

Once I manually created the 4 tables, the 'restore from backup' procedure proceeded without error, and the world was right again.

NOTE:

I believe the solution is for the backup file to turn off Foreign Keys.

SET FOREIGN_KEY_CHECKS = 0;

-- Restore all tables

SET FOREIGN_KEY_CHECKS = 1;
Jeff Shields

By kitsguru - July 30

UPDATE

I discovered a new issue when copying the schemas from one project to another. The tables were created in alphabetical order. I created a new module—an updated shopping cart—that uses the schemas mentioned in an earlier post.

Between the backup, restore, and copying to another database, I discovered I had to rename my tables. This is not a simple process, as it turns out. The trick was to delete the foreign key (FK) columns in the database—using a database tool of your choice but not CMSB—and manually edit the schemas to remove the FK.

I then renamed the tables, adding a prefix that also included the sort order, 

products -> ec1_product
orders -> ec2_orders
product_variants -> ec3_product_variants
order_items -> ec4_order_items

This naming convention solves both moving the schemas and the backup and restore problems I encountered.

I then re-added the FK column in CMSB.

NOTE

When I tried to remove the FK columns in CMSB originally to rename the tables, I got an error. The sequence to rename the tables with referential integrity in play is quite complex, especially in a table with multiple FKs. CMSB does not currently attempt to deal with this scenario.

LESSON

I used openai to query recommended solutions for the problem, and it suggested the above naming conventions. Using a prefix and number for the table names is a simple solution.

Jeff Shields

By TimHurd - July 30 - edited: July 30

Hi Jeff,

Yeah dealing with FK columns can always be tricky when it comes to moving schemas between databases. However, if you disable the FK check constraints as you suggested earlier, I don't see why you would need to go through the whole process of deleting columns and renaming tables (assuming the target system is fresh).

It should just be a matter of disabling the FK constraints for the database, copy over the schemas and then re-enabling the constraints. 

I agree that simply trying to create tables alphabetically without disabling the FKs is going to give you issues, but disabling the FKs should also allow you to create the tables in any order you want. It turns out to be a pretty much straight copy over operation at that point since no referential integrity checks are being done with the FKs. 

If cmsbuilder doesn't disable the keys first then obviously that would be the issue. The rest should be pretty straight forward if I understand things correctly.

Now... if you are changing the table names for other reasons (like a custom prefix you want to use for the new database) then obviously you will have to update the FKs to point to the right tables. 

It just sounds like a lot of work you are going through to achieve something that should be a bit simpler. :)

Tim Hurd
Senior Application Developer
TimHurd.com

By kitsguru - July 30

Hi Tim,

I agree, it should have been straight forward. I got myself into trouble but copying schemas from one CMSB app to another.

CMSB couldn’t create the one table due to the order they were created as soon as I opened CMSB admin. Renaming the tables was a way to avoid the issue going forward.

Jeff Shields