Altering table structure in live database?

[Jim]

Hi,

I want to add a field to a table in a database that is live and being
accessed from the web. I'm using phpMyAdmin and when I try to add the field
I get error #1142 (ER_TABLEACCESS _DENIED_ERROR) "ALTER command denied to
user 'xxx' for table 'yyy'"

I'm a newbie to MySQL but I guess that's a reasonable error as there are
probably open connections. If that is the reason, is there a way I can force
all connections to close and not reopen while I make the alteration to the
table structure? It won't matter in this case that users will experience
access problems while this is taking place (all access is read-only too.)

If possible I want to do this without stopping the MySQL server - it is a
shared resource AFAIK and the host is in the US (I'm in the UK) so easiest
to coordinate if I can do it all from here if possible.

Many thanks.

--
Jim

[Gordon Burditt]

Re: Altering table structure in live database?

>I want to add a field to a table in a database that is live and being[color=blue]
>accessed from the web. I'm using phpMyAdmin and when I try to add the field
>I get error #1142 (ER_TABLEACCESS _DENIED_ERROR) "ALTER command denied to
>user 'xxx' for table 'yyy'"[/color]

You have insufficient privileges to do what you want to do. You
need, among other things, Alter privilege on that table.
There's one or more privileges you don't have.

Read up on the GRANT command, do SHOW GRANTS FOR me@my.host (I
believe phpMyAdmin has a nice way of showing your privileges),
and talk to your database administrator about getting additional
privileges needed.
[color=blue]
>I'm a newbie to MySQL but I guess that's a reasonable error as there are
>probably open connections.[/color]

Open connections don't matter. ALTER TABLE will wait if someone
is modifying the table. You can do a time-consuming ALTER TABLE
(ALTER TABLE usually copies the table, and if it has 10 million
rows this can take a while) and MySQL will even let queries read
the old table while you're building the new ones. Writes will
wait for the new table.

Assuming that the new table and the old table are both acceptable
to applications (e.g. you don't delete a column needed for a query,
or you don't add a column in the middle and mess up the ordering
of columns for "select * from ...", and you don't lengthen a field
beyond the buffer the application is using for it), and the
applications don't have tight time constraints, doing an ALTER TABLE
on the fly with read and write operations going on is safe. But
something may wait for a while, either the ALTER TABLE or some of
the other queries or both.
[color=blue]
>If that is the reason, is there a way I can force
>all connections to close and not reopen while I make the alteration to the
>table structure?[/color]

It's not necessary.

You can stop other connections from making changes to any
tables (this requires RELOAD privilege) with:
FLUSH TABLES WITH READ LOCK;
and undo it with:
UNLOCK TABLES;

(this is often used while you are making a backup).
However, this is overkill for altering one table.
[color=blue]
>It won't matter in this case that users will experience
>access problems while this is taking place (all access is read-only too.)[/color]

If all access is read-only, chances are there won't be any access
problems.
[color=blue]
>If possible I want to do this without stopping the MySQL server - it is a
>shared resource AFAIK and the host is in the US (I'm in the UK) so easiest
>to coordinate if I can do it all from here if possible.[/color]

Gordon L. Burditt