2
error:  "Base table or view not found: 1146 Table"

when I run a collection query in my Magento 2 application I get the above message.

The problem is that the table does indeed exist. however, Magento 2 appears to add the name of my 'database' to the main table name. This is why the query is unable to locate the table.

If I am correct, it means that I need to remove the automatic creation of a table prefix by Magento

This is the collection query:

$accountTypesCollection = $this->_modelConstantAccounttypes->create();
                                    $accountTypesCollection  
                                    ->addFieldToFilter('brandId', 'barclays')
                                    ->addFieldToFilter('brandId', array('eq'=>'barclays'))
                                    ->addFieldToFilter('suppress', array('nlike' => '1'));
        $accountTypesCollection->addAttributeToSort('priority');
  
echo $accountTypesCollection->getSelect(); 

This is the full printout of the select query:

SELECT `main_table`.* FROM `constants_account_types` AS `main_table` WHERE (`brandId` = 'barclays') AND (`brandId` = 'barclays') AND (`suppress` NOT LIKE '1') ORDER BY priority ASC

when I run this query directly into MySQL admin it returns the correct values.

so, I am unclear why Magento adds the name of the database to the query.

this is the full error :

Base table or view not found: 1146 Table 'magentotwo.constants_account_types' doesn't exist, query was: SELECT `main_table`.* FROM `constants_account_types` AS `main_table` WHERE (`brandId` = 'barclays') AND (`brandId` = 'barclays') AND (`suppress` NOT LIKE '1') ORDER BY priority ASC

this is my database config file:

/<Magento Install Dir>/app/etc/env.php

 'db' => 
  array (
    'table_prefix' => '',
    'connection' => 
    array (
      'default' => 
      array (
        'host' => 'localhost',
        'dbname' => 'magentotwo',
        'username' => 'root',
        'password' => '',
        'active' => '1',
      ),
    ),
  ),

you can see that the table prefix column is left blank.

3
  • 1
    According to the error table constants_account_types is not available. Aug 20 '18 at 13:24
  • the table does indeed exist. i am able to run the query direct in mydqlAdmin
    – theSeeker
    Aug 20 '18 at 13:41
  • It happens when you create the table without prefix( while your DB tables have prefix), try with a prefix you have used for your DB creation time. Magento adding that prefix to your new table that doesn't have prefix.that's the issue. Jul 22 '19 at 4:21
0

The constants_account_types table does not exist in magentotwo DataBase so either table is not created or the wrong DataBase is configured in Magento.

7
  • the table does exist. so it has to be the wrong Database configuration. How do you think i would go about debugging this
    – theSeeker
    Aug 20 '18 at 13:37
  • check dbname in app/etc/env.php file.
    – kunj
    Aug 20 '18 at 13:59
  • done. i have edited my original question to show that this is empty-- so, there is no reason why magento should be prefixing the query names with the name of my database table
    – theSeeker
    Aug 20 '18 at 14:01
  • so do you have correct DB and that table is exist in that DB, right?
    – kunj
    Aug 20 '18 at 14:05
  • yes.the database is correclty configured and the table does indeed exist. i beleive the error is caused beucase magento is incorrectly prefixing my table names with the word : magentotwo
    – theSeeker
    Aug 20 '18 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.