На связке Node.js + Express + MySQL при деплое на сервер возникает ошибка в одном из маршрутов.
Если обратиться к этому маршруту то получаем ошибку
"code":"ER_PARSE_ERROR",
"errno":1064,
"sqlMessage":"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE OR REPLACE VIEW tmp AS SELECT TEACH.name, ' at line 1",
"sqlState":"42000",
"index":0
но если взять код запроса из сообщения с ошибкой и вставить в Workbench то он прекрасно отработает.
Собственно сам текст запроса, в который динамически прилетают даты и ИД города. Форматирование а-ля
Workbench.
CREATE OR REPLACE VIEW tmp AS
SELECT
TEACH.name,
CAT.description,
WRK.idTEACHER,
LEC.idLECTURE,
LEC.out,
COUNT(TGFL.idTRAININGFL) AS people,
WRK.hours
FROM
LECTURE LEC
INNER JOIN
TRAININGFL TGFL ON TGFL.idLECTURE = LEC.idLECTURE
AND TGFL.idCITY = 1
AND TGFL.date_attestation BETWEEN '2018-01-01' AND '2018-01-28'
INNER JOIN
WORKLOAD WRK ON WRK.idLECTURE = TGFL.idLECTURE
INNER JOIN
TEACHER TEACH ON TEACH.idTEACHER = WRK.idTEACHER
INNER JOIN
CAT_TEACHER CAT ON CAT.idCAT_TEACHER = TEACH.idCAT
GROUP BY LEC.idLECTURE , WRK.idTEACHER;CREATE OR REPLACE VIEW allpeople AS
SELECT
COUNT(TGFL.idTRAININGFL) AS allpeople
FROM
TRAININGFL TGFL
WHERE
idCITY = 1
AND TGFL.date_attestation BETWEEN '2018-01-01' AND '2018-01-28';SELECT
name,
IFNULL(allin, 0) AS allin,
IFNULL(hoursin, 0) AS hoursin,
IFNULL(allout, 0) AS allout,
IFNULL(hoursout, 0) AS hoursout,
IFNULL(t1.people1, 0) AS people1in,
IFNULL(t2.people1, 0) AS people1out,
IFNULL(t1.people2, 0) AS people2in,
IFNULL(t2.people2, 0) AS people2out,
IFNULL(t1.people3, 0) AS people3in,
IFNULL(t2.people3, 0) AS people3out,
IFNULL(t1.people4, 0) AS people4in,
IFNULL(t2.people4, 0) AS people4out,
allpeople.allpeople
FROM
tmp
LEFT JOIN
(SELECT
idTEACHER,
SUM(IF(tmp.hours = 1, people, 0)) AS people1,
SUM(IF(tmp.hours = 2, people, 0)) AS people2,
SUM(IF(tmp.hours = 3, people, 0)) AS people3,
SUM(IF(tmp.hours = 4, people, 0)) AS people4,
COUNT(idLECTURE) AS allin,
SUM(IF(tmp.hours > 0, hours, 0)) AS hoursin
FROM
tmp
WHERE
1 = 1 AND tmp.out = 1
GROUP BY hours , idTEACHER
ORDER BY name) AS t1 ON tmp.idTEACHER = t1.idTEACHER
LEFT JOIN
(SELECT
idTEACHER,
SUM(IF(tmp.hours = 1, people, 0)) AS people1,
SUM(IF(tmp.hours = 2, people, 0)) AS people2,
SUM(IF(tmp.hours = 3, people, 0)) AS people3,
SUM(IF(tmp.hours = 4, people, 0)) AS people4,
COUNT(idLECTURE) AS allout,
SUM(IF(tmp.hours > 0, hours, 0)) AS hoursout
FROM
tmp
WHERE
1 = 1 AND tmp.out = 0
GROUP BY hours , idTEACHER
ORDER BY name) AS t2 ON tmp.idTEACHER = t2.idTEACHER
LEFT JOIN
allpeople ON 1 = 1
GROUP BY tmp.idTEACHER
ORDER BY description , name
На Windows где собственно и пишу код с теми же самыми версиями пакетов все работает без геморроя.
Где то магия, где пока понять не могу :(
Простой
Средний
Средний
