페이지 트리
메타 데이터의 끝으로 건너뛰기
메타 데이터의 시작으로 이동

Users that hit into Confluence Does Not Start with 'Detected tables with non-default character encoding/collation' Message , may be wondering why, even after the server and table collation have been set correctly, that they are still getting the error in the KB. Reason may be that the following query still returns some rows:

SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<databasename>' AND collation_name != 'utf8_bin';
SELECT * FROM information_schema.TABLES WHERE table_schema = '<databasename>' AND table_collation != 'utf8_bin';

 

Where <databasename> is your Confluence database's name.

If the query above returns even 1 row, Confluence 5.2.5 and above will fail to startup completely

 

 

단계별 가이드

Before you proceed with the following, please take a backup of your Confluence database:

  1. Identify the tables highlighted in the SELECT query above
  2. Run this query in the Confluence Database

    SET foreign_key_checks = 0;
    alter table <tablename> convert to character set utf8 collate utf8_bin;

    Or, if the above fails this one:

     ALTER TABLE <tablename> CHARACTER SET utf8 COLLATE utf8_bin;

    If the diagnostics query returns more than 1 result, you can use below query to generate the alter table query for all the affected tables:

    select concat('alter table ',  table_name, ' convert to character set utf8 collate utf8_bin;') from information_schema.tables  where table_schema='<yourdatabasename>' and table_collation != 'utf8_bin' group by table_name;
  3. After running the query for all affected tables (alternatively, you can run this for all tables in the Confluence Database), verify that this query now returns 0 rows:

    SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<databasename>' AND collation_name != 'utf8_bin';

    Where <databasename> is your Confluence database's name.

     

  4. Run this query to verify that all tables are configured to use utf8_bin as its TABLE COLLATION

    SELECT * FROM information_schema.TABLES WHERE table_schema = '<database name>' AND table_collation != 'utf8_bin';

    Where <databasename> is your Confluence database's name.

     

  5. If the above query does not return 0 rows, take note of the tables name it returns and execute this query. 

    ALTER TABLE <Table Name> DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
  6. Go back to step 4 to ensure all table are converted.
  7. Revert MySQL foreign constraint checking by executing this SQL query. 

    SET foreign_key_checks = 1;

 

관련내용

“contentbylabel” 매크로 렌더링 오류

parameters should not be empty