28149


データベース用にしていたPCが壊れてしまい、/var/lib/mysql を丸ごと別環境に移設しました。  
この時、生きているテーブルと、死んでいるテーブルがあり、死んでいる方は、次のエラーが出ていました。

[code]

    mysql> select * from wp_test_users;
    ERROR 1273 (HY000): Unknown collation '#224' in table 'wp_test_users' definition
    
[/code]

調べてみると、Collation 参照順序 のエラーであることは理解しました。

[code]

    ALTER TABLE "Database" CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
    
[/code]

等のコマンドも試しましたが、このコマンド自体も上記と同じエラーとなり、どうすれば良いか分かりません。  
ご教授願えませんでしょうか。よろしくお願いします。

[code]

    mysql> show character set;
    +----------+-----------------------------+---------------------+--------+
    | Charset  | Description                 | Default collation   | Maxlen |
    +----------+-----------------------------+---------------------+--------+
    | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
    | dec8     | DEC West European           | dec8_swedish_ci     |      1 |
    | cp850    | DOS West European           | cp850_general_ci    |      1 |
    | hp8      | HP West European            | hp8_english_ci      |      1 |
    | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
    | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
    | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
    | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
    | ascii    | US ASCII                    | ascii_general_ci    |      1 |
    | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
    | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
    | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
    | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
    | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
    | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
    | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
    | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
    | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
    | gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
    | latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
    | armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
    | utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
    | ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
    | cp866    | DOS Russian                 | cp866_general_ci    |      1 |
    | keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
    | macce    | Mac Central European        | macce_general_ci    |      1 |
    | macroman | Mac West European           | macroman_general_ci |      1 |
    | cp852    | DOS Central European        | cp852_general_ci    |      1 |
    | latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
    | cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
    | cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
    | cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
    | binary   | Binary pseudo charset       | binary              |      1 |
    | geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
    | cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
    | eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
    +----------+-----------------------------+---------------------+--------+
    
[/code]

-
[code]

    > mysql> SHOW COLLATION;
    +--------------------------+----------+-----+---------+----------+---------+
    | Collation                | Charset  | Id  | Default | Compiled | Sortlen |
    +--------------------------+----------+-----+---------+----------+---------+
    | big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
    | big5_bin                 | big5     |  84 |         | Yes      |       1 |
    | dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
    | dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
    | cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
    | cp850_bin                | cp850    |  80 |         | Yes      |       1 |
    | hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
    | hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
    | koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
    | koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
    | latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
    | latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
    | latin1_danish_ci         | latin1   |  15 |         | Yes      |       1 |
    | latin1_german2_ci        | latin1   |  31 |         | Yes      |       2 |
    | latin1_bin               | latin1   |  47 |         | Yes      |       1 |
    | latin1_general_ci        | latin1   |  48 |         | Yes      |       1 |
    | latin1_general_cs        | latin1   |  49 |         | Yes      |       1 |
    | latin1_spanish_ci        | latin1   |  94 |         | Yes      |       1 |
    | latin2_czech_cs          | latin2   |   2 |         | Yes      |       4 |
    | latin2_general_ci        | latin2   |   9 | Yes     | Yes      |       1 |
    | latin2_hungarian_ci      | latin2   |  21 |         | Yes      |       1 |
    | latin2_croatian_ci       | latin2   |  27 |         | Yes      |       1 |
    | latin2_bin               | latin2   |  77 |         | Yes      |       1 |
    | swe7_swedish_ci          | swe7     |  10 | Yes     | Yes      |       1 |
    | swe7_bin                 | swe7     |  82 |         | Yes      |       1 |
    | ascii_general_ci         | ascii    |  11 | Yes     | Yes      |       1 |
    | ascii_bin                | ascii    |  65 |         | Yes      |       1 |
    | ujis_japanese_ci         | ujis     |  12 | Yes     | Yes      |       1 |
    | ujis_bin                 | ujis     |  91 |         | Yes      |       1 |
    | sjis_japanese_ci         | sjis     |  13 | Yes     | Yes      |       1 |
    | sjis_bin                 | sjis     |  88 |         | Yes      |       1 |
    | hebrew_general_ci        | hebrew   |  16 | Yes     | Yes      |       1 |
    | hebrew_bin               | hebrew   |  71 |         | Yes      |       1 |
    | tis620_thai_ci           | tis620   |  18 | Yes     | Yes      |       4 |
    | tis620_bin               | tis620   |  89 |         | Yes      |       1 |
    | euckr_korean_ci          | euckr    |  19 | Yes     | Yes      |       1 |
    | euckr_bin                | euckr    |  85 |         | Yes      |       1 |
    | koi8u_general_ci         | koi8u    |  22 | Yes     | Yes      |       1 |
    | koi8u_bin                | koi8u    |  75 |         | Yes      |       1 |
    | gb2312_chinese_ci        | gb2312   |  24 | Yes     | Yes      |       1 |
    | gb2312_bin               | gb2312   |  86 |         | Yes      |       1 |
    | greek_general_ci         | greek    |  25 | Yes     | Yes      |       1 |
    | greek_bin                | greek    |  70 |         | Yes      |       1 |
    | cp1250_general_ci        | cp1250   |  26 | Yes     | Yes      |       1 |
    | cp1250_czech_cs          | cp1250   |  34 |         | Yes      |       2 |
    | cp1250_croatian_ci       | cp1250   |  44 |         | Yes      |       1 |
    | cp1250_bin               | cp1250   |  66 |         | Yes      |       1 |
    | cp1250_polish_ci         | cp1250   |  99 |         | Yes      |       1 |
    | gbk_chinese_ci           | gbk      |  28 | Yes     | Yes      |       1 |
    | gbk_bin                  | gbk      |  87 |         | Yes      |       1 |
    | latin5_turkish_ci        | latin5   |  30 | Yes     | Yes      |       1 |
    | latin5_bin               | latin5   |  78 |         | Yes      |       1 |
    | armscii8_general_ci      | armscii8 |  32 | Yes     | Yes      |       1 |
    | armscii8_bin             | armscii8 |  64 |         | Yes      |       1 |
    | utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 |
    | utf8_bin                 | utf8     |  83 |         | Yes      |       1 |
    | utf8_unicode_ci          | utf8     | 192 |         | Yes      |       8 |
    | utf8_icelandic_ci        | utf8     | 193 |         | Yes      |       8 |
    | utf8_latvian_ci          | utf8     | 194 |         | Yes      |       8 |
    | utf8_romanian_ci         | utf8     | 195 |         | Yes      |       8 |
    | utf8_slovenian_ci        | utf8     | 196 |         | Yes      |       8 |
    | utf8_polish_ci           | utf8     | 197 |         | Yes      |       8 |
    | utf8_estonian_ci         | utf8     | 198 |         | Yes      |       8 |
    | utf8_spanish_ci          | utf8     | 199 |         | Yes      |       8 |
    | utf8_swedish_ci          | utf8     | 200 |         | Yes      |       8 |
    | utf8_turkish_ci          | utf8     | 201 |         | Yes      |       8 |
    | utf8_czech_ci            | utf8     | 202 |         | Yes      |       8 |
    | utf8_danish_ci           | utf8     | 203 |         | Yes      |       8 |
    | utf8_lithuanian_ci       | utf8     | 204 |         | Yes      |       8 |
    | utf8_slovak_ci           | utf8     | 205 |         | Yes      |       8 |
    | utf8_spanish2_ci         | utf8     | 206 |         | Yes      |       8 |
    | utf8_roman_ci            | utf8     | 207 |         | Yes      |       8 |
    | utf8_persian_ci          | utf8     | 208 |         | Yes      |       8 |
    | utf8_esperanto_ci        | utf8     | 209 |         | Yes      |       8 |
    | utf8_hungarian_ci        | utf8     | 210 |         | Yes      |       8 |
    | utf8_general_mysql500_ci | utf8     | 223 |         | Yes      |       1 |
    | ucs2_general_ci          | ucs2     |  35 | Yes     | Yes      |       1 |
    | ucs2_bin                 | ucs2     |  90 |         | Yes      |       1 |
    | ucs2_unicode_ci          | ucs2     | 128 |         | Yes      |       8 |
    | ucs2_icelandic_ci        | ucs2     | 129 |         | Yes      |       8 |
    | ucs2_latvian_ci          | ucs2     | 130 |         | Yes      |       8 |
    | ucs2_romanian_ci         | ucs2     | 131 |         | Yes      |       8 |
    | ucs2_slovenian_ci        | ucs2     | 132 |         | Yes      |       8 |
    | ucs2_polish_ci           | ucs2     | 133 |         | Yes      |       8 |
    | ucs2_estonian_ci         | ucs2     | 134 |         | Yes      |       8 |
    | ucs2_spanish_ci          | ucs2     | 135 |         | Yes      |       8 |
    | ucs2_swedish_ci          | ucs2     | 136 |         | Yes      |       8 |
    | ucs2_turkish_ci          | ucs2     | 137 |         | Yes      |       8 |
    | ucs2_czech_ci            | ucs2     | 138 |         | Yes      |       8 |
    | ucs2_danish_ci           | ucs2     | 139 |         | Yes      |       8 |
    | ucs2_lithuanian_ci       | ucs2     | 140 |         | Yes      |       8 |
    | ucs2_slovak_ci           | ucs2     | 141 |         | Yes      |       8 |
    | ucs2_spanish2_ci         | ucs2     | 142 |         | Yes      |       8 |
    | ucs2_roman_ci            | ucs2     | 143 |         | Yes      |       8 |
    | ucs2_persian_ci          | ucs2     | 144 |         | Yes      |       8 |
    | ucs2_esperanto_ci        | ucs2     | 145 |         | Yes      |       8 |
    | ucs2_hungarian_ci        | ucs2     | 146 |         | Yes      |       8 |
    | ucs2_general_mysql500_ci | ucs2     | 159 |         | Yes      |       1 |
    | cp866_general_ci         | cp866    |  36 | Yes     | Yes      |       1 |
    | cp866_bin                | cp866    |  68 |         | Yes      |       1 |
    | keybcs2_general_ci       | keybcs2  |  37 | Yes     | Yes      |       1 |
    | keybcs2_bin              | keybcs2  |  73 |         | Yes      |       1 |
    | macce_general_ci         | macce    |  38 | Yes     | Yes      |       1 |
    | macce_bin                | macce    |  43 |         | Yes      |       1 |
    | macroman_general_ci      | macroman |  39 | Yes     | Yes      |       1 |
    | macroman_bin             | macroman |  53 |         | Yes      |       1 |
    | cp852_general_ci         | cp852    |  40 | Yes     | Yes      |       1 |
    | cp852_bin                | cp852    |  81 |         | Yes      |       1 |
    | latin7_estonian_cs       | latin7   |  20 |         | Yes      |       1 |
    | latin7_general_ci        | latin7   |  41 | Yes     | Yes      |       1 |
    | latin7_general_cs        | latin7   |  42 |         | Yes      |       1 |
    | latin7_bin               | latin7   |  79 |         | Yes      |       1 |
    | cp1251_bulgarian_ci      | cp1251   |  14 |         | Yes      |       1 |
    | cp1251_ukrainian_ci      | cp1251   |  23 |         | Yes      |       1 |
    | cp1251_bin               | cp1251   |  50 |         | Yes      |       1 |
    | cp1251_general_ci        | cp1251   |  51 | Yes     | Yes      |       1 |
    | cp1251_general_cs        | cp1251   |  52 |         | Yes      |       1 |
    | cp1256_general_ci        | cp1256   |  57 | Yes     | Yes      |       1 |
    | cp1256_bin               | cp1256   |  67 |         | Yes      |       1 |
    | cp1257_lithuanian_ci     | cp1257   |  29 |         | Yes      |       1 |
    | cp1257_bin               | cp1257   |  58 |         | Yes      |       1 |
    | cp1257_general_ci        | cp1257   |  59 | Yes     | Yes      |       1 |
    | binary                   | binary   |  63 | Yes     | Yes      |       1 |
    | geostd8_general_ci       | geostd8  |  92 | Yes     | Yes      |       1 |
    | geostd8_bin              | geostd8  |  93 |         | Yes      |       1 |
    | cp932_japanese_ci        | cp932    |  95 | Yes     | Yes      |       1 |
    | cp932_bin                | cp932    |  96 |         | Yes      |       1 |
    | eucjpms_japanese_ci      | eucjpms  |  97 | Yes     | Yes      |       1 |
    | eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
    +--------------------------+----------+-----+---------+----------+---------+
    
[/code]


CC BY-SA 3.0
2016-08-09T06:41:20.307
28148
2016-08-09T07:12:13.617
2016-08-09T07:12:11.367
8000
18423
question
mysql
MySQLにて、Collationエラーの対象が数字であり、解決方法が分かりません。


224 は utf8mb4_unicode_ci です。

おそらく utf8mb4 をサポートしていない古い MySQL の環境に移行したのではないでしょうか。

移行前の MySQL のバージョンと同じものを使用すれば解決すると思います。


CC BY-SA 3.0
2016-08-09T07:12:13.617
28149
2016-08-09T07:12:13.617
3249
28148
answer