Mysql – Stored procedure that accepts an id as a parameter – how to execute for multiple ids list

MySQLstored-procedures

Hi I have the following stored procedure that works for a single id (field22):

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_procedure`(IN f22  INT)
BEGIN

UPDATE Table1 p join Table2 b set p.f1= (p.f1-  (LEAST(p.f2,b.amount)*coalesce((select rate from CurrencyExchangeRate where   fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-  15'),1))) ,
 p.f5= (p.f5- (LEAST(p.f2,b.amount)*coalesce((select f6 from   CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and   date(date)='2017-01-15'),1))),
p.f7= (p.f7- (LEAST(p.f8,b.amount)*coalesce((select rate from    CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-15'),1))) where status!=2 and p.id=b.f13 and b.id=f22;


UPDATE Tabl2 SET status=2, f1=3,changeDate=now() where id=f22 and status!=2;


INSERT INTO Table4 (type,f1,f2,date,f3,amount,f5,f6) select 11,2,tr.id,now(),tx.f11,tr.f12,tr.amount-tr.f13+tr.f14,tx.id from Table6 tr join  Table7 tx where tr.id=tx.f77 and tr.id=f22;

END

I would like to execute this stored procedure for a known list of ids i have:

  [1,22,443, 2112 ...];

Whats the quickest way to go about it?

Best Answer

If you can pass in the string `"1,22,443", then you construct the query:

SET @sql := CONCAT("... IN (", f22, ")...");

then PREPARE it and EXECUTE.

Not clean, but it should work.

If there is only one element to send in, the IN will still work fine. (An empty string would lead to a syntax error.)

Related Solutions

Mysql – Quick MySQL Backup (1 file per table)

Last time, I answered your question by suggesting you perform a well-scripted parallel dump of all tables.

Since the tables are lopsided in size, parallel table dumps are not much help for the 20GB and 13GB tables. What can be done? You must try something out of the ordinary. The good thing here is that all the tables are MyISAM.

Have you ever considered running an rsync? How do you do that?

EXAMPLE

You have /var/lib/mysql as your datadir for a remote production database

Perform an rsync on /var/lib/mysql on prod server (IP Address 10.1.2.30) to /backups on a backup server. Just run a script something like this on the backup server:

INIT_TS=0
COMP_TS=0
DIFF_TS=61
RSYNC_THRESHOLD=60
DBSERVER_IP=10.1.2.30
FOLDER_TO_BACKUP=/var/lib/mysql
mkdir /backups/backup_temp

while [ ${DIFF_TS} -gt ${RSYNC_THRESHOLD} ]
do
    INIT_TS=`date +%s`
    rsync -arv user01@${DBSERVER_IP}:${FOLDER_TO_BACKUP}/ /backups/backup_temp
    CURR_TS=`date +%s`
    (( DIFF_TS = CURR_TS - PREV_TS ))
done

MYSQL_CONN="-h${DBSERVER_IP} -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 3
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

rsync -arv user01@${DBSERVER_IP}:/var/lib/mysql/ /backups/backup_temp

BACKUP_TIMESTAMP=`date +"%Y%m%d%H%M%S"`
mv /backups/backup_temp /backups/${BACKUP_TIMESTAMP}

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here is what this basic backup script does

Step 01) rsync production's /var/lib/mysql (from remote server) to /backups on local server
Step 02) If running time of rsync is greater than 60 seconds, repeat Step 01
Step 03) Close all tables and issue global read lock
Step 04) Perform final rsync (SELECTs allowed only)
Step 05) Release global read lock

Notice the following:

the only mysql interaction is
- issuing the global read lock
- checking for the process ID that is holding the global read lock
- releasing the global read lock
FOLDER_TO_BACKUP is the datadir. If you want to backup a specific database (say mydb), set FOLDER_TO_BACKUP to /var/lib/mysql/mydb
Actual downtime is from the acquisition of the SLEEP_ID to the processing killing of the SLEEP_ID

Give it a Try !!!

UPDATE 2012-02-02 11:47 EDT

The above script secures mysqld in such a way that it takes a disklevel copy of the database while allowing mysqld to perform INSERTs, UPDATEs, and DELETEs during the copy. This is necessary since the MyISAM Storage Engine does not have a natural mechanism for allowing a copying of itself. Even powerful products like MySQL Enterprise Backup and Percona XtraBackup perform full table locking on MyISAM tables just like this. My personal preference is to script it so I can see it, feel it, taste it, and touch it and just know for myself that MyISAM is fully protected.

The advantage of using my script does stems from the fact that I shorten the length of downtime. In the context of this anwser, downtime is really the period where the mysqld process will only allow SELECTs to all MyISAM tables during the final rsync.

An additional benefit is that all MyISAM tables copied will all have the same point-in-time should you wish to restore all MyISAM tables as of the same point-in-time. That point-in-time will be based, not on the start of the backup process, but when the global read lock as acquired.

If you need to copy only specific tables and you can guarantee no DB Connection will touch it, only then can you toss aside any failsafes and copy any MyISAM table to your heart's content.

UPDATE 2012-02-03 11:47 EDT

If the length of time the rsync takes concerns you, here is an additional suggestion:

You should also make nightly backups of the binary logs. By having nightly copies of binary logs, you would essentially have the incremental backups via those binary logs.

That being said, you could set up a weekly or monthly run of the backup script. Recovery to a specific point-in-time would be your responsibility from there, but you would have every piece of data necessary to do so.

Mysql – Optimizing a query so MySQL will use the indexes for the joins

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)