bakert
commented
5 years ago Failed to execute `
SELECT
p.id,
LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
num_decks,
wins,
losses,
draws,
perfect_runs,
tournament_wins,
tournament_top8s,
win_percent,
num_competitions
FROM
person AS p
LEFT JOIN
(
SELECT
d.person_id,
COUNT(d.id) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
deck AS d
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
LEFT JOIN
(
SELECT
`start`.id,
`start`.code,
`start`.start_date AS start_date,
`end`.start_date AS end_date
FROM
season AS `start`
LEFT JOIN
season AS `end` ON `end`.id = `start`.id + 1
) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
WHERE
season.id = 10
GROUP BY
d.person_id
) AS stats ON p.id = stats.person_id
WHERE
p.mtgo_username = 610
GROUP BY
p.id
ORDER BY
`num_decks` DESC, name
` with `[]` because of `(1292, "Truncated incorrect DOUBLE value: 'Daviajagunan'")`
Reported on decksite by logged_out```
--------------------------------------------------------------------------------
Request Method: GET
Path: /people/610/?
Cookies: {'__cfduid': 'da99bc70f543ab5a0e9f35a66ac0a72ce1540596051', 'session': 'eyJsb2NhbGUiOiJydSJ9.DrUy_Q.iakUPxO3mW5BhOXy1ecMt9EhCds'}
Endpoint: person
View Args: {'person_id': '610'}
Person: logged_out
Referrer: None
Request Data: {}
Host: pennydreadfulmagic.com
Accept-Encoding: gzip
Cf-Ipcountry: DE
X-Forwarded-For: 2a01:4f8:150:2314:0:0:0:2, 162.158.88.112
Cf-Ray: 4700a91bce536457-FRA
X-Forwarded-Proto: https
Cf-Visitor: {"scheme":"https"}
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8
User-Agent: Mozilla/5.0 (compatible; BLEXBot/1.0; +http://webmeup-crawler.com/)
Cookie: __cfduid=da99bc70f543ab5a0e9f35a66ac0a72ce1540596051; session=eyJsb2NhbGUiOiJydSJ9.DrUy_Q.iakUPxO3mW5BhOXy1ecMt9EhCds
Cf-Connecting-Ip: 2a01:4f8:150:2314:0:0:0:2
X-Forwarded-Host: pennydreadfulmagic.com
X-Forwarded-Server: pennydreadfulmagic.com
Connection: Keep-Alive
--------------------------------------------------------------------------------
DatabaseException
Failed to execute `
SELECT
p.id,
LOWER(IFNULL(IFNULL(IFNULL(p.name, p.mtgo_username), p.mtggoldfish_username), p.tappedout_username)) AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
num_decks,
wins,
losses,
draws,
perfect_runs,
tournament_wins,
tournament_top8s,
win_percent,
num_competitions
FROM
person AS p
LEFT JOIN
(
SELECT
d.person_id,
COUNT(d.id) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
deck AS d
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
LEFT JOIN
(
SELECT
`start`.id,
`start`.code,
`start`.start_date AS start_date,
`end`.start_date AS end_date
FROM
season AS `start`
LEFT JOIN
season AS `end` ON `end`.id = `start`.id + 1
) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
WHERE
season.id = 10
GROUP BY
d.person_id
) AS stats ON p.id = stats.person_id
WHERE
p.mtgo_username = 610
GROUP BY
p.id
ORDER BY
`num_decks` DESC, name
` with `[]` because of `(1292, "Truncated incorrect DOUBLE value: 'Daviajagunan'")`
Stack Trace:
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 2309, in call
return self.wsgi_app(environ, start_response)
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 2295, in wsgi_app
response = self.handle_exception(e)
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
response = self.full_dispatch_request()
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/discord/.local/lib/python3.6/site-packages/flask/compat.py", line 35, in reraise
raise value
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
rv = self.dispatch_request()
File "/home/discord/.local/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
return self.view_functionsrule.endpoint
File "./decksite/cache.py", line 79, in decorated_function
response = make_response(f(*args, **kwargs))
File "./decksite/main.py", line 85, in person
p = ps.load_person_by_id_or_mtgo_username(person_id, season_id=get_season_id())
File "./decksite/data/person.py", line 38, in load_person_by_id_or_mtgo_username
return load_person_by_mtgo_username(person, season_id)
File "./decksite/data/person.py", line 26, in load_person_by_mtgo_username
return load_person('p.mtgo_username = {username}'.format(username=sqlescape(username)), season_id=season_id)
File "./decksite/data/person.py", line 81, in load_person
person = guarantee.exactly_one(load_people(where, season_id=season_id))
File "./decksite/data/person.py", line 139, in load_people
people = [Person(r) for r in db().select(sql)]
File "./shared/database.py", line 41, in select
[, rows] = self.execute_anything(sql, args)
File "./shared/database.py", line 58, in execute_anything
raise DatabaseException('Failed to execute {sql} with {args} because of {e}'.format(sql=sql, args=args, e=e))
This is related to the "only query on username or discord or id not all three" refactor that I did a couple days ago.