Error when creating references

[randallmeeker]

MySQL 5.5

<cfcomponent extends="plugins.dbmigrate.Migration" hint="creates groups table">
  <cffunction name="up">
    <cfscript>
      t = createTable(name='groups');
      t.string(columnNames='displayName', null=false, limit='100');
      t.string(columnNames='nameSpace', null=false, limit='100');
      t.string(columnNames='description', limit='255');
      t.references('user');
      t.timestamps();
      t.create();
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
      dropTable('groups');
    </cfscript>
  </cffunction>
</cfcomponent>

results in the following sql CREATE TABLE groups ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, displayName VARCHAR(100) NOT NULL, nameSpace VARCHAR(100) NOT NULL, description VARCHAR(255), userid INT NOT NULL, createdat DATETIME, updatedat DATETIME, deletedat DATETIME,

);

[tdm00]

Thanks George, I'll see what I can do

On Fri, Mar 29, 2013 at 1:56 AM, George [email protected] wrote:

MySQL 5.5

t = createTable(name='groups'); t.string(columnNames='displayName', null=false, limit='100'); t.string(columnNames='nameSpace', null=false, limit='100'); t.string(columnNames='description', limit='255'); t.references('user'); t.timestamps(); t.create();

dropTable('groups');

results in the following sql CREATE TABLE groups ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, displayName VARCHAR(100) NOT NULL, nameSpace VARCHAR(100) NOT NULL, description VARCHAR(255), userid INT NOT NULL, createdat DATETIME, updatedat DATETIME, deletedat DATETIME,

);

— Reply to this email directly or view it on GitHubhttps://github.com/tdm00/cfwheels-dbmigrate-plugin/issues/76 .

Troy Murray

[randallmeeker]

FYI, when I pull the t.references statement/function it works.

[tdm00]

George,

The references function is something that was added by the previous maintainer and I don't really understand how it's code works within the plug-in yet. I know there are some other issues pending with that feature as well and I just haven't learned how it works yet. Your best short term solution is to just not use the references for now.

On Apr 1, 2013, at 12:48 PM, George [email protected] wrote:

FYI, when I pull the t.references statement/function it works.

— Reply to this email directly or view it on GitHub.

[randallmeeker]

Ya, it seems to work fine in MSSQL, but in MySQL it is not dumping the sql "FOREIGN KEY (userId) REFERENCES user(Id)", there is a just a blank.

I'm rooting through the plugin to see if I can help. This is my first time using with MySQL, i was using it with MSSQL and it worked great. I need my references, so I'll keep hunting and pecking.

[tdm00]

Any help is appreciated.

On Monday, April 1, 2013, George wrote:

Ya, it seems to work fine in MSSQL, but in MySQL it is not dumping the sql "FOREIGN KEY (userId) REFERENCES user(Id)", there is a just a blank.

I'm rooting through the plugin to see if I can help. This is my first time using with MySQL, i was using it with MSSQL and it worked great. I need my references, so I'll keep hunting and pecking.

— Reply to this email directly or view it on GitHubhttps://github.com/tdm00/cfwheels-dbmigrate-plugin/issues/76#issuecomment-15726321 .

Troy Murray

[randallmeeker]

I updated this with the correct subject. I lied as the current version does not create references on any DB type. I have rolled back to version 0.9 and it creates the references just fine.

[chrislemje]

I found that the references issue (plugin v1.0.1 on MSSQL for me) was caused by line 25 removed from ForeignKeyDefinition.cfc. The change can be seen here No sql is returned from the toSQL call when creating foreign keys. Reinstating this line made my references work.

[tdm00]

Thanks for this

On May 1, 2013, at 5:24 AM, chrislemje [email protected] wrote:

I found that the references issue (plugin v1.0.1 on MSSQL for me) was caused by line 25 removed from ForeignKeyDefinition.cfc. The change can be seen here No sql is returned from the toSQL call when creating foreign keys. Reinstating this line made my references work.

— Reply to this email directly or view it on GitHub.

[tdm00]

I'm at Railsconf this week, when I return next week I'll get this fixed.

On Wed, May 1, 2013 at 5:24 AM, chrislemje [email protected] wrote:

I found that the references issue (plugin v1.0.1 on MSSQL for me) was caused by line 25 removed from ForeignKeyDefinition.cfc. The change can be seen herehttps://github.com/tdm00/cfwheels-dbmigrate-plugin/commit/e5d6dd063935dc83fa2f35c83584fd1882f44f25 No sql is returned from the toSQL call when creating foreign keys. Reinstating this line made my references work.

— Reply to this email directly or view it on GitHubhttps://github.com/tdm00/cfwheels-dbmigrate-plugin/issues/76#issuecomment-17279012 .

Troy Murray

[geirman]

I'm seeing an issue with dbmigrate 1.0.1 and rc1. Not sure if it's a plugin or core issue. With RC1 installed, I wiped out my database and tried to setup everything from scratch. I have 16 migrations in dbmigrate.

Firstly, using dbmigrate v0.9 with 1.1.8, everything configures properly.

With 1.1.8 installed, I attempted to upgrade dbmigrate to 1.0.1 but for some reason it wouldn't unpack. Each time I reloaded the framework, v0.9 would persist, which seems strange. As soon as I load RC1 and reload, it unpacks and upgrades though.

With my database empty, RC1 installed, dbmigrate v1.0.1, when I attempt to run the first migration I get the following error...

  ×Migrating from 0 up to 001.

  -------- 001_prod_schema_initialization --------------------
  Error migrating to 001.
  Error Executing Database Query.
  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 '
   ,
   ,

  )' at line 12

I've made the following observations:

• Line 12 of the migration file is a comment, so not sure what the "line 12" is referring to.
• When I compare the db to the migration file, I've noticed that 5 tables were created successfully before the failure occurred.
• The first table not created is also the first table to use the t.references(), so I'm assuming there's a problem setting up the relationships

Any idea why am I unable to upgrade to dbmigrate v1.0.1 from wheels 1.1.8?

[tdm00]

Chris,

This is the first I've heard of it. I haven't tested dbmigrate with CFWheels 1.2 RC1 yet. My roles here at work have shifted and I'm not presently doing any CF work so I haven't been able to do anything with the plugin for a bit.

I've also seen the problem with the plugin not unpacking and I assumed it was a framework issue as I saw it with another plugin, but haven't been able to go past that assumption yet.

On Thursday, September 26, 2013, Chris Geirman wrote:

I'm seeing an issue with dbmigrate 1.0.1 and rc1. Not sure if it's a plugin or core issue. With RC1 installed, I wiped out my database and tried to setup everything from scratch. I have 16 migrations in dbmigrate.

Firstly, using dbmigrate v0.9 with 1.1.8, everything configures properly.

With 1.1.8 installed, I attempted to upgrade dbmigrate to 1.0.1 but for some reason it wouldn't unpack. Each time I reloaded the framework, v0.9 would persist, which seems strange. As soon as I load RC1 and reload, it unpacks and upgrades though.

With my database empty, RC1 installed, dbmigrate v1.0.1, when I attempt to run the first migration I get the following error...

×Migrating from 0 up to 001.

-------- 001_prod_schema_initialization -------------------- Error migrating to 001. Error Executing Database Query. 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 ' , ,

)' at line 12

I've made the following observations:

• Line 12 of the migration file is a comment, so not sure what the "line 12" is referring to.
• When I compare the db to the migration file, I've noticed that 5 tables were created successfully before the failure occurred.
• The first table not created is also the first table to use the t.references(), so I'm assuming there's a problem setting up the relationships

Any idea why am I unable to upgrade to dbmigrate v1.0.1 from wheels 1.1.8?

— Reply to this email directly or view it on GitHubhttps://github.com/tdm00/cfwheels-dbmigrate-plugin/issues/76#issuecomment-25147074 .

Troy Murray

[geirman]

Troy,

I certainly understand how life and work can get in the way of these open source projects. Thank you for your response and whatever you find time to do. Adam Chapman suggests using the execute() function for references, reporting success with that. I'll give that a try as a work around. And, I'll report a core issue on the plugin upgrade issue. There's definitely something unexpected going on because I tried the following steps...

1. With 1.1.8, add the dbmigrate 1.0.1 plugin to the plugins directory then reload framework RESULT: dbmigrate v0.9
2. Upgrade wheels to 1.2 RC1, reload framework RESULT: dbmigrate v1.0.1
3. Without doing anything to the plugin directory, roll back wheels to 1.1.8 and reload framework RESULT: dbmigrate v0.9

NOTE: In all of the above steps, both dbmigrate-0.9.zip and dbmigrate-1.0.1.zip existed in the plugins directory.

1. With wheels 1.1.8 installed, delete dbmigrate-0.9zip from plugins directory and reload the framework RESULT: dbmigrate 1.0.1, but upgrade fails at the first call to reference()

Chris

[tdm00]

I personally never used the reference() and prefer to do references using the table change, but that's just me. I didn't add the reference() code, it was created by the creator of the plugin so I really don't have any idea how it works. Note that there are one or two pull requests with a number of fixes for issues, I haven't integrated them into the master yet as I was having a problem but wasn't sure if it was my DB or the changes in the plugin. You might check those commit notes.

On Thu, Sep 26, 2013 at 5:00 PM, Chris Geirman [email protected]:

Troy,

I certainly understand how life and work can get in the way of these open source projects. Thank you for your response and whatever you find time to do. Adam Chapman suggests using the execute() function for references, reporting success with that. I'll give that a try as a work around. And, I'll report a core issue on the plugin upgrade issue. There's definitely something unexpected going on because I tried the following steps...

1. With 1.1.8, add the dbmigrate 1.0.1 plugin to the plugins directory then reload framework RESULT: dbmigrate v0.9
2. Upgrade wheels to 1.2 RC1, reload framework RESULT: dbmigrate v1.0.1
3. Without doing anything to the plugin directory, roll back wheels to 1.1.8 and reload framework RESULT: dbmigrate v0.9

NOTE: In all of the above steps, both dbmigrate-0.9.zip and dbmigrate-1.0.1.zip existed in the plugins directory.

1. With wheels 1.1.8 installed, delete dbmigrate-0.9zip from plugins directory and reload the framework RESULT: dbmigrate 1.0.1, but upgrade fails at the first call to reference()

Chris

— Reply to this email directly or view it on GitHubhttps://github.com/tdm00/cfwheels-dbmigrate-plugin/issues/76#issuecomment-25203430 .

Troy Murray