Questions tagged [character-set]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
52 views

Tables character-set migration without huge downtime- mysql

We're in the process of migrating to a new character set for several tables in our database. Some tables are currently using utf8/utf8mb3 character set, which is deprecated now and we want move on ...
1 vote
1 answer
40 views

How can I detect failure in CONVERT( tbl AS utf8)?

MySQL provides the CONVERT(`tbl` AS utf8) expression to transcode text from one charset to another. Sometimes that conversion cannot succeed, because the destination charset does not include ...
1 vote
1 answer
42 views

How can I detect double-encoded MySQL columns and rows, and validate the repair?

My database provider, bless their hearts, migrated our MySQL databases to another server recently, and introduced double-encoding of UTF-8 data via Latin1 into our text data. Strings like 'emdash—here'...
0 votes
2 answers
54 views

What is the definition of latin1 character set in MySQL 8.0 and MySQL 5.7?

I am trying to fix some corruption to character fields in my database after a migration. I suspect that part of the problem is that fields with UTF-8 data were interpreted with the MySQL latin1 ...
0 votes
1 answer
34 views

Valid charset check

I need your help. One of our integrations is failing as we only certain charset What i would like to do is run an adhoc query on the following column (FirstName) and flag any record which is not a ...
  • 133
1 vote
1 answer
115 views

Unknown charset: utf8mb4

Context: I send queries to my MariaDB server(Ver 15.1 Distrib 10.3.34-MariaDB) through a Python script and mysql.connector module. (I don't know which one it's among those 3 in my list: mysql-...
  • 43
0 votes
2 answers
132 views

How can you repair a MySQL 8.0 Installation after changing `character_set_system` from utfmb3 to utfmb4 fails?

NOTE: My issue was due to a syntax error in the my.cnf file, not anything to do with errors on converting to utf8mb4. I still do not have any idea how you do that. I posted an answer explaining what ...
4 votes
1 answer
2k views

Storing special characters (German, French, Spanish) in SQL Server result in weird strings

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße" But this also happens for other characters: "König-...
  • 233
0 votes
1 answer
533 views

How to fix unicode REPLACEMENT CHARACTER � in postgres database

Is there a way fix the � problem in postgres database: As you can see there are names with accents and are not displayed corretly in the database. I would like to know if there is a way to fix this ...
  • 185
2 votes
2 answers
157 views

Index Collision in UTF8 on MariaDB

I am having a problem moving (converting) a DB from SQLite3 to MariaDB 10.3.29 server running on a Synology NAS. One table, actor, has 3 columns actor_id name art_urls Some actors have 2 entries in ...
1 vote
1 answer
63 views

Character set mismatch in database migration

I have an old database (MySQL 5.1.58) that I want to migrate to a new installation (MySQL 8.0.26). When I just use oldserver ~$ mysqldump mydatabase > file.sql newserver ~$ mysql mydatabase < ...
0 votes
0 answers
601 views

Set 'WE8ISO8859P15' character set for existing Oracle 19c DB while migrating 12c DB

I have a database in 12c (12.2.0.1) which I need to migrate to an existing live oracle 19c DB.(This DB already hosts a couple of live DBs’s) Character set of DB’s are as below: 12c -> ...
  • 1
1 vote
2 answers
197 views

Determine Ideal Collation Set for correct data storage

Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following: My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
  • 149
0 votes
0 answers
79 views

MySQL character set CLI and Workbench show different results

Q: Character sets shown by the MySQL CLI command lines and my MySQL workbench show different results. Why is that? I've run into MySQL 'chcp' error and cannot view my server status after updating my ...
0 votes
0 answers
786 views

Convert VARCHAR2 to UTF-8 in Oracle 12

I need to convert VARCHAR2 columns that store text in unkown character sets into a standard UTF-8 BLOB. The table has more columns, but the ones of interest to me are: ADDRESS POST ---------- ----...
1 vote
1 answer
375 views

MySQL query show something like 0x8081 instead of special characters

When i run SELECT CHAR(128,129,130,131,132,133,134,135,136,137); I got 0x80818283848586878889 istead of Çüéâäàåçêë. Does anybody know why?, I'm using charset utf8mb4. When I run show variables like '%...
  • 11
1 vote
1 answer
714 views

What is the impact of converting latin1/latin1_swedish_ci to utf8mb4/utf8mb4_unicode_ci?

I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is if this conversion is safe? Or does ...
  • 111
4 votes
2 answers
4k views

Best General Purpose Character Set and Collation for MySQL

Currently, whenever I create a new MySQL database, I use utf8mb4 as a character set and utf8mb4_unicode_520_ci for the collation, e.g.: CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET ...
  • 173
2 votes
1 answer
108 views

Unable to enter characters in MariaDB

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 ...
  • 21
0 votes
1 answer
4k views

Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?

I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance. mysql -u username -p test < DB-210508.sql The import starts but fails with the ...
  • 283
1 vote
1 answer
541 views

MySQL illegal mix of collations, ASCII to UTF-8

I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead: CREATE TABLE `things` ( `id` bigint unsigned NOT NULL ...
  • 251
1 vote
1 answer
38 views

Is CHARSET important for tables with INT only?

My server default CHARSET is UTF-8 (set in my.cnf). I create tables like CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, Title varchar(255), PRIMARY KEY(ID) ) ENGINE=InnoDB DEFAULT ...
  • 4,301
0 votes
1 answer
163 views

Error: Unknown character set: 'ascii'

When trying to import a database from a .mysql file, I get an error: ERROR 1115 (42000) at line 24: Unknown character set: 'ascii' The part the error occurs at is from columns using ASCII: `column` ...
  • 103
1 vote
1 answer
749 views

mysqldump dumps different data with and without --no-create-info

mysqldump dumps different representations of data when called with/without --no-create-info. Test case First, create the test table and populate it with interesting data. CREATE TABLE `test` ( `...
  • 193
0 votes
1 answer
27 views

MySQL - can't see end of

I am trying to pick the MySQL charset combination which is correct. Unfortunately, at least on my screen, the list of options is very narrow and doesn't show the full text of the options. I want to ...
0 votes
1 answer
458 views

Default value for CHARSET when creating a table

I came across this query in a book i'm learning from. CREATE TABLE `articles` ( `title` varchar(1000), `content` text, `links` text, `image` varchar(500), `url` ...
4 votes
2 answers
690 views

Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
  • 177
4 votes
1 answer
2k views

MySQL - Joining table with different charset in a view

I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci. When i do a join on both tables, it works. But, ...
3 votes
2 answers
945 views

How to convert xml entities to unicode characters (with read-only access) in Oracle?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access. Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain ...
1 vote
2 answers
423 views

Changing from utf8 to utf8mb4

MySql database at the moment has: Character Set: utf8 Default Collation Name: utf8_unicode_ci I want to change it to: utf8mb4 utf8mb4_unicode_ci I would also do the same thing for all tables in ...
0 votes
1 answer
827 views

Oracle 19c Installer Can't Select National Character Set

I am trying to install a new 19c instance of Oracle database in order to migrate a 12c database there. The 12c database uses a database character set of WE8ISO8859P1 and a national character set of ...
  • 321
0 votes
1 answer
355 views

Confusion about charset for connected tables from MS Access

I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am simply glad that it works (!). But going deeper and working more with ...
0 votes
1 answer
678 views

Can I use impdp FULL=Y for an Oracle database charset conversion?

I need create a copy of an Oracle 12.2 instance (Oracle Restart) running on an Linux VM. The new instance needs a charset conversion from WE8ISO8859P1 to UTF8 I started by creating a clone of the VM ...
  • 195
0 votes
1 answer
32 views

Mysql utf-8 behaves differently into different environments

I have MySQL server under docker in 2 different environments: This is my create database: CREATE DATABASE guests CHARACTER SET utf8mb4 COLLATE UTF8MB4_UNICODE_CI; This is the create table: USE ...
  • 85
0 votes
1 answer
372 views

converting latin to utf8mb4 causes questionmarks

The original format of the data is unknown The new table is in utf8mb4_general_ci If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8) as mentioned here - it fixes all text, but converts ...
0 votes
1 answer
929 views

Question marks instead Hebrew characters after mysql dump and import

I tried to migrate my AWS RDS instance from Aurora to MySQL. I created a dump file from the Aurora and imported it into the MySQL instance. Both instances have the same characters set. mysql> ...
  • 333
1 vote
2 answers
2k views

How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;

I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line: mysqldump --add-drop-table --add-locks --allow-keywords --...
  • 113
0 votes
0 answers
356 views

Will MariaDB treat ut8/utf8mb3 and utf8mb4 collations as compatible?

Background on relevant character sets MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on ...
-1 votes
1 answer
181 views

ORACLE DMU (database migration assistant for unicode) Tool

Reading the documentation for Oracle DMU tool, when I try the step create a step for DMU Repository tablespace the document says to use the result of the query for initial size. However; I could not ...
0 votes
0 answers
210 views

ALTER TABLE query to convert table to utf8mb4_bin takes many hours - how to optimise this?

I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion). The current table collation is latin1_swedish_ci but for full emoji ...
  • 111
2 votes
2 answers
143 views

charindex thrown off by extended characters

I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) ) This works ...
7 votes
1 answer
8k views

Query to find rows containing ASCII characters in a given range

I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios. I would have asked my question on the original How to check for Non-Ascii Characters post, ...
  • 73
2 votes
2 answers
3k views

How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?

I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
1 vote
1 answer
3k views

Is there a MySQL character set and encoding that will allow for both emojis and accents?

I've got a database of terms that get added to by one group of users, and queried against by another. I was running into problems when people would query for an emoji in the database and my React app ...
  • 113
0 votes
2 answers
707 views

Which characters are considered "word" characters

Fulltext searchin with InnoDB in MySQL only includes certain characters referred to in the docs as "word characters" and mentioned here: For the built-in full-text parser, you can change the set of ...
0 votes
1 answer
4k views

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

Full error Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file InvalidArgumentException: There was a problem connecting to ...
  • 117
7 votes
2 answers
13k views

Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

When creating a database without specifying a character set or collation the servers defaults are used (as expected). MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ; +----------------------+----...
0 votes
1 answer
612 views

Postgres special character rendering

I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table. This field contains a lot of garbage characters (higher than Ascii 128) which Postgres ...
1 vote
0 answers
73 views

Unable to change character_set_server on MySQL

I have converted my database to UTF8 but the character_set_server is still Latin1. Is it compulsory to change it to UTF8 or can I just leave it like that? This is a production server, so it's not ...
0 votes
1 answer
332 views

MySQL: columns with mixed character encodings, and finding columns which have multibyte data

I have some rather large CSV files that I am loading into my MySQL 5.7 database. The files are several gigabytes in size, several million lines long, and have large column widths that must be used in ...