Драйвер SQL History

spectrekr

~~@Haus:~~

Была у меня идея через messagebox выполнять произвольные sql запросы.
Было бы неплохо, или в драйвер javascript включить модули для основных видов баз. А то при обновлении драйвера модули не включенные в стандартную поставку удаляются.

Haus · javascript: 4.1.10

~~@Bluefox:~~

~~@Haus:~~

~~@RDim:~~

Думаю вопрос про то как в javascript работать с mysql. Да именно из скрипта управления отоплением написанного на javascript, в момент выполнения скрипта. Сами данные в базу пишет каждые пять минут драйве owfs. Если есть такая возможность примерчик очень помог бы. :roll:
Была у меня идея через messagebox выполнять произвольные sql запросы. `
Bluefox, подымаю заново вопрос

Драйвер SQL History, db type MySQL

Как из базы MySQL в javascript вытащить среднее значение температуры за последние 30 минут, 5 минут ?

Haba

вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618

Haus · javascript: 4.1.10

~~@Haba:~~

вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618 `
Да пытаюсь, пока вышло подключится трудно без знаний javascript :(

Haba

Наверное больше не js, а правильно сформировать SQL запрос в базу, чтобы получить искомое :)

SELECT AVG('имя столбца в таблице с показаниями') FROM 'имя таблицы'

WHERE 'имя столбца даты и времени' >= date_sub(now(), INTERVAL 1 HOUR);

попробуй так

jaffa

Из за чего ошибка?

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 '='

instalator

~~@jaffa:~~

Из за чего ошибка?

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

Haus · javascript: 4.1.10

Почему меняя 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'.

Lstt

Сегодня решил попробовать 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

Bluefox

~~@Lstt:~~

Сегодня решил попробовать 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 у тебя?

Lstt

Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно..

Bluefox

~~@Lstt:~~

Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно.. `
К счастью я смог воспроизвести ошибку.

При смене версий ноды нужно пересобирать все бинарные пакеты. И sqlite это один из них.

Лечится просто:

cd ...iobrokerDirectory
iobroker stop sql
cd node_modules/iobroker.sql
npm update --production
cd ..\..
iobroker start sql

electric

Система armbian плата cubietruck.

Ошибка при установки драйвера:
1238_pic_198.jpg

spectrekr

Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится.

electric

~~@spectrekr:~~

Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится. `
Спасибо! Да, подождал, установилось…

instalator

Настроил сегодня 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)

Bluefox

Включи дебаг и посмотри как выглядят команды

instalator

~~@Bluefox:~~

Включи дебаг и посмотри как выглядят команды `

! ````
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

Bluefox

Поменяй строку 28 в lib/mysql.js на

    if (db === "ts_string") state.val = "'" + state.val.replace(/'/g, '\\\'') + "'";

Лучше?

instalator

~~@Bluefox:~~

Поменяй строку 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 1


PS. Не меньбше, так же.

Но не на все объекты ругается

Community Forum

NEWS

Драйвер SQL History

Support us

809

32.4k

81.6k

1.3m