Based on yiisoft/yii2-app-advanced, create a new repository yii2-app-advanced on github, and create a new interface application (implement RESTful-style web service services. API), adjust the default character set to: utf8mb4, the adjustment of the interface response format, the empty array is automatically converted to empty objects, and the request log message is collected in the interface application (1 request corresponds to 1 log message) to the database, and the corresponding interface to implement the log function: log list (set data filter to enable filter processing), log details (5) (2)

by 永夜 · 2018/06/27

浏览量： 6

1. In the development environment, the execution of the database migration command fails: 1071 Specified key was too long; Max key length is 767 bytes, as shown in Figure 17

在开发环境中，执行数据库迁移命令失败：1071 Specified key was too long; max key length is 767 bytes

Figure 17

[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
&gt; execute sql: alter table {{%user}} convert to character set utf8mb4 collect utf8mb4_unicode_ci ...exception: sqlstate[42000]: Syntax Error or Access Violation: 1071 specific key was too long; max key length is 767 bytes
The SQL Being Executeed 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-&gt;convertException(object(pdoexception),ALTER TABLE `PA...)
#1 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/command.php(1075): yii\db\command-&gt;InternalExecute(ALTER TABLE `PA...)
#2 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/migration.php(219): yii\db\command-&gt;execute()
#3 /sobey/www/pcs-api/console/migrations/m180620_105204_update_table_options_to_log.php(19): yii\db\migration-&gt;execute(ALTER TABLE {{%...)
#4 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/migration.php(114): M180620_105204_update_table_options_to_log-&gt;safeup()
#5 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(725): yii\db\migration-&gt;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-&gt;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-&gt;runaction(, array)
#12 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/application.php(180): yii\base\module-&gt;RunAction(migrate, array)
#13 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/application.php(147): yii\console\application-&gt;RunAction(migrate, array)
#14 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/application.php(386): Yii\Console\Application-&gt;HandleRequest(Object(Yii\Console\Request))
#15 /sobey/www/pcs-api/yii(23): yii\base\application-&gt;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. Run sql in the database in the development environment, and report an error: #1071 – specific key was too long; max key length is 767 bytes, as shown in Figure 18

在开发环境中的数据库中运行SQL，报错：#1071 - Specified key was too long; max key length is 767 bytes

3. If you use utf8mb4 and have a unique index on a varchar column with a length of more than 191 characters, you need to open innod b_large_prefix to allow larger columns in the index, since utf8mb4 requires more storage space than utf8 or latin1. If innodb_large_prefix (default) is enabled, the index key prefix is limited to 3072 bytes for InnoDB tables that use dynamic or compressed row format. If innodb_large_prefix is disabled, the index key prefix of any row format table is limited to 767 bytes. The successful execution of the migration command in the local environment is because the mysql version of the local environment is 5.7.19 (mysql 5.7.7 InnoDB_Large_Prefix is enabled by default, InnoDB_Large_Prefix is deprecated in MySQL 5.7.7 and will be deleted in future releases.), and the development environment is 5.6.16, check the URL: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html , as shown in Figure 19

如果使用的是utf8mb4，并且在长度超过191个字符的varchar列上有唯一索引，则需要打开innodb_large_prefix以允许索引中的较大列，因为utf8mb4需要比utf8或latin1更多的存储空间。

Figure 19

4. Parameter configuration of Alibaba Cloud RDS, enable InnoDB_Large_Prefix, as shown in Figure 20

Figure 20

5. Run sql in the database in the development environment, success, as shown in Figure 21

Figure 21

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

set global innodb_file_format = barracuda;

6. Edit the database migration file, \console\migrations\m180620_105204_update_table_options_to_log.php

&lt;?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-&gt;db-&gt;drivername ===mysql) {
// http://stackoverflow.com/questions/766809/whats-the-d IFFERENCE-BETWEEN-UTF8-General-CI-AND-UTF8-Unicode-CI
$tableOptions =character set utf8mb4 collate utf8mb4_unicode_ci engine=innodb;
$this-&gt;execute(alter table {{%user}} row_format=dynamic);
$this-&gt;execute(alter table {{%user}} convert to character set utf8mb4 collate utf8mb4_unicode_ci);
$this-&gt;execute(alter table {{%log}} row_format=dynamic);
$this-&gt;execute(alter table {{%log}} convert to character set utf8mb4 collate utf8mb4_unicode_ci);
}

$this-&gt;AddCommentOnTable({{%user}},User, $tableOptions);
$this-&gt;AddCommentOnTable({{%log}},Journal, $tableOptions);
}

/**
* {@inheritdoc}
*/
public function SafeDown()
{
echo "m180620_105204_update_table_options_to_log cannot be reverted.\n";

return false;
}

tiveting
// 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 method of the request log to allow records, mainly to ignore the log of the get request, edit \api\config\params.php

&lt;?php
return[
administratormail=&gt;admin@example.com,
requestlog=&gt; [
Allowmethod=&gt; [post,Put,delete]#atfp_close_translate_span#, //Request method of request log allowed record
],
];

8. In Postman, get http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac3 8409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2

{
"code": 10000,
"message": "Get log list success",
"data": {
"items":[
{
"id": 21,
"level": 4,
"category": "api\behaviors\\requestLogBehavior::AfterRequest",
"log_time": 1530000329.6175,
"prefix": "[app-api]#atfp_close_translate_span#[/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_15295552309.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"
}
]#ATFP_CLOSE_Translate_span#
},
"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": "Wireless Beijing"
},
"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=E56db1b43546a110431ac3 8409ED8E9E&amp;login_tid=49117dc410c491af0de08f2948aecd8f&amp;page=2"
},
"first": {
"href": "http://api.pcs-api.localhost/v1/logs?login_id=E56db1b43546a110431ac3 8409ed8e9e&amp;login_tid=49117dc410c491af0de08f2948aecd8f&amp;page=1"
},
"prev": {
"href": "http://api.pcs-api.localhost/v1/logs?login_id=E56db1b43546a110431ac3 8409ed8e9e&amp;login_tid=49117dc410c491af0de08f2948aecd8f&amp;page=1"
}
},
"_meta": {
"totalcount": 23,
"pagecount": 2,
"currentPage": 2,
"perpage": 20
}
}
}

You may also like...

Leave a Reply Cancel reply

You may also like...

In Windows 10 Professional, use multiple Laravel Sail environments and set your desired domain name

Errors reported under PHP 7.1.12 and Yii Framework/1.1.16: PHP Notice Unserialize(): Unexpected End of Serialized Data The solution of

Error in shell: [!: Analysis solution of [!: command not found]

Leave a Reply Cancel reply