Dealing with spanish tilde in Golang

Question

So I'm trying to enter this string into a mysql database using a sql driver. I get this error -

Do?a Merced Elementary
panic: Error 1366: Incorrect string value: '\x96a Mer...' for column 'name' at row 1

I thought about excluding this entry, but haven't been able to do so. I've tried -

if !strings.ContainsAny(splitStr[2], "U+0303") {
if !strings.ContainsAny(splitStr[2], '\x96') {

but that has not worked.

It would be better to have mysql deal with this, but I'm not sure how.

Any suggestions?

EDIT

This is how I connect to my db

db, err := sql.Open("mysql", "psanker:123@/education_data")
err = db.Ping()

db.SetMaxOpenConns(0)
check(err)
if err != nil {
    fmt.Println("Failed to prepare connection to database")
    log.Fatal("Error:", err.Error())
}

This is where my issue comes

districtResult, err := db.Exec("INSERT INTO districts(name) VALUES(?)", strings.TrimSpace(splitStr[2]))
check(err)

Output of SHOW CREATE TABLE

---------------------------------+
| Table     | Create Table                                                                                                                                                                                                                   |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| districts | CREATE TABLE `districts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

I think the long-term best thing to do here is convert your input from MacRoman to UTF-8, because UTF-8 is a more widely used and more flexible encoding. You can call a program called `iconv` to do it, and there are library bindings to call iconv functionality from Go code. I'm not sure which is the best, but [`github.com/qiniu/iconv`](https://godoc.org/github.com/qiniu/iconv) came up on a search. Unfortunately I don't have time to come up with the full steps to use it on your input here. — twotwotwo, Sep 01 '15 at 05:51

score 0 · Answer 1 · answered Sep 01 '15 at 05:14

0

Running on an Apple? I assume that should have said Doña Merced Elementary? Yet, to get ñ from x96, you must have started with text in the "MacRoman" encoding.

The simple solution might be to do SET NAMES macroman right after connecting to MySQL.

answered Sep 01 '15 at 05:14

Rick James

135,179
13
127
222

I did that and it said 0 rows were affected – praks5432 Sep 01 '15 at 05:15
That establishes that macroman is the encoding on the _client_ side. So, perhaps the data is already stored wrong? Please run `SELECT col, HEX(col) FROM tbl WHERE ...` to see what is actually in the table. – Rick James Sep 01 '15 at 05:16
Find the "mysql commandline tool" or "phpmyadmin". Construct a `SELECT` statement similar to what I wrote, but reaching for Doña. Run it. Report back on what you see. – Rick James Sep 01 '15 at 05:26
Let's see the code by which you are connecting to the database. – Rick James Sep 01 '15 at 05:26
there is no Dona in the table - that's the first time I'm inserting, I'll post the go code used to connect and insert into the db. – praks5432 Sep 01 '15 at 05:28
There may be a row with just `Do` (truncated). Or perhaps the insert aborted without doing anything. Anyway, please provide `SHOW CREATE TABLE`. – Rick James Sep 01 '15 at 05:32

score 0 · Accepted Answer · answered Sep 01 '15 at 05:20

0

Please change collation to utf8_general_ci. it should work

answered Sep 01 '15 at 05:20

Paresh Gami

4,777
5
23
41

how do I do that? I already tried http://stackoverflow.com/questions/742205/how-to-change-the-default-collation-of-a-table, and it didn't work – praks5432 Sep 01 '15 at 05:22
Won't work because the 96 is encoded in "macroman", not utf8. – Rick James Sep 01 '15 at 05:24

Dealing with spanish tilde in Golang

2 Answers2