1 |
|
`
1 |
|
`
~~~
由于系统执行一个时间比较长的接口ngxin抛出下面的错误
1 | upstream timed out (10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond) while reading response header from upstream |
解决方法:
1,修改fastcgi_read_timeout的参数值,系统默认60秒;
2,fastcgi_read_timeout指定nginx接受后端fastcgi响应请求超时时间 (指已完成两次握手后nginx接受fastcgi响应请求超时时间)1
2
3
4
5
6
7
8
9
10 location ~ \.php(.*)$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_split_path_info ^((?U).+\.php)(/?.+)$;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param PATH_INFO $fastcgi_path_info;
fastcgi_param PATH_TRANSLATED $document_root$fastcgi_path_info;
fastcgi_read_timeout 600;
include fastcgi_params;
}
#背景:
最近需要做个服务端消息推送的事情,首先就考虑到了Laravel自带的广播功能,它是利用redis的发布订阅功能和前端利用node.js实现的一套集合了Websocket 的功能
前置条件:
后端:1
2
3
4
5
6
7
8
91,安装redis扩展
composer require predis/predis
2,在config/app.php中打开注释:
App\Providers\BroadcastServiceProvider::class,
3,在.env中
APP_URL=http://your_domain.com #域名
APP_NAME=YourAppName #默认laravel即可
QUEUE_CONNECTION=redis #redis队列
BROADCAST_DRIVER=redis #broadcast的驱动使用redis
前端:1
2
31,npm Install
2,npm install -g laravel-echo-server #https://github.com/tlaverdure/laravel-echo-server
3,npm install --save socket.io-client
#公有频道
1,后端代码:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41<?php
namespace App\Events;
use Illuminate\Broadcasting\Channel;
use Illuminate\Queue\SerializesModels;
use Illuminate\Foundation\Events\Dispatchable;
use Illuminate\Broadcasting\InteractsWithSockets;
use Illuminate\Contracts\Broadcasting\ShouldBroadcast;
class PublicEvent implements ShouldBroadcast
{
use Dispatchable, InteractsWithSockets, SerializesModels;
/**
* Create a new event instance.
*
* @return void
*/
public $msg = '';
public function __construct($msg)
{
$this->msg = $msg;
}
/**
* Get the channels the event should broadcast on.
*
* @return \Illuminate\Broadcasting\Channel|array
*/
public function broadcastOn()
{
return new Channel('public');
}
// public function broadcastWith()
// {
// return [
// 'data' => 'this is public'
// ];
// }
}
2,前端代码1
2
3
4
5
6
7
8
9
10
11
12
13
14<script>
import Echo from 'laravel-echo'
window.io = require('socket.io-client');
window.Echo = new Echo({
broadcaster: 'socket.io',
host: window.location.hostname + ':6001',
});
window.Echo.channel('public')
.listen('PublicEvent', (e) => {
console.log(e);
});
</script>
3,打开页面在console,即可以看到我们发送的数据1
2
3
4
5{msg: "this is public", socket: null}
msg: "this is public"
socket: null
__proto__: Object
}
#私有频道
PrivateEvent:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51<?php
namespace App\Events;
use App\Models\UserModel;
use Illuminate\Queue\SerializesModels;
use Illuminate\Broadcasting\PrivateChannel;
use Illuminate\Foundation\Events\Dispatchable;
use Illuminate\Broadcasting\InteractsWithSockets;
use Illuminate\Contracts\Broadcasting\ShouldBroadcast;
class PrivateEvent implements ShouldBroadcast
{
use Dispatchable, InteractsWithSockets, SerializesModels;
public $user;
public $userId = '';
/**
* Create a new event instance.
*
* @return void
*/
public function __construct(UserModel $user)
{
$this->user = $user;
$this->userId = 3590;
// dd('user.' . $this->user->u_id);
}
/**
* Get the channels the event should broadcast on.
*
* @return \Illuminate\Broadcasting\Channel|array
*/
public function broadcastOn()
{
// dd(Auth::user());
return new PrivateChannel('user.' . $this->userId);
}
// 精细化返回的数据,如果加了这个函数,公有属性才不会返回而是以这个函数内放回的字段为主
// public function broadcastWith()
// {
// return [
// 'data' => '2424',
// ];
// }
}
BroadcastServiceProvider:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Broadcast;
class BroadcastServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//Broadcast::routes();
Broadcast::routes(['middleware' => 'api']);
#routes()参数如果不配置 默认走的是web中间件
#'middleware' => 'api' 然后广播路由走api中间件,自己也可以定义其他类型的中间件
require base_path('routes/channels.php'); #加载检查权限的广播路由
}
}
routes/channels:#广播路由只针对广播的1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29<?php
/*
|--------------------------------------------------------------------------
| Broadcast Channels
|--------------------------------------------------------------------------
|
| Here you may register all of the event broadcasting channels that your
| application supports. The given channel authorization callbacks are
| used to check if an authenticated user can listen to the channel.
|
*/
//占位符模式
\Illuminate\Support\Facades\Broadcast::channel('user.{userId}', function ($user, $userId) {
return true;
}, ['guards' => ['api']]);
#{userId}只是占位符
#$user 闭包默认的第一个参数,授权的用户
#$userId 时间模板的公有属性(此时该值为3590即在PrivateEvent构造函数上所赋的值),也可以是其他的对象
#闭包函数接受多个参数,这个参数由事件里的broadcastOn函数声明的频道决定,如:'user.' . $this->userId . $this->userName 那么就可以接受2个参数userId和userName
//显式的路由模型绑定
\Illuminate\Support\Facades\Broadcast::channel('user.{user2}', function ($user, \App\Models\UserModel $user2) {
\Illuminate\Support\Facades\Log::info($user);
return true;
}, ['guards' => ['api']]);
# 注意这里的占位符{user2} 必须 \App\Models\UserModel $user2 这个变量名一样。否则会报错
前端代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25<script>
import Echo from 'laravel-echo'
window.io = require('socket.io-client');
window.Echo = new Echo({
broadcaster: 'socket.io',
host: window.location.hostname + ':6001',
auth: {
headers: {
Authorization: 'Bearer ' + 'WVP25lQfJFTpCcuA1rmdadgeXQplMOtobWjL4TvkDD8dMbjOX5pBF4kBwiC9'
},
},
});
window.Echo.private('user.3590')
.listen('PrivateEvent', (e) => {
alert(11);
console.log(1111);
console.log(e);
});
</script>
#user.3590 监听的频道
#PrivateEvent 监听的事件
#Authorization 授权,后端会检测如果不对会报错403
页面展示:不出意外会把公有属性全部展示出来,其他的属性不会展示1
2
3
4
5
6
7{
user: {…}, userId: 3590, socket: null}
socket: null
user: {u_id: 3590, u_username: "02125", u_job_number: "", u_warehouse_id: 1, u_department_id: 0, …}
userId: 3590
__proto__: Object
}
参考:
https://xueyuanjun.com/post/19505
http://silverd.cn/2018/06/01/laravel-broadcasting-adv.html
有时会碰到一些需求,查询分组后的最大值,最小值所在的整行记录或者分组后的top n行的记录,在一些别的数据库可能有窗口函数可以方面的查出来,但是MySQL没有这些函数,没有直接的方法可以查出来,可通过以下的方法来查询。
1 | CREATE TABLE `test1` ( |
##插入数据1
2
3
4
5
6
7
8
9
10
11insert into test1(name,course,score)
values
('张三','语文',80),
('李四','语文',90),
('王五','语文',93),
('张三','数学',77),
('李四','数学',68),
('王五','数学',99),
('张三','英语',90),
('李四','英语',50),
('王五','英语',89);
##查看结果:1
2
3
4
5
6
7
8
9
10
11
12
13
14root:test> select * from test1;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 语文 | 80 |
| 2 | 李四 | 语文 | 90 |
| 3 | 王五 | 语文 | 93 |
| 4 | 张三 | 数学 | 77 |
| 5 | 李四 | 数学 | 68 |
| 6 | 王五 | 数学 | 99 |
| 7 | 张三 | 英语 | 90 |
| 8 | 李四 | 英语 | 50 |
| 9 | 王五 | 英语 | 89 |
+----+--------+--------+-------+
##TOP 1
查询每门课程分数最高的学生以及成绩
1、使用自连接【推荐】1
2
3
4
5
6
7
8
9
10
11
12root:test> select a.name,a.course,a.score from
-> test1 a
-> join (select course,max(score) score from test1 group by course) b
-> on a.course=b.course and a.score=b.score;
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 语文 | 93 |
| 王五 | 数学 | 99 |
| 张三 | 英语 | 90 |
+--------+--------+-------+
rows in set (0.00 sec)
2,使用相关子查询1
2
3
4
5
6
7
8
9
10root:test> select name,course,score from test1 a
-> where score=(select max(score) from test1 where a.course=test1.course);
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 语文 | 93 |
| 王五 | 数学 | 99 |
| 张三 | 英语 | 90 |
+--------+--------+-------+
rows in set (0.00 sec)
或者1
2
3
4
5
6
7
8
9
10root:test> select name,course,score from test1 a
-> where not exists(select 1 from test1 where a.course=test1.course and a.score < test1.score);
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 语文 | 93 |
| 王五 | 数学 | 99 |
| 张三 | 英语 | 90 |
+--------+--------+-------+
3 rows in set (0.00 sec)
##TOP N
N>=1
查询每门课程前两名的学生以及成绩
1、使用union all
如果结果集比较小,可以用程序查询单个分组结果后拼凑,也可以使用union all1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16root:test> (select name,course,score from test1 where course='语文' order by score desc limit 2)
-> union all
-> (select name,course,score from test1 where course='数学' order by score desc limit 2)
-> union all
-> (select name,course,score from test1 where course='英语' order by score desc limit 2);
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 语文 | 93 |
| 李四 | 语文 | 90 |
| 王五 | 数学 | 99 |
| 张三 | 数学 | 77 |
| 张三 | 英语 | 90 |
| 王五 | 英语 | 89 |
+--------+--------+-------+
rows in set (0.01 sec)
2、自身左连接1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16root:test> select a.name,a.course,a.score
-> from test1 a left join test1 b on a.course=b.course and a.score<b.score
-> group by a.name,a.course,a.score
-> having count(b.id)<2
-> order by a.course,a.score desc;
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 数学 | 99 |
| 张三 | 数学 | 77 |
| 张三 | 英语 | 90 |
| 王五 | 英语 | 89 |
| 王五 | 语文 | 93 |
| 李四 | 语文 | 90 |
+--------+--------+-------+
rows in set (0.00 sec)
3、相关子查询1
2
3
4
5
6
7
8
9
10
11
12
13
14
15root:test> select *
-> from test1 a
-> where (select count(*) from test1 where course=a.course and score>a.score) < 2
-> order by a.course,a.score desc;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 6 | 王五 | 数学 | 99 |
| 4 | 张三 | 数学 | 77 |
| 7 | 张三 | 英语 | 90 |
| 9 | 王五 | 英语 | 89 |
| 3 | 王五 | 语文 | 93 |
| 2 | 李四 | 语文 | 90 |
+----+--------+--------+-------+
6 rows in set (0.01 sec)
4、使用用户变量1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23root:test> set @num := 0, @course := '';
Query OK, 0 rows affected (0.00 sec)
root:test>
root:test> select name, course, score
-> from (
-> select name, course, score,
-> @num := if(@course = course, @num + 1, 1) as row_number,
-> @course := course as dummy
-> from test1
-> order by course, score desc
-> ) as x where x.row_number <= 2;
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
| 王五 | 数学 | 99 |
| 张三 | 数学 | 77 |
| 张三 | 英语 | 90 |
| 王五 | 英语 | 89 |
| 王五 | 语文 | 93 |
| 李四 | 语文 | 90 |
+--------+--------+-------+
6 rows in set (0.00 sec)
实现对学生按课程依成绩高低进行排序
建表:1
2
3
4
5
6
7CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
数据:1
2
3
4
5
6
7
8
91 张三 语文 80
2 李四 语文 90
3 王五 语文 93
4 张三 数学 77
5 李四 数学 68
6 王五 数学 99
7 张三 英语 90
8 李四 英语 50
9 王五 英语 89
##子查询1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26SELECT
*, (
SELECT
COUNT(b.score) + 1
FROM
test b
WHERE
a. NAME = b. NAME
AND a.score < b.score
) AS 'rank'
FROM
test a
ORDER BY
NAME,
rank
查询结果:
7 张三 英语 90 1
1 张三 语文 80 2
4 张三 数学 77 3
2 李四 语文 90 1
5 李四 数学 68 2
8 李四 英语 50 3
6 王五 数学 99 1
3 王五 语文 93 2
9 王五 英语 89 3
#自连接1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19SELECT
a.*, COUNT(b.score)+1 AS 'rank'
FROM
test a LEFT JOIN test b ON (a.name = b.name AND a.score < b.score)
GROUP BY
a.name, a.score,a.course
ORDER BY
a.name, COUNT(b.score) asc
查询结果:
7 张三 英语 90 1
1 张三 语文 80 2
4 张三 数学 77 3
2 李四 语文 90 1
5 李四 数学 68 2
8 李四 英语 50 3
6 王五 数学 99 1
3 王五 语文 93 2
9 王五 英语 89 3
1 |
|
安装过程:
1 | cd /usr/src |
异常:
Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details.
打开日志:cat /var/log/mysqld.log1
2
3
42019-12-03T14:55:59.513203Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1088
2019-12-03T14:55:59.626483Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2019-12-03T14:55:59.626637Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-12-03T14:55:59.626707Z 0 [ERROR] [MY-010119] [Server] Aborting
日志分析:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59大概意思是找不到有效数据,查看
/usr/sbin/mysqld,发现是组和拥有者都是root,而我们的mysql执行是以mysql用户执行的。这个时候修改相关文件的组和拥有者。
一:
[root@izj6c3njvldcpf6rgj4srvz dava]# ls -ld /usr/sbin/mysqld
-rwxr-xr-x 1 root root 850078160 Sep 20 17:32 /usr/sbin/mysqld
[root@izj6c3njvldcpf6rgj4srvz dava]# chown -R mysql:mysql /usr/sbin/mysqld
[root@izj6c3njvldcpf6rgj4srvz dava]# ls -ld /usr/sbin/mysqld
-rwxr-xr-x 1 mysql mysql 850078160 Sep 20 17:32 /usr/sbin/mysqld
二,
[root@izj6c3njvldcpf6rgj4srvz dava]# ls -al /var/lib/mysql
total 188492
drwxr-x--x 6 mysql mysql 4096 Dec 3 23:13 .
drwxr-xr-x. 42 root root 4096 Dec 3 22:31 ..
-rw-r----- 1 root root 56 Mar 2 2019 auto.cnf
-rw-r----- 1 mysql mysql 0 Dec 3 22:55 binlog.index
-rw------- 1 root root 1675 Mar 2 2019 ca-key.pem
-rw-r--r-- 1 root root 1107 Mar 2 2019 ca.pem
-rw-r--r-- 1 root root 1107 Mar 2 2019 client-cert.pem
-rw------- 1 root root 1675 Mar 2 2019 client-key.pem
-rw-r----- 1 root root 277 Mar 27 2019 ib_buffer_pool
-rw-r----- 1 root root 79691776 Apr 4 2019 ibdata1
-rw-r----- 1 root root 50331648 Apr 4 2019 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 2 2019 ib_logfile1
-rw-r----- 1 root root 12582912 Apr 4 2019 ibtmp1
drwxr-x--- 2 root root 4096 Mar 2 2019 mysql
srwxrwxrwx 1 root root 0 Apr 1 2019 mysql.sock
-rw------- 1 root root 6 Apr 1 2019 mysql.sock.lock
drwxr-x--- 2 root root 4096 Mar 2 2019 performance_schema
-rw------- 1 root root 1679 Mar 2 2019 private_key.pem
-rw-r--r-- 1 root root 451 Mar 2 2019 public_key.pem
-rw-r--r-- 1 root root 1107 Mar 2 2019 server-cert.pem
-rw------- 1 root root 1679 Mar 2 2019 server-key.pem
drwxr-x--- 2 root root 12288 Mar 2 2019 sys
drwxr-x--- 2 root root 4096 Mar 27 2019 wp
chown mysql:mysql /var/lib/mysql -R
三:使用服务器初始化数据目录,包括mysql包含初始MySQL授权表的数据库,这些数据库确定如何允许用户连接到服务器
mysqld --initailize --user=mysql
四,
[root@izj6c3njvldcpf6rgj4srvz dava]# systemctl start mysqld
[root@izj6c3njvldcpf6rgj4srvz dava]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2019-12-03 23:15:35 CST; 22s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1529 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1556 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─1556 /usr/sbin/mysqld
^C
[root@izj6c3njvldcpf6rgj4srvz dava]#
1 | // 斐波那契数列(Fibonacci sequence),又称黄金分割数列、因数学家列昂纳多·斐波那契(Leonardoda Fibonacci) |
1 | $list = [ |
1 | $arr = [11, 8, 43, 23, 10, 100]; |