use Pimatic with MySQL-Database

Guest

hello friend,
i tried again to connect my pimatic instance to my mysql-server but i failed at the following point and hope that anyone of you have a hint for me…

I have created a table and a user for this
the collation of the table was in the first step (server vault) “latin”
at the second time i have created the table with the collation of “utf8mb4”
–> read that this should help by the following error

The tables are created by pimatic, but he can’t insert/select or so…

Fatal Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x92\xA1 N...' for column 'text' at row 1 at Query.Sequence._packetToError (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Query.ErrorPacket (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/Protocol.js:279:23) at Parser.write (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/Protocol.js:39:16) at Socket.<anonymous> (/home/pi/pimatic-app/node_modules/mysql/lib/Connection.js:103:28) at emitOne (events.js:77:13) at Socket.emit (events.js:169:7) at Socket.emit (/home/pi/pimatic-app/node_modules/pimatic/startup.coffee:63:24) at readableAddChunk (_stream_readable.js:153:18) at Socket.Readable.push (_stream_readable.js:111:10) at TCP.onread (net.js:537:20) -------------------- at Protocol._enqueue (/home/pi/pimatic-app/node_modules/mysql/lib/protocol/Protocol.js:145:48) at Connection.query (/home/pi/pimatic-app/node_modules/mysql/lib/Connection.js:208:25) at /home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/dialects/mysql/index.js:152:18 at Promise._execute (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/debuggability.js:300:9) at Promise._resolveFromExecutor (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:483:18) at new Promise (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:79:10) at Client_MySQL._query (/home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/dialects/mysql/index.js:146:12) at Client_MySQL.query (/home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/client.js:197:17) at /home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/transaction.js:274:21 at tryCatcher (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/util.js:16:23) at Function.Promise.attempt.Promise.try (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/method.js:39:29) at Client_MySQL.trxClient.query (/home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/transaction.js:271:34) at Runner.<anonymous> (/home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/runner.js:146:36) at Runner.tryCatcher (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/util.js:16:23) at Runner.query (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/method.js:15:34) at /home/pi/pimatic-app/node_modules/pimatic/node_modules/knex/lib/runner.js:65:21 at tryCatcher (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/util.js:16:23) at /home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/using.js:185:26 at tryCatcher (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/util.js:16:23) at Promise._settlePromiseFromHandler (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:512:31) at Promise._settlePromise (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:569:18) at Promise._settlePromise0 (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:614:10) at Promise._settlePromises (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:693:18) at Promise._fulfill (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:638:18) at PromiseArray._resolve (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise_array.js:126:19) at PromiseArray._promiseFulfilled (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise_array.js:144:14) at Promise._settlePromise (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:574:26) at Promise._settlePromise0 (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:614:10) at Promise._settlePromises (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/promise.js:693:18) at Async._drainQueue (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/async.js:133:16) at Async._drainQueues (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/async.js:143:10) at Immediate.Async.drainQueues [as _onImmediate] (/home/pi/pimatic-app/node_modules/pimatic/node_modules/bluebird/js/release/async.js:17:14) at processImmediate [as _immediateCallback] (timers.js:383:17)

That error comes in both collations… maybe someone can help me here a bit…

edit:

MySQL runs on another Server (MySQL-Version 8.0.11), but the raspberry can connect.
Via SSH i can make some query’s of the empty pimatic database…

Thanks

kosta

@chris

hi, I found out why the error is occurring.
my try to switch from sqllite to mariadb resulted in the same problem.

problem:
if there are Unicode symbols with 4 byte in the device names the bug occurs.
My symbol was a 🔌
Your approach was right!
mysql utf8 is not really utf8.

but pimatic uses knex.js as sql connector and this one uses utf8
by default for the client connection.

solution:
fortunately, the connection object is passed directly to knex:
so simply add the following to the config

      "client": "mysql",
      "connection": {
        "host": "localhost",
        "User": "USER",
        "password": "PASSWORD",
        "database": "pimatic",
        "charset": "utf8mb4"  <--------------------------
       },
      "deleteExpiredInterval": "5min",
      "diskSyncInterval": "10min"

of course the database and all tables should also use utf8mb4.

i have at least changed only the problematic table.

ALTER TABLE pimatic.message MODIFY COLUMN text text
    CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

now it works like a charm