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.phpFetching info...

[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]

  On 10/8/2019 at 7:51 AM, lixotuka said:

Could this column have been generated by the importer?

Expand  

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?

  On 10/8/2019 at 8:41 AM, 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?

Expand  

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

[lixotuka]

  On 10/8/2019 at 8:46 AM, datakick said:

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

Expand  

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]

  On 10/8/2019 at 7:44 AM, datakick said:

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

Expand  

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]

  On 10/8/2019 at 1:37 PM, 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:

Expand  

@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]

  On 10/8/2019 at 1:37 PM, 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.

Expand  

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.

Expand  

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]

  On 10/8/2019 at 12:56 PM, 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?

Expand  

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]

  On 10/8/2019 at 5:11 PM, musicmaster said:

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

Expand  

Nope, This would lead to just another error message.

[musicmaster]

  On 10/8/2019 at 6:16 PM, Occam said:

Nope, This would lead to just another error message.

Expand  

Which error message? 

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

[Occam]

  On 10/8/2019 at 7:06 PM, musicmaster said:

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

Expand  

😊 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]

  On 10/8/2019 at 3:50 PM, datakick said:

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

Expand  

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

[datakick]

  On 10/9/2019 at 8:23 AM, lixotuka said:

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

Expand  

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?

Expand  

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

Edited October 9, 2019 by lixotuka

[lixotuka]

  On 10/9/2019 at 8:38 AM, 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.

Expand  

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]

  On 10/9/2019 at 11:39 AM, 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).

Expand  

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]

  On 10/9/2019 at 11:44 AM, 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).

Expand  

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.