#798 ✓resolved
Angel Pizarro

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 TABLEuser_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

    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

    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)

    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

    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)

    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.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Tags

Pages