[Toolserver-l] problem with long running selects on toolserver.* tables

[Aryeh Gregor]

On Thu, Sep 3, 2009 at 3:23 AM, Daniel Kinzler<daniel at brightbyte.de> wrote:
> Actually, I think the problem is not an UPDATE or INSERT, the problem is that I
> use mysqldump to make a copy and then import the resulting dump - which starts
> by *dropping* the table and re-creating it. Apparently, the DROP is not
> performed while a SELECT is in progress (makes sense).

Yes, that would definitely do it.  It will also break transactions and
do all sorts of other bad things.  You're not meant to drop tables in
the course of ordinary operation.

> A workable solution would be to suppress the DROP (there's an option for that)
> and to use REPLACE instead of INSERT - but that is only supported by mysqldump
> since 5.1.3 apparently, we are on 5.0.something. Also, it would not propagate
> row deletions - not a big deal in this case though, since rows rarely get killed
> from toolserver.* tables. Seems like I need to write my own mysqlcopy or
> something...

You can just replace the DROP TABLE with DELETE FROM and it should
work fine.  It will take out more locks than strictly necessary, but
it's unlikely to be a problem for us.  Everything should be wrapped in
BEGIN/COMMIT so the intermediate state is hidden.  This could be done
easily and reliably with regex on mysqldump's output.