Solve the Problem of garbled Chinese characters After spark openfire uses the MySQL database

(SPARK) openfire is a very good IM server, and it is implemented in pure Java and has multiple platform versions. Its data storage can use a variety of databases, such as MySQL and Oracle.

In actual use, the most common problem encountered is the garbled Chinese characters after the MySQL database. This problem is very interesting, and we can see some internal mechanisms of openfire.

The actual problem is as follows: first start the openfire server, and then use the client or directly log on to the background to create an account, specifying some Chinese attributes for the account, such as the name. If you do not restart the server, you will never find anything wrong, because all Chinese characters are displayed normally. Next, restart openfire and log on to the client with the created account or go to the background management terminal to view the information. All Chinese characters are changed to question marks. Log on to the database and check that all Chinese characters are question marks. This illustrates two problems:

1. Openfire has application-layer Cache
2. Database Encoding Problems

The solution is actually very simple. First, make sure that the database code you created for openfire is utf8. The table creation statement is as follows:

Create DatabaseOpenfireDefault Character SetUtf8Default collateUtf8_general_ci

 

When you have created a database, you can use:

Alter DatabaseOpenfireDefault Character Set utf8 defaultCollateUtf8_general_ci;

 

Second, when initializing the openfire database, that is, when the openfire server is configured for the first time, the connection string connecting to the database must be in the character encoding format and the UTF-8 encoding requirements must be added to the connection, set the connection string as follows:

JDBC: mysql: // 127.0.0.1: 3306/openfire? Useunicode = true&Characterencoding = utf8

If the installation is complete, you can change the configuration. Go to the openfire installation directory and find CONF/openfire. open the following XML section and modify the serverurl.

<Database>
<Defaultprovider>
<Driver> com. MySQL. JDBC. Driver </driver>
<Serverurl> JDBC: mysql: // 127.0.0.1: 3306/openfire? Useunicode = true& Amp;Characterencoding = utf8 </serverurl>

Note: Because & has special meanings, the original & symbol must be escaped as & amp;