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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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....
|
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...
|
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. ...
|
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...
|
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,...
|
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"....
|
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...
|
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...
|
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 =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
| |