Mysql Error Code 1366 Incorrect Decimal Value
2014 10:13 Reporter: Diane Gabrielsen Email Updates: Status: Not a Bug Impact on me: None Category:MySQL Server: Errors Severity:S3 (Non-critical) Version:5.1.40 OS:Any Assigned to: Tags: 1366, strict mode View Add Comment Files Developer Edit Submission View Progress Log Contributions [18 Jun 2014 15:16] Diane Gabrielsen Description: When in strict mode, while using load data infile, columns with datatype decimal receive error 1366: Incorrect decimal value: '' for column 'column_name' at row [row number] for fields with a null value. This error does not appear when not in strict mode. How to repeat: CREATE TABLE bug_repeat ( name varchar(10), price decimal(12,6) ) ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin; Create a file called bug_test.txt and have it contain one or more null values for the decimal column. Example file: name, name,0 , name,6 name,2 name, name,0 name,0 name, name,0 load data infile 'bug_test.txt' into table dev.bug_repeat fields terminated by ',' lines terminated by '\n'; When loading, you will get this error for every row that contains a null value: 1366: Incorrect decimal value: '' for column 'price' at row [row number] Errors returned for example file used: 1366: Incorrect decimal value: '' for column 'price' at row 1 1366: Incorrect decimal value: '' for column 'price' at row 3 1366: Incorrect decimal value: '' for column 'price' at row 6 1366: Incorrect decimal value: '' for column 'price' at row 9 [18 Jun 2014 15:25] Diane Gabrielsen I forgot to mention that besides giving the error, MySQL replaces the null value with a 0 in the table. This is the most serious part of the issue, since my table now cannot tell the difference between a null or a valid zero! [31 Jul 2014 10:13] Umesh Umesh Thank you for the report. Imho, this is documented behavior described in http://dev.mysql.com/doc/refman/5.1/en/load-data.html "If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”." For string types, the column is set to the empty string. For numeric types, the column is set to 0. For date and time types, the column is set to the appropriate “zero” value for the type. See Section 11.3, “Date and Time Types”. These are the same values that result if you assi
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web Developer Tools Advanced Search Forum Programming Languages PHP Development MySQL error "Incorrect decimal value: for column rentprice at row 1" Thread: MySQL error "Incorrect decimal value: for column rentprice at row 1" Share This Thread Tweet This + https://bugs.mysql.com/bug.php?id=73041 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread November 13th, 2012,10:37 AM #1 BitZoid View Profile View Forum Posts Contributing User Devshed Newbie (0 - 499 posts) Join Date Nov 2012 Posts 98 Rep Power 4 http://forums.devshed.com/php-development-5/mysql-error-incorrect-decimal-value-column-rentprice-row-1-a-934049.html MySQL error "Incorrect decimal value: for column rentprice at row 1" I have an add inventory page where if user clicks rent checkbox, he must enter rentprice - same with sell. However, I keep getting MySQL error "Incorrect decimal value: for column rentprice at row 1" MySQL defined as Column: rentprice DECIMAL(8,2) - Default of NULL Column: sellprice DECIMAL(8,2) - Default of NULL Here is my code: PHP Code: log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more http://dba.stackexchange.com/questions/87206/mysql-is-inserting-as-0-in-decimal-fields-how-to-stop-that about Stack Overflow the company Business Learn more about hiring developers or posting ads https://www.drupal.org/node/1316668 with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a mysql error question Anybody can answer The best answers are voted up and rise to the top MySQL is inserting “” as 0 in decimal fields. How to stop that? up vote 0 down vote favorite The fields are nullable, so they do allow nulls. Why would MySQL convert "" to 0 instead of NULL? Is there a way to force it to be NULL instead? mysql null share|improve this question asked mysql error code Dec 29 '14 at 23:23 Test 12 Are you getting an error message like ERROR 1366 (HY000): Incorrect decimal value: '' for column 'price' at row 1 ? –RolandoMySQLDBA Dec 29 '14 at 23:35 1 Empty string is no more like NULL than it is like 0 and arguably even less so. If you want null, why exactly would you not insert null? –Michael - sqlbot Jun 29 '15 at 4:06 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote This seems be an on-going bug although it is not considered a bug, such as Bug #73041: Error 1366 when loading null into columns with decimal type using strict mode According to Oracle's MySQL Documentation If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error: The following rules describe in more detail how this works: If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possib that make connections all over the world. Join today Download & Extend Drupal Core Distributions Modules Themes UbercartIssues General error: 1366 Incorrect decimal value: '' for column 'list_price' Closed (fixed)Project:UbercartVersion:7.x-3.x-devComponent:ProductsPriority:NormalCategory:Bug reportAssigned:UnassignedReporter:KartagisCreated:October 21, 2011 - 06:26Updated:June 13, 2012 - 09:43 Log in or register to update this issue Jump to:Most recent comment Hi, Today, when I edited a product, cleared List Price & Cost and saved, I got the below error. PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect decimal value: '' for column 'list_price' at row 1: UPDATE {uc_products} SET nid=:db_update_placeholder_0, model=:db_update_placeholder_1, list_price=:db_update_placeholder_2, cost=:db_update_placeholder_3, sell_price=:db_update_placeholder_4, weight=:db_update_placeholder_5, weight_units=:db_update_placeholder_6, length=:db_update_placeholder_7, width=:db_update_placeholder_8, height=:db_update_placeholder_9, length_units=:db_update_placeholder_10, pkg_qty=:db_update_placeholder_11, default_qty=:db_update_placeholder_12, unique_hash=:db_update_placeholder_13, ordering=:db_update_placeholder_14, shippable=:db_update_placeholder_15 WHERE (vid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 4 [:db_update_placeholder_1] => 0003 [:db_update_placeholder_2] => [:db_update_placeholder_3] => [:db_update_placeholder_4] => 15.00 [:db_update_placeholder_5] => 0 [:db_update_placeholder_6] => lb [:db_update_placeholder_7] => 0 [:db_update_placeholder_8] => 0 [:db_update_placeholder_9] => 0 [:db_update_placeholder_10] => in [:db_update_placeholder_11] => 1 [:db_update_placeholder_12] => 1 [:db_update_placeholder_13] => ec542b63a27cc80dfb8028f16269c91f [:db_update_placeholder_14] => 0 [:db_update_placeholder_15] => 0 [:db_condition_placeholder_0] => 4 ) in drupal_write_record() (line 6868 of /srv/www/bilgisayarciniz.org/public_html/includes/common.inc). Comments Comment #1 longwave CreditAttribution: longwave commented October 21, 2011 at 8:41am Status: Active » Fixed This has already been fixed in -dev by #1243762: Make field validation more consistent Log in or register to post comments Comment #2 longwave CreditAttribution: longwave commented October 21, 2011 at 9:09am Title: PDO Exception » General error: 1366 Incorrect decimal value: '' for column 'list_price' Retitling as "PDO Exception" is not very meaningful on its own. Log in or register to post comments Comment #3 November 4, 2011 at 9:10am Status: Fixed » Closed (fixed) Automatically closed -- issue fixed for 2 we if((empty($_POST['rent']))&&(empty($_POST['sell']))){
$errors[]='YoumustselecteitherRentableorSellablecheckbox.';
}
if($_POST['rent']){
$rent=1;
if(