Bug 323977

Summary:	information_schema.REFERENTIAL_CONSTRAINTS errors in akonadi server log
Product:	[Frameworks and Libraries] Akonadi	Reporter:	Geert Janssens <info>
Component:	server	Assignee:	kdepim bugs <kdepim-bugs>
Status:	RESOLVED FIXED
Severity:	normal	CC:	dvratil
Priority:	NOR
Version:	1.10.2
Target Milestone:	---
Platform:	Fedora RPMs
OS:	Linux
Latest Commit:	http://commits.kde.org/akonadi/10d1e2bda3ebc2007f6907b926f02618b2706224	Version Fixed In:	1.10.3

Description Geert Janssens 2013-08-24 17:09:32 UTC

Whenever akonadi starts, it generates a series of errors in the server log:
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 
Error during executing query "SELECT information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME, information_schema.KEY_COLUMN_USAGE.COLUMN_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE, information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS INNER JOIN information_schema.KEY_COLUMN_USAGE ON ( information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME ) WHERE ( information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = ( :0 ) AND information_schema.KEY_COLUMN_USAGE.TABLE_NAME = ( :1 ) )" :  "Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema QMYSQL: Unable to execute query" 

Apart from this, akonadi seems to work ok.

It may be relevant that I am using a real database server as backend:
Centos 5 with mysql-server-5.0.95-5.el5_9

Reproducible: Always

Steps to Reproduce:
1. Set up a Centos 5 server with mysql version 5.0.95
2. Configure akonadi to use that server as backend
3. Start akonadi
Actual Results:  
The errors presented above.

Expected Results:  
No errors

Comment 1 Daniel Vrátil 2013-08-26 12:08:25 UTC

REFERENTIAL_CONSTRAINTS column was introduced in MySQL 5.1.

This means that on startup Akonadi is not able to verify that all foreign keys are correctly set up in the database, which can cause some troubles when upgrading the database scheme.

I recommend you update to at least MySQL 5.1 (or MariaDB) and I will fix Akonadi to check mimimum database version on startup.

Comment 2 Daniel Vrátil 2013-08-26 12:14:13 UTC

Git commit 10d1e2bda3ebc2007f6907b926f02618b2706224 by Dan Vrátil.
Committed on 26/08/2013 at 12:12.
Pushed by dvratil into branch '1.10'.

Check minimum MySQL server version

Akonadi requires at least MySQL 5.1 (because of using information_schema.REFERENTIAL_CONSTRAINTS
column that was introduced in 5.1), so make sure we are running against at least that version.
FIXED-IN: 1.10.3

M  +30   -0    server/src/storage/dbconfigmysql.cpp

http://commits.kde.org/akonadi/10d1e2bda3ebc2007f6907b926f02618b2706224

Comment 3 Geert Janssens 2013-08-26 16:34:13 UTC

Thank you for the quick follow up. For now I have reverted to using internal mysql instead of a remote one. In that setup, the errors are gone (Fedora 19 ships mariadb 5.5.31).