I am using MySQL 5.7 and trying to emulate rank(which is not available in 5.7) using JSON variable.
The problem is that in Laravel DB or PDO query below doesn't work correctly but works fine in TablePlus.
The idea of a query to count rows by groups and filter rows which have counter less than 250. Every group rows have it own counters in json variable.
The idea of this query:
select result.*
from (
select
@extracted_counter:=JSON_EXTRACT(@row_number, CONCAT('game', t.game_id)) as extracted_counter,
JSON_SET(@row_number, CONCAT('key', t.game_id), @extracted_counter 1) as counters,
t.*
from (@row_number := '{"game1": 0, "game27": 0}') as x,
table_name as t
where t.app_id = 16325
) as result
where result.extracted_counter < 250;
@json_variable build from another table. I made it as a string to simplify.
Again, the original query is working ok in TablePlus program but when it comes to Laravel 5.4 DB, @row_number can't perform addition more than 3, for instance:
"{"game27718": 0,"game27398": 3,"game26592": 0,"game24042": 0,"game23957": 3}"
I thought that Laravel uses specific PDO settings but couldn't find anything. Also, I've tried to use CAST but it didn't help really since addition is working for first three records.
Not really sure how is different settings for TablePlus and PDO Laravel. I've tried to google settings of TablePlus but I can't imagine how can I google it correctly to not bump into interface docs.
I would appreciate any help.
The original query I am trying to use looks like this:
SELECT temp.*
FROM (
SELECT
@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS extracted_rank,
if(
@extracted_counter < @games_length,
@row_number:=JSON_SET(
@row_number,
CONCAT('$.game', sao.game_id),
@extracted_counter 1
),
@const_length
) AS rnk,
sao.email, sao.name,
sao.game_id, sao.order_total_price,
sao.device_type, sao.coupon_id,
sao.coupon_code, sao.browser,
sao.city, sao.country,
sao.order_id, sao.created_at
FROM (SELECT @row_number := (
SELECT concat('{', group_concat(concat('"game', s.game_id, '"'), ': 0'), '}') AS str_json
FROM (
SELECT g.id AS game_id
FROM users AS u
LEFT JOIN games AS g ON u.id = g.user_id
WHERE u.app_id = 16325
AND g.deleted_at IS NULL
) AS s
)) AS x,
(SELECT @games_length := 250) AS games_length,
(SELECT @const_length := @games_length 100) AS const_length,
shopify_app_orders as sao
WHERE sao.app_id = 16325
AND sao.coupon_id > 0
ORDER BY sao.id DESC
) AS temp
LEFT JOIN coupons AS c ON c.id = temp.coupon_id
WHERE temp.extracted_rank < @games_length
UPDATE
I've discovered with user_variables_by_thread that there is variables that is still in MySQL before I am querying database.
Variables before query execute:
array:5 [
0 => {#1171
"THREAD_ID": "2314"
"VARIABLE_NAME": "extracted_counter"
"VARIABLE_VALUE": null
}
1 => {#1172
"THREAD_ID": "2314"
"VARIABLE_NAME": "const_length"
"VARIABLE_VALUE": "350"
}
2 => {#1173
"THREAD_ID": "2314"
"VARIABLE_NAME": "games_length"
"VARIABLE_VALUE": "250"
}
3 => {#1174
"THREAD_ID": "2314"
"VARIABLE_NAME": "parse"
"VARIABLE_VALUE": "{"game27718": 0,"game27398": 0,"game26592": 0,"game24042": 0,"game23957": 0}"
}
4 => {#1175
"THREAD_ID": "2314"
"VARIABLE_NAME": "row_number"
"VARIABLE_VALUE": "{"game23957": 250.0, "game24042": 250.0, "game26592": 250.0, "game27398": 250.0, "game27718": 250.0}"
}
]
Variables after query were executed:
array:9 [
0 => {#1178
"THREAD_ID": "2419"
"VARIABLE_NAME": "extracted_counter"
"VARIABLE_VALUE": null
}
1 => {#1176
"THREAD_ID": "2419"
"VARIABLE_NAME": "const_length"
"VARIABLE_VALUE": "350"
}
2 => {#1179
"THREAD_ID": "2419"
"VARIABLE_NAME": "games_length"
"VARIABLE_VALUE": "250"
}
3 => {#1180
"THREAD_ID": "2419"
"VARIABLE_NAME": "row_number"
"VARIABLE_VALUE": "{"game23957": 3, "game24042": 3, "game26592": 3, "game27398": 3, "game27718": 3}"
}
4 => {#1181
"THREAD_ID": "2314"
"VARIABLE_NAME": "extracted_counter"
"VARIABLE_VALUE": null
}
5 => {#1182
"THREAD_ID": "2314"
"VARIABLE_NAME": "const_length"
"VARIABLE_VALUE": "350"
}
6 => {#1183
"THREAD_ID": "2314"
"VARIABLE_NAME": "games_length"
"VARIABLE_VALUE": "250"
}
7 => {#1184
"THREAD_ID": "2314"
"VARIABLE_NAME": "parse"
"VARIABLE_VALUE": "{"game27718": 0,"game27398": 0,"game26592": 0,"game24042": 0,"game23957": 0}"
}
8 => {#1177
"THREAD_ID": "2314"
"VARIABLE_NAME": "row_number"
"VARIABLE_VALUE": "{"game23957": 250.0, "game24042": 250.0, "game26592": 250.0, "game27398": 250.0, "game27718": 250.0}"
}
]
There is at least tow row_number variables within different thread_id. Not really sure what to do with existed variables.
Is it possible to clear performance_schema.user_variables_by_thread? Will it break mysql?
UPDATE 2
I've closed TablePlus connection with database and variables before query disappeared. After query is finished duplicated variables disappeared:
array:4 [
0 => {#1184
"THREAD_ID": "2422"
"VARIABLE_NAME": "extracted_counter"
"VARIABLE_VALUE": null
}
1 => {#1183
"THREAD_ID": "2422"
"VARIABLE_NAME": "const_length"
"VARIABLE_VALUE": "350"
}
2 => {#1182
"THREAD_ID": "2422"
"VARIABLE_NAME": "games_length"
"VARIABLE_VALUE": "250"
}
3 => {#1181
"THREAD_ID": "2422"
"VARIABLE_NAME": "row_number"
"VARIABLE_VALUE": "{"game23957": 3, "game24042": 3, "game26592": 3, "game27398": 3, "game27718": 3}"
}
]
Not really sure why but extracted_counter is null though it should have value.
CodePudding user response：
I am not really sure what to do with that opened post and if somebody will ended up with the same issue at all but I found where was the issue.
For some reasons type auto cast is worked in TablePlus and it didn't work for PDO, so this:
CAST(@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS UNSIGNED) AS extracted_rank
and this
@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS extracted_rank
are not the same.
First one will produce the integer, second one will give you a string.
By the way, next two statements is not the same either:
@extracted_counter < @games_length
@extracted_counter < CAST(@games_length AS UNSIGNED)
even though @games_length where declared like:
(SELECT @games_length := 250) AS games_length,