Altering table structure in live database?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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.


Re: Altering table structure in live database?

Quoted text here. Click to load it

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 (I
believe phpMyAdmin has a nice way of showing your privileges),
and talk to your database administrator about getting additional
privileges needed.

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

It's not necessary.

You can stop other connections from making changes to any
tables (this requires RELOAD privilege) with:
and undo it with:

(this is often used while you are making a backup).
However, this is overkill for altering one table.

Quoted text here. Click to load it

If all access is read-only, chances are there won't be any access

Quoted text here. Click to load it

                    Gordon L. Burditt

Site Timeline