This is driving me mad.

If I run a particular (very simple) query on a customers server phpMyAdmin seems to go mad taking all resources until the server crashes. I've tested in my own dev environment first (of course) and it seems to work just fine .. I think my dev environment is strict enough (or is it?)

Server version: 5.0.51a-community-nt

sql mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The query is ..

    SELECT o.date_purchased, MONTH( o.date_purchased ) AS month , YEAR( o.date_purchased ) AS year, 
           op.products_price, op.final_price, op.products_cost, op.products_quantity AS quantity, 
           opa.products_options_values AS manufacturers_name
    FROM orders o, orders_products op, orders_products_attributes opa
    WHERE o.orders_id = op.orders_id
    AND YEAR(o.date_purchased) = '2008'
    AND op.products_id = '4157'
    AND opa.orders_products_id = op.orders_products_id
    AND (opa.products_options = 'Manufacturer' OR opa.products_options = 'Producent')

Anything obvious here that I'm not seeing please?

Oh best mention that all tables and fields are correct and the query never returns a result phpMyAdmin freezes.

    A few possible reasons:

    1. The tables are not indexed properly.

    2. You don't have enough data in your test database. Try to get the production data and run that in the test environment.

    3. You don't wait long enough. Sometimes queries take very long and it might seem like the database freezes.

    4. You run different versions of MySQL on the test server and the production server.

    5. There are different settings in the database on the test and production server.

    6. The words "year" and "month" are keywords. Using them as variable name may cause problems.

      I had a similar problem with some tables, even "browsing" the table in MyAdmin would cause the problem. Have you tried repairing the table? In my case the repair table command had to be issued through the command line as the query would not execute. Try the MyAdmin repair table, if that stalls out it may be worth trying the command line equivalent for your table type.

      In regards to key words, you can use the tilde to escape the keyword so to speak, not not have to go back and change anything that relies on it being called month.

      //causes error if month is reserved word
Select month ...
//is implied as a field/var name not a keyword and will work
Select `month` ...
        Piranha;10892292 wrote:

        A few possible reasons:

        Thanks for the reply much appreciated

        1. The tables are not indexed properly.

        Well I do do a lot of this and of course indexing would improve query performance but far far heavier queries do not kill the (dedicated) server.

        2. You don't have enough data in your test database. Try to get the production data and run that in the test environment.

        I have the full customers DB on my test server.

        3. You don't wait long enough. Sometimes queries take very long and it might seem like the database freezes.

        No no .. I hear what you are saying but I'm not a newb. Following phpMyAdmin freezing, server load slowly builds until the server dies if the process is not killed.

        4. You run different versions of MySQL on the test server and the production server.

        Yes this is true, but on my test server I run MySQL5 (already strict(er)) in strict mode, client is using MySQL4.1.20.

        5. There are different settings in the database on the test and production server.

        Yes but I don't see how this can affect matters to this level, perhaps you could advise?

        6. The words "year" and "month" are keywords. Using them as variable name may cause problems.

        Interesting .. I have come across this issue before which tends me towards me using name as standard, but I have seen year/month used before and don't really understand how it would kill a server, usually such things just kick errors.

          I am only commenting on the points that I don't think is resolved.

          Yes this is true, but on my test server I run MySQL5 (already strict(er)) in strict mode, client is using MySQL4.1.20.

          4.1.20 != 5.0.51a

          Install MySQL 4.1 on the test server to make sure that they are the same versions. It is not only how strict the server is that makes difference. Normally version trouble causes only errors, but who knows? Anyway it is always good to test against the database version of the production server to avoid query problems.

          Yes but I don't see how this can affect matters to this level, perhaps you could advise?

          I am not a wizard when it comes to database settings, but some settings might cause the database to be slow. An example is memory settings, although I don't think this is the case here.

          Interesting .. I have come across this issue before which tends me towards me using name as standard, but I have seen year/month used before and don't really understand how it would kill a server, usually such things just kick errors.

          If you don't want to test don't worry. Otherwise it is a 30 second test that will eliminate one possible problem.

          I would like to say that Protato have a good idea, I would try to repair the database.

            Write a Reply...