SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why do I suddenly need a default value?

    I have a script running under Apache 2 on a Unix server. This script inserts into MySQL 4.1.21. This script works perfectly.

    I also have a development server - Apache 2 under Windows - running the same script. My development server is using MySQL 5.0.22. This script works far from perfectly. When doing an insert, all fields of a record must either be set with a value, have a default value set in advance, or be nullable. Otherwise MySQL throws a "Field xxx doesn't have a default value" error.

    Why is this? And, is there anyway to bypass this issue, without overhauling dozens of database tables, or messing around with the third-party scripts?
    Last edited by uprightdog; Jul 3, 2007 at 10:29.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    in mysql 4.0:
    Code:
    mysql> create table test (i int not null);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `i` int(11) NOT NULL default '0'
    ) TYPE=MyISAM
    1 row in set (0.00 sec)
    in mysql 5:
    Code:
    mysql> create table test (i int not null);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `i` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    as you can see, mysql 4.0 automatically adds a DEFAULT clause if you don't specify one. they dropped this "feature" in mysql 5.0.

    my suggestion is to never rely on "magic" features such as this and ALWAYS specify EXACTLY what you want.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck View Post
    they dropped this "feature" in mysql 5.0.
    don't hint at it, longneck, say EXACTLY what you mean

    inserting a DEFAULT was bad, and it is good that 5.0 no longer does this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    as you can see, mysql 4.0 automatically adds a DEFAULT clause if you don't specify one. they dropped this "feature" in mysql 5.0.
    Is this connected to 'Strict Mode' - is this mode just set by default in 5.0?

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm not in a position to test that right now. you could test it and find out. in this case, i'm going to venture a guess and say no.

    you should be aware that changing server defaults like this is generally a bad idea, especially since your application may be used in situations where the user has no control over server settings.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •