# Problem with schema\_db\_import on Site5?

**URL:** <https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151>\
**Category:** Rails\
**Created:** [January 26, 2006, 6:46pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151 "2006-01-26T18:46:09Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 26, 2006, 6:46pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/1 "2006-01-26T18:46:09Z")

</div>

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:in`execute’  
/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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 27, 2006, 12:35am UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/2 "2006-01-27T00:35:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 27, 2006, 3:20pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/3 "2006-01-27T15:20:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 27, 2006, 8:36pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/4 "2006-01-27T20:36:50Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 30, 2006, 9:04am UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/5 "2006-01-30T09:04:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 30, 2006, 8:37am UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/6 "2006-01-30T08:37:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [February 23, 2006, 3:52pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/7 "2006-02-23T15:52:54Z")

</div>

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](http://thread.gmane.org/gmane.comp.lang.ruby.rails/49950)

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

Brgds.

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [February 24, 2006, 3:09pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/8 "2006-02-24T15:09:39Z")

</div>

On 2/23/06, MiKael N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![vivek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/77aa72/32.png) [@vivek](https://www.ruby-forum.com/u/vivek)\
**Post date:** [January 30, 2006, 6:47pm UTC](https://www.ruby-forum.com/t/problem-with-schema-db-import-on-site5/53151/9 "2006-01-30T18:47:14Z")

</div>

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