Re: Re: PDO Mysql data truncation missing error

On Tue, Aug 21, 2012 at 4:44 PM, Matt Pelmear <[email protected]> wrote:
> Amit,
>
> Thanks for the suggestion. Unfortunately, even when I explicitly enable
> E_WARNING or even E_ALL error reporting in php I still do not receive any
> indication that data was truncated on insert.
>
> -Matt
>
>
> On 08/21/2012 07:09 AM, Amit Tandon wrote:
>>
>>
>> Dear Matt,
>>
>> Even with MySQL, u get the warning as the show warnings is enabled. With
>> php bound scripts, you have to check warnings  to see the warnings. However,
>> php is quite flexible and you an use error_reporting(E_WARNING) for
>> non-fatal error reporting
>>
>> regds
>> amit
>>
>> On Aug 21, 2012 10:41 AM, "Matt Pelmear" <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>>     On 08/21/2012 01:08 AM, David Robley wrote:
>>
>>         Matt Pelmear wrote:
>>
>>             Hello,
>>
>>             I'm trying to detect data truncation on insert to MySQL
>>             using PDO.
>>
>>             As far as I can tell, this gets reported at least in some
>>             cases (ex:
>>             http://drupal.org/node/1528628), but I have been unable to
>>
>>             see this
>>             myself.
>>
>>             The test table I'm using has a column that is VARCHAR(5):
>>
>>             mysql>  describe test;
>>             +-------+------------------+------+-----+---------+-------+
>>
>>             | Field | Type             | Null | Key | Default | Extra |
>>             +-------+------------------+------+-----+---------+-------+
>>
>>             | id    | int(10) unsigned | YES  |     | NULL    |       |
>>             | data  | varchar(5)       | YES  |     | NULL    |       |
>>             +-------+------------------+------+-----+---------+-------+
>>
>>             2 rows in set (0.00 sec)
>>
>>
>>             My test script inserts a ten character string into the 5
>>             character column:
>>
>>             ==== PHP TEST CODE ====
>>
>>             $pdo = new PDO(
>>                   'mysql:host=localhost;dbname=test',
>>                   'username',
>>                   'password'
>>             );
>>
>>             $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
>>
>>
>>             $retval = $pdo->query( 'INSERT INTO `test` (data) VALUES
>>             ("1234567890")'
>>             );
>>
>>             print_r( $retval );
>>
>>             ==== END TEST CODE ====
>>
>>             This results in a new row in the `test` table, truncated
>>             after the 5th
>>             character as expected, but the truncation is not reported.
>>             (running in
>>             php 5.3.2 and 5.3.4)
>>             Running the same query directly in the mysql command line
>>             shows a
>>             warning as expected.
>>
>>             Am I missing something simple here?
>>
>>             Thanks,
>>             Matt
>>
>>         Caveat: I don't use PDO but maybe the PDO::ERRMODE_WARNING
>>         attribute may do
>>         what you want?
>>
>>
>>
>>         Cheers
>>
>>
>>     Interesting idea. I gave it a try, but got the same result!
>>     I think PDO::ERRMODE_WARNING tells PDO to give PHP warnings versus
>>     throw exceptions when there are problems (if I'm understanding it
>>     properly).
>>
>>     -Matt
>>
>>     --     PHP Database Mailing List (http://www.php.net/)
>>     To unsubscribe, visit: http://www.php.net/unsub.php
>>
>


MySQL will silently truncate it without throwing an error, unless
you've looked into enabling alternate behavior. Set
STRICT_TRANS_TABLES (for InnoDB) or STRICT_ALL_TABLES (for any storage
engine) in MySQL's config and it'll throw the following error instead
of silently truncating data:
[Err] 1406 - Data too long for column '[some column]' at row [XYZ]
Does that help?
-- 

Bastien

Cat, the other other white meat

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