ERROR when click on Orders

[lixotuka]

Hi,

I get an error when I go to orders. It's related to the file OrderState.php file but I don't know why is suddenly happened.

Thanks in advance!

OrderState.php

[datakick]

Looks like your tb_order_state database table contains column named `name`. Thirtybees does not contains such column

SHOW CREATE TABLE tb_order_state;

CREATE TABLE `tb_order_state` (
  `id_order_state` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `invoice` tinyint(1) unsigned DEFAULT '0',
  `send_email` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `module_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unremovable` tinyint(1) unsigned NOT NULL,
  `hidden` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `logable` tinyint(1) NOT NULL DEFAULT '0',
  `delivery` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipped` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `paid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `pdf_invoice` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `pdf_delivery` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_order_state`),
  KEY `module_name` (`module_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

If this is the case, then you need to figure out why this extra column exists, and if you need it (this column was probably created by some module)

 

[lixotuka]

Hi datakick,

I think this has happened after making imports of products. Could this column have been generated by the importer?

Thanks in advance!

[datakick]

Just now, lixotuka said:

Could this column have been generated by the importer?

No. TB core does not modify database schema

[lixotuka]

I can't find the problem. I don't remember installing a module in the last few days.

Anyway, if the issue was caused by the installation of a new module, if I disable the module it will not solve the problem, right?

[datakick]

Did you check if tb_order_state indeed contains the 'name' column?

4 minutes ago, lixotuka said:

Anyway, if the issue was caused by the installation of a new module, if I disable the module it will not solve the problem, right?

it will not. And if the module is written badly, then even module uninstallation won't help. 

[lixotuka]

4 hours ago, datakick said:

Did you check if tb_order_state indeed contains the 'name' column?

Yes, tb_order_state contains the 'name' column (see attached picture).

Could this issue have been caused because we have imported the Prestashop order statuses to TB?

[Occam]

5 hours ago, datakick said:

Looks like your tb_order_state database table contains column named `name`. Thirtybees does not contains such column

You forget that that the column name has always been a required field in in order_state_lang, which is left joined in the sql query.

    public static $definition = [
        'table'     => 'order_state',
        'primary'   => 'id_order_state',
        'multilang' => true,
        'fields'    => [
            'invoice'      => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0', 'dbNullable' => true],
            'send_email'   => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'module_name'  => ['type' => self::TYPE_STRING, 'validate' => 'isModuleName', 'size' => 64],
            'color'        => ['type' => self::TYPE_STRING, 'validate' => 'isColor', 'size' => 32],
            'unremovable'  => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbNullable' => false],
            'hidden'       => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'logable'      => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbType' => 'tinyint(1)', 'dbDefault' => '0'],
            'delivery'     => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'shipped'      => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'paid'         => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'pdf_invoice'  => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'pdf_delivery' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'deleted'      => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '0'],
            'active'       => ['type' => self::TYPE_BOOL, 'validate' => 'isBool', 'dbDefault' => '1'],

            /* Lang fields */
            'name'         => ['type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'required' => true, 'size' => 64],
            'template'     => ['type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isTplName', 'size' => 64, 'dbNullable' => false],
        ],

This is nothing new, and like PrestaShop before tb 1.1.0 creates the database table tb_order_state during the install procedure, see /install/data/db_schema.sql:

CREATE TABLE `PREFIX_order_state_lang` (
  `id_order_state` INT(11) UNSIGNED NOT NULL,
  `id_lang`        INT(11) UNSIGNED NOT NULL,
  `name`           VARCHAR(64)      NOT NULL,
  `template`       VARCHAR(64)      NOT NULL,
  PRIMARY KEY (`id_order_state`, `id_lang`)
)

But there seems to be a bug in some fresh installations, because like shown in the previous post the order_state_lang fields are created in the database table order_state.

[wakabayashi]

53 minutes ago, Occam said:

This is nothing new, and like PrestaShop before tb 1.1.0 creates the database table tb_order_state during the install procedure, see /install/data/db_schema.sql:

@Occam I can only find tests/golden_files/db_schema.sql. Is this the file you meant? This file looks correct to me. But obviously there shouldn't be an id_lang and an name column in @lixotuka table. No idea how it got there :S

[datakick]

2 hours ago, Occam said:

You forget that that the column name has always been a required field in in order_state_lang, which is left joined in the sql query.

I didn't forget about that. This is actually the reason why I deduced there is extra name column in order_state table, as that's the only possible reason for db to throw this ambiguous column error.

2 hours ago, Occam said:

But there seems to be a bug in some fresh installations, because like shown in the previous post the order_state_lang fields are created in the database table order_state.

I don't think so. I've just tested fresh install of both 1.0.8 and 1.1.0, and both created proper order_state table, according to installation script.

I really think the problem here is related either to some third party module, or to some sql-based import error

[datakick]

2 hours ago, lixotuka said:

Yes, tb_order_state contains the 'name' column (see attached picture).

Could this issue have been caused because we have imported the Prestashop order statuses to TB?

How did you import the statuses? Did you use some sql script?

[musicmaster]

Your query will probably work when you change the last sentence into "order by osl.name".

But I would anyway check out what that name field is doing in the tb_order_state table.

 

[Occam]

1 hour ago, musicmaster said:

Your query will probably work when you change the last sentence into "order by osl.name".

Nope, This would lead to just another error message.

[musicmaster]

47 minutes ago, Occam said:

Nope, This would lead to just another error message.

Which error message? 

If you used backticks, did you do it correctly: osl.`name`?

[Occam]

12 minutes ago, musicmaster said:

did you do it correctly: osl.`name`?

😊 yep ... but, sorry, I should have read the whole message. It was caused by a module for partial shipment which was still installed and working but wasn't displayed in the modules section any longer since I upgraded my test shop to Bleeding Edge. (Just another 1.1.0 bug.)

The message disappeared after renamed the module's directory.

[lixotuka]

16 hours ago, datakick said:

How did you import the statuses? Did you use some sql script?

Yes, the statuses were imported using SQL, as my programmer tell me.

[datakick]

9 minutes ago, lixotuka said:

Yes, the statuses were imported using SQL, as my programmer tell me.

Then this issue was most likely introduced during SQL import - script probably dropped and recreated the table with incorrect columns. If there is no custom code (modules) that depends on these additional columns, I suggest your drop them from the table structure. 

Alternative, you can modify the OrderState.php file and change the order by statement, as @musicmaster suggested above. However, there is a strong possibility that there will be similar issues all over the codebase that would need to be fixed as well. Thirtybees simply does not expect these columns to exist, and that can cause many problems. For example, if these columns don't have default database value, it would not be possible to create new Order Statuses from the back office.

[lixotuka]

My programmer told me this:

Quote

It's fixed now, I just removed the line 153 that indicated in the file orderstate.php. can you check that you access correctly?

Now I can access the Order page, but I'm afraid it's not the right solution.

Edited October 9, 2019 by lixotuka

[lixotuka]

3 hours ago, datakick said:

Thirtybees simply does not expect these columns to exist, and that can cause many problems. For example, if these columns don't have default database value, it would not be possible to create new Order Statuses from the back office.

Yes, when I try to create a new Order Status it is assigned ID 0 and name is not displayed. Also, if I try to create a new one I get an error (see attached picture).

Edited October 9, 2019 by lixotuka

[datakick]

17 minutes ago, lixotuka said:

Now I can access the Order page, but I'm afraid it's not the right solution.

...

When I try to create a new Order Status it is assigned ID 0 and name is not displayed. Also, if I try to create a new one I get an error (see attached picture).

You are correct, it's not the right solution. Ask your programmer to fix the database schema for the table, including auto-increment settings.

[musicmaster]

2 minutes ago, lixotuka said:

Yes, when I try to create a new Order Status it is assigned ID 0 and name is not displayed. Also, if I try to create a new one I get an error (see attached picture).

That sounds like the id field in the database table doesn't have the auto-increase property that it should have.

Thirty Bees and Prestashop have more export option than import options. But you cannot just import them as database tables as the exports are almost always composed from several different tables. 

If you want to copy data from one shop to another I recommend my free tool Copy Shopdata.