The only way you can do it is with KILL. You will be able to see some
of the progress on rollback in SHOW INNODB STATUS. I recommend
innotop for watching this if you wish.
If you see the process's status as Killed in SHOW PROCESSLIST, you
have killed it.
Have a copy of War and Peace at the ready :-)
On Wed, Feb 20, 2008 at 1:37 PM, Mike Spreitzer <mspreitz@stripped> wrote:
> I am feeling more motivated to interrupt this operation now. What would
> be an effective way to do that? As a reminder, I submitted this operation
> using the GUI administration tool --- specifically the table editor. The
> whole tool is unresponsive while this operation is running. I can run and
> use another instance of the GUI administration tool.
>
> If I succeed in interrupting this operation, presumably the rollback will
> take quite a while. How can I tell whether I have successfully
> interrupted the operation and the rollback is what's nailing my machine?
>
> Thanks,
> Mike Spreitzer
> SMTP: mspreitz@stripped, Lotus Notes: Mike Spreitzer/Watson/IBM
> Office phone: +1-914-784-6424 (IBM T/L 863-)
> AOL Instant Messaging: M1k3Sprtzr
>
>
>
> Mike Spreitzer/Watson/IBM@IBMUS
> 02/19/08 08:34 PM
>
> To
> "Baron Schwartz" <baron@stripped>
> cc
> mysql@stripped
> Subject
> Re: How bad is adding BLOB column and index at the same time?
>
>
>
>
>
>
> Thanks, Baron. Yes, the table is bigger than memory. It took about 2.5
> days to create the table, inserting about 7,000 rows at a time; this
> column and index addition has been running for about a day now. I notice
> you did not say it was terribly stupid to create this index before putting
>
> the final data in the new column. So I infer there is no big motivation
> to interrupt the operation I have going.
>
> Thanks,
> Mike
>
>
>
>
> "Baron Schwartz" <baron@stripped>
> Sent by: baron.schwartz@stripped
> 02/19/08 08:13 PM
>
> To
> Mike Spreitzer/Watson/IBM@IBMUS
> cc
> mysql@stripped
> Subject
> Re: How bad is adding BLOB column and index at the same time?
>
>
>
>
>
>
> Hi,
>
> On Feb 19, 2008 5:20 PM, Mike Spreitzer <mspreitz@stripped> wrote:
> > I am new to MySQL, and wonder if I have done something terribly stupid.
> I
> > have an InnoDB table with 27 million rows. Without thinking very much,
> I
> > issued the following command through the GUI administration tool:
> >
> > ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL
> AFTER
> > `el_index`,
> > ADD INDEX `el_p2`(`el_p2`(150))
> >
> > Of course, all the values in this column will be the same. The index
> > will, if this ever completes, indicate that one value is associated with
> > all 27E6 rows. My primary question is, will this take O(27E6) time or
> > something worse (e.g., O(27E6 squared) time)?
> >
> > Of course my plan is to eventually put some interesting data in that new
> > column. I do not really need the index until the data is there.
> >
> > This database is not being used on-line, this is just for study, so I do
> > not mind large batch operations. I just don't want to be grossly stupid
> > in my choice of batch operations.
> >
> > My second question is: if I have indeed done something grossly stupid,
> > what is the best (if there is any at all!) way to interrupt it and
> > proceed.
>
> If the table is bigger than memory, building the index will be very slow.
>
> If you interrupt it, it's just going to roll back everything it's done
> so far. So you have the choice of either letting it finish and then
> dropping the index, or killing it and letting it roll back. (You
> can't prevent the roll back, even if you restart).
>
>
>
