Asked  10 Months ago    Answers:  5   Viewed   197 times

In MySQL, the default value for a Integer Field is 0. During insertion the value for that field is NULL and the NULL gets inserted. I do some arithmetic manipulation like addition, multipilcation, division using that field's value. This results in error as NULL got inserted. How to resolve this problem? Don't the DB support default value for the fields like integer,decimal(may not be for string types, as NULL is valid for string) if their insertion value is null. If Not, then is it correct to have NULL values for Numeric types?

For some reason When i have a decimal field with NOT NULL and DEFAULT VALUE as 0, the insertion fails with the error message "Data truncated for column 'Column_Name' at row 1".

If i remove the NOT NULL property, the insertion succeeds the same message as warning.

The value that gets inserted into that decimal field is the result of AVG() MySql function

Really Clueless...

What could be the reason?

 Answers

1

NULL is a correct value for any mysql column / type.

you can set the column to NOT NULL or you need to resolve it in your scripting language, so the null does not get inserted.

Friday, August 27, 2021
 
McAn
 
2

No PHP doesn't have a "fallback to default if null" option. You should instead do:

private function dostuff(?int $limit = null) {
    // pre-int typehinting I would have done is_numeric($limit) ? $limit : 999;
    $limit = $limit ?? 999;
}

Alternatively make sure you either do dostuff() or dostuff(999) when you don't have a sensible value for doing stuff.

Note: There's also reflection to get the default values of method parameters but that seems a too much.

However here's how:

 $m = new ReflectionFunction('dostuff');
 $default = $m->getParameters()[0]->getDefaultValue();
 dostuff($default);
Saturday, May 29, 2021
 
Kwadz
 
5

Windows MySQL v5 throws an error but Linux and other versions only raise a warning. This needs to be fixed. WTF?

Also see an attempt to fix this as bug #19498 in the MySQL Bugtracker:

Bryce Nesbitt on April 4 2008 4:36pm:
On MS Windows the "no DEFAULT" rule is an error, while on other platforms it is often a warning. While not a bug, it's possible to get trapped by this if you write code on a lenient platform, and later run it on a strict platform:

Personally, I do view this as a bug. Searching for "BLOB/TEXT column can't have a default value" returns about 2,940 results on Google. Most of them are reports of incompatibilities when trying to install DB scripts that worked on one system but not others.

I am running into the same problem now on a webapp I'm modifying for one of my clients, originally deployed on Linux MySQL v5.0.83-log. I'm running Windows MySQL v5.1.41. Even trying to use the latest version of phpMyAdmin to extract the database, it doesn't report a default for the text column in question. Yet, when I try running an insert on Windows (that works fine on the Linux deployment) I receive an error of no default on ABC column. I try to recreate the table locally with the obvious default (based on a select of unique values for that column) and end up receiving the oh-so-useful BLOB/TEXT column can't have a default value.

Again, not maintaining basic compatability across platforms is unacceptable and is a bug.


How to disable strict mode in MySQL 5 (Windows):

  • Edit /my.ini and look for line

    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    
  • Replace it with

    sql_mode='MYSQL40'
    
  • Restart the MySQL service (assuming that it is mysql5)

    net stop mysql5
    net start mysql5
    

If you have root/admin access you might be able to execute

mysql_query("SET @@global.sql_mode='MYSQL40'");
Wednesday, June 2, 2021
 
julesj
 
3

You could put the following attribute on your string-properties in your model:

[DisplayFormat(ConvertEmptyStringToNull=false)]

So whenever someone posts a form with empty text-fields, these will be an empty string instead of null...

Thursday, July 29, 2021
 
nika
 
4

I suggest using the constructor to set up the default state of your objects. Hibernate is an ORM. The basic philosophy is that you save your objects. Using default values at the DBMS end can undermine the whole concept, by making it possible to save objects in invalid states. (By making possible for field values to skip the setter methods).

In the other hand, when you instantiate an object its fields are already initiated (to null value or zero or empty string etc.) It would be impossible for Hibernate to guess whether you want to save the actual null or zero or you want to use the DB's default value instead.

When you're talking about default values in the DBMS level, i assume you think of primitive values. There is absolutely no drawback in initializing primitive fields on the Java side (inside the constructor or by declaration).

Thursday, November 18, 2021
 
Rakesh
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :