1

I am facing problems with inserting Spanish characters from my application.

When I am inserting on the DB the characters are inserting properly, but when I am trying passing the same data in from the application, the data stored in the table isn't correct.

For example, when I insert directly on DB server:

CREATE TABLE  prod_inns(`prod_name` varchar(100) COLLATE latin1_bin DEFAULT NULL)

insert into prod_inns values(Otoño);

I see this:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño       |
+-------------+

delete  FROM  prod_inns ;

However, when I insert the same value from the application form, I see this instead:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño      |
+-------------+

Here are my MySQL settings:

MySQL>  show global variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL>  show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

What's wrong with my settings? What should I do to store the data and read the data properly?

Note: I am using mysql-5.7.19

  • IN additional to the answers below - just checking, you're trying to get Spanish characters correct, and you're using latin1_swedish_ci as your default collation? Wanted to be sure that was deliberate - if so, fine. – RDFozz Oct 16 '17 at 18:03
  • yes i am trying to get spanish charecters and i am using latin1_swedish_ci collation . – Jock helson Oct 18 '17 at 7:49
0

Don't use latin1, use utf8. You have "Mojibake" because the bytes in the client are utf8-encoded, yet you said they were latin1.

More on Mojibake and what to do about it: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

|improve this answer|||||
  • I tried your sugesstion. I have altered the table column charecter set as utf8 but still there is no change in result . alter table prod_inns modify prod_name varchar(100) CHARSET utf8; result: select * from prod_inns ; Otoño – Jock helson Oct 16 '17 at 16:56
  • Yes, but it was inserted erroneously to begin with. Try doing the insert again! p.s. use backticks (`) to highlight actual code in comments! – Vérace Oct 16 '17 at 17:00
  • The 'correct' fix was to do a pair of ALTERs, hopping through VARBINARY. See here. (I don't want to think about how to fix it now that you have compounded the problems. – Rick James Oct 16 '17 at 17:04
  • i tried with backtick while inserting throwing ERROR 1054 (42S22): Unknown column 'Otoño' in 'field list' Tried with varbinary as well still same problem persisting. – Jock helson Oct 16 '17 at 17:10
  • @Jockhelson - you missed Vérace's point -- backtics are a formatting convention to get This Look instead of This Look in Comments (and in Q&A). – Rick James Oct 16 '17 at 17:24
-1

You need to alter your database character set and collation as per the instructions here:

i.e.

ALTER  DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name

in your case, it should be

ALTER DATABASE my_database
CHARACTER SET utf8
COLLATE spanish_unicode_ci

(or spanish2 if you want Traditional - as per here)

|improve this answer|||||
  • Those are only defaults for new tables. So these ALTERs will not have any effect. – Rick James Oct 16 '17 at 17:02
  • mysql> ALTER DATABASE ecomm CHARACTER SET utf8 COLLATE spanish_unicode_ci; ERROR 1273 (HY000): Unknown collation: 'spanish_unicode_ci' I recently upgraded mysql to 5.5 to 5.7 , it worked properly in my old mysql version. – Jock helson Oct 16 '17 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.