473,289 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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
Nov 23 '05 #1
1 4057
>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'"
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.
I'm a newbie to MySQL but I guess that's a reasonable error as there are
probably open connections.
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.
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'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.
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 all access is read-only, chances are there won't be any access
problems.
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.


Gordon L. Burditt
Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dariusz | last post by:
I have written a database that counts the number of times a file has been accessed, so I can then later display the results on what is "hot" and what is not. At the moment all it does is count the...
0
by: Dave Serrano | last post by:
I have a question about altering tables and growth of the database and transaction log. I have a database which is approximately 35GB. I had to make a change to a column in the largest table...
3
by: Dan Williams | last post by:
I'm trying to do a simple alteration to the table design of one of our SQL 2k tables, simply changing an identity row so that its not 'not for replication', and its taking absolutely ages to do so,...
36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
3
by: Douglas Buchanan | last post by:
Newbie to donnet This is an example of a structure given in vs.net help ============================ Private Structure Employee Public GivenName As String ' This employee's given name....
5
by: brett valjalo | last post by:
Hey Gang! SORRY ABOUT THE LENGTH! Nice to see some of the same faces around this place from way back when ... Whatta buncha CDMA addicts some o' y'all are ;) Don't get me wrong, I...
9
by: JimmyKoolPantz | last post by:
IDE: Visual Studio 2005 Language: VB.NET Fox Pro Driver Version: 9.0.0.3504 Problem: I currently have a problem altering a DBF file. I do not get any syntax errors when running the program. ...
10
by: Omar | last post by:
I'm looking for a programming language or module that sorta looks and feels like MS Excel (I love and think in tables), yet has the power and open-endedness of python or javascript. I'm still...
1
by: zufie | last post by:
Hi, I want to specifying a foreign key by altering a table. First, I create an ORDERS table without specifying a foreign key. Here is my code: CREATE TABLE ORDERS (Order_ID integer,...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.