test_recreate_plus_card_name_variant errors out with MySQL 5.1.51

Support Ticket

+status
answered
 

Running this test generates SQL that MySQL rejects with a syntax error.

 

This could be deeper than Wagn itself, like somewhere in ActiveRecord or even in the mysql gem. I am still looking into it. I'm just reporting it early so it doesn't get lost.

Here is output from script/about:

About your application's environment
Ruby version              1.8.7 (i686-darwin10)
RubyGems version          1.3.7
Rack version              1.0
Rails version             2.3.5
Active Record version     2.3.5
Active Resource version   2.3.5
Action Mailer version     2.3.5
Active Support version    2.3.5
Application root          /opt/wagn
Environment               development
Database adapter          mysql
Database schema version   20101029053352

And output from rake test:units (I could also run this with --trace, but the extra output didn't seem to be relevant):

(in /opt/wagn)
skipping loading test data.  to force, run  env RELOAD_TEST_DATA=true rake db:test:prepare
/opt/local/bin/ruby -I"lib:test" "/opt/local/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake/rake_test_loader.rb" "test/unit/card/base_test.rb" "test/unit/card/create_test.rb" "test/unit/card/remove_test.rb" "test/unit/card/rename_test.rb" "test/unit/card/search_test.rb" "test/unit/cardtype/image_test.rb" "test/unit/cardtype/invitation_request_test.rb" "test/unit/cardtype/role_test.rb" "test/unit/chunks/link_test.rb" "test/unit/chunks/literal_test.rb" "test/unit/chunks/transclusion_test.rb" "test/unit/chunks/uri_test.rb" "test/unit/diff_test.rb" "test/unit/revision_test.rb" "test/unit/role_test.rb" "test/unit/user_test.rb" "test/unit/wagn_ruby_test.rb" "test/unit/wiki_content_test.rb" 
/opt/local/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/rails/gem_dependency.rb:119:Warning: Gem::Dependency#version_requirements is deprecated and will be removed on or after August 2010.  Use #requirement
Loaded suite /opt/local/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake/rake_test_loader
Started
.........................E..................................................................The 

Any name (or chunk of text being treated as a name) can be translated into a standardized "key."  Keys are comprised solely of lowercase alphanumeric characters, underscores, and asterisks.

 

The key is used primarily in detecting name variants.

 

Some examples:

  • Apples — apple
  • TedErnst — ted_ernst
  • Joe's Blog Entries — joe_s_blog_entry

The default Wagn algorithm for key generation is as follows.

  1. decode any HTML
  2. add a space before any upper case letter followed by a lower case letter (this splits CamelCase into words)
  3. make all upper case into lower case
  4. replace any non-alphanumeric character (except *) with a space
  5. break the name into words, and make each word singular if it's plural
  6. rejoin those words with underscores (instead of spaces)

 

NOTE: At present the above algorithm has an English bias, because the singularization algorithm is based on English patterns.  In coming releases, this will be addressed in attempts to make Wagn more completely international.

 

tickets relevant to keys

interpolation syntax in I18n messages is deprecated. Please use %{key} instead. en - is too short (minimum is
characters) ............. Finished in 18.727965 seconds. 1) Error: test_recreate_plus_card_name_variant(Card::RemoveTest): ActiveRecord::StatementInvalid: Mysql::Error: 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 ''rtb*trash') LIMIT 1' at line 1: SELECT * FROM `cards` WHERE (name=E'rtb*trash') LIMIT 1 app/models/card.rb:25:in `send' app/models/card.rb:25:in `method_missing' /test/unit/card/remove_test.rb:26:in `test_recreate_plus_card_name_variant' 105 tests, 1267 assertions, 0 failures, 1 errors rake aborted! Command failed with status (1): [/opt/local/bin/ruby -I"lib:test" "/opt/loc...] (See full trace by running task with --trace)

 

ooh, thanks for pointing this out.  I want to set up continuous integration testing with mysql, because we haven't been testing it adequately lately.  will look into this soon, but let us know if you learn any more!  

 

- ethan


I think we should change this assertion:

 

assert !Card.find(:first, :conditions=>"name=E'rtb*trash'")

 

I don't know that mysql supports that kind of escaping, and in any event, it would be more railsy to do something like:

 

assert Card.find_by_key('rtb*trash').nil?

  --Ethan McCutchen.....Tue Nov 09 21:47:30 -0800 2010


Ohh, the problem with this was staring me right in the face the whole time. :-) And I kept passing it by.

 

This line in the test:

 

assert !Card.find(:first, :conditions=>"name=E'rtb*trash'")

 

is not safe because it assumes PostgreSQL is in use: E'...' is a Postgres-ism. That shouldn't be hard-coded in the condition.

  --Larry Gilbert.....Tue Nov 09 21:49:55 -0800 2010


Ah, great minds think alike! I was writing a comment while you were posting yours. :-)

 

You are correct; E'...' is unique to PostgreSQL as far as I can tell. I think find_by_key is indeed the way to go.

  --Larry Gilbert.....Tue Nov 09 21:51:24 -0800 2010


cool, that fix is in the master branch now, as are some other mysql issues that Gerry Gleason found while running the spec and cucumber tests. (I'm pretty sure all the issues were with the tests, not with the codebase)

  --Ethan McCutchen.....Thu Nov 18 16:29:55 -0800 2010