unknown column active in field list - ps_order_state

[musicmaster]

When I make a bankwire payment in the new 1.1 version the next screen is an error screen that there is a query for a column "active" in the table ps_order_state. Indeed when I checked this column doesn't exist in this table.

When I look at the code it seems that this is some kind of coreupdater check that is done for many tables and goes wrong here.

I "solved" the problem by manually adding an active field to this table. 

When I checked on my server - where I had started with a direct payment instead of bankwire - I noticed that the system had added there an active field by itself.

 

Edited July 29, 2019 by musicmaster

[datakick]

Core Updater allows to easily switch between two versions of thirty bees. But at the moment it handles code only -- php and javascript files gets updated. The problem is with database changes. If the new code depends on some new database column or database table, it doesn't help. These new database objects are not automatically created.

Historically, this kind of problem was solved by migration scripts. Every release come up with some sql file that was executed during update. This sql script contained DDL statements to alter database schema. This approach has many drawbacks, the most important are these two:

• these scripts aren't usually idempotent - which means you can't run them multiple times to achieve the same result. The second execution often ends up with error (because the database object was already created)
• there is no reverse operation - you can only upgrade, but there's no way to do downgrade. The only way to revert changes was to create whole database backup

These issues are the reason why thirty bees developers decided to drop this approach and come up with new one. The proposed way to handle database migration is based on different principle. Php code will contain metadata that describes complete database structure it requires. When you upgrade (or downgrade) your thirty bees to different version, these metadata will change together with code. And core updater will use them to find all differences to your current database structure and fix them. So, for example, if you want new database column, you will just need to add new item into php file. No muss no fuss.

Unfortunately, this mechanism is not released yet (but it's implemented and under testing, so you'll see it soon).

Now, back to the issue at hand.

There are two database structure changes between 1.0.8 and 1.1.0 - tables tb_order_state and tb_order_return_state received new column named active. Because the metadata-based comparison is not released yet, and because there is no support for legacy sql migration script, there had to be implemented nasty workaround to create these new columns. Basically, whenever the OrderState object is created, thirty bees checks that the active column exists. And if not, it creates it.

This adds some performance drag. And, unfortunately, it also contains a bug. The check uses following sql query:

SELECT `active` FROM `tb_order_state` LIMIT 1

But, obviously, this sql query fails terribly when active column haven't been created yet. In normal mode, thirtybees swallows database errors, so this check somewhat *works*. But if you have debug mode enabled, this invalid sql query is converted into php exception like the one @musicmaster reported. 

The workaround around this issue is simple:

1. go to Advanced Parameters > Performance
2. disable Debug mode
3. go to Orders > Statuses

And that's all. Both columns should be created now, and you can re-enable debug mode

[musicmaster]

Thank you for the explanation.

[musicmaster]

Isn't it a better solution to use SHOW COLUMNS?

Something like:

'show columns from ps_order_state where field="active"'?

[datakick]

6 minutes ago, musicmaster said:

Isn't it a better solution to use SHOW COLUMNS?

 Something like:

'show columns from ps_order_state where field="active"'?

Yes, we need to look at database structure. SHOW COLUMNS would work, but I guess it would be easier to query INFORMATION_SCHEMA.COLUMNS view directly.

It's probably not worth the effort to fix this, though --  this code will be removed completely once the schema comparison functionality is merged into core updater. 

[musicmaster]

There is also an option to do it without using the schema. In pseudocode:

select * from ps_order_state limit 1

$row = mysqli_fetch_array()

if(isset($row["active"])) ...

 

 

[datakick]

12 minutes ago, musicmaster said:

There is also an option to do it without using the schema. In pseudocode:

select * from ps_order_state limit 1

$row = mysqli_fetch_array()

if(isset($row["active"])) ...

Right, this would work as long as there are data in the table. Unfortunately mysql won't return column information for empty datasets