Tag: mysql

Pubblicato il

Mariadb problem after upgrading to a linux lts release

If after a major upgrade (typically to a new LTS release) your maridb local server doesn’t work any more, it could be a problem of AppArmor settings.

To solve it you can try with

sudo nano /etc/apparmor.d/tunables/alias

and then, add the end of that file

alias /var/lib/mysql/ -> /your/mysql-data/path/,

and then

sudo systemctl reload apparmor

and

sudo systemctl start 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

Webtrees mysql error

It can happen that Webtrees gives an error such the following


SQLSTATE[HY000]: General error: 1364 Field 'id' doesn't have a default value

You can try to solve it, assigning an auto-increment default value to that field, in PhpMyAdmin

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

PhpMyAdmin: wildcards as default in search tab

The new PhpMyAdmin, the 5.2.3, unlike the previous, the 5.2.2, has “like” instead of “like %…%” as default search command.

If you prefer the previous default you can do it following these instructions .

I managed to bo back to the previous default modifying only the file
phpMyAdmin/libraries/classes/Types.php
at rows 99 -100

from

return [
'LIKE',
'LIKE %...%',
to
return [
'LIKE %...%',
'LIKE',


Pubblicato il

mysql order negative number

In a table (chronological) if you have dates stored as text you could have problem to order your data.

I.g. you coud have this (wrong) order:

-13.600.000.000, -13.700.000.000,-4.600.000.000

To get the right order you can try with

cast(data as int)

Where “data” is the column of data (as the above values). In this way you will get the correct order:

-13.700.000.000, -13.600.000.000,-4.600.000.000

Pubblicato il

Check a mysql connection error

If your server cannot establish a connection with a webpage, you can check where is the problem with a code like the following:

<?php
$link = mysqli_connect("localhost", "user", "psw", "dbname");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if (!mysqli_query($link, "SET a=1")) {
    printf("Error message: %s\n", mysqli_error($link));
}

/* close connection */
mysqli_close($link);
?>
Pubblicato il

Update a mysql table replacing it

Replacing a table with the same name, I mean. You have to add, before “CREATE TABLE tablename” this row:

DROP TABLE tablename;

You can also add single rows, exporting them, and importing in a sql query, like the following:

INSERT INTO bibliografie (ID, autore, autore_nome, titolo, imagelink, sigla, tipologia, curatori, curatore_unico, opera, rivista, num, pagg, edizione, luogo, data, data_spec, trad_titolo, trad_edizione, trad_luogo, trad_data, contenuti, keywords, reperibilita, letto, letto_quando, valutazione, riferito, destinazione, ambito, href, ad_code) VALUES
(1880, 'Godechot', 'Jacques', 'La Révolution française : chronologie commentée, suivie de notices biographiques sur les personnages cités', NULL, 'Godechot [1988]', 'libro', NULL, NULL, NULL, NULL, NULL, NULL, 'Perrin', 'Paris', '1988', NULL, 'La Rivoluzione francese. Cronologia commentata 1787-1799', 'Bompiani', 'Milano', '2001', '', 'Rivoluzione francese ', NULL, NULL, NULL, NULL, NULL, NULL, 'storia', NULL, NULL);