At 19:39 -0400 4/28/05, Anoop kumar V wrote:
>I mean:
>
>Should I enable something like 'strict checking' / verbose so that
>MYSQLcomplains and refrains from inserting truncated data in the
>tables??
Yes, but you'll need MySQL 5.0 to do it.
http://dev.mysql.com/doc/mysql/en/server-sql-mode.html
>
>Thanks,
>Anoop
>
>On 4/28/05, Anoop kumar V <anoopkumarv@stripped> wrote:
>>
>> Thank you,
>>
>> But the show warnings does not seem to work on my prompt. I am using
>> mysql> SELECT VERSION();
>> +-----------+
>> | VERSION() |
>> +-----------+
>> | 4.0.23-nt |
>> +-----------+
>>
>> And even if it did and does how will that reflect in my tomcat logs?? I
>> mean I want a way where without user interaction any such warnings are
>> recorded somewhere..
>>
>> Also why does it insert at all - I just checked with Sybase and previously
>> MS Sql server - both display a very visible error message and DO NOT insert
>> the data (although the bug filled MS SQL server said "data may have been
>> truncated" when data was not even inserted!!)
>>
>> Should I enable somethink like strict checking so that MSSQL complains and
>> refrains from inserting truncated data in the tables??
>>
>> Thanks and r,
>> Anoop Kumar V.
>>
>> On 4/28/05, mathias fatene <mfatene@stripped> wrote:
>> >
>> > Hi,
>> > I think you shoul dcatch the "show warnings" command cause in mysql
>> > client you see the number of warnings.
>> > Data are even truncated according to the limit of the type (tinyint, int
>> > ...).
>> >
>> > Example :
>> > mysql> create table toto(a tinyint,b char(5));
>> > Query OK, 0 rows affected (0.06 sec)
>> >
>> > mysql> insert into toto values (500,'Long text');
>> > Query OK, 1 row affected, 2 warnings (0.02 sec)
>> >
>> > It's said here that i have 2 warnings.
>> >
>> > mysql> show warnings
>> > -> ;
>> > +---------+------+------------------------------------------------------
>> > +
>> > | Level | Code | Message
>> > |
>> > +---------+------+------------------------------------------------------
>> >
>> > +
>> > | Warning | 1264 | Data truncated; out of range for column 'a' at row 1
>> > |
>> > | Warning | 1265 | Data truncated for column 'b' at row 1
>> > |
>> > +---------+------+------------------------------------------------------
>> >
>> > +
>> > 2 rows in set (0.00 sec)
>> >
>> > mysql> select * from toto;
>> > +------+------+
>> > | a | b |
>> > +------+------+
>> > | 127 | Long | <==== my 500 is also truncated
>> > +------+------+
>> > 1 row in set (0.00 sec)
>> >
>> > Best Regards
>> > --------------------
>> > Mathias FATENE
>> >
>> > Hope that helps
>> > *This not an official mysql support answer
>> >
>> >
>> > -----Original Message-----
>> > From: Anoop kumar V [mailto: anoopkumarv@stripped]
>> > Sent: vendredi 29 avril 2005 00:21
>> > To: mysql@stripped
>> > Subject: No error / warning when data is truncated on insertion into
>> > mysql
>> >
>> > I am using MySQL and SQL server with Tomcat.
>> >
>> > Our application writes into both databases (mysql and ms sql server) at
>> > once
>> > based on some data collected from an end user. Now if the end user
>> > enters
>> > more data (characters) than the column can hold, the data obviously gets
>> >
>> >
>> > truncated.
>> >
>> > But the surprising thing is that although MS SQL server sends a warning
>> > message to tomcat (seen on the tomcat console) that "data may have been
>> > truncated" - MySQL does not show any warning message (I would have
>> > expected
>> > an error actually) as the data in the column is not what the data was
>> > intended to be.
>> > (Actually MS SQL shows the error and does not even insert the data...)
>> >
>> > Does MySQL not care or maybe I need to activate some option in MySQL
>> > like
>> > verbose or stict checking etc... It just truncated and inserted the data
>> >
>> > with no warning / error or any hassle!!
>> >
>> > how can i force mysql to check for such inconsistencies and report??
>> > --
>> > Thanks and best regards,
> > > Anoop
>> >
>> >
>>
>>
>> --
>> Thanks and best regards,
>> Anoop
>>
>
>
>
>--
>Thanks and best regards,
>Anoop
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
