buxxi / znc-mysql Goto Github PK

As mentioned in #1, there's still an issue with some surrogates:

<*sql> Could not save SAY to database caused by: <class 'UnicodeEncodeError'> 'utf-8' codec can't encode character '\udcf3' in position 15: surrogates not allowed

and

<*sql> Could not save SAY to database caused by: <class 'UnicodeEncodeError'> 'utf-8' codec can't encode character '\udcd0' in position 0: surrogates not allowed

I know I know, emojis in IRC... but they happen.

I upgraded from Ubuntu 14.04 to 16.04 and now whenever someone posts something containing an emoji, *sql sends this:
Could not save SAY to database caused by: <class 'pymysql.err.InternalError'> (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x82' for column 'message' at row 1")

I did some research and found out that apparently the MySQL utf8 encoding can't store 4-byte strings and hence throws this error. Using utf8mb4 is said to be the solution, working from MySQL 5.5 upwards.

I was wondering why I didn't get this bug with the old OS and actually grepped for an emoji in my text log files, and then looked up a message containing an emoji in the SQL DB. Sure enough, the message was there, just without an emoji, as if it had been removed.

I think this is responsible for that: https://github.com/buxxi/znc-mysql/blob/master/sql.py#L112

I do wonder why this used to work before. Maybe this line replaced characters python considered invalid, and a newer python version (due to OS upgrade) is now compatible with emoji and passes them on to MySQL which then throws the error.

Currently, only nickname and host are logged, but username/ident is not. It would be useful to have that information too.

ZNC 1.7 recently became stable, and because I am using teward's ubuntu PPA, I suddenly had upgraded from 1.6.3 without really knowing.

Ever since, channel messages aren't being logged anymore. Actions are, and I also see joins, quits and other things. I tried to debug this, but I just couldn't find out what's wrong. I don't get any errors.

I noticed the most recent version doesn't accept --type=network anymore. Looking at the code, it looks like it's a global module now.

My znc has >10 users, and now all messages of all users are being logged, resulting in duplicate entries in the database when users share a channel. If this is intentional, the (znc) user responsible for the entry should have a column too. I like loading as network or user module better though. I only want to log my own stuff, having my other user's messages in the same DB is kind of privacy invading.