Akonadi/MySQL problem after Update Kubuntu 14.04 to 16.04

[wired2051]

Since I updated Kubuntu 14.04 LTS to 16.04 LTS with Muon, the PIM (KMail, KOrganizer , KAddressBook) doesn't work - and shows no "details".

The Akonadi self test shows MySQL Errors: [ERROR] unknown variable 'innodb_additional_mem_pool_size=1M' So I removed the variable from ~/.local/share/akonadi/mysql.conf and tried akonadictl status and then akonadictl start:

USER@PC:~$ akonadictl status
Akonadi Control: stopped
Akonadi Server: stopped
search paths:  ("lib/x86_64-linux-gnu", "lib/x86_64-linux-gnu/qt5/plugins/", "lib/x86_64-linux-gnu/kf5/", "lib/x86_64-linux-gnu/kf5/plugins/", "/usr/lib/qt5/plugins/", "/usr/lib/x86_64-linux-gnu/qt5/plugins", "/usr/bin")
Akonadi Server Search Support: available (Remote Search)
Available Agent Types: akonadi_akonotes_resource, akonadi_archivemail_agent, akonadi_baloo_indexer, akonadi_birthdays_resource, akonadi_contacts_resource, akonadi_davgroupware_resource, akonadi_followupreminder_agent, akonadi_googlecalendar_resource, akonadi_googlecontacts_resource, akonadi_ical_resource, akonadi_icaldir_resource, akonadi_imap_resource, akonadi_invitations_agent, akonadi_kalarm_dir_resource, akonadi_kalarm_resource, akonadi_kolab_resource, akonadi_maildir_resource, akonadi_maildispatcher_agent, akonadi_mailfilter_agent, akonadi_mbox_resource, akonadi_migration_agent, akonadi_mixedmaildir_resource, akonadi_newmailnotifier_agent, akonadi_notes_agent, akonadi_notes_resource, akonadi_openxchange_resource, akonadi_pop3_resource, akonadi_sendlater_agent, akonadi_vcard_resource, akonadi_vcarddir_resource
USER@PC:~$ akonadictl start
Starting Akonadi Server...
   done.
Connecting to deprecated signal QDBusConnectionInterface::serviceOwnerChanged(QString,QString,QString)
USER@PC:~$ search paths:  ("/usr/local/sbin", "/usr/local/bin", "/usr/sbin", "/usr/bin", "/sbin", "/bin", "/usr/games", "/usr/local/games", "/snap/bin", "/usr/sbin", "/usr/local/sbin", "/usr/local/libexec", "/usr/libexec", "/opt/mysql/libexec", "/opt/local/lib/mysql5/bin", "/opt/mysql/sbin")
Found mysql_install_db:  "/usr/bin/mysql_install_db"
Found mysqlcheck:  "/usr/bin/mysqlcheck"
akonadi.collectionattributetable                   OK
akonadi.collectionmimetyperelation                 OK
akonadi.collectionpimitemrelation                  OK
akonadi.collectiontable                            OK
akonadi.flagtable                                  OK
akonadi.mimetypetable                              OK
akonadi.parttable                                  OK
akonadi.parttypetable                              OK
akonadi.pimitemflagrelation                        OK
akonadi.pimitemtable
error    : Table rebuild required. Please do "ALTER TABLE `pimitemtable` FORCE" or dump/reload to fix it!
akonadi.pimitemtagrelation                         OK
akonadi.relationtable                              OK
akonadi.relationtypetable                          OK
akonadi.resourcetable                              OK
akonadi.schemaversiontable                         OK
akonadi.tagattributetable                          OK
akonadi.tagremoteidresourcerelationtable           OK
akonadi.tagtable                                   OK
akonadi.tagtypetable                               OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                                                                                                                                                                                     
error    : Table upgrade required. Please do "REPAIR TABLE `event`" or dump/reload to fix it!                                                                                                                   
mysql.func                                         OK                                                                                                                                                           
mysql.general_log                                  OK                                                                                                                                                           
mysql.help_category                                OK                                                                                                                                                           
mysql.help_keyword                                 OK                                                                                                                                                           
mysql.help_relation                                OK                                                                                                                                                           
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc
error    : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it!
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Repairing tables
mysql.event
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
Warning  : Incorrect datetime value: '0000-00-00 00:00:00' for column 'modified' at row 1
Error    : Invalid default value for 'modified'
status   : Operation failed
mysql.proc
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
Warning  : Incorrect datetime value: '0000-00-00 00:00:00' for column 'modified' at row 1
Error    : Invalid default value for 'modified'
status   : Operation failed
Failed to ALTER TABLE `akonadi`.`pimitemtable` FORCE
Error: Invalid default value for 'atime'
MySQL version OK (required "5.1" , available "5.7" )
Database "akonadi" opened using driver "QMYSQL"
DbInitializer::run()
checking table  "SchemaVersionTable"
checking table  "ResourceTable"
checking table  "CollectionTable"
checking table  "MimeTypeTable"
checking table  "PimItemTable"
checking table  "FlagTable"
checking table  "PartTypeTable"
checking table  "PartTable"
checking table  "CollectionAttributeTable"
checking table  "TagTypeTable"
checking table  "TagTable"
checking table  "TagAttributeTable"
checking table  "TagRemoteIdResourceRelationTable"
checking table  "RelationTypeTable"
checking table  "RelationTable"
checking table  "PimItemFlagRelation"
checking table  "PimItemTagRelation"
checking table  "CollectionMimeTypeRelation"
checking table  "CollectionPimItemRelation"
DbInitializer::run() done
skipping update 2
skipping update 3
skipping update 4
skipping update 8
skipping update 10
skipping update 12
skipping update 13
skipping update 14
skipping update 15
skipping update 16
skipping update 17
skipping update 18
skipping update 19
skipping update 20
skipping update 21
skipping update 22
skipping update 23
skipping update 24
skipping update 25
skipping update 26
skipping update 28
skipping update 30
skipping update 31
Updating indexes
"CREATE  INDEX PimItemTable_ridIndex ON PimItemTable (remoteId)"
Updating index failed:
Sql error: Invalid default value for 'atime' QMYSQL: Unable to execute query
Query: CREATE  INDEX PimItemTable_ridIndex ON PimItemTable (remoteId)
""
Unable to initialize database.
terminating service threads
terminating connection threads
stopping db process
Application 'akonadiserver' exited normally..

Because I read Table rebuild required I looked up ~/.local/share/akonadi/db_data/mysql.err - see attachment.

With use mysql_upgrade to fix this error I've run out of ideas. I know much to little about MySQL to continue.

Please help me to avoid a complete re-installation of Kubuntu. I've backups of all PIM-data (addresses, dates and mail), so a data loss is not a big problem, essential for me is a running PIM.

[GreyGeek]

Did you ever try akonadictl vacuum or fsck on your problem?
I installed aconadiconsole, which had some great tools and help me overcome an endless "retrieving files ..." error.

[wired2051]

I appreciate your help

Because I read that akonadictl vacuum needs a lot of time (and HDD-space), I just tried akonadictl fsck but nothing happens for about 45 minutes. Can you give me any clue?

aconadiconsole is a developer tool and not in my repositories. I'm not sure if it's appropriate for me as a rookie to use it.

[GreyGeek]

I appreciate your help

Because I read that akonadictl vacuum needs a lot of time (and HDD-space), I just tried akonadictl fsck but nothing happens for about 45 minutes. Can you give me any clue?

aconadiconsole is a developer tool and not in my repositories. I'm not sure if it's appropriate for me as a rookie to use it.

It's in Xenial/Main (archive.kde.neon).

After you start either vacuum or fsck you can use ksysmonitor to see if they are still running from time to time, and to note how much CPU time they are taking.

[wired2051]

To make it short: it doesn't work.

I tried akonadictl fsck as user and as root up to 10 hours - no output, no advantage. The process was listet with KSysGuard but no % CPU. I didn't tried aconadiconsole because I don't know what to do with it. I admit, it's a problem for me, that there is nearly no info in the internet what akonadictl vacuum and akonadictl fsck do - it's my productive-OS so I want to minimize the risk of damage.

Is there no way to reset or reinstall akonadi? I've backups of all data.

I can't get further help from forums.mysql.com because MySQL is embedded. Looks like this forum is my last chance to solve this problem...

[GreyGeek]

Akonadi/MySQL problem after Update Kubuntu 14.04 to 16.04

You can download the Xenial version from here.
Akonadiconsole worked for me when akonadictl did not.

You can simulate the removal of akonadi-server to see what will be removed from your system:

:~$ sudo apt-get -s remove akonadi-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
Starting pkgProblemResolver with broken count: 2
Starting 2 pkgProblemResolver with broken count: 2
Investigating (0) kdepim-runtime [ amd64 ] < 4:16.12.3-0neon+16.04+build30 > ( x11 )
Broken kdepim-runtime:amd64 Depends on akonadi-server [ amd64 ] < 4:16.12.3-0neon+16.04+build27 > ( net ) (>= 1.8)
Considering akonadi-server:amd64 10001 as a solution to kdepim-runtime:amd64 1
Removing kdepim-runtime:amd64 rather than change akonadi-server:amd64
Investigating (0) kmail [ amd64 ] < 4:16.12.3-0neon+16.04+build13 > ( mail )
Broken kmail:amd64 Depends on akonadi-server [ amd64 ] < 4:16.12.3-0neon+16.04+build27 > ( net ) (>= 4:16.04)
Considering akonadi-server:amd64 10001 as a solution to kmail:amd64 0
Removing kmail:amd64 rather than change akonadi-server:amd64
Done
The following packages were automatically installed and are no longer required:
accountwizard akonadi-backend-mysql kdepim-addons kdepim-themeeditors kdiagram-data kio-ldap kio-sieve kio-smtp libboost-thread1.58.0
libkf5akonadinotes5 libkf5alarmcalendar5 libkf5eventviews-data libkf5eventviews5 libkf5followupreminder5 libkf5gapi-data libkf5gapicalendar5
libkf5gapicontacts5 libkf5gapicore5 libkf5gapitasks5 libkf5incidenceeditor5 libkf5kmanagesieve5 libkf5ksieve-data libkf5ksieve5 libkf5ksieveui5
libkf5mailcommon5 libkf5mailimporter5 libkf5messagecomposer5 libkf5messagelist5 libkf5sendlater5 libkf5templateparser5 libkf5tnef5 libkgantt2
libkolab1 libkolabxml1v5 libqt5webengine5 mariadb-server-core-10.0 mbox-importer pim-data-exporter pim-sieve-editor
Use 'sudo apt autoremove' to remove them.
The following packages will be REMOVED:
akonadi-server kdepim-runtime kmail
0 upgraded, 0 newly installed, 3 to remove and 0 not upgraded.
Remv kmail [4:16.12.3-0neon+16.04+build13]
Remv kdepim-runtime [4:16.12.3-0neon+16.04+build30]
Remv akonadi-server [4:16.12.3-0neon+16.04+build27]

Removing akonadi-server doesn't look like a good solution. Akonadiconsole isn't the bear you think it is.

[wired2051]

Removing akonadi is not a clever idea, I knew. That's why I ask if re-installation or resetting is possible and a good idea.

Although I found this information, I think, I didn't know what to do with Akonadiconsole to solve my problem.

In a german Wiki they show a way rest akonadi:
1. stopping it with akonadictl stop
2. removing the data from
Server: ~/.local/share/akonadi/
Configuration: ~/.config/akonadi/
Resources: ~/.kde/share/config/akonadi*
Resources : ~/.config/akonadi*
3. starting akonadi with akonadictl start
4. set up the resources in KMail and KOrganizer

Might this be a solution for me?

[GreyGeek]

Can't hurt any worse than what's hurting you now. Generally, when you remove akonadi it also takes with it all of the kdepim apps, like KMail, Kalender, Kontact, kdiagram, etc., which is what the simulation I posted shows. I don't know if you've made backups of your data, but if you have remember one thing: you can reinstall from an ISO in 15 minutes and add back your additonal apps and your data in another hour or less. I've seen people waste days trying to figure out a problem that could be easily solved by a reinstall.

Breakage, malfunctioning updates or accidental misconfigurations is why many of of use Btrfs. Do a snapshot (or incremental snapshot) before you update. If things get broken roll back to the snapshot and reboot.

[wired2051]

I don't know if you've made backups of your data, but if you have remember one thing: you can reinstall from an ISO in 15 minutes and add back your additonal apps and your data in another hour or less.

Yes, I have backups: calenders, contacts and mails are exported and I have backups of home and some other folders (/etc, /var, /opt and /usr) on a hourly basis on my NAS.

What kind of reinstall do you mean? Kubuntu? I will avoid reinstalling Kubuntu because it took me several days to reconfigure everything after the last new PC. Can I simply reinstall KDE? How? Do I have to deinstall something first?

Breakage, malfunctioning updates or accidental misconfigurations is why many of of use Btrfs. Do a snapshot (or incremental snapshot) before you update. If things get broken roll back to the snapshot and reboot.

I confess, I hear now the first time about this file-system. I use ext4.

[GreyGeek]

Purging plasma-desktop is the MOAB option (not quite as drastic as NUKING your installation from orbit and doing a re-install ). I've never deleted the plasma-desktop myself so I can't say what condition your system would be left in. You have backups. You might as well try the solution you mentioned in post #7. If it doesn't work you can try the MOAB option. If that doesn't work there is always the NUCLEAR option.


Understand that I've never done this myself but if I were to do it these are the steps I would follow for the MOAB option:
Boot to the recovery menu
Select the option to log in as root
When you log in as root from the recovery menu the filesystem is mounted read-only
Remount the file system as read/write (rw), so that you can make changes to the files on it.
You won't need to use sudo because you will be logged in as root.
mount -o remount,rw /
Then issue
apt-get purge plasma-desktop
When it is done:
apt-get autoremove
to clear the debris.
Then, to make sure nothing is left in a broken condition:
apt-get -f install
dpkg --configure -a
and repeat those two commands until no error msgs are returned
Then do
apt-get autoremove
againt to clean up any remaining debris.
Then reinstall the plasma desktop
apt-get install plasma-desktop

The Btrfs (Binary TRee File System) has been in the works for about a decade or longer. It has passed out of the experimental stage and is now an available fs option during the install process. Oshunluver has made some excellent posts on it here on this forum. He summerizes Btrfs snapshots and rollbacks very well here.

IMO, moving from ext? to Btrfs is reason enough to do a re-install.

[wired2051]

Finally I solved my problem: I reseted akonadi like mentioned in posting #7. After that I imported the data from my backup. Next time, I will do this step much earlier.

Thank you for your patient help!

And next time I install Kubuntu, I will consider Btrfs - thanks for the suggestion!

[GreyGeek]

You're welcome. Oshunluver has several excellent tutorials and posts about Btrfs on this forum. They should help you get started if you decide to move in that direction. He's been using Btrfs for over five years, IIRC, and I've been using it for 2 1/2 yrs, but I'm no expert. I do know that it has been working flawlessly for me during that time. The Btrfs environment that Kubuntu (Ubuntu) uses is to have just two rootfs subvolumes, @ and @home. This makes creating backup snapshots, and restoring, easy. OpenSUSE, for example, makes a subvolume out of /, and /home, and about every major directory under /. Kubuntu's simplicity allows for a simple script to be used to create snapshots, incremental snapshots, and to do rollbacks. Doing those things manually is equally as easy, as several postings about it on this forum show. I suspect that within 2 or 3 years Btrfs will be the default fs during installation.