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

    • Neuer Blog: Fotos und Eindrücke aus Solingen

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

    • ioBroker goes Matter ... Matter Adapter in Stable

    Драйвер SQL History

    This topic has been deleted. Only users with topic management privileges can see it.
    • 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

                              798
                              Online

                              31.8k
                              Users

                              80.0k
                              Topics

                              1.3m
                              Posts

                              13
                              37
                              13365
                              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