ERROR when MYSQL try to CAST() to LONGTEXT

[André Kablu]

I have a mysql database and created a LIST:INTEGER field

It is created in MYSQL as LONGTEXT type of data


In one of my queries I have received an error:

WEB2PY QUERY:
db.table.id_field_list.contains(db.table2.id)

MYSQL QUERY:
SELECT * FROM table, table2 WHERE (table.id_field_list LIKE (CONCAT('%|',(REPLACE((REPLACE(CAST(table2.id AS LONGTEXT),'%','%%')),'|','||')),'|%')))

The error is with CAST()

Reading mysql documentation, CAST() does not support LONGTEXT
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

And there are some discussions on google about CAST with LONGTEXT in MYSQL that also said it is not compatible


If you want to test this behavior, try this command in MYSQL:


SELECT CAST(123 as LONGTEXT);
you will receive the error

then try:

SELECT CAST(123 as char);
and everything goes fine


Is there any way to fix this issue in official version by removing the CAST from list: fields for MYSQL or any other better idea?

Thanks

[Massimo Di Pierro]

I have a possible fix in trunk. Can you help test it?

[André Kablu]

Sure. Did you pushed it? I cannot find it there...

Many thanks!

[Massimo Di Pierro]

Yes. Is this sufficient?

https://github.com/web2py/web2py/blob/master/gluon/dal.py#L2671

[Kablu®]

Thanks Massimo!

The fix is working fine!

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/P2WpYst-kps/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.