Replace Newline Characters With Commas Using tr, Not sed

I recently had a 5.0.9 Zimbra mailbox with inconsistencies between metadata (MySQL DB) and blob store (/opt/zimbra/store/...) in which the store was missing blob data referenced in the database.

Once I parsed the zmblobchk report data to retrieve information for the mailbox I wanted to fix, I wanted an efficient way to remove the blob item references from the database.

All commands run as zimbra user.

A snip from zmblobchk's mb8blobcheck.rpt file:

* MailboxGroup=8, mailbox=8, item=397: file not found: 397-2020.msg (delete associated metadata)
* MailboxGroup=8, mailbox=8, item=398: file not found: 398-2021.msg (delete associated metadata)


Grab the item IDs using awk piped to sed:

awk -F"item=" '{ print $2 }' mb8blobcheck.out | sed -e 's/: file not.*//g' > mb8blobitems.out


Replace newline characters with commas using a tr and sed combo so the list can be used in a MySQL delete query:

cat mb8blobitems.out | tr '\n' ',' | sed -e 's/,$//g' > mb8blobitems.csv



cat mb8blobitems.csv
397,398


Use the .csv in a MySQL delete query to remove the bad entries:

mysql mboxgroup8 -e "DELETE FROM mail_item WHERE mailbox_id=8 AND id IN(`cat mb8blobitems.csv`)"


Thanks to http://funarg.nfshost.com/r2/notes/sed-return-comma.html for this tip.