MySQL doesn't accept VARCHAR with no length specified

[Former user]

When specifying a string type with no length (e.g. types.Unicode()), the generated create table SQL translates this as VARCHAR with no length specified, which MySQL doesn't accept. An intelligent default should be set (e.g. 255 is the max length taking up 1 byte for MySQL, and this is the default used by Hibernate).

Sorry for no test case, but this is day 1 for me learning Python and I was running the Pylons "QuickWiki" tutorial when I ran into this issue.

[Mike Bayer]

I don't like the idea of people setting up a bunch of String columns and issuing CREATE TABLE without their ever having to make a decision on the storage size of the column.

The String() type is also database and type agnostic so I am -1 on guessing what the default size is, even though 255 is a very good guess.

[Former user]

Hi, this is the original bug poster again. I would say that if you allow to specify a string of no length in the first place, then you should not have an error when running the generated SQL, otherwise, you should not allow that function. The 255 chars is suggested only for the MySQL implementation, and I would expect that the default value would/could be different for other vendors. Since all that should be abstracted away, a user should not have to be concerned with those details.

This does not prevent the user from specifying a size, of course, and setting a reasonable default would be helpful for db newbies, versus throwing an error that would immediately force them to delve into vendor db specifics (plenty of time for that later).

[Mike Bayer]

We allow String with no length to support the wide range of use cases other than CREATE TABLE. The String has unicode conversion and expression-producing behavior, and can also be used in CAST expressions - none of these use cases require a length. The length parameter is also superfluous in SQLite, and optional on Postgres. So allowing String with no length is a solid requirement.

OTOH, I think the size of a column being issued within CREATE TABLE is a detail the user should be concerned with. 255 is not as typical of a default as it used to be - VARCHAR on MySQL 5 and above is 65535. So 255 starts to seem arbitrary here and 65535 seems too large.

A second reason i don't like a default here is that SQLAlchemy's philosophy is based on explicitness, especially within the realm of DDL, which is a controversial feature to start with (that is, software that writes CREATE TABLE statements for you other than vendor-supplied tools). DBAs as well as many developers are already terribly frightened by DDL-generating tools, thinking that they make all kinds of automatic decisions which produce substandard schemas without user intervention, and it's critical that SQLA not have the appearance of making schema sizing decisions.

So from my point of view, it's entirely fine to create a String with no length, and it's an error condition if such a datatype is used within a CREATE TABLE on a database which does not support a length-less string. And that is exactly the behavior right now.

I haven't closed this ticket since this is a controversial issue. I welcome you to bring up the issue on the sqlalchemy or sqlalchemy-devel lists and see what the rest of the community thinks. If people really vote for a default of 255 on databases which require this, we can consider it.

[Former user]

I think it's a matter of user experience, versus strict correctness. Ideally, you should be able to swap out databases without having to worry about adjusting the code, assuming you don't use any vendor specific SQL, etc. and I don't consider the String as something that is vendor specific. This is exactly the case I ran into while running the Pylons QuickWiki tutorial. The tutorial by default runs on sqlite, but I switched it with MySQL as part of my evaluation. It worked fine on sqlite, but not on MySQL, leading to a bad impression given that it was such a simple example. I mean, it's not good to have to immediately debug a tutorial, especially when you're evaluating the product.

I didn't mean that the no length String should not be allowed, the point I was trying to (unsuccessfully) make was that there shouldn't be a function that doesn't work across all supported vendors. That would defeat the purpose of a db abstraction layer.

I am not familiar with other vendors, but for MySQL, 255 makes sense to me in that it's the max size available for the minimum required 1 byte prefix. The default value can be different depending on the vendor.

In terms of the controversy, it's true that DBAs often have different/conflicting priorities than the developer, but the "customer" is the developer, not the DBA :-) and soon enough, the developer will get to looking at the generated schema and tweak his code accordingly, but without the immediate annoying error to turn him/her off to using the product in the first place.

I hope I don't come across as too argumentative, I'm just trying to give my viewpoint as a user! Thanks.

[Mike Bayer]

It is a small thing that you can get hit with up front, which is why our tutorial immediately states this right after the first example of "String" with no length. That the behavior is not 100% "abstracted" is actually one of SQLA's philosophies - that its impossible to create a pure abstractional layer, and the underlying details of the database need to be exposed. This is one of the strongest features of SQLA, that its not attempting to pretend the database isn't there. There's lots of constructs and patterns that aren't going to work on all platforms, like insert_returning, selecting from subqueries with no alias name, executing sequences, ordering on a label name, etc. Its not automatic that your application will run on all databases immediately, its just generally fairly easy.

I would like to get some other opinions on this one, though.

[Michael Trier]

I'm -1 on assuming a default length as well. I think we're doing everything we can to get rid of those sort of things where we find them. It's better for us to be explicit about what we want because everyone has a different idea about the assumptions we should make.

[Michael Trier]

According to jek, maybe version specific max for the length in this case. We're moving this to 0.6.

[Mike Bayer]

yeah OK im ready to stop having this issue on IRC so I'd go for the known max. so String on MySQL gives you 65535 or whatever it is on version 5.

[Philip Jenvey]

for the record I would be -1. The Pylons QuickWiki shouldn't have lacked a length in the first place. I fixed this a little while ago but haven't pushed out the new QuickWiki yet -- if the IRCers are doing the QuickWiki blame Ben and I

[Mike Bayer]

at this point we've removed defaults from all types, including Numeric, etc. While its a nice feature to have an abstraction layer that totally makes platform-specific decisions about sizes when you issue CREATE TABLE statements, that's just not where SQLA wants to go, since our customers are the developers and the DBAs (and if there's only developers, we expect them to think like DBAs when creating their schemas), and really going all the way with a "let's make up all the sizes" layers enters the realm of heavy magic - whereas sticking some default sizing logic only here and there is just inconsistent.

There's also lots of error messages you can get out of the box when you start running a tutorial on a different database - "VARCHAR with no length" is not much of a show stopper. So unless a comprehensive solution to all types is proposed this ticket isn't going anywhere for now.

[Mike Bayer]

I'm so sick of people complaining about this error, only on VARCHAR only on MySQL (i.e. #1623), that I will make VARCHAR raise on render with no length. It turns out MySQL doesn't accept VARCHAR in a CAST anyway.

[Mike Bayer]

so no default length, but a preemptive raise during the render, d9388a51944e1c5bb389a33a2cd824a7e8672115. We can continue to make that error message into as much of a detailed essay on what it means as needed.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)