msql.err for Akonadi 1.13.1_pre20160203

Fri Oct 14 09:55:15 BST 2016

Hi,

With the attention going to database issues these last few days on the ML, I thought I might us the occasion to get help diagnosing the issues of my Akonadi MySQl DB install. This is about akonadi-server  1.13.1_pre20160203. (KF5 version is not yet stable on my distro, Gentoo)

My practical experience is that most things usually work (there are some issues with the IMAP backend not propagating appends or flag changes, but that's separate).

But perhaps things could work smoother once I get rid of some issues causing the error messages I see in the attached files!

Also, I just noticed that ~/.local/share/akonadi/file_lost+found contains about 2.9GB in about 12k files! (~/.local/share/akonadi/file_db_data is about 0.5GB in about 11k files) I guess there is an opportunity to reclaim some space here? (I think I have about 0.4GB of mail, with contact and calendar stuff adding a little bit on top.)

I'll add in the MySQL configuration to give the right context. (I haven't changed it from the installed version.)

Best,

Erik
-------------- next part --------------
2016-10-14 09:45:27 4008 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2016-10-14 09:45:27 4008 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 256)

2016-10-14 09:45:27 4008 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-10-14 09:45:27 4008 [Note] InnoDB: The InnoDB memory heap is disabled
2016-10-14 09:45:27 4008 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-10-14 09:45:27 4008 [Note] InnoDB: Memory barrier is not used
2016-10-14 09:45:27 4008 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-10-14 09:45:27 4008 [Note] InnoDB: Using Linux native AIO
2016-10-14 09:45:27 4008 [Note] InnoDB: Using CPU crc32 instructions
2016-10-14 09:45:27 4008 [Note] InnoDB: Initializing buffer pool, size = 80.0M
2016-10-14 09:45:27 4008 [Note] InnoDB: Completed initialization of buffer pool
2016-10-14 09:45:27 4008 [Note] InnoDB: Highest supported file format is Barracuda.
2016-10-14 09:45:27 4008 [Note] InnoDB: 128 rollback segment(s) are active.
2016-10-14 09:45:27 4008 [Note] InnoDB: Waiting for purge to start
2016-10-14 09:45:27 4008 [Note] InnoDB: 5.6.33 started; log sequence number 4888377776
2016-10-14 09:45:27 4008 [Note] RSA private key file not found: /home/equaeghe/.local/share/akonadi/db_data//private_key.pem. Some authentication plugins will not work.
2016-10-14 09:45:27 4008 [Note] RSA public key file not found: /home/equaeghe/.local/share/akonadi/db_data//public_key.pem. Some authentication plugins will not work.
2016-10-14 09:45:27 4008 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2016-10-14 09:45:27 4008 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thread_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_account_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_user_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_host_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_stages_summary_global_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_history_long' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_thread_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
2016-10-14 09:45:27 4008 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
2016-10-14 09:45:27 4008 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.33'  socket: '/tmp/akonadi-equaeghe.53rmjV/mysql.socket'  port: 0  Source distribution
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."collectionattributetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."collectionmimetyperelation" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."collectionpimitemrelation" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."collectiontable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."flagtable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."mimetypetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."parttable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."parttypetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."pimitemflagrelation" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."pimitemtable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."pimitemtagrelation" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."resourcetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."schemaversiontable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."tagattributetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."tagremoteidresourcerelationtable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."tagtable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-10-14 09:45:27 7f5657183700 InnoDB: Error: Fetch of persistent statistics requested for table "akonadi"."tagtypetable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
-------------- next part --------------
Executable "akonadi_nepomuk_feeder" for agent "akonadi_nepomuk_feeder" could not be found! 
Executable "akonadi_folderarchive_agent" for agent "akonadi_folderarchive_agent" could not be found! 
-------------- next part --------------
DATABASE ERROR: 
  Error code: 1062 
  DB error:  "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex'" 
  Error text: "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex' QMYSQL3: Unable to execute statement" 
  Query: "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, enabled, syncPref, displayPref, indexPref, cachePolicyInherit, isVirtual) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" 
DATABASE ERROR: 
  Error code: 1062 
  DB error:  "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex'" 
  Error text: "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex' QMYSQL3: Unable to execute statement" 
  Query: "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, enabled, syncPref, displayPref, indexPref, cachePolicyInherit, isVirtual) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" 
DATABASE ERROR: 
  Error code: 1062 
  DB error:  "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex'" 
  Error text: "Duplicate entry '13-INBOX' for key 'CollectionTable_parentAndNameIndex' QMYSQL3: Unable to execute statement" 
  Query: "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, enabled, syncPref, displayPref, indexPref, cachePolicyInherit, isVirtual) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" 
-------------- next part --------------
#
# Global Akonadi MySQL server settings,
# These settings can be adjusted using $HOME/.config/akonadi/mysql-local.conf
#
# Based on advice by Kris Köhntopp <kris at mysql.com>
#
[mysqld]

# strict query parsing/interpretation
# TODO: make Akonadi work with those settings enabled
# sql_mode=strict_trans_tables,strict_all_tables,strict_error_for_division_by_zero,no_auto_create_user,no_auto_value_on_zero,no_engine_substitution,no_zero_date,no_zero_in_date,only_full_group_by,pipes_as_concat
# sql_mode=strict_trans_tables

# DEBUGGING:
# log all queries, useful for debugging but generates an enormous amount of data
# log=mysql.full
# log queries slower than n seconds, log file name relative to datadir (for debugging only)
# log_slow_queries=mysql.slow
# long_query_time=1
# log queries not using indices, debug only, disable for production use
# log_queries_not_using_indexes=1
#
# mesure database size and adjust innodb_buffer_pool_size
# SELECT sum(data_length) as bla, sum(index_length) as blub FROM information_schema.tables WHERE table_schema not in ("mysql", "information_schema");

# NOTES:
# Keep Innob_log_waits and keep Innodb_buffer_pool_wait_free small (see show global status like "inno%", show global variables)

#expire_logs_days=3

#sync_bin_log=0

# Use UTF-8 encoding for tables
character_set_server=utf8
collation_server=utf8_general_ci

# use InnoDB for transactions and better crash recovery
default_storage_engine=innodb

# memory pool InnoDB uses to store data dictionary information and other internal data structures (default:8M)
# Deprecated in MySQL >= 5.6.3, removed in 5.7 (works in MariaDB)
# innodb_additional_mem_pool_size=8M

# memory buffer InnoDB uses to cache data and indexes of its tables (default:128M)
# Larger values means less I/O
innodb_buffer_pool_size=80M

# Create a .ibd file for each table (default:0)
innodb_file_per_table=1

# Write out the log buffer to the log file at each commit (default:1)
innodb_flush_log_at_trx_commit=2

# Buffer size used to write to the log files on disk (default:1M for builtin, 8M for plugin)
# larger values means less I/O
innodb_log_buffer_size=1M

# Size of each log file in a log group (default:5M) larger means less I/O but more time for recovery.
innodb_log_file_size=64M

# # error log file name, relative to datadir (default:hostname.err)
log_error=mysql.err

# print warnings and connection errors (default:1)
log_warnings=2

# Convert table named to lowercase
lower_case_table_names=1

# Maximum size of one packet or any generated/intermediate string. (default:1M)
max_allowed_packet=32M

# Maximum simultaneous connections allowed (default:100)
max_connections=256

# The two options below make no sense with prepared statements and/or transactions
# (make sense when having the same query multiple times)

# Memory allocated for caching query results (default:0 (disabled))
query_cache_size=0

# Do not cache results (default:1)
query_cache_type=0

# Do not use the privileges mechanisms
skip_grant_tables

# Do not listen for TCP/IP connections at all
skip_networking

# The number of open tables for all threads. (default:64)
table_open_cache=200

# How many threads the server should cache for reuse (default:0)
thread_cache_size=3

# wait 365d before dropping the DB connection (default:8h)
wait_timeout=31536000

# We use InnoDB, so don't let MyISAM eat up memory
key_buffer_size=16K

[client]
default-character-set=utf8