SA on MySQL 3.23

[King Simon-NFHD78]

Hi,

According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23 should be
supported in some form. However, with SA 0.6.1 and MySQL 3.23.58, I get
the following error:

>>> import sqlalchemy as sa
>>> e = sa.create_engine('mysql://user:password@host')
>>> e.execute('select "Hello World"')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1714, in execute
connection = self.contextual_connect(close_with_result=True)
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1742, in contextual_connect
self.pool.connect(),
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
157, in connect
return _ConnectionFairy(self).checkout()
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
321, in __init__
rec = self._connection_record = pool.get()
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
176, in get
return self.do_get()
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
670, in do_get
con = self.create_connection()
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
137, in create_connection
return _ConnectionRecord(self)
File "/ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py", line
217, in __init__
l.first_connect(self.connection, self)
File
"/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/strategies.py",
line 145, in first_connect
dialect.initialize(c)
File
"/ve/sa6/lib/python2.5/site-packages/sqlalchemy/dialects/mysql/base.py",
line 1755, in initialize
default.DefaultDialect.initialize(self, connection)
File
"/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line
138, in initialize
self.returns_unicode_strings =
self._check_unicode_returns(connection)
File
"/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line
183, in _check_unicode_returns
unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60))
File
"/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line
173, in check_unicode
]).compile(dialect=self)
File
"/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File
"/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your
SQL syntax near '('test unicode returns' AS CHAR(60)) AS anon_1' at line
1")

According to http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
the CAST function was added in 4.0.2.

Is there any way that I can avoid this error? Perhaps with some engine
or dialect option that configures the returns_unicode_strings attribute
without running the test?

Thanks a lot,

Simon

[Michael Bayer]

On Jun 3, 2010, at 1:15 PM, King Simon-NFHD78 wrote:

> Hi,
>
> According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23 should be
> supported in some form. However, with SA 0.6.1 and MySQL 3.23.58, I get
> the following error:
>

> raise errorclass, errorvalue
> _mysql_exceptions.ProgrammingError: (1064, "You have an error in your
> SQL syntax near '('test unicode returns' AS CHAR(60)) AS anon_1' at line
> 1")
>
>
> According to http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
> the CAST function was added in 4.0.2.
>
> Is there any way that I can avoid this error? Perhaps with some engine
> or dialect option that configures the returns_unicode_strings attribute
> without running the test?
>

heh wow, that little test we've added is proving to be quite a PITA. OK so in this case its the CAST thats barfing ? the options we could do here are:

1. have cast() do nothing with the MySQL dialect if the MySQL version < 4.0.2 (is there some MySQL-specific syntax that works maybe ?)
2. have the MySQL dialect not run _check_unicode_returns if the version < 4.0.2
3. put the unicode checks in a try/except and default the returns to False if something didn't work

since i dont have an old MySQL installed here, do you need me to give you patches for these so you can test ?

[King Simon-NFHD78]

I'll happily try any suggestions you've got :-)

I couldn't see anything in the MySQL docs that suggested an alternative
to the CAST function, so it seems reasonable to just omit it for older
MySQL servers. I applied the attached patch, and it at least allowed me
to connect to the server and issue basic queries, but I haven't done any
more testing than that.

Option 1 sounded best to me just because I didn't know if there would be
any other places that SA might implicitly run a query that included a
CAST. I suppose it changes the semantics of the query though...

I've tried to run the unit tests, but I get lots of errors and failures
that I assume are expected on such an old version of MySQL.

Cheers,

Simon

[Michael Bayer]

On Jun 4, 2010, at 6:54 AM, King Simon-NFHD78 wrote:

I'll happily try any suggestions you've got :-)

I couldn't see anything in the MySQL docs that suggested an alternative
to the CAST function, so it seems reasonable to just omit it for older
MySQL servers. I applied the attached patch, and it at least allowed me
to connect to the server and issue basic queries, but I haven't done any
more testing than that.

Option 1 sounded best to me just because I didn't know if there would be
any other places that SA might implicitly run a query that included a
CAST. I suppose it changes the semantics of the query though...

I've tried to run the unit tests, but I get lots of errors and failures
that I assume are expected on such an old version of MySQL.

its not entirely my usual style to have an operator emit nothing on a given platform instead of failing, but because this is such an old MySQL version and "cast" is a little bit of a crossover operator it isn't bothering me much here.   I can commit your patch with an extra "artificial" compiler test in dialect/test_mysql.py to ensure it does what's expected; if you want to tool around with it a bit this week, let me know that we're good with it.

 

Cheers,

Simon

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

<mysql_no_cast.patch>

[King Simon-NFHD78]

> -----Original Message-----
> From: sqlal...@googlegroups.com
> [mailto:sqlal...@googlegroups.com] On Behalf Of Michael Bayer
> Sent: 04 June 2010 14:42
> To: sqlal...@googlegroups.com
> Subject: Re: [sqlalchemy] SA on MySQL 3.23
>
>

If you're more comfortable with a version that just doesn't call
_check_unicode_returns, or that catches the exception, either would be
fine with me. I just sent the first thing I tried that seemed to work. I
agree that silently converting CAST to nothing might mask other bugs,
and so probably isn't ideal.

Which would be your preference then? Catching the exception, or not
calling the method in the first place? I'll make a patch for whichever
you prefer and test it next week.

Thanks again,

Simon

[Michael Bayer]

On Jun 4, 2010, at 12:43 PM, King Simon-NFHD78 wrote:

If you're more comfortable with a version that just doesn't call
_check_unicode_returns, or that catches the exception, either would be
fine with me. I just sent the first thing I tried that seemed to work. I
agree that silently converting CAST to nothing might mask other bugs,
and so probably isn't ideal.

Which would be your preference then? Catching the exception, or not
calling the method in the first place? I'll make a patch for whichever
you prefer and test it next week.

doing the "cast" thing at least allows us to get an answer to the unicode question from mysql 3.  If you can make a trac ticket targeted at 0.6.2 milestone we can get started.   It just needs a test case in dialect/test_mysql (it would create a dialect, hardcode server version to 3.23, assert that compiling cast(foo) produces the expected string).  I can do the test part.