search

jnraine / munkiserver

Visit https://github.com/munkiserver/munkiserver for active development — this repository is no longer maintained
85 stars 27 forks source link

Add support for MySQL #123

Closed treydock closed 12 years ago

treydock commented 12 years ago

This is the same request as #111, that is complete and squashed into a single commit.

The only thing lacking is the new tests functionality. Will update once added.

jnraine commented 12 years ago

I began work on this merge and test this morning. So far, so good -- even with the monstrous migration ;). I didn't get a chance to test a migration of our production SQLite database to mySQL. I'll pick up this thread at my next available chance.

jnraine commented 12 years ago

I attempted to pull in our production database using taps and about a minute after the migration started, it threw the following:

/Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `query': closed MySQL connection (Mysql2::Error)
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `block in log_connection_execute'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/logging.rb:28:in `log_yield'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `log_connection_execute'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/query.rb:414:in `rollback_transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/adapters/shared/mysql.rb:234:in `rollback_transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/query.rb:225:in `rescue in _transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/query.rb:235:in `_transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/query.rb:209:in `block in transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/connection_pool/threaded.rb:84:in `hold'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/connecting.rb:226:in `synchronize'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/database/query.rb:207:in `transaction'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/sequel-3.20.0/lib/sequel/dataset/actions.rb:243:in `import'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/data_stream.rb:315:in `import_rows'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/data_stream.rb:142:in `fetch_remote'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:308:in `block in pull_data_from_table'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:301:in `loop'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:301:in `pull_data_from_table'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:284:in `block in pull_data'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:278:in `each'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:278:in `pull_data'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:253:in `block in run'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:203:in `call'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:203:in `catch_errors'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/operation.rb:246:in `run'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/cli.rb:171:in `clientxfer'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/cli.rb:38:in `pull'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/lib/taps/cli.rb:26:in `run'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/gems/taps-0.3.23/bin/taps:6:in `<top (required)>'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/bin/taps:19:in `load'
    from /Users/jnraine/.rvm/gems/ruby-1.9.2-p180@rails-3.0.10/bin/taps:19:in `<main>'
taps pull mysql2://root:admin@localhost/munkiserver2   12.44s user 2.61s system 28% cpu 52.881 total

Did you experience similar errors in your tests?

treydock commented 12 years ago

I didn't run into anything like that. Do you have the last few lines from the transfer? It should show what table it was migrating before it failed. There may be something there that doesn't agree with MySQL.

I'll try running mine again to see if I can work out how to get more useful information out of the migration process.

Also it may be useful to move the huge migration out of the standard migration. That migration shouldn't be run unless the Munkiserver install was previously running SQlite. It won't be necessary for fresh installs. Maybe a rake task would be a better place than the standard migration.

jnraine commented 12 years ago

I ran my migrations before converting to MySQL and all went well. It was when I tried to push the SQLite database into MySQL through taps. There must be something disagreeable in our prod DB. I don't have any of the logs on hand at the moment. I'll try again and copy the logs here.

treydock commented 12 years ago

Updated the large migration so that it won't always run and move data around. It should only run when the default columns are detected which should only be with SQLite since they can't be set to default in MySQL. The rollback will not run (re-add defaults) if the adapter is MySQL (mysql2).

Unfortunately it seems that adding default values to columns is MUCH easier than removing them completely.

Please disregard the two other commits / reverts ... made changes to wrong branch.

rickychilcott commented 12 years ago

I just received a similar error to Jordans:

Mine fails after install_items

install_items: 100% |==========================================| Time: 00:00:00
/Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `query': closed MySQL connection (Mysql2::Error)
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `block in log_connection_execute'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/logging.rb:28:in `log_yield'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in `log_connection_execute'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/query.rb:414:in `rollback_transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/adapters/shared/mysql.rb:234:in `rollback_transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/query.rb:225:in `rescue in _transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/query.rb:235:in `_transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/query.rb:209:in `block in transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/connection_pool/threaded.rb:84:in `hold'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/connecting.rb:226:in `synchronize'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/database/query.rb:207:in `transaction'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/sequel-3.20.0/lib/sequel/dataset/actions.rb:243:in `import'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/data_stream.rb:315:in `import_rows'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/data_stream.rb:142:in `fetch_remote'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:308:in `block in pull_data_from_table'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:301:in `loop'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:301:in `pull_data_from_table'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:284:in `block in pull_data'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:278:in `each'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:278:in `pull_data'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:253:in `block in run'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:203:in `call'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:203:in `catch_errors'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/operation.rb:246:in `run'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/cli.rb:171:in `clientxfer'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/cli.rb:38:in `pull'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/lib/taps/cli.rb:26:in `run'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/gems/taps-0.3.23/bin/taps:6:in `<top (required)>'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/bin/taps:19:in `load'
    from /Users/chilcotr/.rvm/gems/ruby-1.9.3-p0/bin/taps:19:in `<main>'
treydock commented 12 years ago

An FYI, commit cea98fd is only necessary once we move to Rails 3.2.2. I found that it sees "{}.to_yaml" as type string which throws an error when merge is called on raw_tags. Removing the "to_yaml" fixes but still testing see anything else changes when using Rails 3.2.2.

pinoytech commented 12 years ago

just got that bug like the others. I think its when you migrate a large table.. it stops once it gets to that table.. the table size is 26k rows: """ /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:in query': closed MySQL connection (Mysql2::Error) from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:inblock in log_connection_execute' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/logging.rb:28:in log_yield' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/logging.rb:53:inlog_connection_execute' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/query.rb:414:in rollback_transaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/adapters/shared/mysql.rb:234:inrollback_transaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/query.rb:225:in rescue in _transaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/query.rb:235:in_transaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/query.rb:209:in block in transaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/connection_pool/threaded.rb:84:inhold' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/connecting.rb:226:in synchronize' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/database/query.rb:207:intransaction' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/sequel-3.20.0/lib/sequel/dataset/actions.rb:243:in import' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/data_stream.rb:315:inimport_rows' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/data_stream.rb:142:in fetch_remote' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:308:inblock in pull_data_from_table' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:301:in loop' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:301:inpull_data_from_table' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:284:in block in pull_data' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:278:ineach' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:278:in pull_data' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:253:inblock in run' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:203:in call' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:203:incatch_errors' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/operation.rb:246:in run' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/cli.rb:172:inclientxfer' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/cli.rb:39:in pull' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/lib/taps/cli.rb:27:inrun' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/gems/taps-0.3.24/bin/taps:6:in <top (required)>' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/bin/taps:19:inload' from /Users/teejay/.rvm/gems/ruby-1.9.3-p194@fs/bin/taps:19:in `

' """

treydock commented 12 years ago

@pinoytech , What version of MySQL? based on the logging can you tell which table it stopped at ? I'd be interested to know what the last record contained that it tried.

Did you run the migrations on the sqlite database before doing the taps data transfer?

I was able to test the migration on Ricky's data set just fine which took ~45 minutes to complete on a few GB sql file. This was in Linux using MySQL-5.1.61.

rickychilcott commented 12 years ago

Hey @pinoytech,

I was actually able to do my upgrade. I documented most of the steps of the process here: https://github.com/jnraine/munkiserver/wiki/Moving-to-MySQL

Ricky

On May 14, 2012, at 9:44 PM, treydock wrote:

@pinoytech , What version of MySQL? based on the logging can you tell which table it stopped at ? I'd be interested to know what the last record contained that it tried.

Did you run the migrations on the sqlite database before doing the taps data transfer?

I was able to test the migration on Ricky's data set just fine which took ~45 minutes to complete on a few GB sql file. This was in Linux using MySQL-5.1.61.

Reply to this email directly or view it on GitHub: https://github.com/jnraine/munkiserver/pull/123#issuecomment-5706611

pinoytech commented 12 years ago

hi @rickychilcott I was moving from postgres to mysql.

jnraine commented 12 years ago

I've just merged this into master.