Bug 323977 - information_schema.REFERENTIAL_CONSTRAINTS errors in akonadi server log
Summary: information_schema.REFERENTIAL_CONSTRAINTS errors in akonadi server log
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Unclassified
Component: server (show other bugs)
Version: 1.10.2
Platform: Fedora RPMs Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-08-24 17:09 UTC by Geert Janssens
Modified: 2013-08-26 16:34 UTC (History)
1 user (show)

See Also:
Latest Commit:
Version Fixed In: 1.10.3


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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).