Getting Blank Table

[bradlindblad]

Hello, I am getting a blank table, with the appropriate blue headers. My sql table only has 1 record. Here is my config:

    {
                module: 'MMM-MysqlQuery',
            position: "top_left",
                config: {
                    connection: {
                        host: "my_host",
                        port: 3306,
                        user: "my_user",
                        password: "my_pass",
                        database: "my_db"
                    },
                    query: "SELECT * FROM brad",
                    intervalSeconds: 11 * 60,
                    emptyMessage: "No data",
                    columns: [
                        { name: "name"},
                        { name: "sleep"},
                        { name: "steps"},
                        { name: "dist"},
                        { name: "pts"},
                { name: "updated"}
                    ]
                }
        },

[ralberth]

Hi! Sorry I didn't reply this weekend, was out of town moving my brother.

I tried to reproduce it here with this:

create table brad (
   name   varchar(40),
   sleep  int,
   steps  int,
   dist  varchar(10),
   pts   double,
   updated date
);

insert into brad values ('Brad!', 123, 45678, 'MIT', -45.92, '2018-01-05');

It worked for me with my setup and config file. I copied your config example above and changed the values for host, user, and password.

Let me know what your specific table definition is and what an example of the row is: "mysql -h my_host -u my_user p-my_pass my_db" (you know what to do) and run "SELECT * FROM brad" and post back what you see.

Also, check the log file from MagicMirror and see what the error is, then post it back here. For example, if I change my host value to literally "my_host", I see this in the log:

{ Error: getaddrinfo ENOTFOUND my_host my_host:3306
    at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:67:26)
    --------------------
    at Protocol._enqueue (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/Protocol.js:144:48)
...

and if I change user to root (which isn't allowed to login remotely), I see:

{ Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'10.49.253.151' (using password: YES)
    at Handshake.Sequence._packetToError (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/sequences/Handshake.js:124:18)
...

[bradlindblad]

Thanks! This may seem noobish, but how exactly do I see the logs? I run "npm start dev" in order to see the dev console, but I don't know if that's the same thing...

On Mon, Oct 1, 2018 at 8:51 AM Rich Alberth notifications@github.com wrote:

Hi! Sorry I didn't reply this weekend, was out of town moving my brother.

I tried to reproduce it here with this:

create table brad ( name varchar(40), sleep int, steps int, dist varchar(10), pts double, updated date );

insert into brad values ('Brad!', 123, 45678, 'MIT', -45.92, '2018-01-05');

It worked for me with my setup and config file. I copied your config example above and changed the values for host, user, and password.

Let me know what your specific table definition is and what an example of the row is: "mysql -h my_host -u my_user p-my_pass my_db" (you know what to do) and run "SELECT * FROM brad" and post back what you see.

Also, check the log file from MagicMirror and see what the error is, then post it back here. For example, if I change my host value to literally "my_host", I see this in the log:

{ Error: getaddrinfo ENOTFOUND my_host my_host:3306 at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:67:26)

at Protocol._enqueue (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/Protocol.js:144:48)

...

and if I change user to root (which isn't allowed to login remotely), I see:

{ Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'10.49.253.151' (using password: YES) at Handshake.Sequence._packetToError (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Handshake.ErrorPacket (/Users/ralberth/MagicMirror/modules/MMM-MysqlQuery/node_modules/mysql/lib/protocol/sequences/Handshake.js:124:18) ...

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/ralberth/MMM-MysqlQuery/issues/1#issuecomment-425914984, or mute the thread https://github.com/notifications/unsubscribe-auth/AgonUAbaSxD9yf27xn4MEWZAdSMWijEnks5ugh3ngaJpZM4XAO7R .

[ralberth]

but how exactly do I see the logs?

Your npm start dev will start up the web browser in full-screen mode and send all logging to the terminal window you typed npm start dev in. For example, this is the beginning of what I see:

% npm start dev
> magicmirror@2.4.1 start /Users/ralberth/MagicMirror
> sh run-start.sh "dev"

Starting MagicMirror: v2.4.1
Loading config ...
Loading module helpers ...
...

This is where any problems connecting or interpreting data from MySQL would be logged by the MMM-MysqlQuery module. You'd know it: the logging message would be over 8 lines long.

I said "logging" in the most general sense, since different people run MagicMirror differently. I'm using "node serveronly" to start MagicMirror from inside an init script that runs when the computer starts up. For me, "logs" are redirected to a set of files under /var on my nettop box.

If you're not seeing an obvious error message, the MMM-MysqlQuery module was happy running your SELECT * FROM brad query, and didn't see any rows returned.

Let's reproduce what the module did interactively:

% mysql -h my_host -u my_user -pmy_pass my_db
mysql> SELECT * FROM brad

You'll either see one or more rows, or no records found. If there were no records found and no error messages in the log file, then this is the correct behavior. You can confirm it by adding "emptyMessage: 'nope!'" to the config file and re-running. You'll see the headers and a TR row below it with "nope!". That's MMM-MysqlQuery successfully seeing zero rows and adding a visual note for us humans. If you don't have emptyMessage, it just prints the headers.

If the query DID return a row, we're looking for errors in the log file (again), and then seeing if there is a problem in how it is being rendered. Since you're using npm start dev you already have the DOM inspector open. Go find the HTML TABLE and let me know what was rendered.

[bradlindblad]

Yeah man... no errors related to this module in the log. I used your test file, cmdline_test.js, and it returned the 1 row that is in my MySQL table. Not sure how to use the debugger and the DOM, as I'm not a javascript guy. Any other ideas?

Thanks!

[ralberth]

OK, that means the problem is how MMM-MysqlQuery is fetching and displaying the data from the database. Can you paste the mysql interactive command-line session you tried above? Your MM config entry has a value for emptyMessage and you're not seeing that message on the screen. That means MMM-MysqlQuery thinks it did find at least one row to display. Initially, I'm thinking the column names you put in your MM config don't match the column names from the database. If that were true, it would fetch a row, and then pull the columns it was configured to use, all of which are not in the row that was pulled, so MMM-MysqlQuery just displays empty values.

We could get to the bottom of what actual data is being displayed if you peek at the DOM. You mentioned that you're not a js guy, which isn't a problem. In FireFox for example, three-lines-menu > Developer > Inspector will bring up a new window. This is displaying the current DOM (all the stuff that draws the web page). From here you'll have to right-click on the MySQL headers and select "Inspect Element". This will change the Inspector window to show you the portion of the DOM that MMM-MysqlQuery modified. That will show us what the code actually did with the data from the database.

[bradlindblad]

Sure, here is the command I used:

node cmdline_test.js <host> <port> <user> <pass> <db> 'select * from brad'

This returned:

[ RowDataPacket { name: 'Brad', sleep: 'none', steps: '674', dist: '0.2457', pts: '66.8', updated: '10-21-2018: 10:18' } ] ... which is 100% correct.

When I inspect the DOM, this is what I copied out of there:

<div id="module_17_MMM-MysqlQuery" class="module MMM-MysqlQuery MMM-MysqlQuery"><div class="module-content"><div class="mysqlQuery"><table><thead><tr><th>name</th><th>sleep</th><th>steps</th><th>dist</th><th>pts</th><th>updated</th></tr></thead><tbody></tbody></table></div></div></div>

[ralberth]

Excellent work, given my less than stellar instructions :-)

The back-end of MMM-MysqlQuery has a valid row of data from MySQL and we can see that the column names match your entries in the MM config file.

The DOM has "" and no visible indication of "No Data" from your emptyMessage config entry, meaning it didn't create a row entry for the result. The issue is in how the browser is connected to the back-end MagicMirror server, since the browser didn't add anything below the .

We're in the home stretch here, stay with me a little longer!

I just committed a new version with a bunch of logging. Please cd into modules/MMM-MysqlQuery and type "git pull".

Edit files MMM-MysqlQuery.js and node_module.js and uncomment the "console.log()" function call inside the "debuglog" entry. This "turns on" the hackish debug logging for the module.

Run everything and let it churn a minute.

Here's what I see on the server:

[DEBUG][MMM-MysqlQuery] Received {
  "identifier": "module_16_MMM-MysqlQuery",
  "connection": {
    "host": "[redacted]",
    "port": 3306,
    "user": "[redacted]",
    "password": "[redacted]",
    "database": "my_db"
  },
  "query": "SELECT * FROM brad"
}
[DEBUG][MMM-MysqlQuery] Sending result: [
  {
    "name": "Brad!",
    "sleep": 123,
    "steps": 45678,
    "dist": "MIT",
    "pts": -45.92,
    "updated": "2018-01-05T05:00:00.000Z"
  }
]

and this is what I see in my console tab of the Developer pane in Chrome:

[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: {
  "name": "Brad!",
  "sleep": 123,
  "steps": 45678,
  "dist": "MIT",
  "pts": -45.92,
  "updated": "2018-01-05T05:00:00.000Z"
}
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col name: raw value="Brad!", display value="Brad!"
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col sleep: raw value="123", display value="123"
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col steps: raw value="45678", display value="45678"
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col dist: raw value="MIT", display value="MIT"
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col pts: raw value="-45.92", display value="-45.92"
MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery]       Col updated: raw value="2018-01-05T05:00:00.000Z", display value="2018-01-05T05:00:00.000Z"

Let me know what you see. This will shed more light on why the two parts are not communicating, I hope. Happy Sunday!

[bradlindblad]

Attaching what (I think) localhost-1540173070629.log you're looking for as far as logs. I didn't see anything like what you have above. Also, you mentioned above to uncomment "node_module.js," I didn't see this file within MMM-MysqlQuery, but I did uncomment this in "node_helper.js."

[ralberth]

Thanks! That has a ton of info in it for sure. I did a search for "[DEBUG]" since it's part of the new code, and I found a single occurrence on line 218:

MMM-MysqlQuery.js:14 [DEBUG][MMM-MysqlQuery] Starting up

Nice, you found the row to uncomment in MMM-MysqlQuery.js and it's working.

Since there are no "[DEBUG][MMM-MysqlQuery]" entries below this, I know where the problem lies. The DOM_OBJECTS_CREATED event was not delivered to MMM-MysqlQuery by MagicMirror. You can see this event in file MMM-MysqlQuery.js line 95. This event is delivered automatically to each MagicMirror module once it has been drawn on the screen. Since this wasn't delivered, triggerHelper() wasn't called which means triggerHelper() didn't call sendSocketNotification() to the helper which would have fired off the query.

I also noticed a ton of other errors in the log file, including this on line 239:

main.js:40 Uncaught TypeError: Cannot read property 'appendChild' of undefined
...
    at startModules (loader.js:59)

and this on line 279:

TypeError: Cannot read property 'getElementsByClassName' of null
...
    at updateDomWithContent (main.js:137)
    at main.js:119

These errors are from inside MagicMirror itself, which is likely interfering with delivering events like DOM_OBJECTS_CREATED.

Let's start with a minimal config/config.js file with everything commented out other than this:

var config = {
    address: "localhost",
    port: 8089,
    ipWhitelist: ["127.0.0.1", "::ffff:127.0.0.1", "::1"],
    modules: [
        {
            module: 'MMM-MysqlQuery',
            position: "top_left",
            config: {
                connection: {
                    host: "host",
                    port: port,
                    user: "user",
                    password: "passwd",
                    database: "db"
                },
                query: "SELECT * FROM brad",
                intervalSeconds: 1 * 60,
                emptyMessage: "No data",
                columns: [
                    { name: "name" },
                    { name: "sleep" },
                    { name: "steps" },
                    { name: "dist" },
                    { name: "pts" },
                    { name: "updated" }
                ]
            }
        }
    ]
};

if (typeof module !== "undefined") {module.exports = config;}

and run it normally. Let me know what you see and what is in the log file again. Our goal with this test is to eliminate any problems caused by the core MagicMirror code stack-dumping. When MagicMirror can start up and be healthy, it'll deliver events to MMM-MysqlQuery and we should see things happen. FWIW, you probably have problems with other modules too, and this might fix all of them if they all share this common root cause (hoping!).

[bradlindblad]

This works. Don't know why I didnt think to eliminate all the other modules that were throwing errors...

Question: does your js close the connection each time it updates (pings the DB?) I ask because it appears every time this module updates, it opens a new MySQL connection and leaves it open.

I'm getting a bunch of database errors like this:

2018-10-24T11:37:17.264019Z 195 [Note] Aborted connection 195 to db: 'misfit' user: 'beavertrapper07' host: '208-107-85-<xxx>-dynamic.midco.net' (Got timeout reading communication packets)

[ralberth]

Good catch. I tried adding a con.destroy(); but I'm running into a race condition between when destroy stops the socket with data left to consume, and MagicMirror sending data to the browser. I'll add this as a known bug for right now. This doesn't fix your problem right now, but today and tomorrow I'm slammed :-( Sorry I can't send you a quick fix!

[ralberth]

https://github.com/ralberth/MMM-MysqlQuery/issues/2 carries-over the lack of a mysql destroy() call in node_helper.js.

[bradlindblad]

No worries! Appreciate all the help on this!

[ralberth]

Quick update, this is still on the list to be done, just having trouble finding time to devote to overhauling this appropriately.

[ralberth]

I think I have this taken care of, at least the race condition between calling con.close() and sendSocketNotification() pulling data from the connection asynchronously. We might be done here now, given what I saw above. PLEASE reopen if you're still having trouble after pulling the latest from github.

[bradlindblad]

Awesome!

On Sun, Jan 13, 2019 at 8:06 PM Rich Alberth notifications@github.com wrote:

I think I have this taken care of, at least the race condition between calling con.close() and sendSocketNotification() pulling data from the connection asynchronously. We might be done here now, given what I saw above. PLEASE reopen if you're still having trouble after pulling the latest from github.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/ralberth/MMM-MysqlQuery/issues/1#issuecomment-453887301, or mute the thread https://github.com/notifications/unsubscribe-auth/AgonUArNf_j4vpT7OMUC7Ka21eC91Y0dks5vC-YygaJpZM4XAO7R .

[Skoo84]

First of all, I want to thank You for this module, this is exactly what I've looked for. I want to implement a magic mirror which gathers the data of several nodemcu sensors. The first sensor is a rfid sensor which stores the employees in an out data to an mysql database (amazon AWS hosted). I imagine that the administrator of the company should want to see who came and went from the company The install of your module was easy, but I ran in the very same problems like others, the module came up empty: I read the solved issues, and removed all the other modules but Yours: It worked, now uncomment them one by one and found the problematic module: { module: 'MMM-Carousel', position: 'bottom_bar', // Only required to show navigation config: { See below for configurable options } },

I made some logs maybe You might find what it the issue with the Carousel module. browser log sql only.txt.txt full_log.txt magicmirrorlog.txt.txt

Also, I have 2 questions:

1. How can I calculate the refresh interval? How much is 15 * 60?
2. The employees have a timein and timeout date, the timeout is null until they leave, I added a "nullValue: "(none)" to column, but it still came out empty without "none". Any idea why?

[ralberth]

I couldn't see anything obvious in the output that screams problem, especially given your screenshots above and the supposed interaction with Carousel.

If you haven't picked-up the latest, do an update in your modules/MMM-MysqlQuery folder: "git pull".

Edit your modules/MMM-MysqlQuery/node_helper.js file and uncomment line 16: https://github.com/ralberth/MMM-MysqlQuery/blob/master/node_helper.js#L16 This is the cheap & dirty way to turn on extra logging on the server-side of MysqlQuery module. Start up the whole thing and look for "[DEBUG][MMM-MysqlQuery]" entries. I'm betting you won't see much, indicating that the module isn't getting notifications from your browser that it's time to run a SQL query.

You can probably see a cause from the browser's console. If you're in FireFox for example, go to the hamburger menu (three horizontal line button in upper-right), select Web Developer, then Web Console. Refresh the whole thing with an F5. This screen on the bottom should show an error, or at least a whole pile of logging messages.

The MMM-MysqlQuery module on the server-side just listens for requests from a browser for queries to run. The browser decides what query to run and how often. It's setup this way so you can have multiple entries in your browser running different Mysql queries. If the browser runs into trouble, it will not execute the client-side JavaScript in MMM-MysqlQuery, so you won't see any updates on the browser display and won't see any "[DEBUG][MMM-MysqlQuery]" messages on the MagicMirror console.

If this is true, the next step is to go deep on the browser side to see what stopped that part of the JAvaScript from executing. Find that, and MMM-MysqlQuery will work.

Let's start with:

1. Uncomment the line in node_helper.js above
2. Restart MagicMirror
3. Look at server side for debug messages, you probably aren't seeing any/much
4. Look at web console, F5 and wait a bit
5. Send in the output from the web console, sanitized and edited down to remove anything you don't want out on the Internet.