Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-21 Thread Johan De Meersman 
Probably the dumbest suggestion yet, but have you tried "set names utf8" in the 
client?

On 20 March 2018 20:50:08 CET, Roger House  wrote:
>
>
>On 03/15/2018 02:06 PM, Roger House wrote:
>>
>>
>> On 03/15/2018 11:30 AM, shawn l.green wrote:
>>> Hi Roger,
>>>
>>> (please note, this is a bottom-post forum)
>>>
>>> On 3/13/2018 7:54 PM, Roger House wrote:
>>> >
>>> > On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>> >>
>>> >>
>>> >> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> >>> In all respects except one, the treatment of Unicode works just 
>>> fine.
>>> >>> I can write Unicode to database tables, read it, display it,
>etc.,
>>> >>> with no problems. The exception is mysql, the MySQL Command-Line
>>> >>> Tool. When I execute a SELECT statement to see rows in a table
>>> >>> containing the Venus and Mars Unicode characters, here is what I
>see
>>> >>> on the screen:
>>> >>>
>>> >>> || Venus | ♀ | | Mars | ♂ | |
>>> >>>
>>> >>> What I should see in the right column are the standard glyphs
>for
>>> >>> Venus and Mars.
>>> >>>
>>> >>> Any ideas about how to get the MySQL Command-Line Tool to
>display
>>> >>> Unicode properly?
>>> >> what operating system
>>> >> what terminal
>>> >>
>>> >> all recent Linux systems have UTF8 as default
>>> >>
>>> >
>>> >
 I am running Ubuntu MATE 16.04.  I have the problem also on Windows
>7
 and on Mac OS Version 10.11.6.  I do not think that the problem has
>to
 do with the operating system nor the terminal.  Everything about
>the
 Unicode text works fine in all tools such as editors, the cat
>command,
 etc.  It is only when I am running mysql and I issue a SELECT 
 command to
 see what is in a row.  Then the UTF-8 is not rendered properly. I
 believe the problem is with mysql.

 Roger

>>>
>>> If I presume that your terminal has a code page that is 
>>> utf8-compatible (you say that cat command renders the multibyte 
>>> characters just fine) then it could be your client-side mysql 
>>> settings that are rendering those multibyte characters into 
>>> individual glyphs based on their individual byte values.
>>>
>>> The next time you are in mysql and have a chance to look at some
>utf8 
>>> data, please collect and share these two reports:
>>>
>>> status
>>> SHOW GLOBAL VARIABLES like '%haracter%';
>>>
>>> (you can obfuscate any sensitive details like server names or
>addresses)
>>>
>>> Yours,
>> Here is the requested info:
>>
>> mysql> status
>> --
>> mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine 
>> wrapper
>>
>> Connection id:        5
>> Current database:    ephemeris
>> Current user:        root@localhost
>> SSL:            Not in use
>> Current pager:        stdout
>> Using outfile:        ''
>> Using delimiter:    ;
>> Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
>> Protocol version:    10
>> Connection:        Localhost via UNIX socket
>> Server characterset:    utf8mb4
>> Db characterset:    latin1
>> Client characterset:    utf8mb4
>> Conn.  characterset:    utf8mb4
>> UNIX socket:        /var/run/mysqld/mysqld.sock
>> Uptime:            6 hours 17 min 8 sec
>>
>> Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 
>> 1  Open tables: 41  Queries per second avg: 0.001
>> --
>>
>> mysql> show global variables like '%haracter%';
>> +--++
>> | Variable_name    | Value  |
>> +--++
>> | character_set_client | utf8mb4    |
>> | character_set_connection | utf8mb4    |
>> | character_set_database   | utf8mb4    |
>> | character_set_filesystem | binary |
>> | character_set_results    | utf8mb4    |
>> | character_set_server | utf8mb4    |
>> | character_set_system | utf8   |
>> | character_sets_dir   | /usr/share/mysql/charsets/ |
>> +--++
>> 8 rows in set (0.01 sec)
>>
>>
>Update
>
>I noticed that the status command shows
>
>     Db characterset:    latin1
>
>whereas all the other charactersets are utf8mb4.  So I looked around to
>see
>how to change the Db characterset, and came up with this:
>
>     ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE 
>utf8mb4_general_ci;
>
>After which, status shows
>
>     Db characterset:    utf8mb4
>
>So the database characterset has now been changed.  But
>
>     select * from planet_desc;
>
>still shows
>
>     Venus | ♀
>     Mars  | ♂
>
>I shut down the mysql service and restarted it, but this did not change
>anything.
>
>I'm beginning to think that the command line tool mysql does not pay
>any
>attention to character sets when the SELECT command displays on the
>terminal.
>
>Roger

-- 
Sent from my Android device with K-9 Mail. Please excuse my

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-20 Thread Roger House 



On 03/15/2018 02:06 PM, Roger House wrote:



On 03/15/2018 11:30 AM, shawn l.green wrote:

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just 
fine.

>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT 
command to

see what is in a row.  Then the UTF-8 is not rendered properly. I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is 
utf8-compatible (you say that cat command renders the multibyte 
characters just fine) then it could be your client-side mysql 
settings that are rendering those multibyte characters into 
individual glyphs based on their individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,

Here is the requested info:

mysql> status
--
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine 
wrapper


Connection id:        5
Current database:    ephemeris
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            6 hours 17 min 8 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 
1  Open tables: 41  Queries per second avg: 0.001

--

mysql> show global variables like '%haracter%';
+--++
| Variable_name    | Value  |
+--++
| character_set_client | utf8mb4    |
| character_set_connection | utf8mb4    |
| character_set_database   | utf8mb4    |
| character_set_filesystem | binary |
| character_set_results    | utf8mb4    |
| character_set_server | utf8mb4    |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.01 sec)



Update

I noticed that the status command shows

    Db characterset:    latin1

whereas all the other charactersets are utf8mb4.  So I looked around to see
how to change the Db characterset, and came up with this:

    ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE 
utf8mb4_general_ci;


After which, status shows

    Db characterset:    utf8mb4

So the database characterset has now been changed.  But

    select * from planet_desc;

still shows

    Venus | ♀
    Mars  | ♂

I shut down the mysql service and restarted it, but this did not change
anything.

I'm beginning to think that the command line tool mysql does not pay any
attention to character sets when the SELECT command displays on the
terminal.

Roger


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-15 Thread Roger House 



On 03/15/2018 11:30 AM, shawn l.green wrote:

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just fine.
>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT command to
see what is in a row.  Then the UTF-8 is not rendered properly. I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is 
utf8-compatible (you say that cat command renders the multibyte 
characters just fine) then it could be your client-side mysql settings 
that are rendering those multibyte characters into individual glyphs 
based on their individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,

Here is the requested info:

mysql> status
--
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Connection id:        5
Current database:    ephemeris
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            6 hours 17 min 8 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 1  
Open tables: 41  Queries per second avg: 0.001

--

mysql> show global variables like '%haracter%';
+--++
| Variable_name    | Value  |
+--++
| character_set_client | utf8mb4    |
| character_set_connection | utf8mb4    |
| character_set_database   | utf8mb4    |
| character_set_filesystem | binary |
| character_set_results    | utf8mb4    |
| character_set_server | utf8mb4    |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.01 sec)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-15 Thread shawn l.green 

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just fine.
>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT command to
see what is in a row.  Then the UTF-8 is not rendered properly.  I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is utf8-compatible 
(you say that cat command renders the multibyte characters just fine) 
then it could be your client-side mysql settings that are rendering 
those multibyte characters into individual glyphs based on their 
individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-13 Thread Roger House 
I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7 
and on Mac OS Version 10.11.6.  I do not think that the problem has to 
do with the operating system nor the terminal.  Everything about the 
Unicode text works fine in all tools such as editors, the cat command, 
etc.  It is only when I am running mysql and I issue a SELECT command to 
see what is in a row.  Then the UTF-8 is not rendered properly.  I 
believe the problem is with mysql.


Roger


On 03/13/2018 03:11 PM, Reindl Harald wrote:



Am 13.03.2018 um 22:59 schrieb Roger House:
In all respects except one, the treatment of Unicode works just fine. 
I can write Unicode to database tables, read it, display it, etc., 
with no problems. The exception is mysql, the MySQL Command-Line 
Tool. When I execute a SELECT statement to see rows in a table 
containing the Venus and Mars Unicode characters, here is what I see 
on the screen:


|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for 
Venus and Mars.


Any ideas about how to get the MySQL Command-Line Tool to display 
Unicode properly?

what operating system
what terminal

all recent Linux systems have UTF8 as default




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-13 Thread Reindl Harald 



Am 13.03.2018 um 22:59 schrieb Roger House:
In all respects except one, the treatment of Unicode works just fine. I 
can write Unicode to database tables, read it, display it, etc., with no 
problems. The exception is mysql, the MySQL Command-Line Tool. When I 
execute a SELECT statement to see rows in a table containing the Venus 
and Mars Unicode characters, here is what I see on the screen:


|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for Venus 
and Mars.


Any ideas about how to get the MySQL Command-Line Tool to display 
Unicode properly?

what operating system
what terminal

all recent Linux systems have UTF8 as default

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How to get the MySQL Command-Line Tool to display Unicode properly?

2018-03-13 Thread Roger House 
Five months ago I posted the query shown below on StackOverflow.  I got 
one reply which was not of much help.  So I am trying again, hoping a 
more MySQL-centric forum might be able to solve my problem.


Roger House


How to get the MySQL Command-Line Tool to display Unicode properly?

I use a Python program to write text containing Unicode characters to a 
MySQL database. As an example, two of the characters are


|u'\u2640' a symbol for Venus or female u'\u2642' a symbol for Mars or male |

I use utf8mb4 for virtually all character sets involved with MySQL. Here 
is an excerpt from /etc/mysql/my.cnf


|[client] default-character-set=utf8mb4 [mysql] 
default-character-set=utf8mb4 [mysqld] default-character-set=utf8mb4 
character-set-server =utf8mb4 character_set_system =utf8mb4 |


In addition, all tables are created with these parameters:

|ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

In all respects except one, the treatment of Unicode works just fine. I 
can write Unicode to database tables, read it, display it, etc., with no 
problems. The exception is mysql, the MySQL Command-Line Tool. When I 
execute a SELECT statement to see rows in a table containing the Venus 
and Mars Unicode characters, here is what I see on the screen:


|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for Venus 
and Mars.


Any ideas about how to get the MySQL Command-Line Tool to display 
Unicode properly?


Edit:

I have done a fair amount of research into the various MySQL system 
variables, etc., and I now realize that the my.cnf settings shown above 
have some serious issues. In fact, the server, mysqld, would not launch 
with the settings shown. To correct things, remove these from [mysqld]:


|default-character-set=utf8mb4 character-set-system=utf8mb4 |

I'm not sure that the [client] option does anything, but it doesn't seem 
to hurt.


In Python u'\u2640' represents a single Unicode character, namely "♀". 
This compiles down to three bytes containing the hex value E29980. I am 
having no problems at all encoding and decoding Unicode. The correct 
values are being stored in a MySQL table; they are correctly read from 
the table, and when displayed by a Python program they show up like this:


|♀ Venus ♂ Mars |

The program output can be redirected to a file, processed by a text 
editor, etc., and in all cases the correct Unicode symbol is displayed.


There is only one place where the correct Unicode symbol is not 
displayed, and that is when I am using the MySQL Command Line Tool. When 
I issue a SELECT statement on the table containing the Unicode symbols I 
get the junk shown above. This is not a Windows specific issue. I have 
exactly the same problem with the MySQL Command Line Tool when I run it 
on Windows, Mac OS X, and Ubuntu.

Re: How to get the MySQL Command-Line Tool to display Unicode properly

2017-10-19 Thread Hal.sz S.ndor 

2017/10/18 18:32 ... Roger House:

I get the same behavior with the MySQL Command Line Tool when I run it on
Windows, Mac OS X, and Ubuntu, so I'm pretty sure the problem has to do 
with mysql itself.


What do you know about the displays to which the client is writing?
In the case of "cmd" under Windows, see what others had to do for other 
programs:

https://stackoverflow.com/questions/388490/unicode-characters-in-windows-command-line-how

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How to get the MySQL Command-Line Tool to display Unicode properly

2017-10-18 Thread Roger House 
I use a Python program to write text containing Unicode characters to a 
MySQL

database.  As an example, two of the characters are

    u'\u2640' a symbol for Venus or female
    u'\u2642' a symbol for Mars or male

I use utf8mb4 for virtually all character sets involved with MySQL.  Here is
an excerpt from /etc/mysql/my.cnf

    [client]
    default-character-set=utf8mb4

    [mysql]
    default-character-set=utf8mb4

    [mysqld]
    character-set-server =utf8mb4

I'm not sure that the [client] option does anything, but it doesn't seem to
hurt.

In addition, all tables are created with these parameters:

    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

In all respects except one, the treatment of Unicode works just fine.  I can
write Unicode to database tables, read it, display it, etc., with no
problems.  The exception is mysql, the MySQL Command-Line Tool.  When I
execute a SELECT statement to see rows in a table containing the Venus and
Mars Unicode characters, here is what I see on the screen:

    | Venus | ♀  |
    | Mars  | ♂  |

What I want to see is this

    | Venus | ♀  |
    | Mars  | ♂  |

I get the same behavior with the MySQL Command Line Tool when I run it on
Windows, Mac OS X, and Ubuntu, so I'm pretty sure the problem has to do 
with

mysql itself.

Any ideas about how to get the MySQL Command-Line Tool to display Unicode
properly?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql