MySQL support

[rstacruz]

I'm getting this error using MySQL 14.14, ruby mysql gem 2.8.1, sinatra-sequel 0.9.0:

/gems/sequel-3.23.0/lib/sequel/adapters/mysql.rb:200:in `query': Mysql::Error: BLOB/TEXT column 'name' used in key specification without a key length (Sequel::DatabaseError)
    from /gems/sequel-3.23.0/lib/sequel/adapters/mysql.rb:200:in `block in _execute'
    from /gems/sequel-3.23.0/lib/sequel/database/logging.rb:28:in `log_yield'
    from /gems/sequel-3.23.0/lib/sequel/adapters/mysql.rb:200:in `_execute'
    from /gems/sequel-3.23.0/lib/sequel/adapters/mysql.rb:184:in `block in execute'
    from /gems/sequel-3.23.0/lib/sequel/connection_pool/threaded.rb:84:in `hold'
    from /gems/sequel-3.23.0/lib/sequel/database/connecting.rb:226:in `synchronize'
    from /gems/sequel-3.23.0/lib/sequel/adapters/mysql.rb:184:in `execute'
    from /gems/sequel-3.23.0/lib/sequel/database/query.rb:71:in `execute_dui'
    from /gems/sequel-3.23.0/lib/sequel/database/query.rb:64:in `execute_ddl'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:365:in `block (2 levels) in create_table_indexes_from_generator'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:365:in `each'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:365:in `block in create_table_indexes_from_generator'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:363:in `each'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:363:in `create_table_indexes_from_generator'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:98:in `create_table'
    from /gems/sequel-3.23.0/lib/sequel/database/schema_methods.rb:114:in `create_table?'
    from /gems/sinatra-sequel-0.9.0/lib/sinatra/sequel.rb:46:in `create_migrations_table'
    from /gems/sinatra-sequel-0.9.0/lib/sinatra/sequel.rb:34:in `migration'
    ...

The culprit seems to be:

String :name, :null => false, :index => true

Which should instead be:

String :name, :null => false

...I tried adding index :name but I can't figure out the correct switches to give it a size. (index "name(512)".lit still doesn't work.)

Furthermore, :encoding => 'utf-8' doesn't work in MySQL, it needs :encoding => 'utf8' instead.

[w-A-L-L-e]

I wrote a small app to benchmark sequel gem versus the sinatra/activerecord gem. Here is my testing app.rb:

require 'sinatra' require 'mysql2' require 'sequel' require 'yaml' require 'json'

#these also work but all have the same problem of the sinatra app locking up when running benchmark with concurrency : #set :database, Sequel.connect( "mysql2://root:aaaa5M@localhost/ScaleChampion_development", :max_connections=>5, :socket=>'/var/run/mysqld/mysqld.sock', :encoding=>'utf8' ) #set :database, Sequel.connect( "mysql2://root:aa5M@localhost/ScaleChampion_development", :max_connections=>5, :encoding=>'utf8' )

#as recommended by the sequel wiki use a constant: DB = Sequel.connect( "mysql2://root:thepassword@localhost/ScaleChampion_development", :max_connections=>5, :encoding=>'utf8' )

class User < Sequel::Model end

get '/' do erb :getAuthToken end

get '/getAuthToken' do erb :getAuthToken end

post '/getAuthToken' do erb :getAuthToken end

get '/users' do content_type :json User.all.map{|u| { :id=>u.id, :name => u.name, :email => u[:email] }.to_json } end

#json request sequel get '/user/:id' do u = User.find( params[:id] ) content_type :json { :name => u.name, :email => u.email }.to_json end

Here is my config.ru: require './app.rb'

root_dir = File.dirname(FILE) set :environment, ENV['RACK_ENV'].to_sym set :root, root_dir set :app_file, File.join(root_dir, 'app.rb')

disable :run

run Sinatra::Application

When I run this with Passenger/apache2. All works Except when I try to benchmark it with ab I get following: root@Debian-60-squeeze-64-LAMP:/var/www/viu2_json# ab -n 1000 -c 10 'http://viu.sitweb.eu/user/100' This is ApacheBench, Version 2.3 <$Revision: 655654 $> Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking viu.sitweb.eu (be patient) Completed 100 requests Completed 200 requests Completed 300 requests Completed 400 requests Completed 500 requests Completed 600 requests Completed 700 requests Completed 800 requests Completed 900 requests apr_poll: The timeout specified has expired (70007) Total of 991 requests completed

So it just hangs after 991 requests.

Now when I changed the app to use sinatra/activerecord I get following great results:

Benchmarking viu.sitweb.eu (be patient)

Server Software: Apache/2.2.16 Server Hostname: viu.sitweb.eu Server Port: 80

Document Path: /user/100 Document Length: 44 bytes

Concurrency Level: 10 Time taken for tests: 0.234 seconds Complete requests: 1000 Failed requests: 0 Write errors: 0 Total transferred: 276000 bytes HTML transferred: 44000 bytes Requests per second: 4268.69 #/sec Time per request: 2.343 ms Time per request: 0.234 [ms](mean, across all concurrent requests) Transfer rate: 1150.54 [Kbytes/sec] received

So anyone can solve my issue. Basically if I change in above source code to this: set :database_extras, { :password=>'mysqlpass', :encoding=>'utf8', :max_connections=>5 } set :database, URI.encode( "mysql2://root:mysqlpass@localhost/ScaleChampion_development" )

class User < ActiveRecord::Base #set_table_name :users end

Everything is working great. And what boggles me is that the sinatra/activerecord is supposed to be based on the sequel gem so why is it performing so much better??? https://github.com/bmizerany/sinatra-activerecord

[rstacruz]

sinatra-activerecord is not based on sinatra-sequel. the former uses activerecord, and the latter uses sequel.

If you need help with sinatra-activerecord, you're better off asking in that project than here.

[w-A-L-L-e]

I need help with Sinatra sequel since it hangs with all apache benchmarks on a mysql db. Gave the activerecord as extra Example to show it's not my setup db or apache that is broken. It works fast with sequel for a few hundred requests and then locks for no reason. Using mysql2 adaptor and sequel version I did with gem install yesterday. Maybe this git repo has fix for that?

[w-A-L-L-e]

Gave the complete test Sinatra app that uses sequel above. Maybe someone can test to see if it works on their setup. When I run ruby app.rb it runs fine but with passenger it freezes when testing it with ab -n 1000 -c 10 'http://localhost....'

[w-A-L-L-e]

I love the much quicker startup times of sequel vs activerecord in a new project I would go for postgress but I'm fixed on a legacy mysql db at work that is used by many other daemons already...

[rstacruz]

Sorry mister Walle, but I believe you're asking in the wrong place.

First of all, the slowdown would probably be a Sequel issue, you may want to open an issue there (https://github.com/jeremyevans/sequel) about your lockup issues.

If you're having trouble with Passenger, look at their site's support area and see if you can get help there.

Also, both Sequel and ActiveRecord support both MySQL and Postgres.

[w-A-L-L-e]

Thanks for the quick reply. Indeed it's probably sequel gem issue with mysql2. I've ruled out the passenger and Sinatra above. So my best bet is to ask Jeremy about it. Keep up the great work. Kind regards,w.