2

The value cut off after that character

Why this happening?

create table tmp2(t1 varchar(100)); 

insert into tmp2 values('beforeafter'); 

mysql> select * from tmp2; 
+--------+ 
| t1 | 
+--------+ 
| before | 
+--------+ 
1 row in set (0.01 sec)

I ran followed commands and returned some useful information

mysql> SHOW FULL COLUMNS FROM tmp2;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| t1    | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

and this,

mysql> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "test" AND table_name = "tmp2" AND column_name = "t1";
+--------------------+
| character_set_name |
+--------------------+
| utf8               |
+--------------------+
1 row in set (0.00 sec)

Im testing this on ubuntu/mysql command line.

eerrzz
  • 274
  • 1
  • 4
  • 19
  • The most likely explanation for the behavior you observe is a *mismatch* in the characterset between the client application, the MySQL server, and/or the column. [joelonsoftware.com/articles/Unicode.html](http://joelonsoftware.com/articles/Unicode.html) and [kunststube.net/encoding/](http://kunststube.net/encoding). MySQL Reference Manual: [http://dev.mysql.com/doc/refman/5.6/en/charset.html](http://dev.mysql.com/doc/refman/5.6/en/charset.html). The fix might be as simple as `SET names 'utf8';` – spencer7593 Feb 03 '16 at 00:23
  • @spencer7593 SET names 'utf8'; didn't worked. – eerrzz Feb 03 '16 at 00:27
  • what is the characterset of the column? Since you didn't specify it, it's going to default that of the table. And since that wasn't specified either, it's going to default to the characterset of the database. And that setting may have been the default for the MySQL server. My guess is that the client is using UTF-8, and the string value contains a valid UTF-8 character which is *not* valid in the characterset of the column. But that's just a guess. – spencer7593 Feb 03 '16 at 00:36
  • @spencer7593 I updated question and get some more information about columns. I don't see any problem there. – eerrzz Feb 03 '16 at 00:40

1 Answers1

4

I found the solution here

I learnt some characters are not includes in utf8

There is a good article here

I needed to change column utf8 to utf8mb4 and it worked

alter table tmp2 modify t1 varchar(100) character set utf8mb4;

SET NAMES utf8mb4;

insert tmp2 values('beforeafter');
Community
  • 1
  • 1
eerrzz
  • 274
  • 1
  • 4
  • 19