1. In the development environment, executing the database migration command failed: 1071 Specified key was too long; max key length is 767 bytes, as shown in Figure 17
Plaintext
[root@45fdb670c7c4 /]# php /sobey/www/pcs-api/yii migrate --interactive=0
Yii Migration Tool (based on Yii v2.0.15.1)
Total 1 new migration to be applied:
m180620_105204_update_table_options_to_log
*** applying m180620_105204_update_table_options_to_log
> execute SQL: ALTER TABLE {{%user}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
The SQL being executed was: ALTER TABLE `pa_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci (/sobey/www/pcs-api/vendor/yiisoft/yii2/db/Schema.php:664)
#0 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `pa...')
#1 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('ALTER TABLE `pa...')
#2 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /sobey/www/pcs-api/console/migrations/m180620_105204_update_table_options_to_log.php(19): yii\db\Migration->execute('ALTER TABLE {{%...')
#4 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Migration.php(114): m180620_105204_update_table_options_to_log->safeUp()
#5 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(725): yii\db\Migration->up()
#6 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(199): yii\console\controllers\BaseMigrateController->migrateUp('m180620_105204_...')
#7 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#8 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('', Array)
#11 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('', Array)
#12 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate', Array)
#13 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migrate', Array)
#14 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#15 /sobey/www/pcs-api/yii(23): yii\base\Application->run()
#16 {main}
*** failed to apply m180620_105204_update_table_options_to_log (time: 0.008s)
0 from 1 migrations were applied.
Migration failed. The rest of the migrations are canceled.
2. When running the SQL in the development environment database, an error occurred: #1071 – Specified key was too long; max key length is 767 bytes, as shown in Figure 18
3. If you are using utf8mb4 and have a unique index on a varchar column longer than 191 characters, you need to enable innodb_large_prefix to allow larger columns in the index, because utf8mb4 requires more storage space than utf8 or latin1. If innodb_large_prefix is enabled (the default), the index key prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix length limit is 767 bytes for tables of any row format. The migration command executed successfully in the local environment because the local MySQL version is 5.7.19 (MySQL 5.7.7 enables innodb_large_prefix by default; innodb_large_prefix is deprecated in MySQL 5.7.7 and will be removed in future versions), whereas the development environment is 5.6.16. See the URL: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html , as shown in Figure 19
4. In the Alibaba Cloud RDS parameter configuration, enable Innodb_large_prefix, as shown in Figure 20
5. Running the SQL in the development environment database succeeded, as shown in Figure 21
SQL
ALTER DATABASE `pcs-api-dev` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci
ALTER TABLE pa_user ROW_FORMAT=DYNAMIC
ALTER TABLE pa_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
Note: Some databases may also need to run
SQL
set global innodb_file_format = BARRACUDA;
6. Edit the database migration file, \console\migrations\m180620_105204_update_table_options_to_log.php
PHP
<?php
use yii\db\Migration;
/**
* Class m180620_105204_update_table_options_to_log
*/
class m180620_105204_update_table_options_to_log extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$tableOptions = null;
if ($this->db->driverName === 'mysql') {
// http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
$tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
$this->execute('ALTER TABLE {{%user}} ROW_FORMAT=DYNAMIC');
$this->execute('ALTER TABLE {{%user}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
$this->execute('ALTER TABLE {{%log}} ROW_FORMAT=DYNAMIC');
$this->execute('ALTER TABLE {{%log}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
}
$this->addCommentOnTable('{{%user}}', '用户', $tableOptions);
$this->addCommentOnTable('{{%log}}', '日志', $tableOptions);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m180620_105204_update_table_options_to_log cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m180620_105204_update_table_options_to_log cannot be reverted.\n";
return false;
}
*/
}
7. Configure the request methods allowed for request logging, mainly to ignore GET request logs. Edit \api\config\params.php
PHP
<?php
return [
'adminEmail' => 'admin@example.com',
'requestLog' => [
'allowMethod' => ['POST', 'PUT', 'DELETE'], //请求日志允许记录的请求方法
],
];8. In Postman, GET http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2
JSON
{
"code": 10000,
"message": "获取日志列表成功",
"data": {
"items": [
{
"id": 21,
"level": 4,
"category": "api\\behaviors\\RequestLogBehavior::afterRequest",
"log_time": 1530000329.6175,
"prefix": "[app-api][/v1/config-column-users/1][8]",
"message": {
"url": "/v1/config-column-users/1",
"request_query_params": {
"id": "1",
"login_id": "e56db1b43546a110431ac38409ed8e9e",
"login_tid": "49117dc410c491af0de08f2948aecd8f"
},
"request_body_params": {
"users": [
{
"user_pic": "https://cmcconsole.chinamcloud.com/user_pic/1800000005_13281105967_1529552309.png",
"user_mobile": "13281105967",
"id": "8",
"user_nick": "13281105967",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"user_type": "1",
"update_time": "2018-06-26 15:37:52",
"add_time": "2018-04-26 10:05:28",
"login_name": "13281105967",
"user_token": "fb46626f0e71e423ca8ab4c750620a85",
"is_open": "1",
"user_email": "13281105967@chinamcloud.com"
},
{
"id": "299",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"login_name": "test11",
"user_token": "670d30e8d2d1f994fc0788d4ce95e0f3",
"user_nick": "test11",
"user_pic": "https://cmcconsole.chinamcloud.com/imgs/default_header.png",
"user_mobile": "",
"user_email": "",
"is_open": "1",
"add_time": "2018-05-15 18:47:43",
"update_time": "2018-05-18 15:08:11",
"user_type": "2"
}
]
},
"user_id": "8",
"$_SERVER": {
"HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
"HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
"HTTP_HOST": "api.pcs-api.localhost",
"REMOTE_ADDR": "127.0.0.1",
"REQUEST_URI": "/v1/config-column-users/1",
"REQUEST_METHOD": "PUT",
"CONTENT_TYPE": "application/x-www-form-urlencoded; charset=utf-8"
}
}
},
{
"id": 22,
"level": 4,
"category": "api\\behaviors\\RequestLogBehavior::afterRequest",
"log_time": 1530000396.3784,
"prefix": "[app-api][/v1/config-columns][8]",
"message": {
"url": "/v1/config-columns",
"request_query_params": {
"login_id": "e56db1b43546a110431ac38409ed8e9e",
"login_tid": "49117dc410c491af0de08f2948aecd8f"
},
"request_body_params": {
"status": "1",
"code": "wxbj",
"name": "无线北京"
},
"user_id": "8",
"$_SERVER": {
"HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
"HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
"HTTP_HOST": "api.pcs-api.localhost",
"REMOTE_ADDR": "127.0.0.1",
"REQUEST_URI": "/v1/config-columns",
"REQUEST_METHOD": "POST",
"CONTENT_TYPE": "application/x-www-form-urlencoded; charset=utf-8"
}
}
},
{
"id": 23,
"level": 4,
"category": "api\\behaviors\\RequestLogBehavior::afterRequest",
"log_time": 1530000418.5957,
"prefix": "[app-api][/v1/config-columns/3][8]",
"message": {
"url": "/v1/config-columns/3",
"request_query_params": {
"id": "3",
"login_id": "e56db1b43546a110431ac38409ed8e9e",
"login_tid": "49117dc410c491af0de08f2948aecd8f"
},
"request_body_params": {},
"user_id": "8",
"$_SERVER": {
"HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
"HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
"HTTP_HOST": "api.pcs-api.localhost",
"REMOTE_ADDR": "127.0.0.1",
"REQUEST_URI": "/v1/config-columns/3",
"REQUEST_METHOD": "DELETE",
"CONTENT_TYPE": "application/json; charset=utf-8"
}
}
}
],
"_links": {
"self": {
"href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2"
},
"first": {
"href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=1"
},
"prev": {
"href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=1"
}
},
"_meta": {
"totalCount": 23,
"pageCount": 2,
"currentPage": 2,
"perPage": 20
}
}
}