help with mysql db names
Hello, why these db names created fail but the last one gets success? mysql create database 3208e1c6aa32; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3208e1c6aa32' at line 1 mysql create database 208e1c6aa32; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '208e1c6aa32' at line 1 mysql create database 08e1c6aa32; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '08e1c6aa32' at line 1 mysql create database 8e1c6aa32; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8e1c6aa32' at line 1 mysql create database e1c6aa32; Query OK, 1 row affected (0.01 sec) mysql select version(); ++ | version() | ++ | 5.5.16-log | ++ 1 row in set (0.00 sec) Thanks a lot! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
connection issue
Hello, When connecting to mysql, sometime I got this error: # mysql -ucdn -h113.108.22x.xx -p Enter password: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 But most time it's correct. What's the reason? Thanks. I am running mysql-5.1.37 on Linux, there is no firewall configured on the host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: connection issue
sorry the problem has been resolved. it's the reason of open_files_limit too small. I increased it and the problem resolved. 2012/12/22 Doug d...@hacks.perl.sh: Hello, When connecting to mysql, sometime I got this error: # mysql -ucdn -h113.108.22x.xx -p Enter password: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 But most time it's correct. What's the reason? Thanks. I am running mysql-5.1.37 on Linux, there is no firewall configured on the host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
why this query doesn't use index?
Hello, can you tell me why my this query doesn't use the index? mysql explain select * from iploc where 1902800418 between start_ip and end_ip; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| NULL | 58838 | Using where | ++-+---+--+---+--+-+--+---+-+ 1 row in set (0.00 sec) mysql explain select * from iploc where start_ip =1902800418 and end_ip =1902800418; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| NULL | 58838 | Using where | ++-+---+--+---+--+-+--+---+-+ 1 row in set (0.00 sec) This is the index stru: mysql show index from iploc; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | iploc | 0 | PRIMARY |1 | id | A | 58838 | NULL | NULL | | BTREE | | | iploc | 1 | start_ip |1 | start_ip| A | 58838 | NULL | NULL | | BTREE | | | iploc | 1 | start_ip |2 | end_ip | A | 58838 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ 3 rows in set (0.00 sec) Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
restore from replication
Hello, Currently my master db has about 50G data. There is a replication slave db, it's running with read-only mode. When the master is down for some reasons, I will have to remove the read-only flag in slave mysql and let the applications access to slave with both read and write. When the master is up again, how will I restore those data new updated into slave to master? Thank you. Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
granting file privileges
I have both a theory question and a question on implementation of privileges.
First theory. I have been using:
grant all privileges on db-name.* to user@localhost
identified by 'password';
Because I blunder about as root I never was impacted by 'file' being a global
permission. As 'load infile' seems (to me) to be equivalant to 'insert' I do not
see the reason for this. If its just a historical thing, so be it, but IMO it
makes little sense that a user could create and/or delete a table but to import
data he is required to convert a csv file to 'insert value' statements.
My implementation question is about specifying the user. Apparently the
following are different:
1) user@localhost
2) 'user@localhost'
3) 'user'@'localhost'
I have not tested all this, but I did grant file privileges to #1 but could not
use them logging into with 'mysql -u doug@localhost sysadmin' ('doug' being
setup without a password).
The question is which form should be used and why are they different as all are
accepted without error and all add entries for the users and db tables.
_
Douglas Denault
http://www.safeport.com
d...@safeport.com
Voice: 301-217-9220
Fax: 301-217-9277
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: granting file privileges
Got it - thank you, I did not think about the meaning of file except as it
relates to MySQL. Re syntax - thanks.
On Sun, 31 Jul 2011, Claudio Nanni wrote:
Hi Doug,
1.FILE is GLOBAL because it refers to the ability of the user to read/write
files on the server host filesystem (where the filesystem permissions
allow).
2.
1) user@localhost OK, not recommended
2) 'user@localhost' WRONG
3) 'user'@'localhost' OK, BEST
single quotes prevent any problem in case of special characters in the host
name.
Cheers!
Claudio
2011/7/31 d...@safeport.com
I have both a theory question and a question on implementation of
privileges. First theory. I have been using:
grant all privileges on db-name.* to user@localhost
identified by 'password';
Because I blunder about as root I never was impacted by 'file' being a
global permission. As 'load infile' seems (to me) to be equivalant to
'insert' I do not see the reason for this. If its just a historical thing,
so be it, but IMO it makes little sense that a user could create and/or
delete a table but to import data he is required to convert a csv file to
'insert value' statements.
My implementation question is about specifying the user. Apparently the
following are different:
1) user@localhost
2) 'user@localhost'
3) 'user'@'localhost'
I have not tested all this, but I did grant file privileges to #1 but could
not use them logging into with 'mysql -u doug@localhost sysadmin' ('doug'
being setup without a password).
The question is which form should be used and why are they different as all
are accepted without error and all add entries for the users and db tables.
_
Douglas Denault
http://www.safeport.com
d...@safeport.com
Voice: 301-217-9220
Fax: 301-217-9277
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?**
unsub=claudio.na...@gmail.comhttp://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
--
Claudio
_
Douglas Denault
http://www.safeport.com
d...@safeport.com
Voice: 301-217-9220
Fax: 301-217-9277
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
question about natural join
I am comparing two tables, domains and temp, to find records with a field that has been modified. I create the temp table with create table temp like domains; then [eventually] create a table t3 that contains the domain name of any record that does not match. My question is about 'natural join'. I would have thought that the following two queries are equivalent: 1) select t.name,t.expires,d.expires from domains as d natural join temp as t where d.expirest.expires; 2) select t3.name,t.name,t.expires,d.expires from t3,domains as d,temp as t where t.name=t3.name and d.name=t3.name and d.expirest.expires; The natural join finds no records, the second query works. The structure of the tables domain and temp is: +---+-+--+-++---+ | Field | Type| Null | Key | Default| Extra | +---+-+--+-++---+ | Custid| int(11) | | MUL | 0 | | | Name | varchar(150)| | PRI || | | User | varchar(16) | | MUL || | | Active| tinyint(3) unsigned | | | 0 | | | Created | date| YES | | NULL | | | Expires | date| | | -00-00 | | | Registrar | varchar(50) | | || | | Managed | varchar(50) | YES | | NULL | | | status| varchar(20) | YES | | NULL | | | Start | date| YES | | NULL | | +---+-+--+-++---+ I am using 4.1.20. As always thank you for your insights. Doug _ Douglas Denault http://www.safeport.com d...@safeport.com Voice: 301-217-9220 Fax: 301-217-9277 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question about natural join
Thank you. On Wed, 21 Jan 2009, c...@l-i-e.com wrote: The natural join will JOIN on *all* the fields whose names match, not just the ones you want it to. In particular, the JOIN is matching up .expires and .expires with = You then use WHERE to get only the ones with This is a tautology: There are NO records both = and on the field .expires. You don't really want a natural JOIN here. And, honestly, natural JOIN generally ends up being problematic sooner or later. You end up adding some kind of field to both tables that should not be included (last_update, e.g.) and suddenly your query isn't right any more. Stick with the explicit WHERE clauses that make it crystal clear what your query does. Just as SELECT * is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=d...@safeport.com _ Douglas Denault http://www.safeport.com d...@safeport.com Voice: 301-217-9220 Fax: 301-217-9277 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: C api - mysql_list_fields
It works for me, I used your code:
Field =mycol Type=254 Length=20
so at least your code is fine, and the problem must be somewhere
else. I am using RH EL3.
cheers,
Doug
On 6 Oct 2008, at 19:52, Mike Aubury wrote:
I'm probably being a bit stupid - but I'm trying to determine (in
code) the
length of the string in the schema for a given table.
So - for example :
create table a (
blah char(20)
)
I want to return '20', but I'm getting '60' when I use
mysql_list_fields..
(Always seems to be 3x longer that I'm expecting)...
Am I missing something ? (or should I just divide by 3!!)
Heres an example :
#include stdio.h
#include stdlib.h
#include mysql.h
MYSQL conn;
int main(int argc,char *argv[]) {
// run with username port as arguments
char *tabname=a;
char *db=test1;
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
if (argc!=3) {
printf(usage : %s username password\n, argv[0]);exit(2);
}
u=argv[1]; p=argv[2];
mysql_init(conn);
if (!mysql_real_connect(conn, NULL,u,p,db,0,NULL,0) ) {
fprintf(stderr,
Failed to connect to database: Error: %s\n,
mysql_error(conn));
exit(2);
}
result = mysql_list_fields (conn, tabname, NULL);
field = mysql_fetch_field (result);
printf(Field =%s Type=%d Length=%d\n, field-name,
field-type, field-length);
}
Thanks in advance...
--
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: Ancient, unsolved high-CPU problem
it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem
nothing on a server is weird, just not understood yet :-) so, 'show processlist' comes up with nothing. Does 'ps auxw' show any php processes still active? Does 'netstat -atp' show any established connections to mysql? How long does it take to re-create the problem? You've restarted mysql, how long to high cpu again? Did you start to rebuild indexes any time recently? You seem to have ruled out the web scripts, so can you stop the 30% cron job scripts running? in your other email CPU usage is 45%. The top figure is the percentage of CPU usage that mysql has used, compared with other processes. 92% of 45% (I believe). if you run 'vmstat 5' it will print out resource usage every 5 seconds (ignore the first line) which is more readable than top output as you see changes over time. Change 5 for an interval that suits your purpose (ie, if it takes 10 hours to happen, then vmstat 300). cheers, On 23 Sep 2008, at 15:04, Rene Fournier wrote: Appreciate the suggestions, some of which I've done. The processlist typically just shows the known PHP command-line scripts that run. Maybe 8-10 on average, 20 max. Here's a strange thing: If I stop all the requests to MySQL (shut down Apache, and exit all the commandline PHP scripts), MySQL's CPU usage remains high. So... no processes in MySQL, nothing hitting the database, yet MySQL CPU stays stuck at 60-70%. If I shutdown MySQL and restart, it's normal again. Weird, no? On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List
Re: another INNODB vs MYISAM question
if you switch the default engine type any new tables would be created with that new engine type. it does not convert existing tables to your new format. if you have existing innodb tables you need to have the innodb settings active, in my.cnf On 15 Aug 2008, at 06:01, [EMAIL PROTECTED] wrote: Hello mysql, As I have previously mentioned, I installed WAMPSERVER 2.0 on my Windows XP pro box recently. It installed INNODB as the Default Engine. All of my legacy Databases are MYISAM and after the installation, I copied them all into the DATA folder and everything worked, even adding new tables etc. but the new stuff was INNODB. So I ended up with some MYISAM databases that contained INNODB tables in them. After a few weeks I got to thinking that mixing INNODB and MYISAM might not be a good thing and switched the Default Engine to MYISAM in my.ini file. I didn't just switch the default, I commented out all the INNODB calls in the my.ini file as well. As I half expected, all the databases that I had added INNODB tables failed when I tried to fire up the applications that used them. Although I am not new to mysql, I have had a bit of MYISAM tunnel vision with it so my question is, if I had just switched the default engine and NOT disabled the INNODB calls in my.ini, would that have prevented the problem? I restored all the MYISAM files and got everything back working again. I don't want to go through the lengthy reproduction exercise of reinstalling everything to test the theory so if someone has had some experience with this, I would appreciate hearing from them. -- Best regards, mikesz mailto:[EMAIL PROTECTED] -- 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: Slow mysql connections
Do you have any other services running on the server, such as mail or
web? How long does it take to connect to those services ('telnet
server 25', or telnet server 80')?
What are you using to connect to mysql? how long does 'telnet
server 3306' take (assuming you are on the default port and have
firewall access)? How long does it take to connect from a local
server session?
When you are doing the above tests, do them with the server name and
with the IP address.
The above should let you rule out your client, and any server related
issues such as DNS or firewall. So you will know if it is a mysql
problem or not.
cheers,
Doug
On 13 Aug 2008, at 05:39, Krishna Chandra Prajapati wrote:
Hi all,
Connecting to mysql server (Production) is taking 5 to 6 seconds.
Production
has 16Gb ram. Previously it was using only 6GB ram. The details are as
follows. DNS looks fine.
free -m
total used free sharedbuffers
cached
Mem: 15899 15877 21 0 97
13913
-/+ buffers/cache: 1865 14033
Swap:0 0 0
---
vmstat
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us
sy id
wa
0 0 0 26792 100600 142428680089 15000
7 1
92 0
1
-
cat /proc/meminfo
MemTotal: 16281204 kB
MemFree: 26692 kB
Buffers:100916 kB
Cached: 14243248 kB
SwapCached: 0 kB
Active:8338968 kB
Inactive: 7679900 kB
HighTotal: 0 kB
HighFree:0 kB
LowTotal: 16281204 kB
LowFree: 26692 kB
SwapTotal: 0 kB
SwapFree:0 kB
Dirty: 224 kB
Writeback: 0 kB
AnonPages: 1674640 kB
Mapped: 11616 kB
Slab: 215140 kB
PageTables: 4232 kB
NFS_Unstable:0 kB
Bounce: 0 kB
CommitLimit: 8140600 kB
Committed_AS: 2182136 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 252 kB
VmallocChunk: 34359738043 kB
--
ps aux (The below process is only process using cpu and memory)
mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10
/usr/sbin/mysqld --basedir=/usr --datadir=/v
cpu usage 10.6
memory usage 10.2
Please tell how what are the reasons and how can i solve this
problem.
Krishna Chandra Prajapati
MySQL DBA,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Learning best methods
I have the following table: explain domain_payments; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Invid | int(11) | | | 0 | | | Custid | int(11) | | | 0 | | | Date| date | YES | | NULL| | | Description | varchar(80) | YES | | NULL| | | Domain | varchar(150) | YES | | NULL| | | UnitPrice | double | YES | | NULL| | | Quantity| int(11) | | | 0 | | | Amount | double | YES | | NULL| | +-+--+--+-+-+---+ My goal is to create a table with the latest payment date and invoice id for each domain. This is what I did: create table t select domain,max(invid) as invid from domain_payments group by domain; alter table t add date_paid date; update t,domain_payments as tr set date_paid=date where t.invid=tr.invid; The above worked fine for the number of records in my dataset. In learning [my]sql I am trying to see how this would best be done with a large dataset as well more efficiently in general. Thanks for any pointers. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED] RE: Strange performance problem
It's possibly a DNS problem (reverse DNS exactly). You know, I'm feeling a bit stupid here... That was indeed the problem, as the new server hadn't been moved on DNS yet. I put the IP address into the windows hosts file on the DB server, and the problem cleared up immediately. Thanks! -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange performance problem
OK folks, I'm kind of stumped; looking into things a bit more, but thought I'd hit the list and see if anyone had any suggestions for a rock to look under, in case I'm missing it... DB Server: Windows 2003, 8-way CPU, lots of RAM, MySQL 4.1.22-nt binary from MySQL Current Production web server: Linux, RedHat 7.2, MySQL 5.0.51a client from MySQL Binary RPMs New Web Server: Linux, RedHat EL 4, MySQL 5.0.51a client from MySQL Binary RPMs The current production web server has a fairly high traffic load, but DB usage is extremely efficient, so there's not a significant load on the DB server. Connection times from the production web server are almost instantaneous (under 1 second), using the press enter and observe method, using the command-line interface. Connection times from the new web server box take between 4-6 seconds between the time you press enter and the time that you get the MySQL prompt. YSlow in Firefox confirms a consistent average of 4-6 seconds difference between the servers reflected in page load times. At this point, we're trying to see what it is going on between the client and the server; datacenter folks have assured us that it's not a networking issue (although I'm not sure that I'm convinced). We've ruled out any apache or PHP issues causing a problem, as the configurations and build options are identical. Any thoughts of a performance number or configuration option that would make any differences? Thanks much in advance - I'm scratching my head on this one... -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: secure host and user name for non static ip address
QUESTION: What are the most secure permissions settings for administrator access to connect to my server without using a static IP address? Assuming a unix server (or even a windows server running SSH), use an SSH tunnel, and then connect to 127.0.0.1:whatever_port_you_chose. You get the extra benefit of the traffic being encrypted, plus you don't have to worry about changing the permissions every $x hours when your IP changes. -Doug -- Douglas Phillips Programmer / Database Engineer Cybergroup, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Permissions block database creation
I'm a total newbie working through the tutorial in DuBois's _MySQL_. I've got MySQL running on my PowerBook. In Terminal, I can use the mysql client to get responses to things like SELECT NOW(). But the command CREATE DATABASE sampdb; results in the following error. ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'sampdb' mysql I am unable to determine its location, which causes me to suspect that it is in the portion of the system not accessible from the Finder. Can anyone offer a suggestion? Mac OS 10.4.5 Thanks, dp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error connecting to db.
I have a php application that connects with MySQL using: $SvrConn = @mysql_connect($MySQLServer, $MySQLUser, $MySQLPassword) where: $MySQLServer='127.0.0.1' $MySQLUser='dbuser' MySQLPassword='dbpasswd' and I can login using the above values. However connect fails: mysql select host,user from user; +---+-+ | host | user| +---+-+ | localhost | dbuser | +---+-+ The application worked until I changed the IP of the host. Where else might I look? There is no cnf file. thanks for any help. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error connecting to db.
Thanks for the link I am glad the site has added manuals for multiple versions. I think I have access and db privileges set properly. I can (in effect) login to the server from the host command line using: mysql -u $MySQLUser -p $MySQLPassword I showed the user table on the off chance that 127.0.0.1 does not map to local host and someone would clue me in to that. I have tried setting the host for the user to the IP and to 127.0.0.1. Neither works. I think the problem probably lies with the mysql install but I do not know where to look. I am a PHP novice but AFAIK @mysql_connect should be like accessing mysql from the command line. On Tue, 17 Jan 2006, Logan, David (SST - Adelaide) wrote: Hi Doug, It is probably failing because you changed the ip address. The security system in MySQL is based around where a user is coming from. There is good documentation that is available here http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html I've referred to the current manual for ver. 5.0 as I'm not sure what you are using. You can easily refer to an earlier version of the manual via the http://dev.mysql.com/doc/ page. Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 17 January 2006 1:07 PM To: mysql@lists.mysql.com Subject: error connecting to db. I have a php application that connects with MySQL using: $SvrConn = @mysql_connect($MySQLServer, $MySQLUser, $MySQLPassword) _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP [nativecode=2013 ** Lost connection to MySQL server during query]
Gleb Paharenko wrote: Hello. Check that MySQL doesn't die during the query. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Gleb, I should mention that I get no log errors and I even have it set to warn does this mean mysql could die and not log the error? I have gone through this document for the last week rewritten code based on the suggestions and still get the error. Regards, Doug oug Dalton wrote: I am running mysql 4.1.13 on Suse 10 and I get an error on a simple select query, I have checked the threads on lost connection and my connection times as well as making sure my calls are all made from non forking processes etc... I cannot seem to resolve this error? can someone point me in the right direction? I notice if I remove the LIMIT statement I get fewer errors... but that cannot be the cause of the problem [14-Dec-2005 17:32:20] DB_Error Object ( [error_message_prefix] = [mode] = 1 [level] = 1024 [code] = -14 [message] = DB Error: no database selected [userinfo] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativecode=2013 ** Lost connection to MySQL server during query] [backtrace] = Array ( [0] = Array ( [file] = /usr/share/php5/PEAR/DB.php [line] = 888 [function] = PEAR_Error [class] = DB_Error [type] = - [args] = Array ( [0] = DB Error: no database selected [1] = -14 [2] = 1 [3] = 1024 [4] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativec ode=2013 ** Lost connection to MySQL server during query] ) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP [nativecode=2013 ** Lost connection to MySQL server during query]
Gleb, I watched the server during this error, the server continued to run... it didnt crash... and no warning or logged error? Regards, Doug Doug Dalton wrote: Gleb Paharenko wrote: Hello. Check that MySQL doesn't die during the query. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Gleb, I should mention that I get no log errors and I even have it set to warn does this mean mysql could die and not log the error? I have gone through this document for the last week rewritten code based on the suggestions and still get the error. Regards, Doug oug Dalton wrote: I am running mysql 4.1.13 on Suse 10 and I get an error on a simple select query, I have checked the threads on lost connection and my connection times as well as making sure my calls are all made from non forking processes etc... I cannot seem to resolve this error? can someone point me in the right direction? I notice if I remove the LIMIT statement I get fewer errors... but that cannot be the cause of the problem [14-Dec-2005 17:32:20] DB_Error Object ( [error_message_prefix] = [mode] = 1 [level] = 1024 [code] = -14 [message] = DB Error: no database selected [userinfo] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativecode=2013 ** Lost connection to MySQL server during query] [backtrace] = Array ( [0] = Array ( [file] = /usr/share/php5/PEAR/DB.php [line] = 888 [function] = PEAR_Error [class] = DB_Error [type] = - [args] = Array ( [0] = DB Error: no database selected [1] = -14 [2] = 1 [3] = 1024 [4] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativec ode=2013 ** Lost connection to MySQL server during query] ) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP [nativecode=2013 ** Lost connection to MySQL server during query]
I found a log error after turning on warning running the server manually: 051215 17:32:42 [Warning] Aborted connection 3961 to db: 'db' user: 'user' host: `client' (Got timeout reading communication packets) Any idea what this error is ? the interfaces show no errors from a network perspective: db machine eth0 Link encap:Ethernet HWaddr 00:30:48:2E:E1:F2 inet addr:10.0.0.10 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::230:48ff:fe2e:e1f2/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:7973410 errors:0 dropped:0 overruns:0 frame:0 TX packets:9299327 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:746369778 (711.7 Mb) TX bytes:8126772010 (7750.2 Mb) Base address:0x2000 Memory:da20-da22 client machine: eth1 Link encap:Ethernet HWaddr 00:30:48:55:92:4F inet addr:10.0.0.1 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::230:48ff:fe55:924f/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:19720381 errors:0 dropped:0 overruns:0 frame:0 TX packets:16126186 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:16315799815 (15559.9 Mb) TX bytes:1696496582 (1617.9 Mb) Base address:0xc400 Memory:fc9a-fc9c and the query runs fine a second time? so the query isnt malformed Regards, Doug Doug Dalton wrote: Gleb, I watched the server during this error, the server continued to run... it didnt crash... and no warning or logged error? Regards, Doug Doug Dalton wrote: Gleb Paharenko wrote: Hello. Check that MySQL doesn't die during the query. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Gleb, I should mention that I get no log errors and I even have it set to warn does this mean mysql could die and not log the error? I have gone through this document for the last week rewritten code based on the suggestions and still get the error. Regards, Doug oug Dalton wrote: I am running mysql 4.1.13 on Suse 10 and I get an error on a simple select query, I have checked the threads on lost connection and my connection times as well as making sure my calls are all made from non forking processes etc... I cannot seem to resolve this error? can someone point me in the right direction? I notice if I remove the LIMIT statement I get fewer errors... but that cannot be the cause of the problem [14-Dec-2005 17:32:20] DB_Error Object ( [error_message_prefix] = [mode] = 1 [level] = 1024 [code] = -14 [message] = DB Error: no database selected [userinfo] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativecode=2013 ** Lost connection to MySQL server during query] [backtrace] = Array ( [0] = Array ( [file] = /usr/share/php5/PEAR/DB.php [line] = 888 [function] = PEAR_Error [class] = DB_Error [type] = - [args] = Array ( [0] = DB Error: no database selected [1] = -14 [2] = 1 [3] = 1024 [4] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativec ode=2013 ** Lost connection to MySQL server during query] ) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP [nativecode=2013 ** Lost connection to MySQL server during query]
I am running mysql 4.1.13 on Suse 10 and I get an error on a simple select query, I have checked the threads on lost connection and my connection times as well as making sure my calls are all made from non forking processes etc... I cannot seem to resolve this error? can someone point me in the right direction? I notice if I remove the LIMIT statement I get fewer errors... but that cannot be the cause of the problem [14-Dec-2005 17:32:20] DB_Error Object ( [error_message_prefix] = [mode] = 1 [level] = 1024 [code] = -14 [message] = DB Error: no database selected [userinfo] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativecode=2013 ** Lost connection to MySQL server during query] [backtrace] = Array ( [0] = Array ( [file] = /usr/share/php5/PEAR/DB.php [line] = 888 [function] = PEAR_Error [class] = DB_Error [type] = - [args] = Array ( [0] = DB Error: no database selected [1] = -14 [2] = 1 [3] = 1024 [4] = SELECT * FROM buser Where mUserID='d86659dcee2cf9933d84ea337b1b9785' LIMIT 0,1 [nativec ode=2013 ** Lost connection to MySQL server during query] ) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another null question
First - thank you. I want to again thank this list, which in general is the most tolerant to people new to database use and theory in general, and me in particular. I mostly got your point from an earlier answer. Hopeful your example quashes my ignorance, relative to NULL at least. I think I understand the manual examples with the help of the answers I got. I was not getting it on my own. On Tue, 20 Sep 2005, Joerg Bruehe wrote: Hi! Just some explicit addition: [EMAIL PROTECTED] wrote: [[...]] So in the following query: select * from new_payments where closed1; it is desired that null=1. DeMorgan's law takes a vacation here. You use two-valued logic here, where statements are either true or false. (DeMorgan's law applies to two-valued logic only.) When NULL values are not excluded, SQL uses a three-valued logic, where a stament may also be unknown. Comparing NULL to any value (including a comparison of NULL and NULL) always results in unknown. This also the reason that the SQL syntax does not allow ... WHERE value = NULL but requires that you write ... WHERE value IS NULL It has also been said that NULL is no value but a state - maybe that helps in understanding. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another null question
Thanks Martijn - that is a clear explanation of the philosophy. I did not get it from the examples and the manual. Doug On Mon, 19 Sep 2005, Martijn Tonies wrote: Hello Doug, My question question from MySQL 4.0.22: is it okay that a null interger tests as equal to any value. I am not sure the table structure matters but in case it is [cut] That's because NULL is NOT a value (not the logical not ;) ... It's a state. Any column can have two states: either NULL or NOT NULL. When a column is not null it can have values suitable for it's given domain (eg: all integer values). If there's no value (NULL), how can you compare it? Think of NULL as unknown. Is 2 equal to unknown? I don't know, so the result is unknown, hence, NULL. Hope this helps, With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yet another null question
My question question from MySQL 4.0.22: is it okay that a null interger tests as equal to any value. I am not sure the table structure matters but in case it is I included it. I did not expect that null was not not-equal to 1. Cursory testing seems to indicate that a null integer field is not not-equal to any specific value. If this is the known behavior, I would suggest that this behavior be added to section A.5.3. Testing the field=1 works as I would expect, that is the null value is not equal to 1. Examples follow: mysql explain new_payments; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | payid | int(11) | | PRI | 0 | | | Custid | int(11) | | MUL | 0 | | | User | varchar(16) | | | | | | CkNum | varchar(20) | YES | | NULL| | | Paid | double | YES | | NULL| | | Date | date| YES | | NULL| | | Type | varchar(8) | YES | | NULL| | | Notes | varchar(80) | YES | | NULL| | | Tag| varchar(15) | YES | | NULL| | | closed | tinyint(4) | YES | | NULL| | ++-+--+-+-+---+ mysql select * from new_payments where closed1; Empty set (0.02 sec) mysql select * from new_payments where closed is null; +---++-+---+++--+-+--++ | payid | Custid | User| CkNum | Paid | Date | Type | Notes | Tag | closed | +---++-+---+++--+-+--++ | 956 |198 | stepout | 3001 | 116.95 | 2005-09-16 | AmEx | 0508044 | 7069 | NULL | | 957 |145 | anytech | 62401 |102 | 2005-09-16 | MC | 0504304 | B058 | NULL | +---++-+---+++--+-+--++ 2 rows in set (0.02 sec) mysql select count(*) from new_payments; +--+ | count(*) | +--+ | 945 | +--+ 1 row in set (0.00 sec) mysql select count(*) from new_payments where closed=1; +--+ | count(*) | +--+ | 943 | +--+ 1 row in set (0.01 sec) _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another null question
Thanks but I still do not get why this does not merit a, you might want to think about this. So in the following query: select * from new_payments where closed1; it is desired that null=1. DeMorgan's law takes a vacation here. Correct behavior, in that it is a feature, I expected that; desired - no comment. Probably not expected in that selecting for closed=1 and closed1 does not yield symmetric results. It was the [not] expected nature that lead me to suggest perhaps an example might be helpful. From URL: http://dev.mysql.com/doc/mysql/en/working-with-null.html Note that in MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1. mysql select * from new_payments where not closed; Empty set (0.01 sec) So null is not exactly false, just sorta false. I still think this is worth a comment in A.5.3. On Mon, 19 Sep 2005, Roger Baklund wrote: [EMAIL PROTECTED] wrote: I did not expect that null was not not-equal to 1. Any comparison with NULL returns NULL: mysql select null1,null1,null1,null=1; +-++++ | null1 | null1 | null1 | null=1 | +-++++ |NULL | NULL | NULL | NULL | +-++++ 1 row in set (0.00 sec) This is the correct and desired behaviour. URL: http://dev.mysql.com/doc/mysql/en/null-values.html URL: http://dev.mysql.com/doc/mysql/en/working-with-null.html URL: http://dev.mysql.com/doc/mysql/en/problems-with-null.html -- Roger _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another null question
Belay my comments on true and false. I got mixed up. That works as documented. On Mon, 19 Sep 2005, Roger Baklund wrote: [EMAIL PROTECTED] wrote: I did not expect that null was not not-equal to 1. Any comparison with NULL returns NULL: mysql select null1,null1,null1,null=1; +-++++ | null1 | null1 | null1 | null=1 | +-++++ |NULL | NULL | NULL | NULL | +-++++ 1 row in set (0.00 sec) This is the correct and desired behaviour. URL: http://dev.mysql.com/doc/mysql/en/null-values.html URL: http://dev.mysql.com/doc/mysql/en/working-with-null.html URL: http://dev.mysql.com/doc/mysql/en/problems-with-null.html -- Roger _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculating a running balance
I am using MySQL 4.0.22 and have a question about an efficient way to calculate a running balance as part of the select. The natural way to do this would seem (to me) to be something like: set @balance:=0; select payid,Custid,Paid,@balance:[EMAIL PROTECTED] from payments; This does indeed seem to work. However the way I read the explanation on user variables I would not expect this to work. I found a solution for this from the MySQL Cookbook using a self join which for the above would be: select t1.payid,t1.Custid,t1.user,t1.Paid,sum(t2.Paid) from payments as t1, payments as t2 where t1.payid=t2.payid group by t1.payid; This also works but as I am sure most of you know will not scale to even a few thousand records. Will using the user variable work? If not where might I find some alternate techniques? _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow LIMIT Query
When I do a SELECT using STRAIGHT JOIN against multiple tables where the main table has about 200k rows, it is very fast retrieving the latest rows, ie LIMIT 0, 10, but extremely slow retrieving older rows, for example, LIMIT 18 , 10. Doing an EXPLAIN shows that no filesort or temporary table is being used. When I do the SELECT without the STRAIGHT JOIN, it does do a filesort and is a little bit faster. Is there anyway to speed such a query up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
Hi, I have tried to simply the problem and it exists without any JOINs. have you given the query ? SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 18, 10 - .43 sec SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 - .0007 sec have you described your tables ? stuffed_date is INDEXed have your given the size of each table ? The table is about 200k rows. have you list the indexes ? stuff table has several indices, including 'id' and 'stuffed_date'. have you specify the storage type ? MYISAM In your followup message, you mention reverse sorting the query. I imagine on the application side I would need to reverse sort again to get the correct order. Are there any other ways to speed up such a query? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
Just in case you did not follow this suggestion, if you are using 4.0.x this is very simple. I was looking for this: set @total:=0; select f1,f2,...,@total:[EMAIL PROTECTED] as Total from table where ...; is pretty simple. On Wed, 25 May 2005, Dan Bolser wrote: On Wed, 25 May 2005, Russell Horn wrote: I have a pretty simple table with a list of payments, not much more than: paymentID | amount | paymentDate 1 | 123| 2005-01-10 2 | 77 | 2005-01-13 3 | 45 | 2005-02-16 4 | 13 | 2005-02-17 I can get totals per month using a query like: SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) That would give me: amount | paymentDate 200| 2005-01 58 | 2005-02 Is there any way to get a running cumulative total directly from mysql? Something like: amount | paymentDate 200| 2005-01 258| 2005-02 http://dev.mysql.com/doc/mysql/en/variables.html :D Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Online documentation
Thank you for the clear statement of policy. I had in fact forgotten about the manual that comes with the installation. It however is packaged as one rather large html or text file (my system is FreeBSD). The web site is a far superior reference because of the file format. Doug On Thu, 21 Apr 2005, Joerg Bruehe wrote: Hi Douglas, all! Am Mi, den 20.04.2005 schrieb [EMAIL PROTECTED] um 17:33: Is the online documentation for 4.0.x available? I was working with dates a while back. As it happens 4.1 added the very function I wanted. But that was not marked as added in 4.1, nor should be be as that would be an unbelieable amount of cruft. [cut] All I can propose is to take the manual that came with the version you downloaded and installed. It will at least not contain changes that were introduced later. The documentation team currently is changing the way the manual is maintained, but I do not know whether this will introduce features (like selective extraction) which would help you with your desires. Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Online documentation
Is the online documentation for 4.0.x available? I was working with dates a while back. As it happens 4.1 added the very function I wanted. But that was not marked as added in 4.1, nor should be be as that would be an unbelieable amount of cruft. I would be nice however if maybe one level back was available if even if that was as a compressed file. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find duplicates - revisted
I have read the MySQL Cookbook and the last 6 months (or so) of threads on this. I have a slightly different problem. I use a billing system implemented in filemaker. Happily they are going a rewrite in MySQL/PHP, but for the present I maintain a MySQL database so I can track things otherwise not available using the product. My problem: once a month or so I have a 1-2 hundred new records and about as many updates. The total number of records is relative small, less than 10,000. I extract about 15 fields. Adding new records is not much of a problem. For the updated records the best I have been able to think of is to put the old and newly extracted records in a table and find the changed records based on keys in the data. For the small number of records, this query takes a remarkable amount of time, so clearly this solution does not scale. Any references or thougths greatly appreciated. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find duplicates - revisted
Yes - thanks. I did not define the data with keys because it does not come out of the source that way. Keys and replace does it nicely. Probably my question defined me as a newbie, if not well... Thank you very much for you help. On Wed, 20 Apr 2005, Duncan Hill wrote: On Wednesday 20 April 2005 16:55, [EMAIL PROTECTED] typed: For the small number of records, this query takes a remarkable amount of time, so clearly this solution does not scale. A query that uses keys should not take a long time to run - unless your key data is not maintained as a key in the SQL table. Sample data, queries and the output from EXPLAIN will probably help others to help you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TCP No Longer Working
I suddenly have lost the ability to connect to the MySQL server from my remote host and can not figure out what is wrong. I have searched for the last couple of days and cannot figure out why. I am running MySql 5.0.0 on Linux Suse 9.1 I normally connect to MySQL from Windows XP Pro using either the .Net connector and/or the ODBC connector. When I check the variables it shows that the port is set to 3306 But when I run the following it appears that TCP is not working correctly. linux:~ # netstat -l | grep mysql tcp0 0 *:mysql *:* LISTEN unix 2 [ ACC ] STREAM LISTENING 4671 /var/lib/mysql/mysql.sock If I log in via SSH or from a shell on the Linux box itself I can connect to the database in any of the following ways: mysql -u user_name -p database_name mysql -u user_name -p -h localhost database_name mysql -u user_name -p -h 127.0.0.1 database_name mysql -u user_name -p -h the_static_ip_address database_name As soon as I try to connect from Windows XP using the .Net connector, the ODBC connector, MySQL Query Browser or MySQL Administrator I get the following error: Could not connect to the specified instance. MySQL Error Nr. 2003 Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10060) I am able to successfully ping the IP address of the Linux machine. Here is what is listed in the /etc/my.cnf file [mysqld] datadir=/var/lib/mysql port=3306 socket=/var/lib/mysql/mysql.sock [mysql.server] basedir=/usr/bin Any assistance is greatly appreciated. Doug
Memory problems
When I run top after my server has been running for a few days, Mysql is using 60 or 70MB of memory. When I restart mysql, it goes back to 3000. Any idea where I should start to look for a problem? What causes this? I am running RH 7.3 and php/Mysql combo. I don't expect anyone to fix this, just perhaps suggest where I can look. BTW, I searched for this and got a reference to the perl-DBD-Mysql module, which I do not have installed. D - PresenterNet The Interactive Presenters' Network Doug Wolfgram - CEO 949.248.0439 http://www.presenternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-select
I have a table called issues and other table called users. Issues contains two fields that are in effect userids that reference the users table. I want to produce a report with a single query that grabs the names of both users referenced in the issues table. Is this possible? I knwo how to do this for a single userid... select u.firstname FROM users u, issues i WHERE u.id=iuserid etc... But I need to get two names at the same time...will some version of left join work? D _ If you're not in e-business ... you're not in business.. _ Doug Wolfgram GRAFX Group, Inc. Cell: 949.433.3641 http://www.gfx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Piano Tuning in the area
Hi, I hope you don't mind me writing you. I came across your e-mail address after looking around some web sites local to me and thought I'd ask if you, personally, are in need of a professional piano tuner or might know of someone you could forward this e-mail to. I'm tuning for donations until I build-up a customer base so payment is whatever you can afford. I'll travel to the Los Angeles, San Bernardino, Riverside, Ventura and the Orange County areas. Here's a map of where I live in Glendora in case you'd like to know: http://home.wirelis.com (If this message went out of the Los Angeles area, I apologize. It's not meant to be spam and you'll never receive another message from me again.) You can visit my website or call me if you have any questions or you can schedule a time to get your piano tuned right now at http://wirelis.com Thanks and I'm sorry if I bothered you or if you don't need this service, Doug Freeze A resident of Glendora CA. (626) 290-0908 http://wirelis.com P.S. You'll never receive another message from me so there's no need to write me back and ask to be removed from my mailing list nor will your e-mail address ever be given away or sold to anyone. http://spam.abuse.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple table delete syntax question
I have a question about the multiple table delete syntax. First the documentation on the website is very clear. My question is why not how. The 'delete from using' is not ambiguous (to me). My question is about the form: delete t1 from t1,t2 where ... I would take this to mean remove matching records from t2. I assume the reason records are removed from t1 is SQL language consistency. But as an SQL newbie, I can not see it. Thanks for any thoughts. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table delete syntax question
I have no problem understanding the syntax, or how to do what I want (at least after my first mistake). It is more about if this is a consistant grammar. For example, unix commands have the form verb src object, except for ln -s (IMO). From a lexical view, I do not think the two forms are parallel. My question was, is this to be consistant with other sql constructs, or is it just the way it is? On Sun, 25 Jul 2004, Michael Stassen wrote: The point is to distinguish between the tables which are joined to pick the rows and the tables from which rows are to be deleted. You have 2 options: DELETE FROM t1 USING t1,t2 ... or DELETE t1 FROM t1,t2 ... Perhaps you are extrapolating from 'DELETE FROM t1...' to expect that the second form should delete from both tables, but note that the second form is not 'DELETE FROM t1,t2...', it's 'DELETE t1 FROM t1,t2...'. If you must relate it to something, I'd suggest 'SELECT t1.* FROM t1,t2 ...' is the natural parallel. Michael _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Speed this Query Up? (Solved)
Using INNER JOINs with the STRAIGHT_JOIN option so that the main table displayed first in the EXPLAIN eliminated the temporary table being created and the query speed went from 2.3 seconds to 0.2 seconds. Note that this only works for queries where the offset in the LIMIT is small. As the offset approaches the total row count, the speed gradually decreases, so much so that the query eventually goes from 2.3 seconds to 4.6 seconds. However since the first few pages (newest data)are accessed most of the time, this is not a problem. _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Speed this Query Up?
Thank you for your detailed response. You might get better performance just from using the explicit INNER JOINS but I make no assumptions. I tried INNER JOINS and did not see any difference in speed. You may also get better performance if you had composite indexes (not just several individual field indexes) on the tables that contain all of the columns used in each JOIN clause. For example you could have an index on user_intros with (user_id, lang_id) and the engine won't have to read the table to check for either condition as they would both be integer values that exist in an index. I am already using composite indexes for every table with a lang_id field, like user_intros. ALSO NOTE: there is no condition in the ON clause of loc_countries_lang that relates that table to any other. This means that for all values in the veg table you will need to match one row from the loc_countries_lang table that has lang_id=0. If there are more than one languages that match that key, you will get multiple sets of matches. Well, I was thinking, since this table will never really change, and there only a couple of hundred entries, I should just store this as an array and get the country name directly from the array. However, when I removed the loc_countries_lang table from the query, it was still slow, i.e. 3-5 seconds on production server. Would changing the LEFT JOIN to an INNER JOIN improve things? It would be possible to change the logic such that the veg_titles table is used in an INNER JOIN instead, but when I tried that it was still very slow (3-5 seconds). In this instance the EXPLAIN returned the following: t ALL veg_lang_id NULL NULL NULL 76001 Using where; Using temporary; Using filesort Every other table in the EXPLAIN returned one row with type eq_ref Please, let me know if I helped or not, OK? Yes, thank you for your response, however the respone time is still too slow. Now I'm thinking that maybe my underlying database structure is not correct. Am I joining too many tables? Is there anything else I can do before I try increasing the sort_buffer? Thanks. _ MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Speed this Query Up?
A query which is constantly being run takes about 3 seconds when not cached, and I was wondering if there were any way to speed this up. There are several tables being joined and sorted by latest date with a LIMIT of 10. All fields being joined by are indexed. So I'm not sure what else I can do. The query and EXPLAIN are listed below. Based on the information below, is there anything I can do to speed this up? There are about 100K rows in the main veg table. SELECT v.veg_name, v.veg_id, u.user_id, u.user_name, IFNULL( t.title_name, 'Untitled' ) AS title_name, ctrl.country_name, ctr.nice_country_name, te.equip_name, CONCAT( ui.first_name, ' ', ui.last_name ) AS full_name FROM veg AS v, loc_countries AS ctr, loc_countries_lang AS ctrl, loc_districts AS d, loc_states AS s, users AS u, user_intros AS ui LEFT JOIN veg_titles AS t ON v.veg_id = t.veg_id AND t.lang_id =0 LEFT JOIN tech_equip AS te ON v.equip_id = te.equip_id WHERE d.lang_id =0 AND ctrl.lang_id =0 AND s.lang_id =0 AND ctr.country_id = ctrl.country_id AND d.district_id = s.district_id AND ctr.country_id = d.country_id AND v.latest_version = 'Y' AND u.acct_status = 'Enabled' AND s.state_id = v.state_id AND u.user_id = v.user_id AND v.cur_status = 'Active' AND u.user_id = ui.user_id AND ui.lang_id =0 ORDER BY v.date_submitted DESC LIMIT 0 , 10 s ALL PRIMARY,district_id NULL NULL NULL 2457 Using where; Using temporary; Using filesort d eq_ref PRIMARY,country_id PRIMARY 3 s.district_id,const 1 ctr eq_ref PRIMARY PRIMARY 1 d.country_id 1 ctrl eq_ref PRIMARY,country_id PRIMARY 2 ctr.country_id,const 1 v ref state_id,user_id state_id 2 s.state_id 32 Using where u eq_ref PRIMARY PRIMARY 2 p.user_id 1 Using where ui eq_ref uniq_user_lang_id uniq_user_lang_id 3 u.user_id,const 1 t eq_ref veg_lang_id veg_lang_id 4 v.veg_id,const 1 te eq_ref PRIMARY PRIMARY 2 p.equip_id 1 _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
counting rows in all tables
Not using perl or php I came up with:
mysqlshow -u user --status db_name | awk -F| '{print $2,$5}' | grep -i pattern
Is there a query to do the same thing?
_
Douglas Denault
[EMAIL PROTECTED]
Voice: 301-469-8766
Fax: 301-469-0601
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke question
REVOKE ALL PRIVILEGES FROM [EMAIL PROTECTED] seems to be a 4.1 form of the command (I have 4.0.15) but adding '@localhost' did the trick. Thank you. On Wed, 17 Mar 2004, Michael Stassen wrote: [EMAIL PROTECTED] wrote: I was trying to allocate a new user and I typed something like: grant all privileges on pail to [EMAIL PROTECTED] identified by 'hill'; Here you grant [EMAIL PROTECTED] all privileges on a table named pail. I realized I made a mistake needing to specify (at least) 'pail.*', intending to grant the user access to a database I created. So to clean up I tried: revoke all privileges on mysql.pail from jill; ERROR 1141: There is no such grant defined for user 'jill' on host '%' You didn't specify the host (localhost), so you got the default, which is %. You don't have a user named '[EMAIL PROTECTED]', however, so you got an error. In the privilege tables there is an entry for the user in 'user' and an entry in 'tables_priv'. Nothing any where else. The entries look reasonable (to me). In user: all privileges='N'; In pail: Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter I assume this is my error and I can remove the two entries manually but I would like to know what I am doing wrong. You can clean up with REVOKE ALL PRIVILEGES FROM [EMAIL PROTECTED] then use GRANT ALL ON pail.* to [EMAIL PROTECTED] identified by 'hill' to grant rights to the pail db. _ Douglas Denault [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
revoke question
I was trying to allocate a new user and I typed something like: grant all privileges on pail to [EMAIL PROTECTED] identified by 'hill'; I realized I made a mistake needing to specify (at least) 'pail.*', intending to grant the user access to a database I created. So to clean up I tried: revoke all privileges on mysql.pail from jill; ERROR 1141: There is no such grant defined for user 'jill' on host '%' In the privilege tables there is an entry for the user in 'user' and an entry in 'tables_priv'. Nothing any where else. The entries look reasonable (to me). In user: all privileges='N'; In pail: Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter I assume this is my error and I can remove the two entries manually but I would like to know what I am doing wrong. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql in a jail
I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql in a jail
Thank you for the replies. Jail is a FreeBSD construction that provides a chroot'ed environment that allows you to create another instance of the entire OS. So in my case the physical server running the jails appears to the world like 5 hosts. Each jail has its own IP and is running an independent version of FreeBSD. The jail API layer keeps one instance of a jail from breaking into jail. Only the /proc file system is shared. The kernel belongs to the base system. I was hoping for a FreeBSD/mysql person who has traveled this road. Failing that, is there some residual file that MySQL uses that could trigger this error? I can tell for sure that: 1) there is no mysql socket active 2) there is no file /tmp/mysql.sock (in this jail). 3) it is possible to do. The only reason I have to suspect something in mysql is that I had to re-install to get my second jail running mysql. The re-install did not work here but I did not build the failing jail from scratch. Hence my thought that I left some cruft behind. The reinstall should have cleared /usr/local of mysql files and I cleared /var/db/mysql and /tmp by hand. I think the MySQL question here is: Can a configuration error cause mysqld to think a socket is open when in fact it is not? On Wed, 3 Mar 2004, Rhino wrote: Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 11:28 AM Subject: mysql in a jail I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql in a jail
Permissions /\ / \ /o o \ V ^ Jeez - thank you; I was looking at all the esoteric stuff whilst the snake was chewing on my leg. Thanks. Sockets are files(sorta). When I copied the file tree /tmp was propagated without global write. Not a problem in the system I used as a template because /tmp is a UFS there. Doug On Wed, 3 Mar 2004, Michael Stassen wrote: This error usually occurs for one of two reasons: 1. There is already a /tmp/mysql.sock. You've ruled this out. 2. The user mysqld runs as, typically mysql, does not have permission to write to /tmp. Normally, you would want to `chmod 1777 /tmp`. If that's not appropriate for a jail, just make sure that mysqld can write there. As far as I know, installing (or reinstalling) mysql should have no effect on /tmp permissions, so I would guess that the problem you had previously with the second jail was different. If it turns out that /tmp permissions are not the problem, take a look at the hostname.err (where hostname is the name of the machine from this jail's point of view) file in the data directory. If the contents don't make sense to you, post them in a follow-up message. Michael _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installer problem - Where did it go?
I'm a total newbie. I just ran the installer (mysql-standard-4.0.17.pkg) on my Mac (10.2.1). The installer ran correctly and said that it successfully installed the package. But I can't find it. It's not in Applications or Utilities. I actually read the Read Me file, which says that it's at /usr/local/msql. I could find no such path. I searched my local drive for all occurrences of mysql, but it found only those that occur within the Lasso package. Can someone help me out? Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improving join update
My question is how to improve an update. I had a set of records from an external source to add to a table. In the new data one of the key values was not known. It turns out that another key can be matched to key values in the old data (extracted to the table temp). The relation between the keys can be seen by: select distinct products.prodid,products.groupid,temp.groupid as newgid from products join temp on left(products.prodid,6)=left(temp.prodid,6) where products.groupid=999; Where 999 was just an arbitrary value so I could enter the new data. The select showed the mapping of left(prodid,6) -- groupid. In fact I had 49 values of groupid I could set in the new data. The update query I used: update products join temp on left(products.prodid,6)=left(temp.prodid,6) set products.groupid=temp.groupid where products.groupid=999; Clearly this query does not take advantage of the fact that left(prodid,6) -- groupid is a 1-to-1 mapping. It worked (after a while) because my data set was small. My question is how would you make such a query scalable to larger sets of records. I know I could use the table generated in the select. Is there a way in 4.0.x to do it in a single query. Can it be done with a subquery in 4.1? Lastly is there a link/book/whatever where I can read about this kind of stuff? Sorry this got kind of long. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find duplicates
The db in question is a shopping cart and I was looking for products I added that might have been duplicated in another category. My first attempt that worked. select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml order by cnt; The thing wrong with this of course is that I wanted only the prodid's where cnt1. Eventually: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1; I.e. replacing order by with a having clause. After trying many variations; are 'order by' and 'having' mutually exclusive? If so - how would you order the result table? As always thank you for any thoughts pointers. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find duplicates
blush ouch /blush To Jimmy and Chris and the list in general. This list is particualiarly kind to question of this nature. I for one appricate it. In this case it is amazing how many wrong things I came up with. On Tue, 13 Jan 2004, Chris Elsworth wrote: On Tue, Jan 13, 2004 at 02:03:26AM -0500, [EMAIL PROTECTED] wrote: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1; I.e. replacing order by with a having clause. After trying many variations; are 'order by' and 'having' mutually exclusive? If so - how would you order the result table? They shouldn't be, you just need to get the order right: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1 order by cnt; Is perfectly valid syntax. -- Chris _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inser woes
This has to be something silly, but I can not see it. Any help greatly appreciated: use newshop; insert into products set storeid=2, deptid=5, groupid=66, prodid='C2BUWS1028', prodtitle='bAntique Circle 6/20; 1/2inches-lg Pearl Buttons Novelty Buttons/b', proddesc1='3 buttons per card; 1/2 inches', price=3.75, imgsml=C2BUWS1028.jpg, imgsmlwidth=105, imgsmlheight=105, discid=12; gets ERROR 1109 at line 2: Unknown table 'C2BUWS1028' in field list mysql explain products; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | storeid | varchar(50) | | PRI | | | | deptid | varchar(50) | | PRI | | | | groupid | varchar(50) | | PRI | | | | prodid | varchar(50) | | PRI | | | : : If I put all the col=value entries on one line it seems to work. I am generating a bunch of these so I can format them in any manner that works. It seems that set col1=value1, col2=value2, ... can not be continued Is this correct? Thanks for any pointers _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inser woes
:( Thank you - my only defense - it was 04:00, I think I looked at it too long and got hung up on the line 2. Doug On Mon, 12 Jan 2004, Johan Hook wrote: Hi Doug, I think you need to quote imgsml=C2BUWS1028.jpg, like imgsml='C2BUWS1028.jpg', take care, /Johan [EMAIL PROTECTED] wrote: This has to be something silly, but I can not see it. Any help greatly appreciated: use newshop; insert into products set storeid=2, deptid=5, groupid=66, prodid='C2BUWS1028', prodtitle='bAntique Circle 6/20; 1/2inches-lg Pearl Buttons Novelty Buttons/b', proddesc1='3 buttons per card; 1/2 inches', price=3.75, imgsml=C2BUWS1028.jpg, imgsmlwidth=105, imgsmlheight=105, discid=12; gets ERROR 1109 at line 2: Unknown table 'C2BUWS1028' in field list [cut] _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating table1 from table2
Hi: I am new to MySQL and can not find how to do this. I am running 4.0.16. Table1 has the correct values for a common field. Table 2 (the real one) needs to be updated from t1 via a common field. Can this be done with joins. And if not what is the best way. Thanks for any suggestions. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql network security
On Thu, Nov 06, 2003 at 11:41:54AM -0600, Andrew Falgout wrote: Does anyone know of a method for encrypting the network traffic to and from a mysql database running on Redhat ES 2.1? At the moment, only perl automation and php web pages are accessing the database. You could tunnel your connections over an ssh tunnel, or setup an ipsec tunnel. --Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql network security
Well, you would only need to setup a single ssh tunnel. All your different apps could then use the single tunnel. You could even setup RSA authentication so that it could start manually with no password entering required. Have it automatically come up on boot, etc. MySQL has no encryption built-in, as far as I know. I would be surprised if it did. The correct way would be to use an established mechanism for secure communications such as ssh or ipsec instead of re-implementing basically the same thing in the application. I would not so much recommend pptp. SSH would likely be much easier to setup (especially if you're already using unix-like machines on both the client and server) and definately more secure. IPSEC would also be significantly more difficult, but you wouldn't have to worry so much about the secure session going down. --Doug On Thu, Nov 06, 2003 at 12:12:14PM -0600, Andrew Falgout wrote: Thanks for the quick response. I've never setup an ipsec before. I have been toying with the idea of using cipe to create a PPTP virtual network for the server to talk on. But the time to work on this project has not presented itself as of yet. I was wondering if there was an interface within mysql that would allow for encrypted traffic. The majoroity of my connection are cron jobs doing automated tasks, an ssh connection feels icky. (Yes.. a technical term) - Original Message - From: Doug Clements [EMAIL PROTECTED] To: Andrew Falgout [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 12:03 PM Subject: Re: mysql network security On Thu, Nov 06, 2003 at 11:41:54AM -0600, Andrew Falgout wrote: Does anyone know of a method for encrypting the network traffic to and from a mysql database running on Redhat ES 2.1? At the moment, only perl automation and php web pages are accessing the database. You could tunnel your connections over an ssh tunnel, or setup an ipsec tunnel. --Doug -- 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: Need help with oracledump (contributed program)
On Mon, Sep 01, 2003 at 11:09:02AM -0700, Martin Gainty wrote: Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/html/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Hi Marty, Thanks for following up. I've created a tnsnames.ora file in /usr/local/oracle/9.0.1/network/admin with the settings appropriate to my host. When I run the following commands: % setenv ORACLE_HOME /usr/local/oracle/9.0.1 % setenv ORACLE_SID VAPDEV % oracledump -c -u myUser -p myPassword I get the error message... % Can't call method do on an undefined value at /home/doug/bin/oracledump line 95. When I run it with the -x switch I see... Configuration: (remove --explain/-x option to run with this configuration) Database SID: VAPDEV Database user: myUser Database password: myPassword Tables: All tables Options: --default-databaseUse default database (VAPDEV) --with-table-comments Include table comments --with-column-commentsInclude column comments --default-precision Set to 18 --default-scale Set to 0 --complete-insert Includes list of column names in insert statements I'm not a perl guy and I'm not sure what to make of it other than the variables $nls_date_format, $nls_time_format, $nls_timestamp_format have data at run-time. Thanks again for your help so far... -- Regards, Doug Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Tue, Sep 02, 2003 at 12:25:35PM +0100, Jim Smith wrote: A TNSNAMES file isn't going to help unless you have the Oracle client software installed. If you had the software, you would already have a tnsnames file. Thanks all for your help. I've found a free java-based application (JOracleDump) and am modifying that to do what I need. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with oracledump (contributed program)
Hello, I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My SQL Search
-Original Message- From: Nathan Simms [mailto:[EMAIL PROTECTED] The problem is that I need my statement to search the product_name and description fields as well. However when I add them to my statement, it doesn't work??? The statment below does not work: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND company_name regexp '#FORM.query#' AND product_name regexp '#FORM.query#' AND description regexp '#FORM.query#' Your 'AND' logic is faulty. Try: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND ( company_name regexp '#FORM.query#' OR product_name regexp '#FORM.query#' OR description regexp '#FORM.query#' ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join query result difference between 3.23.49 and 4.0.13
hello victoria, thanks for your response. however, the real goal of my question still remains unanswered: given my sample data, how would i find the balance on an invoice with one sql statement. sure, i could query for the amount paid, then in a separate query subtract the result from the invoice total, but that seems like a step backwards. is there a way to do what i was doing with v3.23, given the new NULL behavior? thanks in advance. doug At 02:53 PM 7/17/2003 +, [EMAIL PROTECTED] wrote: -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 10:33 To: [EMAIL PROTECTED] Subject: Re: join query result difference between 3.23.49 and 4.0.13 Doug Reese [EMAIL PROTECTED] wrote: query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 | NULL |NULL | +-+--+-+ 1 row in set (0.00 sec) NULL values in this result are not expected, nor are they helpful in determining the invoice balance. It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows in the result or if all values are NULL (as in your case). And SELECT billing.amount - NULL also returns NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning: thr_alarm queue is full errors and panic with invalid backtrace
Description: I'm running mysql in a 3 server configuration, with 2 servers being slaves to the first. I'm running vpopmail, which means a connection every incoming mail and every check. I woke up this morning to a mysql that wasn't answering connections, and that had left this in the log: 030716 13:48:06 InnoDB: Started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 Fatal signal 11 while backtracing 030716 13:48:07 mysqld restarted Before this, I got thousands of these: Warning: thr_alarm queue is full I checked the logs of the other 2 replication clients, and they also had the alarm queue error, and were also sucking up large resources. I was not able to connect to them, either. Before those, both replication clients had this logged: 030716 13:42:45 Slave: received 0 length packet from server, apparent master shutdown: 030716 13:42:45 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'courtney-bin.016' position 38236125 030716 13:42:45 Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'ip-address-changed' (61)' errno: 2003 retry-time: 10 retries: 86400 How-To-Repeat: I'm not sure what is triggering this. I was hoping the backtrace might help on that, but crashing while getting a backtrace isn't exactly promising. I have dome some small amount of tuning, which involves setting the max_connections to 500 and max_connect_errors to 50, on all machines. The binary log on the master server is rotated nightly and kept around for about a week. Fix: Unknown. I restarted all 3 mysqld processes and it seems to be going ok now. Submitter-Id: submitter ID Originator: User Organization: MySQL support: none Synopsis: Warning: thr_alarm queue is full errors and panic with invalid backtrace Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.13 (FreeBSD port: mysql-server-4.0.13_1) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.13, for portbld-freebsd4.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 23 min 12 sec Threads: 11 Questions: 37671 Slow queries: 0 Opens: 42 Flush tables: 1 Open tables: 36 Queries per second avg: 27.062 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD courtney.linkline.com 4.8-RELEASE FreeBSD 4.8-RELEASE #2: Fri Jun 20 11:39:35 PDT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/COURTNEY i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rt ti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1223544 Jun 20 14:26 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Jun 20 14:26 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 577872 Jun 20 14:26 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.0.13_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.8' CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.8' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rt ti -fno-exceptions' Perl: This is perl, version 5.005_03 built for i386-freebsd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join query result difference between 3.23.49 and 4.0.13
hello, i have what seems to me a very common operation i'm performing. i need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. there must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what i expected, nor what i received with v3.23. i'm including sample data and queries with my results. i've not been able to find any relevant messages in the list archives. any comments are greatly appreciated. here's the table structure and some sample data: CREATE TABLE `billing` ( `invoice` mediumint(9) NOT NULL auto_increment, `user_id` mediumint(9) NOT NULL default '0', `invoice_date` date NOT NULL default '-00-00', `amount` float NOT NULL default '0', `timestamp` timestamp(14) NOT NULL, PRIMARY KEY (`invoice`), KEY `user_id` (`user_id`), KEY `user_invoice` (`user_id`,`invoice`) ) TYPE=MyISAM COMMENT='invoices'; INSERT INTO `billing` VALUES (1, 1, '2003-07-01', '500', 20030716092700); INSERT INTO `billing` VALUES (10001, 1, '2003-07-03', '600', 20030716092807); # CREATE TABLE `billing_payment` ( `invoice` mediumint(9) NOT NULL default '0', `amount_paid` mediumint(9) NOT NULL default '0', `payment_status` varchar(15) NOT NULL default '', `pending_reason` varchar(15) default NULL, `payment_date` date default NULL, `txn_id` varchar(20) default NULL, `timestamp` timestamp(14) NOT NULL, KEY `txn_id` (`txn_id`), KEY `invoice` (`invoice`) ) TYPE=MyISAM COMMENT='payments on invoices'; INSERT INTO `billing_payment` VALUES (1, 500, 'Completed', NULL, '2003-07-02', '112233', 20030716092746); here are the queries and results. note that query #1 gives the expected (and desired) result on both versions, but query #2 only gives the expected (and useful) result in v3.23. to give a brief explanation of the difference between the queries: invoice 1 has a payment against it, while invoice 10001 has no payment records in the payment table. === v3.23.49 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.00 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 |0 | 600 | +-+--+-+ 1 row in set (0.00 sec) === v4.0.13 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.01 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 | NULL |NULL | +-+--+-+ 1 row in set (0.00 sec) NULL values in this result are not expected, nor are they helpful in determining the invoice balance. thanks again for any suggestions, doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AND NOT
Is there any easy way to create this type of QUERY? I am trying to get a list of usernames from table A who do NOT have a linked record in Table B. D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEX
I am familiar with regex in general, but here is what I want to do... I have a field that contains 6 actual data items separated by the | symbol. Like this... a|b|c|d|e What I need to do is build a list of distinct values between each pipe, so that I get 5 arrays of values, one array for each of the drop-down boxes I want to create. The select statement I need needs to say... Give me a distinct entries matching any character up to the first | character. Or between the first and second |char, etc. Is there a way to do this with regex? ___ Interactive Media that WORKS. ___ Doug Wolfgram CEO GRAFX Group, Inc. 949.433.3641 http://www.gfx.com http://www.aimprojects.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passwords don't work in a WinME installation?
It is all disinformation to confuse Micro$oft. 8-) On Mon, 31 Mar 2003 09:45:09 -0700, Martin Gainty wrote: Michael et al- I am trying to locate ONE working MySQL example for windows. Is this a myth or is there WORKING source example out there somewhere? Thanks, Martin - Original Message - From: Michael Shulman [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 31, 2003 9:24 AM Subject: RE: Passwords don't work in a WinME installation? Start reading here: http://www.mysql.com/doc/en/Privileges.html and read to 4.2.10. Two tips I found useful: 1. The current_user() function tells you what MySQL user name it thinks that you are. This can be used to verify if you are an authenticated user. mysql SELECT current_user(); 2. Remove unneeded rows from the user table. For my installation, I removed all rows except 'root' logging in on the local machine: mysql use mysql; mysql delete from user where user != 'root'; mysql delete from user where host != 'localhost'; mysql flush privileges; mysql select user, password, host from user; +---+---+--+ | user | host | password | +---+---+--+ | root | localhost | | +---+---+--+ 1 row in set (0.00 sec) If you have a line that has an empty username or % for hostname, this will make diagnosing your security issues difficult. -ms -Original Message- From: Helge Moulding [mailto:[EMAIL PROTECTED] Sent: Sunday, March 30, 2003 11:02 PM To: [EMAIL PROTECTED] Subject: Passwords don't work in a WinME installation? I'm learning the ropes with MySQL. I've got it installed on a WinME system, and one of the first things I've noticed is that it doesn't seem to care about users. Is that correct? In particular, I've tried out the GRANT command to create a new user, but MySQL doesn't seem to recognize the new user. Here's what happens: C:\Program Files\mysql40\binmysqld C:\Program Files\mysql40\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.12-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database dummy; Query OK, 1 row affected (0.44 sec) mysql grant select on dummy.* to [EMAIL PROTECTED] identified by 'blah'; Query OK, 0 rows affected (0.28 sec) mysql quit Bye C:\Program Files\mysql40\binmysqladmin flush-privileges C:\Program Files\mysql40\binmysql -u dumdum -p dummy Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) C:\Program Files\mysql40\bin I've tried to be very careful to make sure that the password is correct :-) I don't know if the fault lies in using localhost instead of 127.0.0.1 for the host part of the user. I'd have thought that localhost and 127.0.0.1 would be synonymous... However, if I try to mess with any kind of passwords, I get difficulties. For example, I don't get to set a password for root: C:\Program Files\mysql40\binmysqladmin -u root password temp C:\Program Files\mysql40\binmysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.12-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Notice that mysql let user root in without asking for a password, even though I used mysqladmin to set a password. (In the book I'm using, MySQL Visual Quickstart Guide by Ullman, the password is supposed to be in single quotes, but in the mysqladmin help output, there are no quotes. I've tried it both ways, and neither has the expected effect.) I used winmysqladmin, which prompted me for a user name and password. But mysql lets me in without giving a password for that user, either. For that matter, mysql will let in any user, even if they haven't been created by any method I know of: C:\Program Files\mysql40\binmysql -u nooneknowsme Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.0.12-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql What's going on here? Is the password stuff broken, or is it the fault of me working on Windows ME? -- Helge Moulding mailto:[EMAIL PROTECTED]Just another guy http://hmoulding.cjb.net/ with a weird name ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To
RE: UPDATE syntax help
Very close. Only one table may be updated per UPDATE statement. Doug On Sun, 23 Mar 2003 00:52:59 -, [EMAIL PROTECTED] wrote: Hi I may be way off base here but - why do you reference IMPORT_USERS in your UPDATE statement? You aren't updating any columns in that table. Regards, Sal -Original Message- From: MySQL [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 22:33 To: MySQL Subject: UPDATE syntax help Hi all, I am having a little UPDATE syntax issue. According to the manual UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remove -?
Hi all, I want to take the data in a column, modify and then set it into another column. In specific, I want to remove the dash from our Product ID column and place it in another column without the dash. For instance, a ProdID may be 'SK-22'. I want to take this value from ProdID and place it as 'SK22' into ProdKeywords column for the same record. How would I go about removing the '-' and placing the remainder in the ProdKeywords column. Thank you, Doug Coning sql,query,queries,smallint - 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: selected
PHP Database Mailing List (http://www.php.net/) Mostly MySQL with a smattering of other db's from time to time. hth, Doug On Fri, 28 Feb 2003 13:30:40 -, Andrew wrote: Using MySQL and PHP PS. Do any of you knw of a forum specifically out there for PHP and MySQL? Andrew - 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: Create table, wrong datatype
Hello: Because you have a variable length column type (text) in the row, mysql changes char() types to varchar. This behavior is discussed in the manual in Silent Column Change Specifications under CREATE TABLE. If the stored length of these columns is an issue, you can move the variable length fields to another table. However, the varchar() type reduces storage requirements without a performance penalty. If the retrieved/displayed length is the issue, you can use the LPAD or RPAD functions as appropriate. Doug On Wed, 12 Feb 2003 09:21:31 +0100, Ulla Wensman wrote: Hello! When I run this script the char-fields is converted to varchar. Row-format is dynamic. I think it is because i have a text-field in the script. When I change the text field to somthing else I got the datatype char in the char-columns. I have tried to alter the colums to char but it doesn't work. How do I get around this problem? I use mysql-3.23.55-win and win2000. Regards Ulla #= # Table: BasAtgard #= create table BasAtgard ( AtgIdint not null, ArbBeskrId int not null, Rubrik char(40) null, Beskrivning text null, VardeTillBakasmallint not null, RegDatum datetime null, RegAvchar(20) null, AndradDatum datetime null, AndradAv char(20) null, Raknare int null, PRIMARY KEY (AtgId) ) TYPE=InnoDB; - 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
Robot Replies (WAS: Re: support question (win98_se) (auto))
Am I the only one that equates these robot replies with spam? Most of them are as meaningful as talking to micro$oft. Yeah, I filter them on input and they go to trash, but silence can be taken as consent which might be construed as endorsement. Who knows what would come next? My $0.02, Doug On Fri, 07 Feb 2003 19:17:25 -0700, [EMAIL PROTECTED] wrote: Hi [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Business_Services_Support.html * http://www.mysql.com/doc/en/Which_OS.html * http://www.mysql.com/doc/en/MySQL_licenses.html * http://www.mysql.com/doc/en/Support.html * http://www.mysql.com/doc/en/Languages.html This was an automated response to your email 'support question (win98_se)'. Final search keyword used to query the manual was 'support (win98_se)'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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: batch
On Sun, 9 Feb 2003 16:24:03 -0700, Pamela Hart wrote: My question is this: I need to input data from a .sql file into an existing and empty database I have created. I type this into terminal at the main command line: mysql -uUser database_name filename.sql. I keep getting an error message that says no such file or directory exists. I have tried every version of this command I can think of, including from within the mysql console. Where is filename.sql located? To use the .sql file as input as this command is written requires that the file be in the same directory as the mysql program. You can specify a relative or absolute path to filename.sql such as ../sql/filename.sql or /local/mysql/sql/filename.sql Please help. Hope this does. Doug - 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
CREATE TABLE, NOT NULL fields, and empty strings
I attempted to search various places for my answer but with no luck. So I'm posting here. I create the following table: create table t1 ( id varchar(5) not null, name varchar(5) not null ); I insert the following row: insert into t1 ( id ) values ( 1234 ); I do the following selects: select count(*) from t1 where name is null; -- Result = 0 select count(*) from t1 where name = ;-- Result = 1 Questions: 1) Why did the insert succeed since the name field is not null and I didn't provide a value? 2) Why does MySql think it's correct to substitute an empty string for a non-provided value? - 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 it my English or does this sound bad?
I love it!!! 8-)) You are correct that it does sound a bit rude, even when English is your first language and you stop to think about it. However, what the statement means is identical to The sex of the animal can be represented... and it is acceptable grammar. Thanks for making my day. Doug On Wed, 5 Feb 2003 15:58:21 +1300, Defryn, Guy wrote: I was just reading through the Mysql manual section 3.32 And came across a sentence that started like this Animal sex can be represented in a variety of ways My mother tongue is not English but doesn't it sound rude ? :-) mysql CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), - species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); VARCHAR is a good choice for the name, owner, and species columns because the column values will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to you. (If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.) Animal sex can be represented in a variety of ways, for example, m and f, or perhaps male and female. It's simplest to use the single characters m and f. Query/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: Deleting a database in Mysql
See DROP DATABASE Doug On Wed, 5 Feb 2003 22:50:25 +0800, Calvin Lam wrote: Hi, I am a newbie with this and I need help with Mysql How can I delete a database in Mysql 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 - 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, PHP and Javascript
Steve: On Fri, 31 Jan 2003 08:27:11 +, Steve Davies wrote: Is it possible to mix javascript and PHP in the same script?? Certainly. Exactly as HTML and PHP are intermingled in a document. I have a number of web based apps written in PHP/MySql and while they are functionally pretty good they are aesthetically garbage. I'll like to pretty up the interfaces with rollovers etc, but haven't got time to learn JS properly especially if I have to completly re-write the functionality. Using PHP to enhance javascript's functionality doesn't eliminate the need to know javascript. Any help greatly appreciated Your inquiry will receive more detailed help on one of the lists at www.php.net. Regards, Doug - 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: restart or not?
FLUSH PRIVILEGES On 29 Jan 2003 07:05:29 +0800, Jon Miller wrote: When granting someone permissions are these permissions dynamic or do I have to reload mysql? Also we have a developer who stated he cannot access the database remotely. I've granted him privileges as follows since he works on the entire system both locally and remotely. There is only one database in the system. I've set a password for access to the system also. grant all privileges on *.* to joseph@% identified by 'his_password' with grant option; grant all privileges on *.* to joseph@localhost identified by 'his_password' with grant option; GRANT RELOAD,PROCESS ON *.* TO joseph@localhost; GRANT USAGE ON *.* TO joseph@localhost; -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - 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: cannot access mysql and see privileges
You need to include the (correct) Root password in mysql_connect(h,u,p) On 29 Jan 2003 07:42:37 +0800, Jon Miller wrote: Warning: Access denied for user: 'root@localhost' (Using password: NO) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Error MySQL said: Access denied for user: 'root@localhost' (Using password: NO) Also I get an error message when trying to show grants privileges using: show grants; Thanks -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd 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: Adding a user problem
Assuming you logging in from the same server on which mysqld is running, you need a dmiller@localhost user. The % wildcard is valid only for tcp connections. This is thoroughly explained in the MySQL manual, chapter 4.3.5. hth, Doug On Mon, 27 Jan 2003 15:03:35 -0500, Devin Miller wrote: I am pretty new to mysql and have been teaching myself slowly how to use it. I am however stuck on a problem. I login to mysql as user root and created a database named devin. I then issue the following commands: GRANT ALL PRIVILEGES ON devin.* TO dmiller@% IDENTIFIED BY password; The command returns a Query ok and 0 rows affected. However, when I try to login with dmiller (mysql -u dmiller -p) I get a 1045 Access denied error. I have triple checked spelling and such and it looks good as far as I know. Any Ideas? Thanks a lot in advance for your help. Btw I am using MySQL ver. 3..23.52. Devin Miller - 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 and webpages
For decent explanations of concepts and practicalities, try http://www.webmasterbase.com/subcats/53 and more specifically http://www.webmasterbase.com/article/228 There are also good and more advanced tutorials at http://www.onlamp.com/ http://www.devshed.com/ These are superficial scratches on the surface for web-based tutorials. Enjoy, Doug On Sun, 26 Jan 2003 11:00:18 -, Scott Lewis wrote: As you can tell - very new to Mysql. I was wondering, has anyone some links to howto or even better examples of databases and webpages that I can install, and see the web interaction with webpages and a mysql database. Looking at going down that road as exaaples would help. Scott - 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: Safe to delete duplicate root user? Any users?
You state that you searched the web and the documentation, but did you search the MySQL manual? 4.2 General Security Issues and the MySQL Access Privilege System http://www.mysql.com/doc/en/Privilege_system.html 4.3 MySQL User Account Management http://www.mysql.com/doc/en/User_Account_Management.html And, specifically, read 4.3.5 Adding New Users to MySQL http://www.mysql.com/doc/en/Adding_users.html On Thu, 23 Jan 2003 18:15:00 -0500, Robert K S wrote: I have just set up MySQL and the phpmyadmin script and I would like to add some users and do some other administrative tasks to assure security. Above all, I want to make sure no user (or php script) will have access to any databases without a user name and password. Right now if I look at the list of users, I see: Any hostname root hostname Any localhost root localhost 1. What's the difference between hostname users and localhost users? Why are there two? Is one of them safe to delete? If so, which one (hostname or localhost)? (If it's of any relevance, I only plan on accessing the databases through SSH sessions to the host machine on which the MySQL server is running, and through PHP scripts also running on the same host machine. 2. If there IS a difference, when I create new users, do I need to create two of each? I.e., newuser1 hostname newuser1 localhost 3. What's the deal with the Any users? Why do they exist? Can I delete them? Like I said, I don't want anyone to have access to the databases without a MySQL username and password. I've searched the documentation and the web for answers, but I can't seem to find what I'm looking for. Thanks to you if you can help, and you rock. All the best, Robert K S _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - 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: High list latency?
You mean like your inquiry which took 8:01:27 between the time you sent it and when it hit my mail server? Doug On Thu, 23 Jan 2003 20:44:56 -0800, Jeremy Zawodny wrote: Is anyone else seeing it take 4-8 hours for mail to get thru this list recently? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 5 days, processed 145,791,837 queries (323/sec. avg) - 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 possible?
- Original Message - From: [EMAIL PROTECTED] To: Doug Coning [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 9:15 AM Subject: Re: Is this possible? Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I am trying to merge the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - 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
Is this possible?
Is this possible in MySQL without using a temporary table with multiple SQL commands: I am trying to SELECT the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - 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: Financial packages
You might find something at http://www.linuxapps.com/ Doug On Tue, 21 Jan 2003 00:06:32 -0800, Ed Reed wrote: Does anyone have a list of manufactures of financial packages that will run with MySQL? We need to move from Intuit Quickbooks to something that can potentially scale to as many as 200 users. Greatplains and Solomon appear to only run on SQLServer. Any suggestions or past upgrade experiences would be great. - 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: checking permissions and users in mysql
Certainly. http://www.mysql.com/doc/en/User_Account_Management.html On 22 Jan 2003 06:50:27 +0800, Jon Miller wrote: Is there a way to check or view in mysql what permissions users have to certain databases? -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - 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
Group By
Hi everyone, I want to run a query that will combine the results of 5 columns into 1 column, and then group them by similar results so that only 1 instance is returned. I currently have the following query: SELECT Category FROM gs_Products GROUP BY Category ORDER BY Category ASC How would I modify this query to return all the items from Category, Category2, Category3, Category4 and Category5 and then group them all by similarities? Thank you, Doug Coning - 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: view privileges
SELECT * from mysql.tables_priv where Table_name = 'myTable'; You will probably have to log on as 'root' unless you have created another user with the same privileges. The mysql GRANTs system is used to control USER privileges. This means that for a given table you can view which users can perform what operations on that table. The same holds true of databases and columns within tables. If you have never issued table-specific GRANT statements, then there will be no entries in the mysql.tables_priv table. That is because you have been granting database-wide access to all table functions (Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter) for all users. This is not a good idea. If you want to find out to which users you have been granting all these table-wide privileges, look at the mysql.db table, SELECT * from mysql.db where db='mydb'; The above is a very superficial description of the mysql Access Privilege system. You need to read a lot more in the manual in section 4.2, General Security Issues and the MySQL Access Privilege System. Doug On Thu, 16 Jan 2003 01:15:03 -0600, Addison Ellis wrote: hello, how can i view privileges for a table in a db? thank you, addison sql,query,queries,smallint -- - 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
Confirmation of SELECT...IN syntax
It's my understanding that embedded selects are NOT allowed in MySql. Is this correct? Example: SELECT t1.* FROM table1 t1 WHERE ( t1.Id IN ( SELECT t2.Id FROM table2 t2 ) ) Doug Beyer SAS Research and Development 503-617-7103 (Portland) [EMAIL PROTECTED] SAS... The Power to Know - 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: Password problem
On Wed, 15 Jan 2003 11:00:21 -0600, Neil Aggarwal wrote: Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Yes. See Chapter 4.3.5 of the mysql manual. Note that we must issue GRANT statements for both monty@localhost and monty@%. hth, Doug Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, 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: Beginners Running problems
To paraphrase Yogi Berra: You can see an awful lot just by looking. http://www.mysql.com/doc/en/Can_not_connect_to_server.html Doug -- For Happy Filters, use SQL, query On Sat, 11 Jan 2003 12:50:29 -, alan.keith.simons wrote: Hi I have installed a downloaded version of mysql on my pc but am experiencing problems when I try to run the MySQL monitor. From the command line:- C:\mysql\bin when I type in mysql to activate the monitor, I get the error message: ERROR 2003: Can't connect to MySql server on 'localhost' (10061) I cannot seem to find a solution to this problem on your site. Best regrads Alan Simons - 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: Book recomendations
Hi: Go look in the section where they keep the O'Reilly books and pick up MySQL Cookbook by Paul DuBois ISBN 0-596-00145-2. It is fair to say that Paul has an above average grasp of the topic. He is often quoted on this list. Regards, Doug On Wed, 08 Jan 2003 08:34:18 -0500, Charles Mabbott wrote: I got a gift certificate for book store. I am looking for good MySql books with reference, examples, etc? Anyone have some ideas on good books to look at Chuck - 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: Fatal error
1. This is a PHP syntax question. 2. The PHP manual gives a specific example in the MySQL Functions. $link = mysql_connect(localhost, webuser, webpass) Doug On Sun, 29 Dec 2002 20:11:14 +0200, Pikasz Gyorgy wrote: Hi all!!! Red Hat 7.3, MySQL, PHP I tried to connect to MySQL server with my login name and password, with the next command in my php's file: mysql_connect(localhost, webuser, webpass) In the mysql.user table exist webuser/webpass and in mysql.db table too for an specified database. ...then appear the next error and I don't understand why: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/vk/vk.php on line 11 Thanks picas - 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
