Description:
I've noticed a difference in behavior between MySQL 4.1.22 and MySQL 5.0.45, which I have installed on two different servers running development and production versions of the same database. This difference concerns the behavior of MySQL when adding a column to a table that is set to NOT NULL. On MySQL 4, doing this automatically assigns DEFAULT = '' to the column in question. On MySQL 5, this assumption is not made.
I like the way MySQL 4 handled it. If you have a column set to NOT NULL, it makes sense that there should be some sort of a default value. When using a GUI such as MySQL Administrator or phpMyAdmin, there isn't really a convenient way to tell it "default to an empty string" given the input fields available when constructing a table. I can work around this by either setting columns to NULL, or by typing in command-line SQL code to set up the new fields so that they are explicitly told DEFAULT = ''. But this is time-consuming and I would have to go back and do it for a bunch of old databases I created in MySQL 4 that are now running in MySQL 5. I can't just ignore it, because I have a lot of situations where there are INSERT statements in my code that do not specify a value for the new field (they were written before the new field existed). My expectation is that MySQL will just leave these fields empty upon insert, rather than throwing an error because I didn't specify a value.
This difference seems to be consistent regardless of whether I use a GUI to create a field, or whether I use command-line SQL to say something like "ALTER TABLE tablename ADD columnname VARCHAR(255) NOT NULL". So that narrows it down to MySQL intself and takes the blame off of the GUI.Is there any server-level setting I can change in MySQL 5 to get it to behave like MySQL 4 did in this situation?
How to repeat:
Add a VARCHAR column to a table, set to NOT NULL, without specifying a default value. Do this in both MySQL 4 and MySQL 5. Do a "desc" SQL command to see the table specs for both, or use a GUI to export SQL code for both tables. Note that one will have DEFAULT = '' and one will not.
Suggested fix:
Need a server-level setting to cause mySQL 5 to always assume that NOT NULL fields will default to an empty string.
