This was a frustrating problem, because I had a a view and a routine in my dump that were using a function that had not been created by the dump as of yet. The –force command was not working for me…..

The Solution:

mysql -f -t -vvv -h localhost -u root -p < importfile.sql

Dump and Import one Liner (put all on one line)

mysqldump -h remote.host.com -u username  -pPassword --lock-tables=false 

--routines=true MyDatabase | mysql -f -t -vvv -h localhost -u username2 –pPassword2 

--force MyDatabase

Greg MySQL, SQL Server Stuff MySQL

I needed to take identical tables on 3 databases, and merge it onto a new table on a new database. Clearly the reason for the task was consolidation. My biggest complaint about mysql is its lack of linked servers. You can do federated, but lets be honest….It’s a pain in the %$#^

So when it comes to consolidating, the best way (at least in my situation) was to manually merge the data. I thought of a few ways this could be done, but here is how I ended up doing it.

1) DUMP DATA to a File.

select broker_description,date,type,
       case when type = "O" then abs(Shares*100)
       else abs(Shares) end as Quantity,
       commission,'someportfolio' as portfolio
from accounting.trades r
join tickers t
on t.id = r.ticker
join exec_broker b
on b.id = r.exec_broker
where abs(commission > 0)
INTO OUTFILE "/tmp/dump.txt"

2) SCP the dump file from server1 to server2 (server where merge table will be)

scp /tmp/dump.txt root@10.1.10.20:/tmp/

3) IMPORT DATA (this assumes you scp’d the file to /tmp/ on the merge table server)

LOAD DATA  INFILE '/tmp/dump.txt'
INTO TABLE commission
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(broker_description, date, type,Quantity,commission,portfolio);

NOT NECESSARY FROM HERE DOWN>>>>>THIS WAS AN EXTRA STEP

4) Dump server3 table data to file

In this case I actually needed to dump data from a mysql database that was on a Windows Server. Similar to step 1.

select broker_description,date,type,
       case when type = "O" then abs(Shares*100)
       else abs(Shares) end as Quantity,
       commission,'portfolio2' as portfolio
from accounting.trades r
join tickers t
on t.id = r.ticker
join exec_broker b
on b.id = r.exec_broker
where abs(commission > 0)
INTO OUTFILE "c:/temp/dump.txt"

5) I used WinSCP to upload the file from c:\temp to /tmp on target server

Greg MySQL MySQL

So I kept getting this error using DBD-mysql. DBD::mysql::st execute failed: PROCEDURE Data.getToday can’t return a result set in the given context at demo.pl line 15. The problem turned out to be that, I was using version 3.002 of the MySQL driver. I upgraded to 4.005 and all was good! Hope this helps you out…

Greg MySQL

I haven’t seen this very well and clearly documented, as far as passing parameters go. Here is a simple create user proc.

DELIMITER $$

DROP PROCEDURE IF EXISTS `portfoliomanager`.`createNewUser` $$
CREATE PROCEDURE `portfoliomanager`.`createNewUser` (IN username varchar(45),IN pass varchar(45))
BEGIN
INSERT INTO portfoliomanager.users (email,password) VALUES(username,pass);
END $$

DELIMITER ;

Greg MySQL