Compatible with MariaDB

[Coernel82]

Hi @ralberth , thank you for your module. I use a MariaDB database on the same Raspberry Pi as MagicMirror² runs on but I also get a blank table as someone got it before. (#1)

So question one: Is your plugin compatible with MariaDB? If no this explains my problem. But if it is compatible here are more details:

This is literally my config:

            module: 'MMM-MysqlQuery',
            position: "bottom_bar",
            config: {
                connection: {
                    host: "http://localhost", // it does not matter if I use http or not, or use magicmirror or 127.0.0.1.
                    port: 3306,
                    user: "grafanareader",
                    password: "password",
                    database: "RASPBERRY_PI",
                },
                query: `select LEVEL from zisterne order by date desc limit 1`,
                intervalSeconds: 15 * 60,
                emptyMessage: "Kein Füllstand",
                columns: [
                    { name: "LEVEL"},
                ]
            }
        },

The SQL-Query does work locally (mysql -u root -p then use RASPBERRY_PI and then select LEVEL from zisterne order by date desc limit 1; with ; at the end. It also works remotely from Windows using MySQL-Workbench. Also using credential with read/write permissons does work. The credentials here have SELECT permission.

However the display on MagicMirror simply is LEVEL with blue background:

<table>
    <thead>
        <tr>
            <th>LEVEL</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

I can do the same with SELECT DATE. LEVEL is float and DATE is datetime

Is there any log or other way of debugging?

[ralberth]

I'm sorry you are having trouble, but glad to see you are using Maria DB! Yes, MariaDB is supported, and I set up a database to confirm your configuration works for me.

I created a Maria DB with your database and table, and added a couple rows:

create database RASPBERRY_PI;

use RASPBERRY_PI;

create table zisterne (
    date datetime,
    level float
);

insert into zisterne values ('2023-01-02', 1.2);
insert into zisterne values ('2023-03-04', 3.4);

select LEVEL from zisterne order by date desc limit 1;

It worked and produced results.

This is the test config I set up in my config.js MM file:

{
    module: "MMM-MysqlQuery",
    position: "top_left",
    config: {
        connection: {
            host: "test1.<redacted>.us-east-1.rds.amazonaws.com",
            port: 3306,
            user: "joeuser",
            password: "secretpassword",
            database: "RASPBERRY_PI"
        },
        query: `select level from zisterne order by date desc limit 1`,
        intervalSeconds: 15 * 60,
        emptyMessage: "No colors",
        columns: [
            { name: "level" }
        ]
    }
},

(I changed the host, user, and password above just to post it here securely)

From the other ticket you linked, I edited the .js file and uncommented the debuglog to generate more output in the console.

npm start dev showed a single row with "3.4", so the logic is working for me on my machine.

Let's start with the log messages. In the browser console you should see something similar to me:

MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Starting up
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Received notification DOM_OBJECTS_CREATED, payload=undefined, from undefined
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Sending MYSQLQUERY id=module_9_MMM-MysqlQuery, query=select level from zisterne order by date desc limit 1
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Start timer
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Replacing table with new server results:
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]    Adding row to table: {
  "level": 3.4
}
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col level: raw value="3.4", display value="3.4"

I'm hoping this will let me know exactly what happened. Paste the results here in the ticket and I'll reply with more ideas!

[Coernel82]

Thank you for your extensive reply! After solving this I will make a pull request documenting this error and solution for your readme.md!

I ran npm start dev from the /modules folder. (Starting within your SQL-folder didn't work). How can I deactivate the developer mode again? Just exit it with Crtl+C?

So I got a log in the shell which helped a lot - the node mysql-module was not installed. After installing the module (with nice drawbacks such as full SD-card and therefore uninstalling grafana, deleting the whole node_modules folder) I am a step further! 😊 The address http://127.0.0.1 was not found. I changed it to localhost.

It now works, I will update your readme! Thank you very much!

[ralberth]

I'm surprised anything worked if you ran npm start dev from anywhere but the MagicMirror folder. If you don't have a mysql module loaded, I'll guess you missed the npm install line from the install instructions.

I tried to simulate not running npm install by removing the node_modules folder from my MMM-MysqlQuery folder and restarted. I saw a bunch of stack dumps like these:

[17.01.2023 08:53.40.272] [ERROR] WARNING! Could not load config file. Starting with default configuration. Error found: Error: Cannot find module 'mysql'
Require stack:
- /Volumes/workplace/MagicMirror/modules/MMM-MysqlQuery/node_helper.js
- /Volumes/workplace/MagicMirror/js/app.js
- /Volumes/workplace/MagicMirror/js/electron.js
- /Volumes/workplace/MagicMirror/node_modules/electron/dist/Electron.app/Contents/Resources/default_app.asar/main.js

Did you see anything like this in your server logs?

The npm install reads the package.json file in the local directory. This has a dependency on mysql (line 21).

I'm curious how you had MagicMirror running with the MMM-MysqlQuery module doing anything. It crashes on my machines here if the modules aren't available.

[Coernel82]

No, I really did do the whole installation, including the npm install! (And now I know why you have to do it!) However I suspect that the SD-Card at that moment was exactly that full that everything except the mysql module was installed and I didn't see the error!

I am not sure about the stack dump - and now it works so I can't deny or confirm!

So basically my pull request needs to be denied!