Csaba Toth
Csaba Toth

Reputation: 10719

How to avoid MySQL warning to be escalated to an Exception in a Django application

There's a column in a table which has varchar(5) type, it usually contains numbers in string format. Sometimes it contains some extra characters, like "5 CA". We convert that column into a number for sorting purposes, and that is done with an extra query:

qs.extra(select={'pnumsort':'main_person.player_number+0'}).order_by('pnumsort', "player_number")

The problem is that the warning caused by this query is escalated into an Exception on my developer box, so the request fails. The warning:

Warning at /players/data/players_table/ Truncated incorrect DOUBLE value: '5 CA'

There's such a warning for every row where the content of that player number column has some extra non numeric characters. The mystery is that my fellow developer colleague doesn't get the error at all on his box (he may have warnings but they are not escalated to errors). The test system has the error, fortunately the live does not.

What I've found out so far:

  1. I get the same warning if I try to use qs.extra(select={'pnumsort':'CAST(main_person.player_number AS UNSIGNED)'}).order_by('pnumsort', "player_number") or qs.extra(select={'pnumsort':'CONVERT(main_person.player_number, UNSIGNED)'}).order_by('pnumsort', "player_number"). And because of the warnign it still causes error on my system.

  2. There's a Django ticket dated back 5 years ago: https://code.djangoproject.com/ticket/12293 It seems that this behavior (MySql warnings escalated to error) supposed to be normal if DEBUG mode is on in the Django Python application. But why my colleague doesn't get the error then?

  3. The behavior doesn't seem to be dependent either on the MySql version, the Django version or the MySql-python version.

    box | MySql version | MySql python version | Django DEBUG | problem

    my box | 5.6.24 | 1.2.5 final | True | Yes

    colleague | 5.1 | 1.2.3 final | True | No

    test | 5.5.38 | 1.2.3 final | True | Yes

    live | 5.5.x | 1.2.5 final | False | No

  4. I tried to turn off MySQL strict mode (although it's not advised). My colleague doesn't have anything set in his sql_mode (a MySql configuration setting Disabling MySQL Strict Mode). Clearing sql_mode didn't help either, although interestingly the PyCharm Database connection view tells that it picks up the STRICT mode for its session (SELECT @@SESSIon.slq_mode). But PyCharm uses JDBC connection to MySQL, so it's a totally different thing than Django and MySQL-python.

How to avoid the error (because of MySQL warnings) on my box and the test server? And why my colleague doesn't have this issue?

The servers are Linux, and the developer environments are Windows.

Upvotes: 0

Views: 952

Answers (1)

Bryant Cardwell
Bryant Cardwell

Reputation: 26

You can have your warning ignored with code like this somewhere before the query is executed.

from warnings import filterwarnings
import MySQLdb as Database
filterwarnings('ignore', message="^Truncated incorrect DOUBLE value:.*", 
                category = Database.Warning)

see https://docs.python.org/2/library/warnings.html for more information

Upvotes: 1

Related Questions