problems while backing up address book

[brunt82]

I tried to backup calendar and addressbook of a Nextcloud installation:

• Debian 9
• MariaDB 10.1.37
• NC 13.0.8
• 3000+ users

Unfortunately the script cannot run because of an faulty address book (backup of calendars went successfully). It gets the following error message:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

After line 1283 ("db_query='"SELECT ${convert:-carddata} FROM ${table_cards}...") I added the following output-line: _output printf "Principal-Name='${principal[$i]}' SELECT '${convert:-carddata}' FROM '${table_cards}' WHERE addressbookid='${id[$i]}'"

and get the following output:

+ saving addressbook 37506df8-49ba-4b35-a08a-603a896b61ec-37506df8-49ba-4b35-a08a-603a896b61ec.vcf (from database)...Principal-Name='37506df8-49ba-4b35-a08a-603a896b61ec' SELECT 'carddata' FROM 'oc_cards' WHERE addressbookid=''ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

But I could not find the string "37506df8-49ba-4b35-a08a-603a896b61ec" in database (where does it come from?). Therefore I could not find out why it has no addressbook id..

Any help?

[BernieO]

Uhmmm - that is quite weird.

${principal} is the username to which the according addressbook belongs, taken from the column principaluri in table [PREFIX]addressbooks (principals/users/ is being cut from that variable in line 764).

${filename} is created from ${principal} (the username) and ${displayname} (in line 994-996), which is the displayname of the according addressbook, also taken from the table [PREFIX]addressbooks.

In your case displayname and the username part of principaluri from the table [PREFIX]addressbooks seem to be set to the same value 37506df8-49ba-4b35-a08a-603a896b61ec, which is a bit weird, but should not lead to any problem, if that particular addressbook would have an id - and that is the strange bit: ${id} is also taken from the table [PREFIX]addressbooks. In your case the addressbook seems to have no id. That is really weird, because id in that table is the primary key, which is being autoincremented and thus it should be there in any case.

Could you post the output of (anonymize private data):

mysql -u [MARIADB_USERNAME] -p [DATABASE_NAME] -e "SELECT id, principaluri, displayname FROM [PREFIX]addressbooks WHERE displayname='37506df8-49ba-4b35-a08a-603a896b61ec';"

EDIT: To make sure there is nothing wrong with the delimiter: could you also post the output of (which is exactly the command the script uses - again anonymize privat data):

mysql -u [MARIADB_USERNAME] -p [DATABASE_NAME] -sre "SELECT CONCAT_WS('|', id, principaluri, displayname, uri)  FROM [PREFIX]addressbooks WHERE displayname='37506df8-49ba-4b35-a08a-603a896b61ec' ORDER BY id;"

maybe you have to add host and port/socket where MariaDB is listening to the command...

[brunt82]

I found the problem: After some additional search I could found the string as "uri" (!) inside the addressbook table:

'id', 'principaluri', 'displayname', 'uri', 'description'
'2700', 'principals/users/kh07602', 'MK 01\n', '37506df8-49ba-4b35-a08a-603a896b61ec', NULL, '1'

And if you look at the display name of the address book you will see the "\n", which is probably interpreted by calcardbackup. I don't know how the user could entered this new line (maybe by any external CardDav client). Anyhow after removing it from the column, calcardbackup ran smoothly to the end.

[BernieO]

Glad to hear, that you found the problem. Thanks for digging a bit deeper!

Note to my future self: The new line character "\n" is not escaped in the mysql output resulting in bash breaking up the single table row in two different lines. This breaks the while read -r line; do loop in lines 753-773 (v0.8.5).

Although this is obviously caused by a faulty database entry, I experimented a bit, whether calcardbackup could be improved to handle such line breaks in the tables. For the mysql command line client calcardbackup uses (among options -s and -e) option -r|--raw, which disables character escaping. To circumvent the particular issue reported here, instead of using options -sre I tried to use -sse or -Bse (both lead to the same result), to enable escaping of special characters: the newline is then printed as \n in the output of mysql. This helps, if a field in oc_calendars or oc_addressbooks contains a line break like reported from @brunt82 above.

BUT: this 'solution' leads to big problems in exporting the BLOB calendardata in table oc_calendarobjects, because calcardbackup needs to be able to read these multiline calendardata entries line by line to be able to analyze them (e.g. strip VTIMEZONEs), which isn't possible if the newline characters are escaped!

[BernieO]

Actually, this could easily be fixed by changing the order of the fields in the SELECT statement for the database queries: commit https://github.com/BernieO/calcardbackup/commit/9e8f0dd0bef216833ac2041e299e35a27c5478be fixes this

Additionaly commit https://github.com/BernieO/calcardbackup/commit/42cef9920767be4a0a8cb61ac78d3e2da81e94eb enhances the fix, so that calcardbackup will not only ignore linebreaks, but also multiple lines in displayname of an calendars/addressbooks.

Yay!