none
Try changing the Connection encryption settings to connect to this data source. RRS feed

  • Question

  • Hi there!

    I have a PQ query.

    let
        Source = MySQL.Database("10.8.0.10", "leb"),
        leb_x_sale_order_props_value = Source{[Schema="leb",Item="x_sale_order_props_value"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(leb_x_sale_order_props_value,{"ORDER_ID", "ORDER_PROPS_ID", "VALUE"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [ORDER_PROPS_ID] = 16)
    in
        #"Filtered Rows"

    When I am trying to filter table via Table.SelectRows I got this error. But why? 
    I have already connected to Data Source without any problems.

    What's wrong?

    -- Paul


    Monday, February 2, 2015 9:41 AM

Answers

  • Looking in the trace files, I see the error "Exception:\r\nExceptionType: MySql.Data.MySqlClient.MySqlException, MySql.Data, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d\r\nMessage: Incorrect key file for table '/tmp/m/#sql_1a85_0.MYI'; try to repair it". This is probably the error that gets reported as the encryption issue. I don't know enough about MySQL to suggest a remedy, but it looks like it's a server side condition of some kind where temporary tables aren't given a proper encryption key.

    With respect to the generated query,
    We currently have a single query generation pipeline that's shared between all relational back-ends. It generates patterns that were shown to be reasonably efficient with SQL Server and Oracle. I believe MySQL was added later, and it's possible that the MySQL query engine is sufficiently different to the other ones we use that the same patterns perform very poorly. This is the first time we've gotten this complaint, and rewriting query generation for MySQL is no small task. The only suggestion I can make with respect to M is that it might help to combine the SelectRows predicates into as few as possible. But I suspect it may not make a difference :(. Can I assume you've tried to run the same query directly from HeidiSQL and had the same result?

    With respect to multiple evaluations,
    When you're in the editor, we run evaluations for each step of the query so that we can quickly show you the incremental results. There are some scenarios where this results in poorer performance than there should be, and we're actively working on some improvements in that regard.

    Wednesday, February 4, 2015 5:11 PM

All replies

  • It may be a bug. Can you enable tracing, rerun this query, then use "Send a Frown" with the traces attached so we can see what error from the database we're actually getting?
    Monday, February 2, 2015 1:49 PM
  • Hi Curt,

    Thanks for your time.
    I found two interesting things. Piece of my M-code looks like this:

    ...
    b_sale_order_props_value = Source{[Schema="leb",Item="b_sale_order_props_value"]}[Data],
    b_sale_order_props_value_s1 = Table.SelectColumns(b_sale_order_props_value,{"ORDER_ID", "begin", "deal_code", "PRODUCT_ID", "PRICE", "QUANTITY"}),
    b_sale_order_props_value_s2 = Table.SelectRows(b_sale_order_props_value_s1, each ([ORDER_PROPS_ID] = 16))
    ...

    (1) If I am trying to use Table.SelectColumns I got error.

    DataSource.Error: MySQL: Unable to connect to any of the specified MySQL hosts.
    Details:
        Message=Unable to connect to any of the specified MySQL hosts.
        ErrorCode=-2147467259

    If I am avoid using Table.SelectColumns everything is OK.

    (2) M-Code 

    b_sale_order_props_value_s2 = Table.SelectRows(b_sale_order_props_value_s1, each ([ORDER_PROPS_ID] = 16))

    return error only if FastCombine is ON.  Probably PQ suspect that I parametrize query with external data (= 16) and trigger policy.

    Does it help? Or I should generate log file?

    -- Paul



    Monday, February 2, 2015 5:13 PM
  • The log files for both cases would be interesting.
    Monday, February 2, 2015 5:28 PM
  • Curt,

    Trace is here: https://www.dropbox.com/s/x5gwrvs0612rfo2/Traces.zip?dl=0

    Please let me know If I should do something more.

    -- Paul

    Monday, February 2, 2015 5:40 PM
  • Hi Curt,

    I really need your help.

    I did some progress in investigation. I realized that I can see (via MySQL client HeidiSQL) what SQL-code was generated by PQ and sent to MySQL server. 
    It seems that PQ nevertheless does Query Folding but SQL-query generated by PQ is rather heavy.

    So, I have such simple native SQL-query (executed 0.06 sec):
    ---
    SELECT
    sb.PRODUCT_ID,
    sb.PRICE AS p_price,
    sb.QUANTITY p_qty,
    w.deal_code

    FROM b_sale_basket sb
    INNER JOIN b_sale_order so ON so.ID = sb.ORDER_ID AND so.canceled = "N"
    INNER JOIN s_warehouse w ON w.id = sb.PRODUCT_ID AND w.active = 1
    INNER JOIN s_deal d ON d.code = w.deal_code

    WHERE  
    d.type = 'default' 
    AND d.status = 'enabled' 
    AND d.begin BETWEEN '2015-02-01' AND '2015-02-02'
    ---

    Because I want to auto-referesh my report in Office 365 ProPlus + Power BI
    I should rewrite it to M-code (Data Management Gateway does not support native SQL).

    So, I rewrote native SQL-query in such M-code (executed 15+ min with error):
    ----
    let
        Source = MySQL.Database("10.8.0.10", "leboutique"),
        s_deal = Source{[Schema="leb", Item="s_deal"]}[Data],
        s_deal_s1 = Table.SelectColumns(s_deal, {"code", "begin", "type", "status"}),
        s_deal_s2 = Table.SelectRows(s_deal_s1, each [begin] >= #datetime(2015, 2, 1, 0, 0, 0) and [begin] < #datetime(2015, 2, 2, 0, 0, 0) and [type] = "default" and [status] = "enabled"),
        s_deal_s3 = Table.SelectColumns(s_deal_s2,{"code", "begin"}),
        s_warehouse = Source{[Schema="leboutique",Item="s_warehouse"]}[Data],
        s_warehouse_s1 = Table.SelectColumns(s_warehouse, {"id", "deal_code", "active"}),
        s_warehouse_s2 = Table.SelectRows(s_warehouse_s1, each [active] = true),
        s_warehouse_s3 = Table.SelectColumns(s_warehouse_s2,{"id", "deal_code"}),
        join1 = Table.Join(s_warehouse_s3, {"deal_code"}, s_deal_s3, {"code"}, JoinKind.Inner),
        clean = Table.RemoveColumns(join1,{"code"}),
        b_sale_basket = Source{[Schema="leboutique", Item="b_sale_basket"]}[Data],
        b_sale_basket_s1 = Table.SelectColumns(b_sale_basket,{"ORDER_ID", "PRODUCT_ID", "PRICE", "QUANTITY"}),
        /* join below is failed with strange error: 
        Try changing the Connection encryption settings to connect to this data source
        */
        join2 = Table.Join(clean, {"id"}, b_sale_basket_s1, {"PRODUCT_ID"}, JoinKind.Inner)
    in
        join2
    ---

    SQL-code generated by PQ is here:
    ---
    select `$Outer`.`id`,
        `$Outer`.`deal_code`,
        `$Outer`.`begin`,
        `$Inner`.`ORDER_ID`,
        `$Inner`.`PRODUCT_ID`,
        `$Inner`.`PRICE`,
        `$Inner`.`QUANTITY`
    from 
    (
        select `$Outer`.`id`,
            `$Outer`.`deal_code`,
            `$Inner`.`begin`
        from 
        (
            select `_`.`id`,
                `_`.`deal_code`
            from 
            (
                select `id`,
                    `deal_code`,
                    `active`
                from `leboutique`.`s_warehouse` `$Table`
            ) `_`
            where `_`.`active` = 1
        ) `$Outer`
        inner join 
        (
            select `_`.`code`,
                `_`.`begin`
            from 
            (
                select `code`,
                    `type`,
                    `status`,
                    `begin`
                from `leboutique`.`s_deal` `$Table`
            ) `_`
            where ((`_`.`status` = 'enabled' and `_`.`type` = 'default') and `_`.`begin` >= '2015-02-01 00:00:00') and `_`.`begin` < '2015-02-02 00:00:00'
        ) `$Inner` on (`$Outer`.`deal_code` = `$Inner`.`code`)
    ) `$Outer`
    inner join 
    (
        select `ORDER_ID`,
            `PRODUCT_ID`,
            `PRICE`,
            `QUANTITY`
        from `leboutique`.`b_sale_basket` `$Table`
    ) `$Inner` on (`$Outer`.`id` = `$Inner`.`PRODUCT_ID`)
    ---

    Could you help?
    Probably I wrote bad M-code? 

    I also see that PQ generate query in several threads (via several Microsoft.Mashup.Container.NetFX40.exe processes). 
    But even if I canceled PQ query, SQL threads on MySQL side still continue execution. 
    So, while I tested different combination of M-code I generated 20+ SQL threads. 

    Looking forward to your reply.

    -- Paul



    Tuesday, February 3, 2015 8:18 PM
  • Looking in the trace files, I see the error "Exception:\r\nExceptionType: MySql.Data.MySqlClient.MySqlException, MySql.Data, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d\r\nMessage: Incorrect key file for table '/tmp/m/#sql_1a85_0.MYI'; try to repair it". This is probably the error that gets reported as the encryption issue. I don't know enough about MySQL to suggest a remedy, but it looks like it's a server side condition of some kind where temporary tables aren't given a proper encryption key.

    With respect to the generated query,
    We currently have a single query generation pipeline that's shared between all relational back-ends. It generates patterns that were shown to be reasonably efficient with SQL Server and Oracle. I believe MySQL was added later, and it's possible that the MySQL query engine is sufficiently different to the other ones we use that the same patterns perform very poorly. This is the first time we've gotten this complaint, and rewriting query generation for MySQL is no small task. The only suggestion I can make with respect to M is that it might help to combine the SelectRows predicates into as few as possible. But I suspect it may not make a difference :(. Can I assume you've tried to run the same query directly from HeidiSQL and had the same result?

    With respect to multiple evaluations,
    When you're in the editor, we run evaluations for each step of the query so that we can quickly show you the incremental results. There are some scenarios where this results in poorer performance than there should be, and we're actively working on some improvements in that regard.

    Wednesday, February 4, 2015 5:11 PM
  • Hi Curt,

    Thanks for reply.

    (1) Yes, I tried to run sql-query generated by PQ in HeideSQL.
    I waited for 10+ minutes and canceled it. 

    (2) Regarding error "Incorrect key file for table '/tmp/m/#sql_1a85_0.MYI'; try to repair it"

    I asked our sysadmin to help me and what we found. First of all we Googled problem 
    and found such solutions: http://stackoverflow.com/questions/19003106/mysql-error-126-incorrect-key-file-for-table

    Since sql-query generated by PQ had a lot of sub-queries MySQL tried to create tmp tables with several index files (.MYI). 
    As result in 3 minutes we seen in tmp folder 4 index files each 41GB and they continued growing! 
    As total free disk space was about 256GB, in few minutes we had situation with no free space and of course MySQL returned error.

    Unfortunately I use PQ on real world data:
    table 's_warehouse': 11M+ rows, 7,5GB
    table 'b_sale_order_props_value': 57M+ rows, 8,9GB

    I think it would be great to add to PQ standard library function 
    which can Join more than 2 tables.

    What do you think?

    -- Paul


    Thursday, February 5, 2015 9:05 AM
  • Hi

    I am new to Power Query and am using this as an exercise to get up to speed.

    I have a list of SQL server that I simply pass into a M query and I want to create a list of databases on that server

    = Table.AddColumn(Source, "DatabaseName", each Sql.Databases([ServerName] ))

    The above creates a table field, but when I click on it I get the error message "Try changing the Connection encryption setting to connect to this data source, with a button <Edit Credentials>.  It odes work for some servers.

    tks D

    Tuesday, February 17, 2015 4:09 AM