Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've an MySQL 5.1 slave for our BI team.

They need to make some CREATE SELECT with big select queries (several million lines).

As CREATE SELECT is a DDL, if the replication attempts to update some rows in same tables than the SELECT statement, replication is blocked until the freeing of the CREATE SELECT.

Do you now a good non-blocking alternative to thoses CREATE SELECT statements?

I thought to an SELECT INTO OUTPUT FILE then LOAD DATA INFILE but they will fill out our disks as BI guys like to do... :)

Max.

share|improve this question
    
Is the CREATE SELECT from a single table or a complex join ? Please post the query you have. –  RolandoMySQLDBA Mar 1 '13 at 16:24
    
Complex joins (8-10 tables it's not a particular query) –  Maxime FOUILLEUL Mar 1 '13 at 16:36
    
Are all the tables InnoDB (please say yes) ??? –  RolandoMySQLDBA Mar 1 '13 at 16:37
    
I think so (pretty sure), i don't have a sample query, we have some MyISAM tables but i think the problem came from the CREATE statement that create a transaction and blocks rows until it'is done. –  Maxime FOUILLEUL Mar 1 '13 at 16:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.