Questions tagged [character-set]
The character-set tag has no usage guidance.
145
questions
1
vote
1answer
19 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 ...
3
votes
2answers
280 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 ...
2
votes
1answer
35 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 ...
0
votes
1answer
182 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 ...
1
vote
1answer
85 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 ...
1
vote
1answer
29 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 ...
0
votes
1answer
30 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` ...
1
vote
1answer
97 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` (
`...
0
votes
1answer
21 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
1answer
48 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
2answers
351 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?
0
votes
1answer
650 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
2answers
385 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
2answers
147 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
1answer
328 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 ...
0
votes
1answer
61 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
1answer
301 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 ...
0
votes
1answer
14 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 ...
0
votes
1answer
93 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
1answer
454 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> ...
1
vote
2answers
917 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 --...
0
votes
0answers
96 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
1answer
138 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
0answers
46 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 ...
2
votes
2answers
55 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 ...
6
votes
1answer
3k 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, ...
2
votes
2answers
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 ...
1
vote
1answer
1k 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 ...
0
votes
2answers
344 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
1answer
2k 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 ...
5
votes
2answers
8k 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
1answer
498 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
0answers
31 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
1answer
182 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
1answer
206 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 ...
-1
votes
1answer
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 ...
1
vote
1answer
275 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 ...
2
votes
2answers
928 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.
...
0
votes
0answers
368 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 ...
2
votes
2answers
857 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 (...
16
votes
1answer
7k views
Error starting SQL Server 2017 service. Error Code 3417
I have SQL Server 2017 installed on my computer. This is what SELECT @@VERSION returns:
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) ...
1
vote
2answers
1k views
Wrong sort order using utf8_unicode_ci and swedish characters like ö
I am confused about what encoding and collation I should use so my queries returns the correct sort order.
Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and ...
1
vote
1answer
73 views
Convert Character Encoding, and Check Encoding for Issues
I'm planning to convert a table from utf8 (_general_ci) to utf8mb4 (utf8mb4_unicode_ci). I want to make sure I don't have any malformed data after the conversion though so I was planning to duplicate ...
0
votes
1answer
545 views
Apostrophe shows up as “ÔÇÖ” [closed]
I pulled data out of a database and the apostrophe is showing up as "ÔÇÖ". What character set is this from?
1
vote
2answers
569 views
MySQL does not read user specific options in ~/.my.cnf
I want the server to run an initial SQL query every time it starts:
[mysqld]
init-connect='SET NAMES utf8mb4'
and I want it to be only user specific not globally. so I created a .my.cnf in my user's ...
0
votes
1answer
449 views
Using character set en_us.364 in Informix database DB_LOCALE
I am trying to change my db_locale to en_us.364 which is an ASCII character set. It says on IBM's Knowledge Center that 364 is a valid character set:
https://www.ibm.com/support/knowledgecenter/en/...
1
vote
0answers
391 views
Converting and/or enforcing an Ascii Character Set in Informix database from 819 or ISO Latin 1
Overview: In Informix is it possible to enforce ASCII for a database or instance.
Details: I am trying to enforce ASCII in an instance of informix for a while. This is to help have a smooth ...
1
vote
2answers
5k views
How to fix accented character substitutions on French text in MySQL db
I'm building a small database for a charity I volunteer with. It will track events in English and French. When I enter characters like an accented e (as in "journée") it appears in the database as (...
4
votes
1answer
1k views
Collate issues with wrong characters
Well, the problem is well known, but I'm looking for a smarter solution if there's one.
For some reason the system doesn't recognize some characters and I can't compare the columns
Here is an ...
18
votes
5answers
15k views
Why does the varchar datatype allow unicode values?
I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below.
Create table VarcharUnicodeCheck
(
col1 varchar(100)
)
insert into ...
