Forum: Rails Engines login_engine - rake migrate error, "ENGINE=InnoDB"

A012eb524ad9f0dfe44f0fa2237eaa64?d=identicon&s=25 brad (Guest)
on 2006-01-09 17:48
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
05d703f649ef1d07e78d7b479fb4c4ac?d=identicon&s=25 James Adam (Guest)
on 2006-01-09 18:16
(Received via mailing list)
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
A012eb524ad9f0dfe44f0fa2237eaa64?d=identicon&s=25 brad (Guest)
on 2006-01-10 06:18
James Adam 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
05d703f649ef1d07e78d7b479fb4c4ac?d=identicon&s=25 James Adam (Guest)
on 2006-01-10 10:57
(Received via mailing list)
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+Data...

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

- james
A012eb524ad9f0dfe44f0fa2237eaa64?d=identicon&s=25 brad (Guest)
on 2006-01-12 21:27
Right you were:

James Adam 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
2ffe981753288446bbb91798ab3a8d17?d=identicon&s=25 Louis (Guest)
on 2006-11-08 08:03
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
78f821588ae1bda6c75cf02988937a0c?d=identicon&s=25 Michał Stefanów (stefek99)
on 2007-10-27 13:02
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!
956aa05b9393a462971188030318e81d?d=identicon&s=25 Joaquín Vicente (wacko)
on 2009-07-02 09:16
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
This topic is locked and can not be replied to.