Rake test not observing mysql table type

Rails
1

I’ve run into a bit of a problem.

When ‘rake test’ recreates the development database in test, it’s
defaulting mysql table types to Innodb. (Except 'schema_info which is
being set to MyISAM)

For the most part I prefer Innodb, but I have one table that can only
be implemented in MyISAM - This table cannot be implemented in InnoDB.

And thus tests are bailing.

Setting the engine type in schema.rb doesn’t help, as it looks like
schema is regenerated (and table options are lost)

  • Table type in development is MyISAM.

  • Default table type in mysql is MyISAM.

There are a couple of threads on this topic earlier in 2006, without
resolution. Anybody find a solution to this problem?

Thanx all.
Jodi

2

On 10/31/06, Jodi S. [email protected] wrote:

And thus tests are bailing.

Thanx all.
Jodi

Are you absolutely positive the table in question in the dev db is
myisam? Are there any non-native things in the table that
SchemaDumper might not be able to handle, like enums or a primary key
not named “id” ?

Also, set the schema format to sql and give that a try.

3

Thanx Rob, setting

config.active_record.schema_format = :sql

corrected the problem.

Rob (anyone?) - I’d like to know the implications of the above? I’ve
looked for docs on Config - nothing on api.rubonrails.com.
* schema.db lokss the same, but now have development_structure.sql
* does this impact migrations? Or just cloning of db structure to test?

Are you absolutely positive the table in question in the dev db is
myisam? Are there any non-native things in the table that
SchemaDumper might not be able to handle, like enums or a primary key
not named “id” ?

And yes I the dev database is myisam. I’m not aware of the
limitations of SchemaDumper, but the datatypes include: int,
datetime, tinyint, varchar & text.

The only thing that comes to mind, is the table breaks innodb’s
maximum row length of 8000 bytes. (which is why I’m using myisam)

Plus, I’m using rails 1.1.2. Would prefer not to migrate at this time.

Thanx for your help Rob.

J

4

[followup.]

It looks as if as of 1.1, rails is defaulting mysql table creates to
innodb.

This assumption is, well, presumptuous.

Might it not be better to either use the default database setting
(default table type) ? Or permit the developer to define (may be a
lowest common denominator problem?)

Does rails somehow ‘think’ it knows better than the developer which
table type to choose?

Rails is choosing to clone the test database, enforcing certain table
types for certain tables. The developers tables are being forced to
innodb, and 2 system tables (schema_info and engine_schema_info) to
myisam.

The second is interesting. engine_schema_info (most likely not
favored by rails, grin) is being replicated as myisam. This symptom
suggests that it is possible to to force a particular table type -
but for the life of me, I can’t discover how.

Anybody?

Thanx for reading my outburst. I appreciate any help you may provide!

Jodi

Thanx Rob, setting

config.active_record.schema_format = :sql

corrected the problem.

Rob (anyone?) - I’d like to know the implications of the above? I’ve
looked for docs on Config - nothing on api.rubonrails.com.
* schema.db lokss the same, but now have development_structure.sql
* does this impact migrations? Or just cloning of db structure to test?

Are you absolutely positive the table in question in the dev db is
myisam? Are there any non-native things in the table that
SchemaDumper might not be able to handle, like enums or a primary key
not named “id” ?

And yes I the dev database is myisam. I’m not aware of the
limitations of SchemaDumper, but the datatypes include: int,
datetime, tinyint, varchar & text.

The only thing that comes to mind, is the table breaks innodb’s
maximum row length of 8000 bytes. (which is why I’m using myisam)

Plus, I’m using rails 1.1.2. Would prefer not to migrate at this time.

Thanx for your help Rob.

J

5

On Nov 1, 2006, at 8:12 AM, Jodi S. wrote:

(default table type) ? Or permit the developer to define (may be a

The second is interesting. engine_schema_info (most likely not
favored by rails, grin) is being replicated as myisam. This symptom
suggests that it is possible to to force a particular table type -
but for the life of me, I can’t discover how.

Anybody?

Thanx for reading my outburst. I appreciate any help you may provide!

The reason rails defaults to innodb is that you have to use innodb

to get transactions. Since the test db is run in one big transaction
and then rolled back for each test it requires innodb if you want
transactional test which are way faster. I use only innodb tables for
all of my rails apps that are on mysql.

Cheers-
– Ezra Z.
– Lead Rails Evangelist
– [email protected]
– Engine Y., Serious Rails Hosting
– (866) 518-YARD (9273)

6

On 1-Nov-06, at 5:27 PM, Ezra Z. wrote:

Rails is choosing to clone the test database, enforcing certain table

Thanx for reading my outburst. I appreciate any help you may provide!

The reason rails defaults to innodb is that you have to use innodb
to get transactions. Since the test db is run in one big transaction
and then rolled back for each test it requires innodb if you want
transactional test which are way faster. I use only innodb tables for
all of my rails apps that are on mysql.

Thanx Ezra.

I understand. I would prefer innodb, but I have run into a limit on
that engine (a limit not imposed by myisam)

Give your explanation, I would expect that :
self.use_transactional_fixtures = false

would then result in the database clone to not change myisams to
innodb. Although this is not the case.

Is there a workaround you’re aware of?

cheers,
J

7

Begin forwarded message:

On Nov 1, 2006, at 8:12 AM, Jodi S. wrote:

Might it not be better to either use the default database setting
innodb, and 2 system tables (schema_info and engine_schema_info) to
provide!
Thanx Ezra.
Is there a workaround you’re aware of?

cheers,
J

Ezra, a final thought I wanted make before hastily running downstairs
for dinner! (low blood sugar and all…)

I have worked around the ‘problem’ by:
config.active_record.schema_format = :sql

This solution seems to be working at this time, but I’m not aware of
the consequences - other than cloning to test.

Will this setting impact using ruby migrations?

Are there others?

thanx again for your help.
Jodi

8

Hi~

On Nov 1, 2006, at 3:35 PM, Jodi S. wrote:

creates to
table type to choose?
suggests that it is possible to to force a particular table type -
to get transactions. Since the test db is run in one big transaction

Are there others?

thanx again for your help.
Jodi

Hmm I don't really ever use myisam tables so I can't say that I have

run into this issue personally. I think you should still be ok as far
as ruby migrations go though. I think you are fine with the :sql
setting.

– Ezra Z.
– Lead Rails Evangelist
– [email protected]
– Engine Y., Serious Rails Hosting
– (866) 518-YARD (9273)

9

On 11/1/06, Jodi S. [email protected] wrote:

Will this setting impact using ruby migrations?

Are there others?

thanx again for your help.
Jodi

I’m not Ezra, but I’ve had experience with the differences between
setting that to sql and setting it to ruby. The main difference is
when its ruby, rails uses the rake task db:test:clone, which uses
schema.rb, and when its sql rails uses db:test:clone_structure, which
uses development_structure.sql. This comes out of looking through
databases.rake w/i rails, btw, its pretty easy to follow.

Now, what that actually means is that when you are using ruby, you are
constrained to whatever Rails’ SchemaDumper supports - meaning
whatever DHH and core believe is the “rails way”. So things like
weird primary keys, mysql enums or other db specific types, and stored
procs are out - schema dumper will either ignore them or may error if
it sees them.

When you are using sql, you are basically doing a straight sql load
from your dev db to test - meaning you can use a lot more crazy
database stuff. For example, in one of my projects we have a legacy
db with auto-increment primary keys named things like “foo_table_id” -
when schema dumper tried to convert these, it wouldn’t recognize them
because it expects primary keys to be “id” and only “id”. So the
fields would be created, but wouldn’t be set to be auto increment
primary key - and we got no warning of this. Luckily, our test suite
turned this up before we had any real damage, and switching schema
format to sql fixed things.

As far as how this effects production - regardless of what format you
use, rails uses ActiveRecord::Migrator to do the migrations - but
then after a migration completes rails will dump the schema if ruby
is used, but do nothing if sql is used. I’m guessing thats because
the sql structure will be updated regardless if you are using sql and
run a test, but I’m not sure.

Hope that helps - moral of the story is you probably want to use sql
if you have a legacy db or db specific things, and if you have a
greenfield app that follows the rails way stick with ruby schema.

  • Rob

http://www.ajaxian.com

10

On 1-Nov-06, at 11:30 PM, Rob S. wrote:

Hope that helps - moral of the story is you probably want to use sql
if you have a legacy db or db specific things, and if you have a
greenfield app that follows the rails way stick with ruby schema.

  • Rob

Great info Rob.

If I learn anymore on this topic I’ll post for posterity.

cheers, and have a good week.

Jodi