[Akonadi] [Bug 362580] New: akonadi server crashes when recreating the MySQL akonadi database

[Christoph Pospiech via KDE Bugzilla]

https://bugs.kde.org/show_bug.cgi?id=362580

            Bug ID: 362580
           Summary: akonadi server crashes when recreating the MySQL
                    akonadi database
           Product: Akonadi
           Version: 16.04
          Platform: Kubuntu Packages
                OS: Linux
            Status: UNCONFIRMED
          Severity: crash
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: pospiech-HD at t-online.de

The akonadi database is maintained on a external MySQL server
(/usr/sbin/mysqld).
The error occurs after upgrading to Kubuntu 16.04, which includes upgrading
mysql-server-5.6 to mysql-server-5.7 . Assuming the akonadi database schema has
changed, I tried to drop the database and recreate it. This crashed when
attempting to create the table PimItemTable.

Reproducible: Always

Steps to Reproduce:
1. akonadictl stop
2. mysql -h localhost -u akonadi -p
3. mysql> drop database if exists akonadi;
4. akonadictl start

Actual Results:  
"CREATE TABLE PimItemTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, rev
INTEGER NOT NULL DEFAULT 0, remoteId VARBINARY(255), remoteRevision
VARBINARY(255), gid VARBINARY(255), collectionId BIGINT, mimeTypeId BIGINT,
datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP, dirty BOOL, size
BIGINT NOT NULL DEFAULT 0, FOREIGN KEY (collectionId) REFERENCES
CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY
(mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT)
 COLLATE=utf8_general_ci DEFAULT CHARSET=utf8"
"\nSql error: Invalid default value for 'atime' QMYSQL: Unable to execute
query\nQuery: CREATE TABLE PimItemTable (id BIGINT NOT NULL AUTO_INCREMENT
PRIMARY KEY, rev INTEGER NOT NULL DEFAULT 0, remoteId VARBINARY(255),
remoteRevision VARBINARY(255), gid VARBINARY(255), collectionId BIGINT,
mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime
TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, FOREIGN KEY
(collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE
CASCADE, FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE
CASCADE ON DELETE RESTRICT)  COLLATE=utf8_general_ci DEFAULT CHARSET=utf8"
Unable to initialize database.
terminating service threads
terminating connection threads
stopping db process
Failed to remove runtime connection config file
Application 'akonadiserver' exited normally...


Expected Results:  
PimItemTable should be created along with all other tables and akonadi server
should run.

Work around:
After recreating the above crash, execute the following SQL statements on the
mysql command prompt.
Then redo "akonadictl start".
mysql> 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
mysql>  CREATE TABLE PimItemTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY
KEY, rev INTEGER NOT NULL DEFAULT 0, remoteId VARBINARY(255), remoteRevision
VARBINARY(255), gid VARBINARY(255), collectionId BIGINT, mimeTypeId BIGINT,
datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,  dirty BOOL, size BIGINT NOT NULL DEFAULT 0, FOREIGN KEY
(collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE
CASCADE, FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE
CASCADE ON DELETE RESTRICT)  COLLATE=utf8_general_ci DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

This is the same SQL statement as in the "Actual Results" tab above, but
"DEFAULT CURRENT_TIMESTAMP" added after "atime TIMESTAMP".

-- 
You are receiving this mail because:
You are the assignee for the bug.