"How do I ..." SQL question
Hi there: I have a "How do I..." SQL question regarding selecting distinct values from a field not included in an aggregated query when LIMIT is in effect, illustrated by the following example: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 kim2004 1 joe2005 Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. I would like to be compatible with 3.23.xx. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant tables update backward compatibility
Hi there, I have been using mysql 3.23.58, and I want to upgrade to 4.0.20. My question is this: after I run the script to upgrade the grant tables to support the new privilgeges, can I then revert back to 3.23.58 seamlessly or will I need to readjust the grant tables. Thanks in advance. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data disappeared in mysql on XP
Hi all, I have just installed mysql 4.0 on my XP. I inserted data into the db by java. Then I used 1) command prompt to select from the table 2) MysqlCC 3) JDBC to retrieved the inserted data. It's ok. However, after a while (like rebooting my notebook, or hibernate my notebook... but not repeatable), the data is just all gone. I tried to insert data by MysqlCC, it's like that also. This case always happen. Has anyone experienced this before and advise the solution ? THanks. Perseus _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table design of multi-lingual content
Hi all ,
I want to design a table, say
T_PRODUCT { PRD_ID, NAME, DESCRIPTION, PRICE }
where I want NAME and DESCRIPTION to have multi language inputed.
I searched before where found two solutions :
1)
T_PRODUCT {PRD_ID, PRD_DETAIL_ID, PRICE}
T_PRODUCT_DETAIL { PRD_DEATIL_ID, LANG, NAME, DESCRIPTION }
2)
T_PRODUCT {PRD_ID, NAME_LANG_ID, DESCRIPTION_LANG_ID, PRICE}
T_LANG { LANG_ID, LANG, CONTENT }
But 1) seems to be a very trivial process where I have to have one
additional table for each table for language. But I am afraid 2 will have
too much join if multi-lingual attributes increase.
Is there a better design ? Please advise
Thanks.
Perseus
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query formulation question
Hi there, I have some experience with SQL, but I'm drawing a blank on this one. I have a table with these fields: Location Unit StartDate StopDate I want to construct a SELECT statement to get to a report that looks something like this: Location1Location2 Location3 ... Date1 count of units where Datex is between Date2 StartDate and StopDate ... Date3 ... Where Date1, Date2, etc are a week apart within a given date range. Any help appreciated. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
safe_mysqld bind-address
Hi all , I am trying to do the following : [root@xxx bin]# ./safe_mysqld --bind-address=192.168.1.222 Starting mysqld daemon with databases from /usr/local/mysql/var but when I telnet xxx 3306, the mysqld have the following response: ./safe_mysqld: line 1: 12325 Segmentation fault nice --5 nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/hkpfaServer.pid --skip-locking --bind-address\=192.168.1.222 >>/usr/local/mysql/var/hkpfaServer.err 2>&1 Number of processes running now: 2 mysqld process hanging, pid 12328 - killed ./safe_mysqld: kill: (12328) - No such pid 021029 00:37:34 mysqld restarted So how can I bind the address successfully ? Thanks. Perseus _ Get a speedy connection with MSN Broadband. Join now! http://resourcecenter.msn.com/access/plans/freeactivation.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL strange error: lost connection during query
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: MySQL strange error: lost connection during query
>Description:
When 2 concurrent processes access the database 'sometimes' MySQL
reports this error to both processes at the same time. It happens very
rarely and about 10 seconds later they continue with its normal work.
When we increase the number of processes accessing the database this
happens more often.
Following queries after this error return 'MySQL server has gone
away'. The processes access the database stored at localhost.
It is possible that the tables involved in the queries could get
corrupted after this error. Then, database must be repaired before any
successive inserts in the table.
>How-To-Repeat:
void insercion(MYSQL mysql_h1, int orden)
{
int i=1;
if( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") )
do {
printf("%d ERROR %s, n.try %d\n", orden,
mysql_error(&mysql_h1), ++i );
sleep( rand() % 5 +1 );
} while( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") );
else
printf("%d Correct!\n", orden);
}
This is the possible fix in the user code, retrying some times and
about a random time (in order to avoid another concurrent error). But it
doesn't work. Sometimes reports: 'Database not selected' maybe because
after a bad query the connection to the database has been lost?
I have tested this code in a linux machine with 15 simultaneus
processes and it doesn't happen at all. Could be a problem with the
architecture?
>Fix:
Unknown fix
>Submitter-Id:Zeus Gomez Marmolejo
>Originator:
>Organization: Facultat d'Informatica de Barcelona [UPC]
>MySQL support: none
>Synopsis:MySQL error report
>Severity:serious
>Priority:medium
>Category:mysql
>Class:sw-bug
>Release:mysql-3.23.38 (Official MySQL binary)
>Environment:
System: OSF1 merlin.fib.upc.es V5.1 732 alpha
Machine: alpha
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake
/usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from
/usr/local/lib/gcc-lib/alpha-dec-osf4.0e/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release)
Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args
-fast -inline speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4
-ansi_alias -fast -inline speed -speculate all -noexceptions -nortti'
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root system17 Jun 14 13:04 /lib/libc.a ->
../ccs/lib/libc.a
lrwxrwxrwx 1 root system17 Jun 14 13:04 /usr/lib/libc.a ->
../ccs/lib/libc.a
Configure command: ./configure --prefix=/usr/local/mysql
'--with-comment=Official MySQL binary' --with-extra-charsets=complex
--with-server-suffix= --with-prefix=/usr/local/mysql
--with-mysqld-ldflags=-all-static --disable-shared
'--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared
Perl: This is perl, version 5.005_03 built for alpha-dec_osf
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL strange error: lost connection during query
>Description:
When 2 concurrent processes access the database 'sometimes' MySQL reports this
error to both processes at the same time. It happens very rarely and about 10 seconds
later they continue with its normal work. When we increase the number of processes
accessing the database this happens more often.
Following queries after this error return 'MySQL server has gone away'. The
processes access the database stored at localhost.
It is possible that the tables involved in the queries could get corrupted
after this error. Then, database must be repaired before any successive inserts in the
table.
>How-To-Repeat:
void insercion(MYSQL mysql_h1, int orden)
{
int i=1;
if( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") )
do {
printf("%d ERROR %s, n.try %d\n", orden,
mysql_error(&mysql_h1), ++i );
sleep( rand() % 5 +1 );
} while( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") );
else
printf("%d Correct!\n", orden);
}
This is the possible fix in the user code, retrying some times and about a
random time (in order to avoid another concurrent error). But it doesn't work.
Sometimes reports: 'Database not selected' maybe because after a bad query the
connection to the database has been lost?
I have tested this code in a linux machine with 15 simultaneus processes and
it doesn't happen at all. Could be a problem with the architecture?
>Fix:
Unknown fix
>Submitter-Id: Zeus Gomez Marmolejo
>Originator:
>Organization: Facultat d'Informatica de Barcelona [UPC]
>MySQL support: none
>Synopsis: MySQL error report
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.38 (Official MySQL binary)
>Environment:
System: OSF1 merlin.fib.upc.es V5.1 732 alpha
Machine: alpha
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/local/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf4.0e/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release)
Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline
speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline
speed -speculate all -noexceptions -nortti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root system17 Jun 14 13:04 /lib/libc.a -> ../ccs/lib/libc.a
lrwxrwxrwx 1 root system17 Jun 14 13:04 /usr/lib/libc.a ->
../ccs/lib/libc.a
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official
MySQL binary' --with-extra-charsets=complex --with-server-suffix=
--with-prefix=/usr/local/mysql --with-mysqld-ldflags=-all-static --disable-shared
'--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared
Perl: This is perl, version 5.005_03 built for alpha-dec_osf
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL strange error: lost connection during query
>Description:
When 2 concurrent processes access the database 'sometimes' MySQL reports this
error to both processes at the same time. It happens very rarely and about 10 seconds
later they continue with its normal work. When we increase the number of processes
accessing the database this happens more often.
Following queries after this error return 'MySQL server has gone away'. The
processes access the database stored at localhost.
It is possible that the tables involved in the queries could get corrupted
after this error. Then, database must be repaired before any successive inserts in the
table.
>How-To-Repeat:
void insercion(MYSQL mysql_h1, int orden)
{
int i=1;
if( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") )
do {
printf("%d ERROR %s, n.try %d\n", orden,
mysql_error(&mysql_h1), ++i );
sleep( rand() % 5 +1 );
} while( mysql_query(&mysql_h1, "insert into conexiones values ('', \
'a5sbue123', 'f3089047', 'fassion', '', NULL, 'P', 0)") );
else
printf("%d Correct!\n", orden);
}
This is the possible fix in the user code, retrying some times and about a
random time (in order to avoid another concurrent error). But it doesn't work.
Sometimes reports: 'Database not selected' maybe because after a bad query the
connection to the database has been lost?
I have tested this code in a linux machine with 15 simultaneus processes and
it doesn't happen at all. Could be a problem with the architecture?
>Fix:
Unknown fix
>Submitter-Id: Zeus Gomez Marmolejo
>Originator:
>Organization: Facultat d'Informatica de Barcelona [UPC]
>MySQL support: none
>Synopsis: MySQL error report
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.38 (Official MySQL binary)
>Environment:
System: OSF1 merlin.fib.upc.es V5.1 732 alpha
Machine: alpha
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/local/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf4.0e/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release)
Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline
speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline
speed -speculate all -noexceptions -nortti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root system17 Jun 14 13:04 /lib/libc.a -> ../ccs/lib/libc.a
lrwxrwxrwx 1 root system17 Jun 14 13:04 /usr/lib/libc.a ->
../ccs/lib/libc.a
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official
MySQL binary' --with-extra-charsets=complex --with-server-suffix=
--with-prefix=/usr/local/mysql --with-mysqld-ldflags=-all-static --disable-shared
'--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared
Perl: This is perl, version 5.005_03 built for alpha-dec_osf
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Altering Table Fields Place on the table
Hi there, I'm rather new to mySQL and I have some questions to ask : How can I alter my table field to move before another table field ? Chua Zhi Hon aka. Zeus - Founder, Frozened.com "Your site for technology news, SUMMARISED!" http://www.frozened.com Editorial, paGn.net http://www.pagn.net
