# 1273-알 수없는 데이터 정렬 : 'utf8mb4_unicode_ci'cPanel
로컬 컴퓨터에 cPanel의 호스팅 phpMyAdmin으로 전송하려는 WordPress 데이터베이스가 있습니다. 그러나 데이터베이스를 환경으로 가져 오려고하면이 오류가 계속 발생합니다.
#1273 - Unknown collation: 'utf8mb4_unicode_ci'
나는 주변에 구글을 시도하고 내가 찾을 수있는 유일한 해결책은이 하나의 phpmysql 오류-# 1273-# 1273-알 수없는 데이터 정렬 : 'utf8mb4_general_ci' . 쿠키를 지우려고했지만 여전히 작동하지 않습니다. 도와주세요!
모든 서버에서 이전 버전의 MySQL을 실행하는 것과 동일한 문제가있었습니다. 이것은 PHP 스크립트를 실행하여 해결할 수 있습니다. 파일에이 코드를 저장하고 데이터베이스 이름, 사용자 및 암호를 입력하여 실행하며에서 정렬을 변경할 수 있습니다 utf8mb4/utf8mb4_unicode_ci에utf8/utf8_general_ci
<!DOCTYPE html>
<html>
<head>
<title>DB-Convert</title>
<style>
body { font-family:"Courier New", Courier, monospace; }
</style>
</head>
<body>
<h1>Convert your Database to utf8_general_ci!</h1>
<form action="db-convert.php" method="post">
dbname: <input type="text" name="dbname"><br>
dbuser: <input type="text" name="dbuser"><br>
dbpass: <input type="text" name="dbpassword"><br>
<input type="submit">
</form>
</body>
</html>
<?php
if ($_POST) {
$dbname = $_POST['dbname'];
$dbuser = $_POST['dbuser'];
$dbpassword = $_POST['dbpassword'];
$con = mysql_connect('localhost',$dbuser,$dbpassword);
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db($dbname);
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
}}
echo "<script>alert('The collation of your database has been successfully changed!');</script>";
}
?>
1) 데이터베이스의 "내보내기"탭을 클릭하십시오
2) "사용자 정의"라디오 버튼을 클릭하십시오
3) "형식 별 옵션"섹션으로 이동하여 "다음과의 출력 호환성을 최대화하기 위해 데이터베이스 시스템 또는 이전 MySQL 서버 :"의 드롭 다운을 NONE에서 MYSQL40으로 변경하십시오.
4) 하단으로 스크롤하여 "GO"를 클릭하십시오.
이 작업을 수행하면 데이터가 손실되는지 확실하지 않지만 한 번 시도한 후에는 전혀 눈치 채지 못했습니다. 위의 포럼에서 응답 한 사람도 없었습니다.
편집 8/12 / 16- 이 방법으로 데이터베이스를 내 보내면 Black Studio TinyMCE Visual Editor 위젯에 저장된 데이터가 손실 되지만 확인을 위해 여러 테스트를 실행하지는 않았다고 생각합니다.
The best thing to do is export your database to .sql, open it on Notepad++ and go to "Search and Replace". Then you put "utf8mb4" on search and "utf8" on replace. It will replace the utf8mb4_unicode_ci to utf8_unicode_ci. Now you go to your PhpMyAdmin (destination) and set the DB collation to utf8_unicode_ci (Operations > Collation).
i use this in linux :
sed -i 's/utf8mb4/utf8/g' your_file.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' your_file.sql
then restore your_file.sql
mysql -uyourdbuser -pyourdbpasswd yourdb < your_file.sql
Wordpress 4.2 introduced support for "utf8mb4" character encoding for security reasons, but only MySQL 5.5.3 and greater support it. The way the installer (and updater) handles this is that it checks your MySQL version and your database will be upgraded to utfmb4 only if it's supported.
This sounds great in theory but the problem (as you've discovered) is when you are migrating databases from a MySQL server that supports utf8mb4 to one that doesn't. While the other way around should work, it's basically a one-way operation.
As pointed out by Evster you might have success using PHPMYAdmin's "Export" feature. Use "Export Method: Custom" and for the "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".
For a command line export using mysqldump. Have a look at the flag:
$ mysqldump --compatible=mysql4
Note: If there are any 4-byte characters in the database they will be corrupted.
Lastly, for anyone using the popular WP Migrate DB PRO plugin, a user in this Wordpress.org thread reports that the migration is always handled properly but I wasn't able to find anything official.
The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL
At this time, there doesn't appear to be a way to opt out of the database update. So if you are using a workflow where you are migrating a site from a server or localhost with MySQL > 5.5.3 to one that uses an older MySQL version you might be out of luck.
In my case it turns out my
new server was running MySQL 5.5,
old server was running MySQL 5.6.
So I got this error when trying to import the .sql file I'd exported from my old server.
MySQL 5.5 does not support utf8mb4_unicode_520_ci, but
MySQL 5.6 does.
Updating to MySQL 5.6 on the new server solved collation the error !
If you want to retain MySQL 5.5, you can:
- make a copy of your exported .sql file
- replace instances of utf8mb4unicode520_ci and utf8mb4_unicode_520_ci
...with utf8mb4_unicode_ci
- import your updated .sql file.
There is a line in wp-config.php:
define('DB_CHARSET', 'utf8mb4');
If you follow Markouver's / Evster's instructions, don't forget to change this line on production server to
define('DB_CHARSET', 'utf8');
in order to fix broken 4-byte characters
After the long time research i have found the solution for above:
Firstly you change the wp-config.php> Database DB_CHARSET default to "utf8"
Click the "Export" tab for the database
Click the "Custom" radio button
Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
Scroll to the bottom and click go
Then you are on.
Seems like your host does not provide a MySQL-version which is capable to run tables with utf8mb4 collation.
The WordPress tables were changed to utf8mb4 with Version 4.2 (released on April, 23rd 2015) to support Emojis, but you need MySQL 5.5.3 to use it. 5.5.3. is from March 2010, so it should normally be widely available. Cna you check if your hoster provides that version?
If not, and an upgrade is not possible, you might have to look out for another hoster to run the latest WordPress versions (and you should always do that for security reasons).
So I solved in this way, from MySQL 5.6 to MySQL 5.5:
$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql
$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql
(Optional) Create a .sql.gz file:
$ gzip database_name.sql
Explanation
$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql
As explained in this answer, this is just the equivalent of this options from phpMyAdmin: "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".
$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql
We needs this, to solve this issue:
ERROR 1064 (42000) at line 18: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 9
I also experienced this issue. Solution which worked for me was opening local database with Sequel Pro and update Encoding and Collation to utf8/utf8_bin for each table before importing.
The easiest way to do is export your database to .sql, open it on Notepad++ and "Search and Replace" the utf8mb4_unicode_ci to utf8_unicode_ci and also replace utf8mb4 to utf8. Also don't forget to change the database collation to utf8_unicode_ci (Operations > Collation).
open the sql file on Notepad++ and ctrl + H. Then you put "utf8mb4" on search and "utf8" on replace. The issue will be fixed then.
참고URL : https://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel
'Programing' 카테고리의 다른 글
| OS X에서 파일 인코딩을 어떻게 확인합니까? (0) | 2020.05.31 |
|---|---|
| 안드로이드 페이스 북 키 해시를 만드는 방법? (0) | 2020.05.31 |
| C ++ for 루프 전에는 본 적이 없다 (0) | 2020.05.31 |
| AM / PM을 사용하여 12 시간 형식으로 현재 시간 표시 (0) | 2020.05.31 |
| ID가 #### 인 프로세스가 Visual Studio Professional 2013 업데이트 3에서 실행되고 있지 않습니다. (0) | 2020.05.31 |