MySQL Bugs: #38644: In the table editor, NOT NULL columns' Default Value is reported as NULL

Bug #38644	In the table editor, NOT NULL columns' Default Value is reported as NULL
Submitted:	7 Aug 2008 23:37	Modified:	26 May 2009 12:50
Reporter:	Federico Razzoli (OCA)	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Linux (Ubuntu 8.04)
Assigned to:		CPU Architecture:	Any

Description:
Edit an existing table that has a NOT NULL column. In the Table Editor, you will see that the default value for that column is NULL... of course, that's impossible.

As a result, you can delete NULL and click on Apply Changes.

How to repeat:
1. Right-click on the table and click on Edit
2. Look at the Default Value column in the grid for NOT NULL columns

Thank you for a problem report.

It is a DBA's responsibility to correctly set the value of [Default Value].  To have a value of anything other than NULL would only make less sense than the issue you are currently reporting.

If you whish for  [Default Value] to = an actual null string("") then this is actually S4 issue.

FYI,
A similar bug has been recently fixed in R13 release http://bugs.mysql.com/bug.php?id=36076

I don't understand. If the column is NOT NULL, then it should not be NULL, even if I didn't set a default value. Reasons:
1) in the server NULL is not the default value for a NULL column
2) if I edit a field with the table editor, I have to change its value to something that is not NULL, else the table editor will try to make it NULL. I think "" or 0 would be good.

1) in the server NULL is not the default value for a NULL column

> It is impossible to insert a NULL row where NOT NULL has been specified.  Nobody knows the default value for NOT NULL column because it is IMPOSSIBLE to replicate.  This is the way it is and always will be.

2) if I edit a field with the table editor, I have to change its value to something that
is not NULL, else the table editor will try to make it NULL.

> DBMS are no brainers they provide a simple service and developers are to see that the solution is properly implemented using their own business logic.

- This 'Traditional MySQL' feature naturaully keeps my database in tune with my software during development phase.

- How is a DBA to have scope of [default values] and inserting random "" everywhere is only going to lead to tables with rubbish data.

- MySQL is a competative product to SQL Server and should be different in its own ways.

I agree totally and I don't know SQL Server.
But in my opinion the GUI should not automatically produce an ALTER TABLE which is not correct.

ALTER TABLE ... NOT NULL ... DEFAULT NULL

is a command written in an unknown alien language, not SQL, so the server refuses to execute it (and I think it's logical). To avoid this I can:
1) use the GUI having care of deleting undesired NULLs from NOT NULL fields's [default value]
2) don't use the GUI and manually write my ALTER TABLEs, so that no one tries to impose me unwanted NULLs.

The second solution is simpler and faster, but if it's really a designe choice it's okay for me.

Bug is duplicate of http://bugs.mysql.com/bug.php?id=36076

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. Unfortunately you are using an unsupported platform. More informations about supported platforms you will find here:

http://www.mysql.com/support/supportedplatforms/tools.html

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/