0

I've just installed an Amazon EC2 linux instance with MariaDB. When trying to input accented chars in the db command line (like â, é, etc), it doesn't allow me. I can't figure out what's wrong. The bash command line allows them with no problems.

UPDATE:

Maybe I haven't explained the issue well. As I am spanish, I am very used to write accented chars in every application. I can write them all kind of editors, including vim or nano. Bash allows them obviously. But when logged in the MariaDB (db command line ( MariaDB [adatabase]> )), I can't input any letter that is accented. This happens only in the MariaDB that I've installed in the Amazon EC2 instance, but not in another MariaDB database that I've installed (for testing purposes) in my own computer.

A new information for this issue is that with PHP (mysqli_connect function), I can insert or update rows with accented strings, without problem. So it's only the MariaDB command line that doesn't allow me to enter data with accented letters. Again: it's not that the letters don't appear in the database, but I can't even input them in the command line.

I've added:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

to the mariadb-server.cnf file, and

[client]
default-character-set=utf8mb4

to the client.cnf file, both of them in the /etc/my.cnf.d folder, but no solution.

Rafael Avila Coya
  • 1
  • 1

1 Answers1

0

  • Have you established that the connection is UTF-8? Or is Bash even using that?

  • Have you declared your column to be CHARACTER SET utf8mb4? Or what?

See "best practice" and diagnostics for various common failure cases here.

More

init-connect (in my.cnf) is ignored by user root. Be sure not to use root (or other SUPER user) for application work.

I don't know (and I suspect you don't know) what encoding bash, vim, nano, and other editors use. It could (should) be UTF-8. But it could be latin1 or any of all too many other encodings, most of which are quite happy to handle all the accented letters of Spanish and the rest of Western European languages.

If you can somehow get the hex for, say, ñ, we can dig deeper. If you get the single character, hex F1, then it is one of cp1250, cp1257, dec8, latin1, latin2, latin5, latin7. If you get hex C3B1 then it is utf8 or utf8mb4 (as named in MySQL) or UTF-8 (as named by everyone else).

Spanish Characters

chars:  áéíñóúü
        ÁÉÍÑÓÚÜ
        ¿¡€’“”«»–— 
latin1:  E1 E9 ED F1 F3 FA FC
         C1 C9 CD D1 D3 DA DC
         BF A1 80 92 93 94 AB BB 96 97
UTF-8:  C3A1 C3A9 C3AD C3B1 C3B3 C3BA C3BC
        C381 C389 C38D C391 C393 C39A C39C
        C2BF C2A1 E282AC E28099 E2809C E2809D C2AB C2BB E28093 E28094

CMD (in Windows)

The command "chcp" controls the "code page". chcp 65001 provides utf8, but it needs a special charset installed, too. See some code pages . To set the font in the console window: Right-click on the title of the window → Properties → Font → pick Lucida Console

$ mysql

https://stackoverflow.com/a/6788223/1766831 suggests (with an update to utf8mb4):

mysql --default-characterset=utf8mb4

and (or)

[mysql]
default-character-set = utf8mb4

Note that it is [mysql], not [mysqld].

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • @RafaelAvilaCoya - Still not enough info. See the addition to my Answer. – Rick James Jun 19 '18 at 20:41
  • Thank you, Rick. When in the bash, I type ñ with no problem. When getting inside the MariaDB command line, it doesn't type anything, as it happens with any accented letter. This is something wrong with MariaDB instance in Amazon AWS, not with bash of their system, it seems quite clear. Personally, it's not a big issue for me (at least not for the moment), as I've done a workaround with PHP to add data to the DB, but there are probably more people who have the same problem, and it would be nice to know what the cause is. I have no clue. – Rafael Avila Coya Jun 23 '18 at 15:56
  • @RafaelAvilaCoya - "getting inside the MariaDB command line" -- meaning the `-e` argument? Or the `mysql` commandline tool? And is Windows involved anywhere? – Rick James Jun 23 '18 at 16:07
  • Rick: With "the MariaDB command line" I mean this: `MariaDB [(adatabase)]>` I don't use windows since the 90's, but in any case I am talking about a EC2 Linux instance in Amazon AWS. – Rafael Avila Coya Jun 23 '18 at 16:36
  • @RafaelAvilaCoya - I updated my Answer again; perhaps this time I am on the same wavelength. – Rick James Jun 23 '18 at 19:02