mysql — простой скрипт phple schdule вставляет записи дважды при подключении к удаленной базе данных

Это реализация PHP тот же планировщик. Я сделал эту реализацию PHP, потому что я думал, что у меня есть проблема с асинхронной природой nodejs. К сожалению, у них та же проблема. Это может указывать на то, что что-то не так с моей логикой или mysql. Мне было трудно понять, почему.

У меня есть 10 команд, хранящихся в таблице расписания. Когда код дня, часы и минуты совпадают с текущим кодом дня, часами и минутами. Эти команды будут скопированы в таблицу команд. Это зависит от планировщика, который я реализую.

Вот результат после запуска графика. Предполагается, что у одного расписания будет одна вставка в таблицу команд. В настоящее время одно расписание имеет 2 вставки в таблицу команд, а в дублировании schedule_id имеет значение NULL. Это очень странно. Я провел много времени и не мог понять, почему. Это состояние гонки? Я не понимаю

Полный код

<?php

include_once("./config.php");
include_once("./database.php");

function connect_db() {
global $g_pdo;
$g_pdo = Database::connect();
$g_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

function close_db() {
Database::disconnect();
}function get_current_utc_time() {
$date_utc = new \DateTime(null, new \DateTimeZone("UTC"));
return $date_utc->format("Y-m-d H:i:s");
}

function insert_into_pending_cmd($schedule_id, $device_name, $cmd) {
global $g_pdo;
global $g_is_test_table;

printf("\n** insert_into_pending_cmd **\n");

$curr_time = get_current_utc_time();

if($g_is_test_table) {
$sql = "insert into cmd_test set CommandDate = ".  "'". $curr_time. "'".  ",". "RemoteName = ".  "'". $device_name. "'". ",". "CommandJSON = ". "'". $cmd. "'". ",". "CommandComplete = 0". ",".  "ScheduleId = ". "'". $schedule_id. "'";
}
else {
$sql = "insert into Command set CommandDate = ".  "'". $curr_time. "'".  ",". "RemoteName = ".  "'". $device_name. "'". ",". "CommandJSON = ". "'". $cmd. "'". ",". "CommandComplete = 0". ",".  "ScheduleId = ". "'". $schedule_id. "'";
}
printf("\nsql:, %s\n", $sql);

$q = $g_pdo->query($sql);
//$q->execute();
}function is_schedule_already_pending($schedule_id, $device_name) {
global $g_pdo;
global $g_is_test_table;
$condi = true;

printf("\n** is_schedule_already_pending **\n");

if($g_is_test_table) {
$sql = "select count(*) as num from cmd_test where ScheduleId = ".  "'".  $schedule_id.  "'".  " and CommandComplete = 0 and RemoteName = ". "'". $device_name. "'";
}
else {
$sql = "select count(*) as num from Command where ScheduleId = ".  "'".  $schedule_id.  "'".  " and CommandComplete = 0 and RemoteName = ". "'". $device_name. "'";
}
printf("\nsql:, %s\n", $sql);

//$q = $g_pdo->prepare($sql);
//$q->execute();
$q = $g_pdo->query($sql);
$data = $q->fetch(PDO::FETCH_ASSOC);
$num = $data["num"];//test
print_r("print_r");
print_r($data);

if($num == 0) {
printf("\nnot there, schedule not in cmd, %s\n", $num);
$condi = false;
}
else {
printf("\nin there, schedule in cmd, %s\n", $num);
$condi = true;
}

return $condi;
}function get_schedule_rows() {
global $g_pdo;
global $g_is_test_table;

printf("\n---- start ----\n");

// http://php.net/manual/en/mysqli-stmt.fetch.php

if($g_is_test_table) {
$sql = "select ScheduleId, ScheduleRemoteName, ScheduleDaycode, ScheduleTime, ScheduleCommandJSON   from schedule_test order by ScheduleId asc";
}
else {
$sql = "select ScheduleId, ScheduleRemoteName, ScheduleDaycode, ScheduleTime, ScheduleCommandJSON from Schedule order by ScheduleId asc";
}
//printf("\nsql:, %s\n", $sql);

//$q = $g_pdo->prepare($sql);
//$q->execute();
$q = $g_pdo->query($sql);

while($data = $q->fetch(PDO::FETCH_ASSOC)) {
$schedule_id = $data["ScheduleId"];
$device_name = $data["ScheduleRemoteName"];
$schedule_day_code = $data["ScheduleDaycode"];
$schedule_time = $data["ScheduleTime"];
$cmd = $data["ScheduleCommandJSON"];

// print schedule row
printf("\n== start schedule row ==\n");
printf("\n%s, %s, %s, %s, %s\n", $schedule_id, $device_name, $schedule_day_code, $schedule_time, $cmd);

// schedule
$arr = explode(":", $schedule_time);
$schedule_h = $arr[0];
$schedule_min = $arr[1];

// print day_code, h and min
printf("\n%s, %s, %s\n", $schedule_day_code, $schedule_h, $schedule_min);

// curr
// https://stackoverflow.com/questions/8655515/get-utc-time-in-php
// http://php.net/manual/en/function.date.php
// http://php.net/manual/en/datetime.construct.php
$date_utc = new \DateTime(null, new \DateTimeZone("UTC"));
$curr_day_code = $date_utc->format("w");
$curr_h = $date_utc->format("H");
$curr_min = $date_utc->format("i");

// print current utc day, etc
printf("\n%s, %s, %s\n", $curr_day_code, $curr_h, $curr_min);

// match day code
if($curr_day_code == $schedule_day_code) {
printf("\n..match up day code..\n");
if($curr_h == $schedule_h) {
printf("\n__match up hour__\n");
if($curr_min == $schedule_min) {
printf("\n## match up d-h-m ##\n");

// is schedule already pending
if(is_schedule_already_pending($schedule_id, $device_name)) {
// no insert
}
else {
// insert
insert_into_pending_cmd($schedule_id, $device_name, $cmd);
}
}
}

}

printf("\n== end schedule row ==\n");
}

printf("\n---- end ----\n");
}function main() {
connect_db();

while(true) {
get_schedule_rows();
sleep(5);
}

close_db();
}// run
main();

выходной журнал

---- start ----

== start schedule row ==

254, BC1D300917, 2, 01:05:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

2, 01, 05

0, 06, 25

== end schedule row ==

== start schedule row ==

255, BC1D300917, 4, 01:05:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

4, 01, 05

0, 06, 25

== end schedule row ==

== start schedule row ==

256, BC1D300917, 2, 02:59:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

2, 02, 59

0, 06, 25

== end schedule row ==

== start schedule row ==

257, BC1D300917, 4, 02:59:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

4, 02, 59

0, 06, 25

== end schedule row ==

== start schedule row ==

258, BC1D300917, 2, 03:18:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

2, 03, 18

0, 06, 25

== end schedule row ==

== start schedule row ==

259, BC1D300917, 4, 03:18:00, {"command":{"description":"Up","channel":2},"type":"Somfy4Channel"}

4, 03, 18

0, 06, 25

== end schedule row ==

== start schedule row ==

266, BC14100310, 3, 21:30:00, {"command":{"channel":0,"description":"Down"},"type":"Becker1Channel"}

3, 21, 30

0, 06, 25

== end schedule row ==

== start schedule row ==

267, BC14100310, 6, 21:30:00, {"command":{"channel":0,"description":"Down"},"type":"Becker1Channel"}

6, 21, 30

0, 06, 25

== end schedule row ==

== start schedule row ==

288, BC14100316, 3, 21:09:00, {"command":{"channel":0,"description":"unrecognized command"},"type":"Becker1Channel"}

3, 21, 09

0, 06, 25

== end schedule row ==

== start schedule row ==

289, BC14100316, 6, 21:09:00, {"command":{"channel":0,"description":"unrecognized command"},"type":"Becker1Channel"}

6, 21, 09

0, 06, 25

== end schedule row ==

== start schedule row ==

290, BC1D100603, 1, 11:30:00, {"command":{"channel":4,"description":"Down"},"type":"Somfy4Channel"}

1, 11, 30

0, 06, 25

== end schedule row ==

== start schedule row ==

291, BC1D100603, 3, 11:30:00, {"command":{"channel":4,"description":"Down"},"type":"Somfy4Channel"}

3, 11, 30

0, 06, 25

== end schedule row ==

== start schedule row ==

292, BC1D100603, 6, 11:30:00, {"command":{"channel":4,"description":"Down"},"type":"Somfy4Channel"}

6, 11, 30

0, 06, 25

== end schedule row ==

== start schedule row ==

293, BC1D100603, 9, 22:15:00, {"command":{"channel":4,"description":"Up"},"type":"Somfy4Channel"}

9, 22, 15

0, 06, 25

== end schedule row ==

== start schedule row ==

298, BC14100399, 100, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

100, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

299, BC14100399, 4, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

300, BC1U900046, 2, 03:26:00, {"command":{"channel":0,"description":"Up"},"type":"Viva4Channel"}

2, 03, 26

0, 06, 25

== end schedule row ==

== start schedule row ==

301, BC1U900046, 4, 03:26:00, {"command":{"channel":0,"description":"Up"},"type":"Viva4Channel"}

4, 03, 26

0, 06, 25

== end schedule row ==

== start schedule row ==

302, BC1U900046, 6, 03:26:00, {"command":{"channel":0,"description":"Up"},"type":"Viva4Channel"}

6, 03, 26

0, 06, 25

== end schedule row ==

== start schedule row ==

303, BC14100399, 4, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

304, BC14100399, 2, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

2, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

305, BC1H600654, 4, 00:00:00, {
"type" : "phoenix_multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

306, BC1H600654, 3, 00:00:00, {
"type" : "phoenix_multi_remote",
"command" : {
"channel" : "1",
"description" : "DOWN"}
}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

307, BC14100399, 3, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

308, BC14100399, 5, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

5, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

309, BC1H600654, 4, 00:00:00, {
"type" : "phoenix_multi_remote",
"command" : {
"channel" : "1",
"description" : "DOWN"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

310, BC1H600654, 4, 00:00:00, {
"type" : "phoenix_multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

311, BC14100399, 4, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

312, BC14100399, 1, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

1, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

313, BC14100399, 3, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

314, BC14100399, 1, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

1, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

315, BC14100399, 5, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "DOWN"}
}

5, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

316, BC14100399, 3, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

318, BC1M600204, 0, 08:20:00, {"command":{"channel":1,"description":"Up"},"type":"motolux_multi_remote"}

0, 08, 20

0, 06, 25

..match up day code..

== end schedule row ==

== start schedule row ==

319, BC1M600204, 0, 08:21:00, {"command":{"channel":1,"description":"Up"},"type":"motolux_multi_remote"}

0, 08, 21

0, 06, 25

..match up day code..

== end schedule row ==

== start schedule row ==

320, BC14100399, 3, 00:00:00, {
"type" : "generic_blind_1_channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

321, BC1U900046, 4, 00:00:00, {
"type" : "tube_multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

322, BC14100399, 4, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

323, BC14100399, 3, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

324, BC14100399, 2, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

2, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

325, BC1U900046, 3, 00:00:00, {
"type" : "Viva4Channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

326, BC14100399, 3, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

327, BC1F200700, 3, 00:00:00, {
"type" : "Dynaveil_Multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

328, BC14100399, 4, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "generic_blind_1_channel"}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

329, BC1F200716, 4, 00:00:00, {
"type" : "Dynaveil_Multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

330, BC1U900046, 3, 00:00:00, {
"type" : "Viva4Channel",
"command" : {
"channel" : "1",
"description" : "UP"}
}

3, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

331, BC1D100603, 4, 00:00:00, {
"type" : "somfy_multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

332, BC1F200697, 4, 00:00:00, {
"command" : {
"description" : "UP",
"channel" : "1"},
"type" : "Dynaveil_Multi_remote"}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

333, BC1U900046, 4, 00:00:00, {
"type" : "tube_multi_remote",
"command" : {
"channel" : "1",
"description" : "UP"}
}

4, 00, 00

0, 06, 25

== end schedule row ==

== start schedule row ==

334, BC14100305, 1, 15:24:00, {"command":{"channel":1,"description":"Up"},"type":"generic_blind_1_channel"}

1, 15, 24

0, 06, 25

== end schedule row ==

== start schedule row ==

335, BC14100305, 2, 15:24:00, {"command":{"channel":1,"description":"Up"},"type":"generic_blind_1_channel"}

2, 15, 24

0, 06, 25

== end schedule row ==

== start schedule row ==

336, BC14100305, 3, 15:24:00, {"command":{"channel":1,"description":"Up"},"type":"generic_blind_1_channel"}

3, 15, 24

0, 06, 25

== end schedule row ==

== start schedule row ==

337, BC14100305, 5, 15:24:00, {"command":{"channel":1,"description":"Up"},"type":"generic_blind_1_channel"}

5, 15, 24

0, 06, 25

== end schedule row ==

== start schedule row ==

338, BC14100305, 9, 15:24:00, {"command":{"channel":1,"description":"Up"},"type":"generic_blind_1_channel"}

9, 15, 24

0, 06, 25

== end schedule row ==

== start schedule row ==

341, BC1M600204, 9, 09:21:00, {"command":{"channel":3,"description":"Up"},"type":"motolux_multi_remote"}

9, 09, 21

0, 06, 25

== end schedule row ==

== start schedule row ==

382, BC1M600666, 6, 20:34:00, {"command":{"channel":5,"description":"Up"},"type":"motolux_multi_remote"}

6, 20, 34

0, 06, 25

== end schedule row ==

== start schedule row ==

383, BC1M600666, 5, 20:34:00, {"command":{"channel":5,"description":"Up"},"type":"motolux_multi_remote"}

5, 20, 34

0, 06, 25

== end schedule row ==

== start schedule row ==

384, BC1M600666, 9, 08:26:00, {"command":{"channel":5,"description":"Down"},"type":"motolux_multi_remote"}

9, 08, 26

0, 06, 25

== end schedule row ==

== start schedule row ==

388, BC1M600666, 6, 20:27:00, {"command":{"channel":1,"description":"Up"},"type":"motolux_multi_remote"}

6, 20, 27

0, 06, 25

== end schedule row ==

== start schedule row ==

389, BC1M600666, 5, 20:27:00, {"command":{"channel":1,"description":"Up"},"type":"motolux_multi_remote"}

5, 20, 27

0, 06, 25

== end schedule row ==

== start schedule row ==

592, MYTEST_3, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '592' and CommandComplete = 0 and RemoteName = 'MYTEST_3'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:20',RemoteName = 'MYTEST_3',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '592'

== end schedule row ==

== start schedule row ==

593, MYTEST_1, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '593' and CommandComplete = 0 and RemoteName = 'MYTEST_1'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:20',RemoteName = 'MYTEST_1',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '593'

== end schedule row ==

== start schedule row ==

594, MYTEST_2, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '594' and CommandComplete = 0 and RemoteName = 'MYTEST_2'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:20',RemoteName = 'MYTEST_2',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '594'

== end schedule row ==

== start schedule row ==

595, MYTEST_0, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '595' and CommandComplete = 0 and RemoteName = 'MYTEST_0'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:20',RemoteName = 'MYTEST_0',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '595'

== end schedule row ==

== start schedule row ==

596, MYTEST_4, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '596' and CommandComplete = 0 and RemoteName = 'MYTEST_4'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:20',RemoteName = 'MYTEST_4',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '596'

== end schedule row ==

== start schedule row ==

597, MYTEST_8, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '597' and CommandComplete = 0 and RemoteName = 'MYTEST_8'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:21',RemoteName = 'MYTEST_8',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '597'

== end schedule row ==

== start schedule row ==

598, MYTEST_6, 0, 06:25:00, CMD

0, 06, 25

0, 06, 25

..match up day code..

__match up hour__

## match up d-h-m ##

** is_schedule_already_pending **

sql:, select count(*) as num from Command where ScheduleId = '598' and CommandComplete = 0 and RemoteName = 'MYTEST_6'
print_rArray
(
[num] => 0
)

not there, schedule not in cmd, 0

** insert_into_pending_cmd **

sql:, insert into Command set CommandDate = '2016-09-11 06:25:21',RemoteName = 'MYTEST_6',CommandJSON = 'CMD',CommandComplete = 0,ScheduleId = '598'

== end schedule row ==

== start schedule row ==

599, MYTEST_5, 0, 06:25:00, CMD