[MDEV-9500] Bug after upgrade to 10.1.10 (and 10.1.11) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.10, 10.1.11, 10.1(EOL)
Fix Version/s: 10.1.12
Component/s: Triggers
Labels:
None
Environment:
CentOS x86_64

Sprint:
10.1.12

Description

Hi Elena and all,

The bug behind ~~MDEV-9332~~ isn't after all behind my query not running. It runs perfectly until 10.1.9 and after it, it throws an error.

The Query:

UPDATE

	tbl_stock_grupo_controlo

SET

	tbl_stock_grupo_controlo.MSVArm71 = (

		SELECT

			MIN(TIMESTAMPDIFF(MONTH,det.`Data`, CURDATE()))

		FROM

			MCPC_SI.tbl_facturas fac,

			MCPC_SI.tbl_facturas_detalhe det,

			MCLP_SI.tbl_armazens_rel rel

		WHERE

			fac.CodCliente <> rel.CodCliente

			AND (

				fac.CtaPostal = ''

				OR IFNULL(fac.CtaPostal,'') <> IFNULL(rel.CtaPostal,'')

			AND tbl_stock_grupo_controlo.Armazem = rel.ArmazemDestino

			AND fac.Armazem = rel.ArmazemOrigem

			AND fac.Factura = det.Factura

			AND tbl_stock_grupo_controlo.v_ReferenciaCompleta = det.Referencia

			AND (det.Marca = rel.Marca OR rel.Marca = '')

			AND det.Atendedor <> 'VEND.INTERNET'

			AND det.CodCliente NOT IN ('K0071','K0078','K0471','K0478')

			AND det.`Data` >= '2012-01-01'

		GROUP BY

			tbl_stock_grupo_controlo.Armazem,

			det.Referencia

MariaDB 10.1.9: OK
MariaDB >= 10.1.10: SQL Error (1062): Duplicate entry '191' for key 'group_key'

10.1.9 EXPLAIN FORMAT=JSON

  "query_block": {

    "select_id": 1,

    "table": {

      "update": 1,

      "table_name": "tbl_stock_grupo_controlo",

      "access_type": "index",

      "key": "PRIMARY",

      "key_length": "",

      "used_key_parts": ["Armazem", "Marca", "Referencia"],

      "rows": 14371

},

    "subqueries": [

        "query_block": {

          "select_id": 2,

          "filesort": {

            "temporary_table": {

              "function": "buffer",

              "table": {

                "table_name": "rel",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "FK_tbl_armazens_rel_tbl_armazens_2"],

                "key": "FK_tbl_armazens_rel_tbl_armazens_2",

                "key_length": "11",

                "used_key_parts": ["ArmazemDestino"],

                "ref": ["MCPC_SI.tbl_stock_grupo_controlo.Armazem"],

                "rows": 1,

                "filtered": 100

},

              "table": {

                "table_name": "det",

                "partitions": ["p08", "p09", "p10", "p11", "p12", "p99"],

                "access_type": "ref",

                "possible_keys": ["Referencia", "Factura", "CodCliente", "Data"],

                "key": "Referencia",

                "key_length": "153",

                "used_key_parts": ["Referencia"],

                "ref": ["MCPC_SI.tbl_stock_grupo_controlo.v_ReferenciaCompleta"],

                "rows": 6,

                "filtered": 100,

                "attached_condition": "(((det.Marca = MCLP_SI.rel.Marca) or (MCLP_SI.rel.Marca = '')) and (det.Atendedor <> 'VEND.INTERNET') and (det.CodCliente not in ('K0071','K0078','K0471','K0478')) and (det.`Data` >= '2012-01-01') and (det.Factura is not null))"

},

              "table": {

                "table_name": "fac",

                "access_type": "eq_ref",

                "possible_keys": ["PRIMARY", "Armazem"],

                "key": "PRIMARY",

                "key_length": "47",

                "used_key_parts": ["Factura"],

                "ref": ["MCPC_SI.det.Factura"],

                "rows": 1,

                "filtered": 100,

                "attached_condition": "((fac.Armazem = MCLP_SI.rel.ArmazemOrigem) and (fac.CodCliente <> MCLP_SI.rel.CodCliente) and ((fac.CtaPostal = '') or (ifnull(fac.CtaPostal,'') <> ifnull(MCLP_SI.rel.CtaPostal,''))))"

10.1.10 and 10.1.11 EXPLAIN FORMAT=JSON

  "query_block": {

    "select_id": 1,

    "table": {

      "update": 1,

      "table_name": "tbl_stock_grupo_controlo",

      "access_type": "index",

      "key": "PRIMARY",

      "key_length": "",

      "used_key_parts": ["Armazem", "Marca", "Referencia"],

      "rows": 14110

},

    "subqueries": [

        "query_block": {

          "select_id": 2,

          "filesort": {

            "temporary_table": {

              "function": "buffer",

              "table": {

                "table_name": "rel",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "FK_tbl_armazens_rel_tbl_armazens_2"],

                "key": "FK_tbl_armazens_rel_tbl_armazens_2",

                "key_length": "11",

                "used_key_parts": ["ArmazemDestino"],

                "ref": ["MCPC_SI.tbl_stock_grupo_controlo.Armazem"],

                "rows": 1,

                "filtered": 100

},

              "table": {

                "table_name": "det",

                "partitions": ["p08", "p09", "p10", "p11", "p12", "p99"],

                "access_type": "ref",

                "possible_keys": ["Referencia", "Factura", "CodCliente", "Data"],

                "key": "Referencia",

                "key_length": "153",

                "used_key_parts": ["Referencia"],

                "ref": ["MCPC_SI.tbl_stock_grupo_controlo.v_ReferenciaCompleta"],

                "rows": 6,

                "filtered": 100,

                "attached_condition": "(((det.Marca = MCLP_SI.rel.Marca) or (MCLP_SI.rel.Marca = '')) and (det.Atendedor <> 'VEND.INTERNET') and (det.CodCliente not in ('K0071','K0078','K0471','K0478')) and (det.`Data` >= '2012-01-01') and (det.Factura is not null))"

},

              "table": {

                "table_name": "fac",

                "access_type": "eq_ref",

                "possible_keys": ["PRIMARY", "Armazem"],

                "key": "PRIMARY",

                "key_length": "47",

                "used_key_parts": ["Factura"],

                "ref": ["MCPC_SI.det.Factura"],

                "rows": 1,

                "filtered": 100,

                "attached_condition": "((fac.Armazem = MCLP_SI.rel.ArmazemOrigem) and (fac.CodCliente <> MCLP_SI.rel.CodCliente) and ((fac.CtaPostal = '') or (ifnull(fac.CtaPostal,'') <> ifnull(MCLP_SI.rel.CtaPostal,''))))"

Any idea what might be?

Best regards,
Antonio Fernandes

Attachments

Issue Links

includes

MDEV-9332 Bug after upgrade to 10.1.10

Closed

is caused by

MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column.

Closed

Activity

Ascending order - Click to sort in descending order

Antonio Fernandes added a comment - 2016-02-01 19:29

Test with MariaDB 10.1.9 Windows ZIP 64bits:
/* Affected rows: 163 Found rows: 0 Warnings: 0 Duration for 1 query: 00:01:49 */

Test with MariaDB 10.1.11 Windows ZIP 64bits:
/* SQL Error (1062): Duplicate entry '191' for key 'group_key' */

Antonio Fernandes added a comment - 2016-02-01 19:29 Test with MariaDB 10.1.9 Windows ZIP 64bits: /* Affected rows: 163 Found rows: 0 Warnings: 0 Duration for 1 query: 00:01:49 */ Test with MariaDB 10.1.11 Windows ZIP 64bits: /* SQL Error (1062): Duplicate entry '191' for key 'group_key' */

Elena Stepanova added a comment - 2016-02-01 21:40

ampf,
Could you please provide the schema/data dump for all structures participating in the query?
You can upload it to our ftp.askmonty.org/private, only MariaDB developers will have access to it.

Elena Stepanova added a comment - 2016-02-01 21:40 ampf , Could you please provide the schema/data dump for all structures participating in the query? You can upload it to our ftp.askmonty.org/private, only MariaDB developers will have access to it.

Antonio Fernandes added a comment - 2016-02-02 01:03

I've uploaded the tables & trigger 'tbl_stock_grupo_controlo_before_update' (the issue is somehow here).

By trial and error I figure it out that, when I delete the trigger, the query runs. When the trigger is enforcing, it throws the error.

And with 10.1.9, no issues...

Antonio Fernandes added a comment - 2016-02-02 01:03 I've uploaded the tables & trigger 'tbl_stock_grupo_controlo_before_update' (the issue is somehow here). By trial and error I figure it out that, when I delete the trigger, the query runs. When the trigger is enforcing, it throws the error. And with 10.1.9, no issues...

Elena Stepanova added a comment - 2016-02-02 01:21 - edited

Thanks.I'll check it out shortly.
There has been a change related to before triggers, so it's quite possible.

Elena Stepanova added a comment - 2016-02-02 01:21 - edited Thanks.I'll check it out shortly. There has been a change related to before triggers, so it's quite possible.

Elena Stepanova added a comment - 2016-02-03 15:31 - edited

ampf, thanks for the data, I can reproduce the problem.

The problem appeared with the fix for ~~MDEV-8605~~.
I'm intentionally leaving the test case as is, unsimplified, to avoid deviating from the original problem.

The only two changes:

Before running UPDATE, 'root'@'%' needs to be created and granted corresponding permissions, as the trigger has it as a definer. In its absence the problem is still reproducible, but versions which are not affected cannot run the UPDATE when the user is missing.

The provided data does not completely correspond the UPDATE from the original description, there is a missing column in WHERE. But it does not affect the outcome, this query does not use the column and still shows the problem:

UPDATE tbl_stock_grupo_controlo

SET tbl_stock_grupo_controlo.MSVArm71 = (

  SELECT MIN(TIMESTAMPDIFF(MONTH,det.`Data`, CURDATE()))

  FROM MCPC_SI.tbl_facturas fac, MCPC_SI.tbl_facturas_detalhe det, MCLP_SI.tbl_armazens_rel rel

  WHERE

    fac.CodCliente <> rel.CodCliente

    AND ( fac.CtaPostal = '' OR IFNULL(fac.CtaPostal,'') <> IFNULL(rel.CtaPostal,'') )

    AND tbl_stock_grupo_controlo.Armazem = rel.ArmazemDestino

    AND fac.Armazem = rel.ArmazemOrigem

    AND fac.Factura = det.Factura

    AND tbl_stock_grupo_controlo.v_ReferenciaCompleta = det.Referencia

    AND (det.Marca = rel.Marca OR rel.Marca = '')

    AND det.Atendedor <> 'VEND.INTERNET'

    AND det.`Data` >= '2012-01-01'

  GROUP BY tbl_stock_grupo_controlo.Armazem, det.Referencia

);

Elena Stepanova added a comment - 2016-02-03 15:31 - edited ampf , thanks for the data, I can reproduce the problem. The problem appeared with the fix for MDEV-8605 . I'm intentionally leaving the test case as is, unsimplified, to avoid deviating from the original problem. The only two changes: Before running UPDATE , 'root'@'%' needs to be created and granted corresponding permissions, as the trigger has it as a definer. In its absence the problem is still reproducible, but versions which are not affected cannot run the UPDATE when the user is missing. The provided data does not completely correspond the UPDATE from the original description, there is a missing column in WHERE. But it does not affect the outcome, this query does not use the column and still shows the problem: UPDATE tbl_stock_grupo_controlo SET tbl_stock_grupo_controlo.MSVArm71 = ( SELECT MIN(TIMESTAMPDIFF(MONTH,det.`Data`, CURDATE())) FROM MCPC_SI.tbl_facturas fac, MCPC_SI.tbl_facturas_detalhe det, MCLP_SI.tbl_armazens_rel rel WHERE fac.CodCliente <> rel.CodCliente AND ( fac.CtaPostal = '' OR IFNULL(fac.CtaPostal,'') <> IFNULL(rel.CtaPostal,'') ) AND tbl_stock_grupo_controlo.Armazem = rel.ArmazemDestino AND fac.Armazem = rel.ArmazemOrigem AND fac.Factura = det.Factura AND tbl_stock_grupo_controlo.v_ReferenciaCompleta = det.Referencia AND (det.Marca = rel.Marca OR rel.Marca = '') AND det.Atendedor <> 'VEND.INTERNET' AND det.`Data` >= '2012-01-01' GROUP BY tbl_stock_grupo_controlo.Armazem, det.Referencia );

Antonio Fernandes added a comment - 2016-02-03 17:10 - edited

Sorry for that. I've trimmed the data because the 'original' takes 2Gb and I've missed either not removing that columns or removing it from the reported query.

Antonio Fernandes added a comment - 2016-02-03 17:10 - edited Sorry for that. I've trimmed the data because the 'original' takes 2Gb and I've missed either not removing that columns or removing it from the reported query.

Antonio Fernandes added a comment - 2016-02-23 23:49

Any way I can access this build?

Antonio Fernandes added a comment - 2016-02-23 23:49 Any way I can access this build?

People

Assignee:: Sergei Golubchik

Reporter:: Antonio Fernandes

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-01-31 22:25

Updated:: 2016-02-23 23:49

Resolved:: 2016-02-23 12:00

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server