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

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

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

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

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

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

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

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!

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