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.