Core updater: database migration support

[datakick]

New version of coreupdater module was released yesterday, and it contains one new functionality I'd like to talk about a little bit:

Database schema comparator and migration

What does it mean? It means that core updater will now look into php codebase and extract metadata from object model classes (like Product, Customer, or Order). These metadata describes exactly how database should look like -- what database tables should exists, what columns should they contain. It describes primary or foreign keys, column type, default values, collations, etc..

Core updater will use these collected information and compare them against your actual database schema. And if there are some differences, it will report them. And it will also allow you to fix them.

This essentially makes php codebase single source of truth in regard to database structure.

When you install your system from scratch you shouldn't see any database differences. But those of you that are running your thirtybees system for a long time now will see a lot of differences. It could happen for many reasons

• we developers tend to forget about implementing respective sql migration when we change php code
• when we actually implement such migration script, it can contain errors. After all, php devs aren't usually mysql experts
• also, some modules can change database schema for their needs

Why do these differences matter so much? That's because PHP code operates under some assumptions. For example, it expects that order reference code can contain 11 characters. The php code that generates unique reference code for order can fail terribly if the actual database column can store only 10 characters. The error can occur right there during reference code generation (then it's easy to find). But it can happen much later. With these data consistency issues it's hard to figure out how they happend, and how to fix them.

By aligning your database structure with expected database schema, you are much closer to (my favourite) vanilla thirtybees installation. Thirtybees core code will run more smoothly in such environment. I'm not saying it will not run OK on modified database schema, just that there can be some very strange behaviour sometimes.

How to use this new tool

Please be aware this is experimental feature, and requires some database knowledge. It's not very user friendly. And some messages will probably scare you -- but that's intended behaviour. It is very powerfull tool, but we should use it very carefully. 

There is new tab named Database schema (for developers). This tab will display you all (relevant) database differences.

Note that thirtybees core needs to support this functionality. At the moment, this is supported in bleeding edge only. In current version 1.1.0 or older, this tab will simply inform you that the database comparison functionality is not supported. So, if you want to test this new functionality, please migrated to bleeding edge.

This is how the list of database differences looks like. You should review all differences, starting with those marked as   critical 

While it says critical, the reality is usually not so grim. Obvisouly, missing columns or tables are indeed very serious issues, and you should fix these immediately. But different column type is not such a big deal. 

All database differences can be fixed. You can simply click on Apply fix button to adjust database.

Please make sure you understand what this means!!! Some database differences can be actually valid and wanted. For example, some module might have changed database structure of core table in order to provide some additional functionality. *Fixing* this table back to original state might break the module. One such module is *Multiple feature values* -- this module drops unique key for one core database table in order to actually save multiple feature values into the database.

If unsure, please ask here -- it also gives us opportunity to learn more about different type of customizations.

And some video:

 

[musicmaster]

I like the idea. But I have doubts about the proposed implementation.

In my experience missing tables, indexes and auto-increases are the main source of problems. The chance that fixing them will cause problems is small.

But extra fields and fields that are bigger than default are a different story. Those are usually implemented for a reason and encouraging users to undo them might easily damage the working of modules. Panda has an extra "hover" field in the tb_image table and I have implemented a change in the tb_feature_value_lang table that allows you to enter more than 255 bytes. I wouldn't want someone who maintains my websites when I am on holiday to fix those "critical" "problems".

[zen]

Quote

What does it mean? It means that core updater will now look into php codebase and extract metadata from object model classes (like Product, Customer, or Order). These metadata describes exactly how database should look like -- what database tables should exists, what columns should they contain. It describes primary or foreign keys, column type, default values, collations, etc..

does it also look into overrides php code to extract added fields needed by modules or personnal modifications ?

[movieseals]

It does not seem to work for me.  I get the following error: ajax call failed with error code 500

[datakick]

25 minutes ago, musicmaster said:

I like the idea. But I have doubts about the proposed implementation.

In my experience missing tables, indexes and auto-increases are the main source of problems. The chance that fixing them will cause problems is small.

But extra fields and fields that are bigger than default are a different story. Those are usually implemented for a reason and encouraging users to undo them might easily damage the working of modules. Panda has an extra "hover" field in the tb_image table and I have implemented a change in the tb_feature_value_lang table that allows you to enter more than 255 bytes. I wouldn't want someone who maintains my websites when I am on holiday to fix those "critical" "problems".

I share your concerns. That's actually the reason why I wrote that extreme caution should be taken when applying *Fixes* -- people should know what they are doing. They should try to understand why these differences exists.

But, ultimately, these differencies must be fixes. The php code must be aware of the actual database structure, otherwise various problems will occur.

Take this example:

I increased size of some column in core table to allow more characters, and imported the data directly using sql script. But then I go to UI form in back office, and try to edit these newly imported entries. Controller will show it correctly in input field. But when I hit save (without actually changing anything), I get back an error -- controller says that the length is larger than allowed 80 chars. And we have a problem. Thats because php code validated input values against the metadata stored in php code. So, I now have very inconsisten system.  

If we need this type of modification, there have to be a mechanism to do it. And not force it.

For example, we can tell core via an override that the size should be 128 instead of 80. Core will pick this information up, and will work correctly. It will consider input values to be max 128 chars, and everything will work as expected. CoreUpdater will pick this as well, and actually offer you to migrate this database colum by simple click. It's a win-win.

But yeah, there's still a lot of work ahead of us. 

[datakick]

2 minutes ago, movieseals said:

It does not seem to work for me.  I get the following error: ajax call failed with error code 500

And what is that 500 error? Please look into your error log and file a bug

[datakick]

13 minutes ago, zen said:

does it also look into overrides php code to extract added fields needed by modules or personnal modifications ?

Yes

[movieseals]

1 minute ago, datakick said:

And what is that 500 error? Please look into your error log and file a bug

Will do.  We file a bug report via Github?

[datakick]

Just now, movieseals said:

Will do.  We file a bug report via Github?

Yes, github is the best for this

[datakick]

48 minutes ago, musicmaster said:

Panda has an extra "hover" field in the tb_image

I forgot to mention that this comparison tool does not actually report extra columns and tables (it can detect them, but it disregard these differences silently). It only tracks known database objects, anything extra is silently allowed...

I have some problems with that as well, though. For example, extra column in core table can be a very serious problem if it is defined as a NOT NULL, and does not have any default value. Client of mine installed a module that created such column + added override for controller to ensure that some value is always passed. But then he disabled this module -- and his shop stopped working, because insert into this table throws mysql exception. Again, it would be best if these kind of modification were described in metadata layer, so core knows how to deal with it. 

Similarly, extra unique key can cause a lot of issues.

[musicmaster]

I once considered making such a thing for Prestools. But I discarded the idea when I saw how Prestashop constantly changed the database structure - the indexes the most. Even core indexes were sometimes different - as an extra field was included. 

As many of those database variations will be maintained in shops moving to Thirty Bees my expectation is that you will see a wide variety of structures and most of those differences will be irrelevant.

My suggestion would be to divide the output in three categories:

 - the really critical: missing tables, fields, indexes and field properties. The user should be recommended to fix these.

 - the potentially problematic, such as the NOT NULL field you mentioned. The user should be recommended to fix those only if there are problems and he has checked that they aren't used by some module.

 - the nearly certainly innocent - such as id fields with length 10 instead of 11.

Making a dump of the repaired tables might help when things go wrong.

Edited November 22, 2019 by musicmaster

[toplakd]

I tested this "database schema" function on exact copy of my shop which is latest bleeding edge.

After applying all fixes (38pages of them)  tested many things and everything is working fine.

What would be nice if there was a button "apply all" 🙂

 

I will do some more testing, and if nothing shows up, i will apply the fixes to main shop during closing time in holidays.

 

99% of fixes were

has data type int(10) unsigned instead of int(11) unsigned

should use character set utf8mb4/utf8mb4_unicode_ci instead of utf8/utf8_general_ci

 

Thank you.

 

 

Edited December 5, 2019 by toplakd