create mysql functions or stored procedure from a rake task

I'm trying to create functions and stored procedures inside RoR project MySql database, these objects are needed for me to import historic data from the old platform the customer is actually running.....

well, i've created a bounch of rake functions inside a task, chained them top pre-populate some service tables and operate all migrations prior to try to start data import from older version of website.

I've tried also to process records one-by-one, reading from origin, doing some needed calcs to adapt structures ti the new platform and trying to store them back inside new database, but there i've found some strange behaviours from database engine. I've operated on a limited set of data for testing (extracted records with limit to 20 rows!), all is good on reading, but unable to complete writing action, it randomly hungs (note, no errors at all) it really seems to be freezed, the stuck point is always different (may be record 1/20 or 3, 5, 11 or may be rarely complete!)

so, I've changed data load method, going to operate directly using stored procedure inside mysql database, it works! more than 30k records are read, processed and stored in less than 15 secs!!

now, going to make this automatic, inside rake task to be operative when we'll go online on production server.... i'm looking to avoid any manual action just to lower 'human' error rate....

first step is for me creation of functions and stored procedures inside 'new' mysql database...

I've this script (it works fine inside mysql workbench for creating a function):

USE APERWeb_development;
DROP function IF EXISTS APERWeb_development.prov_id;

DELIMITER $$
USE APERWeb_development$$
CREATE DEFINER=`root`@`localhost` FUNCTION prov_id(prov char(2)) RETURNS bigint
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE pvid BIGINT;
    SELECT p.id INTO pvid FROM APERWeb_development.provinces AS p WHERE p.Prov = prov;
RETURN pvid;
END$$

DELIMITER ;

it works great also if I save it as 'create-func.sql' and load it inside rails console like:

rails dbconsole < lib/sql/create-func.sql

to make this pass from a rake task, I've tried this way, executing script row-by-row

mysql_script = File.open("#{Rails.root}/lib/sql/create-func.sql").read
mysql_script.split('/;$/').each do |sql_statement|

  ActiveRecord::Base.connection.execute(sql_statement)

end

but running the rake task this is the result:

.......
rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP function IF EXISTS APERWeb_production.prov_id;

DELIMITER $$
USE APERWeb_d' at line 2
.......

if someone knows a better way (going to correct, any 'working way'!) to execute this kind of scripts against mysql database engine from inside a rake task.... any suggestion will be greatly appreciated!!

thanks Francesco

edit on 8.11.2023 as smathy suggested....

going to try this way:

create_prod_id_function =<<-SQL
  USE APERWeb_development;
  DROP function IF EXISTS APERWeb_development.prov_id;

  DELIMITER $$
  USE APERWeb_development$$
  CREATE DEFINER=`root`@`localhost` FUNCTION prov_id(prov char(2)) RETURNS bigint
      READS SQL DATA
      DETERMINISTIC
  BEGIN
    DECLARE pvid BIGINT;
    SELECT p.id INTO pvid FROM APERWeb_development.provinces AS p WHERE p.Prov = prov;
  RETURN pvid;
  END$$

  DELIMITER ;
SQL


ActiveRecord::Base.connection.execute(create_prod_id_function)

result is again an error:

......
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP function IF EXISTS APERWeb_development.prov_id;

        DELIMITER $$
     ' at line 2

......

tried to change last line as...

ActiveRecord::Base.connection.exec_query(create_prod_id_function)

exact same error during execution

other suggestions??

finally, I've found a solution....

created a script file, simplified it because using 'direct' database connection will focus script execution against current rails app & environment....

here is the script, store inside file: lib/sql/00-create_provid_function.sql

CREATE DEFINER=`root`@`localhost` FUNCTION `prov_id`(prov char(2)) RETURNS bigint
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE pvid BIGINT;
    SELECT p.id INTO pvid FROM provinces AS p WHERE p.Prov = prov;
RETURN pvid;
END

then, inside the rake task i'm going to process this with this command:

ActiveRecord::Base.connection.execute(IO.read(File.join("lib/sql","00-create_provid_function.sql")))

it works, without any issue with another more complex script that creates a stored procedure i'm going to use to process all records from old db, elaborate them and store using new database & table schema!

hoping to be helpful, regards

Francesco