View Issue Details

IDProjectCategoryView StatusLast Update
0007087OXID eShop (all versions)4.06. Language and translationspublic2020-02-07 10:20
ReporterHelmut L. Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status acknowledgedResolutionopen 
Product Version6.1.5 
Summary0007087: views for multi language tables with lowercase oxid column can't be created
DescriptionIf you have created an own multi language table that has a lowercase oxid column the views can't be created.
In the _set tables the oxid column is always created in uppercase and when the columns for the view are collected the lowercase and the uppercase column are both added to the view, which results in a duplicate column name error from MySQL.
Steps To ReproduceCreate a multilanguage table with a lowercase oxid column:
 create table test (
  oxid char(32) CHARACTER SET latin1 COLLATE latin1_general_ci not null,
  test_field varchar(10),
  test_field_1 varchar(10),
  primary key (oxid)
 );

~ Start: Added by MK ~

edit config.inc.php
change $this->aMultiLangTables = null; to $this->aMultiLangTables = ['test'];
add $this->iLangPerTable = 2;

~ End: Added by MK ~

Add languages to the shop until the _set tables are created.

Try to generate the views:
php vendor/oxid-esales/oxideshop-db-views-generator/generate_views.php
Additional InformationThis problem exists only since OXID 6, in the older versions there was already a fix for it.

In the older versions the column name was always converted to uppercase:
/core/adodblite/adodbSQL_drivers/mysqli/mysqli_meta_module.inc, line 287

Since OXID 6, the column name is no longer converted to uppercase:
/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php, line 1151

Added by MK: Exception message:
[2020-02-07 10:17:49] OXID Logger.ERROR: Duplicate column name 'OXID' ["[object] (OxidEsales\\Eshop\\Core\\Exception\\DatabaseErrorException(code: 1060): Duplicate column name 'OXID' at /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php:938, Doctrine\\DBAL\\Exception\\NonUniqueFieldNameException(code: 0): An exception occurred while executing 'CREATE OR REPLACE SQL SECURITY INVOKER VIEW `oxv_test` AS SELECT test.oxid,test.test_field,test.test_field_1,test.test_field_2,test_set1.OXID,test_set1.test_field_3 FROM test LEFT JOIN test_set1 USING (OXID) ':\n\nSQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'OXID' at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:76, Doctrine\\DBAL\\Driver\\PDOException(code: 42S21): SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'OXID' at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:59, PDOException(code: 42S21): SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'OXID' at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:57)\n[stacktrace]\n#0 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php(779): OxidEsales\\EshopCommunity\\Core\\Database\\Adapter\\Doctrine\\Database->convertException(Object(Doctrine\\DBAL\\Exception\\NonUniqueFieldNameException))\n#1 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/Database/Adapter/Doctrine/Database.php(576): OxidEsales\\EshopCommunity\\Core\\Database\\Adapter\\Doctrine\\Database->executeUpdate('CREATE OR REPLA...', Array)\n#2 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Application/Model/Shop.php(360): OxidEsales\\EshopCommunity\\Core\\Database\\Adapter\\Doctrine\\Database->execute('CREATE OR REPLA...')\n#3 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Application/Model/Shop.php(141): OxidEsales\\EshopCommunity\\Application\\Model\\Shop->_runQueries()\n#4 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/DbMetaDataHandler.php(569): OxidEsales\\EshopCommunity\\Application\\Model\\Shop->generateViews(false, Array)\n#5 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Application/Controller/Admin/ToolsList.php(36): OxidEsales\\EshopCommunity\\Core\\DbMetaDataHandler->updateViews()\n#6 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/Controller/BaseController.php(524): OxidEsales\\EshopCommunity\\Application\\Controller\\Admin\\ToolsList->updateViews()\n#7 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/ShopControl.php(332): OxidEsales\\EshopCommunity\\Core\\Controller\\BaseController->executeFunction('updateViews')\n#8 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/ShopControl.php(274): OxidEsales\\EshopCommunity\\Core\\ShopControl->executeAction(Object(OxidEsales\\Eshop\\Application\\Controller\\Admin\\ToolsList), 'updateViews')\n#9 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/ShopControl.php(137): OxidEsales\\EshopCommunity\\Core\\ShopControl->_process('OxidEsales\\\\Esho...', 'updateViews', NULL, NULL)\n#10 /var/www/html/vendor/oxid-esales/oxideshop-ce/source/Core/Oxid.php(26): OxidEsales\\EshopCommunity\\Core\\ShopControl->start()\n#11 /var/www/html/source/index.php(15): OxidEsales\\EshopCommunity\\Core\\Oxid::run()\n#12 /var/www/html/source/admin/index.php(11): require_once('/var/www/html/s...')\n#13 {main}\n"] []
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionNot defined

Activities

QA

2020-02-07 10:18

administrator   ~0013118

-MK