[PHP-DB] Not solved - mysql data truncation does not cause an error to be thrown

[Andrew Blake]

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 -->

$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 "".mysql_errno($db) . ": " . mysql_error($db) . "". 
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
[EMAIL PROTECTED]
-Original Message-
From: Andrew Blake [mailto:[EMAIL PROTECTED] Sent: Thursday, 
November 08, 2007 7:51 AM

To: Instruct ICC
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] 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

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

[Andrew Blake]

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

$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 "".mysql_errno($db) . ": " . mysql_error($db) . "". 
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 -->

$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 "".mysql_errno($db) . ": " . mysql_error($db) . "". 
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
[EMAIL PROTECTED]
-Original Message-
From: Andrew Blake [mailto:[EMAIL PROTECTED] Sent: Thursday, 
November 08, 2007 7:51 AM

To: Instruct ICC
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] 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

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

[chris smith]

On Nov 9, 2007 8:34 PM, Andrew Blake <[EMAIL PROTECTED]> 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 ?

I doubt it.

> 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 ?

It's not a php problem. Mysql should throw proper errors instead of a 'warning'.

http://dev.mysql.com/doc/refman/4.1/en/blob.html
If you assign a value to a BLOB or TEXT column that exceeds the data
type's maximum length, the value is truncated to fit and a warning is
generated.

Same for char/varchar & binary fields
(http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.html and
http://dev.mysql.com/doc/refman/4.1/en/char.html).

You will get the same "problem" in other languages I'm sure (try it in
python, perl, ruby).

-- 
Postgresql & php tutorials
http://www.designmagick.com/

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

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

[Andrew Blake]

Hiya
This seems to be one thing java has over php (perl etc) as it does know 
about the warning and acts approperately. This should be an option to 
ignore or not ignore warnings within php to prevent errorless truncation 
of data.

Cheers

Andy

chris smith wrote:

On Nov 9, 2007 8:34 PM, Andrew Blake <[EMAIL PROTECTED]> 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 ?



I doubt it.

  

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 ?



It's not a php problem. Mysql should throw proper errors instead of a 'warning'.

http://dev.mysql.com/doc/refman/4.1/en/blob.html
If you assign a value to a BLOB or TEXT column that exceeds the data
type's maximum length, the value is truncated to fit and a warning is
generated.

Same for char/varchar & binary fields
(http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.html and
http://dev.mysql.com/doc/refman/4.1/en/char.html).

You will get the same "problem" in other languages I'm sure (try it in
python, perl, ruby).

  


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

[PHP-DB] Re: [PHP] Re: [PHP-DB] Re: MySQL Identifying worst-performing codes

[Mark Summers]

There may be something useful here...

http://forge.mysql.com/

chris smith wrote:
> On Nov 9, 2007 1:18 AM, Colin Guthrie <[EMAIL PROTECTED]> wrote:
>   
>> Lasitha Alawatta wrote:
>> 
>>> There is  a tool call "idera" (SQL diagnostic manager). Basically it is
>>> a performance monitoring and diagnostics tool.
>>>
>>> It has a feature;
>>> Identifying of worst-performing codes –
>>>
>>> Identifies performance bottlenecks such as the worst-performing stored
>>> procedures, long-running queries, most frequently run queries, SQL
>>> Statements and SQL batches
>>>
>>> http://www.idera.com/Products/SQLdm/Features.aspx
>>>
>>>
>>> I'm looking for a same like tool for MySQL. Is anyone have any  ideas.
>>>   
>> I know this is OT for this list but.
>>
>> In addition to the slow query logging I mentioned before (which you
>> seemed to appreciate :)), I've just stumbled across this:
>> http://rackerhacker.com/mysqltuner/
>> 
>
> http://jeremy.zawodny.com/mysql/mytop/ might come in handy too.
>
>   

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

[PHP-DB] Interactive Order Form

[VanBuskirk, Patricia]

Happy Friday All!

We presently have an order form online (using php5) that feeds into a
FileMaker 9 database.  What we are trying to do is:

If someone clicks on "cell phone" (checkbox), a new box of fields shows
under it requiring additional info that feeds into a new table in the
database (one order number to many cell phones).  Once they fill in that
data (action:add/chg/delete, user name, cell number, userid, etc), there
should be a button for "more lines" that they can click that will give
them another line to enter info on.  Once that info is entered and they
are done, those fields of info go to the other table, while the original
form info goes to the orders table.

I am at the point in programming php where I know something CAN be done
... but HOW to do it can be the puzzler!  Any assistance or examples
would be greatly appreciated.  I can send the form and processing page
to anyone offlist if requested.

Thanks!

Trish

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

RE: [PHP-DB] Interactive Order Form

[Evert Lammerts]

I've never worked with FileMaker before, but a quick Google
(http://www.filemaker.com/support/technologies/php.html) tells me you need
to get a copy of the FileMaker API for PHP, if you're not using Windows that
is. If you are using Windows you can use ODBC
(http://www.filemaker.com/help/15-Using%20ODBC.html).

If your database is only interfaced by a website I'd recommend using a
database system that is supported by PHP (MySQL would be a good option).
There might of course be other options out there that I'm not aware of.

Good luck,
Evert

-Original Message-
From: VanBuskirk, Patricia [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 9 november 2007 18:01
To: php-db@lists.php.net
Subject: [PHP-DB] Interactive Order Form

Happy Friday All!

We presently have an order form online (using php5) that feeds into a
FileMaker 9 database.  What we are trying to do is:

If someone clicks on "cell phone" (checkbox), a new box of fields shows
under it requiring additional info that feeds into a new table in the
database (one order number to many cell phones).  Once they fill in that
data (action:add/chg/delete, user name, cell number, userid, etc), there
should be a button for "more lines" that they can click that will give
them another line to enter info on.  Once that info is entered and they
are done, those fields of info go to the other table, while the original
form info goes to the orders table.

I am at the point in programming php where I know something CAN be done
... but HOW to do it can be the puzzler!  Any assistance or examples
would be greatly appreciated.  I can send the form and processing page
to anyone offlist if requested.

Thanks!

Trish

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

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

[PHP-DB] Corrupted query results in memory.

[Max Thayer]

I posted an issue to the mysql dev forums last week with regards to an
issue we are encountering.  The issue as we understood it last week was
simple...after some time (undetermined as of then) query results from
the web app (a PHP/MySQL/Javascript Ajax app.) would come back all
'wonky.'  Upon issuing a mysql-server restart the data would be fine,
and query results back to normal.  This morning I figured out how to
replicate the issue.  Issue a double request for the mysql result set.
Due to the "Ajaxy" nature of my web app it isn't the same as a double
HTTPRequest of POST, as it is requesting a result set back from the
database twice in a row before the response is collected.

We run Apache 2.2 on FreeBSD 6.1.x, I am currently running MySQL
5.0.xgiven the fact that I can repeat and reproduce the problem by
either hitting my refresh button twice, or by hitting my javascript/Ajax
button twice before I get my first responsecan anyone point me in a
direction to narrow down why my code might be doing this?  I am using
PDO with prepared statements and MySQL stored procedures to produce my
result sets... if you asked me yesterday, I'd have said I wasn't doing
any thing that MySQL couldn't handle...but apparently that isn't the
case...

Max H. Thayer
Software Developer
Center for High-Throughput Structural Biology
 
Hauptman-Woodward Medical Research Inst.
700 Ellicott St.
Buffalo, NY 14203
Phone: 716-898-8637
Fax: 716-898-8660
http://www.chtsb.org  
http://www.hwi.buffalo.edu  

RE: [PHP-DB] Corrupted query results in memory.

[Instruct ICC]

> This morning I figured out how to
> replicate the issue. Issue a double request for the mysql result set.
> Due to the "Ajaxy" nature of my web app it isn't the same as a double
> HTTPRequest of POST, as it is requesting a result set back from the
> database twice in a row before the response is collected.

I think we'd have to see how you are doing your "Ajaxy" implementation.  This 
is the lowest level of Ajax I use now 
http://www.xajaxproject.org/docs/xajax-in-10-minutes.php

If it's asynchronous, how can a second request wonk it up?  Did you roll your 
own using XMLHttpRequest directly?


> We run Apache 2.2 on FreeBSD 6.1.x, I am currently running MySQL
> 5.0.xgiven the fact that I can repeat and reproduce the problem by
> either hitting my refresh button twice, or by hitting my javascript/Ajax
> button twice before I get my first responsecan anyone point me in a
> direction to narrow down why my code might be doing this? I am using
> PDO with prepared statements and MySQL stored procedures to produce my
> result sets... if you asked me yesterday, I'd have said I wasn't doing
> any thing that MySQL couldn't handle...but apparently that isn't the
> case...

I'm still rooting for "MySQL could handle it".

I wouldn't think it necessary, but maybe you need to obtain table locks for 
your needs.

--MAE Alumnus rocking the high compensation to effort ratio in this CS world of 
no-accountability-maybe-we'll-fix-it-in-the-next-release-but-pay-to-find-out-beotch.
  Thank you Billy boy.
_
Climb to the top of the charts!  Play Star Shuffle:  the word scramble 
challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_oct
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Corrupted query results in memory.

[Bastien Koert]

so the simple solution is to disable the button after the call until the 
results are returned
 
bastien> Date: Fri, 9 Nov 2007 15:02:31 -0500> From: [EMAIL PROTECTED]> To: 
php-db@lists.php.net> Subject: [PHP-DB] Corrupted query results in memory.> > I 
posted an issue to the mysql dev forums last week with regards to an> issue we 
are encountering. The issue as we understood it last week was> simple...after 
some time (undetermined as of then) query results from> the web app (a 
PHP/MySQL/Javascript Ajax app.) would come back all> 'wonky.' Upon issuing a 
mysql-server restart the data would be fine,> and query results back to normal. 
This morning I figured out how to> replicate the issue. Issue a double request 
for the mysql result set.> Due to the "Ajaxy" nature of my web app it isn't the 
same as a double> HTTPRequest of POST, as it is requesting a result set back 
from the> database twice in a row before the response is collected.> > We run 
Apache 2.2 on FreeBSD 6.1.x, I am currently running MySQL> 5.0.xgiven the 
fact that I can repeat and reproduce the problem by> either hitting my refresh 
button twice, or by hitting my javascript/Ajax> button twice before I get my 
first responsecan anyone point me in a> direction to narrow down why my 
code might be doing this? I am using> PDO with prepared statements and MySQL 
stored procedures to produce my> result sets... if you asked me yesterday, I'd 
have said I wasn't doing> any thing that MySQL couldn't handle...but apparently 
that isn't the> case...> > Max H. Thayer> Software Developer> Center for 
High-Throughput Structural Biology> > Hauptman-Woodward Medical Research Inst.> 
700 Ellicott St.> Buffalo, NY 14203> Phone: 716-898-8637> Fax: 716-898-8660> 
http://www.chtsb.org  > http://www.hwi.buffalo.edu 
 > 
_
Send a smile, make someone laugh, have some fun! Start now!
http://www.freemessengeremoticons.ca/?icid=EMENCA122