UPDATE command denied

Hi,
I'm using your mercury class free mysql database offer for windows azure.
When I'm trying to update a record I get an error message saying Error Code: 1142. UPDATE command denied to user 'xxx'@'65.52.73.241' for table 'yyy'

I know there's a size quota of 20MB for this offer, but querying the database using the query
SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;
I get the database size which is only 5MB.

Can you let me know what the problem is?
1 person has
this problem
+1
Reply
  • Hi Kostas,

    If you can give me your DB schema name, I will look up your account and see what's going on, and release the lock if appropriate

    You should be aware that temporary tables (created in the course of executing queries) count towards the size ceiling. So it is possible that you can transiently cross that line even though your DB size stays below it. That said, it seems unlikely that you could make 15+ MB of temp tables, so I'd like to investigate this for you.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Michael,

    My db schema is ksharpAJ0nNeh4th

    I don't know if WordPress (that's what is installed) creates any temp tables but I don't believe so.
    Anyway I would be grateful if you could investigate this for me.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Kostas,

    First, I removed the restrictive lock on your db since it is currently well under the size limit, as you say. However, our records show that it was recently much larger.

    (Temp tables get created by MySQL in the ordinary course of resolving queries; it doesn't depend on Wordpress or whatever the querying app.)

    So you should be good to go now; if you have any further concerns please let us know.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Michael,
    I'm having the same problem as before.
    My DB reports 6.6MB but I can't save anything in it. If it's the log/temp files case again, is there a way I can truncate it? How can the log file grow when all I've done lately is read from the database (no update/inserts)?
    Thank you so much for having a look into it.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Kostas,

    I released the lock on your db.

    To be clear, it's not a log file issue; your logs are not counted against you. It's specifically temp tables. The best way to guard against the problem is to make sure that your queries are optimized. Queries that require table scans or otherwise result in a lot of rows returned can generate large temp tables.

    I hope this helps; let me know if I can be of further assistance.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Daniel,
    Unfortunately I'm using a plain vanilla wordpress installation against the database and can't really change anything on the way the queries are performed.

    I would expect though, that those temporary tables that are causing the problem are destroyed and the database is cleaned up once the queries where completed.

    If that's not possible I would really like to find a way to clean up any temporary storage used, cause I can't call for help every time this happens.

    Thank you so much.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Sure, I understand that. Our system sweeps periodically to unlock dbs that were locked but have come back into compliance; in general you shouldn't find yourself locked down for more than a 20 minutes after going temporarily over the ceiling.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I’m frustrated
    Hi Michael,
    Unfortunately I'm once again locked.
    Although I've waited for more than an hour for the database to come back nothing has changed.
    I can't have this happening all the time. Can you please give me a solution for this? As I mentioned earlier, if that's not possible can I have a way to clean up any temporary storage used without having to call for help every time this happens.

    Thank you.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Kostas,

    I'm sorry for the inconvenience. If you're not able to optimize your queries, and I gather that you're using a stock Wordpress install, the solution is simply to upgrade to the next service tier.

    It's not a question of cleaning up temp storage; temporary tables persist only for the duration of the query which creates them, but if they push you over the storage limit, the system will apply the lock.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Michael,
    So you're suggesting that although I'm currently using 43% of my 20MB available storage (Running a query to calculate my db size reports that I'm using 8,6MB) I should buy a subscription that gives me 1GB of storage (50 times bigger) in order to run without problems.
    Does this make sense to you?
    Why is temp storage that is created by the database engine - not me - in order to facilitate a query has to be included in the space limit check to begin with, why do I have to pay for it when it's going to be de-allocated at some point once the query has completed?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Kostas,

    Temp storage gets allocated because that's how MySQL works. I'm not an expert on the program's internals, but that's how it works through queries with multiple logical clauses.

    We have to include that in our space calculation because, temporary or not, they consume system resources and our product is priced in relation to our cost to maintain those resources.

    I hear that you are unhappy about how this is working in your particular case, and I understand that, but you are using a free service level which - if you look at our pricing page - is explicitly stated as suitable for development and proof-of-concept, not necessarily for a production environment. Many of our customers do successfully run production applications at this level, but once your db grows to a certain point, we have to ask you to upgrade to continue with uninterrupted service. No matter where we drew that line, _someone_ would end up in your position.

    Again, I apologize for the inconvenience.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated