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

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 !!!