hello,

I try to execute this sql in mysql shell,but got error as:

mysql> select * from (select uin,count(*) as dd from active_users where
date >= date_add(curdate(),interval -30 day) group by uin) where dd >=3;

ERROR 1248 (42000): Every derived table must have its own alias

But I can execute the sql statement of "select uin,count(*) as dd from
active_users where date >= date_add(curdate(),interval -30 day) group by
uin" successfully.

How to fixup it? thanks!


Get the name you always wanted with the new y7mail email address.
www.yahoo7.com.au/y7mail

tech user wrote:
> hello,
>
> I try to execute this sql in mysql shell,but got error as:
>
> mysql> select * from (select uin,count(*) as dd from active_users where
> date >= date_add(curdate(),interval -30 day) group by uin) where dd >=3;
>
> ERROR 1248 (42000): Every derived table must have its own alias
>
> But I can execute the sql statement of "select uin,count(*) as dd from
> active_users where date >= date_add(curdate(),interval -30 day) group by
> uin" successfully.
>
> How to fixup it? thanks!
>
>
> Get the name you always wanted with the new y7mail email address.
> www.yahoo7.com.au/y7mail
>
>
>
>
>


Add an alias for the subquery

select * from ( select .... ) my_alias where dd >= 3;

Better, use a having clause and eliminate the subquery. Odds are it
will be more efficient in MySQL.

>
> Add an alias for the subquery
>
> select * from ( select .... ) my_alias where dd >= 3;
>
> Better, use a having clause and eliminate the subquery. Odds are it
> will be more efficient in MySQL.
>

How to replace the original one with a having statement?
Thanks again.


Get the name you always wanted with the new y7mail email address.
www.yahoo7.com.au/y7mail

tech user wrote:
>> Add an alias for the subquery
>>
>> select * from ( select .... ) my_alias where dd >= 3;
>>
>> Better, use a having clause and eliminate the subquery. Odds are it
>> will be more efficient in MySQL.
>>
>>
>
> How to replace the original one with a having statement?
> Thanks again.
>

select uin,count(*) as dd from active_users
where date >= date_add(curdate(),interval -30 day)
group by uin
having count(*) >=3;

Hi...
I need test mysql connections but i have a problem...

freebsd# uname -a
FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan
16 04:18:52 UTC 2008 root@dessler.cse.buffalo.edu
<mailto:root@dessler.cse.buffalo.edu>:/usr/obj/usr/src/sys/GENERIC i386
freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2
freebsd# more /var/db/mysql/my.cnf
[CLIENT]
port = 3306
socket = /tmp/mysql.sock
[MYSQLD]
port = 3306
ndbcluster
socket = /tmp/mysql.sock
query_cache_type = 1
query_cache_size = 26214400
ndb-connectstring=192.168.6.1
max_user_connections = 500
max_connections = 3000
max_connect_errors = 10
table_cache = 2048
thread_cache_size = 500
# log-bin=/usr/local/etc/mysql/log-binario.txt
[MYSQL_CLUSTER]
ndb-connectstring=192.168.6.1
freebsd#

on the script i have...

cnt=0
while true; do
query="insert into Mytable values('99','`date +%d-%H:%M`','$cnt');"
/usr/local/bin/mysql -u root -ppasswd -e "$query" MYDB >/dev/null
if [ $cnt -eq 999999 ];then
break
else
cnt=`expr $cnt "+" 1`
fi
done


but it return error on the screen like and this its my PROBLEM! I would
like that all connections finish ok. The errors not apear on mysql_error
file.

: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
ERROR 2002 (HY000)ERROR: Can't connect to local MySQL server through
socket '/tmp/mysql.sock' (61)


The file mysql.sock exist...
freebsd# ll /tmp
total 10
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .ICE-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .X11-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .XIM-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .font-unix
drwxrwxr-x 2 root operator 512 Mar 10 06:44 .snap
srwxrwxrwx 1 mysql wheel 0 Apr 2 16:04 mysql.sock
freebsd#

i change the owner but nothing
freebsd# ll /tmp/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Apr 2 16:04 /tmp/mysql.sock
freebsd#

the Mysql its up
freebsd# ps awx | grep mysqld
25121 p2 S 1:21.87 [mysqld]
freebsd#

When i connect to mysql it show me error
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)

but i try again and i in


mysql> show status like 'Thre%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 94 |
| Threads_connected | 26 |
| Threads_created | 120 |
| Threads_running | 3 |
+-------------------+-------+
4 rows in set (0.14 sec)

mysql>

--
================================================== ==
Ing. Vidal Garza Tirado
Depto. Sistemas
Aduanet S.A. de C.V.
Tel. (867)711-5850 ext. 4346, Fax (867)711-5855.
Ave. César López de Lara No. 3603 Int. B Col Jardín.
Nuevo Laredo, Tamaulipas, México.



--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
For all your IT requirements visit: http://www.aduanet.net

Just try mysqladmin ping that whether mysql is working or not

Krishna

On Thu, Apr 3, 2008 at 5:45 AM, Vidal Garza <vgarza@aduanet.net> wrote:

> Hi...
> I need test mysql connections but i have a problem...
>
> freebsd# uname -a
> FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan 16
> 04:18:52 UTC 2008 root@dessler.cse.buffalo.edu <mailto:
> root@dessler.cse.buffalo.edu>:/usr/obj/usr/src/sys/GENERIC i386
> freebsd# mysql --version
> mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2
> freebsd# more /var/db/mysql/my.cnf
> [CLIENT]
> port = 3306
> socket = /tmp/mysql.sock
> [MYSQLD]
> port = 3306
> ndbcluster
> socket = /tmp/mysql.sock
> query_cache_type = 1
> query_cache_size = 26214400
> ndb-connectstring=192.168.6.1
> max_user_connections = 500
> max_connections = 3000
> max_connect_errors = 10
> table_cache = 2048
> thread_cache_size = 500
> # log-bin=/usr/local/etc/mysql/log-binario.txt
> [MYSQL_CLUSTER]
> ndb-connectstring=192.168.6.1
> freebsd#
>
> on the script i have...
>
> cnt=0
> while true; do
> query="insert into Mytable values('99','`date +%d-%H:%M`','$cnt');"
> /usr/local/bin/mysql -u root -ppasswd -e "$query" MYDB >/dev/null
> if [ $cnt -eq 999999 ];then
> break
> else
> cnt=`expr $cnt "+" 1`
> fi
> done
>
>
> but it return error on the screen like and this its my PROBLEM! I would
> like that all connections finish ok. The errors not apear on mysql_error
> file.
>
> : Can't connect to local MySQL server through socket '/tmp/mysql.sock'
> (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
> through socket '/tmp/mysql.sock' (61)
> : Can't connect to local MySQL server through socket '/tmp/mysql.sock'
> (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
> through socket '/tmp/mysql.sock' (61)
> : Can't connect to local MySQL server through socket '/tmp/mysql.sock'
> (61)
> ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
> through socket '/tmp/mysql.sock' (61)
> : Can't connect to local MySQL server through socket '/tmp/mysql.sock'
> (61)
> ERROR 2002 (HY000)ERROR: Can't connect to local MySQL server through
> socket '/tmp/mysql.sock' (61)
>
>
> The file mysql.sock exist...
> freebsd# ll /tmp
> total 10
> drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .ICE-unix
> drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .X11-unix
> drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .XIM-unix
> drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .font-unix
> drwxrwxr-x 2 root operator 512 Mar 10 06:44 .snap
> srwxrwxrwx 1 mysql wheel 0 Apr 2 16:04 mysql.sock
> freebsd#
>
> i change the owner but nothing
> freebsd# ll /tmp/mysql.sock
> srwxrwxrwx 1 mysql mysql 0 Apr 2 16:04 /tmp/mysql.sock
> freebsd#
>
> the Mysql its up
> freebsd# ps awx | grep mysqld
> 25121 p2 S 1:21.87 [mysqld]
> freebsd#
>
> When i connect to mysql it show me error
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
>
> but i try again and i in
>
>
> mysql> show status like 'Thre%';
> +-------------------+-------+
> | Variable_name | Value |
> +-------------------+-------+
> | Threads_cached | 94 |
> | Threads_connected | 26 |
> | Threads_created | 120 |
> | Threads_running | 3 |
> +-------------------+-------+
> 4 rows in set (0.14 sec)
>
> mysql>
>
> --
> ================================================== ==
> Ing. Vidal Garza Tirado
> Depto. Sistemas
> Aduanet S.A. de C.V.
> Tel. (867)711-5850 ext. 4346, Fax (867)711-5855.
> Ave. César López de Lara No. 3603 Int. B Col Jardín.
> Nuevo Laredo, Tamaulipas, México.
>
>
> --
> Este mensaje ha sido analizado por MailScanner
> en busca de virus y otros contenidos peligrosos,
> y se considera que está limpio.
> For all your IT requirements visit: http://www.aduanet.net
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=p...tikc@gmail.com
>
>


--
Krishna Chandra Prajapati
MySQL DBA,

Email-id: prajapatikc@gmail.com

Hi Vidal, all !


Vidal Garza wrote:
> Hi...
> I need test mysql connections but i have a problem...
>
> freebsd# uname -a
> FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan
> 16 04:18:52 UTC 2008 root@dessler.cse.buffalo.edu
> <mailto:root@dessler.cse.buffalo.edu>:/usr/obj/usr/src/sys/GENERIC i386
> freebsd# mysql --version
> mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2
> [[...]]
>
>
> but it return error on the screen like and this its my PROBLEM! I would
> like that all connections finish ok. The errors not apear on mysql_error
> file.
>
> : Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (61)
> [[...]]

That 61 is not for decorative purposes, it has a significance.

Search your system header files for the error number 61, it should be
ECONNREFUSED ("Connection refused").

Then, check your FreeBSD documentation for the possible reasons of this
error number. This might give you a hint. On the machine I can access
(FreeBSD 6.0), "man connect" contains this:

ERRORS
The connect() system call fails if:
[[...]]
[ECONNREFUSED] The attempt to connect was forcefully rejected.

I did not check other calls whether they might return ECONNREFUSED.

At the moment, I am at a loss what might cause this reply.
Sadly, Stevens ("Advanced Programming in the Unix Environment") does not
describe it, rather refers to his "Unix Network Programming" (which I do
not have).
I propose you consult Google or some other search engine.


Your attempt to change the socket owner was bound to fail, IMO, because
a permission problem would have produced a different error number
(typically, EACCESS).


HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Krishna Chandra Prajapati escribió:
> Just try mysqladmin ping that whether mysql is working or not
>
> Krishna
>
> On Thu, Apr 3, 2008 at 5:45 AM, Vidal Garza <vgarza@aduanet.net> wrote:
>
>
>> Hi...
>> I need test mysql connections but i have a problem...
>>
>> freebsd# uname -a
>> FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan 16
>> 04:18:52 UTC 2008 root@dessler.cse.buffalo.edu <mailto:
>> root@dessler.cse.buffalo.edu>:/usr/obj/usr/src/sys/GENERIC i386
>> freebsd# mysql --version
>> mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2
>
I read, the connect information, but i dont find the answer...
I change the connection metod script.
I set

#!/bin/sh
....
/usr/local/bin/mysql -h 127.0.0.1 -u root -pmypassword MYDB -e "$query"
....

the -h option but i have a another message error...

....
....
ERRORERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60)
ERROR 2003 (HY000) 2003 (HY000): Can't connect to MySQL server on
'127.0.0.1' (60)
ERROR: Can't connect to MySQL server on '127.0.0.1' (60)
2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60)
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60)
ERRORERRORERRORERROR 2003 (HY000) 2003 (HY000) 2003 (HY000) 2003
(HY000): Can't connect to MySQL server on '127.0.0.1' (60)
: Can't connect to MySQL server on '127.0.0.1' (60)
....

There arent message on the systems logs and mysql logs...

With a shell i test mysql with ping
....
[ Thu Apr 3 11:11:53 CST 2008 ] mysqld is alive
[ Thu Apr 3 11:12:35 CST 2008 ] mysqld is alive
....

I have a cuestion, where do you find the system header files error
number (61)?



--
================================================== ==
Ing. Vidal Garza Tirado
Depto. Sistemas
Aduanet S.A. de C.V.
Tel. (867)711-5850 ext. 4346, Fax (867)711-5855.
Ave. César López de Lara No. 3603 Int. B Col Jardín.
Nuevo Laredo, Tamaulipas, México.



--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
For all your IT requirements visit: http://www.aduanet.net

Hi,


Vidal Garza wrote:
> [[...]]
>
> I have a cuestion, where do you find the system header files error
> number (61)?

I do that brute-force:

#! /bin/sh
#
# FGIN shell script to use "fgrep" on all
"/usr/include/....h" files
#
# $1, $2, ... options and arguments given to "fgrep"
#
# Simple example:
# FGIN seteuid return the line(s) in system header files where
# 'seteuid()' is defined or otherwise mentioned.
#
# 2004-09-13 Joerg Bruehe Initial "published" version

find /usr/include -follow -name '*.h' -print | xargs fgrep -n $*

This should work regardless of nested includes.

It is meant for any system definition, not just for error numbers,
that's why I do not restrict the list of file names.


If you are looking for an error number (as opposed to an identifier),
you may want to filter the output a bit, depending on your system's
conventions (example from LinuX):

The line you target for is
#define ECONNREFUSED 111 /* Connection refused */
but all you have is the 111 (the number).

1) "grep -n" in the script makes it write the line number, so you can
filter for : # define (tab or blank, any number) E
FGIN 111 | grep ':#define[ ]*E'
(the square bracket contains a blank and a tab).
On my system, this brings the output down from 926 lines (the 111
matches a postal code in the GPL comment !) to 9 lines.

2) If you are searching for error numbers, it is highly likely that the
file name contains "err":
FGIN 111 | grep '^[^:]*err'
(string "err" before the first colon). This returns 11 lines for me.

3) Combine the two, and it is only one hit (for me):
FGIN 111 | grep ':#define[ ]*E' | grep '^[^:]*err'

Try on your system, using 61.


HTH,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Make sure your mysqld is running

On Fri, Apr 4, 2008 at 3:24 AM, Joerg Bruehe <joerg@mysql.com> wrote:

> Hi,
>
>
> Vidal Garza wrote:
>
> > [[...]]
> >
> > I have a cuestion, where do you find the system header files error
> > number (61)?
> >
>
> I do that brute-force:
>
> #! /bin/sh
> #
> # FGIN shell script to use "fgrep" on all "/usr/include/....h"
> files
> #
> # $1, $2, ... options and arguments given to "fgrep"
> #
> # Simple example:
> # FGIN seteuid return the line(s) in system header files where
> # 'seteuid()' is defined or otherwise mentioned.
> #
> # 2004-09-13 Joerg Bruehe Initial "published" version
>
> find /usr/include -follow -name '*.h' -print | xargs fgrep -n $*
>
> This should work regardless of nested includes.
>
> It is meant for any system definition, not just for error numbers, that's
> why I do not restrict the list of file names.
>
>
> If you are looking for an error number (as opposed to an identifier), you
> may want to filter the output a bit, depending on your system's conventions
> (example from LinuX):
>
> The line you target for is
> #define ECONNREFUSED 111 /* Connection refused */
> but all you have is the 111 (the number).
>
> 1) "grep -n" in the script makes it write the line number, so you can
> filter for : # define (tab or blank, any number) E
> FGIN 111 | grep ':#define[ ]*E'
> (the square bracket contains a blank and a tab).
> On my system, this brings the output down from 926 lines (the 111 matches
> a postal code in the GPL comment !) to 9 lines.
>
> 2) If you are searching for error numbers, it is highly likely that the
> file name contains "err":
> FGIN 111 | grep '^[^:]*err'
> (string "err" before the first colon). This returns 11 lines for me.
>
> 3) Combine the two, and it is only one hit (for me):
> FGIN 111 | grep ':#define[ ]*E' | grep '^[^:]*err'
>
> Try on your system, using 61.
>
>
> HTH,
> Joerg
>
> --
> Joerg Bruehe, Senior Production Engineer
> MySQL AB, www.mysql.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
>
>


--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn