Nested Row issue

[calmor15014]

On the latest update of this module and MM2 as of Jan 1, 2019.

When calling my query (which is actually a stored procedure call, though I'm not sure if that has anything to do with it), I got some strange results.

The first "row" is actually the two rows I'm expecting to receive as a return. The second "row" is a list of server results containing the number of rows affected, etc.

So, when debugging, it looks something like this:

Adding row to table: { [ "Month": "01/2019", "Value": 123 ] [ "Month": "02/ 2019", "Value": 234 } }

I'd later get another "adding row to table" which was not nested, and have the server results.

As a result, the table would populate undefined for everything, because the columns didn't match (were unnamed).

I added a kludge to check for a nested first row in the table creation function and split it out. It works, but it's not pretty. Is this an effect of using the stored procedure? (I know very little of Javascript so I'm not even sure how the call is being made to check.)

[ralberth]

hi! I'm buried at the moment, but will look into it hopefully tonight or tomorrow. The New Year is a busy time here at work. I got your email, and it's sitting high up on my inbox list of things to do!

• Rich

On Tue, Jan 1, 2019 at 1:00 PM calmor15014 notifications@github.com wrote:

On the latest update of this module and MM2 as of Jan 1, 2019.

When calling my query (which is actually a stored procedure call, though I'm not sure if that has anything to do with it), I got some strange results.

The first "row" is actually the two rows I'm expecting to receive as a return. The second "row" is a list of server results containing the number of rows affected, etc.

So, when debugging, it looks something like this:

Adding row to table: { [ "Month": "01/2019", "Value": 123 ] [ "Month": "02/ 2019", "Value": 234 } }

I'd later get another "adding row to table" which was not nested, and have the server results.

As a result, the table would populate undefined for everything, because the columns didn't match (were unnamed).

I added a kludge to check for a nested first row in the table creation function. It works, but it's not pretty. Is this an effect of using the stored procedure? (I know very little of Javascript so I'm not even sure how the call is being made to check.)

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ralberth/MMM-MysqlQuery/issues/4, or mute the thread https://github.com/notifications/unsubscribe-auth/AAChNVW8-Y1OphDkp6KyN9OmLBQmpF1kks5u-6JMgaJpZM4Zl-tN .

[calmor15014]

I wouldn't put it that high on the list. :) I'm sure you have more than enough other things to do - such is the life of hobbyist coding. I'm just happy you wrote the plugin to begin with! Beats trying to make my own for sure - super rusty on the JavaScript.

I did kludge it enough (and I mean that in the ugliest way) to make it function for what I need it to do and not error out. If you do decide you want to dive into it, I can get you better logs and actual table descriptions of what I'm doing for your tests.

Jim

On Wed, Jan 2, 2019 at 1:44 PM Rich Alberth notifications@github.com wrote:

hi! I'm buried at the moment, but will look into it hopefully tonight or tomorrow. The New Year is a busy time here at work. I got your email, and it's sitting high up on my inbox list of things to do!

• Rich

On Tue, Jan 1, 2019 at 1:00 PM calmor15014 notifications@github.com wrote:

On the latest update of this module and MM2 as of Jan 1, 2019.

When calling my query (which is actually a stored procedure call, though I'm not sure if that has anything to do with it), I got some strange results.

The first "row" is actually the two rows I'm expecting to receive as a return. The second "row" is a list of server results containing the number of rows affected, etc.

So, when debugging, it looks something like this:

Adding row to table: { [ "Month": "01/2019", "Value": 123 ] [ "Month": "02/ 2019", "Value": 234 } }

I'd later get another "adding row to table" which was not nested, and have the server results.

As a result, the table would populate undefined for everything, because the columns didn't match (were unnamed).

I added a kludge to check for a nested first row in the table creation function. It works, but it's not pretty. Is this an effect of using the stored procedure? (I know very little of Javascript so I'm not even sure how the call is being made to check.)

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ralberth/MMM-MysqlQuery/issues/4, or mute the thread < https://github.com/notifications/unsubscribe-auth/AAChNVW8-Y1OphDkp6KyN9OmLBQmpF1kks5u-6JMgaJpZM4Zl-tN

.

— 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/4#issuecomment-450947603, or mute the thread https://github.com/notifications/unsubscribe-auth/Aa_40NYS8d5yp2SnxNn_GOrjJKMjhuWsks5u_P4hgaJpZM4Zl-tN .

[ralberth]

I tried playing around with a fresh database and a quick stored procedure that just queries a spare table I have. No luck with what little I tried. What is the stored procedure you're using? I'm curious what the output of calling the stored procedure looks like in a mysql command-line so we can get a feel for what it's doing too.

• Rich

On Wed, Jan 2, 2019 at 1:54 PM calmor15014 notifications@github.com wrote:

Rich,

I wouldn't put it that high on the list. :) I'm sure you have more than enough other things to do - such is the life of hobbyist coding. I'm just happy you wrote the plugin to begin with! Beats trying to make my own for sure - super rusty on the JavaScript.

[calmor15014]

Since my hack is working and I don't want to disturb the superglue and duct tape, this week I'll set up a test on a VM or container and send over the output. I should have some time tomorrow to get you everything needed.

I didn't try to run the CALL in a command line environment. In MySQL Workbench, it returns two rows just like a normal select would... but it's hard to say that it isn't also sending something that Workbench is picking up and managing.

Thanks for looking into it!

On Mon, Jan 7, 2019 at 11:39 PM Rich Alberth notifications@github.com wrote:

I tried playing around with a fresh database and a quick stored procedure that just queries a spare table I have. No luck with what little I tried. What is the stored procedure you're using? I'm curious what the output of calling the stored procedure looks like in a mysql command-line so we can get a feel for what it's doing too.

• Rich

On Wed, Jan 2, 2019 at 1:54 PM calmor15014 notifications@github.com wrote:

Rich,

I wouldn't put it that high on the list. :) I'm sure you have more than enough other things to do - such is the life of hobbyist coding. I'm just happy you wrote the plugin to begin with! Beats trying to make my own for sure - super rusty on the JavaScript.

— 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/4#issuecomment-452172971, or mute the thread https://github.com/notifications/unsubscribe-auth/Aa_40M-nUflB5GqTCuDXN_Ude-JL0nfcks5vBCEFgaJpZM4Zl-tN .

[ralberth]

I have a new idea: your stored procedure might be returning more than one result. Check out http://www.mysqltutorial.org/mysql-stored-procedures-return-multiple-values/

Here's my example:

create table sheetmusic ( composer varchar(600), title varchar(600) );

insert into sheetmusic (composer, title) values ("Bach", "Suite No. 1 in G major"), ("Beethoven", "Symphony No. 5 in C Minor"), ("Bach", "Brandenburg Concerto");

DELIMITER // CREATE PROCEDURE goodmusic (IN com varchar(80)) BEGIN SELECT FROM sheetmusic where composer = com; SELECT FROM sheetmusic where composer = "Bach"; END // DELIMITER ;

Test that the stored procedure works as expected by typing the following into the mysql command interpreter:

CALL goodmusic('Bach');

• Rich

[calmor15014]

@ralberth - thanks for all of your hard work and testing. I finally got to do some testing of my own.

On the target machine, I installed mysql-client and ran mysql from the command line. The SP is returning two rows in one set:

MySQL [gnucash]> CALL getBudgetResults('Worst Case Budget', YEAR(NOW()), MONTH(NOW()), "'Groceries','Dining'");
+--------+-----------+----------+--------+----------+
| Month  | Category  | Budgeted | Actual | Variance |
+--------+-----------+----------+--------+----------+
| 1/2019 | Dining    |   300.00 |  39.23 |   260.77 |
| 1/2019 | Groceries |   500.00 | 140.45 |   359.55 |
+--------+-----------+----------+--------+----------+
2 rows in set (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

So, that seems like it's working normally. Then I tried cmdline_test.js. There, I see the extra information.

Command (specific data removed): node cmdline_test.js host 3306 user password db "CALL getBudgetResults('Worst Case Budget', YEAR(NOW()), MONTH(NOW()), \"'Groceries','Dining'\");"

[ [ RowDataPacket {
      Month: '1/2019',
      Category: 'Dining',
      Budgeted: 300,
      Actual: 39.23,
      Variance: 260.77 },
    RowDataPacket {
      Month: '1/2019',
      Category: 'Groceries',
      Budgeted: 500,
      Actual: 140.45,
      Variance: 359.55 } ],
  OkPacket {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 34,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]

It's this OkPacket that seems to be causing the problem. The rows return inside a nested array. If I don't try to iterate the nested array, then the module debug info shows two rows, but those two rows don't map to the column headers I defined because row 1 is a nested array and row 2 is the OkPacket row, which has different results.

Not sure if it matters, but I have to escape the call differently inside the module, but it pulls the same OkPacket information and is returning data.

I'm running the below stored procedure against a Gnucash MySQL database. The SP is as follows (and I apologize for my mess, I'm not a DBA at heart either). Table schema is here.

I wanted a far simpler query that allowed me to pick (on the fly) the accounts to compare to the budget. But, the budget uses periods (numbered starting with 0) and the actual transaction log uses the real dates, so I'm making some assumptions that 0 is Jan, etc... then trying to mash them together. The budget also has one line for Dining, for example, but I have nested accounts under Dining in the actual transaction log, for example Dining at Restaurant X, etc. This rolls up all of the actuals, for the months provided, and the month prior if it's after January (because I didn't have a budget last year). The temp table executes first, pulls all of the data for all accounts, and then the variables are passed to the statement that's executed second, stored in the sqlt variable, using the field names passed in the CALL statement (which is why they're in quotes and escaped the way they are).

Yeah, kind of kludgy on its own, but I'm not a good enough DBA to make it cleaner given the constraints and the desire to not rewrite the SP every time I want to see a different account/category. But, it does return normally in the command line, so I'm not sure if it's the SP or whatever version of MySQL server or client tools I'm using. Seems like its a node.js thing, but not sure what to do about it?

Thanks for looking into it!

PROCEDURE `getBudgetResults`(
    budgetName varchar(2048),
    reportYear int,
    reportMonth int,
    accountNames varchar(2048)
)
BEGIN
        SET @sqlt = CONCAT(
                "SELECT `Month`, `Category`, `Budgeted`, `Actual`, `Budgeted` - `Actual` AS 'Variance'
        FROM tt1
        WHERE `Category` IN (", accountNames, ");");

        DROP TEMPORARY TABLE IF EXISTS tt1;
        CREATE TEMPORARY TABLE tt1 ENGINE=MEMORY AS
                (SELECT CONCAT(`budg_amt1`.`period_num` + 1,'/', reportYear) AS 'Month',
                        `acct1`.`name` AS 'Category',
                        ROUND(`budg_amt1`.`amount_num` / `budg_amt1`.`amount_denom`, 2) AS 'Budgeted',
                        COALESCE(
                                (SELECT ROUND(SUM(`spl1`.`value_num` / `spl1`.`value_denom`),2)
                                FROM `splits` AS spl1
                                INNER JOIN `accounts` AS acct2 ON `acct2`.`guid` = `spl1`.`account_guid`
                                INNER JOIN `transactions` AS tx1 ON `tx1`.`guid` = `spl1`.`tx_guid`
                                WHERE (`acct2`.`guid` = `acct1`.`guid` OR `acct2`.`parent_guid` = `acct1`.`guid`)
                                        AND MONTH(`tx1`.`post_date`) = `budg_amt1`.`period_num` + 1
                                        AND YEAR(`tx1`.`post_date`) = reportYear
                                ),0.00) AS 'Actual'
                FROM `budget_amounts` AS budg_amt1
                INNER JOIN `accounts` AS acct1 ON `budg_amt1`.`account_guid` = `acct1`.`guid`
                INNER JOIN `budgets` AS budg1 ON `budg_amt1`.`budget_guid` = `budg1`.`guid`
                WHERE `budg1`.`name` = budgetName
                        AND `budg_amt1`.`period_num` IN (reportMonth - 2, reportMonth - 1)
                ORDER BY `budg_amt1`.`period_num`, `acct1`.`name`);

        PREPARE stmt FROM @sqlt;
    EXECUTE stmt;

    DROP TEMPORARY TABLE tt1;
END

[ralberth]

Found the root cause: my library is working at a lower-level than I expected when invoking stored procedures.

Read https://github.com/mysqljs/mysql/issues/654 for more info.

In short, when this happens, MMM-MysqlQuery should detect it and handle it automatically.

This is on the list to be repaired when I can attack it with the other tickets here in Github.

[calmor15014]

Thanks - makes sense! Now that we know what's occurring, I might get courageous and try to fix it correctly. If I do, I'll submit a PR for it.

[calmor15014]

I do have some code that seems to work and isn't as ugly as my kludge. I tested it against stored procedures returning 0, 1, 2, and 4 rows, as well as normal SELECT queries that return 0, 1, and 2 rows. It handles both cases normally. The only issue would be if the SELECT query had columns named fieldCount, affectedRows, and serverStatus as the fix looks for all of those three column names to determine the OkPacket. Not knowing node.js well enough, I couldn't figure out any easier way to identify it.

I'd be happy to submit a PR if you're interested?

[ralberth]

OK, probably a good solution in place. This pulls apart what comes from MySQL, makes intelligent guess at what's happening, and just concats everything other than the final summary object.

Put another way, feel free to use CALL and even return multiple sets of return sets!

Let me know what you think...closing this just because I saw it work on my box. Let me know for real and we'll reopen this and get back into the details of what you're up to!

THANK YOU for working through this and providing such specific code above...really helped me out.

[calmor15014]

Thanks! I'll give it a test. I made a prettier version of the kludge I did before, but this does a similar thing in a much cleaner way. Sorry to be that corner case that just makes things difficult!