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.
