How to migrate stored procedures to testing db?

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>

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

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.