search

flashmob / go-guerrilla

Mini SMTP server written in golang
MIT License
2.79k stars 366 forks source link

subject column in mysql need be tinyblob #187

Open lord-alfred opened 5 years ago

lord-alfred commented 5 years ago

Need to change this column type in wiki for new users. With varchar type was getting errors like:

time="2019-08-30T03:39:13+03:00" level=error msg="There was a problem the insert" error="Error 1366: Incorrect string value: '\\xCC\\xE5\\xF4\\xE1\\xF7\\xE5...' for column 'subject' at row 1"
time="2019-08-30T03:39:13+03:00" level=error msg="doQuery error" error="Error 1366: Incorrect string value: '\\xCC\\xE5\\xF4\\xE1\\xF7\\xE5...' for column 'subject' at row 1"
time="2019-08-30T03:39:13+03:00" level=error msg="storage error"

After change subject column type to tinyblob:

`subject` tinyblob NOT NULL,

the error is gone :)

tinyblob has 255 symbols length, as is previous varchar(255): https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html

flashmob commented 5 years ago

Thanks for reporting this. This happens even after the column's charset was changed to utf8mb4? The only reason to choose VARCHAR was so that a query for the column using = / LIKE would be case-insensitive. As mentioned before, if MySQL is in strict mode off, you won't see these errors...

On Sun, 15 Sep 2019 at 17:10, Lord Alfred notifications@github.com wrote:

Need to change this column type in wiki https://github.com/flashmob/go-guerrilla/wiki/Configuration-example:-save-to-Redis-&-MySQL for new users. With varchar type was getting errors like:

time="2019-08-30T03:39:13+03:00" level=error msg="There was a problem the insert" error="Error 1366: Incorrect string value: '\xCC\xE5\xF4\xE1\xF7\xE5...' for column 'subject' at row 1" time="2019-08-30T03:39:13+03:00" level=error msg="doQuery error" error="Error 1366: Incorrect string value: '\xCC\xE5\xF4\xE1\xF7\xE5...' for column 'subject' at row 1" time="2019-08-30T03:39:13+03:00" level=error msg="storage error"

After change subject column type to tinyblob: ``subject tinyblob NOT NULL,

the error is gone :)

tinyblob has 255 symbols length, as is previous varchar: https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/flashmob/go-guerrilla/issues/187?email_source=notifications&email_token=AAE6MP7QIXB5EFF2TNHZHJLQJXNX3A5CNFSM4IWZPUHKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HLNKQMA, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE6MP64Z4HAKJWHYHAMJ6TQJXNX3ANCNFSM4IWZPUHA .

lord-alfred commented 5 years ago

Yes, there were errors even after changing the encoding to utf8mb4

flashmob commented 5 years ago

Strange. How about changing the connection charset, as mentioned by the following article? https://hackernoon.com/today-i-learned-storing-emoji-to-mysql-with-golang-204a093454b7

On Sun, 15 Sep 2019 at 17:38, Lord Alfred notifications@github.com wrote:

Yes, there were errors even after changing the encoding to utf8mb4

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/flashmob/go-guerrilla/issues/187?email_source=notifications&email_token=AAE6MPZM3L4AAXSZ54EJUCTQJXQ5ZA5CNFSM4IWZPUHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6XLEJI#issuecomment-531542565, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE6MP6KFZS7ANYCJL5EMXTQJXQ5ZANCNFSM4IWZPUHA .

lord-alfred commented 5 years ago

Connection string already as is in article and database of course use CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci too