[MDEV-25182] Complex query in Store procedure corrupts results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10
Component/s: Prepared Statements, Stored routines
Labels:
None

Description

When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

| customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |

+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

| reference1        |            1 | NULL            | NULL       | NULL                 | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL           |                   0 |

+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:

mysql ds2 -e "select * from results where customerReference<>'reference1'"

+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

| customerReference              | policyNumber | schemeReference | schemeName | applicationReference                               | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate      | isBusinessException |

+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

| w  (       ??      0??K    |            0 | NULL            | NULL       | NULL                                               | 2020-01-01 10:10:10     | NULL                  |   2147483647 |            NULL |   NULL |        0 | NULL                |                NULL |

|                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |

| w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |        0 | 0000-00-00 00:00:00 |                NULL |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  rence1                     |            1 |                 | NULL       | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |      1 |     NULL | NULL                |                NULL |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  rence1                     |            1 |                 |            | NULL                                               | NULL                    | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  rence1                     |            1 |                 | NULL       |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |

|                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  rence1                     |            1 |                 | NULL       |                                                    | NULL                    | NULL                  |            1 |               1 |      1 |        0 | NULL                |                NULL |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

| w  (       ??      0??K    |            0 | NULL            | NULL       |                                                    | 2020-01-01 10:10:10     | NULL                  |   2147483647 |               0 |      1 |        0 | 7036-87-44 17:76:64 |                NULL |

| w  rence1                     |            1 |                 |            | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |   2147483647 |               0 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            0 |                 |            | ?K                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

|                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |

+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

CS0236378.zip
4 kB
2021-03-17 21:47

Issue Links

is duplicated by

MDEV-23130 Server crashes on 2nd execution of sp

Closed

Activity

Ascending order - Click to sort in descending order

Kyle Joiner (Inactive) created issue - 2021-03-17 21:50

Kyle Joiner (Inactive) made changes - 2021-03-17 21:54

Field	Original Value	New Value
Description	When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion. Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows: +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ \| customerReference \| policyNumber \| schemeReference \| schemeName \| applicationReference \| modifiedCreatedDateTime \| demandCreatedDateTime \| countDemands \| genericDemandId \| status \| isUrgent \| reactivateDate \| isBusinessException \| +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ \| reference1 \| 1 \| NULL \| NULL \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| 0 \| +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ Under high concurrency (should be a static result) from 1000 iterations the following rows were inserted: mysql ds2 -e "select * from results where customerReference<>'reference1'" +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+ \| customerReference \| policyNumber \| schemeReference \| schemeName \| applicationReference \| modifiedCreatedDateTime \| demandCreatedDateTime \| countDemands \| genericDemandId \| status \| isUrgent \| reactivateDate \| isBusinessException \| +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+ \| w ( ?? 0??K \| 0 \| NULL \| NULL \| NULL \| 2020-01-01 10:10:10 \| NULL \| 2147483647 \| NULL \| NULL \| 0 \| NULL \| NULL \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| w rence1 \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| NULL \| NULL \| 0 \| 0000-00-00 00:00:00 \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| NULL \| 1 \| NULL \| NULL \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| \| NULL \| NULL \| 2020-01-01 10:10:10 \| 1 \| NULL \| NULL \| NULL \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| \| NULL \| NULL \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w ( ?? 0??K \| 0 \| NULL \| NULL \| \| 2020-01-01 10:10:10 \| NULL \| 2147483647 \| 0 \| 1 \| 0 \| 7036-87-44 17:76:64 \| NULL \| \| w rence1 \| 1 \| \| \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| NULL \| NULL \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 2147483647 \| 0 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| ?K \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+	When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion. Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows: +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ \| customerReference \| policyNumber \| schemeReference \| schemeName \| applicationReference \| modifiedCreatedDateTime \| demandCreatedDateTime \| countDemands \| genericDemandId \| status \| isUrgent \| reactivateDate \| isBusinessException \| +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ \| reference1 \| 1 \| NULL \| NULL \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| 0 \| +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+ Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted: mysql ds2 -e "select * from results where customerReference<>'reference1'" +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+ \| customerReference \| policyNumber \| schemeReference \| schemeName \| applicationReference \| modifiedCreatedDateTime \| demandCreatedDateTime \| countDemands \| genericDemandId \| status \| isUrgent \| reactivateDate \| isBusinessException \| +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+ \| w ( ?? 0??K \| 0 \| NULL \| NULL \| NULL \| 2020-01-01 10:10:10 \| NULL \| 2147483647 \| NULL \| NULL \| 0 \| NULL \| NULL \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| w rence1 \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| NULL \| NULL \| 0 \| 0000-00-00 00:00:00 \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| NULL \| 1 \| NULL \| NULL \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| \| NULL \| NULL \| 2020-01-01 10:10:10 \| 1 \| NULL \| NULL \| NULL \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w rence1 \| 1 \| \| NULL \| \| NULL \| NULL \| 1 \| 1 \| 1 \| 0 \| NULL \| NULL \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| w ( ?? 0??K \| 0 \| NULL \| NULL \| \| 2020-01-01 10:10:10 \| NULL \| 2147483647 \| 0 \| 1 \| 0 \| 7036-87-44 17:76:64 \| NULL \| \| w rence1 \| 1 \| \| \| NULL \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| NULL \| NULL \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 2147483647 \| 0 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 0 \| \| \| ?K \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| \| \| 1 \| \| \| \| 2020-01-01 10:10:10 \| 2020-01-01 10:10:10 \| 1 \| 1 \| 1 \| 0 \| 0000-00-00 00:00:00 \| 0 \| +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

Alice Sherepa made changes - 2021-03-18 12:15

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-03-18 12:15

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-03-18 13:18

Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]

Alice Sherepa made changes - 2021-03-18 13:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2021-03-18 13:57

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik made changes - 2021-03-19 10:57

Description

When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
| customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
| reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:
mysql ds2 -e "select * from results where customerReference<>'reference1'"
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
| customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
| w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
| w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
| w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:
{noformat}
+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
| customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
| reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
+-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
{noformat}
Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:
{noformat}
mysql ds2 -e "select * from results where customerReference<>'reference1'"
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
| customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
| w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
| w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
| w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
| | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
+--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
{noformat}

Sergei Golubchik made changes - 2021-03-19 11:28

Priority

Critical [ 2 ]

Blocker [ 1 ]

Oleksandr Byelkin made changes - 2021-03-25 09:09

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Julien Fritsch made changes - 2021-03-31 14:51

Labels

need_feedback

Chris Calender (Inactive) made changes - 2021-03-31 16:04

Labels

need_feedback

Oleksandr Byelkin made changes - 2021-04-07 09:32

Component/s

Prepared Statements [ 10804 ]

Oleksandr Byelkin made changes - 2021-04-07 10:19

Summary

Complex query in Store procedure corrupts results under high concurrency

Complex query in Store procedure corrupts results

Oleksandr Byelkin made changes - 2021-04-07 11:01

Assignee	Oleksandr Byelkin [ sanja ]	Igor Babaev [ igor ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Igor Babaev (Inactive) made changes - 2021-04-12 08:20

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2021-04-12 08:20

Assignee

Igor Babaev [ igor ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2021-04-12 15:44

Fix Version/s		10.2.38 [ 25207 ]
Fix Version/s		10.3.29 [ 25206 ]
Fix Version/s		10.4.19 [ 25205 ]
Fix Version/s		10.5.10 [ 25204 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]

Oleksandr Byelkin made changes - 2021-04-12 15:45

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:53

Workflow

MariaDB v3 [ 120234 ]

MariaDB v4 [ 159045 ]

Rex Johnston made changes - 2023-08-07 03:49

Link

This issue duplicates ~~MDEV-23130~~ [ ~~MDEV-23130~~ ]

Roel Van de Paar made changes - 2023-08-07 06:11

Link

This issue is duplicated by ~~MDEV-23130~~ [ ~~MDEV-23130~~ ]

Roel Van de Paar made changes - 2023-08-07 06:11

Link

This issue duplicates ~~MDEV-23130~~ [ ~~MDEV-23130~~ ]

Jira Automation (IT) made changes - 2024-07-04 02:34

Zendesk Related Tickets

144504

People

Assignee:: Oleksandr Byelkin

Reporter:: Kyle Joiner (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2021-03-17 21:50

Updated:: 2024-07-07 21:27

Resolved:: 2021-04-12 15:45

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration