In-Portal Issue Tracker - In-Portal CMS
Viewing Issue Advanced Details
707 [In-Portal CMS] Database bug report always 2010-04-25 08:34 2010-11-30 12:48
alex  
alex  
normal  
closed 5.0.3-B2  
fixed  
 
none 5.1.1-B1  
http://groups.google.com/group/in-portal-bugs/browse_thread/thread/a1b9d6680d7442a9
0
0000707: SQL Error during usage of "Re-build Multilanguage Fields" functionality
In MySQL 5.x versions output format of "DESCRIBE" sql statement has changed. In particular it returns "NULL" instead of default column value even for "NOT NULL" fields, that were added to table without specifying actual default value.

That have raised a problem in 3 system places:
1. unit config "Fields" array validator (enable "DBG_VALIDATE_CONFIGS" in "debug.php" and press "Reset Configs Cache" in "System Tools" section.
2. unit config "Fields" array builder (enter table name into "Table Structure" input in "System Tools" section and press "Run" near it)
3. "Re-build Multilanguage Fields" functionality (button in "System Tools" section).

In all mentioned above places NOT NULL fields can have NULL as default value and this can lead to SQL fatal error.

For items "1." and "2." I propose to guess default value using following rule:
 * NULL for fields, that can be NULL
 * empty string for textual fields
 * 0 for numeric fields

For "3." item I propose to omit "DEFAULT ..." statement from "ALTER TABLE ..." database query when we have retrieved "NULL" as default value for "NOT NULL" field.
In global scale phpMyAdmin displays "None", when "DEFAULT ..." statement was omitted during table creation/column adding. When we have default value, then it is used. When we don't have default value, then default value will be:

    * NULL for fields, that can be NULL
    * empty string for textual fields
    * 0 for numeric fields

We need to updated following places to reflect that:

    * remove "DEFAULT ..." statement from "install_schema.sql" file, when it matches default value auto-assigned from previous list
    * update unit config validator to support ommited "DEFAULT ..." statements
    * update translatable field auto-creator (partially what Sergey posted) to skip "DEFAULT ..." in ALTER TABLE statements, when field's default value matches the one, that MySQL will use automatically


This all can be successfully done based on DESCRIBE results. Omitted "DEFAULT ..." statement is shown differently on DESCRIBE sql only since 5.x version of MySQL. Although omitted "DEFAULT ..." statement was allowed in 4.x version of MySQL too, but DESCRIBE showed actual default value, what MySQL will use for NOT NULL fields and not NULL as 5.x MySQL does.
related to 0000845closed  (5.1.1)Dmitry Revising usage of NULL columns 
patch not_null_with_default_null.patch (898) 2010-04-25 08:34
http://tracker.in-portal.org/file_download.php?file_id=477&type=bug
patch processing_missng_default_value_of_db_columns.patch (8,468) 2010-09-12 07:06
http://tracker.in-portal.org/file_download.php?file_id=762&type=bug
Issue History
2010-11-30 12:48 Dmitry Note Added: 0003036
2010-11-30 12:48 Dmitry Status resolved => closed
2010-09-12 12:49 alex Note Added: 0002819
2010-09-12 12:49 alex Status reviewed and tested => resolved
2010-09-12 12:49 alex Fixed in Version => 5.1.1-B1
2010-09-12 12:49 alex Resolution open => fixed
2010-09-12 12:49 alex Changeset attached 5.1.x r13935
2010-09-12 12:15 Dmitry Note Added: 0002818
2010-09-12 12:15 Dmitry Assigned To !COMMUNITY => alex
2010-09-12 12:15 Dmitry Status needs testing => reviewed and tested
2010-09-12 07:09 alex Time Estimate Removed 3 =>
2010-09-12 07:09 alex Note Added: 0002815
2010-09-12 07:09 alex Assigned To alex => !COMMUNITY
2010-09-12 07:09 alex Developer => alex
2010-09-12 07:09 alex Status needs work => needs testing
2010-09-12 07:06 alex File Added: processing_missng_default_value_of_db_columns.patch
2010-09-01 03:56 alex Relationship added related to 0000845
2010-08-27 13:13 alex Time Estimate Added 3
2010-07-31 14:03 Dmitry Assigned To => alex
2010-07-31 14:03 Dmitry Status active => needs work
2010-07-31 14:03 Dmitry Target Version Icebox => 5.1.1
2010-04-25 08:34 alex Note Added: 0001924
2010-04-25 08:34 alex New Issue
2010-04-25 08:34 alex File Added: not_null_with_default_null.patch
2010-04-25 08:34 alex Reference => http://groups.google.com/group/in-portal-bugs/browse_thread/thread/a1b9d6680d7442a9

Notes
(0001924)
alex   
2010-04-25 08:34   
Patch "not_null_with_default_null.patch" fixes 3rd item (provided by Sergey).
(0002815)
alex   
2010-09-12 07:09   
2nd patch includes 1st patch.

Additionally following was made:
1. default value comparison of integer columns wasn't working (e.g. "-1" and "0" were assumed to be equals)
2. check for missing virtual field for each calculated field added


Both additions discovered several inconsistencies in unit configs, but they won't be fixed in context of this task. Probably they will be fixed in one of the related tasks.
(0002818)
Dmitry   
2010-09-12 12:15   
Tested attached patch.

All cases are working, please commit.
(0002819)
alex   
2010-09-12 12:49   
Fix committed to 5.1.x branch. Commit Message:

Fixes 0000707: SQL Error during usage of "Re-build Multilanguage Fields" functionality
(0003036)
Dmitry   
2010-11-30 12:48   
Closing released 5.1.1 task.