Home Contact Buy
Sitemap Contact
Home Download Video Free Buy Sitemap Contact

How to Backup and Restore Time Tracker Database

Use mysqldump utility to dump the content of your database to a file. For example, for a database named timetracker type the following at the command prompt:
# mysqldump -u root -p[root_password] --default-character-set=utf8mb4 timetracker > timetracker.sql
The resulting timetracker.sql file will contain SQL statements required to recreate the database from scratch. If you need to dump a database from a remote server, use this command:
# mysqldump -u root -p[root_password] -h server_name --default-character-set=utf8mb4 timetracker > timetracker.sql

How to Restore

If you need to restore your database from a dump file use the following command:
# mysql -u root -p[root_password] db_name < dump_file.sql
Do not restore an old dump file into a running production database. The entire content of db_name will be re-created from dump_file.sql and your recent changes will get lost.

Resolving Problems with UTF-8 Encoding

After import, you may see corrupted UTF-8 encoded characters (such as ß in German Straße, accented ä and so on). What happens after import is that instead of an ß in "Straße" you may see garbage like "Straße" on your Time Tracker web pages. And if you examine the dump_file.sql with a binary editor, you may see that the ß, instead of needed UTF-8 2 byte encoding "C3 9F" is encoded with 4 bytes such as "C3 83 C5 B8".

Troubleshooting

The problem may be difficult to troubleshoot because it's hard to determine where the corruption occurs. For example, you may think it's the import problem, or may be console-input related problem, while the issue is in the corrupted dump file. And your database may be large to further complicate things. We located the problem like this:
  • Created a simple database with one table and one varchar(80) field.
  • Inserted "Straße" into a row it via a modified Time Tracker web page (to ensure UTF-8 encoding).
  • Did a dump as per command above.
  • Examined the content of the dump file with a binary editor. At this point it became evident that the ß is encoded by 4-bytes instead of 2 "C3 9F".

More Troubleshooting

It is best to start with the database to see binary codes for Unicode characters in it. For example, register a new account with ß in all fields and then examine the data with a query like this (substitute 10 with a real user id):
select name, hex(name) from tt_users where id = 10;
Normal output is like this for utf8mb4 encoding. hex() function obtains hexadecimal content of the field.
mysql> select name, hex(name) from tt_users where id = 10;
+------+-----------+
| name | hex(name) |
+------+-----------+
| ß   | C39F      |
+------+-----------+
If instead you see "C383C5B8" in there (for example, when not using charset=utf8mb4 in your DSN), then encoding in database is wrong and you need to fix it using a solution described below.

Solution

The solution consists of 2 steps.
  1. Call mysqldump with the "--default-character-set=latin1" parameter.
  2. Edit the dump file and change the encoding in "SET NAMES" instruction in beginning of the file to "utf8".
The resulting mysqldump command:
# mysqldump -u root -p[root_password] --default-character-set=latin1 timetracker > timetracker.sql
This is how the "SET NAMES" line in the dump file should look like after editing.
/*!40101 SET NAMES utf8mb4 */;
There is no change to the restore procedure. Use mysql command as above.

How to Edit Large Files

If your file is large, you may have a problem editing it or even finding a suitable editor to handle it. We used the following script called hfnano written by user B T at this stackoverflow thread. It opens a small section of a large file in nano editor, then saves your changes back into a large file:
#!/bin/sh

if [ "$#" -ne 3 ]; then
  echo "Usage: $0 hugeFilePath startLine endLine" >&2
  exit 1
fi

sed -n -e $2','$3'p' -e $3'q' $1 > hfnano_temporary_file
nano hfnano_temporary_file
(head -n `expr $2 - 1` $1; cat hfnano_temporary_file; sed -e '1,'$3'd' $1) > hfnano_temporary_file2
cat hfnano_temporary_file2 > $1
rm hfnano_temporary_file hfnano_temporary_file2

Related Double-Encoding Problem with Console Output

In some situations there may occur a similar double-encoding problem with mysql console output. For example, Time Tracker application may work just fine but when you select records in mysql console, some characters become double-encoded and printed as garbage. "Straße" prints normally in Time Tracker pages but as "Straße" in mysql console output. A solution in this case is one of the following:

1) Adjust server settings so that its character_set variables are utf8mb4 and not latin1. You can examine these variables with a command like this:
mysql> show variables where variable_name like 'character_set_%';
Change server config files, restart it, and make sure you see utf8mb4 in there. For MariaDB the following settings may work:
[mysqld]
skip-character-set-client-handshake
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4

2) Try using latin1 character set for mysql client. Either start mysql console as:
mysql --default-character-set=latin1
or put the following in .my.cnf file.
[client]
default-character-set=latin1

Time Tracker FAQ