Mysql-Datenbank von Akonadi repariert

Problem
Nach Umzug von openSuse 13.2 auf Mint 17.3 (KDE 4.14.2) funktionierte KMail (4.14.2) nicht mehr.
Akonadi startete zwar, aber kmail meldete gleich beim Starten einen Fehler („Fehler beim .. der Ressourcen-Collection“ oder so).
Über die Akonadiserver-Konfiguratioon in der akonadiconsole (Button „Test“) die Fehlermeldungen angeschaut, in einer der Dateien stand:
„MySQL server has gone away“
Denn der Mysql-Server stoppte gleich nach dem Start, mit folgendem mysql.err:


160109 15:14:30 [Note] Plugin 'FEDERATED' is disabled.
160109 15:14:30 InnoDB: The InnoDB memory heap is disabled
160109 15:14:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160109 15:14:30 InnoDB: Compressed tables use zlib 1.2.8
160109 15:14:30 InnoDB: Using Linux native AIO
160109 15:14:30 InnoDB: Initializing buffer pool, size = 80.0M
160109 15:14:30 InnoDB: Completed initialization of buffer pool
160109 15:14:30 InnoDB: highest supported file format is Barracuda.
160109 15:14:30 InnoDB: Waiting for the background threads to start
160109 15:14:31 InnoDB: 5.5.46 started; log sequence number 8324119707
160109 15:14:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.46-0ubuntu0.14.04.2' socket: '/tmp/akonadi-sunito.VZqpdc/mysql.socket' port: 0 (Ubuntu)
160109 15:14:32 InnoDB: Warning: table 'akonadi/collectiontable'
InnoDB: in InnoDB data dictionary has unknown flags 50.
160109 15:14:32 InnoDB: Warning: table 'akonadi/tagtable'
InnoDB: in InnoDB data dictionary has unknown flags 50.
160109 15:14:32 InnoDB: Warning: table 'akonadi/tagtypetable'
InnoDB: in InnoDB data dictionary has unknown flags 50.
160109 15:14:42 InnoDB: error clustered record for sec rec not found
InnoDB: index `PimItemTable_collectionIndex` of table `akonadi`.`pimitemtable`
InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000d81f55cad87d; asc U };;
1: len 8; hex d5cad81f55caae11; asc U ;;

InnoDB: clust index record PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 8; hex 800000000004cd69; asc i;;
1: len 6; hex 00000803712e; asc q.;;
2: len 7; hex 1800000c8c35cc; asc 5 ;;
3: len 4; hex 80000003; asc ;;
4: len 26; hex 313435313932313936302e52362e7a7573653133322e73697465; asc 1451921960.R6.zuse132.site;;
5: SQL NULL;
6: len 30; hex 3c73656e7368752f536f7a692f6973737565732f31352f31363836393736; asc <senshu/Sozi/issues/15/1686976; (total 44 bytes);
7: len 8; hex 8000000000000031; asc 1;;
8: len 8; hex 8000000000000008; asc ;;
9: len 4; hex 568a87ea; asc V ;;
10: len 4; hex 568a95fd; asc V ;;
11: len 1; hex 80; asc ;;
12: len 8; hex 8000000000001e08; asc ;;

TRANSACTION 8043EEB, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 0
MySQL thread id 20, OS thread handle 0x7f102b514700, query id 59908 localhost sunito Sending data
SELECT PimItemTable.id, MimeTypeTable.name, PimItemTable.rev, PimItemTable.size, PimItemTable.datetime, PimItemTable.collectionId FROM PimItemTable INNER JOIN MimeTypeTable ON ( PimItemTable.mimeTypeId = MimeTypeTable.id ) WHERE ( collectionId = ? AND PimItemTable.datetime >= ? ) ORDER BY PimItemTable.id DESC

InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: Dump of the child page:
160109 15:19:50 InnoDB: Page dump in ascii and hex (16384 bytes):

[...]

160109 15:19:50 InnoDB: Page checksum 1915289840, prior-to-4.0.14-form checksum 3717995491
InnoDB: stored checksum 3711358679, prior-to-4.0.14-form stored checksum 3717995491
InnoDB: Page lsn 1 4285448650, low 4 bytes of lsn at page end 4285448650
InnoDB: Page number (if stored to page already) 6,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 5
InnoDB: Page may be an index page where index id is 50
InnoDB: (index "PimItemTable_collectionIndex" of table "akonadi"."pimitemtable")
InnoDB: Corruption of an index tree: table "akonadi"."pimitemtable", index "PimItemTable_collectionIndex",
InnoDB: father ptr page no 4489, child page no 553
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000d81f55cad87d; asc U };;
1: len 8; hex d5cad81f55caae11; asc U ;;
n_owned: 0; heap_no: 421; next rec: 9366
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000241; asc A;;
1: len 8; hex 800000000001992c; asc ,;;
2: len 4; hex 00001189; asc ;;
n_owned: 4; heap_no: 395; next rec: 112
InnoDB: You should dump + drop + reimport the table to fix the
InnoDB: corruption. If the crash happens at the database startup, see
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html about
InnoDB: forcing recovery. Then dump + drop + reimport.
160109 15:19:50 InnoDB: Assertion failure in thread 139707457586944 in file btr0btr.c line 1304
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:19:50 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16384
read_buffer_size=131072
max_used_connections=48
max_threads=256
thread_count=48
connection_count=48
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 560024 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x7f103e0e8ec0]
/usr/sbin/mysqld(handle_fatal_signal+0x3d5)[0x7f103dfd3025]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f103cd62340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7f103c3b9cc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f103c3bd0d8]
/usr/sbin/mysqld(+0x561e73)[0x7f103e158e73]
/usr/sbin/mysqld(+0x56a887)[0x7f103e161887]
/usr/sbin/mysqld(+0x571c0d)[0x7f103e168c0d]
/usr/sbin/mysqld(+0x57573f)[0x7f103e16c73f]
/usr/sbin/mysqld(+0x60adbc)[0x7f103e201dbc]
/usr/sbin/mysqld(+0x60b8d8)[0x7f103e2028d8]
/usr/sbin/mysqld(+0x601bc4)[0x7f103e1f8bc4]
/usr/sbin/mysqld(+0x54b973)[0x7f103e142973]
/usr/sbin/mysqld(+0x53d5dc)[0x7f103e1345dc]
/usr/sbin/mysqld(+0x541452)[0x7f103e138452]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f103cd5a182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f103c47d47d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Lösung

# Wiederherstellen der Original-Datenbank
mv .local/share/akonadi/ .local/share/akonadi_160109c_kop2
cp -a ../sunito_zuse132/.local/share/akonadi/ .local/share/akonadi/

# in der mysql.conf den Eintrag innodb_force_recovery auf 2 gesetzt (1 funktionierte nicht)
kwriteconfig --file /home/sunito/.local/share/akonadi/mysql.conf --group mysqld --key innodb_force_recovery 2

# Mysql alleine (ohne Akonadi) gestartet:
akonadictl stop
killall /usr/sbin/mysqld
mkdir /tmp/akonadi-sunito.vZoEHB/
/usr/sbin/mysqld --defaults-file=/home/sunito/.local/share/akonadi/mysql.conf --datadir=/home/sunito/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-sunito.vZoEHB/mysql.socket

# Den Server in seinem Konsolefenster laufen lassen, in neuem Fenster die ganze Datenbank gedumpt:
mkdir /opt/ms/dump1/
mysqldump -S /tmp/akonadi-sunito.vZoEHB/mysql.socket -A >/opt/ms/dump1/a2all.sql # 2016-01-09 17:23:24
# Danach den Mysql-Server weieder beendet:
killall /usr/sbin/mysqld

# Dann die Datenbank-Daten weggemovet:
mv .local/share/akonadi/db_data/ .local/share/akonadi/db_data_zuse-kaputt
mv .local/share/akonadi/file_db_data/ .local/share/akonadi/file_db_data_zuse-kaputt
# und den DB-Ordner wieder erstellt:
mkdir .local/share/akonadi/db_data
# in der mysql.conf den Eintrag innodb_force_recovery wieder entfernt
kwriteconfig --file /home/sunito/.local/share/akonadi/mysql.conf --group mysqld --key innodb_force_recovery 0

# Nun in dem vorigen Fenster wieder Mysql gestartet:
/usr/sbin/mysqld --defaults-file=/home/sunito/.local/share/akonadi/mysql.conf --datadir=/home/sunito/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-sunito.vZoEHB/mysql.socket
# Dann die DB aus dem Dump wiederhergestellt:
mysql -S /tmp/akonadi-sunito.vZoEHB/mysql.socket # Und Mysql-Server weieder beendet:
killall /usr/sbin/mysqld

# Nun Akonadi gestartet
akonadictl start

–> Läuft!!!

 


 

Dann festgestellt, dass einige Konfigurationsdateien nicht im neuen Userverzeichnis angekommen waren:

cp -a ../sunito_zuse132/.kde4/share/config/akonadi_pop3_resource_[1-9]*rc ~/.kde/share/config

Allerdings stimmen dan die in diesen Dateien eingetragenen Zielordner-IDs (targetCollection) nicht mehr. Mühsam manuell per GUI geändert (ohne Internetverbindung, damit keine Mails in die falschen Odner gelangen).

Rails 2 mit MySQL

# Kurze Anleitung um das „Klarwerk“ Projekt benutzen zu können …

# MySQL und libmysql-dev runterladen und installieren.
# Bei der Passwortabfrage das Feld leer lassen!
sudo apt-get install mysql-server
sudo apt-get install libmysql-dev

# Rails und MySQL zu Ruby 1.8.7 hinzufügen.
# RVM auf Ruby 1.8.7 umstellen!
RVM use 1.8.7
gem install -v 2.3.14 rails
gem install mysql

# Edit 2012-Juni-4, ich musste mysql mit „Architektur“ 64bit installieren, also:
ARCHFLAGS=“-arch x86_64″ gem install mysql
# http://stackoverflow.com/questions/5952265/rake-dbcreate-generated-if-you-set-the-charset-manually-make-sure-you-have-a

# Neues MySQL Konto mit Benutzerrechten erstellen:
mysql -u root <<eot
CREATE USER kab@localhost IDENTIFIED BY 'kab';
GRANT ALL ON *.* TO kab@localhost;
eot

# MySQL in UTF-8 einstellen
cd /etc/mysql/
sudo nano my.cnf
# In der my.cnf unter [client]
default-character-set = utf8
# und unter [mysqld]
character-set-server = utf8
# hinzufügen.

# MySQL neustarten
sudo service mysql restart
# MySQL Client starten
mysql -u root

# Zeichencodierung aufrufen
show variables like '%char%';

# Nun sollte folgendes angezeigt werden:

# +————————–+—————————-+
# | Variable_name | Value |
# +————————–+—————————-+
# | character_set_client | utf8 |
# | character_set_connection | utf8 |
# | character_set_database | utf8 |
# | character_set_filesystem | binary |
# | character_set_results | utf8 |
# | character_set_server | utf8 |
# | character_set_system | utf8 |
# | character_sets_dir | /usr/share/mysql/charsets/ |
# +————————–+—————————-+

# Ins Projektverzeichnis wechseln …
# Erstellt eine Datenbank
rake db:create
# Initialisiert die Datenbank
rake db:migrate