[MDEV-9500] Bug after upgrade to 10.1.10 (and 10.1.11) Created: 2016-01-31  Updated: 2016-02-23  Resolved: 2016-02-23

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.1.10, 10.1.11, 10.1
Fix Version/s: 10.1.12

Type: Bug Priority: Major
Reporter: Antonio Fernandes Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS x86_64

Issue Links:
PartOf
includes MDEV-9332 Bug after upgrade to 10.1.10 Closed
Problem/Incident
is caused by MDEV-8605 MariaDB not use DEFAULT value even wh... Closed
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

 Comments   
Comment by Antonio Fernandes [ 2016-02-01 ]

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' */

Comment by Elena Stepanova [ 2016-02-01 ]

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.

Comment by Antonio Fernandes [ 2016-02-02 ]

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...

Comment by Elena Stepanova [ 2016-02-02 ]

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

Comment by Elena Stepanova [ 2016-02-03 ]

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 
);

Comment by Antonio Fernandes [ 2016-02-03 ]

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.

Comment by Antonio Fernandes [ 2016-02-23 ]

Any way I can access this build?

Generated at Thu Feb 08 07:35:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.