Data missing after field optimization

Hopefully I won't look like too much of a numbskull here but =
after reading some sites on table optimization, I decided to remove the =
NULL as default on the fields in my products table. I thought =
everything went well until I realized that we hadn't received any orders =
for 2 days. That's when I realized that my products table was empty! =
Luckily this happened over the weekend. =3D:\

I've made changes to field types before and never had a problem =
like this. Does anyone know what went wrong from my limited =
description? Why would removing the NULL default cause data to be lost?

Thanks,
Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Data missing after field optimization

----- Original Message -----
> From: sono-io@fannullone.us
>
> description? Why would removing the NULL default cause data to be
> lost?

What exactly do you mean by "removing the NULL default"? Did you set your colums NOT NULL?


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Data missing after field optimization

On Jun 6, 2011, at 10:06 PM, Johan De Meersman wrote:

> What exactly do you mean by "removing the NULL default"? Did you set =
your colums NOT NULL?

Yes. That's all I did.

Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Data missing after field optimization

> On Jun 6, 2011, at 10:06 PM, Johan De Meersman wrote:
> > What exactly do you mean by "removing the NULL default"? Did you set
> > your colums NOT NULL?
>
> Yes. That's all I did.

In stead of getting info drop-by-drop, you might want to share the output of
SHOW CREATE TABLE...,, but my guess is you had a UNIQUE key somewhere that got
violated when converting to NULL's to empty strings. If would require an ALTER
IGNORE TABLE... instead of a normal ALTER TABLE though.

That, or an outside source (code we cannot see querying for NOT NULLs etc.)
--
Rik Wasmus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Data missing after field optimization

----- Original Message -----
> From: sono-io@fannullone.us
>
> Yes. That's all I did.

If that's all you did, you indeed 'removed the default NULL' but did not specify another default. Hence, if you don't explicitly specify a value in your insert statement, the insert can not happen as the server doesn't know what to put there and is explicitly disallowed from leaving the value empty.

Where did you find the advice about setting columns NOT NULL? You really shouldn't take everything you read on the internet as truth :-)

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Data missing after field optimization

On Jun 7, 2011, at 10:43 PM, Johan De Meersman wrote:

> If that's all you did, you indeed 'removed the default NULL' but did =
not specify another default. Hence, if you don't explicitly specify a =
value in your insert statement, the insert can not happen as the server =
doesn't know what to put there and is explicitly disallowed from leaving =
the value empty.

That makes sense. So then why does phpMyAdmin allow you to =
choose "None" as a default?

> Where did you find the advice about setting columns NOT NULL?

I can't find them today, but I read on a couple of web sites =
that not populating fields with NULL would make the tables more =
efficient.

I have a shopping cart that has every field as type TEXT and I'm =
going through it with a fine tooth comb trying to make it more =
efficient.

> You really shouldn't take everything you read on the internet as truth =
:-)

NOW you tell me! =3D;) I guess I learned my lesson the hard =
way.

Thanks again,
Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Data missing after field optimization

>> If that's all you did, you indeed 'removed the default NULL' but did not
>specify another default. Hence, if you don't explicitly specify a value in
>your
>insert statement, the insert can not happen as the server doesn't know what
>to
>put there and is explicitly disallowed from leaving the value empty.
>
> That makes sense. So then why does phpMyAdmin allow you to choose "None"
>as a default?

[JS] There are times when you want to enforce certain conditions even though
they might sometimes generate an error. Two examples are required fields (no
default value) or referential integrity.

The goal is not to lose data, but to make sure your applications are doing
what they are supposed to do. You would program your applications to trap and
report errors.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Data missing after field optimization

On Jun 7, 2011, at 10:43 PM, Johan De Meersman wrote:

> Where did you find the advice about setting columns NOT NULL?

It took me awhile, but I just found it again, in case anyone is =
interested:

http://dev.mysql.com/doc/refman/5.0/en/data-size.html


7.8.1. Make Your Data as Small as Possible

Declare columns to be NOT NULL if possible. It makes everything faster =
and you save one bit per column. If you really need NULL in your =
application, you should definitely use it. Just avoid having it on all =
columns by default.

Marc=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Data missing after field optimization

Heeh. That's not a random blog post, that's the official manual :-p

If it's in there, it's pretty much trustworthy; and I've learned something new :-) So apparently there's a dedicated "NULL" bit to columns... Yes, then there would be a small performance benefit.

I stand corrected. Still, as you've noticed, don't change the design of an existing application without thoroughly testing the consequences :-p


----- Original Message -----
> From: sono-io@fannullone.us
> To: mysql@lists.mysql.com
> Sent: Tuesday, 14 June, 2011 7:34:04 PM
> Subject: Re: Data missing after field optimization
>
> It took me awhile, but I just found it again, in case anyone is
> interested:
>
> http://dev.mysql.com/doc/refman/5.0/en/data-size.html
>
>
> 7.8.1. Make Your Data as Small as Possible
>
> Declare columns to be NOT NULL if possible. It makes everything
> faster and you save one bit per column. If you really need NULL in
> your application, you should definitely use it. Just avoid having
> it on all columns by default.
>
> Marc

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org