Tag: MariaDB

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

mysql multiple tables rename

To rename many mysql/mariadb tables with a same prefix, you can uso this script (in a terminal)

mysql -u root -p -N -e " SELECT CONCAT('RENAME TABLE \', table_name, '\ TO \', REPLACE(table_name, 'oldprefix_', 'newprefix_'), '\;') FROM information_schema.tables WHERE table_schema = 'your-database' AND table_name LIKE 'oldprefix_%'; " > renamed.sql

In this way will created the file renamed.sql (same tables, with different names), that you then can use to replace your database, with

mysql -u root -p yourdatabase < renamed.sql

If you have a wordpress database you have also to check options and meta tables, removing oldprefixes, with:

UPDATE oldprefix_options
SET option_name = REPLACE(option_name, 'oldprefix_', 'newprefix_')
WHERE option_name LIKE 'oldprefix\_%';

and

UPDATE abc_usermeta
SET meta_key = REPLACE(meta_key, 'oldprefix_', 'newprefix_')
WHERE meta_key LIKE 'oldprefix\_%';

And finally, you could check if there are any oldprefix, in options with

SELECT option_name FROM newprefix_options WHERE option_name LIKE 'oldprefix\_%';

same for meta:

SELECT meta_key FROM newprefix_usermeta WHERE meta_key LIKE 'oldprefix\_%';
Pubblicato il

leaving xampp

For many years I used xampp (for Linux: Lampp), because of its simplicity, unlike the “native” Linux apache/php/mysql apps, wich seemed to me very difficult to configure.

The pro of xampp is its simplicity (with few mouse clicks you can do all to install and configure your local php/mysql sever). But the con is that your xampp apps don’t are updated regularly, and there can raise conflicts with Linux “native” mysql.

Therefore I decided to learn how to configure “native” (system-rooted, so to say) apache/php/mysql apps. I chose to install mariadb and not mysql.

And in these last weeks I managed to configure the server, with the following steps:

  • sudo apt install -y apache2
  • sudo apt install -y php
  • sudo apt install mariadb*
  • sudo mysql_secure_installation
  • sudo mysql -u root -p (and within >mysql shell
    • use mysql
    • GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION;
    • SHOW GRANTS FOR 'root'@localhost; //to check if it is all right
    • FLUSH PRIVILEGES;
    • exit;
  • sudo systemctl enable mariadb.service

Pubblicato il

import an xml into phpmyadmin

Given that WordPress doesn’t allow to import a local file (and this is quite unfair, in my opinion), you can transform a wp site into a xml file and then import it in your local database via phpmyadmin.

But you have to format carefully the xml: look how phpmyadmin export an xml file and format your xml according to that model.

Pubblicato il

localhost: connessione non riuscita e altro

Non si tratta di permessi, né di files di configurazione. Ho risolto reinstallando lampp (o meglio istallandone una nuova versione).
Da notare che bisogna salvare l’installer di lampp (se si ha linux) su una partizione linux, altruimenti non parte.

Tabella corrotta, secondo l’ultima versione di MariaDB: si veda http://stackoverflow.com/questions/26871926/mariadb-mysql-database-table-corrupt-cant-repair.
In particolare funziona il “REPAIR TABLE myTable USE_FRM;”