Tag: innodb

Pubblicato il

Fix a corrupt innodb

Today happened that mariadb worked almost correctly, but two wordpress db were incorrectly managed. Indeed I could modify a post, but I couldn’t add a new one.

In another PC I, luckily, had the good wp db (perfectlty working with its innodb). So, it was possible to fix this problem. With the following steps:

a) in the well working device dump (from a teminal) the working wp db:

mysqldump -u root -p --single-transaction --routines --triggers your-db-name > dump-yourdbname.sql

b) in the bad working device

  • activate (if not already active) mariadb
  • in a terminal: mysql -u root -p (of course you have to provide your mysql psw)
  • and then, within mysql, you have to delete the old, bad, db, and create a new one:
    • DROP DATABASE IF EXISTS yourdbname;
    • CREATE DATABASE yourdbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    • EXIT;
  • Now you have a new, good but empty db. You have now to import the content of it, with this code: mysql -u root -p yourdbname < dump-yourdbname.sql

Now all should work 🙂.

Pubblicato il

Innodb: pros and cons

If you have to sync different devices you could have many problems with Innodb engine.

But on the other hand, it has some advantages: is more modern than MyIsam, and support foreign keys.

A compromise can be have Innodb as engine, but all the tables in MyIsam.
You should in any way be careful with some files, as already explained, in particular ibdata1.

how get rid of Innodb

If you don’t need foreign kayes, you can get rid of Innodb in /etc/my.cnf, with these instructions (stop mariadb before, and restart after):

default-storage-engine=MyISAM


Pubblicato il

Table relations lost in phpmyadmin

If it happen that relations between tables don’t work anymore, you can try (very carefully, and after complete a backup), if you have a backup of them, to replace the files 

ibdata1  e ib_logfile0

after stopping mariadb (or mysql). At your own risk: with no guarantee.

Pubblicato il

convert an InnoDB database to MyIsam

The problem of Innodb is that you can’t copy the database tables from a PC to another or from localhost to an online website, because of the file “ibdata1”, which is out of the tables folder.

So I think that the best solution, if you need to sync your database between different locations, is to convert Innodb tables into MyIsam tables, which can be copied without problems.

Unfortunately I didn’t find a way to convert a whole database with a single mysql command, but I had to convert each table, with this code:

ALTER TABLE table_name ENGINE=MyISAM.

But at the end the result was any way good.