MySQL Bugs: #32271: mysql client unicode output incorrectly padded

Bug #32271	mysql client unicode output incorrectly padded
Submitted:	12 Nov 2007 8:55	Modified:	28 Mar 2014 6:54
Reporter:	Justin Watt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.38, 5.0.50	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	client, Unicode

Description:
When using the mysql client to select data that includes unicode strings, the results will be incorrectly spaced/padded, presumably based on a disconnect between the multibyte string length and display. 

Not sure if this is a problem with the mysql client per se, or the libraries it uses to output text.

How to repeat:
mysql> create table test (test varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 | 
| Iñtërnâtiônàlizætiøn    | 
+--------------------------------+
2 rows in set (0.00 sec)

The CREATE TABLE in your example does not specify any character set,
did you change the default character set to UTF8 before creating
the table or are you using the defaut settings (latin1 with
latin1_swedish_ci collation)?

The results are the same when I set the column character set to utf8 or the default table character set to utf8:

mysql> create table test (test varchar(255) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 | 
| Iñtërnâtiônàlizætiøn    | 
+--------------------------------+

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (test varchar(255)) default character set utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 | 
| Iñtërnâtiônàlizætiøn    | 
+--------------------------------+

Thank you for the report.

Please provide output of \s command.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

Connection id:          1126
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
--------------

Thank you for the feedback.

This can be same as bug #30250 or bug #17939. Please check these reports and indicate accurate version of your operating system, because bug #30250 was repeatable only with specified OS.

I got the same results setting the client charset to UTF-8 in addition to the table charset:

mysql> set names 'utf8';
Query OK, 0 rows affected (0.08 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

Connection id:          1161
Current database:       [removed]
Current user:           [removed]
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 17 days 21 hours 5 min 3 sec

mysql> create table test (test varchar(255)) default character set utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 | 
| Iñtërnâtiônàlizætiøn    | 
+--------------------------------+

Current OS is Ubuntu 7.04 Gutsy Gibbon.

Sorry I meant Ubuntu 7.04 Feisty Fawn. I have not upgraded to Gusty Gibbon yet.

Please ignore my previous comment, I was using a Putty connection set to encode in latin-1. Here is a new log with it set to utf8, on the same server:

mysql> create table test (test varchar(255)) default character set utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 |
| Iñtërnâtiônàlizætiøn    |
+--------------------------------+
2 rows in set (0.00 sec)

mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (test varchar(255)) default character set utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 |
| Iñtërnâtiônàlizætiøn    |
+--------------------------------+
2 rows in set (0.00 sec)

Also tested under 5.0.50, same results.

Gary,

thank you for testing. Set as verified, because can be different from bug #30250

The problem is that when you send 'SET NAMES utf8', the client no longer agrees with the server about what character set the results are in, so it gets confused.

You can more safely change the charset within the client using 'charset utf8' or '\C utf8', or by using the --default-character-set switch.

Having the server communicate back to the client when the character set has been changed with SET NAMES is part of WL#4797 (currently private, unfortunately). That will resolve this problem.

I can't reproduce it on with MySQL 5.5.8 on Ubuntu 10.04.1 LTS.

mysql [localhost] {msandbox} (test) > set names 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > drop table test;
Query OK, 0 rows affected (0.07 sec)

mysql [localhost] {msandbox} (test) > create table test (test varchar(255)) default character set utf8;
Query OK, 0 rows affected (0.13 sec)

mysql [localhost] {msandbox} (test) > insert into test values ('012345678901234567890123456789');
Query OK, 1 row affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into test values ('Iñtërnâtiônàlizætiøn');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > select * from test;
+--------------------------------+
| test                           |
+--------------------------------+
| 012345678901234567890123456789 |
| Iñtërnâtiônàlizætiøn           |
+--------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) >

Isn't this fixed already?

The bug is not reproduced on 5.5.35 or later.

The issue raised by Jim Winstead is addressed in
WL#4797 and mysql->charset is updated properly.