This documentation covers the necessary steps to migrate from Kimai 1 to Kimai 2.
Read the version documentation before, to understand if you need this migration guide.
Before starting with the migration, please read the following FAQs:
v1.0.1
and database revision 1388
(check your configuration
table)--fix-email
optioninvisible
1
in the trash
column before importingPlease read the installation docs first and execute the installation. You can install it on the same server, but you have to meet the server requirements (see downloads page).
The other option is to dump the old database and import it in the new server, the import does not need a running Kimai installation, but only the data!
After Kimai 2 runs properly, the actual migration takes place, by importing the data from your Kimai 1 database into Kimai 2.
You have to have SSH access to your server, as you will use a command shipped with Kimai 2, which will pull the data into the new database (configured in your .env
file).
The database does not have to be on the same server, and the database user (for the Kimai 1 tables) needs only read access.
See the help for the import command and all its options and arguments by executing:
bin/console kimai:import:v1 --help
A full command could look like this:
bin/console kimai:import:v1 --global --timezone="timezone" --language="language" --country="DE" --currency="EUR" --prefix="kimai1_" "mysql://user:password@127.0.0.1:3306/database?charset=utf8" "password"
All arguments (eg. country
, currency
, timezone
and language
) are optional and will be set to sensitive defaults if not provided.
Most flags are used for imported customers and users, becuase they were optional or not existing in Kimai 1.
It is recommended to test the import in a fresh database. You can test your import as often as you like and fix possible problems in your installation. A sample command could look like that:
bin/console doctrine:schema:drop --full-database --force && \
bin/console kimai:install -n && \
bin/console kimai:import:v1 --global --timezone="Europe/Zurich" --country="CH" --language="ch" --currency="CHF" "mysql://kimai:test@127.0.0.1:3306/kimai?charset=latin1" "NEW-PASSWORD-1234"
That will drop all tables (including ones that are not created by Kimai) in the configured database and re-create it, before importing the data from the mysql
database at 127.0.0.1
on port 3306
authenticating the user kimai
with the password test
for import.
The connection will use the charset latin1
and the default table prefix kimai_
for reading data. Imported users can login with the password test123
and all customer will have the country CH
and the currency CHF
assigned.
Kimai 1 was written a long time ago, when MySQL was lacking proper UTF8 support and foreign keys. While migrating dozens of customers installations I stumbled upon some recurring problems, that can be solved with some SQL commands.
You can either fix the problems manually as described below, or you let the importer handle all these problems by using the
arguments --fix-email=example.com
, --fix-utf8
and --fix-timesheet
. The argument --skip-error-rates
is also interesting.
If you want to work on these issues manually (for best results) you find infos and tips below.
Many Kimai 1 installations have broken special character (like german umlauts or other language specific non-ascii characters) in the database.
This problem does not show up in the frontend of Kimai 1, as the database connection is using a different collation as the database, which leads to an implicit encoding change. But you can see these problems, when you query the database directly (eg. with a tool like phpMyAdmin).
You can find these broken entries (mainly timesheet descriptions) with SQL statements like these (in the Kimai 1 database):
SELECT * FROM `kimai_timeSheet` WHERE comment like "%ä%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%Ä%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%ü%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%Ü%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%ö%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%Ö%";
SELECT * FROM `kimai_timeSheet` WHERE comment like "%ß%";
Changing them is can be done with SQL queries like these:
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "ä", "ä") WHERE comment like "%ä%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "Ä", "Ä") WHERE comment like "%Ä%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "ü", "ü") WHERE comment like "%ü%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "Ü", "Ü") WHERE comment like "%Ü%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "ö", "ö") WHERE comment like "%ö%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "Ö", "Ö") WHERE comment like "%Ö%";
UPDATE `kimai_timeSheet` SET comment = REPLACE(comment, "ß", "ß") WHERE comment like "%ß%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "ä", "ä") WHERE description like "%ä%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "Ä", "Ä") WHERE description like "%Ä%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "ü", "ü") WHERE description like "%ü%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "Ü", "Ü") WHERE description like "%Ü%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "ö", "ö") WHERE description like "%ö%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "Ö", "Ö") WHERE description like "%Ö%";
UPDATE `kimai_timeSheet` SET description = REPLACE(description, "ß", "ß") WHERE description like "%ß%";
UPDATE `kimai_users` SET name = REPLACE(name, "ä", "ä") WHERE name like "%ä%";
UPDATE `kimai_users` SET name = REPLACE(name, "Ä", "Ä") WHERE name like "%Ä%";
UPDATE `kimai_users` SET name = REPLACE(name, "ü", "ü") WHERE name like "%ü%";
UPDATE `kimai_users` SET name = REPLACE(name, "Ü", "Ü") WHERE name like "%Ü%";
UPDATE `kimai_users` SET name = REPLACE(name, "ö", "ö") WHERE name like "%ö%";
UPDATE `kimai_users` SET name = REPLACE(name, "Ö", "Ö") WHERE name like "%Ö%";
UPDATE `kimai_users` SET name = REPLACE(name, "ß", "ß") WHERE name like "%ß%";
UPDATE `kimai_activities` SET name = REPLACE(name, "ä", "ä") WHERE name like "%ä%";
UPDATE `kimai_activities` SET name = REPLACE(name, "Ä", "Ä") WHERE name like "%Ä%";
UPDATE `kimai_activities` SET name = REPLACE(name, "ü", "ü") WHERE name like "%ü%";
UPDATE `kimai_activities` SET name = REPLACE(name, "Ü", "Ü") WHERE name like "%Ü%";
UPDATE `kimai_activities` SET name = REPLACE(name, "ö", "ö") WHERE name like "%ö%";
UPDATE `kimai_activities` SET name = REPLACE(name, "Ö", "Ö") WHERE name like "%Ö%";
UPDATE `kimai_activities` SET name = REPLACE(name, "ß", "ß") WHERE name like "%ß%";
UPDATE `kimai_projects` SET name = REPLACE(name, "ä", "ä") WHERE name like "%ä%";
UPDATE `kimai_projects` SET name = REPLACE(name, "Ä", "Ä") WHERE name like "%Ä%";
UPDATE `kimai_projects` SET name = REPLACE(name, "ü", "ü") WHERE name like "%ü%";
UPDATE `kimai_projects` SET name = REPLACE(name, "Ü", "Ü") WHERE name like "%Ü%";
UPDATE `kimai_projects` SET name = REPLACE(name, "ö", "ö") WHERE name like "%ö%";
UPDATE `kimai_projects` SET name = REPLACE(name, "Ö", "Ö") WHERE name like "%Ö%";
UPDATE `kimai_projects` SET name = REPLACE(name, "ß", "ß") WHERE name like "%ß%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "ä", "ä") WHERE comment like "%ä%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "Ä", "Ä") WHERE comment like "%Ä%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "ü", "ü") WHERE comment like "%ü%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "Ü", "Ü") WHERE comment like "%Ü%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "ö", "ö") WHERE comment like "%ö%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "Ö", "Ö") WHERE comment like "%Ö%";
UPDATE `kimai_projects` SET comment = REPLACE(comment, "ß", "ß") WHERE comment like "%ß%";
UPDATE `kimai_customers` SET name = REPLACE(name, "ä", "ä") WHERE name like "%ä%";
UPDATE `kimai_customers` SET name = REPLACE(name, "Ä", "Ä") WHERE name like "%Ä%";
UPDATE `kimai_customers` SET name = REPLACE(name, "ü", "ü") WHERE name like "%ü%";
UPDATE `kimai_customers` SET name = REPLACE(name, "Ü", "Ü") WHERE name like "%Ü%";
UPDATE `kimai_customers` SET name = REPLACE(name, "ö", "ö") WHERE name like "%ö%";
UPDATE `kimai_customers` SET name = REPLACE(name, "Ö", "Ö") WHERE name like "%Ö%";
UPDATE `kimai_customers` SET name = REPLACE(name, "ß", "ß") WHERE name like "%ß%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "ä", "ä") WHERE designation like "%ä%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "Ä", "Ä") WHERE designation like "%Ä%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "ü", "ü") WHERE designation like "%ü%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "Ü", "Ü") WHERE designation like "%Ü%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "ö", "ö") WHERE designation like "%ö%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "Ö", "Ö") WHERE designation like "%Ö%";
UPDATE `kimai_expenses` SET designation = REPLACE(designation, "ß", "ß") WHERE designation like "%ß%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "ä", "ä") WHERE comment like "%ä%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "Ä", "Ä") WHERE comment like "%Ä%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "ü", "ü") WHERE comment like "%ü%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "Ü", "Ü") WHERE comment like "%Ü%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "ö", "ö") WHERE comment like "%ö%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "Ö", "Ö") WHERE comment like "%Ö%";
UPDATE `kimai_expenses` SET comment = REPLACE(comment, "ß", "ß") WHERE comment like "%ß%";
Find and update all users, that have no email address:
SELECT * FROM `kimai_users` WHERE mail = '' OR mail IS NULL;
UPDATE `kimai_users` SET mail = concat(name, '@example.com') WHERE mail = '' OR mail IS NULL;
Update an account with a new password
in your Kimai 1 database:
UPDATE `kimai_users` SET password = md5(concat('your-salt', 'new-password', 'your-salt')) WHERE userID = XYZ;