Major diff between changing a VARCHAR to 70 or 100?

[Database Administrator]

"Harald Fuchs" wrote in message
news:87r79w4stx.fsf@srv.protecting.net...
> In article <11ml7ctkk8g3399@corp.supernews.com>,
> "Laphan" writes:
>

> If you try to keep your field sizes tight, then you shouldn't use MySQL:
> if you make one of your fields by accident somewhat too tight, MySQL
> (at least before version 5) will silently destroy some of your data.


Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
dependent feature; rather it is a user selected pluggable database version
issue.
As I understand it, some of the engines will recoil and cancel transaction
(like INNODB).

~ Duane Phillips.

[Database Administrator]

In article ,
"Duane Phillips" writes:

> "Harald Fuchs" wrote in message
> news:87r79w4stx.fsf@srv.protecting.net...
>> In article <11ml7ctkk8g3399@corp.supernews.com>,
>> "Laphan" writes:
>>
>
>> If you try to keep your field sizes tight, then you shouldn't use MySQL:
>> if you make one of your fields by accident somewhat too tight, MySQL
>> (at least before version 5) will silently destroy some of your data.
>

> Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
> dependent feature; rather it is a user selected pluggable database version
> issue.
> As I understand it, some of the engines will recoil and cancel transaction
> (like INNODB).

Unfortunately it's a frontend issue - I destroyed most of my data by
erroneously doing a

ALTER TABLE mytbl MODIFY mycol TEXT NOT NULL

where mytbl was an InnoDB table. mycol originally was MEDIUMTEXT and
I only wanted to set the NOT NULL attribute.

Even MySQL 5.0.15 with "sql_mode = 'traditional'" does not prevent that.

[Database Administrator]

Harald Fuchs wrote:
> In article ,
> "Duane Phillips" writes:
>
>
>>"Harald Fuchs" wrote in message
>>news:87r79w4stx.fsf@srv.protecting.net...
>>
>>>In article <11ml7ctkk8g3399@corp.supernews.com>,
>>>"Laphan" writes:
>>>
>>
>>
>>
>>>If you try to keep your field sizes tight, then you shouldn't use MySQL:
>>>if you make one of your fields by accident somewhat too tight, MySQL
>>>(at least before version 5) will silently destroy some of your data.
>>
>>
>
>
>>Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
>>dependent feature; rather it is a user selected pluggable database version
>>issue.
>>As I understand it, some of the engines will recoil and cancel transaction
>>(like INNODB).
>
>
> Unfortunately it's a frontend issue - I destroyed most of my data by
> erroneously doing a
>
> ALTER TABLE mytbl MODIFY mycol TEXT NOT NULL
>
> where mytbl was an InnoDB table. mycol originally was MEDIUMTEXT and
> I only wanted to set the NOT NULL attribute.
>
> Even MySQL 5.0.15 with "sql_mode = 'traditional'" does not prevent that.


So, what's the problem? It did exactly what you told it to - like it
should.

I you have valuable data, just ensure you backup the table before you
alter it!


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Database Administrator]

Laphan wrote:
> Hi Guys
>
> Many thanks for the feedback.
>
> I know this is a little off topic, but I use decimal types for my price
> figures is there any problem in expanding these to say (13,6) when they are
> currently (10,3).
>
> Problems in that I am talking file size, query performance, etc.
>
> Thanks
>
>

It will add 3 bytes to each row in the table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================