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 -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060126/26a48935/attachment.html
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-- Posted via http://www.ruby-forum.com/.
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 On 1/27/06, scott <scottnj@gmail.com> wrote:> > 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060127/d7f3f4a3/attachment.html
From the manual: http://dev.mysql.com/doc/refman/4.1/en/legal-names.html
The identifier quote character is the backtick (''`''):
You probably want (need?) the `quotes` around the identifiers. All
quotes are not created (or defined) equal.
-Rob
At 1/27/2006 09:18 AM, you 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
>
>
>On 1/27/06, scott <<mailto:scottnj@gmail.com>scottnj@gmail.com>
wrote:
>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
>
>--
>Posted via <http://www.ruby-forum.com/>http://www.ruby-forum.com/.
>_______________________________________________
>Rails mailing list
><mailto:Rails@lists.rubyonrails.org>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060127/9104b739/attachment.html
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 Krishna 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
--
Posted via http://www.ruby-forum.com/.
Vivek Krishna
2006-Jan-30 07:35 UTC
[Rails] Re: Re: Problem with schema_db_import on Site5?
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 On 1/28/06, scott <scottnj@gmail.com> wrote:> > 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 Krishna 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 > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060130/363b29a4/attachment.html
Vivek Krishna 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. -- Alex
Vivek Krishna
2006-Jan-30 17:45 UTC
[Rails] Re: Re: Problem with schema_db_import on Site5?
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 On 1/30/06, Alex Young <alex@blackkettle.org> wrote:> > Vivek Krishna 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. > > -- > Alex > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060130/339ccef0/attachment-0001.html
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.
On 2/23/06, MiKael Navarro <klnavarro@gmail.com> wrote:> > 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. >Thats good, Lets hope it gets fixed fast. vivek -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060224/22a5384e/attachment-0001.html