Login_engine - rake migrate error, "ENGINE=InnoDB"

#1

Hi,

I followed all the instructions at
http://api.rails-engines.org/login_engine/ and successfully installed
and used the Login_Engine with a local MySQL database instance.

However, when I did exactly the same (slightly different app, but same
mods and all–using the same instructions), but with a remote MySQL
instance, I receive the following error when attempting to do the
migration:

Migrating engine ‘login_engine’
rake aborted!
Mysql::Error: You have an error in your SQL syntax near ‘ENGINE=InnoDB’
at line 1: CREATE TABLE users (id int(11) DEFAULT NULL auto_increment
PRIMARY KEY, login varchar(80) DEFAULT ‘’ NOT NULL, salted_password
varchar(40) DEFAULT ‘’ NOT NULL, email varchar(60) DEFAULT ‘’ NOT
NULL, firstname varchar(40), lastname varchar(40), salt
varchar(40) DEFAULT ‘’ NOT NULL, verified int(11) DEFAULT 0, role
varchar(40), security_token varchar(40), token_expiry datetime,
created_at datetime, updated_at datetime, logged_in_at datetime,
deleted int(11) DEFAULT 0, delete_after datetime) ENGINE=InnoDB

Anyone have any ideas / clues as to what is wrong?

Thank you very much!

~ Brad

#2

Is the version of your remote MySQL server different to the local one?
That chunk of SQL runs fine on my local MySQL instance too…

  • james
#3

James A. wrote:

Is the version of your remote MySQL server different to the local one?
That chunk of SQL runs fine on my local MySQL instance too…

Thanks James. I did figure out that I am using MySQL 5.0.16 (locally)
and the remote version is (was) 3.23… and that that old version must
use the ‘TYPE=InnoDB’ modifier, not ‘ENGINE=InnoDB’ modifier! So, after
much mucking about in Rake and elsewhere grepping, I couldn’t find out
how to change the engine_migrate script to use the “old” syntax (how to
do that would be an interesting ‘side question’ but anyway), so we
upgraded the (remote) MySQL instance to 4.1.19.

Now, however, we’re getting the following error:

Mysql::Error: Lost connection to MySQL server during query: SELECT
version FROM
engine_schema_info WHERE engine_name = ‘login_engine’

And looking in the mysql log file we see:

[Warning] mysql.user table is not updated to new password format;
Disabling new password usage until mysql_fix_privilege_tables is run

We seem to be able to log on the the new 4.1.19 version with our GUI
tools (or from the command line) just fine and execute commands, but no
go from login_engine. I guess I’m thinking to reinstall everything,
unless I get any other clues. Disappointing because everything went so
smoothly on my local instance of MySQL.

Any more ideas on how to get this thing to sing? Does it require MySQL
5.x to run the login_engine?

Thanks again,
Brad

#4

I dont’ think your issue has anything to do with the login engine or
plugins, but rather Rails itself connecting with your database. Wasn’t
there some kind of password scheme change between MySQL 4 and 5? The
important error message seems to be “[Warning] mysql.user table is not
updated to new password format;” - perhaps this page might help?

http://wiki.rubyonrails.com/rails/pages/MySQL+Database+access+problem

FYI, the login_engine doesn’t require any particular database, it only
requires that Rails’ own migrations operate properly.

  • james
#5

The quickest way to fix the MySQL 3.x problem is to find this file
(mysql_adapter.rb) and change the line that says:

super(name, {:options => “ENGINE=InnoDB”}.merge(options))

to this:

super(name, {:options => “TYPE=InnoDB”}.merge(options))

I found this little trick here:
http://dev.rubyonrails.org/ticket/6382

#6

Right you were:

James A. wrote:

I dont’ think your issue has anything to do with the login engine or
plugins, but rather Rails itself connecting with your database.

But it wasn’t a password problem as you were guessing, James, it seemed
to be some other problem with the build of the mysql adapter for Windows
(in Ruby or Rails) that was the problem.

Just for the record, in case others have this problem, what I did was
download a compresssed .RAR file for mysql 4.1.15 (mysql-ruby-win32.rar)
and copied it as instructed in the readme at this link:

http://jeroen.concept-q.biz/files/

This did the trick… I’ve spent more than 3 days pulling my hair out
(as well as my business partner who’s also lost some hair as well–plus
he hates it when I pull his hair ;-), but didn’t discover this link
until just now.

EVERYONE: If you are getting ANY kind of MySQL::Error, in particular
what I was getting was variations of this:

Mysql::Error: Lost connection to MySQL server during query [ . . . ]

(Buried deep in a stack trace from Rails–this wasn’t occuring for a
local instance of MySQL server, but only when it was running on a remote
server–in our case on a 1&1 dedicated host that we self-manage. Of
course the adapter worked fine from the server itself AND it worked fine
from a Mac OS X box running the identical Ruby/Rails/login_engine code
against this remote MySQL server, but always got the error above when
running from my Windows XP SP 2 box.)

Anyway, if you have this problem, try going to the above URL and
download the .RAR file there (you may need to download WinACE or
something similar to read .rar compressed files), then read the
readme.txt located within and copy the file where it directs.

Note: This is no longer an “ENGINE=InnoDB” problem, hence I’ve renamed
the Subject: line of this reply…

Good luck!

~ Brad

#7

Louis wrote:

The quickest way to fix the MySQL 3.x problem is to find this file
(mysql_adapter.rb) and change the line that says:

super(name, {:options => “ENGINE=InnoDB”}.merge(options))

to this:

super(name, {:options => “TYPE=InnoDB”}.merge(options))

I found this little trick here:
http://dev.rubyonrails.org/ticket/6382

I just typed “ENGINE=InnoDB” in Google and got there… This trick
solved my problem and thanks for help!

#8

brad wrote:

Hi,

I followed all the instructions at
http://api.rails-engines.org/login_engine/ and successfully installed
and used the Login_Engine with a local MySQL database instance.

However, when I did exactly the same (slightly different app, but same
mods and all–using the same instructions), but with a remote MySQL
instance, I receive the following error when attempting to do the
migration:

Migrating engine ‘login_engine’
rake aborted!
Mysql::Error: You have an error in your SQL syntax near ‘ENGINE=InnoDB’
at line 1: CREATE TABLE users (id int(11) DEFAULT NULL auto_increment
PRIMARY KEY, login varchar(80) DEFAULT ‘’ NOT NULL, salted_password
varchar(40) DEFAULT ‘’ NOT NULL, email varchar(60) DEFAULT ‘’ NOT
NULL, firstname varchar(40), lastname varchar(40), salt
varchar(40) DEFAULT ‘’ NOT NULL, verified int(11) DEFAULT 0, role
varchar(40), security_token varchar(40), token_expiry datetime,
created_at datetime, updated_at datetime, logged_in_at datetime,
deleted int(11) DEFAULT 0, delete_after datetime) ENGINE=InnoDB

Anyone have any ideas / clues as to what is wrong?

Thank you very much!

~ Brad

I’d the same problem. I fixed it changing MySQL configuration file,
enabling InnoDB.
The ‘mysql.ini’ file had a line which reads ‘skip-innodb’
I deleted that line, restarted mysql and voilá! now migrations run
without any problem at all

Joaquín Vicente