基于 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
图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
图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
图19
4、阿里云RDS的参数配置，启用Innodb_large_prefix，如图20
图20
5、在开发环境中的数据库中运行SQL，成功，如图21
图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
}
}
}
![运行 SQL 文件时，报错：[ERR] 2013 - Lost connection to MySQL server during query](https://www.shuijingwanwq.com/wp-content/uploads/2017/08/1-3-520x245.png)
2 条回复
[…] 20、参考：阿里云RDS的参数配置，启用Innodb_large_prefix 。编辑 config/database.php，修改 ‘engine’ => null, 为 ‘engine’ => ‘InnoDB’,。再次执行数据库迁移，报错。SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘failed_jobs’ already exists。在数据库中删除掉所有表后，重新执行迁移。不再报错。 […]
[…] Reference:Parameter configuration of Alibaba Cloud RDS, enable InnoDB_Large_Prefix . Edit config/database.php, modifyEngine=>null, forEngine=>innodb,. Execute the database […]