Драйвер SQL History
-
Думаю вопрос про то как в javascript работать с mysql.
Да именно из скрипта управления отоплением написанного на javascript, в момент выполнения скрипта. Сами данные в базу пишет каждые пять минут драйве owfs. Если есть такая возможность примерчик очень помог бы. :roll:
Была у меня идея через messagebox выполнять произвольные sql запросы. `
Bluefox, подымаю заново вопросДрайвер SQL History, db type MySQL
Как из базы MySQL в javascript вытащить среднее значение температуры за последние 30 минут, 5 минут ?
-
вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618
-
вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618 `
Да пытаюсь, пока вышло подключится трудно без знаний javascript
-
Наверное больше не js, а правильно сформировать SQL запрос в базу, чтобы получить искомое
SELECT AVG('имя столбца в таблице с показаниями') FROM 'имя таблицы'
WHERE 'имя столбца даты и времени' >= date_sub(now(), INTERVAL 1 HOUR);
попробуй так
-
Из за чего ошибка?
sql-0 2016-04-17 04:10:21 error Cannot select SELECT id, type FROM iobroker.datapoints WHERE name='megad.0.p7_Свет_1';: Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_
sql-0 2016-04-17 04:10:21 warn Cannot get index of "megad.0.p7_Свет_1": Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
-
Из за чего ошибка?
sql-0 2016-04-17 04:10:21 error Cannot select SELECT id, type FROM iobroker.datapoints WHERE name='megad.0.p7_Свет_1';: Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_
sql-0 2016-04-17 04:10:21 warn Cannot get index of "megad.0.p7_Свет_1": Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' `
http://stackoverflow.com/questions/1008 … ysql-error -
Почему меняя host: 'localhost' на удалённый host: '192.168.1.10' все равно стучит в localhost ?
var mysql = require('mysql'); var connection = mysql.createConnection({ //host: 'localhost', host: '192.168.1.10', user: 'root', password: '********', database: 'demo', socketPath: '/var/run/mysqld/mysqld.sock' }); //создаем подключение к базе connection.connect(function(err) { if (err) { log('error connecting: ' + err.stack); return; } log('connected as id ' + connection.threadId); }); function start () { connection.query('SELECT (dev_value) AS cur_temp FROM tmp_2 WHERE ContID=6 ORDER BY dev_date DESC LIMIT 3', function(err, res, fields) { if (err) throw err; log('cur_temp: ' + (res[0].cur_temp).toFixed(2)); //setState('Heat.cur_temp', (res[0].cur_temp).toFixed(2)); }); } setInterval ( function () { start (); },30000 );PS. закомментировал socketPath: '/var/run/mysqld/mysqld.sock' стал отрабатывать host: '192.168.1.10'.
-
Сегодня решил попробовать node.js 4.4.3, получаю в логе:
sql.0 2016-04-27 09:29:07 error sql.0 TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:28:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:28:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:27:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:27:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:26:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:26:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function admin-1 2016-04-27 09:25:56 info successful connection to socket.io from ::ffff:XXX.YYY.169.102 sql-0 2016-04-27 09:25:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:25:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function -
Сегодня решил попробовать node.js 4.4.3, получаю в логе:
sql.0 2016-04-27 09:29:07 error sql.0 TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:28:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:28:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:27:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:27:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:26:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:26:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function admin-1 2016-04-27 09:25:56 info successful connection to socket.io from ::ffff:XXX.YYY.169.102 sql-0 2016-04-27 09:25:37 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function sql-0 2016-04-27 09:25:07 error TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function ```` `А какой dbtype у тебя?
-
Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно..
-
Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно.. `
К счастью я смог воспроизвести ошибку.При смене версий ноды нужно пересобирать все бинарные пакеты. И sqlite это один из них.
Лечится просто:
cd ...iobrokerDirectory iobroker stop sql cd node_modules/iobroker.sql npm update --production cd ..\.. iobroker start sql -
Система armbian плата cubietruck.
Ошибка при установки драйвера:
1238_pic_198.jpg -
Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится.
-
Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится. `
Спасибо! Да, подождал, установилось… -
Настроил сегодня MySQL .
В логе есть такие ошибки:
! ````
sql.0 2016-11-20 20:37:19.752 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(93, 1479649029728, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:19.410 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(74, 1479649029399, ''''''hdmi1'''''', 0, 2, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hdmi1'''''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:37:19.188 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(108, 1479649029176, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:16.136 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(133, 1479649026114, ''''''*pow=off '''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:13.316 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(71, 1479649023305, ''''''cool'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cool'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:07.716 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(149, 1479649017635, ''''''OK'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OK'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:07.248 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(113, 1479649017231, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:02.142 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(105, 1479649012081, ''''''*pow=off '''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:00.944 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(80, 1479649010933, ''''''0B'''''', 0, 3, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0B'''''', 0, 3, 0)' at line 1
sql.0 2016-11-20 20:36:59.489 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(50, 1479649009472, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:59.245 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479649009233, ''''''01R240G3B0'''''', 0, 2, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01R240G3B0'''''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:36:58.558 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(118, 1479649008542, ''''''phone'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'phone'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:57.851 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479649007841, ''''''min'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'min'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:35.858 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(46, 1479648985848, ''myhome-RS232,myhome-NRF,myhome-Bathroom,myhome-kitchen,myhome-bedroom,myhome-Mother,myhome-Conditioner,myhome-BENQ,myhome-Guestroom,myhome-Audio_Amplifier,myhome-Lighting'', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myhome-RS232,myhome-NRF,myhome-Bathroom,myhome-kitchen,myhome-bedroom,myhome-Mot' at line 1
mqtt.0 2016-11-20 20:35:25.866 info mqtt.0 Client [phone] closed
mqtt.0 2016-11-20 20:35:25.858 info mqtt.0 Client [phone] disconnected
sql.0 2016-11-20 20:35:19.719 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(93, 1479648909707, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:19.411 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(74, 1479648909397, ''''hdmi1'''', 0, 2, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hdmi1'''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:35:19.186 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(108, 1479648909174, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:16.122 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(133, 1479648906111, ''''*pow=off '''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:13.312 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(71, 1479648903301, ''''cool'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cool'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:07.642 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(149, 1479648897631, ''''OK'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OK'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:07.220 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(113, 1479648897209, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:02.090 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(105, 1479648892079, ''''*pow=off '''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:00.945 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(80, 1479648890930, ''''0B'''', 0, 3, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0B'''', 0, 3, 0)' at line 1
sql.0 2016-11-20 20:34:59.475 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(50, 1479648889463, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:34:59.136 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479648889124, ''''01R240G3B0'''', 0, 2, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01R240G3B0'''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:34:58.556 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(118, 1479648888540, ''''phone'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'phone'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:34:57.850 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479648887839, ''''min'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'min'''', 1, 1, 0)' at line 1
mqtt.0 2016-11-20 20:34:49.108 info mqtt.0 Client [phone] connectedКаждый раз при новой ошибке в переменной добавляются кавычки. [266_______.png](/assets/uploads/files/266_______.png) [266______.png](/assets/uploads/files/266______.png) -
Включи дебаг и посмотри как выглядят команды
-
Включи дебаг и посмотри как выглядят команды `
! ````
sql.0 2016-11-20 21:07:17.589 error Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650837572, ''01R240G3B0'', 0, 4, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the m
inMem 2016-11-20 21:07:17.576 debug message * kodi.1.playing_time val=06-37-17, ack=true, ts=1479650837485, q=0, from=system.adapter.kodi.1, lc=1479650837485
sql.0 2016-11-20 21:07:17.573 debug INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650837572, ''01R240G3B0'', 0, 4, 0);>! ```` sql.0 2016-11-20 21:08:37.649 error Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650917641, ''''''01R240G3B0'''''', 0, 4, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; che sql.0 2016-11-20 21:08:37.642 debug INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650917641, ''''''01R240G3B0'''''', 0, 4, 0); sql.0 2016-11-20 21:08:37.641 debug timed-relog mqtt.0.myhome.test.test, value='''''01R240G3B0''''', lastLogTime=1479650907639, ts=1479650917641 -
Поменяй строку 28 в lib/mysql.js на
if (db === "ts_string") state.val = "'" + state.val.replace(/'/g, '\\\'') + "'";Лучше?
-
Поменяй строку 28 в lib/mysql.js на
if (db === "ts_string") state.val = "'" + state.val.replace(/'/g, '\\\'') + "'";Лучше? `
Меньше но проскакивает:! ````
sql.0 2016-11-21 19:17:18.410 debug sql.0 INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479730628288, ''''min'''', 1, 1, 0);
sql.0 2016-11-21 19:17:18.574 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479730628288, ''''min'''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'min'''', 1, 1, 0)' at line 1PS. Не меньбше, так же. Но не на все объекты ругается -
Ядрёнбатон….головастена
Я извиняюсь, поправил не тот файл (изменил mSsql.js).
Теперь вот как выдает:
! ````
sql.0 2016-11-21 20:50:19.359 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(108, 1479736209339, ''\'off\''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off\''', 1, 1, 0)' at line 1
sql.0 2016-11-21 20:50:16.940 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(93, 1479736206928, ''\'off\''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off\''', 1, 1, 0)' at line 1
sql.0 2016-11-21 20:50:14.965 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(50, 1479736204944, ''\'off\''', 1, 1, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'off\''', 1, 1, 0)' at line 1Первая запись после рестарта драйвера нормальная, потом с каждой записью добавляет кавычки. SQL настроен на Запись неизменённых значений каждые n секунд. [266_234234344444.png](/assets/uploads/files/266_234234344444.png) [266______2222.png](/assets/uploads/files/266______2222.png)
