Django Filtering MySQL Warnings

django queryset
django filter in list
django db connection queries
django-filter example
django-filter foreign key
django queryset get field value
django orm
django database tutorial

Before you all point me to here and here mine a bit different. So I started getting the famous error after shifting to my production server.

django/db/backends/mysql/base.py:86: Warning: Data truncated for column 'slug' at row 1

The first thing I did was start googling this after I fixed the problem. To fix this, I tweaked both the models to have a max_length of 128 ad then updated the SQL tables to match it. But the problem persisted.. Somewhat confident that I actually fixed the problem I figured I'd just as well start filtering them out. So at the top of my script I placed this.

# Get rid of the MySQLdb warnings
import warnings
import MySQLdb
with warnings.catch_warnings():
    warnings.filterwarnings("ignore", category=MySQLdb.Warning)

And I happily pushed this off to production. Guess what - you guessed it the problem remained. So now what. I am quickly loosing confidence that I did in fact fix the problem but a double check of that shows that all the slug columns are 128 chars long. Furthermore I wrapped sluggify to error if it is longer than 128 and still nothing. So 2 questions:

  1. How can I nail down what operation is flagging this. i.e. where in my code is the flag getting raised?

  2. How could I really filter these out? My fix isn't working? Is this really a MySQLdb warning or a django.db.mysql.base warning?

Thanks and happy Django hacking!

For those who have questions on the structure..

CREATE TABLE `people_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(75) DEFAULT NULL,
  `location_id` varchar(100) DEFAULT NULL,
  `jpeg` longtext,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `maildomain` varchar(32) DEFAULT NULL,
  `mailserver` varchar(32) DEFAULT NULL,
  `mailfile` varchar(64) DEFAULT NULL,
  `contractor` tinyint(1) NOT NULL,
  `temporary` tinyint(1) NOT NULL,
  `formal_name` varchar(100) DEFAULT NULL,
  `nickname` varchar(32) DEFAULT NULL,
  `cell_phone` varchar(32) DEFAULT NULL,
  `office_phone` varchar(32) DEFAULT NULL,
  `other_phone` varchar(32) DEFAULT NULL,
  `fax` varchar(32) DEFAULT NULL,
  `assistant_id` int(11) DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `is_supervisor` tinyint(1) NOT NULL,
  `department_id` varchar(100) DEFAULT NULL,
  `division_id` varchar(100) DEFAULT NULL,
  `section_id` varchar(100) DEFAULT NULL,
  `job_classification_id` varchar(100) DEFAULT NULL,
  `functional_area_id` varchar(100) DEFAULT NULL,
  `position_id` varchar(100) DEFAULT NULL,
  `notes_url` varchar(200) DEFAULT NULL,
  `ldap_active` tinyint(1) NOT NULL,
  `notes_active` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `last_update` datetime NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `site_id` int(11) NOT NULL,
  `slug` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `people_employee_location_id` (`location_id`),
  KEY `people_employee_assistant_id` (`assistant_id`),
  KEY `people_employee_supervisor_id` (`supervisor_id`),
  KEY `people_employee_department_id` (`department_id`),
  KEY `people_employee_division_id` (`division_id`),
  KEY `people_employee_section_id` (`section_id`),
  KEY `people_employee_job_classification_id` (`job_classification_id`),
  KEY `people_employee_functional_area_id` (`functional_area_id`),
  KEY `people_employee_position_id` (`position_id`),
  KEY `people_employee_site_id` (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1429 DEFAULT CHARSET=latin1;

And the relevant models.py.

slug = models.SlugField(max_length=128, editable=False, unique=True)

Hope that helps..

First, I'd strongly recommend against filtering warnings like this: this error is generated by MySQL and it absolutely means you are losing data.

The first thing to do would be using the MySQL describe command to make sure that your database column is actually defined to the same size you're expecting: Django has no support for database migrations if you change the length of a column so if your slug field was ever shorter than it is now you'd need to manually alter the table to set the new length:

mysql> DESCRIBE my_table slug;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| slug  | varchar(255) | NO   | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

If that field wasn't what you expected you could simply solve the problem by updating the column length:

mysql> ALTER TABLE my_table MODIFY slug VARCHAR(255) NOT NULL;

If you allow Unicode characters in your slugs, that could also explain it as your dump above appears to be using the latin1 character set rather than UTF-8 – a single UTF-8 character can be up to 4 bytes of data, meaning that a value as short as 17 bytes could overflow a VARCHAR(64).

One next debugging step is a simple variation on the call you're making to filter warnings to figure out exactly where your errors are happening:

warnings.simplefilter("error",  category=MySQLdb.Warning)

This will make the warning fatal, which will halt your program, but more importantly will also produce a stacktrace. With something like this, you'll see the output below:

#!/usr/bin/env python
import warnings

def foo():
    warnings.warn("uhoh")

def bar():
    foo()

def main():
    warnings.simplefilter("error", UserWarning)
    bar()

if __name__ == "__main__":
    main()

Without the simplefilter call:

cadams@Io:~ $ python test_warnings.py 
test_warnings.py:5: UserWarning: uhoh
  warnings.warn("uhoh")

With the simplefilter call:

cadams@Io:~ $ python test_warnings.py 
Traceback (most recent call last):
  File "test_warnings.py", line 15, in <module>
    main()
  File "test_warnings.py", line 12, in main
    bar()
  File "test_warnings.py", line 8, in bar
    foo()
  File "test_warnings.py", line 5, in foo
    warnings.warn("uhoh")
UserWarning: uhoh

Security in Django | Django documentation, If the query does not return rows, a (possibly cryptic) error will result. Warning. If you are performing queries on MySQL, note that MySQL's silent type coercion  UPDATE ticket SET balance = 10.00; UPDATE ticket SET balance = balance - 1.79; SHOW WARNINGS; It seems that MySQL doesn't like strings as much as it likes floats and Django converts Decimal() to strings. So. Account.objects.create(name='z1', balance='10.00') Account.objects.filter(name__startswith='z').update(balance=F('balance') - Decimal('1.79')) or

I would edit my project's settings.py file to let this behavior happen across my whole django project. Otherwise, i could simply include it in a part of the script where i want this behavior to happen.


Raise MySQL Warnings as errors:

import warnings, MySQLdb
warnings.filterwarnings('error', category=MySQLdb.Warning)

To ignore instead of raising an error, replace "error" with "ignore".

Handle them in a try-except block like:

try:
    # a MySQL DB operation that raises a warning
    # for example: a data truncated warning
except Warning as a_warning:
    # do something here

How to use Django with MongoDB by adding just one line of code., __str__() does not generate valid MySQL query with dates from datetime import date >>> from django.db import connection >>> from myblog.models import Entry This query also fails with a warning in the MySQL command line: The patch (to be attached) is fairly heavily commented, and I intend to post a reference to this bug on django-users and encourage people to test it. Changes. requires MySQLdb-1.2.1 and newer. eliminates MysqlDebugWrapper in favor of the standard util.CursorDebugWrapper and using warning filtering to raise warnings as exceptions.

Had a similar issue, but with different modules (e.g. gis module not found when importing floppyforms.) These were truly superfluous to our application, so I added the following to our settings file:

# Set up warnings filters
# (action, re-pattern, category)   See warnings module docu.  Python std lib 28.6)

WARNINGS_FILTERS = [
    ("ignore", "Unable to import floppyforms.gis, geometry widgets not available", UserWarning),
    ("ignore", "There are known rendering problems with Cairo <= 1.14.0", UserWarning),
    ("ignore", "@font-face support needs Pango >= 1.38", UserWarning),
]

import warnings

for wf in WARNINGS_FILTERS:
    warnings.filterwarnings(*wf)

Iterating over a list like this gives me a simpler, cleaner way to add / modify / remove warnings in the future.

NOTE: there is a negligible bit of "extra" processing each first time settings are imported (new worker kicks off, say), but, the warnings module will replace existing warnings if append=False, which is the default. In other words, multiple imports of settings won't result in multiple, identical filters being registered.

Warning. The query cache is disabled by default in MySQL 5.7.20+ and MariaDB 10.1.7+, and removed in MySQL 8.0+. Example usage: # Fetch recent posts  Sum ('amount')). filter (sumamount__gt = 3) print qs #displays 0 results when using sqlite + Django 1.4, displays 1 result when using mysql or Django 1.3.1 comment:2 Changed 8 years ago by anonymous

work fine with it like Mariadb, MySQL-server, oracle SQL etc. I preferably used postresql. One next debugging step is a simple variation on the call you're making to filter warnings to figure out exactly where your errors are happening: warnings.simplefilter("error", category=MySQLdb.Warning) This will make the warning fatal, which will halt your program, but more importantly will also produce a stacktrace.

Displays errors, warnings and notes. SHOW WARNINGS can be used after EXPLAIN EXTENDED to see how a query is internally rewritten by MariaDB. If the sql_notes The mysql client also has a number of options related to warnings. From MySQL 5.7 onwards and on fresh installs of MySQL 5.6, the default value of the sql_mode option contains STRICT_TRANS_TABLES. That option escalates warnings into errors when data are truncated upon insertion, so Django highly recommends activating a strict mode for MySQL to prevent data loss (either STRICT_TRANS_TABLES or STRICT_ALL_TABLES).

10.2.44 MySQLConnection.raise_on_warnings Property. This property can be The following example shows the execution of a query that produces a warning: MySQL 8.0 Reference Manual. Preface and Legal Notices. General Information. Installing and Upgrading MySQL. Tutorial. MySQL Programs. log_filter_internal:

Comments
  • Are the field(s) this data can come from have text limits imposed?
  • No - I am setting the slug on save. I am pushing out a warning if the length is > 128 (but it's not..).
  • Can you post the output of your table's describe (from the production box) and the Model you're using?
  • Can you paste the query where the warning messages are generating?
  • Wow - Talk about a revival! Nice Job! I had already solved it (failed to update this site). It was the UTF-8/Latin issue that you mentioned. What a PITA it was to find it. Great hints here though!