Questions tagged [character-set]
The character-set tag has no usage guidance.
161
questions
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 ...
- 161
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'...
- 161
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 ...
- 161
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 ...
- 115
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 < ...
- 205
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
---------- ----...
- 101
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 ...
- 123
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` ...
- 103
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, ...
- 157
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 ...
- 21
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 ...
- 23
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 ...
- 1
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 ...
- 123
-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 ...
- 35
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 ...
- 143
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 ...
- 21
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 ...
- 604
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
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 ...
- 101