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.