DROP TABLE TOOK 39MIN

[Krishna Chandra Prajapati]

Hi Experts,

I have a crm(customer resource management) table which contains 654 million
records. Dropping table took 39min. In addition to this other queries become
very slow and they are not associated with bkp_mtlog any way. why?

mysql> show table status like 'bkp_mtlog';
+---++-++---++--+-+--+-++-+-++---+--++-+
| Name  | Engine | Version | Row_format | Rows  | Avg_row_length |
Data_length  | Max_data_length | Index_length | Data_free   | Auto_increment
| Create_time | Update_time | Check_time | Collation | Checksum |
Create_options | Comment |
+---++-++---++--+-+--+-++-+-++---+--++-+
| bkp_mtlog | InnoDB |  10 | Compact| 654135647 |289 |
189507928064 |   0 | 549887164416 | 58322845696 |   NULL
| NULL| NULL| NULL   | latin1_swedish_ci | NULL |
partitioned| |
+---++-++---++--+-+--+-++-+-++---+--++-+
1 row in set (2 min 11.29 sec)

mysql> drop table bkp_mtlog;
Query OK, 0 rows affected (39 min 7.39 sec)

Thanks,
Krishna

How to enable partition in MySQL version 5.0.81-community

[Jeetendra Ranjan]

Hi,

We are using MySQL version 5.0.81-community.We need to partition few tables but 
the have_partition option is not available that means this version doesn't 
support partition feature. 

Does MySQL provide any patch to enable the feature of partition in the same 
version of MySQL or how to enable the partition option in MySQL version 
5.0.81-community?



--

Regards,
Jeetendra Ranjan


SynapseIndia
http://www.synapse.in
I.T. Outsourcing @ Peace of Mind

--

RE: Finding users who haven't posted in a week

[John Meyer]

Now I'm wondering if I can use this query in an update to set a variable in
a second table

Users
--
User_id VARCHAR(50)
. . .
User_active BITINT(1)

I want to set user_active to 0 where the user_id is in the query below.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;


-Original Message-
From: John Meyer [mailto:johnme...@pueblocomputing.com] 
Sent: Sunday, November 08, 2009 9:45 AM
To: 'Michael Dykman'
Cc: mysql@lists.mysql.com
Subject: RE: Finding users who haven't posted in a week

Thanks, morning coffee hasn't kicked in.  This worked out well.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;

I forgot when to use the where and when to use the having clause.

-Original Message-
From: Michael Dykman [mailto:mdyk...@gmail.com] 
Sent: Sunday, November 08, 2009 8:35 AM
To: John Meyer
Cc: mysql@lists.mysql.com
Subject: Re: Finding users who haven't posted in a week

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

 - michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
 wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=john.l.me...@gmail.com

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Temp file issues on Ubuntu 9.10

[Sebastiaan van Erk]

Hi,


$ ls -ld /tmp/mysql
drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql

$ ls -ld /tmpfs/mysql
drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql

So I don't see the difference

Has anyone encountered similar problems, or know what's going on here?

Best regards,
Sebastiaan



This might just be a typo, but the chown statement you gave us didn't 
have a target and so would not have affected the relevant directories:


mkdir /tmpfs
mount -t tmpfs -o size=1g tmpfs /tmpfs
mkdir /tmpfs/mysql
chown mysql:mysql
Probably should be:
chown -R mysql:mysql  /tmpfs/mysql

john


Hi,

Thanks, yes that's a typo indeed, sorry I didn't catch it before sending 
the mail. The ls output was copy-pasted from a terminal and there the 
ownerships are correct.


Regards,
Sebastiaan



smime.p7s
Description: S/MIME Cryptographic Signature

RE: Finding users who haven't posted in a week

[John Meyer]

Thanks, morning coffee hasn't kicked in.  This worked out well.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;

I forgot when to use the where and when to use the having clause.

-Original Message-
From: Michael Dykman [mailto:mdyk...@gmail.com] 
Sent: Sunday, November 08, 2009 8:35 AM
To: John Meyer
Cc: mysql@lists.mysql.com
Subject: Re: Finding users who haven't posted in a week

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

 - michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
 wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Finding users who haven't posted in a week

[Michael Dykman]

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

 - michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
 wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Temp file issues on Ubuntu 9.10

[John List]

Sebastiaan van Erk wrote:

Hi all,

I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts 
of temp file problems. For example, when I try to delete a row and 
violate a contraint I get:


ERROR 1451 (23000): Cannot delete or update a parent row: a foreign 
key constraint fails (temp file operation failed)


Instead of telling me which constraint is violated, it tells me the 
temp file creation failed. I have no reason why it failed, I don't see 
any error messages in the log.


To solve this problem I tried to make a tmpfs partition (I thought, 
maybe somehow my using ext4 might be a problem):


mkdir /tmpfs
mount -t tmpfs -o size=1g tmpfs /tmpfs
mkdir /tmpfs/mysql
chown mysql:mysql

and changed the tmpdir in the mysql config to /tmpfs/mysql

tmpdir=/tmpfs/mysql

But then mysql fails on startup:

/usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' 
(Errcode: 13)
091108 10:12:46  InnoDB: Error: unable to create temporary file; 
errno: 13

091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error.
091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE 
ENGINE failed.


I checked error code 13, which is permission denied, but I don't 
understand this, because if I change tmpdir to /tmp/mysql it does 
work, and I have:


$ ls -ld /tmp/mysql
drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql

$ ls -ld /tmpfs/mysql
drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql

So I don't see the difference

Has anyone encountered similar problems, or know what's going on here?

Best regards,
Sebastiaan



This might just be a typo, but the chown statement you gave us didn't 
have a target and so would not have affected the relevant directories:


mkdir /tmpfs
mount -t tmpfs -o size=1g tmpfs /tmpfs
mkdir /tmpfs/mysql
chown mysql:mysql 


Probably should be:
chown -R mysql:mysql  /tmpfs/mysql

john


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Finding users who haven't posted in a week

[John Meyer]

I want to get a list of all users who haven't posted in a week. But when I
use the following function.

 

select user_id, max(tweet_createdat) from tweets where
datediff(now(),max(tweet_createdat)) > 7;

 

 

Is producing the error:

 

Invalid use of group function

 

 

Re: Find neighboring rows

[Jaime Crespo Rincón]

2009/11/8 Andrew Tran :
> I want to find the first occurrence of "Andrew", but also the
> neighboring rows (like the two rows above the first "Andrew" and the

There is a conceptual issue with what you are trying to accomplish.
What are "the rows above" and "below"?
According to relational theory, entities are grouped into sets, and
thus, without any implicit order.
So, if you want them by alphabetic order, it is ok, if you expect them
"in the order you inserted them", you should explicit this through an
additional timestamp or auto_increment column, as records are not
guaranteed to be returned in that order.

Let's suppose that you want them on natural order, your query could be
done with something like this:
SET @name = 'whatever';
SELECT n
  FROM names
  WHERE n >= @name
  ORDER BY n ASC
  LIMIT 3
) UNION ALL (
SELECT n
  FROM names
  WHERE n < @name
  ORDER BY n DESC
  LIMIT 2
) ORDER BY n;

Row has been named n; table: names. Unexpected results if @name does not exist.

If you expect a lot of records, expect also bad performance with this
query: add an index to speed up the orderings; also several
performance optimizations could be applied depending on your case.

-- 
Jaime Crespo
MySQL & Java Instructor
Warp Networks


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Find neighboring rows

[Andrew Tran]

Let's say I have a single column table like this:

Name
--
Sam
Daniel
Artur
Andrew
Mark
Thomas
Ross
Andrew
Michelle
Adrian
Diane

I want to find the first occurrence of "Andrew", but also the
neighboring rows (like the two rows above the first "Andrew" and the
two rows below the first "Andrew"). This is an example of what I'd
like the query to return:

Daniel
Artur
Andrew
Mark
Thomas

Please keep in mind this is just a demo to illustrate my question;
this is not a real table example!
Thanks a lot!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Temp file issues on Ubuntu 9.10

[Sebastiaan van Erk]

Hi all,

I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts of 
temp file problems. For example, when I try to delete a row and violate 
a contraint I get:


ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (temp file operation failed)


Instead of telling me which constraint is violated, it tells me the temp 
file creation failed. I have no reason why it failed, I don't see any 
error messages in the log.


To solve this problem I tried to make a tmpfs partition (I thought, 
maybe somehow my using ext4 might be a problem):


mkdir /tmpfs
mount -t tmpfs -o size=1g tmpfs /tmpfs
mkdir /tmpfs/mysql
chown mysql:mysql

and changed the tmpdir in the mysql config to /tmpfs/mysql

tmpdir=/tmpfs/mysql

But then mysql fails on startup:

/usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' 
(Errcode: 13)

091108 10:12:46  InnoDB: Error: unable to create temporary file; errno: 13
091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error.
091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE 
failed.


I checked error code 13, which is permission denied, but I don't 
understand this, because if I change tmpdir to /tmp/mysql it does work, 
and I have:


$ ls -ld /tmp/mysql
drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql

$ ls -ld /tmpfs/mysql
drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql

So I don't see the difference

Has anyone encountered similar problems, or know what's going on here?

Best regards,
Sebastiaan



smime.p7s
Description: S/MIME Cryptographic Signature