Mysql – Unable to enter characters in MariaDB

character-setdebianencodingmariadbMySQL

I have just switched from MySQL to MariaDB and am running into a very silly problem: I cannot enter any extended characters in the database (for example, ö ä or å).

The system is utf8, and I tried switching the keyboard to swedish — works everywhere except for mariadb prompt, and even tried copying and pasting from gucharmap — nothing is pasted at prompt.

Either method worked under MySQL in the past. I looked in MariaDB conf.d, the character_set_system was not set so I changed it to utf8, but that did not help. default-character-set in 50-client.cnf is set to utf8mb4. Any ideas?

System: debian bulseye/sid

Mariadb: Ver 15.1 Distrib 10.5.8-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

These are the relevant mariadb variables:

| 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/ |

| collation_connection | utf8mb4_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

| session_track_system_variables | 
autocommit,character_set_client,character_set_connection,character_set_results,time_zone

show create table gives

ENGINE=InnoDB AUTO_INCREMENT=1625 DEFAULT CHARSET=utf8mb4

The rest are field definitions, and no collations are set.

Of course, there is a prompt: it is a standard mysql prompt.

I am not sure how to make it clearer. I am at mysql prompt. In the past, typing something like å worked fine, now it shows nothing. When I try to paste the character copied from another utility like gucharmap, nothing is pasted. When I paste "normal" characters (i.e. standard English text) everything works.

Note that the extended characters in database entries created BEFORE the switch show up just fine. Yes, it is all very mysterious.

Best Answer

If you are using Windows cmd, you need chcp 65001 before entering mysql/mariadb. Since you are using a Linux variant, terminal may need SET NAMES utf8mb4; from within the mysql/mariadb.

If the columns are not utf8mb4, you will have other problems. Check a table by using SHOW CREATE TABLE tablename;

This provides a rundown on common problems and leads to their solutions: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

Related Solutions

Mysql – When are we supposed to set default-character-set for the client

You have the option of setting character sets/collations on the fly:

While trial-and-error may be necessary, don't go willy-nilly on these variables in /etc/my.cnf. You are better off setting them dynamically during any trial-and-error testing.

To make sure of any corner cases, look at the initial variables of any mysqldump and see if character sets or collations are set in the beginning and reset at the bottom.

In fact, here is a sample for a mysqldump:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

See the variables

@OLD_CHARACTER_SET_CLIENT
@OLD_CHARACTER_SET_RESULTS
@OLD_COLLATION_CONNECTION
SET NAMES is hardwired to utf8

You could perhaps set these options

when calling the mysqldump
place these options in /etc/my.cnf under [mysqldump] group section
edit these values for existing mysqldumps using perl, awk, etc.

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.