Re: Not solved - mysql data truncation does not cause an error to be thrown

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Should i say badly written code to reproduce the problem
here is the code again with the errors

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db= mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'andy';
mysql_select_db($dbname);
$insertquery="insert into test values('012345678901234567891')";
$results= mysql_query($insertquery) or die('Error, insert query failed');
print_r($results);
echo "<br>".mysql_errno($db) . ": " . mysql_error($db) . "<br>". mysql_info($db) ;
?>

Andrew Blake wrote:
Hello

It seems that the normal mysql_query doesn't error when an sql command forces the server to truncate the data. The mysql server executes the sql and provides a warning but it still executes the sql so allowing php to ignore the fact that there is a warning. You can run another sql command "SHOW COUNT(*) WARNINGS" after your insert command to check if there is a warning and roll back the changes - similar to the java mysql interface. I haven't used mysqli and perhaps this is addressed in that ? Using sqlyog (3rd party mysql gui) to run the sql also provides no warning message but running it on the command line provides the "1 row affected, 1 warning message" With data validation and check this data too large situation should never happen but surely php should pay attention to the warnings too ?

code to reproduce the problem -->

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password'; *
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');*
$dbname = 'andy';
*mysql_select_db($dbname);*
insertquery="insert into test values('012345678901234567891')";
$results= mysql_query($insertquery) or die('Error, insert query failed');
print_r($results);
echo "<br>".mysql_errno($db) . ": " . mysql_error($db) . "<br>". mysql_info($db) ;
?>

[SQL]
create database if not exists `andy`;
USE `andy`;
/*Table structure for table `test` */
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
 `test` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
[/SQL]

I understand that there are ways around this but php should really automatically notify of the warning.
Maybe i'm missing something obvious......

Cheers

Andy
Andrew Blake wrote:
I figured it out

it was the mysql install not php :-)
cheers for your help though :-)

Vandegrift, Ken wrote:
You may want to check the my.ini setting for the table type you are
using and see if there is a setting in there that needs to be enabled. I thought I read once that truncation may happen silently depending on
the my.ini setting.

Just a thought.

Ken Vandegrift
Shari's Management Corporation
Web Developer/Administrator
(Direct) 503-605-4132
kvandegrift@xxxxxxxxxx
-----Original Message-----
From: Andrew Blake [mailto:a.blake@xxxxxxxxxxxxx] Sent: Thursday, November 08, 2007 7:51 AM
To: Instruct ICC
Cc: php-db@xxxxxxxxxxxxx
Subject: Re:  mysql data truncation does not cause an error to
be thrown

Hiya
I could check the length of the field against the entry data and javascript myself out of trouble but i was more worried that there is no

error or message when mysql clearly returns one saying i've truncated this yet php ignores it completely. It should fail or know about the truncation at least !
Cheers for your reply though :-)

Andy

Instruct ICC wrote:
Using mysql_query if i try to force more data than a field can have
the
data is truncated yet no error is throw at all.
Is there a way round this ?
Cheers

Andy
This isn't exactly what you want to hear, but how about validating
your input before submitting a query?
_________________________________________________________________
Boo! Scare away worms, viruses and so much more! Try Windows Live
OneCare!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotm
ailnews




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux