Re: No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

I wish I could upgrade - believe me I would love to but the product we use 
is supposedly not compatible with any higher version than 4.0.23 :-(.

Is there any workaround at all - even something that is known to be a bit 
performance intensive as our application is not that heavy!!

Thanks a TON for your answers.
Anoop


On 4/28/05, Paul DuBois <[EMAIL PROTECTED]> wrote:
> 
> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> >> > Sent: vendredi 29 avril 2005 00:21
> >> > To: mysql@lists.mysql.com
> >> > 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 
> 



-- 
Thanks and best regards,
Anoop

Re: No error / warning when data is truncated on insertion into mysql

[Paul DuBois]

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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
 > Sent: vendredi 29 avril 2005 00:21
 > To: mysql@lists.mysql.com
 > 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

I mean:

Should I enable something like 'strict checking' / verbose so that
MYSQLcomplains and refrains from inserting truncated data in the
tables??

Thanks,
Anoop

On 4/28/05, Anoop kumar V <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> > Sent: vendredi 29 avril 2005 00:21
> > To: mysql@lists.mysql.com
> > 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

Re: No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> Sent: vendredi 29 avril 2005 00:21
> To: mysql@lists.mysql.com
> 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

RE: No error / warning when data is truncated on insertion into mysql

[mathias fatene]

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:[EMAIL PROTECTED] 
Sent: vendredi 29 avril 2005 00:21
To: mysql@lists.mysql.com
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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]