Data too long for a column? Help?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Data too long for a column? Help?

Post by kaisellgren »

Hi,

I'm coding a project, it uses MySQL(i), PostgreSQL, Oracle and MSSQL (SQL Server through ODBC).

Everything seems fine, except for a few errors.

It seems that MySQL 5+ throws an error if you insert let's say 500 characters long string into a varchar(255), but MySQL 4 will automatically truncate it? How can I force MySQL to do this autotruncating?

Does this have something to do with SQL modes?

http://dev.mysql.com/doc/refman/5.0/en/ ... -mode.html

Does setting it to Strict mode provide any help?

Thanks for your answers :)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Data too long for a column? Help?

Post by onion2k »

Switch off strict mode prior to doing the query:

Code: Select all

SET sql_mode='';
What on Earth are you writing that needs 5 different databases?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Data too long for a column? Help?

Post by kaisellgren »

onion2k wrote:Switch off strict mode prior to doing the query:

Code: Select all

SET sql_mode='';
What on Earth are you writing that needs 5 different databases?
It's for several different servers, some support some :) (ok ok and I admit also for the education of mine :P)

But, can't I do that myself too? I mean the truncation.

The best way is to 'force sql into strict mode and then do checkings&changes myself'? There's no point of doing the checks/changes in both PHP & SQL side, so I just force the SQL mode to strict and do the checkings myself? :)

Or... do you think that it's always possible to set an SQL server into nonstrict mode? Are there servers that won't do this, or is any of those dbms I mentioned limited to use strict only?

How can I know what kind of things to look at. The varchar too long is just one kind of problem, what other problems there could possibly be (caused by this strict mode)?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Data too long for a column? Help?

Post by onion2k »

If validating the data in the code first is an option then do that. Always. Maybe you could use some sort of data dictionary approach?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Data too long for a column? Help?

Post by kaisellgren »

onion2k wrote:If validating the data in the code first is an option then do that. Always. Maybe you could use some sort of data dictionary approach?
Can you light me more about that data dictionary approach?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Data too long for a column? Help?

Post by onion2k »

A data dictionary is basically a model of your database schema that contains meta data you can use to generate validation rules, queries, etc. http://dev.mysql.com/tech-resources/art ... onary.html
Post Reply