How to Fix Invalid Column Data Type when Reindex Magento 2

Hello Magento Friends,

In this blog, I am going to provide a solution to Fix Invalid Column Data Type when Reindex Magento 2.

Reindexing is required when the store data changes to increase the performance of the Magento 2 storefront. You can perform reindex through the command line or reindex programmatically in Magento 2.

But sometimes the reindex command throws an error of invalid column data type as shown in the below image

Let’s see How to Fix Invalid Column Data Type when Reindex Magento 2

Steps to Fix Invalid Column Data Type when Reindex Magento 2:

Step 1: Go to the below path

vendor/magento/module-eav/Model/ResourceModel/Helper.php

Insert the following lines into getDdlTypeByColumnType() function

   case 'int unsigned':
                $columnType = 'int';
                break;
            case 'smallint unsigned':
                $columnType = 'smallint';
                break;
            case 'bigint unsigned':
                 $columnType = 'bigint';
                 break;

So the getDdlTypeByColumnType() function before looked like this

public function getDdlTypeByColumnType($columnType)
{
    switch ($columnType)
    {
        case 'char':
        case 'varchar':
            $columnType = 'text';
            break;
        case 'tinyint':
            $columnType = 'smallint';
            break;
        default:
            break;
    }
    return array_search($columnType, $this->_ddlColumnTypes);
}

And now the getDdlTypeByColumnType() function has changed as below

public function getDdlTypeByColumnType($columnType)
{
     switch ($columnType)
     {
            case 'int unsigned':
                $columnType = 'int';
                break;
            case 'smallint unsigned':
                $columnType = 'smallint';
                break;
            case 'bigint unsigned':
                 $columnType = 'bigint';
                 break;
            case 'char':
            case 'varchar':
                $columnType = 'text';
                break;
            case 'tinyint':
                $columnType = 'smallint';
                break;
            default:
                break;
     }
     return array_search($columnType, $this->_ddlColumnTypes);
}

Conclusion:

Hence, this way you can get rid of Invalid Column Data Type while Reindexing in Magento 2. Additionally, if you face an error like “index is locked by another reindex process. Skipping.” while reindexing in Magento 2, you can easily solve it – Check the steps

Eliminate running the reindex command every time, instead perform reindexing with just a one-click from the admin dashboard. Integrate Magento 2 Admin Reindex Plugin.

Happy Coding!