Django Filtering MySQL Warnings

Question

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..

Answer 1

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

Answer 2

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