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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.