Gentoo Websites Logo
Go to: Gentoo Home Documentation Forums Lists Bugs Planet Store Wiki Get Gentoo!
Bugzilla DB migration completed. Please report issues to Infra team via email via infra@gentoo.org or IRC
Bug 688746 - kde-apps/akonadi-18.12.3-r1 with dev-db/mariadb-10.4.6 - org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelation`, CONSTRAINT `collectionmimetyperelation_ibfk_1` ...
Summary: kde-apps/akonadi-18.12.3-r1 with dev-db/mariadb-10.4.6 - org.kde.pim.akonadis...
Status: CONFIRMED
Alias: None
Product: Gentoo Linux
Classification: Unclassified
Component: Current packages (show other bugs)
Hardware: All Linux
: Normal normal with 1 vote (vote)
Assignee: Gentoo KDE team
URL: https://bugs.kde.org/show_bug.cgi?id=...
Whiteboard: workaround: mysql_upgrade --socket=/t...
Keywords: UPSTREAM
Depends on:
Blocks:
 
Reported: 2019-06-26 14:24 UTC by jms
Modified: 2019-08-25 11:14 UTC (History)
6 users (show)

See Also:
Package list:
Runtime testing required: ---


Attachments
emerge --info (emerge--info.txt,7.44 KB, text/plain)
2019-06-26 14:29 UTC, jms
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jms 2019-06-26 14:24:15 UTC
After an upgrade to mariadb-10.4.6 I expermented some problems with kdepim.
-not able to send mail (error with tag)
-not fetching storing any new mail
I went down to try 
https://docs.kde.org/trunk5/en/pim/kmail2/clean-start-after-a-failed-migration.html

then i noticed problem with database inserts(DB error: "Cannot add or update a child row: a foreign key constraint fails ) pbs as described in log extract below


I downgraded to mariadb-10.3.16 and this seems to sort my problem as I am know re-adding my accounts to kmail and able to send mail.

I wonder if there are some problem between mariadb-10.4.6 and kde-akonadi-18.12.3-r1
maybe due to the change as noted in elog
"Log: postinst
This version of mariadb reorganizes the configuration from a single my.cnf
to several files in /etc/mysql/mariadb.d.
Please backup any changes you made to /etc/mysql/my.cnf
and add them as a new file under /etc/mysql/mariadb.d with a .cnf extension.
You may have as many files as needed and they are read alphabetically.
Be sure the options have the appropriate section headers, i.e. [mysqld].
"
or something else with this version of mariadb

I only know that since this update it looks like I couln't use kmail
I was going to reset my akonadi config/mysqldb folowing
"Clean start after a failed migration"
https://userbase.kde.org/KMail/FAQs_Hints_and_Tips

but i do encounter several errors
in ~/.local/share/akonadi/db_data/mysql.err

2019-06-26 13:14:41 0 [Note] InnoDB: Using Linux native AIO
2019-06-26 13:14:41 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-06-26 13:14:41 0 [Note] InnoDB: Uses event mutexes
2019-06-26 13:14:41 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-06-26 13:14:41 0 [Note] InnoDB: Number of pools: 1
2019-06-26 13:14:41 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-06-26 13:14:41 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2019-06-26 13:14:41 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-06-26 13:14:41 0 [Note] InnoDB: Completed initialization of buffer pool
2019-06-26 13:14:41 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-06-26 13:14:41 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-06-26 13:14:41 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-06-26 13:14:41 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-06-26 13:14:41 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-06-26 13:14:41 0 [Note] InnoDB: Waiting for purge to start
2019-06-26 13:14:41 0 [Note] InnoDB: 10.4.6 started; log sequence number 401125; transaction id 310
2019-06-26 13:14:41 0 [Note] InnoDB: Loading buffer pool(s) from /home/jms/.local/share/akonadi/db_data/ib_buffer_pool
2019-06-26 13:14:41 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-06-26 13:14:41 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2019-06-26 13:14:41 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2019-06-26 13:14:41 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1017: Can't find file: './mysql/' (errno: 2 "No such file or directory")
2019-06-26 13:14:41 0 [Note] Reading of all Master_info entries succeeded
2019-06-26 13:14:41 0 [Note] Added new Master_info '' to hash table
2019-06-26 13:14:41 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.4.6-MariaDB' socket: '/tmp/akonadi-jms.ccu7Fb/mysql.socket' port: 0 Source distribution
2019-06-26 13:14:41 0 [Note] InnoDB: Buffer pool(s) load completed at 190626 13:14:41
2019-06-26 13:14:41 9 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
2019-06-26 13:14:41 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
and so on with Transaction not registered for MariaDB 2PC, but transaction is active

and in ~/.local/share/akonadi/akonadiserver.error
[...]
019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: DATABASE ERROR:
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Error code: "1452"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelation`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelation`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) QMYSQL3: Unable to execute statement"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(qlonglong, 5))(":1", QVariant(qlonglong, 7)))
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Query: "INSERT INTO CollectionMimeTypeRelation (Collection_id, MimeType_id) VALUES (:0, :1)"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Error during adding a record to table "CollectionMimeTypeRelation"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Failed to append mimetype "inode/directory" to Collection 5
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Failed to append mimetypes ("inode/directory", "message/rfc822") to new collection "akonadi_maildir_resource_1" (ID 5 ) in resource "akonadi_maildir_resource_1"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Error while handling command CreateCollection on connection akonadi_maildir_resource_1 (0x5636ef5aebb0)
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: DATABASE ERROR:
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Error code: "1452"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelation`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelation`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) QMYSQL3: Unable to execute statement"
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(qlonglong, 6))(":1", QVariant(qlonglong, 7)))
2019-06-26T13:14:44 [CRITICAL] org.kde.pim.akonadiserver: Query: "INSERT INTO CollectionMimeTypeRelation (Collection_id, MimeType_id) VALUES (:0, :1)"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Error during adding a record to table "CollectionMimeTypeRelation"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Failed to append mimetype "inode/directory" to Collection 6
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Failed to append mimetypes ("inode/directory", "message/rfc822") to new collection "Local Folders" (ID 6 ) in resource "akonadi_maildir_resource_1"
2019-06-26T13:14:44 [WARN ] org.kde.pim.akonadiserver: Error while handling command CreateCollection on connection akonadi_maildir_resource_1 (0x5636ef5aebb0)
Comment 1 jms 2019-06-26 14:27:38 UTC
I have this now
mariadb
Installed versions:  10.3.16(0/18)(15:59:51 26/06/2019)(backup bindist pam perl server xml -client-libs -cracklib -debug -extraengine -galera -innodb-lz4 -innodb-lzo -innodb-snappy -jdbc -jemalloc -kerberos -latin1 -libressl -mroonga -numa -odbc -oqgraph -profiling -rocksdb -selinux -sphinx -sst-mariabackup -sst-rsync -static -systemd -systemtap -tcmalloc -test -tokudb -yassl ELIBC="-FreeBSD")

and 
I] kde-apps/akonadi
     Available versions:  (5) 18.12.3-r1^t **18.12.49.9999^t[1] (~)19.04.2^t **9999^t[1]
       {debug designer +mysql postgres sqlite test tools xml}
     Installed versions:  19.04.2(5)^t(15:31:14 07/06/2019)(mysql tools xml -debug -designer -postgres -sqlite -test)
     Homepage:            https://community.kde.org/KDE_PIM/akonadi
     Description:         Storage service for PIM data and libraries for PIM apps
Comment 2 jms 2019-06-26 14:29:06 UTC
Created attachment 580974 [details]
emerge --info
Comment 3 jms 2019-06-26 15:02:24 UTC
is going to mariadb-10.3 To mariadb-10.4 a major version change ?
as I didn't run the mysql_upgrade tool.

In this case no a bug but a misunderstanding...
Comment 4 Mark Nowiasz 2019-06-27 18:25:03 UTC
I can confirm that it's 10.4.6 related. After downgrading to mariadb 10.3.x, kmail works again - and is able to fetch news mails.
Comment 5 Mark Nowiasz 2019-06-28 14:12:11 UTC
Upstream bug: https://bugs.kde.org/show_bug.cgi?id=409224
Comment 6 jms 2019-06-28 16:46:49 UTC
so (In reply to Mark Nowiasz from comment #5)
> Upstream bug: https://bugs.kde.org/show_bug.cgi?id=409224
ok
mabe ebuild should have
<dev-db/mariadb-10.4.6

by the way
I reported problem with
kde-apps/akonadi-18.12.3-r1 
but in fact I think I had
kde-apps/akonadi-19.04.2

Is this bug is affecting both version?
can you specify which akonadi version you have this problem with?
Comment 7 Mark Nowiasz 2019-06-28 16:56:24 UTC
> 
> Is this bug is affecting both version?
> can you specify which akonadi version you have this problem with?

I've got 19.04.2 - the update to mariadb was done together with lots of packages, including kde and plasma ones. So I can't tell if the problem was also in 18.x.
Comment 8 Pavel Volkov 2019-07-02 09:27:27 UTC
I had to run:

mysql_upgrade --socket=/tmp/akonadi-rondo.mpDWDW/mysql.socket 


Which gave me this error:

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed

And then I created mysql database:
mysql --socket=/tmp/akonadi-rondo.mpDWDW/mysql.socket
create database mysql;

Then I ran mysql_upgrade again.
After that connecting to socket was impossible for both mysql client and Akonadi.

Running "akonadictl restart" fixed everything.
Comment 9 MickKi 2019-07-11 10:36:21 UTC
(In reply to Pavel Volkov from comment #8)

> And then I created mysql database:
> mysql --socket=/tmp/akonadi-rondo.mpDWDW/mysql.socket
> create database mysql;

The akonadi database runs as your normal desktop user.  It does not have the default mysql database tables, only akonadi relevant tables.  Your creation of a mysql database messes up akonadi as it adds a new database and tables and this is why you experienced problems thereafter.  This is what the mysql structure used by akonadi contains on my system:

====================================================================
$ mysql -u michael --socket=/tmp/akonadi-michael.tJdRwT/mysql.socket
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.2.22-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| akonadi            |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use akonadi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [akonadi]> show tables;
+----------------------------------+
| Tables_in_akonadi                |
+----------------------------------+
| collectionattributetable         |
| collectionmimetyperelation       |
| collectionpimitemrelation        |
| collectiontable                  |
| flagtable                        |
| mimetypetable                    |
| parttable                        |
| parttypetable                    |
| pimitemflagrelation              |
| pimitemtable                     |
| pimitemtagrelation               |
| relationtable                    |
| relationtypetable                |
| resourcetable                    |
| schemaversiontable               |
| tagattributetable                |
| tagremoteidresourcerelationtable |
| tagtable                         |
| tagtypetable                     |
+----------------------------------+
19 rows in set (0.00 sec)

MariaDB [akonadi]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| GEOMETRY_COLUMNS                      |
| SPATIAL_REF_SYS                       |
| CLIENT_STATISTICS                     |
| INDEX_STATISTICS                      |
| INNODB_SYS_DATAFILES                  |
| USER_STATISTICS                       |
| INNODB_SYS_TABLESTATS                 |
| INNODB_LOCKS                          |
| INNODB_MUTEXES                        |
| INNODB_CMPMEM                         |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP                            |
| INNODB_FT_DELETED                     |
| INNODB_CMP_RESET                      |
| INNODB_LOCK_WAITS                     |
| TABLE_STATISTICS                      |
| INNODB_TABLESPACES_ENCRYPTION         |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FIELDS                     |
| INNODB_CMPMEM_RESET                   |
| INNODB_SYS_COLUMNS                    |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_CMP_PER_INDEX_RESET            |
| user_variables                        |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_FT_BEING_DELETED               |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_TRX                            |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLES                     |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_CONFIG                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_VIRTUAL                    |
| INNODB_TABLESPACES_SCRUBBING          |
| INNODB_SYS_SEMAPHORE_WAITS            |
+---------------------------------------+
76 rows in set (0.00 sec)

MariaDB [information_schema]> \q
Bye
================================

Adding databases and tables in akonadi is probably not advisable.  In any case, akonadiserver which launches mysql for akonadi to use, always runs mysqlcheck at the start to catch any database version upgrade changes and resolve them.  Therefore, AFAIK there should be no need to run mysql_upgrade manually.
-- 
Regards,
Mick
Comment 10 Pavel Volkov 2019-07-11 17:15:47 UTC
What do you mean by problems thereafter? I didn't have any.

I also didn't have 'information_schema' db before mysql_upgrade.
Comment 11 MickKi 2019-07-12 07:38:44 UTC
(In reply to Pavel Volkov from comment #10)
> What do you mean by problems thereafter? I didn't have any.

I meant you not being able to connect (to the same socket), but this may be because the mysql-upgrade script restarted mysql and created a new socket?  I do not know if creating a new database on the same socket akonadi is running, will generate new/different global variables and stats which could mess up akonadi's operation.  I was just being cautionary.

> I also didn't have 'information_schema' db before mysql_upgrade.

I have an 'information_schema' as shown above on mariadb-10.2.22-r1.  My understanding is this is created by default whenever a database is set up.
-- 
Regards,
Mick
Comment 12 Larry the Git Cow gentoo-dev 2019-07-23 18:55:20 UTC
The bug has been referenced in the following commit(s):

https://gitweb.gentoo.org/repo/gentoo.git/commit/?id=ea3a25e7a92db18125aba2cc1e6e7323ed9dd550

commit ea3a25e7a92db18125aba2cc1e6e7323ed9dd550
Author:     Andreas Sturmlechner <asturm@gentoo.org>
AuthorDate: 2019-07-23 18:49:27 +0000
Commit:     Andreas Sturmlechner <asturm@gentoo.org>
CommitDate: 2019-07-23 18:55:05 +0000

    kde-apps/akonadi: Warn users about the trouble with USE mysql
    
    External DBs can't be fixed automatically, and >=mariadb-10.4 still can't
    be upgraded automatically, at least add warnings that hopefully will be read.
    
    Bug: https://bugs.gentoo.org/688746
    Package-Manager: Portage-2.3.69, Repoman-2.3.16
    Signed-off-by: Andreas Sturmlechner <asturm@gentoo.org>

 kde-apps/akonadi/akonadi-19.04.3.ebuild | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)
Comment 13 Larry the Git Cow gentoo-dev 2019-07-24 11:32:27 UTC
The bug has been referenced in the following commit(s):

https://gitweb.gentoo.org/proj/kde.git/commit/?id=b7b95f71ddff77ab067caba16374081b4949f8d0

commit b7b95f71ddff77ab067caba16374081b4949f8d0
Author:     Andreas Sturmlechner <asturm@gentoo.org>
AuthorDate: 2019-07-23 18:49:27 +0000
Commit:     Andreas Sturmlechner <asturm@gentoo.org>
CommitDate: 2019-07-24 11:29:25 +0000

    kde-apps/akonadi: Warn users about the trouble with USE mysql
    
    External DBs can't be fixed automatically, and >=mariadb-10.4 still can't
    be upgraded automatically, at least add warnings that hopefully will be read.
    
    Bug: https://bugs.gentoo.org/688746
    Package-Manager: Portage-2.3.69, Repoman-2.3.16
    Signed-off-by: Andreas Sturmlechner <asturm@gentoo.org>

 kde-apps/akonadi/akonadi-19.07.80.ebuild      | 22 ++++++++++++++++++++++
 kde-apps/akonadi/akonadi-19.08.49.9999.ebuild | 22 ++++++++++++++++++++++
 kde-apps/akonadi/akonadi-9999.ebuild          | 22 ++++++++++++++++++++++
 3 files changed, 66 insertions(+)