Navigation

    Logo
    • Register
    • Login
    • Search
    • Recent
    • Tags
    • Unread
    • Categories
    • Unreplied
    • Popular
    • GitHub
    • Docu
    • Hilfe
    1. Home
    2. Русский
    3. ioBroker
    4. ioBroker драйвера
    5. Драйвер SQL History

    NEWS

    • ioBroker@Smart Living Forum Solingen, 14.06. - Agenda added

    • ioBroker goes Matter ... Matter Adapter in Stable

    • Monatsrückblick - April 2025

    Драйвер SQL History

    This topic has been deleted. Only users with topic management privileges can see it.
    • Bluefox
      Bluefox last edited by

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

      1 Reply Last reply Reply Quote 0
      • I
        instalator last edited by

        @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
        1 Reply Last reply Reply Quote 0
        • Bluefox
          Bluefox last edited by

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

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

          Лучше?

          1 Reply Last reply Reply Quote 0
          • I
            instalator last edited by

            @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. Не меньбше, так же.

Но не на все объекты ругается
            1 Reply Last reply Reply Quote 0
            • I
              instalator last edited by

              Ядрёнбатон….головастена

              Я извиняюсь, поправил не тот файл (изменил 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)
              1 Reply Last reply Reply Quote 0
              • H
                Haus last edited by

                DB Type MySQL

                При обновление 1.3.2

                sql.0	2016-11-22 11:14:50.503	info	sql.0 enabled logging of system.adapter.admin.0.memRss
sql.0	2016-11-22 11:14:50.474	info	sql.0 Connected to mysql
sql.0	2016-11-22 11:14:50.187	info	sql.0 starting. Version 1.3.2 in /opt/iobroker/node_modules/iobroker.sql, node: v4.6.1
Server-PC	2016-11-22 11:14:45.249	info	iobroker npm install --production --prefix "/opt/iobroker/node_modules/iobroker.sql" (System call)
Server-PC	2016-11-22 11:14:45.121	info	host.Server-PC instance system.adapter.sql.0 started with pid 26337
Server-PC	2016-11-22 11:14:45.120	info	host.Server-PC "system.adapter.sql.0" enabled
Server-PC	2016-11-22 11:14:45.119	info	host.Server-PC object change system.adapter.sql.0
Server-PC	2016-11-22 11:14:45.064	info	iobroker host.Server-PC Adapter "system.adapter.sql.0" is started
Server-PC	2016-11-22 11:14:44.613	info	iobroker npm WARN unmet dependency which is version 2.3.0
Server-PC	2016-11-22 11:14:44.605	info	iobroker npm WARN unmet dependency /opt/iobroker/node_modules/iobroker.modbus/node_modules/jsmodbus/node_modules/serialport/node_modules/node-pre-gyp/node_modules/tar-pack requires debug@'~2.2.0' but
Server-PC	2016-11-22 11:14:10.353	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginReset(node_sqlite3::Statement::Baton*)’:../src/macros.h:125:9: warning: unused variable ‘status’
Server-PC	2016-11-22 11:14:10.316	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginEach(node_sqlite3::Statement::Baton*)’:../src/macros.h:125:9: warning: unused variable ‘status’
Server-PC	2016-11-22 11:14:10.277	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginAll(node_sqlite3::Statement::Baton*)’:../src/macros.h:125:9: warning: unused variable ‘status’ [
Server-PC	2016-11-22 11:14:10.257	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginRun(node_sqlite3::Statement::Baton*)’:../src/macros.h:125:9: warning: unused variable ‘status’ [
Server-PC	2016-11-22 11:14:10.241	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginGet(node_sqlite3::Statement::Baton*)’:../src/macros.h:125:9: warning: unused variable ‘status’ [
Server-PC	2016-11-22 11:14:10.230	info	iobroker In file included from ../src/statement.cc:6:0:../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginBind(node_sqlite3::Statement::Baton*)’: ../src/mac
Server-PC	2016-11-22 11:14:10.200	info	iobroker ../src/statement.cc: In static member function ‘static void node_sqlite3::Statement::Work_BeginPrepare(node_sqlite3::Database::Baton*)’:../src/statement.cc:118:9: warning: unused variable ‘st
Server-PC	2016-11-22 11:13:49.579	info	iobroker ../src/database.cc: In static member function ‘static void node_sqlite3::Database::Work_BeginLoadExtension(node_sqlite3::Database::Baton*)’:../src/database.cc:625:9: warning: unused variable
Server-PC	2016-11-22 11:13:49.554	info	iobroker ../src/database.cc: In static member function ‘static void node_sqlite3::Database::Work_BeginExec(node_sqlite3::Database::Baton*)’:../src/database.cc:525:9: warning: unused variable ‘status’
Server-PC	2016-11-22 11:13:49.405	info	iobroker ../src/database.cc: In static member function ‘static void node_sqlite3::Database::Work_BeginClose(node_sqlite3::Database::Baton*)’:../src/database.cc:230:9: warning: unused variable ‘status’
Server-PC	2016-11-22 11:13:49.383	info	iobroker ../src/database.cc: In static member function ‘static void node_sqlite3::Database::Work_BeginOpen(node_sqlite3::Database::Baton*)’:../src/database.cc:144:9: warning: unused variable ‘status’
iobroker	2016-11-22 11:09:48.369	info	ERR! Tried to download(403): https://mapbox-node-binary.s3.amazonaws.com/sqlite3/v3.1.8/node-v46-linux-ia32.tar.gz node-pre-gyp ERR! Pre-built binaries not found for sqlite3@3.1.8 and node@4.6.1 (no
iobroker	2016-11-22 11:09:48.361	info	node-pre-gyp
iobroker	2016-11-22 11:09:23.368	info	WARN deprecated node-uuid@1.4.7: use uuid module instead
iobroker	2016-11-22 11:09:23.362	info	npm
host.Server-PC	2016-11-22 11:09:07.520	info	instance system.adapter.sql.0 terminated with code 0 (OK)
iobroker	2016-11-22 11:09:07.459	info	npm install iobroker.sql --production --prefix "/opt/iobroker" (System call)
host.Server-PC	2016-11-22 11:09:07.389	info	stopInstance system.adapter.sql.0 killing pid 1915
host.Server-PC	2016-11-22 11:09:07.388	info	stopInstance system.adapter.sql.0
host.Server-PC	2016-11-22 11:09:07.386	info	"system.adapter.sql.0" disabled
host.Server-PC	2016-11-22 11:09:07.385	info	object change system.adapter.sql.0
iobroker	2016-11-22 11:09:07.352	info	host.Server-PC Adapter "system.adapter.sql.0" is stopped.
iobroker	2016-11-22 11:09:05.298	info	upgrade sql
                1 Reply Last reply Reply Quote 0
                • Bluefox
                  Bluefox last edited by

                  Работает же.

                  1 Reply Last reply Reply Quote 0
                  • H
                    Haus last edited by

                    @Bluefox:

                    Работает же. `
                    Да, так для информации 🙂

                    1 Reply Last reply Reply Quote 0
                    • A
                      Alekseym6 last edited by

                      Привествую! после обновления перестал работать sqlite3. пишет:

                      sql.0 2017-02-13 11:58:17.643 error sql.0 TypeError: Cannot read property 'borrow' of undefined

                      sql.0 2017-02-13 11:58:17.643 error sql.0 Selected SQL DB was not installed properly: "sqlite". SQLite requires build tools on system. See README.md

                      sql.0 2017-02-13 11:58:12.757 warn sql.0 No connection to SQL-DB

                      как поправить?

                      1 Reply Last reply Reply Quote 0
                      • K
                        kID last edited by

                        Пробую сконвертировать history в sqlite и как то "не выходит каменный цветок".

                        ! yura@debian-srv:/opt/iobroker/node_modules/iobroker.history/converter$ node history2db.js sql.0 info 20170215 /opt/iobroker/iobroker-data/history
                        ! Send Data to sql.0
                        ! Start at 20170215
                        ! Use historyDir /opt/iobroker/iobroker-data/history
                        ! 2017-02-15 19:42:08.031 - info: history.0 starting. Version 1.5.2 in /opt/iobroker/node_modules/iobroker.history, node: v4.7.3
                        ! No stored earliesDBValues found
                        ! No stored existingDBTypes found
                        ! started processFiles with 0 known db values
                        ! We start earliest at 20170215
                        ! Day 20170211 - history.javascript.0.ibutton.value.json
                        ! datapoints reduced from 1 –> 1
                        ! Received Keypress: x
                        вроде находит данные в history а вот дальше висит не реагируя ни на что, при каждом нажатии на клавишу рапортует Received Keypress:

                        и все. Думал что данных много, уже создал запись с единственным значением и все равно облом.

                        1 Reply Last reply Reply Quote 0
                        • A
                          Alekseym6 last edited by

                          откатил до 1.4.1 заработал

                          1 Reply Last reply Reply Quote 0
                          • K
                            kID last edited by

                            @Alekseym6:

                            после обновления перестал работать sqlite3. пишет:

                            sql.0 2017-02-13 11:58:17.643 error sql.0 TypeError: Cannot read property 'borrow' of undefined

                            sql.0 2017-02-13 11:58:17.643 error sql.0 Selected SQL DB was not installed properly: "sqlite". SQLite requires build tools on system. See README.md

                            sql.0 2017-02-13 11:58:12.757 warn sql.0 No connection to SQL-DB

                            как поправить? `
                            На самом деле ответ на эту проблему описан как в рекомендуемом README.md так и этой же теме на предыдущей странице

                            http://forum.iobroker.net/viewtopic.php … 73d#p24790

                            Тоже обновил ноду и словил аналогичную ошибку.

                            1 Reply Last reply Reply Quote 0
                            • A
                              Alekseym6 last edited by

                              Благодарю! исправил.

                              1 Reply Last reply Reply Quote 0
                              • L
                                lexa last edited by

                                А если не обновлять ноду, а ставить на последнюю и по инструкции - тоже самое. Перекомпиляция бинарников не помогает:

                                sql.0	2017-12-09 08:54:41.871	error	TypeError: Cannot read property 'borrow' of undefined
sql.0	2017-12-09 08:54:41.870	error	Selected SQL DB was not installed properly: "sqlite". SQLite requires build tools on system. See README.md

                                [root@mtqq iobroker.sql]# npm update --production
npm WARN deprecated node-uuid@1.4.8: Use uuid module instead
npm notice created a lockfile as package-lock.json. You should commit this file.
+ sql-client@0.7.0
added 82 packages in 12.959s
                                1 Reply Last reply Reply Quote 0
                                • A
                                  andrey99986 last edited by

                                  На github issue сделал, но на всякий случай напишу свою проблему здесь на русском.

                                  Если при старте иоброкера значение объекта (owfs.0.wires.Балкон в моём случае) равно NULL, то при первой попытки записи этого значения в БД драйвер history c версией больше чем 1.4.6 пишет:

                                  если указан тип - number:
                                  ` > sql.0 2017-12-01 08:46:19.331 info Do not store value "null" for owfs.0.wires.Балкон because no number

                                  sql.0 2017-12-01 08:46:19.331 debug Do Automatic Datatype conversion for owfs.0.wires.Балкон

                                  sql.0 2017-12-01 08:46:19.331 debug Datatype owfs.0.wires.Балкон: Currently: string, StorageType: Number если указан тип - Automatic: > sql.0 2017-12-01 08:59:05.841 debug INSERT INTOiobroker.ts_string (id, ts, val, ack, _from, q) VALUES(1, 1512100745455, 'null', 1, 17, 64); `

                                  Через ~1 минуту когда в значение объекта становиться нормальным числом (не NULL), попыток сохранения значений объекта в БД не осуществляется.

                                  Последняя версия, в которой такой проблемы не было - это 1.4.6. На ней и сижу пока.

                                  1 Reply Last reply Reply Quote 0
                                  • First post
                                    Last post

                                  Support us

                                  ioBroker
                                  Community Adapters
                                  Donate

                                  408
                                  Online

                                  31.6k
                                  Users

                                  79.5k
                                  Topics

                                  1.3m
                                  Posts

                                  13
                                  37
                                  13149
                                  Loading More Posts
                                  • Oldest to Newest
                                  • Newest to Oldest
                                  • Most Votes
                                  Reply
                                  • Reply as topic
                                  Log in to reply
                                  Community
                                  Impressum | Datenschutz-Bestimmungen | Nutzungsbedingungen
                                  The ioBroker Community 2014-2023
                                  logo