cast support detection fails for old mysql version

[Former user]

bug detect on every sqlAlchemy version 0.5.x,0.6.x,0.7.x

I'm using two old database mysql 4.0.22 and mysql 4.0.20

sqlAlchemy try to detect 'cast' support using server_version_info. the default level is 4.0.2 which cause this error

ProgrammingError: (1064, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(60)) AS anon_1' at line 1")

to correctif this I edit : sqlalchemy-0.7.1-py2.7-win32.egg\sqlalchemy\dialects\mysql\base.py and change line 1815 (nearby ...)

@property
def _supports_cast(self):
    return self.server_version_info is None or \
                self.server_version_info >= (4, 0, 23)

instead of :

@property
def _supports_cast(self):
    return self.server_version_info is None or \
                self.server_version_info >= (4, 0, 2)

I don't know if it's the right answer. In my opinion 4.1.0 should be the right target. Old mysql version are not download available, so testing is not easy ...

[Mike Bayer]

ill just bump it to 23 and we'll be done with it, I don't know that this is something people are really hitting much.

[Former user]

thank's a lot zzzeek you save my life, each time correcting eggs mysql/base.py file

[Mike Bayer]

OK a little weird, 4.0.2 is documented as the actual version:

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.

so maybe because you're attempting to CAST to BINARY or CHAR, two types that aren't supported before 4.1.1.

I can't really blow away CAST for 4.0.X since its supported, people might be using it.

Here is a method you can use now in 0.6 or 0.7 to manually override CAST:

from sqlalchemy.sql.expression import _Cast
from sqlalchemy.ext.compiler import compiles

@compiles(_Cast, 'mysql')
def _check_mysql_version(element, compiler, **kw):
    if compiler.dialect.server_version_info < (4, 1, 0):
        return compiler.process(element.clause, **kw)
    else:
        return compiler.visit_cast(element, **kw)


if __name__ == '__main__':
    from sqlalchemy import create_engine, String, select, cast

    e = create_engine('mysql://scott:tiger@localhost/test', echo=True)

    print e.execute(select([String)](cast(1,))).first()

    e.dialect.server_version_info = (4, 0, 2)

    print e.execute(select([String)](cast(1,))).first()

[Mike Bayer]

96a42aa9f5173d8819a839f53c02c1a5dac4f749 adds this workaround to the documentation.

[Mike Bayer]

Removing milestone: 0.6.9 (automated comment)