/ Register

Join the community today
Become a Member

Getting "table does not exist in engine" error but it actually exists

Discussion in 'System Administration' started by Afaq, May 7, 2018.

Tags:
Previous Thread Next Thread
Loading...
  1. May 7, 2018 #1
    Afaq

    Afaq Member

    81
    3
    8
    Aug 5, 2015
    Ratings:
    +5
    Local Time:
    12:51 AM
    I am running latest centminmod on CentOS 7 for Magento 2.2.2. I am trying to run Magento's reindexing but it gives an error that a table does not exist in engine so some truncate query cannot be run. However, I have checked the database using the show tables query and it shows that particular table. I have also tried running the select * from that particular table but that also gives the same error (table does not exist in engine). I have also tried restarting the server but that doesnt fix it as well. So any idea how can I fix this?

    Thanks.
     
  2. May 7, 2018 #2
    eva2000

    eva2000 Administrator Staff Member

    59,272
    12,508
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,140
    Local Time:
    5:51 AM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    Hard to figure out without the exact actual query and error output ?
     
  3. May 7, 2018 #3
    Afaq

    Afaq Member

    81
    3
    8
    Aug 5, 2015
    Ratings:
    +5
    Local Time:
    12:51 AM
    It runs a truncate query during indexing no this table.

    Code:
    SQLSTATE[42S02]: Base table or view not found: 1932 Table 'rollover.catalog_product_index_eav_decimal_replica' doesn't exist in engine, query was: TRUNCATE TABLE `catalog_product_index_eav_decimal_replica`
    Its more to do with some mysql server level issue than a particular query or something.

    Code:
    MariaDB [rollover]> select * from catalog_product_index_eav_decimal_replica;
ERROR 1932 (42S02): Table 'rollover.catalog_product_index_eav_decimal_replica' doesn't exist in engine
    Furthermore, I also tried making a mysqldump of the database, this is the error I get:
    Code:
    mysqldump: Got error: 1932: "Table 'rollover.catalog_product_index_eav_decimal_replica' doesn't exist in engine" when using LOCK TABLES
     
  4. May 7, 2018 #4
    eva2000

    eva2000 Administrator Staff Member

    59,272
    12,508
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,140
    Local Time:
    5:51 AM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    Might need to check if Magento issue tracker magento/magento2 or forums have come across the issue Core Technology - Magento 2 - Magento Forums

    i.e. issues search

    but error
    suggests you may have corrupted or deleted your innodb tablespace data file i.e. /var/lib/mysql/ibdata1 which contains a record of all your innodb database names, table names ete etc. So if you mistakenly deleted ibdata1 or overwrote it with a imported ibdata1 or ran out of disk free space during writes etc, then you would of potentially have corrupted your the innodb tablespace data file.

    So you'd need to figure out how to either repair corruption which isn't 100% guarantee to have 100% intact data or restore from backup - that means restore every since mysql database on the server not just the database the problem came from.

    Innodb repair and recovery is complex so no free support from me. Best way is backup entire mysql data directory physical files /var/lib/mysql when mysql server is stopped and then completely delete and wipe all mysql data /var/lib/mysql and then restore from backups every single mysql database on the server. Part of process outlined at PHP-FPM - MariaDB - Insight Guide - How to use mysql_install_db to reset your MySQL database directory

    What updates or mariadb mysql data operations have you done lately ? Any database/data file imports ? how were they imported ? any backup mysql data restored/reimported on server ? how were they restored / imported ?
     
  5. May 7, 2018 #5
    eva2000

    eva2000 Administrator Staff Member

    59,272
    12,508
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,140
    Local Time:
    5:51 AM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    FYI, you can check if table is intact using describe command for table structure
    Code (Text):
    mysql -e "describe rollover.catalog_product_index_eav_decimal_replica;"

    should return table's column, type and key field structure / schema of how the table was created
     
  6. May 7, 2018 #6
    Afaq

    Afaq Member

    81
    3
    8
    Aug 5, 2015
    Ratings:
    +5
    Local Time:
    12:51 AM
    Ok so I have fixed the issue. I exported the same table from another magento installation (this table is usually empty). Exported the faulty magento db except this table. Made a new db. Imported the old db and then imported this table which I got from the other magento installation. Now, everything is working fine.
     
  7. May 7, 2018 #7
    eva2000

    eva2000 Administrator Staff Member

    59,272
    12,508
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,140
    Local Time:
    5:51 AM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    Via import tablespaces ? That would be proper way as outlined

     
Previous Thread Next Thread
Loading...

.