visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: WARN_DATA_TRUNCATED, Data truncated for column 'C4' at row 2 Error No.: 1265
By: omuyelijah Nigeria  Date: 23/12/2009 15:08:41  English  Points: 20 Status: Answered
Quality : Excellent
Hi all,

O.S: Microsoft Windows XP;
PHP 5.2.5/2
Apache 2.2.11
MySQL 5.0.45

I'm doing this PHP/MySQL application. On the MySQL side, am using stored procedures and prepared statements while am also coding with php using the MySQLI extension. Of recent, I'm having the error of the sort above and can't explain why. I have a table created with the code like below:

CREATE TABLE IF NOT EXISTS TB1 (SN INT NOT NULL AUTO_INCREMENT, C0 TEXT NOT NULL, C1 FLOAT, C2 FLOAT, C3 FLOAT, C4 FLOAT, PRIMARY KEY(SN)) ENGINE=InnoDB;



Fields C1 to C4 can take integer as well as real values so I judged declaring them float (instead of integer) will be nice so that float values don't loose precision. I'm leaving fields C1 to C4 to be null by default so I can update them later. From code, C4 is updated through a call to stored procedure upd also defined as follows:

DELIMITER // CREATE PROCEDURE `upd` (pr TEXT,nv FLOAT) BEGIN SET @urst=CONCAT("UPDATE `TB1` SET `C4`='",nv,"' WHERE `C0`='",pr,"'"); PREPARE stmt FROM @urst; EXECUTE stmt; DEALLOCATE PREPARE stmt; END; // DELIMITER ;



The procedure has been working fine since start of project together with other similar procedures but just yesterday evening and this morning, the error above started displaying, running 4rm my browser. Other similar stored procedures work except this. What is more striking is that calling thesame procedure from the mysql console [i.e. call upd(...)] is giving a similar output; sometimes it fails with the error/warning while most times it work. I'm calling the procedure trying integer as well as float values but only a few of them (especially integer values less than 100) update well. Its painful bcos running the weblication from the web browser calling this function fails.

I've searched the ~net for similar issues but didn't find this my match. Other issues I found are more related to passing invalid values to fields of differing data types (int and float should be more related) or passing data into fields whose memory size is small.

I really need help from someone. Pls, come 2 my rescue.

Thanks in advance
By: VGR Date: 23/12/2009 19:25:07 English  Type : Comment
probably a non-float value, or NULL, passed to the column... (because it has no default value)

you should check the "line 2" values...
By: omuyelijah Date: 28/12/2009 12:42:22 English  Type : Answer
Hi,

Thanks VGR 4 ur contribution. U c, I've debugged all the way: I called the stored proc. manually from mysql client program, (i.e. call upd(...)) passing the values but same problem. I've also used the MySQL GUI administrator/Query Browser but to no avail.

To make it all fearsome, inside upd() are codes 2 mathematically operate on the parameters. Well, I managed to overcome this issue & thank God I did.

Step 1: re-declare table columns changing field type from float 2 varchar(100). This is so that input will come as text into the procedure.

CREATE TABLE IF NOT EXISTS TB1 (SN INT NOT NULL AUTO_INCREMENT, C0 TEXT NOT NULL, C1 varchar(100), C2 varchar(100), C3 varchar(100), C4 varchar(100), PRIMARY KEY(SN)) ENGINE=InnoDB;



Step 2: Change calling convention so I can mathematically operate on the data in the stored procedures. Ordinarily I would have single-quoted variables $c1,$c2,$c3 and $c4 just like $c0 but cos they will be mathematically operated inside upd() and the results pushed into table TB1.

$mysqli->query("call upd('$c0',$c1,$c2,$c3,$c4)");



This technique works well for very large integer/float values coming into upd() and I've since upgraded related procedures for fear of repeat, also to the admiration of my client.

Thanks again VGR 4 ur concern.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 295.627830 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page