Last updated: June 7, 2023 at 4:30 PM
As shown in the featured image, on the core server (V1), after migration to the new server, when updating the article containing 4B code (utf8mb4) such as emoji, the following error is displayed and the article can not be updated.
更新に失敗しました。データベースの投稿を更新できませんでした。This phenomenon seems to be a failure that occurs due to inconsistencies in the coding system of the WordPress DB due to server relocation etc.
Therefore, I had no choice but to contact the core server support and ask them to repair it with the DB before migration. However, the expectations were in vain, and the results were disappointing.
The following is a WordPress DB table restored to MariaDB 10.6.
Restoring with core server support and restoring with the UpdraftPlus plugin had similar results.
When I checked with phpMyAdmin, the DB collation was a mixture of uft8mb3_general_ci and uft8mb4_general_ci, as shown below, and the coding scheme was not consistent.
In order to enable pictogram input supported by WordPress 4.2 or later, at least the character code and collation of tables such as wp_posts and wp-postmeta must be set to uft8mb4, so in the following SQL I've tried changing the character codeset and collation of the table.
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ciBut still, the result was NG.
So, after further trial and error, we were able to repair the DB safely by the following method!
You can now enter and update emojis. 🎊 😄
WordPress DB repair after server relocation
First, check the definition of "DB_CHARSET" in wp-config.php in the directory where WordPress is installed. If it is not defined as below, modify the line.
/** データベースのテーブルを作成する際のデータベースの文字セット */
define( 'DB_CHARSET', 'utf8mb4' );After confirming the above settings, follow the steps below to repair the DB.
1．「 Login to phpMyAdmin" ⇒ Select the database name you want to change
2. Select the "Operations" tab in the header ⇒ Look at the part where "Collation" is written.
3. Specify "utf8mb4_general_ci" in collation and click "Execute"
Here, we change the collation in the columns of all tables and perform character encoding at the same time.
When performing this process, please be sure to back up the DB in advance. Also, please do so at your own risk.
For reference, please also refer to the following articles.
2022.06.13 Update
phpMyAdmin disables SQL
On the core server (V1) of the new server, phpMyAdmin seems to have restrictions. For phpMyAdmin, please refer to the recent version of phpMyAdmin version 5.2.0 I've updated it.
When you log in to phpMyAdmin, you will see the following warnings:
phpMyAdmin 環境保管領域が完全に設定されていないため、いくつかの拡張機能が無効になっています。理由についてはこちらをご覧ください。
代わりにデータベースの操作タブを使って設定することもできます。This means that the phpMyAdmin environment storage is not set up, so the extension cannot be used. THIS SEEMS TO INVALIDATE SOME SQL THAT DEALS WITH DB AND TABLE OPERATIONS, SUCH AS CREATE DATABASE, DROP DATABASE, ALTER TABLE, AND SO ON.
The immediate cause is that the phpMyAdmin environment storage has disappeared with the migration from MySQL to another DB, i.e. MariaDB.
Actually, I can't say anything about this matter because my memory is not clear, but in fact, there may not have been an environmental storage area from the beginning. 😓
This problem seems to be solved by importing "create_tables.sql" from the directory "phpmyadmin /sql/" where phpMyAdmin is installed, creating the DB "mysql" of the environment storage, and editing "config.inc.php", but phpMyAdmin Reading this file in will result in the following error:
Apparently, in the environment of the new server, there is no access authority to create the DB of the environment storage area, so this method could not be supported. Basically, the above method seems to be the procedure to log in to phpMyAdmin in SU mode and process it.
However, if the account of the pma user is registered in advance by the server administrator, it seems that there are cases where you can create a DB of the environment storage area by creating an extension database from the compane, changing the extension database name described in "create_tables.sql" and "config.inc.php" to an appropriate name, overwriting the file and processing.
However, in the core server environment, this does not apply in either case, so on the core server (V1), we give up because SQL cannot be used in phpMyAdmin. (However, DB operations from operation tags are possible)
When logging in to phpMyAdmin, I also got the error "The configuration file now requires a secret passphrase", which I could deal with by opening "config.inc.php", setting a 32-byte private passphrase with any string in blowfish_secret, and modifying the file.
Regarding this matter, we made an inquiry to core server support as follows.
Posted Date:2022/06/13
Is it a specification that SQL cannot be used in phpMyAdmin on the core server (V1) after migration?
The background is described in the related article linked below, but after the migration of the core server (V1), some extensions are disabled because the environment storage area is not set in phpMyAdmin. Therefore, it is a situation where SQL can not be used at all.
In order to be able to use SQL, you need to create an environment storage area for phpMyAdmin. For that purpose, import "create_tables.sql" from the directory "phpmyadmin/sql/" where phpMyAdmin is installed, create the DB "mysql" of the environment storage, edit "config.inc.php" I think that it will solve the problem, but if you import "create_tables.sql", " You do not have permission." (Attached image)
Therefore, there is nothing we can do about it.Miura
Contact Details (573074) | Contact Form (value-domain.com)
Responses to this matter were received from core server support as follows:
Apparently, there is no support for phpMyAdmin's environment storage.
I don't think that the support person fully understands the meaning of my question, but if you understand the meaning of the technical department, DB creation can be done from the compane, so SQL query operations should be executed from the program.
In phpMyAdmin, restricting its use for SQL queries is probably the security policy of the core server.
As for the core server, since the support skills are low and they only play the role of messenger to the technical department, the answer to questions is slow, and sometimes only answers to meaningless templates are returned, so it is better to have people who do not expect support and can solve themselves as much as possible.
For the time being, there is no particular hindrance to the operation of my server, so this matter is now closed.
2022.06.16 Update
Since the previous response of the support person was ambiguous, when I asked the detailed question again, I received the following official answer from the person in charge of the core server.
In short, for DB operations that are not authorized, it seems to be operated from the core server's companic panel and from the operation tag of phpMyAdmin.
We will inform you about the inquiry.
Regarding Core Server V1, database operation authority is not granted to the database user name used by the customer as before and after the renewal.
Therefore, if "CREATE" and "ALTER" of PhpMyAdmin are "CREATE DATABASE" statements and "ALTER DATABASE" statements, it can not be executed.
