gitea database dump doesn't escape LF character

[samangh]

• Gitea version (or commit ref): 1.8.0
• Git version: 2.21.0
• Operating system: Linux (Debian Buster)
• Database (use [x]):
  • MySQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Not relevant
• Log gist:

When exporting via gitea dump,the \n at then end of commit message title (in column content of table action) is not properly escaped.

I've checked that this happens when exporting both from MySQL and NoSQL databases. As an example,

$ ./gitea dump
$ unzip -j gitea-dump-1557579865.zip gitea-db.sql `pwd`/gitea-db.sql
$ grep "66fd8c3d224c1f358f57d242374563ad55543761" gitea-db.sql

INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (894, 1, 5, 1, 1, 0, false, 'master', true, '{"Len":1,"Commits":[{"Sha1":"66fd8c3d224c1f358f57d242374563ad55543761","Message":"test \\n \\r \\t\n","AuthorEmail":"[email protected]","AuthorName":"Saman Ghannadzadeh","CommitterEmail":"[email protected]","CommitterName":"Saman Ghannadzadeh","Timestamp":"2019-05-11T13:31:40+01:00"}],"CompareURL":"saman/dotfiles/compare/5f81ccf3c05313658d4a9edd817526046e6a8cef...66fd8c3d224c1f358f57d242374563ad55543761"}', 1557577915);

Notice the Message":"test \\n \\r \\t\n". The special characters are escpaed, except the last \n.

This can cause mashalling errors when the dump is later imported into a database, see #6793.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[PerfectSlayer]

Hello,

I encounter the same issue with 1.8.2 version after dumping (gitea dump) and re-importing data (mysql -uUSER -pPASSWD DATABASE < gitea-db.sql).

I think I will try to write a script to patch my backup to convert \n to \\n in Commits[].Message from content field for op_type = 5||16 in action database. Any device before implementing this?

Thanks!

[nodiscc]

I also encounter this problem when trying to restore a backup from Gitea 1.8.0 on a recent release. This workaround worked for me:

sed -i 's/\\n/\\\\n/g' gitea-dump-1581252886/gitea-db.sql
sed -i 's/\\r/\\\\e/g' gitea-dump-1581252886/gitea-db.sql

However considering the numerous issues I noted at #9100 (comment) you may want to remove the dump command altogether from gitea, and simply provide backup instructions (dump the database using standard tools, backup the dump and data directory)

[hefengxian]

For temp solution, we can use database dump tool, for example on MySQL

mysqldump -h192.168.1.xxx -uxxx -pxxx -P3306 --single-transaction --set-gtid-purged=OFF --column-statistics=0 --default-character-set=utf8mb4 --databases gitea > gitea.sql

then import to MySQL

mysql --default-character-set=utf8mb4 -uxxx -pxxx gitea < gitea.sql

[nodiscc]

I also use standard filesystem/database tools to manage gitea backups. It has been more reliable so far:

• https://github.com/nodiscc/xsrv/blob/master/roles/gitea/templates/etc_rsnapshot.d_gitea.conf.j2
• https://github.com/nodiscc/xsrv/blob/master/roles/postgresql/templates/etc_rsnapshot.d_postgresql.conf.j2
• https://github.com/nodiscc/xsrv/blob/master/roles/postgresql/templates/usr_local_bin_postgres-dump-all-databases.sh.j2

[zeripath]

It would be helpful to report which versions of Gitea have this problem and if they are fixed on the newer versions. (including 1.14 - where it should be fixed)