In general, I would say it's not a good idea to silently change the
explicitly set length of a field. Either pass the length to the database
and let the database throw an exception, or raise an exception in web2py
(the former is probably preferable, since web2py won't know the true limit
of the particular database version).
Anthony
On Saturday, June 22, 2013 2:16:58 PM UTC-4, Massimo Di Pierro wrote:
>
> There are historical reason for this and I do not oppose to change it
> since modern mysql versions support longer varchar.
> You can change this yourlself:
>
> db = DAL('mysql://...'_
> db._adapter.maxcharlength = 1024 # or whatever you want
>
> If we change the default in web2py this would trigger a migration. Can you
> help us test it?
> Should we change it or remove the check completely. Whatever value we set
> it to (for mysql and other dbs) it will change in the future and we will
> run in the same problem. Should web2py perhaps have a check and raise an
> error? Or should it pass the length to the db without checks?
>
>
>
>
>
>
> On Saturday, 22 June 2013 11:46:33 UTC-5, Chris wrote:
>>
>> Derek, the main issue isn't truncation at insert time. In a web2py
>> DAL-mediated create table statement, DAL code unilaterally reduces MySQL
>> varchar lengths to 255, whether the specific version of MySQL could
>> tolerate a longer varchar or not. The developer says, column abc should be
>> varchar(1000); DAL runs the define_table; the resulting column is
>> varchar(255); no error is raised; developer has no reason to think there's
>> a problem; then inserts data and mySQL silently truncates (corrupts) it.
>> The DAL problem would be equally bad, although easier for the developer to
>> notice / and sooner, if MySQL didn't perform truncation. The fact that
>> MySQL performs truncation silently makes it that much more important that
>> DAL not change the column definition silently.
>>
>> If I use the mysql command line tool, create table xyz (abc
>> varchar(1000)) results in a column of length 1000. If I use the logically
>> equivalent DAL define_table statement, it results in a column of length
>> 255. The two approaches, given the same inputs, really should have the
>> same outcome.
>>
>> The behavior is also inconsistent among DB engines. For any DBMS other
>> then MySQL, DAL enforces a max varchar length of 2^15. However, Oracle 9i
>> has a max varchar length of 4000; SQL Server 2005 has a max of 8000; etc.
>> Many DBMS versions have some limit. In those cases, DAL attempts to
>> define the table per the developer's instructions and returns a
>> DBMS-specific error if a column length is unacceptable. The same should be
>> done for MySQL.
>>
>> The correct behavior is to return an error if the developer issues a
>> define_table statement that can't be fulfilled by the database layer.
>>
>>
>>
>>
>> On Wednesday, June 19, 2013 7:15:44 PM UTC-4, Derek wrote:
>>>
>>> It's not web2py's fault that MySQL silently truncates data.
>>>
>>> http://www.davidpashley.com/blog/databases/mysql/silently-truncated
>>>
>>>
>>> On Wednesday, June 19, 2013 5:52:26 AM UTC-7, Chris wrote:
>>>>
>>>> The main problem is that the failure is silent. It makes no sense that
>>>> a developer declares a field to be length 1000, and DAL arbitrarily
>>>> reduces
>>>> that to 255 instead of providing an error message. I understand why 255
>>>> may be a safe default limit for MySQL; but if so then fail the field
>>>> creation with a relevant error message (and maybe reference Jonathan's
>>>> solution re: how to override maxcharlength if the developer knows the DBMS
>>>> version can handle the larger limit).
>>>>
>>>> In the current 2.5.1 code, looking in dal.py:
>>>>
>>>> class MySQLAdapter(BaseAdapter):
>>>> ....
>>>> maxcharlength = 255
>>>>
>>>> ...
>>>>
>>>> class Table(object):
>>>> ...
>>>> def __init__( self,
>>>> db,
>>>> tablename,
>>>> *fields,
>>>> **args
>>>> ):
>>>> ...
>>>> for field in fields:
>>>> ...
>>>> if db and not field.type in ('text', 'blob', 'json') and \
>>>> db._adapter.maxcharlength < field.length:
>>>> field.length = db._adapter.maxcharlength ### problem
>>>> line
>>>>
>>>>
>>>> I'd recommend the last line above, marked ### problem line, be replaced
>>>> with something like, raise ValueError('Requested field length exceeds
>>>> adapter maxcharlength, cannot create. See http://whatever for a way
>>>> to override this limit.')
>>>>
>>>> ??
>>>>
>>>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
