Hi,
I have been using myisam tables in mysql with rails because my client
will soon want fulltext searchable content, however when I migrated my
development db from schema.rb all of the tables generated were innodb. I
understand that I can override this, but also like the transactions and
foreign keys of innodb. So 2 questions really:
- are transactions in activerecord dependent on innodb or do they
recreate that functionality?
- if I did stick with innodb would there be a workaround for effective
text searching?
thanks
dorian
–
I do things for love or money
Hi !
2006/3/24, Dorian M. [email protected]:
- are transactions in activerecord dependent on innodb or do they recreate that functionality?
ActiveRecord builds on the underlying database engine to provide
transactional support. If you have MyISAM tables, forget about
transactions. That being said, there’s nothing preventing you from
having different table engines in the same database.
- if I did stick with innodb would there be a workaround for effective text searching?
Take a look at Ferret, Indexed Search Engine and ActiveSearch Plugin:
http://wiki.rubyonrails.com/rails/pages/HowToIntegrateFerretWithRails
http://wiki.rubyonrails.org/rails/pages/Indexed+Search+Engine
http://wiki.rubyonrails.org/rails/pages/Active+Search+Plugin
Hope that helps !
Hi Dorian,
- are transactions in activerecord dependent on innodb or do they
recreate that functionality?
The transactions in ActiveRecord are dependent on the underlying
database to provide the transaction support since the transaction API
only creates the required transaction SQL.
That also means you cannot use transactions with MyISAM. Must use
InnoDB or BDB.
- if I did stick with innodb would there be a workaround for
effective text searching?
Yes, some possibilities include:
- If using replication, have your master table type as InnoDB and
slave table type as MyISAM. Then run all the SELECT statements on
slaves.
- Use Lucene (See Erik H.'s book) or Ferret for your project.
There’s some interesting discussion on my blog about InnoDB vs.
MyISAM.
MySQL Consulting and NoSQL Consulting: MySQL DBA: Innodb or MyISAM? What's your preference?
You may also want to see:
Peak Obsession
If you need to know how to integrate Lucene, I can provide you with
code.
Hope this helps.
Frank
Dorian M. [email protected] wrote: Hi,
I have been using myisam tables in mysql with rails because my client
will soon want fulltext searchable content, however when I migrated my
development db from schema.rb all of the tables generated were innodb.
I understand that I can override this, but also like the transactions
and foreign keys of innodb. So 2 questions really:
- are transactions in activerecord dependent on innodb or do they
recreate that functionality?
- if I did stick with innodb would there be a workaround for effective
text searching?
thanks
dorian
–
I do things for love or money
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
Want to read my Pro Rails book or Pro Server Management book? Become a
beta reader now. Write me to learn more.
Rails Blog: http://railsruby.blogspot.com
MySQL Blog: http://mysqldatabaseadministration.blogspot.com
Linux / Security Blog: http://frankmash.blogspot.com
Programming One Liners: http://programming-oneliners.blogspot.com
–
View this message in context:
http://www.nabble.com/innodb-vs-myisam-t1334763.html#a3582672
Sent from the RubyOnRails Users forum at Nabble.com.
On Fri, 2006-03-24 at 00:43 -0500, Dorian M. wrote:
- are transactions in activerecord dependent on innodb or do they
recreate that functionality?
Transactions in Rails require that you use a database that supports
transactions. With MySQL, you need to use InnoDB (which Oracle now
owns…).
- if I did stick with innodb would there be a workaround for
effective text searching?
You need to do some fun stuff to make that work.
If you aren’t forced to stay on MySQL, you might consider another open
source database… PostgreSQL.
Transactional support + tsearch2 = relational data integrity + smart
full text searching.
More info:
-Robby
–
/**************************************************************
- Robby R., Founder & Executive Director *
- PLANET ARGON, LLC | www.planetargon.com *
- Ruby on Rails Development, Consulting, and Hosting *
- Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 *
- blog: www.robbyonrails.com | book: www.programmingrails.com *
***************************************************************/
On 3/25/06, Robby R. [email protected] wrote:
-Robby
So, once again I’ll demonstrate just how really dense I am, in hopes of
learning a thing or two 
I have tried no less than three time before to set up postgresql on my
Debian system using apt-get. It installs without a hitch, of course, but
that’s as far as I can get. I have never been able to successfully
create a
user account, much less a db or any tables that I can use.
I have tried to RTFM and GTFW (many times) on the subject, but I’ve yet
to
find any tutorial or howto that really works with the default Debian
setup
(on Debian unstable, if that matters). Can anyone point me to any Debian
specific references, that they can verify have worked for them?
The one thing I’ve not tried is downloading the source tar.gz and
configuring/compiling it myself. Perhaps this is the best way?
Thanks In Advance,
Howard
Howard R. wrote:
-
http://www.postgresql.org/
Debian system using apt-get. It installs without a hitch, of course, but
that’s as far as I can get. I have never been able to successfully create a
user account, much less a db or any tables that I can use.
It’s really simple, once you know how:
As root:
su - postgres
createuser yourusername
at this point it asks if you want to be able to create databases, users
and so on. Say yes.
Now you can, from a shell as yourself, do commands like:
createdb foobar
psql foobar_development
dropdb foobar_development
createuser rails
and so on… Of course you can do that from the DB too, but it’s
generally more convenient to do it from the command line.
The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.
Once you get going with Postgresql, it’s hard to use Mysql again, IMO
(although in fairness, perhaps 5.0, with InnoDB is starting to be an ok
system).
–
David N. Welton
Linux, Open Source Consulting
David,
Thanks for the reply.
It’s really simple, once you know how:
As root:
su - postgres
createuser yourusername
~$ su
Password:
/home/howardroberts# su - postgres
/home/howardroberts# createuser howardroberts
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL: role “root”
does
not exist
khaosdebian:/home/howardroberts#
The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.
I think they might…
Howard
~$ su
Password:
/home/howardroberts# su - postgres
/home/howardroberts# createuser howardroberts
You should have gotten a $ prompt, not a # prompt. You’re doing
something wrong…
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL: role “root” does
not exist
Indeed, you’re still root. Perhaps the postgres user doesn’t have a
shell?
khaosdebian:/home/howardroberts#
The permissions in pg_hba.conf might take some fiddling with, but it’s
nothing that difficult.
I think they might…
That doesn’t have anything to do with the above problem.
–
David N. Welton
Linux, Open Source Consulting
You might also consider firebird (formerly Interbase, formerly RDB). It
has a successful history spanning nearly 30 years on many platforms,
both as a commercial engine and as an open source engine.
http://www.ibphoenix.com
