
bad SQL for migrate + mysql + Serial col type
Reported by Angel Pizarro | February 9th, 2009 @ 10:34 AM
Using this model:
class UserGroup
include DataMapper::Resource
property :id, Serial
property :name, String, :nullable => false
property :is_user_group, Boolean, :default => false
property :summary, String, :size => 150
property :created_at, DateTime
property :updated_at, DateTime
has n, :users, :through => Resource
belongs_to :owner, :class_name => "User", :child_key => [:owner_login]
has n, :pages, :through => Resource
is :nested_set
end
Fails with this
(mysql_errno=1064, sql_state=42000) 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 'PRIMARY KEY,
name
VARCHAR(50) NOT NULL, is_user_group
TINYINT DEFAULT 0, su' at line 1 Query: CREATE
TABLE
user_groups( **serial PRIMARY
KEY**,
nameVARCHAR(50) NOT
NULL,
is_user_groupTINYINT DEFAULT
0,
summaryVARCHAR(150),
created_atDATETIME,
updated_atDATETIME,
owner_loginVARCHAR(80),
lftINT(11),
rgtINT(11),
parent_idINT(11),
PRIMARY KEY(
id)) ENGINE = InnoDB CHARACTER SET utf8
COLLATE utf8_general_ci
Offending SQL highlighted.
Using the property def :
property :id, Integer, :key => true, :serial => true
works with mysql though.
Comments and changes to this ticket
-
Angel Pizarro February 9th, 2009 @ 11:59 AM
- Tag changed from 0.9.10, mysql to 0.9.10, migrations, mysql
Please note that the rake db:automigrate task does create the proper SQL for DB creation, so the problem seems to stem from using the db-migrations plugin.
-
Greg Campbell February 9th, 2009 @ 06:05 PM
Angel,
Could you attach or paste into a comment the migration that caused this problem? (You have the model declaration, but not the migration). That might help in diagnosing the issue.
-
Dan Kubb (dkubb) February 21st, 2009 @ 02:49 AM
- State changed from unconfirmed to hold
Putting this ticket on hold until Angel supplies migration to help reproduce the issue.
-
Angel Pizarro February 23rd, 2009 @ 09:20 AM
Can't recreate the conditions for whatever reason. Both the migrate and automigrate rake commands work with the same migration files.
Chalk this up to ghost in the machine and close the ticket if you please.
BTW, migrations are below:
## create DB migration 1, :create_db do up do DataMapper.auto_migrate! end down do end end ## popluate pages model migration 2, :populate_pages do #home page p = Page.new(:label=> "home", :text => "h1. Welcome\n\nThis is the ITMAT LIMS") p.save() #help page p = Page.new(:label=> "help", :text => "h1. Coming Soon!") p.save() #about page p = Page.new(:label=> "about", :text => "This site stores file into projects, and feeds our data analysis pipelines") p.save() end down do Page.all.destroy() end end
-
Dan Kubb (dkubb) February 23rd, 2009 @ 03:28 PM
- State changed from hold to resolved
Ok, closing this for now.
Angel, if you see this happen again please feel free to reopen this ticket. It usually helps if you can package up a small script that reproduces the problem just by running it.. usually this type of problem can be fixed very easily when it's reproducible.
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »