Problem with schema_db_import on Site5?

Rails
1

Hello,
I am trying to put my rails app on a host .So I presume the method to
do
this is to first do
rake db_schema_dump and then copy it to the host and do
rake db_schema_import.

I use mySQL Ver 14.7 Distrib 4.1.14, for pc-linux-gnu (i686) using
readline
4.3

** Invoke db_schema_import (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db_schema_import
rake aborted!
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
‘(14)) ENGINE=InnoDB’ at line 1: CREATE TABLE channelposts (id int(11)
DEFAULT NULL auto_increment PRIMARY KEY, channel_id int(11) DEFAULT 0
NOT
NULL, post varchar(255) DEFAULT ‘’ NOT NULL, created_on
datetime(14))
ENGINE=InnoDB
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract_adapter.rb:88:in
log' /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:180:inexecute’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:94:in
`create_table’
…

Has anyone faced such an error?
Vivek

2

I think I had a similar problem once. Try replacing all the ` (above
your tab key) with ’ (next to the enter key) or vice versa. The single
quotes look the same, but are not.

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
‘(14)) ENGINE=InnoDB’ at line 1: CREATE TABLE channelposts (id int(11)
DEFAULT NULL auto_increment PRIMARY KEY, channel_id int(11) DEFAULT 0
NOT
NULL, post varchar(255) DEFAULT ‘’ NOT NULL, created_on
datetime(14))
ENGINE=InnoDB

3

Hi Scott,
I am using rake db_schema_import…There are no in that file. Rails seems to add a ??
One of the tables gets created .The problem is with the other one.

Vivek

4

My mistake, I was thinking of a mysql dump, not a rake db_schema dump.

  • Did you create the database on site5
  • Did you create the database user on site5
  • Did you create give the database user permission to access the db on
    site5
  • Check your settings in database.yml

If one table gets created, the above is probably ok.

Look in your schems.rb, do all of the tables have “:force => true”? This
will drop the table (if it exists) then create a new one.

create_table “users”, :force => true do |t|
t.column “login”, :string, :limit => 40
#…
t.column “created_at”, :datetime
t.column “updated_at”, :datetime
end

If that does not work, try posting the question on the site5 ruby forum
http://forums.site5.com/forumdisplay.php?f=44

Good Luck,
Scott

Vivek K. wrote:

Hi Scott,
I am using rake db_schema_import…There are no in that file. Rails seems to add a ??
One of the tables gets created .The problem is with the other one.

Vivek

5

Vivek K. wrote:

Well, I did raise a ticket and it seems the problem according to them is
that
the ‘engine=InnoDB’ should actually be ‘type=InnoDB’

You can fix this one yourself… It’s a problem with using Rails with
earlier versions of MySQL. If you put this in lib/mysql_fix.rb:

module ActiveRecord
module ConnectionAdapters
class MysqlAdapter
def create_table(name, options = {}) #:nodoc:
super(name, {:options => “TYPE=InnoDB”}.merge(options))
end
end
end
end

And this in config/environment.rb:

require ‘mysql_fix.rb’

then it should go away.

6

Well, I did raise a ticket and it seems the problem according to them is
that
the ‘engine=InnoDB’ should actually be ‘type=InnoDB’ Any Idea why rails
generates the SQL like this? And yes,I did create the database,the
database
user and and the permissions.It actually creates on of the tables and
then
fails the second(which has force=true)
I think I need to dig in to find out whats going on…
Vivek

7

Hi Vivek,

Thanks for the fix but i figured out by tinkering with the SQL
query on the mysql prompt that the real problem is with the datetime(14)
by default when rails converts a field like
t.column “created_on”, :timestamp, :limit => 14
to created_on datetime(14).
This gave the above syntax error .When I removed the 14 and just did
created_on datetime there were no syntax errors.
That seems to be valid SQL but is there a way to make RoR not do the
:limit=>14?

I agree with you, the problem come from the conversion
from :timestamp, :limit => 14 to the SQL command datetime(14)"
that is invalid in MySQL!

I have reported a problem concerning this case here:
http://thread.gmane.org/gmane.comp.lang.ruby.rails/49950

Into this ticket I suggest to replace datetime(14) by timestamp(14)…

Brgds.

8

On 2/23/06, MiKael N. [email protected] wrote:

That seems to be valid SQL but is there a way to make RoR not do the

Brgds.

Thats good,
Lets hope it gets fixed fast.
vivek

9

Hi Alex,
Thanks for the fix but i figured out by tinkering with the SQL query
on
the mysql prompt that the real problem is with the datetime(14)

by default when rails converts a field like
t.column “created_on”, :timestamp, :limit => 14
to created_on datetime(14).
This gave the above syntax error .When I removed the 14 and just did
created_on datetime there were no syntax errors.
That seems to be valid SQL but is there a way to make RoR not do the
:limit=>14?
Vivek