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.
    • 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

                            818
                            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