How to migrate stored procedures to testing db?

Question

I have an issue with stored procedures and the test database in Rails 3.0.7. When running

rake db:test:prepare

it migrates the db tables from schema.rb and not from migrations directly. The procedures are created within migrations by calling the execute method and passing in an SQL string such as CREATE FUNCTION foo() ... BEGIN ... END;.

So after researching, I found that you should use

config.active_record.schema_format = :sql

inside application.rb. After adding this line, I executed

rake db:structure:dump rake db:test:clone_structure

The first one is supposed to dump the structure into a development.sql file and the second one creates the testing database from this file. But my stored procedures, and functions are still not appearing in the testing db. If anyone knows something about this issue. Help will be appreciated.

I also tried running rake db:test:prepare again, but still no results.

MySQL 5.5, Rails 3.0.7, Ruby 1.8.7.

Thanks in advance!

Answer 1

There is no other rake task for that and structure_dump is defined like this:

# File activerecord/lib/active_record/connection_adapters/mysql_adapter.rb, line 354
  def structure_dump #:nodoc:
    if supports_views?
      sql = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"
    else
      sql = "SHOW TABLES"
    end

    select_all(sql).map do |table|
      table.delete('Table_type')
      select_one("SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}")["Create Table"] + ";\n\n"
    end.join("")
  end

so it apparently works only for tables, not procedures, unless you monkeypatch it.

The only solution as far as I know is to use shell:

mysqldump -R -u <user> <development_database> | mysql -u <user> <test_database>

Answer 2

Looks like (I have not tested) rake db:structure:dump support for stored functions and procedures has been added in Rails 5. See this commit in the rails GitHub project. The --routines flag to mysqldump is described here. Note method structure_dump looks very different than when Jan Minárik answered six years ago.

Answer 3

DISCLAIMER : I am not a Ruby-on-Rails Programmer

Strictly in terms of MySQL, you basically have two ways to extract Stored Procedures (SP) and Stored Functions (SF).

Keep in mind that mysql.proc and information_schema.routines provide the housing on disk and in memory for SPs. Yet, there are 2 SQL statements to retrieve them: SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION.

The first way involves collecting all SPs and SFs using mysql.proc and form them into SQL statements that expose them.

Example I have 6 SPs and 2 SFs in my test database. Here is how to generate SQL for all 8 routines:

mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc;
+-----------------------------------------------------+
| SQLStatements                                       |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G  |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G  |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G  |
| SHOW CREATE FUNCTION `test`.`InsertName`\G          |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G   |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G      |
+-----------------------------------------------------+
8 rows in set (0.00 sec)

You can cycle through and collect the code needed to each stored procedure and function.

Triggers must be collected separately.

In MySQL 5.x you can collect triggers using this query:

mysql> SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+--------------------------------------------------+
| SQLStatements                                    |
+--------------------------------------------------+
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G    |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G |
+--------------------------------------------------+

or to save time UNION the two SQL statements

mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc UNION SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+-----------------------------------------------------+
| SQLStatements                                       |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G  |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G  |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G  |
| SHOW CREATE FUNCTION `test`.`InsertName`\G          |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G   |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G      |
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G       |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G    |
+-----------------------------------------------------+
10 rows in set (0.07 sec)

The second way is the preferred way for DBAs, using mysqldump.

This will collect all table structures, SPs, SFs, and triggers in a single file.

mysqldump -h... -u... -p... --no-data --routines --triggers --all-databases > MySQLSchema.sql

This will do the same but without the CREATE TABLE stuff:

mysqldump -h... -u... -p... --no-data --no-create-info --routines --triggers --all-databases > MySQLSchema.sql

Give these a Try !!!

Answer 4

If you want Ruby dumps (as opposed to SQL dumps) you might give this gem a try:

https://github.com/jovoto-team/trackless_triggers

It supports dumping triggers and functions for mysql out of the box without introducing new rake tasks. It is based on tenderlove's trigger_happy plugin.

Answer 5

On Rails 4 I hooked it in as a post load hook on the db:test:load rake task as follows:

require File.expand_path('../config/application', __FILE__)

Rails.application.load_tasks

namespace :db do
  namespace :test do

    task :post_load_hook do
      re_create_sps
    end

    def re_create_sps
      [20170905123456, 20170905123457].each do |version|
        ActiveRecord::Migrator.run(
          :down, ActiveRecord::Migrator.migrations_paths, version)
        ActiveRecord::Migrator.run(
          :up, ActiveRecord::Migrator.migrations_paths, version)
      end
    end

    # stored procs must be restored each time.
  end
end

Rake::Task['db:test:load'].enhance(['db:test:post_load_hook'])

This approach will run automatically so you don't have to manually reload the db each test run and it will only affect the db:test:load task, so I think it is fairly isolated.

IMHO a bit ugly having the migration ids in the task, so you could alternatively extract the migration code to a lib and call it from both the migration and the Rake task above to clean it up.

Answer 6

Was searching for how to do the same thing then saw this: http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps

To quote:

"db/schema.rb cannot express database specific items such as foreign key constraints, triggers or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this then you should set the schema format to :sql."

i.e.:

config.active_record.schema_format = :sql

I haven't tried it yet myself though so I'll post a follow-up later.

Answer 7

I took Matthew Bass's method of removing existing rake task and redefined a task using mysqldump with the options that RolandoMySQLDBA provided

http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/

Rake::TaskManager.class_eval do
  def remove_task(task_name)
    @tasks.delete(task_name.to_s)
  end
end

def remove_task(task_name)
  Rake.application.remove_task(task_name)
end

# Override existing test task to prevent integrations
# from being run unless specifically asked for
remove_task 'db:test:prepare'

namespace :db do
  namespace :test do
    desc "Create a db/schema.rb file"
    task :prepare => :environment do
      sh "mysqldump --routines --no-data -u root ni | mysql -u root ni_test"
    end
  end
end