MySql8 compatibility

[smoqadam]

Hello everyone,

In my latest project, I needed to connect Magento (v. 1.9.4.4) to Mysql 8 that I faced a problem during installation. I couldn't find any reference for it but I have fixed it.

It seems, in some cases, the describe method sometimes, add unsigned to int, tinyint, smallint, mediumint and bigint.

The files that I change fixed the issue for me, I just mentioned it here because if anyone else faced this issue, we can fix it for the next version.

These three files need to change in order to get the issue fix.
app/code/core/Mage/Core/Model/Resource/Helper/Abstract.php line 273
add

          ...
            case 'tinyint':
            case 'smallint':
            case 'smallint unsigned':

lib/Varien/Db/Adapter/Pdo/Mysql.php between line 1886 and 1896 must add some new cases:

            case 'tinyint':
            case 'tinyint unsigned':
            case 'smallint':
            case 'smallint unsigned':
            return Varien_Db_Ddl_Table::TYPE_SMALLINT;
            case 'mediumint':
            case 'int':
            case 'int unsigned':
                return Varien_Db_Ddl_Table::TYPE_INTEGER;
            case 'bigint':
            case 'bigint unsigned':

app/code/core/Mage/Eav/Model/Resource/Helper/Mysql4.php line 85

            ...
            case 'int unsigned':
                $columnType = 'int';
                break;

[tmotyl]

Hi
Can you provide a PR?
Also I would suggest we clean up the value "int unsigned" to "int" in the place we're getting it from DB, instead of patching all the places with "int unsigned".

[smoqadam]

@tmotyl I agree with your suggestion. I will take a look at code, and I'll send a PR

[smoqadam]

@tmotyl I had a deep dive into source code, and apparently column type is coming from different places during installation and during upgrade scripts (e.g. salesrule_setup.upgrade-1.6.0.0-1.6.0.1.php).

I suggest continuing with the changes I made because I'm currently using it in production and so far I haven't faced any issues.

If you agree I will send a PR for this.

Thanks

[tmotyl]

Hi
Please send a PR, then we will discuss the details.
From how many places the column type is comming from?

[discountscott]

We had this same issue on a few of our existing sites running against Mysql 8. Mainly when running the flat catalog/product indexer.

These changes worked fine for us. We can create a PR for this.

[CyberCr33p]

I get this error during reindex.

php -f /home/www/sites2/example.com/www/shell/indexer.php reindexall

[sites2@server140 ~/example.com/www]$ 
Product Attributes index was rebuilt successfully in 00:00:00
Product Prices index was rebuilt successfully in 00:00:00
Catalog URL Rewrites index was rebuilt successfully in 00:00:08
Unknown old style column type definition: smallint unsigned.
Category Flat Data index process unknown error:
Zend_Db_Exception: Invalid column data type "" in /home/www/sites2/example.com/www/lib/Varien/Db/Ddl/Table.php:382
Stack trace:
#0 /home/www/sites2/example.com/www/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(606): Varien_Db_Ddl_Table->addColumn('entity_id', false, NULL, Array, 'entity_id')
#1 /home/www/sites2/example.com/www/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1461): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#2 /home/www/sites2/example.com/www/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1475): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#3 /home/www/sites2/example.com/www/app/code/core/Mage/Catalog/Model/Category/Indexer/Flat.php(260): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#4 /home/www/sites2/example.com/www/app/code/core/Mage/Index/Model/Process.php(212): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#5 /home/www/sites2/example.com/www/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#6 /home/www/sites2/example.com/www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#7 /home/www/sites2/example.com/www/shell/indexer.php(216): Mage_Shell_Compiler->run()
#8 {main}
Category Products index was rebuilt successfully in 00:00:00
Search Index index was rebuilt successfully in 00:00:06
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00
Catalin SEO index was rebuilt successfully in 00:00:01

[colinmollenhour]

Hi @CyberCr33p,

On a completely clean installation with one simple product added and flat indexes enabled I am not able to reproduce the issue. Perhaps it is related to a certain attribute type.

~/projects/openmage/magento-lts/dev/openmage $ docker exec -u 33 openmage_mysql_1 mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
~/projects/openmage/magento-lts/dev/openmage $ docker exec -u 33 openmage_apache_1 php shell/indexer.php reindexall
Product Attributes index was rebuilt successfully in 00:00:00
Product Prices index was rebuilt successfully in 00:00:00
Catalog URL Rewrites index was rebuilt successfully in 00:00:00
Product Flat Data index was rebuilt successfully in 00:00:01
Category Flat Data index was rebuilt successfully in 00:00:00
Category Products index was rebuilt successfully in 00:00:00
Catalog Search Index index was rebuilt successfully in 00:00:00
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00

Please see what the contents of $this->_columns is at line 592 of Mage_Catalog_Model_Resource_Category_Flat.

E.g.:

             $this->_columns = array_merge($this->_getStaticColumns(), $this->_getEavColumns());
+            Mage::log(json_encode($this->_columns));
             foreach ($this->_columns as $fieldName => $fieldProp) {

Since for one of those columns this expression ($fieldProp['type'][0]) appears to be null or undefined. Are there any related PHP warnings?

[airoptix]

Somebody could send me the modified files to solve my issue with magento 1.9?
Or upload them to somewhere?

I have problem with reindex flat category and product data.

Thanks in advance!

[bryanveIoso]

Somebody could send me the modified files to solve my issue with magento 1.9? Or upload them to somewhere?

I have problem with reindex flat category and product data.

Thanks in advance!

Quick fix is just disabled flat indexer. or you can just copy the modifications on the 3 files above.

[airoptix]

Somebody could send me the modified files to solve my issue with magento 1.9? Or upload them to somewhere?
I have problem with reindex flat category and product data.
Thanks in advance!

Quick fix is just disabled flat indexer. or you can just copy the modifications on the 3 files above.

I copied the modifications, they dont work. Or Indid something wrong. I would need the modifies files to let me upload them.

disabling flat indexer is not a solution, site speed terribly falls back