MiKael Navarro
2006-Feb-23 14:31 UTC
[Rails] Problem with db_schema_import and MySQL timestamp!
Hi,
This post concerns a SQL error while I try to import a schema into MySQL
with timestamp dates.
To reproduce try these commands:
mickey@bunny:~/dev/rails/timeonrails$ rake db_schema_dump
(in /home/mickey/dev/rails/timeonrails)
mickey@bunny:~/dev/rails/timeonrails$ cat db/schema.rb
# This file is autogenerated. Instead of editing this file, please use the
# migrations feature of ActiveRecord to incrementally modify your database, and
# then regenerate this schema definition.
ActiveRecord::Schema.define() do
create_table "categories", :force => true do |t|
t.column "name", :string, :limit => 20
t.column "description", :text
t.column "created_on", :timestamp, :limit => 14
t.column "updated_on", :timestamp, :limit => 14
end
end
Then, when I try to re-import the same schema, I get a SQL error:
mickey@bunny:~/dev/rails/timeonrails$ rake db_schema_import
(in /home/mickey/dev/rails/timeonrails)
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),
`updated_on` datetime(14)) ENGINE=InnoDB'' at line 1: CREATE TABLE
categories
(`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `name` varchar(20),
`description` text, `created_on` datetime(14), `updated_on` datetime(14))
ENGINE=InnoDB
After executing the import with --trace option it looks like the problem is
coming from mysql_adapter.rb file, where any reference to :timestamp is
replaced by :datetime, but the ''datetime'' SQL command does not
accept an
argument (14)!
PS: Another consequence of this error is that the table is dropped at the same
time, that is not a good news!
I propose the following patch:
mickey@bunny:~/.../vendor/.../active_record/connection_adapters$ diff -bru
./mysql_adapter.rb ./mysql_adapter-patched.rb
---
./mysql_adapter.rb
2006-02-23 12:30:19.000000000 +0100
+++
./mysql_adapter-patched.rb
2006-02-23 12:29:50.000000000 +0100
@@ -103,7 +103,7 @@
:integer => { :name => "int", :limit => 11 },
:float => { :name => "float" },
:datetime => { :name => "datetime" },
- :timestamp => { :name => "datetime" },
+ :timestamp => { :name => "timestamp" },
:time => { :name => "time" },
:date => { :name => "date" },
:binary => { :name => "blob" },
I don''t know if it''s a good idea, I am not an expert on MySQL.
So, I''m waiting for yours advises...
Bgrds.
