JOINing complication, help please
CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: JOINing complication, help please
Ok, I got a result here, still trying to determine if its correct or not :) LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOINing complication, help please
, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID) This actually worked great, I only had to refer to ADDRESS once in the select for output, I only had to join the other tables CITY, PROVINCE, COUNTRY once on ADDRESS too. BUT the darned thing wouldn't see the indexes on the related address fields, so when I populated the ADDRESS table with 10 records, the return took much longer than desired. IF it did pay attention to the indexes like I expected, then it would have been the much preferable choice, but it didn't, so I had to do many extra joins and use aliases and get a monstrosity :) ( i do really appologize for that big spew of SQL ) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 11:37 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: JOINing complication, help please Sorry to reply to myslef but I just saw my own typo. Here is a better example statement: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO (the problem was: the s and the a tables are aliases of the same table so they should have had the same column names. SORRY !!!) [EMAIL PROTECTED] To: Luc Foisy [EMAIL PROTECTED] 06/02/2004 11:31 cc: MYSQL-List (E-mail) [EMAIL PROTECTED] AM Fax to: Subject: Re: JOINing complication, help please Luc, This looks like you want a list of all Customers with Contacts (because you are basing it on the CONTACT_X_CUSTOMER table) and you want to show the Address (if it exists) or the Shipping Address (if it exists) instead of the Address? Am I close? If I want to get one of two or more result choices in a column, I use an IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement, depending on what it is I am choosing between. In your case I think you want to chose which address to use based on their existence, in this case, if it exists, it won't be null: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO In this statement, the COALESCE statement will resolve to be the first non-null expression in the list. If there is no Address that matches your _X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased as s will be NULL, Same with ID_ADDRESS and the table aliased as a. If neither address exists the string 'none' is the result. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Luc Foisy [EMAIL PROTECTED]To: MYSQL-List (E-mail) [EMAIL PROTECTED] -magic.com cc: Fax to: 06/02/2004 10:46 AM Subject: JOINing complication, help please CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
RE: JOINing complication, help please
Oops, that LEFT JOIN ADDRESS ON IF(etc...) shouldn't be in the big long select statement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql error file
So I am guessing this is not a bug, and that I will have to modify the script myself?
-Original Message-
From: Luc Foisy
Sent: Monday, April 12, 2004 4:34 PM
To: Michael Stassen; MYSQL-List (E-mail)
Subject: RE: mysql error file
Michael wrote:
mm... Have you looked in /etc/rc.d/init.d/mysql to see what, exactly, it
oes when you tell it to start?
Michael
I briefly looked in there. This is the same script it uses to start MySQL, and was
installed with the rpm of MySQL.
I would think it should come with all available options already...
To me, it doesn't look like it even reads that option from the my.cnf file. It doesn't
look like its reading any option from mysqld_safe group.
Pasting the whole thing, so I don't have to work with file attaching.
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB Monty Program KB Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 90
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 3 5
# Default-Stop: 3 5
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=path-to-mysql-installation-directory
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
basedir=
# The following variables are only set for letting mysql.server find things.
# Set some defaults
datadir=/var/lib/mysql
pid_file=
if test -z $basedir
then
basedir=/
bindir=/usr/bin
else
bindir=$basedir/bin
fi
PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
export PATH
if test -z $pid_file
then
pid_file=$datadir/`/bin/hostname`.pid
else
case $pid_file in
/* ) ;;
* ) pid_file=$datadir/$pid_file ;;
esac
fi
mode=$1# start or stop
parse_arguments() {
for arg do
case $arg in
--basedir=*) basedir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--datadir=*) datadir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--pid-file=*) pid_file=`echo $arg | sed -e 's/^[^=]*=//'` ;;
esac
done
}
# Get arguments from the my.cnf file,
# groups [mysqld] [mysql_server] and [mysql.server]
if test -x ./bin/my_print_defaults
then
print_defaults=./bin/my_print_defaults
elif test -x $bindir/my_print_defaults
then
print_defaults=$bindir/my_print_defaults
elif test -x $bindir/mysql_print_defaults
then
print_defaults=$bindir/mysql_print_defaults
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e /$subpat/!d -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x $d/bin/my_print_defaults
then
print_defaults=$d/bin/my_print_defaults
break
fi
if test -x $d/bin/mysql_print_defaults
then
print_defaults=$d/bin/mysql_print_defaults
break
fi
done
fi
# Hope it's in the PATH ... but I doubt it
test -z $print_defaults print_defaults=my_print_defaults
fi
parse_arguments `$print_defaults mysqld mysql_server mysql.server`
# Safeguard (relative paths, core dumps..)
cd $basedir
case $mode in
'start')
# Start daemon
if test -x $bindir/safe_mysqld
then
# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.
$bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file
# Make lock for RedHat / SuSE
if test -w /var/lock/subsys
then
touch /var/lock/subsys/mysql
fi
else
echo Can't execute $bindir/safe_mysqld from dir $basedir
fi
;;
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s $pid_file
then
mysqld_pid=`cat $pid_file`
echo Killing mysqld with pid $mysqld_pid
kill $mysqld_pid
# mysqld should remove
RE: mysql error file
I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql error file
I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql error file
Actually, that didn't change anything. Still writing to $HOSTNAME.err -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 12:03 PM To: Luc Foisy Cc: Paul DuBois; MYSQL-List (E-mail) Subject: Re: mysql error file Note (see ps output) that you are using safe_mysqld, rather than mysqld_safe, as you have mysql 3.23.58. Hence, you need to change [mysqld_safe] to [safe_mysqld] in your my.cnf. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html. Michael Luc Foisy wrote: I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql error file
Michael wrote:
mm... Have you looked in /etc/rc.d/init.d/mysql to see what, exactly, it
oes when you tell it to start?
Michael
I briefly looked in there. This is the same script it uses to start MySQL, and was
installed with the rpm of MySQL.
I would think it should come with all available options already...
To me, it doesn't look like it even reads that option from the my.cnf file. It doesn't
look like its reading any option from mysqld_safe group.
Pasting the whole thing, so I don't have to work with file attaching.
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB Monty Program KB Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 90
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 3 5
# Default-Stop: 3 5
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=path-to-mysql-installation-directory
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
basedir=
# The following variables are only set for letting mysql.server find things.
# Set some defaults
datadir=/var/lib/mysql
pid_file=
if test -z $basedir
then
basedir=/
bindir=/usr/bin
else
bindir=$basedir/bin
fi
PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
export PATH
if test -z $pid_file
then
pid_file=$datadir/`/bin/hostname`.pid
else
case $pid_file in
/* ) ;;
* ) pid_file=$datadir/$pid_file ;;
esac
fi
mode=$1# start or stop
parse_arguments() {
for arg do
case $arg in
--basedir=*) basedir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--datadir=*) datadir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--pid-file=*) pid_file=`echo $arg | sed -e 's/^[^=]*=//'` ;;
esac
done
}
# Get arguments from the my.cnf file,
# groups [mysqld] [mysql_server] and [mysql.server]
if test -x ./bin/my_print_defaults
then
print_defaults=./bin/my_print_defaults
elif test -x $bindir/my_print_defaults
then
print_defaults=$bindir/my_print_defaults
elif test -x $bindir/mysql_print_defaults
then
print_defaults=$bindir/mysql_print_defaults
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e /$subpat/!d -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x $d/bin/my_print_defaults
then
print_defaults=$d/bin/my_print_defaults
break
fi
if test -x $d/bin/mysql_print_defaults
then
print_defaults=$d/bin/mysql_print_defaults
break
fi
done
fi
# Hope it's in the PATH ... but I doubt it
test -z $print_defaults print_defaults=my_print_defaults
fi
parse_arguments `$print_defaults mysqld mysql_server mysql.server`
# Safeguard (relative paths, core dumps..)
cd $basedir
case $mode in
'start')
# Start daemon
if test -x $bindir/safe_mysqld
then
# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.
$bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file
# Make lock for RedHat / SuSE
if test -w /var/lock/subsys
then
touch /var/lock/subsys/mysql
fi
else
echo Can't execute $bindir/safe_mysqld from dir $basedir
fi
;;
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s $pid_file
then
mysqld_pid=`cat $pid_file`
echo Killing mysqld with pid $mysqld_pid
kill $mysqld_pid
# mysqld should remove the pid_file when it exits, so wait for it.
sleep 1
while [ -s $pid_file -a $flags != aa ]
do
[ -z $flags ] echo Wait for mysqld to exit\c || echo .\c
flags=a$flags
mysql error file
I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
Wait, I see it now :) -Original Message- From: Luc Foisy Sent: Wednesday, April 07, 2004 10:45 AM To: [EMAIL PROTECTED] Subject: RE: stuck with simple query. Plz have a look I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reference to a command that I can not find AND Foriegn Key information
There was a user comment under the Foriegn Key section of the documentation reading: To restore from a mysqldump file that uses foreign keys: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE your_dump_file; mysql SET FOREIGN_KEY_CHECKS = 1; The command I am looking for is the call SOURCE. Where is this in the documentation? Also, will this FOREIGN_KEY_CHECKS ignore the definitions while creating the tables, as in not create them, or just merely not check for its consitancy? Is any of the above limited to the InnoDB type? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Replication
I sent this out friday, but didn't see it come through to the list, so sorry if it comes up twice if the original is lost in lala land at the moment. -Original Message- From: Luc Foisy Sent: Friday, December 12, 2003 4:17 PM To: MYSQL-List (E-mail) Subject: Replication The scenario we wish to accomplish SERVER1 - Logging DB1 SERVER2 - Logging DB2 Logging DB3 Replicating DB1 from SERVER1 - Logging DB1 SERVER3 - Replicating DB1 from SERVER2 Replicating DB2 from SERVER2 Replicating DB3 from SERVER2 What I am asking is for confirmation that the following my.cnf files would do that. SERVER1 [mysqld] log-bin binlog-do-db=DB1 server-id=1 SERVER2 [mysqld] log-bin master-host=SERVER1 master-user=SERVER2 master-password=password binlog-do-db=DB2 binlog-do-db=DB3 log-slave-updates server-id=2 SERVER3 [mysqld] master-host=SERVER2 master-user=SERVER3 master-password=password binlog-do-db=DB1 binlog-do-db=DB2 binlog-do-db=DB3 server-id=3 To do the initial setup of the slaves I would do the following? Dump DB1 from SERVER1 Start logging of DB1 on SERVER1 Load Dump of DB1 onto SERVER2 Start replication of DB1 on SERVER2 (add replication parameters to my.cnf and restart) Dump DB2 from SERVER2 Dump DB3 from SERVER2 Load Dump of DB1 onto SERVER3 Load Dump of DB2 onto SERVER3 Load Dump of DB3 onto SERVER3 Start replication of DB1 on SERVER3 (add replication parameters to my.cnf and restart) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication
I tried the below. asked show master status on SERVER2 and I see the two db's I asked to replicate on that server but no indication that the logging is happening from the replication of DB1 from SERVER1 ( I missed below showing replicate-do-db=DB1 in the SERVER2 my.cnf, and the binlog-do-db's in SERVER3 should read replicate-do-db) Will set up the replication on SERVER3 in a bit to see if I am getting anything from that log-slave-updates -Original Message- From: Luc Foisy Sent: Monday, December 15, 2003 11:03 AM To: MYSQL-List (E-mail) Subject: FW: Replication I sent this out friday, but didn't see it come through to the list, so sorry if it comes up twice if the original is lost in lala land at the moment. -Original Message- From: Luc Foisy Sent: Friday, December 12, 2003 4:17 PM To: MYSQL-List (E-mail) Subject: Replication The scenario we wish to accomplish SERVER1 - Logging DB1 SERVER2 - Logging DB2 Logging DB3 Replicating DB1 from SERVER1 - Logging DB1 SERVER3 - Replicating DB1 from SERVER2 Replicating DB2 from SERVER2 Replicating DB3 from SERVER2 What I am asking is for confirmation that the following my.cnf files would do that. SERVER1 [mysqld] log-bin binlog-do-db=DB1 server-id=1 SERVER2 [mysqld] log-bin master-host=SERVER1 master-user=SERVER2 master-password=password binlog-do-db=DB2 binlog-do-db=DB3 log-slave-updates server-id=2 SERVER3 [mysqld] master-host=SERVER2 master-user=SERVER3 master-password=password binlog-do-db=DB1 binlog-do-db=DB2 binlog-do-db=DB3 server-id=3 To do the initial setup of the slaves I would do the following? Dump DB1 from SERVER1 Start logging of DB1 on SERVER1 Load Dump of DB1 onto SERVER2 Start replication of DB1 on SERVER2 (add replication parameters to my.cnf and restart) Dump DB2 from SERVER2 Dump DB3 from SERVER2 Load Dump of DB1 onto SERVER3 Load Dump of DB2 onto SERVER3 Load Dump of DB3 onto SERVER3 Start replication of DB1 on SERVER3 (add replication parameters to my.cnf and restart) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
The scenario we wish to accomplish SERVER1 - Logging DB1 SERVER2 - Logging DB2 Logging DB3 Replicating DB1 from SERVER1 - Logging DB1 SERVER3 - Replicating DB1 from SERVER2 Replicating DB2 from SERVER2 Replicating DB3 from SERVER2 What I am asking is for confirmation that the following my.cnf files would do that. SERVER1 [mysqld] log-bin binlog-do-db=DB1 server-id=1 SERVER2 [mysqld] log-bin master-host=SERVER1 master-user=SERVER2 master-password=password binlog-do-db=DB2 binlog-do-db=DB3 log-slave-updates server-id=2 SERVER3 [mysqld] master-host=SERVER2 master-user=SERVER3 master-password=password binlog-do-db=DB1 binlog-do-db=DB2 binlog-do-db=DB3 server-id=3 To do the initial setup of the slaves I would do the following? Dump DB1 from SERVER1 Start logging of DB1 on SERVER1 Load Dump of DB1 onto SERVER2 Start replication of DB1 on SERVER2 (add replication parameters to my.cnf and restart) Dump DB2 from SERVER2 Dump DB3 from SERVER2 Load Dump of DB1 onto SERVER3 Load Dump of DB2 onto SERVER3 Load Dump of DB3 onto SERVER3 Start replication of DB1 on SERVER3 (add replication parameters to my.cnf and restart) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if function?
UPDATE table SET d='complete' WHERE a=0 AND b=0 and c=0; -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 3:58 PM To: MySQL List Subject: if function? Is there a way to write an if statement in mysql to affect the following?... if field a=0,b=0,c=0 then update d='complete' where the table consists of... a int(1), b int(1), c int(1), d char(8) |a |b |c |d | |1 |0 |0 |null | |1 |1 |0 |null | |0 |0 |0 |null | resulting with the third recode updated to show 'complete'? TIA Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: String Functions
That I can do
REPLACE(REPLACE(REPLACE(REPLACE(str,'(',''),')',''),'-',''),' ','')
Looks kinda horrid to handle removing just four characters ( ) - and space
And if any other characters end up in str, then they are not handled, and my formula
is broken.
So there is no functions that would do what I need?
Anyone have some kind of formula (multiple functions perhaps) that would simulate
what I need, something that would only return numeric characters?
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:15 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: RE: String Functions
Try the Replace function
replace the `-` character with ``
-Original Message-
From: Luc Foisy [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 10:09 AM
To: MYSQL-List (E-mail)
Subject: String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT Query assistance please
I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT Query assistance please
Got it, thanks SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference', EVENT.ID, EVENT.ID_PROJECTCODE FROM PROJECTCODE LEFT JOIN EVENT ON PROJECTCODE.ID = EVENT.ID_PROJECTCODE WHERE EVENT.ID IS NULL ORDER BY PROJECTCODE.Name -Original Message- From: Luc Foisy Sent: Thursday, November 13, 2003 2:01 PM To: MYSQL-List (E-mail) Subject: SELECT Query assistance please I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
If I have replication already active for a single database ( already active between master and slave ) and I want to start replicating a new database on the master, what would the correct procedure be? I have a dump of the database I want to replicate. Below is the current setup for replication. Master my.cnf log-bin binlog-do-db=database1 server-id=1 Slave my.cnf master-host=master.address master-user=replicant2 master-password=password replicate-do-db=database1 server-id=3 Would I just do the following? Master my.cnf binlog-do-db=database2 (then restart master) load dump into slave db Slave my.cnf replicate-do-db=database2 (then restart slave) Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL powered MailServer
Try here http://www.lencom.com/desc/indexN16185.html -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 1:18 PM To: [EMAIL PROTECTED] Subject: MySQL powered MailServer Just wanted to know if anyone knows the status of the MySQL powered Mailserver found at http://www.mysql.com/portal/software/item-239.html Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL powered MailServer
Or not, try here instead http://gd.tuwien.ac.at/graphics/sf/h/hmailserver/ -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 1:18 PM To: [EMAIL PROTECTED] Subject: MySQL powered MailServer Just wanted to know if anyone knows the status of the MySQL powered Mailserver found at http://www.mysql.com/portal/software/item-239.html Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL standalone and Java
Are there packages for java to include a standalone mysql database? Or are there plans for such? Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myisamchk question (important)
But it is procedure that the MySQL documentation states MySQL uses themselves without any problems. Perhaps the documentation isn't stated clear enough. Quote 4.4.7 Setting Up a Table Maintenance Regimen -- You should also check your tables regularly during normal system operation. At MySQL AB, we run a cron job to check all our important tables once a week, using a line like this in a `crontab' file: 35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI This prints out information about crashed tables so we can examine and repair them when needed. As we haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough for us. We recommend that to start with, you execute myisamchk -s each night on all tables that have been updated during the last 24 hours, until you come to trust MySQL as much as we do. -- Regardless of any warnings on other documentation pages, I see no FLUSH TABLES happening here. That looks like a recomended procedure to me. Is there additional hidden documentation that should be part of this? We are looking at all the options at this time. And I am sad to tell you that one of our servers is still running 3.23.32 (which does not have CHECK TABLE). We will be upgrading it, some people are under the notion, if its not broken, don't fix it and possibly introduce other bugs that crop up. Why, because its happened before on other servers :) Also, I want to make darn sure about what the possible outcomes of this would be. Someone went forward with running myisamchk on a daily basis. I want to know if we have possibly broken something. I am not fighting to use any particular method at this time :) What my suggestion was, was to use a PROCESS permitted user and run these CHECK TABLE from the command line. The problem with CHECK TABLE that I can see? Thats a large process to check multiple databases with 20 or 30 tables each. myisamchk is a great deal more easier to use. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 5:00 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: myisamchk question (important) At 16:32 -0400 8/21/03, Luc Foisy wrote: Thank you Paul. whew! So just checking will not do anything to the database, in an case? I am not really caring if it returns the # users still connected/ or table not closed right warning. I am not really looking for that. I am looking for warnings telling my that tables are corrupted... Well, the thing is, if you don't tell the server to flush changes, I believe there may be partially flushed changes that can cause myisamchk to get confused and believe that there is corruption. I don't recall that I have ever seen this in practice when trying to see what breaks by performing this unrecommended procedure :-) -- but I'm reluctant to say that it can *never* happen. Any reason not to use CHECK TABLE? You may find it a better alternative. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 3:08 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: myisamchk question (important) I should qualify my answer, to indicate something that may not apply to the situation you have in mind. If you're using myisamchk only to *check* tables, it operates in read-only fashion. The problems occur if you're using it to repair tables, because then if you have both myisamchk and the server writing to the table files, you're not going to like the results. However, even with checking, if you don't flush the tables, you may see spurious warnings of inconsistencies. (Due to unflushed changes, for example.) These do not result in table damage. At 14:37 -0400 8/21/03, Luc Foisy wrote: If you read http://www.mysql.com/doc/en/Maintenance_regimen.html they say something interesting that would contracdict other places in the documentation They actually recomend running myisamchk on a running instance of mysqld. The method on that page (a method they use themselves) would not even allow a flush tables to be called before. Strange... How many answers can I find to this, does anyone know? I have several conflicting sources now -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 10:06 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: myisamchk question (important) At 9:54 -0400 8/19/03, Luc Foisy wrote: Would anything happen to the database if I ran myisamchk --silent /usr/data/mysql/*/*.MYI when I havent run FLUSH TABLES first? Yes, you may have unflushed changes still in the server's buffers. Running myisamchk in that case can make the tables *in*consistent because the tables won't have in them what the server thinks. Don't do it. As it states in the documentation: If mysqld is running, you must force a sync/close
RE: myisamchk question (important)
If you read http://www.mysql.com/doc/en/Maintenance_regimen.html they say something interesting that would contracdict other places in the documentation They actually recomend running myisamchk on a running instance of mysqld. The method on that page (a method they use themselves) would not even allow a flush tables to be called before. Strange... How many answers can I find to this, does anyone know? I have several conflicting sources now -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 10:06 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: myisamchk question (important) At 9:54 -0400 8/19/03, Luc Foisy wrote: Would anything happen to the database if I ran myisamchk --silent /usr/data/mysql/*/*.MYI when I havent run FLUSH TABLES first? Yes, you may have unflushed changes still in the server's buffers. Running myisamchk in that case can make the tables *in*consistent because the tables won't have in them what the server thinks. Don't do it. As it states in the documentation: If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. Or is that only to avoid seeing: myisamchk: warning: 1 clients is using or hasn't closed the table properly Its very important that I get an answer soon... Please and Thank you -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myisamchk question (important)
Thank you Paul. whew! So just checking will not do anything to the database, in an case? I am not really caring if it returns the # users still connected/ or table not closed right warning. I am not really looking for that. I am looking for warnings telling my that tables are corrupted... -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 3:08 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: myisamchk question (important) I should qualify my answer, to indicate something that may not apply to the situation you have in mind. If you're using myisamchk only to *check* tables, it operates in read-only fashion. The problems occur if you're using it to repair tables, because then if you have both myisamchk and the server writing to the table files, you're not going to like the results. However, even with checking, if you don't flush the tables, you may see spurious warnings of inconsistencies. (Due to unflushed changes, for example.) These do not result in table damage. At 14:37 -0400 8/21/03, Luc Foisy wrote: If you read http://www.mysql.com/doc/en/Maintenance_regimen.html they say something interesting that would contracdict other places in the documentation They actually recomend running myisamchk on a running instance of mysqld. The method on that page (a method they use themselves) would not even allow a flush tables to be called before. Strange... How many answers can I find to this, does anyone know? I have several conflicting sources now -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 10:06 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: myisamchk question (important) At 9:54 -0400 8/19/03, Luc Foisy wrote: Would anything happen to the database if I ran myisamchk --silent /usr/data/mysql/*/*.MYI when I havent run FLUSH TABLES first? Yes, you may have unflushed changes still in the server's buffers. Running myisamchk in that case can make the tables *in*consistent because the tables won't have in them what the server thinks. Don't do it. As it states in the documentation: If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. Or is that only to avoid seeing: myisamchk: warning: 1 clients is using or hasn't closed the table properly Its very important that I get an answer soon... Please and Thank you -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk question (important)
Would anything happen to the database if I ran myisamchk --silent /usr/data/mysql/*/*.MYI when I havent run FLUSH TABLES first? As it states in the documentation: If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. Or is that only to avoid seeing: myisamchk: warning: 1 clients is using or hasn't closed the table properly Its very important that I get an answer soon... Please and Thank you Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad data in mysql tables in replication server
Last week many of our server and client servers had a power problem. Not quite sure how the servers were handled, wasn't on site, but I don't think some of these servers got shut down gracefully. but anyways that shouldn't matter to my question I ran myisamchk on the data directories and I get a large report containing things such as myisamchk: MyISAM file /usr/data/mysql/qbslive/MANIFESTSPOOL.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/usr/data/mysql/qbslive/MANIFESTSPOOL.MYI' is usable but should be fixed myisamchk: MyISAM file /usr/data/mysql/qbslive/MARKETING.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/usr/data/mysql/qbslive/MARKETING.MYI' is usable but should be fixed myisamchk: MyISAM file /usr/data/mysql/qbslive/ORDERHEADER.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/usr/data/mysql/qbslive/ORDERHEADER.MYI' is usable but should be fixed I am assuming I can just run myisamchk -r on these tables to fix them up on the master server... but I have multiple replication servers of databases... Can I repair these in the same way, or will the system fix it up or what? The slave servers are definately not running at the moment, have show slave status on one rep server of | 1062 | error 'Duplicate entry '5486435' for key 1' on query 'INSERT INTO DISPATCHLOG (CreateStamp) VALUES (NOW())' | Not quite sure what to do here, is my replicant toast? Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk
If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. If i didnt do the force sync/close of all tables, what would happen? Could bad things happen? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlbinlog suggestion
I suppose datetime would be the better option here -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Friday, July 18, 2003 10:00 AM To: [EMAIL PROTECTED] Subject: Re: mysqlbinlog suggestion Luc Foisy [EMAIL PROTECTED] wrote: allowing date range options to the command line would be really neat Date or datetime option? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbinlog suggestion
allowing date range options to the command line would be really neat Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RedHat 9.0 - Mysql 3.23.56
I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Thanks Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RedHat 9 - MySQL 3.23.56
Something interesting that may be my problem This is a known working install # mysql --version mysql Ver 11.18 Distrib 3.23.52, for pc-linux-gnu (i686) # find /etc/rc.d -name *mysql /etc/rc.d/init.d/mysql /etc/rc.d/rc0.d/K90mysql /etc/rc.d/rc1.d/K90mysql /etc/rc.d/rc2.d/S90mysql /etc/rc.d/rc3.d/S90mysql /etc/rc.d/rc4.d/S90mysql /etc/rc.d/rc5.d/S90mysql /etc/rc.d/rc6.d/K90mysql This is the broken one # mysql --version mysql Ver 11.18 Distrib 3.23.56, for pc-linux (i686) ]# find /etc/rc.d -name *mysql /etc/rc.d/init.d/mysql /etc/rc.d/rc0.d/K90mysql /etc/rc.d/rc1.d/K90mysql /etc/rc.d/rc2.d/S90mysql /etc/rc.d/rc3.d/K90mysql /etc/rc.d/rc4.d/S90mysql /etc/rc.d/rc5.d/K90mysql /etc/rc.d/rc6.d/K90mysql Anyone else see the possible problem? Where there a reason this was changed? Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RedHat 9.0 - Mysql 3.23.56
Yes, I turned those runlevels on already and now it works. The problem is that it was a practically default install, that is the way the runlevels were set out of the box 3.23.56 was this way after install mysql 0:off 1:off 2:on3:off4:on5:off6:off 3.23.52 was this way mysql 0:off 1:off 2:on3:on4:on5:on6:off Was wondering why though. A mistake or for some reason that I am curious about.. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:13 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: RedHat 9.0 - Mysql 3.23.56 At 10:55 -0400 6/25/03, Luc Foisy wrote: I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Try: chkconfig --list mysql to see what runlevels the mysql script thinks it's supposed to start for. My guess is that it's not enabled properly. If not, then do this: chkconfig --levels 2345 mysql on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RedHat 9.0 - Mysql 3.23.56
I must add, to be honest, and everything is clear :) I did not install 3.23.52 on Red Hat 9.0, so it may in fact be that way for all versions of mysql on RH9 Sorry about that. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 1:04 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: RedHat 9.0 - Mysql 3.23.56 At 12:41 -0400 6/25/03, Luc Foisy wrote: Yes, I turned those runlevels on already and now it works. The problem is that it was a practically default install, that is the way the runlevels were set out of the box 3.23.56 was this way after install mysql 0:off 1:off 2:on3:off4:on5:off6:off 3.23.52 was this way mysql 0:off 1:off 2:on3:on4:on5:on6:off Was wondering why though. A mistake or for some reason that I am curious about.. Thanks. I wanted to know whether they weren't set correctly out of the box for you. It may be a change with the way that chkconfig works in recent versions of Red Hat. Thanks. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:13 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: RedHat 9.0 - Mysql 3.23.56 At 10:55 -0400 6/25/03, Luc Foisy wrote: I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Try: chkconfig --list mysql to see what runlevels the mysql script thinks it's supposed to start for. My guess is that it's not enabled properly. If not, then do this: chkconfig --levels 2345 mysql on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there any way to search a whole database for a value?
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 11:33 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: Is there any way to search a whole database for a value? At 9:21 -0500 3/12/03, Luc Foisy wrote: Perhaps one day UNION will be added to UPDATE You'd still have to name every column to be updated explicitly. There is no update whatever column happens to contain this value syntax. of course you would have to name everything explicitly, but the original task was to update several tables/columns, not necessarily all tables and all columns, so thats not much of a problem. A thought about the UNION, might as well do two UPDATE's :) You have available to you as of 4.0.4 the multi-table UPDATE UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] Curious if the columns are named the same in all tables if you could a single SET, such as... UPDATE table1, table2 SET column = 5 WHERE column = 10 If you feel the need to go further with such strange tactics, you could use the multi-table UPDATE to explicitly name columns up to the allow query length Then again I am no Paul Dubois, just putting out some thoughts that may tickle some peoples fancy :) I need to look in several different tables/columns in a database for a particular value. If I find it, I need to update it. Is there any way to search/update every table/column in one query in a particular database? - 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
RE: mysql 4.0.11
Did I miss the global release message for this version? I got the InnoDB release message but no other... -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 10:53 AM To: [EMAIL PROTECTED] Subject: re: mysql 4.0.11 On Friday 21 February 2003 19:05, Reetz Wendy wrote: Does anyone know when the release date for the mysql 4.0.11 version? I was just about to rebuild everything (apache,php,etc), but if it's next week, I'd rather wait for it. You can find 4.0.11 at: http://www.mysql.com/downloads/mysql-4.0.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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 - 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
RE: Row numbers
On Thu, Feb 13, 2003 at 03:52:18PM -0800, Steve Edberg wrote: That means no field exists or should exist in the database. I only want to generate at query time. I can't use an autoincrement field since that wont work very well with results that are returned out of order and maybe not with all the data. Using variables is the best response to my question. I just dislike using them cause they are ugly to work with because of the session persistance and because I have to issue multiple queries to do the job. If the value isn't being used to even select a row, why not wrap the lines with OL...results.../OL or perhaps just use PHP/Perl/? to do an $i++ for display? Why put this in the query at all, if it has nothing to do with the data? I didn't say it had nothing to do with the data, I said it had nothing to do with the data in the database. The data in the recordset returned from the database referenced by row is important. As I said, sure I could make external functions to do the job. But why? (I did because I have no choice at the moment) 1. There are a lot of functions that return values that have nothing to do with data contained in the database. Math functions for one, they calculate return values using data that is in the database. And many many other functions. Why not one more. In the 4.x series the developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(), something that could just have easily been handled by external programming... 2. There is a use for it. since... 3. Going through the archives, I have seen many many people ask for it. 4. Those numbers probably already exist, how else does it ORDER BY, it has to put the results in an array of some kind - 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 Feature
I don't think it is possible at the moment, and pretty sure its not standard SQL but it would still be neat to have SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 'aColumn' ) FROM aTable So in this example, if the expression was true, include that column in the result set, otherwise don't Sure this can be handled outside MySQL and just change the query on condition, just thought it would be nice - 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
Row numbers
Is there a way to get a row number returned with any select query? - 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
RE: Row numbers
There is no relevant data or use to this number. It is the row number of the returned result set, purely for display. I was hoping there was some kind of function just to drop a number in there, regarless of any data that is stored in the table or regardless of the order the resultset appears. -Original Message- From: Jerry [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 1:48 PM To: Luc Foisy Subject: Re: Row numbers Have to have one in the row and select that along with the query, if your going to use it for some other sql command it probally should be in the table already - Original Message - From: Luc Foisy [EMAIL PROTECTED] To: Jerry [EMAIL PROTECTED] Sent: Thursday, February 13, 2003 5:59 PM Subject: RE: Row numbers No language, just straight mysql -Original Message- From: Jerry [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 12:53 PM To: Luc Foisy Subject: Re: Row numbers using what language ? or the mysql client ? - Original Message - From: Luc Foisy [EMAIL PROTECTED] To: MYSQL-List (E-mail) [EMAIL PROTECTED] Sent: Thursday, February 13, 2003 5:48 PM Subject: Row numbers Is there a way to get a row number returned with any select query? - 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 - 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
RE: MySQL Feature
-Original Message- From: Zak Greant [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 10:38 AM To: [EMAIL PROTECTED] Subject: Re: MySQL Feature On Thu, Feb 13, 2003 at 09:57:06AM -0500, Luc Foisy wrote: I don't think it is possible at the moment, and pretty sure its not standard SQL but it would still be neat to have SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 'aColumn' ) FROM aTable So in this example, if the expression was true, include that column in the result set, otherwise don't Sure this can be handled outside MySQL and just change the query on condition, just thought it would be nice You can use a WHERE clause to get an effect like this. Something like: SELECT CASE expression WHEN 'value' THEN column ... ELSE 'default result' END FROM Table; BUT SELECT Data, CASE WHEN 0 != 0 THEN 'Something' END FROM table will return two columns, Data and CASE WHEN 0 != 0 THEN 'Something' END. It would be better if I could do.. SELECT Data, CASE WHEN 0 != 0 THEN 'Something' AS 'A Column' END FROM table and the result set would only contain one column, Data I am sure people could find a use for it... - 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
RE: Row numbers
I still don't understand do you mean the actual row number or just a display number. There is no relevant data or use to this number. It is the row number of the returned result set, purely for display. That means no field exists or should exist in the database. I only want to generate at query time. I can't use an autoincrement field since that wont work very well with results that are returned out of order and maybe not with all the data. Using variables is the best response to my question. I just dislike using them cause they are ugly to work with because of the session persistance and because I have to issue multiple queries to do the job. I only want to issue one query. Is there a function to give me a number that increments by one for every row returned? If the answer is no, then no need to go any further other than me putting in a request to implement such ( maybe whatever [EMAIL PROTECTED] was talking about with Oracles ROWNUM ) Thanks for your effort guys... - 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
Replication Problem - Droping tmp tables
Here are a number of queries that are run from time to time on the master. They are
always run together and in order that they appear.
Version of the server is 3.23.32 ( yes its old, but difficult to update at this time )
Version of the slave is 3.23.49a
Note: [param:variable] is handled by our own query parser and relevant data is
placed withing before going to the server
DROP TABLE IF EXISTS ORD1;
CREATE TEMPORARY TABLE ORD1
SELECT DISTINCT
PAYROLLEVENT.ID_CONTRACTOR,
ORDERHEADER.ID,
ORDERHEADER.ID_CUSTOMER
FROM ORDERHEADER
LEFT JOIN ORDERLINE ON ORDERHEADER.ID = ORDERLINE.ID_ORDERHEADER
LEFT JOIN PAYROLLEVENT ON ORDERLINE.ID = PAYROLLEVENT.ID_ORDERLINE
WHERE (ORDERHEADER.AvailableAt BETWEEN '[param:Start Date]' AND
DATE_FORMAT('[param:End Date]', '%Y%m%d235959'))
AND PAYROLLEVENT.ID_CONTRACTOR IS NOT NULL
ORDER BY ORDERHEADER.ID;
SELECT
ID AS 'Order #',
ID_CUSTOMER AS 'Acct #'
FROM ORD1
GROUP BY ID HAVING COUNT(*) 1
ORDER BY ID;
Here is the Slave status
SHOW SLAVE STATUS
*** 1. row ***
Master_Host: 192.168.0.251
Master_User: replicant1
Master_Port: 3306
Connect_retry: 60
Log_File: QBSDB251-bin.045
Pos: 4730525
Slave_Running: No
Replicate_do_db: qbslive
Replicate_ignore_db:
Last_errno: 1158
Last_error: Slave: query 'drop table qbslive.ORD1' partially completed on the
master and was aborted. There is a chance that your master is inconsistent at this
point. If you are sure that your master is ok, run this query manually on the slave
and then restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;
Skip_counter: 0
There is nothing in the error logs that would indicate a problem.
What does this partially completed comment mean?
What would cause a drop table to abort?
Is this a version bug?
Where would I look on the master to see if my master is ok?
Since I can't find a problem ( maybe not knowing where to look ) and I don't want to
skip anything and run into another problem or something
-
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
RE: help me, please
At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? Maybe SELECT No, Name, Phone, Date, Time FROM tablename ORDER BY No DESC LIMIT 1 - 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
RE: RE: more about using sets
What exactly is wrong about using ENUM's? Luc sql,mysql - 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
RE: RE: more about using sets
What exactly is wrong about using ENUM's?
Changing them will kill you unless you're _very_ careful.
Using them is usually unnecessary as you could've used an ID value
pointing to another table of values. That table can then be added to
with no risk to your existing queries. As a contrived example:
Employee
--
ID ... primary key
Gender enum('male','female')
vs.
GenderID tinyint
Gender
--
ID tiny int ... primary key
Name varchar(10)
INSERT into Gender(Name) values ('male'),('female');
Later, you might need:
INSERT into Gender(Name) values ('unknown');
Is not enum forced to any of the values used when created?
We use enum extensively for 'Y' 'N' values, sort of true false.
That way the values are forced to be one or the other. So any end user has to put one
of those
values in (if we allow it in that way, though we usually force them to use a checkbox).
And its a little more viewable/understandable than 0 or 1, for the end user.
I also was under the impression that JOINs did take a little extra time/resource
rather than a direct value from the same table. We have a number of tables that
have multiple true/false values (one with about 50), is not joining that table
to 50 different fields not a little expensive?
Maybe we are just doing it all wrong? If we never expect the
values to be different (EVER) then is it still wrong?
Bah, sql, mysql
-
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
Tomcat 4.1.12 and maybe mysql
Same message I posted at JGuru and on the JDJ Mailing list, but posting here just incase it is mysql related and someone can help On RedHat 7.0 RedHat 7.3 with Java 1.4.0_03 When page is hit fairly quickly I get the following error: 2002-12-17 14:56:37 StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception org.apache.jasper.JasperException: Socket closed at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:248) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:380) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533) at java.lang.Thread.run(Thread.java:536) - Root Cause - java.net.SocketException: Socket closed at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:126) at org.apache.coyote.http11.InternalOutputBuffer$OutputStreamOutputBuffer.doWrite(InternalOutputBuffer.java:652) at org.apache.coyote.http11.filters.ChunkedOutputFilter.doWrite(ChunkedOutputFilter.java:166) at org.apache.coyote.http11.InternalOutputBuffer.doWrite(InternalOutputBuffer.java:523) at org.apache.coyote.Response.doWrite(Response.java:513) at org.apache.coyote.tomcat4.OutputBuffer.realWriteBytes(OutputBuffer.java:380) at org.apache.tomcat.util.buf.ByteChunk.flushBuffer(ByteChunk.java:360) at org.apache.coyote.tomcat4.OutputBuffer.flush(OutputBuffer.java:341) at org.apache.coyote.tomcat4.CoyoteResponse.flushBuffer(CoyoteResponse.java:554) at org.apache.coyote.tomcat4.CoyoteResponseFacade.flushBuffer(CoyoteResponseFacade.java:227) at org.apache.jasper.runtime.JspWriterImpl.flush(JspWriterImpl.java:211) at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:808) at org.apache.jsp.index_jsp._jspService(index_jsp.java:75) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:136) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at
make: don't know how to make mi_test_all. Stop
I am forwarding Shawn's real question to the list, since I haven't got a clue :) -Original Message- From: shawn xu [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 12:42 PM To: Luc Foisy Subject: RE: I can not post to this list after I have joined Hi, Luc Foisy: I don't know why this one got through, but the question I post never get through. Did you get my post : Subject: mi_test_all error help Content: I am using BSDI 4.3, and downloaded MySql source code 3.23.53. After untarring it, it passed ./configure --prefix=/usr/local/mysql. But when I run make, finally it says make: don't know how to make mi_test_all. Stop *** Error code 1 Your help will be appreciated Shawn Xu ps: I am using www.yahoo.com. I really appreciate if you can help with this. --- Luc Foisy [EMAIL PROTECTED] wrote: Why cannot I post questions to this list since I joined a month ago? Why? It says it cannot include attachment, but definately I didn't include attachment when I tried to post. What do you use to send your mail? Is it web mail? Things like incredimail and some webmails attach stuff to your email, like pictures and stuff to make it look pretty If thats the case, try using a plain text email client sql - 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
RE: join count and 0
I am wanting to see if there is a way to do a join but have count(id) show up as 0 when there is no records in the 2nd table. With out having to 2 selects. for example. orderid = has persons name and contact info select oi.id,oi.name, count(o.id) from orderid oi, orders o where o.order_id = oi.id group by oi.id; you have say 100 records in orderid , and only 50 of them have related orders in orders for those that don't have matching orders I would want count(o.id) to show 0 rather then not being listed. Maybe this might give you an idea, or totally mess you up, but its my = ramblings I have to offer I have no idea if it will even work, but its an idea SELECT orderid.id, orderid.name, orders.id, IF(orders.id IS NULL, '0', = COUNT(orders.id)) AS 'Count' FROM orderid, orders WHERE orders.order_id =3D orderid.id GROUP BY orderid.id, orders.id SQL - 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
Things are a lot better with humor
Not quite sure if this is appropriate for the list, but I had to share it SQL Query http://www.thinkgeek.com/tshirts/frustrations/595d/zoom/ Luc - 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
RE: Incorrect DATE_FORMAT output for weeks with year e.g. '%v-%y'
PHP handles this correctly -- if I do a date(W-y, $date)
for '2001-12-31'
I get '01-02', but in mysql you get the wrong year: '01-01'
I don't know where you got this data from, but the second number would be the year,
that means mysql is showing the right year and php is showing the wrong year ( xx-01
is right )
Here's the an example (the second one is not what I'd expect):
SELECT DATE_FORMAT('2001-12-25', '%v-%y');
++
| DATE_FORMAT('2001-12-25', '%v-%y') |
++
| 52-01 |
++
SELECT DATE_FORMAT('2001-12-31', '%v-%y');
++
| DATE_FORMAT('2001-12-31', '%v-%y') |
++
| 01-01 |
++
SELECT DATE_FORMAT('2002-01-05', '%v-%y');
++
| DATE_FORMAT('2002-01-05', '%v-%y') |
++
| 01-02 |
++
I'm using mysql version 3.23.51-log, and I haven't seen anything with
DATE_FORMAT in the changelog since.
Do people agree that this is a bug?
Is there any work around for this?
from the documentation
%v Week (01..53), where Monday is the first day of the week. Used with '%x'
should you be using %x with this?
%x Year for the week, where Monday is the first day of the week, numeric, 4 digits,
used with '%v'
Don't really know how you would use them with each other to get the right date, but
you must be able to do it some way
%u Week (00..53), where Monday is the first day of the week
SELECT DATE_FORMAT('2001-12-31', '%u-%y');
++
| DATE_FORMAT('2001-12-31', '%u-%y') |
++
| 53-01 |
++
the note that the week can return 53, perhaps this is being retranslated into week 1
when using %v
-
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
RE: Mysql Encryption
You could do something like (I repeat something like, you may need to modify) http://tjw.org/chroot-login-HOWTO/ or you could put something like this in thier startup script (this will disconnect when they press a key) Maybe in .bash_profile in the users home directory echo press any key to close this window read THISKEY exec Since you really want to lock out everything but mysql, the username of the shell account can be the same for everyone. Whatever is doing the remote connection can worry about the connection info to the database... Luc -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 2:24 PM To: Muir, Michael (OTS-EDH); 'Andy Eastham'; Mysql@Lists. Mysql. Com Subject: RE: Mysql Encryption As far as I know you have to have a shell account to use ssh tunnels, am I right? If so this may be a concern for some. Mike -Original Message- From: Muir, Michael (OTS-EDH) [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 11:01 AM To: 'Andy Eastham'; Mysql@Lists. Mysql. Com Subject: RE: Mysql Encryption PuTTY is a nice freeware SSH client. http://www.chiark.greenend.org.uk/~sgtatham/putty/ -mike -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 8:39 AM To: Mysql@Lists. Mysql. Com Subject: RE: Mysql Encryption Don't forget that SSH (eg OpenSSH) can tunnel regular port connections too. This is dead easy to set up with a client such as SecureCRT from Van Dyke, but this is a paid product (although worth the money in my opinion - I own it). They also do Entunnel which is cheaper product which just does secure tunnelling. But as this tunelling is a fundamental part of SSH, there must be some open source implementation too? Anyone else like to add to this? Andy mysql query -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED]] Sent: 18 November 2002 13:54 To: Alexandre Aguiar; Fraser Stuart Cc: [EMAIL PROTECTED] Subject: RE: Mysql Encryption A windows versionof Stunnel is available from the stunnel website (www.stunnel.org), I would reccomend using it for your needs. Mike Hillyer -Original Message- From: Alexandre Aguiar [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 6:09 AM To: Fraser Stuart Cc: [EMAIL PROTECTED] Subject: Re: Mysql Encryption On 14 Nov 2002 Fraser Stuart shaped the electrons to write something about [Mysql Encryption] We are about to embark on a project that requires data encryption - mainly to stop sensitive information being viewed accidentally (ie Isnt it possible to tunnel MySQL connections through ssl? Under Linux stunnel does a great job encrypting protocols that use a single port for connections. I think it would not be hard to port stunnel to other platforms. Under Windows I guess PuTTY (freeware, source available) or one of its related applications (Plink?) (http://www.chiark.greenend.org.uk/~sgtatham/putty/) can handle client side tunneled connections. HTH, Alexandre Aguiar - 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 - 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 - 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 Notice: This e-mail and any attachments are intended only for the individual or company to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure or unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please
RPMs and glibc
Just looking for confirmation on this one. If I install the .53a RPM on Red Hat 8.0 ( fresh install ), will it work right ( with the know issues at least ) Luc sql - 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
RE: using libmysqld - can't connect to remote DB as client.
not sure if this applies here, its from the c++ api docs (but why switch it around) Do you have the parameters sent in the right order? // The full format for the Connection constructor is // Connection(cchar *db, cchar *host=, //cchar *user=, cchar *passwd=) I really know nothing about the c, c++ api's, just trying to help -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 3:58 PM To: Black, Kelly W [PCS]; Mysql list Subject: RE: using libmysqld - can't connect to remote DB as client. I am. See the original post: // Here's the remote connection - it connects to the same local machine, not the remote machine. two = db_remote_connect (remote_machine,remote,remote,dbname); db_do_query(two, SHOW DATABASES); --- Black, Kelly W [PCS] [EMAIL PROTECTED] wrote: So use the C syntx instead. :) -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 12:21 PM To: Black, Kelly W [PCS]; Mysql list Subject: RE: using libmysqld - can't connect to remote DB as client. Ok, but I'm writing this in C, not perl. - Steve --- Black, Kelly W [PCS] [EMAIL PROTECTED] wrote: I think your problem might be here - you have db_connect(my_blah_database); Try using something like my $dbh = DBI-connect(DBI:mysql:$dbname:hostname, $dbuser, $dbpasswd) || die can't connect: $DBI::errstr\n; Also make sure you include the || die can't connect: $DBI::errstr\n; as in this way you can get some useful error output about the connect. Not certain but at least I am trying to help :) Oh and don't forget sql query! :) ~Kelly W. Black -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 11:20 AM To: Mysql list Subject: Re: using libmysqld - can't connect to remote DB as client. Still nobody has answered my question. Has anyone here even tried linking with libmysqld.a before? - Steve --- Steven Webb [EMAIL PROTECTED] wrote: Nobody answered my question, so I felt like I should ask again ... I'm using libmysqld to embed a mysql server in my C app. However, I'd also like to connect to other remote mysql databases as well, but the mysql_real_connect command doesn't seem to be working when trying to connect to a remote database in the same app as the libmysqld stuff. The connect works, but I think that it's connecting to my local database and not the remote one. Here's a little code: mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups); // Here's the embedded database. This works. one = db_connect(NULL); db_do_query(one, CREATE DATABASE my_blah_database); mysql_close(one); one = db_connect(my_blah_database); db_do_query(one, create table blank (num int)); mysql_close(one); /* This must be called after all other mysql functions */ mysql_server_end(); // Here's the remote connection - it connects to the same local machine, not the remote machine. two = db_remote_connect (remote_machine,remote,remote,dbname); db_do_query(two, SHOW DATABASES); - 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
RE: BETWEEN
sql,query Would it be possible to supply BETWEEN in any order (or rather to allow to do that)? Such as WHERE timestamp BETWEEN 2002100100 AND 20021031235959 would get the same results with WHERE timestamp BETWEEN 20021031235959 AND 2002100100 or any other case where BETWEEN can be used it really is BETWEEN those values wouldn't that be just a simple greater than check - 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
RE: Difficult query
Or, you could use distinct and substring_index instead of my last convoluted answer :) -Original Message- From: Kip Turk [mailto:kipt;wcc.net] Sent: Monday, November 04, 2002 4:17 PM To: Alexander Newald Cc: mySQL Mailing Liste Subject: Re: Difficult query On Mon, 4 Nov 2002, Alexander Newald wrote: Hello, I have a table with only ips in it. I want to get all networks the ips belong to: Example: 123.123.123.1 123.123.132.2 123.123.123.3 1.1.1.4 1.1.1.5 1.1.1.6 12.12.12.1 12.12.12.2 12.12.12.10 The result should be: 123.123.123.0 1.1.1.0 12.12.12.0 Instead of converting on matching row (for example 123.123.123.2) to 123.123.123.0 it can be left as 123.123.123.2 or even can be truncated to 123.123.123 The table is stored on a 3.23.43 mySQL Server. The table type is varchar(15) but this can be changed. Possibly not the most optimized solution, but it functions as requested. select distinct(concat(substring_index(field, '.', 3), '.0')) as network from table order by network; field and table should be replaced with your actual names of course. - 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
RE: Understanding MySQL column type SET
mysql CREATE TABLE settest( chain SET( A, B, C ) NOT NULL,
UNIQUE INDEX ichain( chain ) );
mysql INSERT INTO settest SET chain=A,C;
mysql SELECT * from settest;
+---+
| chain |
+---+
| A,C |
+---+
mysql SELECT * FROM settest WHERE chain=A,C;
+---+
| chain |
+---+
| A,C |
+---+
mysql SELECT * FROM settest WHERE chain=C,A;
Empty set (0.00 sec)
++From Manual: http://www.mysql.com/doc/en/SET.html
++SET values are sorted numerically. NULL values sort before non-NULL SET values.
That means when you create SET(A,B,C) they are assigned the decimal values of
1,2,4
++mysql SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
++mysql SELECT * FROM tbl_name WHERE set_col 1;
++The first of these statements looks for an exact match. The second looks for values
+containing the first set member.
Since they are sorted numerically, and WHERE chain=C,A is looking for an exact
match, it will not find it.
mysql SELECT * FROM settest WHERE FIND_IN_SET(C,A, chain);
Empty set (0.01 sec)
++Normally, you perform a SELECT on a SET column using the LIKE operator or the
+FIND_IN_SET() function:
++mysql SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
++mysql SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)0;
Looks to me these are expecting value (singular) not values
And regardless all of them are looking for exact matches against how the data is stored
If you were to create with SET(C,A,B) looking for FIND_IN_SET(C,A,chain) would
probably work
Anyways, not considering myself a Guru, but that's how I am seeing it
Luc
-
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
RE: MySQL Release Dates
-Original Message- From: Lenz Grimmer [mailto:lenz;mysql.com] Sent: Thursday, October 24, 2002 3:21 PM To: [EMAIL PROTECTED] Cc: Jennifer Goodie; Luc Foisy Subject: Re: MySQL Release Dates We usually note down the date when a release was tagged in the BK tree. The general availibilty of the packages is usually some time later, since the build and test procedures take some time. If you're more interested in this date, check the announce list archives on lists.mysql.com. The dates available at http://www.mysql.com/doc/en/News.html are just fine for what I need. Just tracking installations that need to be upgraded ( since some of the servers can't get upgraded all the time they sometimes get left behind and some of them just get forgotten about ). Now I can query MySQL server to tell me that I need to be upgraded :) Thanks Lenz.. - 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
RE: MySQL Release Dates
Great thanks! hmm I have 2002-10-17 for 3.23.53a taken from the post date of Lenz Grimmer's mail I'd like to correct 3.23.49 to be 3.23.49a, does anyone know what the release date is for this version? --- Thats weird D.2.1 Changes in release 3.23.54 ?? Somone sure is all geared up for the next release :) Luc -Original Message- From: Jennifer Goodie [mailto:goodie;apollointeractive.com] Sent: Thursday, October 24, 2002 3:05 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: MySQL Release Dates From looking at http://www.mysql.com/doc/en/News.html it looks like 3.23.32 22 Jan 2001 3.23.36 27 Mar 2001 3.23.49 ? 3.23.52 14 Aug 2002 3.23.53a ? Or maybe that is when the change log was released? I'd look around the site if I were you. -Original Message- From: Luc Foisy [mailto:Luc.Foisy;technical-magic.com] Sent: Thursday, October 24, 2002 11:51 AM To: MYSQL-List (E-mail) Subject: MySQL Release Dates We are trying to track the software we are using.. Could anyone supply the release dates of the following versions of MySQL ( will assume server and client are the same ) And perhaps a retired date ( which would probably be when the next actual release was available ) 3.23.32 3.23.36 3.23.49a 3.23.52 3.23.53a Thanks! - 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 Release Dates
We are trying to track the software we are using.. Could anyone supply the release dates of the following versions of MySQL ( will assume server and client are the same ) And perhaps a retired date ( which would probably be when the next actual release was available ) 3.23.32 3.23.36 3.23.49a 3.23.52 3.23.53a Thanks! Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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
RE: Query Spans Multiple Pages
-Original Message- From: William Martell [mailto:[EMAIL PROTECTED]] I am trying to query MySQL using PHP and I would like the results to display on multiple pages. I would like to display only 20 results per page and allow the user access to the other pages by clicking next or an index of numbers 1 2 3 4 5 6 and so on dependent upon the number of results returned. I do not know how to do this?? Can anyone point me in the right direction??? First you would probably query for the number of records that would be returned. That way you can create your navigation bar with the correct amount of increments ( 1 2 3 4 5 6 ). Each increment = 20 records. Then you can perform queries using LIMIT ( LIMIT 0,19 ... LIMIT 20,39 ... LIMIT 40,59 etc ). Also, When a user does perform this query, will all of the pages be created at that time. Or will the page be created with another request and response from the server. One page at a time using LIMIT. You could create all the pages at once, but where are you going to put them, how are you going to serve them... I think you should just query each page of data when the user wants it. In other words. Does the result of the query get stored in a variable that is accessed on the client machine or does the php code return the limit per Hey look, you almost got a solution yourself... Luc - 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
RE: Replication question
That would be a great way to keep your website up to date. The slave can handle loss of connection (though some data may not make sense if part of it is missing), and resume where it has left of previously. Or you could also manually start and stop the slave process when you know there is a live connection. As long as you are not modifying the slave database, that could cause problems. Some people have successfully looped thier replication process (A-B, B-A), which I know nothing about. I am assuming you want to have the hosting companies server replicate from your local copy of the database? If that is the case, starting and stopping the slave process is not really an option (unless you have your own install of mysql). And of course if you do not have a static IP address it would be a real pain in the behind ( since your hosting company will probably be the one changing the slave access, unless of course you have your own install ) Good luck Luc -Original Message- From: Alexander Shaw [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 28, 2002 5:47 AM To: MySQL List Subject: Replication question Hi Is it feasible to use replication to keep the database which powers my website up-to-date? The database itself is on the hosting companies server and contacted via dialup so the connection isn't there all of the time. Naturally I keep a copy of the database on my local machine too. Alex Alexander Shaw Agricultural Stock and Assignment Photography www.Agri-Image.com sql query - 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
RE: Database Replication
-Original Message- From: Paul Darius [mailto:[EMAIL PROTECTED]] Subject: Database Replication 'lock database with read lock' command already done before the above replication started and followed by 'unlock all' when finished. Question, how to sync an unsync table or the whole database again ? I am not quite sure what your replication process entails, but I am not sure that its what I call replication. What you are describing just sounds not suite right to me. Check this link out. http://www.mysql.com/doc/search.php?q=replicationfrom=%2Fdoc%2Fen%2Findex.html - 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
RE: Setting Permission For Column Level Only
You know that process would be much easier if you used the GRANT syntax http://www.mysql.com/doc/search.php?q=GRANTfrom=%2Fdoc%2Fen%2Findex.html -Original Message- From: Insanely Great [mailto:[EMAIL PROTECTED]] Subject: Setting Permission For Column Level Only Greetings... I want to create a user which will have only Select privileges in two columns a particular table. For that I creates a entry in the User table in MySQL and set it as default with N in all. Also I entered some rows in the Columns_Priv table. Now my question is - Do I have to enter records in db and tables_priv table with the user name and all privileges set to N or default to make the column privileges work or if I enter a row in the columns_priv table, that will do. Rgds Insane - 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
RE: IFNULL || NULLIF
http://www.mysql.com/doc/en/Control_flow_functions.html top of the page -Original Message- From: Gramos Brestovci [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:47 AM To: [EMAIL PROTECTED] Subject: IFNULL || NULLIF Does MySQL 3.23.39(version) support IFNULL(NULL,0) || NULLIF(0,NULL) Thanks, Gramos - 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 - 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 and RedHat 7.3
When I installed Mysql ( whatever the current 3.x series is on RedHat 7.3, the service did not start when I restarted. If you have gnome installed, you can acess Service Configuration and set MySQL to start when system boots. Just information for those that may experience this. Luc Foisy - 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
RE: granting does not give the correct privileges
Independent table permissions are stored in the 'db' table. Base user permissions are stored in the 'user' table. -Original Message- From: Nestor Florez [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 12:52 PM To: [EMAIL PROTECTED] Subject: granting does not give the correct privileges Is this an error in Mysql? I do the follwing command from a shell grant insert on mydb.* to username@localhost identified by user 'pwd'; When I check the permissions the insert_priv field on user table of mysql db is set to 'N'. Why is that? I can change the privileges by giving an update command. Any ideas?, Nestor :-) - 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 - 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
RE: mysql -p as simple user ?
mysql -u root -p This will enter as user 'root' -Original Message- From: hans schneidhofer [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 1:00 PM To: [EMAIL PROTECTED] Subject: mysql -p as simple user ? hi, have done a succesful update 3.23.36 to 2.23.47 on my mandrake-box 8.2, but have now that question : as user root it is possible to login now with mysql -p the password-question appears, typing in the root-password is okay and I can see the prompt mysql but as a simple user and doing all the steps above I get : [papabaer@hanna papabaer]$ mysql -p Enter password: ERROR 1045: Access denied for user: 'papabaer@localhost' (Using password: YES) is it not possible, doing a login as a simple user - not only as root ? thought, using mysql -p does the trick ? or may I have to do some changes more ? and if so, where I have to do that steps ? what I have to do there ? hope someone can help me. I know, this is a really question for dummies but I don't know it. thanks and bye hans - 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 - 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
3.23.52 daemon issue
I just finished installing RedHat 7.3 on two computers. One a server install, the other a laptop install. I proceeded at that point to install MySQL 3.23.52( the RPMs from www.mysql.com) on both. MySQL had been started on both after the install (which is pretty normal). I rebooted both, MySQL started up on boot on the server, but it did not on the laptop. I can start MySQL manually on the laptop. Does anyone know of anything I might look at here? The laptop is an IBM ThinkPad 770x, 192MB RAM (actually I think it is the basic setup of that model you can buy from IBM) Luc Foisy - 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
Windows Lowercase issue
We have a database running on a linux box, we decided to replicated it to one of our NT boxes(this was a while ago, so a large amount of data has been transfered). So anyways, at the time I didn't know there would be an issue. So recently I dumped the data and imported it into our development database on another linux box. All of our table names were originally upper case and the NT has shafted that. I am assuming the data will have correct casing, please correct me if I am wrong. So now I want to make all the tables uppercase ( that would be much simpler, I am hoping, then changing all our programming and other database entries that refer to capitolized table names). I am sure it can't be as simple as just writing a script to rename the files. Is there something else I would need to do to change the case of the tables? Luc Foisy sql,mysql - 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
One of those What the heck?! messages
Can anyone tell me what this means??? Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.058 Pos: 52706154 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: Last_errno: 4294967295 Last_error: error 'unexpected success or fatal error' on query 'bslive' Luc Foisy - 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
RE: One of those What the heck?! messages
On Thu 2002-09-05 at 09:29:41 -0400, [EMAIL PROTECTED] wrote: Can anyone tell me what this means??? Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.058 Pos: 52706154 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: Last_errno: 4294967295 Last_error: error 'unexpected success or fatal error' on query 'bslive' It means that an statement that failed on the master, succeeded on the slave and therefore the slave cannot be not in sync with the master for some reason. This may happen, if e.g. you run an UPDATE which affects the whole table, but *after* already updating some rows, it gets an duplicate-key error. The query is then logged and send to the slave and is expected to do the same on the slave, i.e. update some rows and then bail out. But for some reason it did not get the same error, but succeeded instead. And this is considered an error, because it shows an inconsistency, and therefore manual intervention. Are you saying the slave was manually altered? Hmm. I started up the slave again by the way and it continued on like nothing happened. I don't think what you are saying is entirely correct. Look at the following two lines. Replicate_do_db: qbslive Last_error: error 'unexpected success or fatal error' on query 'bslive' Doesn't that look like something is looking in the wrong place?? - 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
RE: Problem with ORDER BY
turn them back into numbers with a math function
ORDER BY ABS(value)
-Original Message-
From: Jürgen Müller [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 30, 2002 5:23 PM
To: [EMAIL PROTECTED]
Subject: Add: Problem with ORDER BY
I've changed my query to:
$order = SUBSTRING_INDEX(objektname,'-',1);
$order .=
,RIGHT(objektname,(LENGTH(objektname)-LOCATE('-',objektname)));
so that the syntax ist okay, and the tests too:
mySQL returns the correct values:
objekt SUBSTRING_INDEX(objekt,'-',1) LENGTH(objekt)
LOCATE('-',objekt) RIGHT(...)
MD1-1234 MD1 8
4 1234
MD1-32MD1 6
4 32
SD1-1 SD1 5
4 1
But the search-results are not ordered corectly. Perhaps i
can transform
the numbers int integer? I think they are stil handled as string...
Greetings,
Jürgen
-
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
-
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
RE: Bug ?
It think MySQL could very well have corrupted the data back in 3.23.38 for Marian when I first started out using mysql, I was fairly new to linux and totally new to MySQL MySQL default install went to /var/lib/mysql, default RedHat /var is quite small So unknowingly to me, it filled up quite quickly, and MySQL did indeed keep on chugging, and my data did indeed get totally trashed Since no other daemons were running that would write to /var (not even mail) then my finger was pointing at mysql I was also running replication at the time, so that may have also been a problem, mind you I was able to succesfully restore from the slave datasets ( i believe it did not send any more updates to the slave once the filesystem was full ) dunno, just an experience I had to share -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Monday, September 02, 2002 10:22 AM To: Nairam Cc: [EMAIL PROTECTED] Subject: Re: Bug ? Marian wrote: Why mysql corrupt tables if filesystem if full ... ? On good database servers (eg. PROGRESS) process shutdown server (protetcting data). sytem: linux-2.2.19 mysql-3.23-38 - 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 What table types are you using? MySQL doesn't usually corrupt tables when the disk is full, it handles it in the following way: http://www.mysql.com/doc/en/Full_disk.html Also, you're using a pretty old version of MySQL, you might want to upgrade to a newer version if possible. The latest version in the 3.23 series is 3.23.52 -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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
RE: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] * Harald Fuchs In article [EMAIL PROTECTED], Victoria Reznichenko [EMAIL PROTECTED] writes: As you can see txt1 and txt2 contain text file ~ 8M UPDATE tbl1 SET total=CONCAT(txt1,txt2) WHERE id=1; SELECT id, LENGTH(txt1), LENGTH(txt2), LENGTH(total) FROM tbl1; ++--+--+---+ | id | LENGTH(txt1) | LENGTH(txt2) | LENGTH(total) | ++--+--+---+ | 1 | 8390060 | 8390060 | 0 | ++--+--+---+ The same result. MySQL inserts NULL in the total, because you can't store data more than max_allowed_packet. Thanks for the clarification, Victoria. I'm sorry, but this is not very clear to me... The manual describes max_allowed_packet as Max packetlength to send/receive from to server. Why are the columns transferred between server/client in the above statement? Shouldn't the entire UPDATE happen on the server side? It probably does happen on the server side. But wouldn't it be smart to limit itself to something it knows it can't transfer later? - 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
Query Question
Some ficticious data ID Data1 Data2 Data3 1 1 141 1 2 5 140 1 3 4 142 1 4 3 141 1 5 2 142 1 6 5 142 1 7 7 140 1 8 2 143 1 9 3 142 1 10 3 143 1 11 4 144 1 12 2 144 1 13 8 141 2 14 9 140 2 15 6 142 2 16 11 141 2 17 0 142 2 18 9 142 2 19 10 140 2 20 0 143 2 21 11 142 2 22 3 143 2 23 6 144 2 24 0 144 2 For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 I would like those records to include the group that does not contain a Data2 value of 141 There is also a WHERE clause on Data3 = 1 The return would include these values for Data1 7 5 2 4 Is there some way to do this with a single query? If this is unclear, let me know Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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
RE: Query Question
I suppose I should have specified that I would not be able to filter by Data1 -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 11:52 AM To: Luc Foisy Subject: RE: Query Question select * from xxx where data2 141 and data3 = 1 and (data3 = 7 or data3 = 5 or data3 = 2 or data3 = 4) groupby data1 -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 10:32 AM To: MYSQL-List (E-mail) Subject: Query Question Some ficticious data IDData1 Data2 Data3 1 1 141 1 2 5 140 1 3 4 142 1 4 3 141 1 5 2 142 1 6 5 142 1 7 7 140 1 8 2 143 1 9 3 142 1 103 143 1 114 144 1 122 144 1 138 141 2 149 140 2 156 142 2 1611 141 2 170 142 2 189 142 2 1910 140 2 200 143 2 2111 142 2 223 143 2 236 144 2 240 144 2 For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 I would like those records to include the group that does not contain a Data2 value of 141 There is also a WHERE clause on Data3 = 1 The return would include these values for Data1 7 5 2 4 Is there some way to do this with a single query? If this is unclear, let me know Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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 - 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
RE: Query Question
That would filter out the records containing 141 before it did the group by That would mean I could get a group that had 141 in it 4 3 141 1 9 3 142 1 103 143 1 So, here it would not event look at record 4, then it would return me a group that contains a Data1 value of 3, but I do not require that result because it's original group contains 141 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 11:58 AM To: Luc Foisy Cc: MYSQL-List (E-mail) Subject: Re: Query Question :ID Data1 Data2 Data3 :1 1 141 1 :2 5 140 1 :For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 : :I would like those records to include the group that does not contain a Data2 value of 141 : :There is also a WHERE clause on Data3 = 1 : :The return would include these values for Data1 : :7 :5 :2 :4 : Try this, if you haven't already SELECT ID, Data1 FROM sm_table WHERE Data1 != 141 GROUP by Data1; - 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
RE: Query Question
Because I don't know what Data1 is, thats the result I want to find -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 12:10 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: Query Question Why cant you filter on Data1? I meant select * from xxx where data2 141 and data3 = 1 and (data1 = 7 or data1 = 5 or data1 = 2 or data1 = 4) groupby data1 -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 10:51 AM To: MYSQL-List (E-mail) Subject: RE: Query Question I suppose I should have specified that I would not be able to filter by Data1 -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 11:52 AM To: Luc Foisy Subject: RE: Query Question select * from xxx where data2 141 and data3 = 1 and (data3 = 7 or data3 = 5 or data3 = 2 or data3 = 4) groupby data1 -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 10:32 AM To: MYSQL-List (E-mail) Subject: Query Question Some ficticious data ID Data1 Data2 Data3 1 1 141 1 2 5 140 1 3 4 142 1 4 3 141 1 5 2 142 1 6 5 142 1 7 7 140 1 8 2 143 1 9 3 142 1 10 3 143 1 11 4 144 1 12 2 144 1 13 8 141 2 14 9 140 2 15 6 142 2 16 11 141 2 17 0 142 2 18 9 142 2 19 10 140 2 20 0 143 2 21 11 142 2 22 3 143 2 23 6 144 2 24 0 144 2 For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 I would like those records to include the group that does not contain a Data2 value of 141 There is also a WHERE clause on Data3 = 1 The return would include these values for Data1 7 5 2 4 Is there some way to do this with a single query? If this is unclear, let me know Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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 - 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 - 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
RE: Query Question
Yes that's it, Thanks! Ignore that correction. It seems that what you want is indeed this: SELECT Data1 FROM table_name WHERE Data3 = 1 GROUP BY Data1 HAVING SUM(Data2 = 141) = 0; [Filter fodder: SQL] Luc - 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
JOINS one table twice
Since the below select query blew up on me, then I must be doing something wrong, perhaps it is because I am using the wrong type of join, dunno I would like to join the same table twice, with different criteria as below Anyone got a solution to this? SELECT EVENTATTENDANCE.ID, CONCAT(IF(SALUTATION.Name IS NOT NULL,CONCAT(SALUTATION.Name,' '),''),IF(FirstName IS NOT NULL, FirstName, ''),' ', IF(LastName IS NO T NULL, LastName, '')) AS 'Contact' , ORGANIZATION.Name AS 'Organization', RELATIONSHIP.Name AS 'Relationship', STATUS.Description AS 'Status' FROM EVENTATTENDANCE LEFT JOIN EVENT ON EVENTATTENDANCE.ID_EVENT = EVENT.ID LEFT JOIN CONTACT ON EVENTATTENDANCE.ID_CONTACT = CONTACT.ID LEFT JOIN STATUS ON EVENTATTENDANCE.ID_STATUS = STATUS.ID LEFT JOIN ORGANIZATION ON CONTACT.ID_ORGANIZATION = ORGANIZATION.ID LEFT JOIN TYPE RELATIONSHIP ON CONTACT.ID_TYPE_RELATIONSHIP = TYPE.ID LEFT JOIN TYPE SALUTATION ON CONTACT.ID_TYPE_SALUTATION = TYPE.ID WHERE EVENTATTENDANCE.ID_EVENT = -1 ORDER BY CONTACT.FirstName SQL Error in populateDataSet == Please Restart The Application == SQLException: General error: Unknown table 'TYPE' in on clause Luc Foisy - 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
RE: JOINS one table twice
Luc Foisy wrote: Since the below select query blew up on me, then I must be doing something wrong, perhaps it is because I am using the wrong type of join, dunno I would like to join the same table twice, with different criteria as below Anyone got a solution to this? SELECT EVENTATTENDANCE.ID, CONCAT(IF(SALUTATION.Name IS NOT NULL,CONCAT(SALUTATION.Name,' '),''),IF(FirstName IS NOT NULL, FirstName, ''),' ', IF(LastName IS NO T NULL, LastName, '')) AS 'Contact' , ORGANIZATION.Name AS 'Organization', RELATIONSHIP.Name AS 'Relationship', STATUS.Description AS 'Status' FROM EVENTATTENDANCE LEFT JOIN EVENT ON EVENTATTENDANCE.ID_EVENT = EVENT.ID LEFT JOIN CONTACT ON EVENTATTENDANCE.ID_CONTACT = CONTACT.ID LEFT JOIN STATUS ON EVENTATTENDANCE.ID_STATUS = STATUS.ID LEFT JOIN ORGANIZATION ON CONTACT.ID_ORGANIZATION = ORGANIZATION.ID LEFT JOIN TYPE RELATIONSHIP ON CONTACT.ID_TYPE_RELATIONSHIP = TYPE.ID LEFT JOIN TYPE SALUTATION ON CONTACT.ID_TYPE_SALUTATION = TYPE.ID WHERE EVENTATTENDANCE.ID_EVENT = -1 ORDER BY CONTACT.FirstName SQL Error in populateDataSet == Please Restart The Application == SQLException: General error: Unknown table 'TYPE' in on clause Your Table is named TYPE? Bad idea, because it is a reserved word in all SQL dialects I know of. try 'TYPE' instead of TYPE, but I really recommend to rename that table...You'll run into trouble all the time and all coders/developers who'll work with that DB will hate you for eternity ;-) TYPE is not a reserved word. We have used TYPE for the last 2 years, created several databases with that table name, used it many times in many many places. MySQL has not complained once before now. And TYPE is not here http://www.mysql.com/doc/R/e/Reserved_words.html That can't be the problem, it has to be something with the joins. - 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
RE: JOINS one table twice
On 2 Aug 2002, at 11:43, Luc Foisy wrote: FROM EVENTATTENDANCE LEFT JOIN EVENT ON EVENTATTENDANCE.ID_EVENT = EVENT.ID LEFT JOIN CONTACT ON EVENTATTENDANCE.ID_CONTACT = CONTACT.ID LEFT JOIN STATUS ON EVENTATTENDANCE.ID_STATUS = STATUS.ID LEFT JOIN ORGANIZATION ON CONTACT.ID_ORGANIZATION = ORGANIZATION.ID LEFT JOIN TYPE RELATIONSHIP ON CONTACT.ID_TYPE_RELATIONSHIP = TYPE.ID LEFT JOIN TYPE SALUTATION ON CONTACT.ID_TYPE_SALUTATION = TYPE.ID WHERE EVENTATTENDANCE.ID_EVENT = -1 ORDER BY CONTACT.FirstName SQL Error in populateDataSet == Please Restart The Application == SQLException: General error: Unknown table 'TYPE' in on clause You've assigned the aliases RELATIONSHIP and SALUTATION to the TYPE table, so you need to use those in place of TYPE for your join criteria. Change TYPE.ID to RELATIONSHIP.ID and SALUTATION.ID. Ahh, so LEFT JOIN TYPE SALUTATION ON CONTACT.ID_TYPE_SALUTATION = SALUTATION.ID ?? - 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
RE: Do you run multiple servers on WIndows?
I would, very much, like to know if anyone has been able to do this. NOTE: Multiple MySQL servers does not necessarily mean running multiple MySQL services (just to clarify to another reply on this) You can have multiple MySQL servers running on Unix system with some configuration in your my.cnf and using multi_mysqld instead of safe_mysqld http://www.mysql.com/doc/m/y/mysqld_multi.html As far as I can tell, the windows version does not have a multi_mysqld and I can find no other configuration choices to do it. The first line of the above page reads mysqld_multi is meant for managing several mysqld processes running in different Unix sockets and TCP/IP ports. If there is no ability for the windows version to do this, then here is my vote to get that done some time -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 12:35 PM To: [EMAIL PROTECTED] Subject: Do you run multiple servers on WIndows? It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - 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 - 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
RE: how to update time field with current time?
It would be a lot easier for you to have just used a timestamp, it automatically updates itself when a record is modified. Otherwise you would just update the time field in question when you do your other updates SET timefield = NOW() should work Though using a TIME datatype is not very informative as it only stores the time 00:00:00, and we all know that that time happens once a day, every day for the rest of eternity :) -Original Message- From: Dermot Paikkos [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 12:46 PM To: [EMAIL PROTECTED] Subject: how to update time field with current time? Hi Probably a silly question but how can I set a time field to the current time with update in mysql? I have 4 time fields per record. Should I have used set them up as timestamp? Thanx. Dp. ~~ Dermot Paikkos * [EMAIL PROTECTED] Network Administrator @ Science Photo Library Phone: 0207 432 1100 * Fax: 0207 286 8668 - 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 - 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
RE: help !
-Original Message- I was trying to connect into an MYSQL database, and I keep getting the error message Can't connect to MySQL server on '10.10.10.250' (10054) but I know that the database is running and I can connect fine on the server itself! Any suggestions would be greatly appreciated. You probably have only granted permission, for the user you are connecting as, to the localhost where MySQL is installed use mysql; SELECT * FROM user\G This will show all users able to connect to mysql server and from what host address. Check the ip of the computer you are connecting from and see if it in there. If not, try: GRANT some privileges on database.table to 'username'@'10.10.10.%' identified by 'password'; ^^^ can be wildcard *.* or database.* Check the GRANT section in the mysql manual. If you are connecting to the server from windows, you may want to use the -u option, which is the user name on the mysql server, just incase your windows username does not match : mysql -u username hmm, what else... try that for now - 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
RE: MySQL install on Linux 7.1
-Original Message- Hey can someone provide me some help in installing and configuring mysql ver3.23.51 on my Linux server? I downloaded the file from mysql.com to my servers hard drive and now want to install it. I right-clicked the installation file and clicked install. It appears to have installed something, but I can't find it. I looked in the /var/lib directories and don't see anything. Though the installation steps are covered somewhat on mysql.com, I'm still having problems as I'm totally new to linux. Any documented steps from a newbie's perspective or something simple would be really helpful. Any help is appreciated. Default RedHat mysql server rpm install will go to /var/lib/mysql make sure you installed the server, and not just the client run: find /usr/bin -name *mysql* or look in /usr/bin if you don't see safe_mysqld then you haven't installed the server - 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
FW: calculating inside query
Woops, sent this to support. Sorry Support! -Original Message- From: Luc Foisy Sent: Wednesday, July 10, 2002 1:37 PM To: 'Mysql-Support (E-mail) Subject: RE: calculating inside query -Original Message- Hi there, I would like to find out the top 10 of some ratings. There are 2 columns. One for the rating and one for the amount of ratings. To find out the top 10 I would have to divide those 2 values and sort it. Somehow this does not work. I do not have a clue why. Maybe someone of you guys do see the error: SELECT ID, rating_total / ratings_amount AS top10 FROM rating ORDER BY top10 desc LIMIT 10 Thanx for any help, Andy query Try ORDER BY (rating_total / ratings_amount) desc and you may want to quote your alias AS 'top10' - 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
RE: Grants UGGH! Not working for some reason ...
[snip] if you don't restart MySQL after changing the grants priviliges, you have to issue the flush privileges command from the MySQL Monitor. If you don't do either one your privilege changes will not take affect. not sure if when you said reloaded you meant flush privileges but if not try it and see how it goes. [/snip] That's what I meant. I have reloaded MySQL, flushed, stopped and started the server. The killer is that this user is able to access the database just hunky-dorie, do selects, inserts, deletes, updates. Thanks! Jay sql, mysql, query If you use the GRANT command, you don't have to flush privileges. If you UPDATE,INSERT your own grant records, then you have to flush. Try granting FILE privilege. mysqlimport is LOAD DATA INFILE like, which needs the FILE privilege - 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
RE: Basic Q's: Numerical Sorting
Here is the solution I posted about t months ago. I would also add, this solution can handle both text and numbers in the same field...: my boss figured out a nice solution to this ORDER BY IF(ABS(Item) 0,LPAD(Item,9,'0'),Item) This will pad numbers ( ok it might not do so hot with DECIMAL, but it can be fixed to work I supposed) with 0 So it should sort 1 2 00010 00022 00050 00230 instead of 1 10 2 22 230 50 And it will skip padding text cause I guess the ABS of text is 0, and the numbers and words will appear separated from each other My boss is smart :) -Original Message- From: CVIOG at UGA [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 09, 2002 4:22 PM To: [EMAIL PROTECTED] Subject: Basic Q's: Numerical Sorting I have a fairly basic question: How do I sort numerically? Normally when I query using ORDER BY (field), it orders by the first digit (i.e. 1, 10, 2, 21, 3, 32) rather than by number (1, 2, 3, 10, 21, 32). Thanks for any help Dave - 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
Error
My Slave stopped replicating. SHOW SLAVE STATUS *** 1. row *** Master_Host: 209.217.92.34 Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.049 Pos: 56605370 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: Last_errno: 1054 Last_error: error 'Unknown column 'PrinintLe' in 'field list'' on query 'UPDATE PRINTJOBDATA SET ID_PRINTJOB = '55986', ID_PRINTERCODE = '1', PrinintLe = ' 26/06 32440615:27 Doris 37 5330 Canotek 1794 Woodward ECO4.95 ', Sequence = '35' WHERE PRINTJOBDATA.ID = 5328541' Skip_counter: 0 -- When replication receives stuff like 'Unknown column' errors, can't it just go back and try it again? It probably just lost some data on the transfer. MySQL doesn't log erroneous queries ( does it? ) so it should know that there was some kind of transfer error. 'PrinintLe' should be 'PrintLine'. It seems to have moved 'in' back a few characters. I restarted the slave and off it went, doing its merry little chore :) I don't know what I am hoping for here, just griping about it :) Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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
RE: timestamp problem ..
The first timestamp in any table is automatically updated by mysql every time you modify that record. It is the modify timestamp If you wish to use the timestamp in your table, you should create two timestamps at least and use the second one Modstamp timestamp Usable timestamp See here http://www.mysql.com/doc/D/A/DATETIME.html starting on the fourth paragraph I'll take my beer via air mail :) -Original Message- From: Wouter van Vliet [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 9:53 AM To: [EMAIL PROTECTED] Cc: Wouter @ Witbier Subject: timestamp problem .. Heey Folks, I'm having a slight problem with the timestamp column format. When I alter a table and, add a column of type timestamp all records get the current timestamp, that's ok. When i insert a new row, all records get the current timestamp. That too is ok. But now, when I update one row of the table, that row gets a new timestamp. And that's not what i'd like it to do. Does somebody have any idea on how this can be prevented? I've tried to make the column of type int(14) and then set now() or UNIX_TIMESTAMP as default value, but that just results in a very well known error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1). Thanks ! Wouter (ps. beer for the helper .. if you'd come up with some idea to give it to you) - 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 - 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
RE: is this query possible?
How bout SELECT main.id, sub1.other, sub2.other FROM main LEFT JOIN sub1 ON main.sub1fk = sub1.id LEFT JOIN sub2 ON main.sub2fk = sub2.id Luc mysql,sql -Original Message- From: Erik Price [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 3:54 PM To: [EMAIL PROTECTED] Subject: is this query possible? I have a query that I have in mind, but am not sure of how I can actually write it. It might not even be possible. I was hoping someone could tell me if I will have to use two queries instead, or if this will actually work: (In simplified form:) ++ +---+| main | | sub1 |+++---+ +---+| id || sub2 | | id|---| sub1fk |+---+ | other || sub2fk |---| id| +---+++| other | +---+ As you can see from the simple diagram, I have a main table with its own primary key (id) but with two foreign key columns. The first one (sub1fk) points to the primary key of the table sub1. The second one (sub2fk) points to the primary ky of the table sub2. The query I'm trying to build would look something like this: SELECT main.id, IF(main.sub1fk,sub1.other,NULL) AS sub1other, IF(main.sub2fk,sub2.other,NULL) AS sub2other FROMmain, sub1, sub2 WHERE main.id = some_criteria_or_other AND sub1.id = main.sub1fk AND sub2.id = main.sub2fk; The above SQL, of course, won't work -- because there are no situations where all of the WHERE clauses are true. Rather, I'm trying to get a result set that would look like this (again, this is in theory): ++---+---+ | id | sub1other | sub2other | ++---+---+ | 1 | 2 | NULL | | 2 | NULL | 5 | | 3 | NULL |17 | | 4 | 8 | NULL | | .. |...etc |...etc | ++---+---+ Later, in my application, I can test each column for NULL and I will know that the other column is the one to use (for instance, if the value of the sub1other column is NULL in one record, then I'll use the value of sub2other to do what I want to do, and vice versa). But this just doesn't seem possible. I can always do it with two separate queries if need be, but it would be elegant to do it with one. Any advice? Thanks very much, Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] - 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
RE: Irritating Discovery
try tail -n 10 /mysql/why.log /tmp/why.log echo /mysql/why.log -Original Message- From: Van [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 12, 2002 6:01 PM To: Paul DuBois Cc: MySQL Subject: Re: Irritating Discovery Paul DuBois wrote: At 13:12 -0700 6/12/02, Van wrote: Greetings: Perhaps a bug, even. After about 5 months my mysql log file grew to over 300MBytes and I figured I'd do something about it as follows: tail -n 10 /mysql/why.log /tmp/why.log sudo mv /tmp/why.log /mysql/ mysqladmin -u root flush-logs Unfortunately, this set the permissions of why.log as a user other than mysql, So what happened? Did this command: sudo mv /tmp/why.log /mysql/ replace your old /mysql/why.log file with one owned by root? Yes. Well, actually owned by user vanboers (I use sudo, not root). If so, I guess I wouldn't be surprised. preventing mysqld from logging queries (exactly what I'd hoped to avoid), so I: sudo chown mysql.mysql /mysql/why.log But, this should have fixed it so mysqld could start writing to it again... - and - mysqladmin refresh And, this should have written to it... This: mysqladmin shutdown /etc/rc.d/rc.mysql shouldn't have been necessary. See what I mean? Van -- = Linux rocks!!! http://www.dedserius.com/ = - 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 - 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
RE: replication question
I am replicating to NT Server system from Red Hat Linux was not aware of any issues to win2k from linux with replication Is there something I am not aware of? -Original Message- From: Bartomiej Dolata [mailto:[EMAIL PROTECTED]] Sent: Monday, June 10, 2002 9:44 AM To: Mysql@Lists. Mysql. Com Subject: replication question hello, can someone please explain why it is not possible to do the replication between e.g. linux and bsd systems ? why wouldnt it be possible to exchange data in system-independent fashion ? i have set up replication between mysql running on win2k, but am unable to do that with linuxw2k nor linuxopenbsd combination. i would like to see technical explanation, not just 'filesystem difference' best regards, terry - 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 - 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
I have a problem....
We are running mysql on a red hat box. We replicate a single database twice, once on another red hat box(thank whatever is holy), once on a winnt box. The winnt box cares not for case sensitivity, so it created all our table names in lower case ( our standard is to use CAPS for tablenames ) ( i knew this was an issue, but I forgot about it when i got a new toy to play with: the nt server ) Now, I did a dump to a development red hat box, and it created all the tables in lower case ( which is useless since our application is geared to the uppercase table names ) Does anyone have a solution or work around for this ? I briefly considered creating a script for the linux box to rename the files but thought I should ask if there was any other way to do this... Luc - 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 queries, dynamic???
Is there any way to dynamically create columns non programatically? As in pure sql
queries?
This is what I am doing now:
SELECT SQL_BUFFER_RESULT
ORDERHEADER.ID_CUSTOMER AS 'Acct #',
CUSTOMER.Company AS 'Company',
SALESLEVEL.Name AS 'Client Type',
DATE_FORMAT(ORDERHEADER.AvailableAt, '%b') AS 'Month',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 1,1,0)) AS '1',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 2,1,0)) AS '2',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 3,1,0)) AS '3',
etc
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 31,1,0)) AS '31',
COUNT(ORDERHEADER.ID) AS 'Total'
FROM ORDERHEADER
LEFT JOIN CUSTOMER ON ORDERHEADER.ID_CUSTOMER = CUSTOMER.ID
LEFT JOIN SALESLEVEL ON CUSTOMER.ID_SALESLEVEL = SALESLEVEL.ID
WHERE (ORDERHEADER.AvailableAt BETWEEN '[param:StartDate]' AND
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959'))
GROUP BY ORDERHEADER.ID_CUSTOMER, DATE_FORMAT(ORDERHEADER.AvailableAt, '%m')
ORDER BY DATE_FORMAT(ORDERHEADER.AvailableAt, '%b'), Total DESC
(NOTE: [param:EndDate] and [param:StartDate] is filled in through a parser, these can
range from 1 day to months apart)
Sample output is as follows:
(these are days)
Acct# Company Client Type Month 1 2 3 ... 31 Total
3212Joe's Bike Shop Weekly DEC 5 7 3 ... 4
72
3423Sam's Deli Daily DEC 6 3 3 ... 2
65
3243Jerry's Bi-Weekly DEC 4 5 2 ... 5
69
3212Joe's Bike Shop Weekly JAN 0 4 3 ... 7
57
3423Sam's Deli Daily JAN 0 2 7 ... 8
64
3243Jerry's Bi-Weekly JAN 0 4 3 ... 3
57
Desired output:
Acct# Company Client Type 12/112/212/3... 12/31 12/Total
1/1 1/2 1/3 ... 1/311/Total Grand Total
3212Joe's Bike Shop Weekly 5 7 3 ... 4 72
0 4 3 ... 3 57 129
3423Sam's Deli Daily 6 3 3 ... 2 65
0 2 7 ... 8 64 129
3243Jerry's Bi-Weekly 4 5 2 ... 5 69
0 4 3 ... 3 57 126
12/1, 12/2, 12/3 etc being month/day
The report original report has the day columns hard coded, can they by dynamic based
on the dates chosen, as in the number of days per month and the actual date numbers
themselves
Is this possible in any way? it doesnt matter if temp tables need to be made or not to
do this, whatever means sql queries can manage it
I have a progromatic (JAVA) way to do this with a while loop cycling through the date
ranges which generates the sql then runs that query, but don't really want to program
everything when a new report like this would be needed
-
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
