1

I observe a strange situation in Windows 10 with XAMPP Control Panel v3.2.4

Terminal window is launched and connected to Mysql DB.

Note: prior in terminal window issued command 'chcp 65001' to support UTF8 encoding.

Now when I attempt to update some table with value which is in Cyrillic then MySQL complains about not closed quote symbol. If I replace Cyrillic input to English then command is accepted.

MariaDB [youtube]> update episodes set name='Катя' where id=11;
    '>

If I attempt to insert a new record into DB same situation happens

MariaDB [youtube]> insert into episodes (youtube_id,series_id,season,episode,name) values (12345678904,1,0,1,'Катя');
    '>

If double quotes are used situation is the same

MariaDB [youtube]> insert into episodes (youtube_id,series_id,season,episode,title) values (12345678904,1,0,1,"Катя");
    ">

What a magic touch required to make it work through terminal window?

Update:

John suggested to look into configuration file of MariaDB for UTF8 settings.

The settings was changed to the following and the problem still persists

# The MySQL server
default-character-set=utf8mb4

[mysqld]
init-connect=\'SET NAMES utf8\'
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake
character_sets-dir="C:/bin/XAMPP/App/xampp/mysql/share/charsets"

Initially settings was

# The MySQL server
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Server status report

MariaDB [youtube]> \s
--------------
mysql  Ver 15.1 Distrib 10.4.10-MariaDB, for Win64 (AMD64), source revision c24ec3cece6d8bf70dac7519b6fd397c464f7a82

Connection id:          17
Current database:       youtube
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.4.10-MariaDB mariadb.org binary distribution
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 11 min 12 sec

Threads: 7  Questions: 59  Slow queries: 0  Opens: 22  Flush tables: 1  Open tables: 16  Queries per second avg: 0.087
--------------

MariaDB Documentation has reference to an option --default-character-set=name.

An attempt to use --default-character-set=utf8mb4 on command line had no effect on behavior of insert/update record in terminal client.

mysql -u root -p --default-character-set=utf8mb4 youtube
....
MariaDB [youtube]> update episodes set title='Катя' where id=11;
    '>
Polar Bear
  • 6,762
  • 1
  • 5
  • 12
  • have you tried double quotes? set name="Катя" – nbk Jul 01 '20 at 00:54
  • @nbk -- well I did not state in my question but I did try to use double quotes with same outcome. Question was edited to reflect outcome with double quotes. – Polar Bear Jul 01 '20 at 00:57
  • Mariadb, not mysql. Mariadb is a for kof mysql that is not the sane as mysql anymore. – Shadow Jul 01 '20 at 01:07
  • I don't use mariadb only mysql and with a utf8mb4 table , the mysql comand has no problems what soever – nbk Jul 01 '20 at 01:07
  • *Always* put data (including numeric data) inside of single quotes (versus not using quotes) as one slip up (even with the escape command) can lead to an SQL injection vulnerability! Also, I tested your query on one of my databases and it worked fine, see my answer below. – John Jul 01 '20 at 01:59
  • @John -- I do put data in single quotes, but when it does not work I have to look that may be software expects double quotes (like in this case). I program perl and found that parameters in Linux should be wrapped into single quotes, but in MS Windows it expects arguments be wrapped into double quotes - otherwise it produces an error. In Windows (active code page 1252 North America) I can not pass Cyrillic arguments without quite complex work around. I found that in Windows programmer **has to jump over many hoops** to work with UTF8 -- it is possible, but more complicated than could be. – Polar Bear Jul 01 '20 at 17:05
  • Don't use Microsoft applications, Microsoft makes a mess of pretty much everything they touch (they have a very vile and negative corporate "culture") and basically bang on the war drums and eventually forget about what they were warring on about after a few months or years. What do you need programs for? There are tons of free *quality* alternatives out there that will make your life so much easier. :-) – John Jul 02 '20 at 04:23
  • Also! Always make sure you use quotes on **ALL input!!** Even with escaping data covering 100% of all queries it is possible to still do an SQL injection attack if you're not checking data types. So I highly recommend changing `WHERE id=1;` to `WHERE id='1';`. – John Jul 02 '20 at 04:31

1 Answers1

0

I highly recommend getting a copy of the freeware program HeidiSQL. It's not perfect and even crashes occasionally though compared to everything else I've worked with? Oh boy, totally worth my time.

Secondly you want to make sure that you're using the following:

Database Character Set: utf8mb4 Database Collation: utf8mb4_unicode_520_ci

These have the greatest UTF-8 support from what I've read from Stackoverflow member Rick James, via his website. He's a database superstar here on SO so if you ever hire him dump buckets of money on his face. His site has a comparison chart. In fact it's been a while and 520 might have been superseded since I last checked.

To set/change the Database Character Set you will need to change the my.cnf configuration file for MariaDB, I recommend using Notepad++ for code editing. This should make any newly created databases use the correct encoding however you may have to go through and manually update the character sets and collations for databases, tables and table columns so do not forget to be thorough!

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_520_ci

Once you do that your queries with Russian/Greek/etc should work normally as they do with English. Keyword: should.

Since I only speak two languages (English and bad English) I encode all characters past a certain Unicode numeric point just to be certain. It'll take up a bit more space however there are sometimes characters added for languages to Unicode after the majority of the language has been defined thus potentially fragmenting language support. If you're interested comment and I'll go find that code for you.

I'm at a moderate level of comprehension and I'm no Rick James though I have about two or three dozen translation pages (use the search feature and search for 'translation') on the site in my profile if you want to see the output. After I did these things I stopped having the data get corrupted. I hope this helps!

John
  • 1
  • 13
  • 98
  • 177
  • Thank you for your reply. The table which I work with has `CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci` applied at create time. I can store/edit data in Cyrillic through `phpMyAdmin` web interface of just fine. The problem is not with DB/Table itself but with terminal interface to the DB. I fire a command `mysql -p` and try insert/update a record and here the problem manifests itself. I may be need to look for command line option which might be responsible for **activating utf8** support on input. – Polar Bear Jul 01 '20 at 16:51
  • Thank you for the tip about [HeidiSQL](https://www.heidisql.com/) -- I heard about it before but never had a chance to install/use it. Most of my time I spend in Linux and prefer terminal window to GUI programs -- it is faster, save a lot of resources and gives more deep understanding **what is under the hood and how it works**. Very often Linux servers are not preinstalled with GUI and nowadays when Linux run in Virtual Environment (Linux containers - LXD, LXC, Docker, KVM, XEN) GUI is too excessive on resources. – Polar Bear Jul 01 '20 at 16:55
  • John - An attempt to change `my.ini` with **collation_server=utf8_unicode_520_ci** had not effect on terminal `mysql` client. It still does complain about not closed quote. I have _expanded_ my question with some additional details. – Polar Bear Jul 01 '20 at 17:45
  • You keep mentioning quotes, do you need to \" \' escape? I can try your query on the command line though I'm running Windows. Are you having trouble on specifically Windows or Linux or both? You mentioned the database is already UTF8, what about the table columns? I really recommend running a GUI because it will show you things that you were likely not aware of previously. I agree with resources however productivity is vastly more important. – John Jul 02 '20 at 04:29
  • In Linux on my web space sever this problem does not exist, it is specific to Windows (Win 10 Pro active code page 1252 North America). My understanding of the problem is that in Windows `mysql` terminal client does not understand how to handle UTF8 data (Cyrillic in my case) . I can understand that Win10 Pro active code page 1252 can handle only Latin1 arguments on command line, but software should handle STDIN properly internally. For some inexplicable reason `mysql` terminal client does not interpret input properly. Try it on your computer (although your ACP can be different). – Polar Bear Jul 02 '20 at 05:50
  • Hm, that might be what the Windows PowerShell is supposed to be for. I looked it up on Wiki and it mentions Unicode support. I never use it though the command line for Start ⇨ Run is `%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe`. – John Jul 02 '20 at 06:18
  • How about this? https://stackoverflow.com/questions/49476326/displaying-unicode-in-powershell#answer-49481797 – John Jul 02 '20 at 06:20