Rebuild multilanguage fields: NOT NULL field with no default value generates error

[Sergejs Gribs]

Latest MySQL versions support NOT NULL fields with no default value. But when you have to rebuild multilanguage fields, and you have some of such fields in table already (for ex., l1_Name & l2_Name), no default value results as NULL, and multilanguage_helper tries to add NOT NULL field with NULL as default value, what leads to SQL error.

Solution is simple: we shouldn't supply default value when we have NOT NULL field and no (or null) default value. Patch is attached.

[Dmitry Andrejev]

Hi Sergey,

Yes, this seems to be the case, but I in your patch you have only applied this to the MultiLanguage formatter while we have other places to do so - correct?

Thanks.

DA.

On Wed, Mar 31, 2010 at 9:51 AM, Sergejs Gribs <serg...@gmail.com> wrote:

Latest MySQL versions support NOT NULL fields with no default value. But when you have to rebuild multilanguage fields, and you have some of such fields in table already (for ex., l1_Name & l2_Name), no default value results as NULL, and multilanguage_helper tries to add NOT NULL field with NULL as default value, what leads to SQL error.

Solution is simple: we shouldn't supply default value when we have NOT NULL field and no (or null) default value. Patch is attached.

--
You received this message because you are subscribed to the Google Groups "In-Portal Bugs Team" group.
To post to this group, send email to in-port...@googlegroups.com.
To unsubscribe from this group, send email to in-portal-bug...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/in-portal-bugs?hl=en.

[S.G.]

Patch is for kMultiLanguageHelper, not formatter. May be there are
some more places to fix similar bug (I didn't check), but after this
one multilanguage field rebuild definitely works.

On Apr 1, 12:21 am, Dmitry Andrejev <dandre...@gmail.com> wrote:
> Hi Sergey,
>
> Yes, this seems to be the case, but I in your patch you have only applied
> this to the MultiLanguage formatter while we have other places to do so -
> correct?
>
> Thanks.
>
> DA.
>

> On Wed, Mar 31, 2010 at 9:51 AM, Sergejs Gribs <sergb...@gmail.com> wrote:
> > Latest MySQL versions support NOT NULL fields with no default value. But
> > when you have to rebuild multilanguage fields, and you have some of such
> > fields in table already (for ex., l1_Name & l2_Name), no default value
> > results as NULL, and multilanguage_helper tries to add NOT NULL field with
> > NULL as default value, what leads to SQL error.
>
> > Solution is simple: we shouldn't supply default value when we have NOT NULL
> > field and no (or null) default value. Patch is attached.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "In-Portal Bugs Team" group.
> > To post to this group, send email to in-port...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>

[Dmitry Andrejev]

Yes, you are right I meant to say kMultiLanguageHelper.

I guess we'll be checking for more places within the system.

Thanks!

DA.

To unsubscribe from this group, send email to in-portal-bug...@googlegroups.com.

[Alexander Obuhovich]

This seems to be a bit phpMyAdmin bug, that we are using. Since latest pma version allows to create table with empty string as default value, but our pma version creates field with "None" as default value (displayed in pma). Don't know how this "None" is interpreted by PHP, maybe as NULL.

I think, that developers should understand, that if we have field in database, then it must have default value of some kind (except for "TEXT" type fields), since we already have NULL to detect if value was set to field, isn't "None" value confusing?

By the way that patch does this:

when it sees, that we have NOT NULL field with NULL as default value (is this even possible?) then it sets default value to ''. If you (Segey) say, that  presumably MySQL support fields without default value, then you should ommit "DEFAULT " part at all in ALTER TABLE clause. 

What do you think?

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

[S.G.]

Alex,

1) Since MySQL support NOT NULL fields without default value, this
shouldn't lead to SQL error in our system, independently of what
developers or applications like PMA configured in DB.

2) When using DESCRIBE, no default value (None in PMA table structure)
returns as NULL.

3) If you'll look again at patch with attention, you'll see that in
case when field is NOT NULL and returned by DESCRIBE value is NULL
(which means no default value), then DEFAULT part is omitted.

On Apr 2, 1:06 am, Alexander Obuhovich <aik.b...@gmail.com> wrote:
> This seems to be a bit phpMyAdmin bug, that we are using. Since latest pma
> version allows to create table with empty string as default value, but our
> pma version creates field with "None" as default value (displayed in pma).
> Don't know how this "None" is interpreted by PHP, maybe as NULL.
>
> I think, that developers should understand, that if we have field in
> database, then it must have default value of some kind (except for "TEXT"
> type fields), since we already have NULL to detect if value was set to
> field, isn't "None" value confusing?
>
> By the way that patch does this:
> when it sees, that we have NOT NULL field with NULL as default value (is
> this even possible?) then it sets default value to ''. If you (Segey) say,
> that  presumably MySQL support fields without default value, then you should
> ommit "DEFAULT " part at all in ALTER TABLE clause.
>
> What do you think?
>
>
>
> On Thu, Apr 1, 2010 at 3:27 PM, Dmitry Andrejev <dandre...@gmail.com> wrote:
> > Yes, you are right I meant to say kMultiLanguageHelper.
>
> > I guess we'll be checking for more places within the system.
>
> > Thanks!
>
> > DA.
>

> >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com>

>
> >> > > .
> >> > > For more options, visit this group at
> >> > >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "In-Portal Bugs Team" group.
> >> To post to this group, send email to in-port...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> >> .
> >> For more options, visit this group at
> >>http://groups.google.com/group/in-portal-bugs?hl=en.
>
> >  --
> > You received this message because you are subscribed to the Google Groups
> > "In-Portal Bugs Team" group.
> > To post to this group, send email to in-port...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> --

> Best Regards,
>
> http://www.in-portal.comhttp://www.alex-time.com

[Alexander Obuhovich]

So in both (when default value is not specified and when it is set to NULL) field will have NULL in it's value after INSERT INTO statement is executed. This also leads to conclusion, that automatic field builder also incorrectly processes absence of default value for field.

Sorry about quick patch judgement, I will look closely again, when I will be back to my pc with TortoiseDiff installed. 

To unsubscribe from this group, send email to in-portal-bug...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/in-portal-bugs?hl=en.

[Alexander Obuhovich]

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.

[S.G.]

Removing DEFAULT statement from install schema is optional, since it's
presence doesn't break anything.

On Apr 6, 8:35 pm, Alexander Obuhovich <aik.b...@gmail.com> wrote:
> 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.
>

> On Sat, Apr 3, 2010 at 12:46 AM, Alexander Obuhovich <aik.b...@gmail.com>wrote:
>
>
>
> > So in both (when default value is not specified and when it is set to NULL)
> > field will have NULL in it's value after INSERT INTO statement is executed.
> > This also leads to conclusion, that automatic field builder also incorrectly
> > processes absence of default value for field.
>
> > Sorry about quick patch judgement, I will look closely again, when I will
> > be back to my pc with TortoiseDiff installed.
>

> >> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com>
> >> <in-portal-bugs%252Buns...@googlegroups.com<in-portal-bugs%25252Bun...@googlegroups.com>

> >> > Best Regards,
>
> >> >http://www.in-portal.comhttp://www.alex-time.com
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "In-Portal Bugs Team" group.
> >> To post to this group, send email to in-port...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> >> .
> >> For more options, visit this group at
> >>http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > --
> > Best Regards,
>

[Dmitry A.]

So what we should do here as a solution - course of action?

DA.

> > >> > >> > > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>
>
> > >> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>
> > >> <in-portal-bugs%252Buns...@googlegroups.com<in-portal-bugs%25252Bunsub scr...@googlegroups.com>

>
> > >> > >> > > .
> > >> > >> > > For more options, visit this group at
> > >> > >> > >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > >> > >> --
> > >> > >> You received this message because you are subscribed to the Google
> > >> Groups
> > >> > >> "In-Portal Bugs Team" group.
> > >> > >> To post to this group, send email to in-port...@googlegroups.com
> > >> .
> > >> > >> To unsubscribe from this group, send email to

> > >> > >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>

>
> > >> > >> .
> > >> > >> For more options, visit this group at
> > >> > >>http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > >> > >  --
> > >> > > You received this message because you are subscribed to the Google
> > >> Groups
> > >> > > "In-Portal Bugs Team" group.
> > >> > > To post to this group, send email to in-port...@googlegroups.com.
> > >> > > To unsubscribe from this group, send email to

> > >> > > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>

>
> > >> > > .
> > >> > > For more options, visit this group at
> > >> > >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > >> > --
> > >> > Best Regards,
>
> > >> >http://www.in-portal.comhttp://www.alex-time.com
>
> > >> --
> > >> You received this message because you are subscribed to the Google Groups
> > >> "In-Portal Bugs Team" group.
> > >> To post to this group, send email to in-port...@googlegroups.com.
> > >> To unsubscribe from this group, send email to

> > >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsubscribe@go oglegroups.com>

[Alexander Obuhovich]

I already told my opinion after words "We need to updated following places to reflect that:" in my post from 6th of April.

To unsubscribe from this group, send email to in-portal-bug...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/in-portal-bugs?hl=en.

[Dmitry Andrejev]

Alex would you please convert this in task and properly describe things we need to do.

Last thing we should do is let this discussion to go away.

Thanks!

DA.

[S.G.]

Alex,

Why

"remove "DEFAULT ..." statement from "install_schema.sql" file, when

it matches default value auto-assigned from previous list"?

It's not wrong if we pass default value in case we know it, but making
as you described puts us on risk that default value won't match what
we expect if in any future version MySQL default value auto-assigment
logic will change. Same for

"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".

Only case when we need to skip "DEFAULT ..." is not null field with no
default value (what is initially posted). Please avoid leak work
amount for this. If you don't fully understand my position, or
disagree we may discuss it personally later.

On Apr 9, 12:51 pm, Alexander Obuhovich <aik.b...@gmail.com> wrote:
> I already told my opinion after words "We need to updated following places
> to reflect that:" in my post from 6th of April.
>

> > > > >> > >> > > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> > <in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > > > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com><in-portal-bugs%252Bunsubscri
> > b...@googlegroups.com>
>
> > > > >> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com><in-portal-bugs%252Bunsubscri
> > b...@googlegroups.com>
> > > > >> <in-portal-bugs%252Buns...@googlegroups.com<in-portal-bugs%25252Bun...@googlegroups.com><in-portal-bugs%25252Bunsub

> > scr...@googlegroups.com>
>
> > > > >> > >> > > .
> > > > >> > >> > > For more options, visit this group at
> > > > >> > >> > >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > > > >> > >> --
> > > > >> > >> You received this message because you are subscribed to the
> > Google
> > > > >> Groups
> > > > >> > >> "In-Portal Bugs Team" group.
> > > > >> > >> To post to this group, send email to
> > in-port...@googlegroups.com
> > > > >> .
> > > > >> > >> To unsubscribe from this group, send email to

> > > > >> > >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> > <in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > > > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com><in-portal-bugs%252Bunsubscri

> > b...@googlegroups.com>
>
> > > > >> > >> .
> > > > >> > >> For more options, visit this group at
> > > > >> > >>http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > > > >> > >  --
> > > > >> > > You received this message because you are subscribed to the
> > Google
> > > > >> Groups
> > > > >> > > "In-Portal Bugs Team" group.
> > > > >> > > To post to this group, send email to
> > in-port...@googlegroups.com.
> > > > >> > > To unsubscribe from this group, send email to

> > > > >> > > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>
> > <in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > > > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Buns...@googlegroups.com><in-portal-bugs%252Bunsubscri

> > b...@googlegroups.com>
>
> > > > >> > > .
> > > > >> > > For more options, visit this group at
> > > > >> > >http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > > > >> > --
> > > > >> > Best Regards,
>
> > > > >> >http://www.in-portal.comhttp://www.alex-time.com
>
> > > > >> --
> > > > >> You received this message because you are subscribed to the Google
> > Groups
> > > > >> "In-Portal Bugs Team" group.
> > > > >> To post to this group, send email to
> > in-port...@googlegroups.com.
> > > > >> To unsubscribe from this group, send email to

> > > > >> in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>

> > <in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> > > > >> .
> > > > >> For more options, visit this group at
> > > > >>http://groups.google.com/group/in-portal-bugs?hl=en.
>
> > > > > --
> > > > > Best Regards,
>
> > > > >http://www.in-portal.com
> > > > >http://www.alex-time.com
>
> > > > --
> > > > Best Regards,
>
> > > >http://www.in-portal.comhttp://www.alex-time.com
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "In-Portal Bugs Team" group.
> > To post to this group, send email to in-port...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsu...@googlegroups.com>

> > .
> > For more options, visit this group at
>

> ...
>
> read more »

[Alexander Obuhovich]

It seems, that you are afraid of big changes and based on your overall mysql/php experience I will agree with you on that matter.

So we need to update following places (to properly process DESCRIBE sql output from MySQL 5+ versions):

• unit config field validator
• automatic field array builder for unit config
• multilingual helper to make proper ALTER statements (from Sergey's patch)
  

[Dmitry Andrejev]

Sergey, Alex - I am glad we are moving toward resolution and creation of the task here.

Alex, please finalize and create the task when you feel ready.

Thanks to all here!

DA.

[Dmitry A.]

Quick reminder here.

On Apr 17, 7:39 pm, Dmitry Andrejev <dandre...@gmail.com> wrote:
> Sergey, Alex - I am glad we are moving toward resolution and creation of the
> task here.
>
> Alex, please finalize and create the task when you feel ready.
>
> Thanks to all here!
>
> DA.
>

> On Sat, Apr 17, 2010 at 4:23 AM, Alexander Obuhovich <aik.b...@gmail.com>wrote:
>
>
>
> > It seems, that you are afraid of big changes and based on your overall
> > mysql/php experience I will agree with you on that matter.
>
> > So we need to update following places (to properly process DESCRIBE sql
> > output from MySQL 5+ versions):
>

> >    - unit config field validator
> >    - automatic field array builder for unit config
> >    - multilingual helper to make proper ALTER statements (from Sergey's
> >    patch)

> >> > > > > >> > >> > > in-portal-bug...@googlegroups.com<in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>
>
> >> > > <in-portal-bugs%2Bunsubscribe@go oglegroups.com>
> >> > > > > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>

> >> <in-portal-bugs%252Buns...@googlegroups.com<in-portal-bugs%25252Bunsub scr...@googlegroups.com>

> >> ><in-portal-bugs%252Bunsubscri
> >> > > b...@googlegroups.com>
>
> >> > > > > >> > >> <in-portal-bugs%2Bunsu...@googlegroups.com<in-portal-bugs%252Bunsubscri b...@googlegroups.com>
>

[Alexander Obuhovich]

Task created: http://tracker.in-portal.org/view.php?id=707

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com