1. 云图网首页
  2. 技术专区
  3. 大前端

基于 yiisoft/yii2-app-advanced，在 GitHub 上新建仓库 yii2-app-advanced，新建接口应用(实现 RESTful 风格的 Web Service 服务的 API)，调整默认字符集为：utf8mb4，接口响应格式的调整，空数组自动转换为空对象，在接口应用中收集请求日志消息(1个请求对应1条日志消息)至数据库，且实现日志功能的相应接口：日志列表(设置数据过滤器以启用筛选器处理)、日志详情 (五) (2)

• 大前端, 技术专区, 数据库, 架构设计, 研发管理, 移动开发, 编程笔记

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

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

图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
    > 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、在开发环境中的数据库中运行SQL，报错：#1071 – Specified key was too long; max key length is 767 bytes，如图18

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

图18

3、如果使用的是utf8mb4，并且在长度超过191个字符的varchar列上有唯一索引，则需要打开innodb_large_prefix以允许索引中的较大列，因为utf8mb4需要比utf8或latin1更多的存储空间。如果启用innodb_large_prefix（默认值），则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀限制为3072个字节。如果innodb_large_prefix被禁用，则任何行格式的表的索引键前缀限制为767字节。在本地环境执行迁移命令成功，是因为本地环境的MySQL版本为5.7.19(MySQL 5.7.7 默认启用innodb_large_prefix，Innodb_large_prefix在MySQL 5.7.7中已弃用，并将在未来版本中删除。)，而开发环境为 5.6.16，查看网址：https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html ，如图19

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

图19

4、阿里云RDS的参数配置，启用Innodb_large_prefix，如图20

阿里云RDS的参数配置，启用Innodb_large_prefix

图20

5、在开发环境中的数据库中运行SQL，成功，如图21

在开发环境中的数据库中运行SQL，成功

图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

注：一些数据库还可能需要运行

set global innodb_file_format = BARRACUDA;

6、编辑数据库迁移文件，/console/migrations/m180620_105204_update_table_options_to_log.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、配置请求日志允许记录的请求方法，主要是忽略 GET 请求的日志，编辑 /api/config/params.php

<?php
return [
    'adminEmail' => 'admin@example.com',
    'requestLog' => [
        'allowMethod' => ['POST', 'PUT', 'DELETE'], //请求日志允许记录的请求方法
    ],
];

8、在 Postman 中，GET http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2

{
"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
}
}
}

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/181168.html

赞 (0)
0
上一篇 2021年10月31日
下一篇 2021年10月31日

相关推荐

发表回复

登录后才能评论