Upgrade from Ubuntu 14.04 LTS to 16.04 LTS

A have a Ubuntu server 14.04 LTS working as a mail server for a several users and serving a few virtual domains with a nearly 3y uptime. Today I decide to upgrade.
#do-release-upgrade
bla-bla-bla
When we come to mysql upgrade :

dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.7; however:
  Package mysql-server-5.7 is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
No apport report written because the error message indicates its a followup error from a previous failure.
                                                                                                          Processing triggers for libc-bin (2.23-0ubuntu5) ...
Processing triggers for systemd (229-4ubuntu16) ...
Processing triggers for ureadahead (0.100.0-19) ...
Errors were encountered while processing:
 mysql-server-5.7
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

apt-get install --reinstall mysql-server 
apt-get -f install 
apt-get install --fix-missing
apt-ger remove --purge mysql-server 
even dpkg -i latest.mysql.binary.from.oracle.deb 
Nothing help... reading manuals, logs and shits ... troubleshooting, searching and drinking more coffee don't help either
Fleeting flash: It's always apparmor !!!

# service apparmor stop
# service apparmor teardown
# update-rc.d -f apparmor remove
# apt-get --purge remove apparmor apparmor-utils libapparmor-perl libapparmor1
# apt-get update && apt-get upgrade && apt-get install mysql-server 
Nice... to see the rising sun!

After that - another fail in migration from 14.04 to 16.04
saslauthd[2060]: pam_mysql - something went wrong when invoking crypt() - No such file or directory
saslauthd[2060]: DEBUG: auth_pam: pam_authenticate failed: Authentication failure
mail saslauthd[2060]: do_auth         : auth failure: [user=user@domain.com] [service=smtp] [realm=domain.com] [mech=pam] [reason=PAM auth error]

libpam is deprecated ... dirty fix: change pam auth with Remote IMAP auth:

Change in /etc/default/saslauthd next options (if we have a working courier auth daemon)

 MECHANISMS="rimap"

MECH_OPTIONS="127.0.0.1"

 

find /etc/init.d/ | grep courier | while read line; do $line restart; done

And ... another one ... 

When user trying to sending/receiving mails got error: status=deferred (address resolver failure) 
and
 postfix/trivial-rewrite[8584]: warning: mysql query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'virtual FROM domains WHERE domain='domain.com'' at line 1
postfix/trivial-rewrite[8584]: fatal: mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem

In my /etc/postfix/mysql-virtual_domains.cf I have:
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
so ... I just edited that file and removed ' AS virtual '

For now everything looks great and users are able to connect via pop3(s)/imap(s), sends and receive mails to internal and external domains.

mantis MySQL troubles

При опит да се работи с mantis-a се получава

Database query failed. Error received from database was #145: Table './dbname/mantis_bug_history_table' is marked as crashed and should be repaired for the query: INSERT INTO mantis_bug_history_table
( user_id, bug_id, date_modified, type, old_value, new_value, field_name )
VALUES
( '1', '22044', '2009-05-30 10:02:06', '1', '', '', '' ).

Голяма греда като за добро утро...

mysqlcheck -u root -p dbname дава че всичко е ОК, но пък mysql> SELECT COUNT(*)
-> FROM mantis_bug_monitor_table
-> WHERE user_id='3' AND bug_id='21975';
ERROR 145 (HY000): Table './dbname/mantis_bug_monitor_table' is marked as crashed and should be repaired ... след като той така тогава аз така:
mysqlcheck --auto-repair -u root -p dbname
Enter password:
dbname.mantis_bug_history_table
error : Table './dbname/mantis_bug_history_table' is marked as crashed and should be repaired

Repairing tables
dbname.mantis_bug_history_table
warning : Number of rows changed from 318190 to 318192
status : OK

и после пак греда:

mysqldump --create-options --add-drop-table --no-data --default-character-set=utf8 dbname -u root -p > dbname-nodata.sql
Enter password:
mysqldump: Got error: 145: Table './dbname/mantis_bug_history_table' is marked as crashed and should be repaired when using LOCK TABLES

Ех да умреш от сол в кофите с боклук дано ...

~/var/lib/mysql/dbname# myisamchk -cs *.MYI
myisamchk: MyISAM file mantis_bug_monitor_table.MYI
myisamchk: warning: Table is marked as crashed
myisamchk: warning: Size of datafile is: 252999 Should be: 252990
MyISAM-table 'mantis_bug_monitor_table.MYI' is usable but should be fixed
myisamchk: MyISAM file mantis_user_table.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'mantis_user_table.MYI' is usable but should be fixed

В крайна сметка възможно най-лесното решение се оказа:
mysqlcheck --auto-repair -u root -p dbname mantis_bug_history_table ... това беше ключа за палатката... да упомена изрично таблица която искам да ми repair-не

Mantis upgrade

Покрай праздниците трябваше да upgrade-на Mantis bug tracking системата.
Самият upgrade мина на два етапа : от ver 0.19 към ver 1.0.3 и от ver 1.0.3 към ver 1.16 (последният stable). Съгласно тази инструкция всико мина ОК.
Основният проблем беше с нещата писани на кирилица в смият Мантис... страта версия се оказа, че използва по дефолт Latin1 character set докато новата UTF8. Малко ровене из wiki-то на mantis попаднах на това което естественно не сработи и цял следобед трябваше да ровичкам и да псувам докато оправя бозата... в крайна сметка решението мо конвертирането от Latin1 към UTF8 e:
mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -i "s/latin1/utf8/g" dump.sql
mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_ge
mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql

P.S. в тон с текущите праздници да си отбележа:
като цяло от предната година не съм доволен въпреки, че успях да се сдобия с някои gadgets (SmartStore NAS, Assus eeePC 7'', Privileg X6 dualSIM, Toyota Yaris, PSP 2004) и да изкарам/похарча известна сума пари... Можеше да обърна повече внимание на работата, повече на "учението" и сертифицирането, повече на образованието, повече на здравето си и на близките и приятелите си... ще се опитам за тази година да го направя това и ще започна като се запиша на английски... и тръгвам на зъболекар