search

datamapper / do

DataObjects
147 stars 74 forks source link

Only 4.1 and later supports setting sql_mode on a per session basis #49

Closed xertres closed 11 years ago

xertres commented 11 years ago

According to MySQL 4.1 Docs its not possible to set sql_mode on a session basis before version 4.1.

dkubb commented 11 years ago

If we're selecting specific sql_mode entries based on MySQL server version, should the code be more specific? For example, NO_DIR_IN_CREATE was added in 4.0.15, yet this code may (?) blow up with earlier versions of mysql.

The docs at http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html show when each mode was added in the 4.x series, and later versions of that same doc give the exact versions some 5.0 specific options were added.

In the ideal case, we would specify every single option when it is allowed, since these make MySQL behave in a more strict manner and can help catch bugs that would otherwise have been shallowed. Also, it makes code easier to port forward, since the default behaviour in upcoming MySQL is to be more strict by default.

dbussink commented 11 years ago

My stance in the past has been not to support pre 4.1 version at all. 4.1 was released in October 2004. Also support for 4.1 has already been stopped by MySQL itself (since end of 2009). So basically with still supporting 4.1 at the moment, a version that did not have vendor support for the last 3 years. I think this is already quite extensive support.

Do you have pressing needs for supporting pre 4.1 versions?

xertres commented 11 years ago

We are creating a web api for a service connected to an old legacy MySQL 4.0.18 database, in an effort to move away from MySQL. Once the other systems have been changed and everything goes through the web api, we can simply switch out the database underneath.

The main issue i ran into was that the database server would not accept the client setting sql_mode at all. From what i gather in the documentation, before 4.1 the system administrator was suppose to set the sql_mode globally for the entire server. In 4.1 the feature for setting the sql_mode for the current client session was added, which is what the code tries to do.

I agree what more granular control of the different sql_mode options sounds like a good idea, in this case its a matter of no sql_mode at all.

I'm surprised not anyone else has run into this already, DM is great for gradual migrations like this. And i'm sure there are many places still running ancient versions of MySQL.

As for pressing needs? Yes, but we can always use our own fork.

dbussink commented 11 years ago

Ok, good to know. My question was mainly because if it were just because of what the manual said, it wasn't really worth it in my opinion.

@dkubb As the for trying to be as strict as possible, I think for these really old versions the effort isn't really worth it to do more than this.

dkubb commented 11 years ago

@dbussink ok, fair enough. I didn't realize MySQL 4.1 has been EOL for so long.