Questions tagged [character-set]
The character-set tag has no usage guidance.
155
questions
0
votes
2
answers
27
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 ...
- 115
4
votes
1
answer
411
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-...
- 229
0
votes
1
answer
57
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 ...
- 175
2
votes
2
answers
36
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 ...
0
votes
1
answer
22
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 < ...
- 195
0
votes
0
answers
154
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
73
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. ...
- 147
0
votes
0
answers
17
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
355
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
---------- ----...
- 101
1
vote
1
answer
107
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
401
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
2k
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 ...
- 163
2
votes
1
answer
92
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
3k
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
299
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
37
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,179
0
votes
1
answer
74
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
454
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 ...
- 123
0
votes
1
answer
230
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` ...
- 103
4
votes
2
answers
535
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
3
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, ...
- 137
3
votes
2
answers
760
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
271
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 ...
- 21
0
votes
1
answer
673
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 ...
- 311
0
votes
1
answer
236
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 ...
- 23
0
votes
1
answer
465
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
24
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
201
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 ...
- 1
0
votes
1
answer
754
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> ...
- 381
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
298
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 ...
- 123
-1
votes
1
answer
171
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 ...
- 35
0
votes
0
answers
105
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
86
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 ...
- 143
7
votes
1
answer
6k
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
2k
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 ...
- 21
1
vote
1
answer
2k
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
579
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 ...
- 525
0
votes
1
answer
3k
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
6
votes
2
answers
11k
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
582
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
1
vote
0
answers
38
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
264
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 ...
1
vote
1
answer
286
views
Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal?
I have a table with a collation defined on a column:
CREATE TEMPORARY TABLE test_table (
utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
INSERT INTO test_table VALUES('ã');
According ...
- 327
-1
votes
1
answer
2k
views
Incorrect string value: '\xF0\x9F\xA4\x96<b...' for column 'body' at row 1 [closed]
I've set the column type to utf8mb4 and the collation to utf8mb4_bin.
The table defaults are also utf8mb4 and utf8mb4_bin.
When I use SequelPro to add the emoji "🤖" to the database manually, it ...
- 99
1
vote
1
answer
340
views
MySQL utf8mb4 Migration - Replication Considerations
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.
Our server is 5.7.22 and ...
- 113
2
votes
2
answers
1k
views
Using CONVERT TO CHARACTER SET for changing only collation?
I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations.
...
- 63
0
votes
0
answers
431
views
Resolving "there is unknown charset/collation names" warnings on views
I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the ...
- 368
2
votes
2
answers
980
views
How do I use 'N' in my prepared SELECT statement
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.
I'm trying to run the following query which searches the database for Chinese characters:
SELECT TOP (...
- 21