gmane.comp.db.mysql.general

Query weirdness...

Don Wieland — 2012-05-25T19:58:42

I am hoping someone can bail me out on why this query in not working.

In the queries below:

query 1 returns multiple rows - one per client.

in the rows where:

client_id is 254240, dr_all_ther_qty = 1
client_id is 253821, dr_all_ther_qty = 1
client_id is 254023, dr_all_ther_qty = 6

But when I break out the queries into their own queries (queries  
2,3,4), I get different result (WHICH are ACCURATE - the ones I need):

query 2 - dr_all_ther_qty = 0
query 3 - dr_all_ther_qty = 0
query 4 - dr_all_ther_qty = 5

Clueless on why these DO NOT reconcile. I really need query 1 results  
to be the same results as queries 2,3, and 4. Little help!

--- start query 1 ---

SELECT q1.* FROM

(SELECT
apt.user_id,
apt.client_id,
c.last_name,
c.first_name,
MIN(apt.time_start) AS stime,
FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m-%d') AS ftime,
(SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id =  
tl_appt.room_id
WHERE client_id = apt.client_id AND r.location_id = '1' AND  
appt_status_id = '3' AND time_start < MIN(apt.time_start)) AS previous,
(SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id =  
tl_appt.room_id WHERE client_id = apt.client_id AND  r.location_id =  
'1' AND user_id = apt.user_id AND appt_status_id = '3' AND time_start  
 > '1293858000') AS dr_all_ther_qty

FROM tl_appt apt
LEFT JOIN tl_rooms r on r.room_id = apt.room_id
LEFT JOIN tl_clients c on c.client_id = apt.client_id
LEFT JOIN tl_users u on u.user_id = apt.user_id

WHERE
apt.appt_id IS NOT NULL AND
apt.time_start between '1293858000' and '1325393999' AND
apt.appt_status_id = '3' AND
apt.user_id = '506' and
r.location_id = '1'

GROUP BY apt.user_id, apt.client_id
ORDER BY u.last_name, u.first_name, c.last_name, c.first_name) as q1

WHERE q1.previous = 0

--- end query 1 ---


--- start query 2 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r  
on r.room_id = tl_appt.room_id WHERE client_id = '254240' AND  
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND  
time_start > '1324927800'
--- end query 2 ---

--- start query 3 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r  
on r.room_id = tl_appt.room_id WHERE client_id = '253821' AND  
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND  
time_start > '1318617000'
--- end query 3 ---

--- start query 4 ---
SELECT count(*) AS dr_all_ther_qty FROM tl_appt LEFT JOIN tl_rooms r  
on r.room_id = tl_appt.room_id WHERE client_id = '254023' AND  
r.location_id = '1' AND user_id = '506' AND appt_status_id = '3' AND  
time_start > '1321903800'
--- end query 4 ---


Don Wieland
D W   D a t a   C o n c e p t s

ANN: Hopper for MySQL, first public beta available!

Martijn Tonies — 2012-05-25T10:44:54

Dear ladies and gentlemen,

Upscene Productions is happy to announce the launch of a new
database-developer tool:
"Hopper" (public beta 1)

Hopper is a Stored Routine Debugger, the first beta for MySQL
is now available.


For more information, see 
http://www.upscene.com/displaynews.php?item=20120525


With regards,

Martijn Tonies

Upscene Productions
http://www.upscene.com

category with parentid

HaidarPesebe — 2012-05-25T07:57:46

I have a database for the category

id | name   | parentid
----------------------
1 | cat A      | 0
2 | cat B      | 0
3 | subcat A | 1
4 | subcat A | 1
5 | subncat B | 2
-------------------------

I want to display the result like this:

1. Cat A
      - Subcat A
      - Subcat A
2. CatB
      - Subcat B

ask for help for this.
Thanks for the help



__________ Information from ESET NOD32 Antivirus, version of virus signature database 7165 (20120524) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Architectural Help

Anupam Karmarkar — 2012-05-24T09:17:12

Hi All,


I need architectural help for our requirement, 


We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server.  Application consists of write/read/delete operations, 


Current writes each day central server 1million. 


Only 1/1000 need to be distrubuted acrross servce rest need to be in central server.


How can we achive this ? solution needs very much real time data accepting nework lags. 


Solution

Collect all changes in other 49 server into 1 central server(How can we collect data)


49 keeps updating data into local database from central server(Using Repliation Can be done) 



--Anupam

Need help for performance tuning with Mysql

Yu Watanabe — 2012-05-23T02:07:08

Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU    : Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS     : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt                  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf. 

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read "Hiperformance Mysql vol.2" ,
and increased the following values, 

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date       Time      CPU%  RSS     VSZ       
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
            sys  5%
            io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to 
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

Query assistance...

Don Wieland — 2012-05-21T16:17:58

I have got this query that returns the correct rows I want to display,  
BUT the columns dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are  
not calculating correctly:

--- START QUERY ONE ---

SELECT q1.* FROM

(SELECT  apt.user_id, apt.client_id, c.last_name, c.first_name,  
MIN(apt.time_start) AS stime, FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m- 
%d') AS ftime,

(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND  
appt_status_id = '3' AND time_start < apt.time_start) AS previous,

(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND  
user_id = apt.user_id AND appt_status_id = '3' AND  
FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and  
'2012-05-20') AS dr_ther_qty,

(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND  
user_id != apt.user_id AND appt_status_id = '3' AND  
FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and  
'2012-05-20') AS dr_not_ther_qty,

(SELECT DISTINCT count(*) FROM tl_appt WHERE client_id = apt.client_id  
AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d')  
between '2012-01-01' and '2012-05-20') AS dr_all_ther_qty

FROM tl_appt apt

LEFT JOIN tl_rooms r on r.room_id = apt.room_id
LEFT JOIN tl_clients c on c.client_id = apt.client_id
LEFT JOIN tl_users u on u.user_id = apt.user_id

WHERE apt.appt_id IS NOT NULL AND FROM_UNIXTIME(apt.time_start,'%Y-%m- 
%d') between '2012-01-01' and '2012-05-20' AND apt.appt_status_id =  
'3' and r.location_id = '2' and apt.user_id IN (14, 503)
GROUP BY apt.user_id, apt.client_id
ORDER BY u.last_name, u.first_name, c.last_name, c.first_name) as q1

WHERE q1.previous > 0;

--- END QUERY ONE ---

The totals of dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are  
not the same if I broke them out into separate queries:

I think it is something to do with the GROUP BY - it is multiplying  
rows. Basically, the rows are correct and I want to use the user_id  
and client_id to calculate the SUB-SELECTS.

Can someone explain why when I run in the MAIN query I get this:

dr_ther_qty = 25
dr_not_ther_qty = 22
dr_all_ther_qty = 47

BUT when I break out that client into the separate queries, I get

SELECT count(*) as dr_ther_qty FROM tl_appt WHERE client_id = 161 AND  
user_id = 503 AND appt_status_id = '3' AND  
FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and  
'2012-05-20';

dr_ther_qty = 6

SELECT count(*) as dr_not_ther_qty FROM tl_appt WHERE client_id = 161  
AND user_id != 503 AND appt_status_id = '3' AND  
FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and  
'2012-05-20';

dr_not_ther_qty = 2

SELECT count(*) as dr_all_ther_qty FROM tl_appt WHERE client_id = 161  
AND appt_status_id = '3' AND FROM_UNIXTIME(time_start,'%Y-%m-%d')  
between '2012-01-01' and '2012-05-20';

dr_all_ther_qty = 8


I appreciate any enlightenment on this. Thanks!

Don

Reducing ibdata1 file size

Manivannan S. — 2012-05-21T13:03:46

Hi ,

                I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file.

How to reduce the ibdata1 file size in both LINUX and WINDOWS machine.

Do you have any idea how to solve this problem. Thanks for any feedback.



Thanks
Manivannan S

DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law.  If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailadmin< at >spanservices.com and destroy the original message.  Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN.

[Warning] Aborted connection...... (Got timeout reading communication packets)

Shafi AHMED — 2012-05-21T07:45:09

Ladies and Gentlemen:

I am getting below errors and therefore the user sessions terminate causing
business impact...Can some one who is expertise already in this advice at
the earliest?


120513  8:19:45 [Warning] Aborted connection 1167257 to db: 'iib' user:
'iibuser' host: '210.18.3.94' (Got timeout reading communication packets)


OS version: RHEL 5.3

DB version: MYSQL 5.1 

Table involved in the DB is of type : inndoDB

Background : This is an online exam registration site DB and the concurrent
connex invariably reaches to 200 for 500 users which should not be the case.

Ideally the concurrent connex must be <10.

Normally , we run truncate table before the exam starts up.

A similar setup(in terms of DB/OS/config etc )  works fine which is actually
DR at different site.

PS: Network segment between web and DB tier has been thoroughly checked and
seems to be fine.

Thanks a ton!

 
Best Rgs,
    Shafi AHMED
    Sify - Chennai

 
 




Get your world in your inbox!

Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id!
Log on to http://www.sify.com

********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to 
Sify Technologies Limited and is intended for use only by the individual or entity to 
which it is addressed, and may contain information that is privileged, 
confidential or exempt from disclosure under applicable law. If this is a 
forwarded message, the content of this E-MAIL may not have been sent with 
the authority of the Company. If you are not the intended recipient, an 
agent of the intended recipient or a  person responsible for delivering the 
information to the named recipient,  you are notified that any use, 
distribution, transmission, printing, copying or dissemination of this 
information in any way or in any manner is strictly prohibited. If you have 
received this communication in error, please delete this mail & notify us 
immediately at admin< at >sifycorp.com

SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

Daevid Vincent — 2012-05-19T00:33:58

I have a table of DVDs, another of scenes and a last one of encoding
formats/files...

I want to find in one query all the dvd_id that have > 0 scene_id that's
encoded in format_id = 13.
In other words all DVDs that are format_id = 13 despite not having a direct
link.

CREATE TABLE `dvds` (
  `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
  `dvd_title` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`dvd_id`),
)

CREATE TABLE `scenes_list` (
  `scene_id` int(11) NOT NULL auto_increment,
  `dvd_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  PRIMARY KEY  (`scene_id`),
)

CREATE TABLE `moviefiles` (
  `scene_id` int(11) NOT NULL default '0',
  `format_id` int(3) NOT NULL default '0',
  `filename` varchar(255),
  `volume` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`scene_id`,`format_id`),
)

create alias for columns bound to database?

D. Dante Lorenso — 2012-05-18T21:21:59

All,

I'd like to be able to create column aliases which are bound to the 
database itself.  I have an assortment of columns without naming 
standards which i'd like to migrate to a better naming scheme.

Is there a feature in MySQL that would allow me to give a database 
column multiple names?  I'm thinking that for SELECT * statements, you 
would use the default column name, but for insert, delete, update, etc, 
it would be fine to use the aliased name or default.

Doing this would *really* help to allow me to migrate the database to 
the new naming convention without breaking existing code.  I would then 
be able to refactor at a more leisurely pace.

Does the feature exist, or can it be created?

ATT: List OWNER/MODERATOR

Noel Butler — 2012-05-18T00:51:04

List is broken, in many ways.

Amongst many of its failures, the two current biggest is:

1/ This lists bounce messages are clueless  they do not include the
reject reason

2/ mysql-owner address STILL points to a sun address which of course is
rejected as they no longer host this list.


*sigh*

Query help,,,

Don Wieland — 2012-05-17T14:37:01

Hi folks,

I am trying to compile a query that does statistics on appointments  
based on specific criteria. Here is my starting query:

SELECT
     u.user_id,
     c.client_id,
     c.first_name,
     c.last_name,
     a.time_start AS stime,
     FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted

  FROM tl_appt a
   LEFT JOIN tl_users u ON a.user_id = u.user_id
   LEFT JOIN tl_clients c ON a.client_id = c.client_id
   LEFT JOIN tl_rooms r on a.room_id = r.room_id

   WHERE a.appt_id IS NOT NULL AND FROM_UNIXTIME(a.time_start,'%Y-%m- 
%d') between '2011-05-01' and '2011-12-31' and r.location_id = '2' and  
a.user_id IN (14) ORDER BY u.last_name, u.first_name, c.last_name,  
c.first_name

This will return a set of rows where a client may have MORE THEN ONE  
appointment. From this set I need to narrow more:

1) Only display the first appointment PER Client. (there will be no  
duplicate client_id)

Then once I have that set of rows established, I need to query for two  
more result:

1) Show New Customers = those where the COUNT of appointments (in the  
entire tl_appt table) LESS THAN the stime = 0

2) Show FORMER Customers = those where the COUNT of appointments (in  
the entire tl_appt table) LESS THAN the stime > 0

I am sure I need a multiple select query, but I am having a hard time  
wrapping my head around it.

Thanks for any feedback.

Don

Foreign key and uppercase / lowercase values

GF — 2012-05-16T07:54:09

Good morning,
I have an application where the user ids were stored lowercase.
Some batch import, in the user table some users stored  a uppercase
id, and for some applicative logic, in other tables that have a
foreign key to the user table, their user ids are stored lowercase.
MySQL didn't throw any error probalby because the collation used is
"case insensitive".
My problem is that the application is Java and java strings are case
sensitive, so now I want to set user ids to lowercase EVERYWHERE.

I supposed that I could execute with ease these commands:
- update mytable1 set USER_ID = LOWER(USER_ID);
- update mytable2 set USER_ID = LOWER(USER_ID);
- update mytable3 set USER_ID = LOWER(USER_ID);

But for some tables I got some Foreign key constraint to throw an
error. (butwhy they didn't throw an error on the insert but just on
the update???)
And if I try to disable foreign key checks during these updates, I get
some "duplicate key" errors where USER_ID is a part of composite key
with other columns. (but I don't have any data that might cause a real
duplicate key error just changing the case of one column)

Have you any idea how to solve this situation without
stopping/recreating the DB? (it's a production environment)
Thanks

Seu imóvel precisa estar sempre em perfeitas condições na aparência, segurança e conforto?

Sena Engenharia — 2012-05-15T20:32:36

Seu cliente de e-mail não pode ler este e-mail.
Para visualizá-lo on-line, por favor, clique aqui:
http://www.engsena.com.br/market/display.php?M=88578&C=a422ea16f0fc983e666748f74186ae45&S=9&L=7&N=1


Para parar de receber nossos
Emails:http://www.engsena.com.br/market/unsubscribe.php?M=88578&C=a422ea16f0fc983e666748f74186ae45&L=7&N=9

E-Marketer

Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases.

Pothanaboyina Trimurthy — 2012-05-15T11:57:43

hi every one

  Is there any performance difference, maintaining separate ibdata
files for each and every table insted of having one singl tabale for
all databases, for InnoDB Storage Engine.

please let me know the difference.

Performance question

Nicolas Rannou — 2012-05-14T21:05:26

Hi all,

We are currently designing a database for our application (python/mysql)
and we have some performance concern:

We would have "users" and "images".
"users" can view some "images".
"images" can be viewed by several "users".
(n to m mapping)

Which would be most efficient practice (regarding speed)?
We want to figure the most efficient way to get the images that a user see?
We want to handle 10 000 users and 100 000 images.

*1*  to create 3 tables:*

user - info about a user
images - info about an image
user_image_mapping
 -> one row per association:
 user 1 -> image 22
 user 1 -> image 8888
 user 2 -> image 567
 user 3 -> image 888
 user 3 -> image 44453

*2* to create 2 tables*

user - info about a user
 -> a field would contain a list which represents the ids of the images
the user can look at?
images - info about an image

Any suggestion would be greatly appreciated,

Best,
Nicolas

ANN: 70% Ten Year Anniversary discount on all our products!

Martijn Tonies — 2012-05-14T13:16:15

Upscene Productions is celebrating it's 10 year anniversary with a massive 
discount on all our products: 70% discount until the end of May.

Don't forgot to blog and twitter about this!

We produce database development, management and testing tools for:
* Oracle
* Microsoft SQL Server
* MySQL
* InterBase
* Firebird
* SQL Anywhere
* NexusDB
* Advantage Database
* Generic connectivity tools for ADO and ODBC

These include test data generator tools, database design and development 
tools, auditing tools, a dbExpress driver for Firebird, debugging tools, 
performance analysis tools.

Coupon code TENYEARS will get you this discount, check 
http://www.upscene.com for more information.

MySQL 5.1.59 - slow_log purge problem.

Rafał Radecki — 2012-05-14T09:48:52

Hi all.

I write a script to delete rows from slow_log older than 2 weeks.

#!/bin/bash

if [ $# -ne 1 ]; then
echo "Usage: $0 mysql_config_file"
exit 1
fi

SELECTQUERY="select * from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
DELETEQUERY="delete from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
CONFIG_FILE="$1"
HOSTNAME="$(hostname | awk -F'.' '{print $1}')"
INSTANCENAME="$(grep datadir $CONFIG_FILE | awk -F'/' '{print $4}'|uniq)"
LOG_FILE="/var/log/${INSTANCENAME}-${HOSTNAME}-slowlog-clean.log"

echo "***" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Started cleaning..." >> $LOG_FILE
echo "Before there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
mysql --defaults-file=${CONFIG_FILE} mysql -e \"$DELETEQUERY\" > /dev/null
2>&1
echo "After there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Stopped cleaning..." >> $LOG_FILE
echo "***" >> $LOG_FILE

When I issue the delete statement I get:
mysql --defaults-file=/etc/my.cnf mysql -e "delete from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
ERROR 1556 (HY000) at line 1: You can't use locks with log tables.

mysql --defaults-file=/etc/my.cnf mysql -e "select *  from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 4 WEEK) limit 1"
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+
| start_time          | user_host                 | query_time | lock_time
| rows_sent | rows_examined | db | last_insert_id | insert_id | server_id |
sql_text        |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+
| 2011-10-09 23:06:17 | root[root] < at > localhost [] | 00:00:06   | 00:00:00
 |         1 |             0 |    |              0 |         0 |        72
| select sleep(6) |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+

Above select works fine.

How can I resolve the error? What is the proper way to clean slow_log?

Best regards,
Rafal Radecki.

Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

Andrés Tello — 2012-05-12T14:08:28

While doning a batch process...

show full processlist show:

| 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |    6 |
end                          | update `account` set `balance`= 0.00 +
'-4000' where accountid='2583092'

No other process, lo locking no nothing...

so you take this same query... run it isolated, and the mu....fu... is
just...f.... fast!!!!!


update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


ARRRG!  I have seen this type of query take as long as 100+ seconds.. and I
don't have a F*** clue...

MySQL slowlog - only in file?

Rafał Radecki — 2012-05-11T09:10:11

Hi all.

Is there a possibility to see the info from slowlog somewhere in database?
I would like to see slow queries using mysql and not by watching the log
file.
I've searched on google and mysql website but hasn't found the solution.

Best regards,
Rafal Radecki.

drop partitions

louis liu — 2012-05-11T08:34:41

Hi all

    last night  we droped some partitions and we found that the first drop
costs about 30mins  and then it's about 3 seconds.

when droping the partition  all processes are shown waiting for opening
table like:


+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host              | db            | Command     |
Time    | State                                                          |
Info
                          |
+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 4656803 | bkdloguser  | 10.3.0.116:48642  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656804 | bkdloguser  | 10.3.0.116:48643  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656805 | bkdloguser  | 10.3.0.116:48644  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656806 | bkdloguser  | 10.3.0.116:48645  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656807 | bkdloguser  | 10.3.0.116:48646  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656808 | bkdloguser  | 10.3.0.116:48647  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656809 | bkdloguser  | 10.3.0.116:48648  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656810 | bkdloguser  | 10.3.0.116:48649  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656811 | bkdloguser  | 10.3.0.116:48650  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |


whole instance hangs and  all databases are waiting until we completely
drop partitions


we create this table as:


CREATE TABLE `centralmobile_logs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `trader_name` VARCHAR(25) DEFAULT NULL,
  `trader_password` VARCHAR(128) DEFAULT NULL,
  `client_system` VARCHAR(20) DEFAULT NULL,
  `client_version` VARCHAR(20) DEFAULT NULL,
  `protocol` VARCHAR(20) DEFAULT NULL,
  `interface_version` VARCHAR(20) DEFAULT NULL,
  `call_interface_name` VARCHAR(200) DEFAULT NULL,
  `user_token` VARCHAR(128) DEFAULT NULL,
  `client_IP` VARCHAR(60) DEFAULT NULL,
  `server_IP` VARCHAR(128) DEFAULT NULL,
  `create_time` DATETIME NOT NULL,
  `request_URL` LONGTEXT,
  `cookie` TEXT,
  `request_head` TEXT,
  `requestBody` LONGTEXT,
  `responseHead` TEXT,
  `responseBody` LONGTEXT,
  `hasException` VARCHAR(10) NOT NULL,
  `exceptionString` LONGTEXT,
  `useTimes` BIGINT(20) DEFAULT NULL,
  `ifOrderAmount` VARCHAR(20) DEFAULT NULL,
  `ifOrderCode` VARCHAR(30) DEFAULT NULL,
  `clientTelnetPhone` VARCHAR(60) DEFAULT NULL,
  `clientAppVersion` VARCHAR(20) DEFAULT NULL,
  `provinceId` VARCHAR(10) DEFAULT NULL,
  `searchkeywords` VARCHAR(60) DEFAULT NULL,
  `netnotekeywords` VARCHAR(800) DEFAULT NULL,
  `unionKey` VARCHAR(20) DEFAULT NULL,
  `longitude` DOUBLE DEFAULT NULL,
  `latitude` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`id`,`create_time`),
  KEY `trader_name` (`trader_name`),
  KEY `createtime` (`create_time`),
  KEY `usertimes` (`useTimes`),
  KEY `callinterfacename` (`call_interface_name`),
  KEY `clientip` (`client_IP`),
  KEY `token` (`user_token`)
) ENGINE=MYISAM AUTO_INCREMENT=247057481 DEFAULT CHARSET=utf8 CHECKSUM=1
DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (to_days(create_time))
(PARTITION p1101 VALUES LESS THAN (734534) ENGINE = MyISAM,
 PARTITION p1102 VALUES LESS THAN (734562) ENGINE = MyISAM,
 PARTITION p1103 VALUES LESS THAN (734593) ENGINE = MyISAM,
 PARTITION p1104 VALUES LESS THAN (734623) ENGINE = MyISAM,
 PARTITION p1105 VALUES LESS THAN (734654) ENGINE = MyISAM,
 PARTITION p1106 VALUES LESS THAN (734684) ENGINE = MyISAM,
 PARTITION p1107 VALUES LESS THAN (734715) ENGINE = MyISAM,
 PARTITION p1204 VALUES LESS THAN (734989) ENGINE = MyISAM,
 PARTITION p1205 VALUES LESS THAN (735020) ENGINE = MyISAM,
 PARTITION p1206 VALUES LESS THAN (735050) ENGINE = MyISAM,
 PARTITION p1207 VALUES LESS THAN (735081) ENGINE = MyISAM) */




so  why first drop costs so long time  and why all  database are waiting
for opening table ?




cheers