Re: Trouble setting up website with mysql as backend

On 03/17/2012 04:49 PM, Swaroop Shankar V wrote:
> Hello All,
> I was developing a website and the development was all done using sqlite
> database. Now the development is almost complete so i need to test the
> site using mysql. When i did a syncdb on a fresh database i got the
> following error:
[snip]
> _mysql_exceptions.Warning: Data truncated for column 'name' at row 1
>
> As you can see its a mysql warning since i had changed mysql mode to non
> strict.

Hello,

Others have already explained why this happened. Let me add that you
should not run MySQL in non-strict mode. Django is actually saving you
from creating junk data in this case but that would not necessarily be
the case if you were to do insert or update operations on that same
database with something other than Django.

MySQL used to have a "feature", which I considered a bug, whereby it
would happily accept a string longer than the width of the column in
which that data was to be stored and not even mention that it had
truncated your string to fit into that particular column.

Recent versions of MySQL allow you to set the database to non-strict
mode whereby it will still allow you to insert a string longer than the
width of a column but at least, it will generate a warning message.
Older versions would just silently truncate. Either scenario is
unacceptable in a database. Why would you want the database to tell you
after-the-fact, "Oh, by the way, I just screwed up your data."
after-the-fact? The first job of a database should be to protect data
integrity so if you're attempting to insert 51 characters into a
varchar(50) column, it should not accept it under any circumstances and
throw an exception.

The MySQL docs
<http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html>, which
I've quoted below, explains this.

###
TRADITIONAL

Make MySQL behave like a "traditional" SQL database system. A
simple description of this mode is "give an error instead of a warning"
when inserting an incorrect value into a column.
Note

The INSERT/UPDATE aborts as soon as the error is noticed. This may
not be what you want if you are using a nontransactional storage engine,
because data changes made prior to the error may not be rolled back,
resulting in a "partially done" update. (Added in MySQL 5.0.2)

When this manual refers to "strict mode," it means a mode where at least
one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.
###
--
Regards,

Clifford Ilkay
Dinamis
1419-3266 Yonge St.
Toronto, ON
Canada M4N 3P6

<http://dinamis.com>
+1 416-410-3326

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.