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)
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
Created attachment 580974 [details] emerge --info
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...
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.
Upstream bug: https://bugs.kde.org/show_bug.cgi?id=409224
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?
> > 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.
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.
(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
What do you mean by problems thereafter? I didn't have any. I also didn't have 'information_schema' db before mysql_upgrade.
(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
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(+)
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(+)
The bug has been referenced in the following commit(s): https://gitweb.gentoo.org/proj/kde.git/commit/?id=c64308088b7b68117d4243ee02d0cbf3adbaf432 commit c64308088b7b68117d4243ee02d0cbf3adbaf432 Author: Andreas Sturmlechner <asturm@gentoo.org> AuthorDate: 2021-03-16 23:04:33 +0000 Commit: Andreas Sturmlechner <asturm@gentoo.org> CommitDate: 2021-04-04 19:50:18 +0000 kde-apps/akonadi: Update IUSE=mysql instructions using readme.gentoo Provide detailed instructions for how to fix existing backends. Bug: https://bugs.gentoo.org/688746 Package-Manager: Portage-3.0.17, Repoman-3.0.2 Signed-off-by: Andreas Sturmlechner <asturm@gentoo.org> kde-apps/akonadi/akonadi-9999.ebuild | 15 ++++++-------- kde-apps/akonadi/files/README.gentoo | 39 ++++++++++++++++++++++++++++++++++++ 2 files changed, 45 insertions(+), 9 deletions(-)
The bug has been referenced in the following commit(s): https://gitweb.gentoo.org/repo/gentoo.git/commit/?id=fe87d665b6f99bad6352d89eb9862632ba144fa0 commit fe87d665b6f99bad6352d89eb9862632ba144fa0 Author: Andreas Sturmlechner <asturm@gentoo.org> AuthorDate: 2021-03-16 23:04:33 +0000 Commit: Andreas Sturmlechner <asturm@gentoo.org> CommitDate: 2021-04-04 19:53:09 +0000 kde-apps/akonadi: Update IUSE=mysql instructions using readme.gentoo Provide detailed instructions for how to fix existing backends. Bug: https://bugs.gentoo.org/688746 Package-Manager: Portage-3.0.17, Repoman-3.0.2 Signed-off-by: Andreas Sturmlechner <asturm@gentoo.org> kde-apps/akonadi/akonadi-20.12.3.ebuild | 15 +++++-------- kde-apps/akonadi/files/README.gentoo | 39 +++++++++++++++++++++++++++++++++ 2 files changed, 45 insertions(+), 9 deletions(-)
Has not been a problem in a very long time.
