Database corruption with MariaDB Table doesn t exist in engine

Question

I m in an environement setup  running OSX with MariaDB 10 0 12-MariaDB Homebrew  I ve screwed up the installation so I did completely removed MySQL and MariaDB from my setup and started again   After finishing installing MariaDB  I ve reimported my databases  innoDB  via a DB Dump from the production server  It worked fine  After a reboot  the day after  I can no longer access to the databases     Table  my table  doesn t exist in engine  What s causing this and what s the solution   I do see the structure of my database  but when I try to access it  it gives me this error message   I did try mysql-upgrade --force and deleting rm ib logfile1 ib logfile0  The data loss is not a problem here  the problem is that I can t spend 30 minutes on re-installing each database each time I do a reboot   Here s some logs    140730  9 24 13  Note  Server socket created on IP   127 0 0 1   140730  9 24 14  Note  Event Scheduler  Loaded 0 events 140730  9 24 14  Warning  InnoDB  Cannot open table mysql gtid slave pos from the internal data dictionary of InnoDB though the  frm file for the table exists  See http   dev mysql com doc refman 5 6 en innodb-troubleshooting html for how you can resolve the problem  140730  9 24 14  Warning  Failed to load slave replication state from table mysql gtid slave pos  1932  Table  mysql gtid slave pos  doesn t exist in engine 140730  9 24 14  Note   usr local Cellar mariadb 10 0 12 bin mysqld  ready for connections  Version   10 0 12-MariaDB   socket    tmp mysql sock   port  3306  Homebrew 140730 16 26 28  Warning  InnoDB  Cannot open table db site from the internal data dictionary of InnoDB though the  frm file for the table exists  See http   dev mysql com doc refman 5 6 en innodb-troubleshooting html for how you can resolve the problem

User · Answer

Ok folks  I ran into this problem this weekend when my OpenStack environment crashed  Another post about that coming soon on how to recover    I found a solution that worked for me with a SQL Server instance running under the Ver 15 1 Distrib 10 1 21-MariaDB with Fedora 25 Server as the host    Do not listen to all the other posts that say your database is corrupted if you completely copied your old mariadb-server s  var lib mysql directory and the database you are copying is not already corrupted   This process is based on a system where the OS became corrupted but its files were still accessible     Here are the steps I followed    Make sure that you have completely uninstalled any current versions of SQL only on the NEW server     Also  make sure ALL mysql-server or mariadb-server processes on the NEW AND OLD servers have been halted by running      service mysqld stop or service mariadb stop   On the NEW SQL server go into the  var lib mysql directory and ensure that there are no files at all in this directory   If there are files in this directory then your process for removing the database server from the new machine did not work and is possibly corrupted   Make sure it completely uninstalled from the new machine    On the OLD SQL server      mkdir  OLDMYSQL-DIR    cd  OLDMYSQL-DIR   tar cvf mysql-olddirectory tar  var lib mysql   gzip mysql-olddirectory tar  Make sure you have sshd running on both the OLD and NEW servers   Make sure there is network connectivity between the two servers    On the NEW SQL server      mkdir  NEWMYSQL-DIR  On the OLD SQL server      cd  OLDMYSQL-DIR   scp mysql-olddirectory tar gz    NEWMYSQL-DIR  On the NEW SQL server      cd  NEWMYSQL-DIR   gunzip mysql-olddirectory tar gz  OR tar zxvf mysql-olddirectory tar gz    if tar zxvf doesn t work  tar xvf mysql-olddirectory tar gz  You should now have a  mysql  directory file sitting in the NEWMYSQL-DIR   Resist the urge to run a  cp  command alone with no switches   It will not work   Run the following  cp  command and ensure you use the same switches I did       cd mysql    cp -rfp    var lib mysql   Now you should have a copy of all of your old SQL server files on the NEW server with permissions in tact   On the NEW SQL server      cd  var lib mysql     VERY IMPORTANT STEP   DO NOT SKIP   gt  rm -rfp ib logfile     Now install mariadb-server or mysql-server on the NEW SQL server   If you already have it installed and or running then you have not followed the directions and these steps will fail      FOR MARIADB-SERVER and DNF    gt  dnf install mariadb-server  gt  service mariadb restart   FOR MYSQL-SERVER and YUM    gt  yum install mysql-server  gt  service mysqld restart

User · Answer

I had old MySQL and Centos OS  ver 6 I believe  that was not supported  One day I couldn t access Plesk  Using Filezilla  I copied files the database files from var lib mysql databasename  I then purchased a new server with new Centos 8 OS and MariaDB  In Plesk  I created a new database with the same name as my old one  Using Filezilla  I then pasted the old database files into the newly created database folder   I could see the data in phpmyadmin but it was giving errors such as the ones described here   I happened to have an old sql backup dump file   I imported the dump file and it overwrote those files   I then pasted the old files back into var lib mysql databasename  I then had to do a repair in Plesk  To my suprise  It worked  I had over 6 months of order data restored and I didn t lose anything

User · Answer

In my case  the error disappeared after I rebooted my OS and restarted MariaDB-server  Strange

User · Answer

Something has deleted your ibdata1 file where InnoDB keeps the dictionary  Definitely it s not MySQL who does it  UPDATE  I made a tutorial on how to fix the error - https   youtu be 014KbCYayuE

User · Answer

You may try to     backup your database folder from C  xampp mysql data   frm  amp   ibd files only corresponding your table names  reinstall xampp and recopy DB folder at C  xampp mysql data modify my ini add    mysqld  innodb file per table   on  then open phpmyadmin  or any other DB viewer as Navicat or MYSQL Workbench  and run  ALTER TABLE tbl name IMPORT TABLESPACE    for each table Once you can open your tables make a full mysql dump delete everything and make a clean install   I know  a lot of work to do that you don t need it

User · Answer

This theme required awhile to find results and reasons   using MaiaDB 5 4  via SuSE-LINUX tumblweed    some files in the appointed directory havn t been necessary in any direct relation  with mariadb   I e  I placed some hints  a text-file  some bakup-copys somewhere in the same appointed directory for mysql mariadb and this caused endless error-messages and blocking the server from starting  Mariadb appears to be very sensible and hostile with the presence of other files not beeing database files comments backups experimantal files etc    using libreoffice as client then there already this generated much problems with the creation and working on a database and caused some crashes The crashes eventually produced bad tables  May be because of that or may be because of the presence of not yet deleted but unusable tables    the mysql mariadb server crashed and didn t want to do it  job not even start    Error message always same    Table  some table  doesn t exist in engine   But when it started then tables appeared as normal  but it was unpossible to work on them   So what to do without a more precise Error Message    The unusable tables showed up with   CHECK TABLE  or on the command line of the system with  mysqlcheck    So I deleted by filemanager or on the system-level  as root or as allowed user all the questionable files and then problem was solved    Proposal  the Error Message could be a bit more precisely for example   corrupted tables   which can be found by CHECK TABLE  but only if the server is running  or by mysqlcheck even whe server is not running - but here are  other disturbing files like hints bakups a s o not visible   ANYWAY it helped a lot to have backup-copies of the original database-files on a backup volume  This helped to check out and test it again and again until solution was found   Good luck all - Herbert

User · Answer

This one really sucked   I tried all of the solutions suggested here but the only thing that worked was to   create a new database run ALTER TABLE old db  table name  RENAME new db  table name  on all of the functioning tables run DROP old db create old db again run ALTER TABLE new db  table name  RENAME old db  table name  on all the tables in new db   Once you have done that you can finally just create the table again that you previously had

User · Answer

I ve just had this problem with MariaDB InnoDB and was able to fix it by   create the required table in the correct database in another MySQL MariaDB instance stop both servers copy  ibd   frm files to original server start both servers drop problem table on both servers

[mysql] Database corruption with MariaDB : Table doesn't exist in engine

Examples related to mysql

Examples related to innodb

Examples related to mariadb