[RESOLVED] Columns with a NOT NULL indicator do not work.

mmonaco

I have a few columns in my table where I've specified NOT NULL.

My INSERT statement:

INSERT INTO table SET resourceID='234234', billingDescription='Test desc';

Note I did not set a value for taskID which was specified as NOT NULL

My application was originally built on Window XP, Apache 2, PHP 5, MySQL 5. This statement was not accepted.

When I moved it to Fedora Core 4, Apache 2, PHP 5, MySQL 4.1 the INSERT statement is accepted. This is not good.

On the windows machine in the command prompt

+--------------------+-------------------------------------------------+------+-----+---------------------+----------------+
| Field              | Type                                            | Null | Key | Default             | Extra          |
+--------------------+-------------------------------------------------+------+-----+---------------------+----------------+
| id                 | int(11)                                         | NO   | PRI | NULL                | auto_increment |
| resourceID         | varchar(36)                                     | NO   |     |                     |                |
| taskID             | varchar(36)                                     | NO   |     |                     |                |
| billingDescription | varchar(1000)                                   | YES  |     | NULL                |                |
| portalDescription  | varchar(1000)                                   | YES  |     | NULL                |                |

However on the fedora machine:

+--------------------+-------------------------------------------------+------+-----+-------------------+----------------+
| Field              | Type                                            | Null | Key | Default           | Extra          |
+--------------------+-------------------------------------------------+------+-----+-------------------+----------------+
| id                 | int(8)                                          |      | PRI | NULL              | auto_increment |
| resourceID         | varchar(36)                                     |      |     |                   |                |
| taskID             | varchar(36)                                     |      |     |                   |                |
| billingDescription | text                                            | YES  |     | NULL              |                |

I suspect the problem has something to do either with the fact that 'NO' is not indicated in the null column or that the default value is blank for the NOT NULL fields.

subwayman

INSERT INTO table SET col='val', col3='val';

If col and col3 are defined with NOT NULL then this query WILL work. NOT NULL means an INSERT or an UPDATE cannot set the field to NULL.

The query above set values for both columns. I suspect your problem is elsewhere. For example you are inserting a record with only two columns set - so there must be default values for all the remaining columns - especially any that are or make up indexes. MYSQL is generally much more tollerant of loose SQL than some other DB's.

mmonaco

I'm sorry, you missed something, in that sample INSERT statement i set values for col and col3 NOT col2

I am going to edit my original post to indicate the actual values

Sxooter

Here's the deal. MySQL was BROKEN in 4.1. What you are describing is improper behaviour for any database. However, most MySQL users, having used only MySQL didn't know this. Other database users recoiled in horror upon learning all the ways that MySQL was basically broken.

MySQL fixed that, and many other problems in 5.x. By another version or two, they may actually be SQL compliant for the most part.

There may be a --ansi type switch in mysql 4 that will turn on the correct behaviour. Yes, MySQL 4.x is broken by default. Fewer things in 5 are broken by default, but there are a few.

My serious suggestion is to switch to a database that does things in a non-broken way by default. Firebird or PostgreSQL come to mind.

Note that defaults WOULD allow those unnamed columns to be inserted, but without a default, you SHOULD be getting an error. PostgreSQL has errored out on this kind of stuff for about 10 years now, by the way.

mmonaco

Just to expand that answer and answer my own question, the only way to fix the problem is to compile mysql-4.1 with the option to ignore defaults.

Sxooter

Are you sure there's no startup switch / envvar setting to accomplish this behaviour in 4.1?