This documentation covers all necessary steps to migrate from Kimai 1 to Kimai 2.
Before starting with the migration, please read the following FAQs:
v1.0.1and database revision
trashcolumn before importing
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 configured database from your
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 arguments by executing:
bin/console kimai:import --help
A full command would look like this:
bin/console kimai:import-v1 "mysql://user:email@example.com:3306/database?charset=utf8" "db_prefix" "password" "country" "currency" --timezone="timezone" --language="language"
currency are optional and will be set to DE and EUR if not given.
language are used for imported users, in case they didn’t set it in Kimai 1 (you should update the preference in Kimai 1 before importing, if you work across different timezones).
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 --force && \ bin/console doctrine:schema:create && \ bin/console kimai:import-v1 "mysql://kimai:firstname.lastname@example.org:3306/kimai?charset=latin1" "kimai_" "test123" "CH" "CHF" --timezone="Europe/Zurich" --language="ch"
That will drop the configured Kimai v2 database schema 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
Kimai 1 was written a long time ago, when MySQL was lacking proper UTF8 support and foreign keys (in shared hostings). While migrating dozens of customers installations I stumbled upon some recurring problems, that can be solved with some SQL commands.
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 og Kimai 1, as the database connection is using a different collation then the database. But you can see these problems, when you query the database directly (eg. with a tool like phpMyAdmin).
You can fix these broken entries (mainly timesheet descriptions) with SQL statements like these:
SELECT * FROM `kimai2_timesheet` WHERE description like "%Ã¼%";
They cannot be fixed automatically by Kimai 2, but changing them is then just a matter of rewriting these SQL queries:
UPDATE `kimai2_timesheet` SET description = REPLACE(description, "Ã¼", "ü") WHERE description like "%Ã¼%"; UPDATE `kimai2_timesheet` SET description = REPLACE(description, "Ã¤", "ä") WHERE description 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;