Re: freebsd 5.1 + mysql 4.0.13
Jeremy Zawodny wrote: On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. no i don't *need* it, but switching os's would mean more downtime while possibly not even fixing the problem. and if i were switching os, i would probably go to linux anyway... i've narrowed the problem down already: i have specified "enable-locking" and "skip-name-resolve" in my.cnf now, and i haven't had any corruptions for a whole day now. it looks like one of those two options fixes the problem. i can't really tell which one of them does it, and i don't feel like playing russian roulette trying to find that out, so this is my works-for-me case for now... cheers richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/INNODB speed on large databases
On Sat, Jun 28, 2003 at 01:47:28PM -0400, Wendell Dingus wrote: > Hello, I've got a situation I'm hoping someone here can help me out with. We > have a web server serving up data via some PHP programs pulling from MySQL > (3.23.56 currently) in INNODB tables. There are 40+ (active) databases > totalling a bit over 28 Gigabytes at this point. As we add more users speed > is beginning to drop... > > Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of > RAM. 4 x 15K RPM drives RAID5. It's hitting load averages >12 during peak > times and database query speeds are really starting to drag... Uh oh. > I've talked some with the consulting folks at MySQL and have the intention > of contracting them to help analyze our database layout, structure, and > queries to help optimize things. Smart move. > My quandry is this though.. The existing server has 4GB but I can't get > innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that > route if it could be utilized, I've not figured out how though and assume > that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a > 64-bit address space sounds good. Yeah, if you want more stuff in memory, look seriously at the Opterons. > I'm sure I can get the LVS approach with a load balancer and 3 or 4 back-end > "real" servers going without too much trouble. Where do I spend the money > most wisely though? More and more and more RAM in each backend server or is > anything over 2GB going to mostly not be used (Probably P4 2.4Ghz XEON > boxes). Drive speed? I'm thinking I'll test out using fast SCSI drives and > maybe some SATA drives RAID-0 stiped versus non-striped to see the speed > difference. My whole goal is speed, speed, and more speed out of > MySQL! That's what I'd suggest. Get some SCSI disks with really low seek times (4ms) and stripe using RAID-0. > The > actual web applications place a very small load on the server, viewing top > or mytop show that database access is where all the time is going and > primarily on the 2 largest databases. A query against one of the other > databases when the server is mainly idle happen in very few seconds or > tenths. The same query (data layout is basically identical across all the > databases) takes > 2 minutes sometimes on either of the largest 2 databases. Can you post the query and the result of running it thru EXPLAIN? > I have the bosses ear for hardware _now_ though and want to move > forward before that opportunity passes. I'll post the database specs > at the end of this. If you were hosting that much data without a ton > of inserts all day long but lots of selects where would you > concentrate your efforts? Speed of CPU or speed/quantity of RAM or > speed of drives or what? You haven't told us how busy the CPU gets, so I'm going to assume it's *not* the bottleneck. I'd focus ot disk performance and having sufficent memory. > Would you load balance a quantity of servers or lean more towards a > couple of much larger 64-bit servers? Running entirely out of RAM > is very appealing but somehow I suspect that will be cost > prohibitive :) Just buy reasonable hardware, load-balance it, and > then get the MySQL folks to help optimize things? That's the approach we use at work and it works well. Keeps the costs down and makes [most] scaling pretty easy. > Mytop output after about 28 hours of uptime so this is just Friday during > business hours access primarily: > MySQL on localhost (3.23.56-Max-log) > up 1+04:56:30 [13:29:15] > Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 > Threads Total: 33Active: 1 Cached: 0 > Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 Ugh. Only 17 queries/sec average? Hmm. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 813,104,760 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/PHP ?
Thanks Salam, but a question: this would mean that I must do by hand every record ID link, no? Ted On Sunday, June 29, 2003, at 02:29 AM, Salam Baker Shanawa wrote: Hi Ted, 1. Modify the query which presents all records by selecting let's say the peopleID. 2. When you build your update link you can use that ID to ask for a particular record. 3. Then you can use the same query + " AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK". something like this: ALL RECORDS: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; PARTICULAR RECORD: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK; regards Salam [EMAIL PROTECTED] wrote: I'm writing here in hopes of finding someone who uses this and might be able to help me. I am dealing with a recordset who's query is thus: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; My tables and keys are: table 1) people - primary key = peopleID table 2) make - primary key = makeID table 3) models - primary key = modelID - foreign key = make ID table 4) machines - primary key = machinesID - foreign keys = peopleID, modelID The above query (using Dreamweaver MX speak) is a "repeating region" behavior so that I get a "master" PHP page in web browser that displays all my records with the fields as I wish them (using the data from the 4 tables), that works fine --thanks all. At the end of each row (record) on that "master" page I am building 2 links: Update and Delete. The Update link will lead to a "detail page" of that particular record, identical visually to the particular record in the master page except that it will be presented in an editable/submit form -that will update the record in the database. Right now my problem is how to adjust the recordset query -above- to produce the result that will give that specific record/row that the link is next to. Right now I have the link working but I am using the same query as above and I am not getting the row that is "next to" the link, i.e., I'm not getting the correct record -but some other record. If anyone on this list has any knowledge and could help get over this hurdle I would be EXTREMELY grateful -been trying to figure this out for days. I apologize in advance for posting this here if that was wrong. I won't do again, I'm just looking for some help. I have several books and have been to other forums and the internet... still working on >> it. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: > hi! > > i'm using mysql 4.0.13 from the standard freebsd 5.1 ports distribution, > and i keep getting repeated random table corruptions. the same thing > happens when building both with the native threading lib, and with > linuxthreads. > > i figured it would be related to threading as it's always happening when > the mysql server is loaded, that is has lots of concurrent queries on > the same table going on. suddenly the table would get corrupted, and i > have to run a "repair table" on it (this always nukes a few rows from > the table). it's reproducable only in the sense that it keeps happening > randomly ;) > > are there any known issues with this combination, or with mysql 4.0.13 > or freebsd 5.1 in general? what could be the cause for this, and how > could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 813,050,547 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/PHP ?
Hi Ted, 1. Modify the query which presents all records by selecting let's say the peopleID. 2. When you build your update link you can use that ID to ask for a particular record. 3. Then you can use the same query + " AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK". something like this: ALL RECORDS: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; PARTICULAR RECORD: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK; regards Salam [EMAIL PROTECTED] wrote: I'm writing here in hopes of finding someone who uses this and might be able to help me. I am dealing with a recordset who's query is thus: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; My tables and keys are: table 1) people - primary key = peopleID table 2) make - primary key = makeID table 3) models - primary key = modelID - foreign key = make ID table 4) machines - primary key = machinesID - foreign keys = peopleID, modelID The above query (using Dreamweaver MX speak) is a "repeating region" behavior so that I get a "master" PHP page in web browser that displays all my records with the fields as I wish them (using the data from the 4 tables), that works fine --thanks all. At the end of each row (record) on that "master" page I am building 2 links: Update and Delete. The Update link will lead to a "detail page" of that particular record, identical visually to the particular record in the master page except that it will be presented in an editable/submit form -that will update the record in the database. Right now my problem is how to adjust the recordset query -above- to produce the result that will give that specific record/row that the link is next to. Right now I have the link working but I am using the same query as above and I am not getting the row that is "next to" the link, i.e., I'm not getting the correct record -but some other record. If anyone on this list has any knowledge and could help get over this hurdle I would be EXTREMELY grateful -been trying to figure this out for days. I apologize in advance for posting this here if that was wrong. I won't do again, I'm just looking for some help. I have several books and have been to other forums and the internet... still working on it. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fix_privilege_tables doesn't fix privilege tables [50 character or so descriptive subject here (for reference)]
>Description: When I start up mysqld (4.1.0-alpha), I get the message in the error log: mysql.user table is not updated to new password format: Disabling new password usage until mysql_fix_privilege_tables is run I *HAVE* run mysql_fix_privilege_tables. At least 4 times. I have a whole bunch of privilege columns including super_priv. Every time I run it it corrupts the (select-only web server) users I created since upgrading (from 3.23.56 to 4.1.0-alpha) by handing out privileges I don't want these users to have, like CREATE TEMPORARY TABLES. What's the point of SHOW PRIVILEGES? There are a lot of privilege columns in mysql.user that don't show in SHOW PRIVILEGES, yet I can grant them and display them in SHOW GRANTS FOR ... . I thought it was to keep the list of privileges in sync with what the server actually supports? >How-To-Repeat: Start mysqld. Get warning message. Run mysql_fix_privilege_tables. Repeat ad nauseum. >Fix: Is the mysql_fix_privilege_tables that came with 4.1.0-alpha really up to date? >Submitter-Id: >Originator:Gordon Burditt >Organization: >MySQL support: none [none | licence | email support | extended email support ] >Synopsis: mysql_fix_privilege_tables doesn't fix the privilege tables >Severity: non-critical >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-4.1.0-alpha (FreeBSD port: mysql-server-4.1.0_1) >Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for >portbld-freebsd4.7 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.1.0-alpha-log Protocol version10 Connection mysql.burditt.org via TCP/IP TCP port3306 Uptime: 2 hours 25 min 6 sec Threads: 1 Questions: 385428 Slow queries: 7 Opens: 35 Flush tables: 1 Open tables: 29 Queries per second avg: 44.272 >C compiler:2.95.4 >C++ compiler: 2.95.4 >Environment: System: FreeBSD hammy.lonestar.org 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Wed Feb 19 01:29:17 CST 2003 [EMAIL PROTECTED]:/scratch5/i386-obj/usr/src/sys/HAMMY i386 Some paths: /usr/local/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-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1218496 Dec 3 2002 /usr/lib/libc.a lrwxrwxr-x 1 root wheel 9 Dec 3 2002 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 574916 Dec 3 2002 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.1.0_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--with-openssl' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.7' 'CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.7' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql-test-run failure Mac OS X 10.2.6 fails ctype_latin1_de test [assumed]
From: root <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [mysql-test-run failure Mac OS X 10.2.6] >Description: >How-To-Repeat: < sudo perl mysql-test-run> >Fix: < unknown > >Submitter-Id: >Originator:System Administrator >Organization: >MySQL support: [none] >Synopsis: >Severity: <[ non-critical | serious | critical ] (one line)> File Edit Options Buffers Tools Help >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-4.0.13 (Source distribution) >C compiler:gcc (GCC) 3.1 20020420 (prerelease) >C++ compiler: g++ (GCC) 3.1 20020420 (prerelease) >Environment: System: Darwin localhost 6.6 Darwin Kernel Version 6.6: Thu May 1 21:48:54 PDT 2003; root:xnu/x\ nu-344.34.obj~1/RELEASE_PPC Power Macintosh powerpc Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/libexec/gcc/darwin/ppc/3.1/specs Thread model: posix Apple Computer, Inc. GCC version 1151, based on gcc version 3.1 20020420 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x 1 root wheel 15 Jun 25 20:12 /usr/lib/libc.dylib -> libSystem.dylib Configure command: ./configure '--prefix=/usr/local/stayathome/mysql' '--with-openssl=/usr' '--w\ ith-embedded-server' '--with-vio' '--enable-assembler' '--sharedstatedir=/usr/local/stayathome/m\ ysql/share/com' '--sysconfdir=/usr/local/stayathome/mysql/etc' '--infodir=/usr/local/stayathome/\ mysql/share/info' '--mandir=/usr/local/stayathome/mysql/share/man' Sorry for the cut and paste but the mysqlbugs script put me into an environment that I am not used to. This may be useless to you but I am interested. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
On Saturday, June 28, 2003, at 07:15 PM, Bruce Feist wrote: otherguy wrote: On Saturday, June 28, 2003, at 03:43 PM, MyLists wrote: That gets me halfway there BF: Does it? No, I don't think it does, upon further consideration and testing... I thought it did b/c I read, and misinterpreted the UNION documentation BF: In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. This is right. The key for my situation is that I need ONLY the records that exist in BOTH. Any other thoughts for this, or am I bumping up against the limits of SQL in general? Well, keep in mind that although SQL can do a lot in a single statement, it can't always do *everything* required for a business function in one statement. As I'm painfully aware of, but was hopeful about. [snip] Here's a sequence. [snip] I can't thank you enough for your time and effort. I should be able to tweak this enough to make it usable. Thanks again. Now I just have to beat up the system guys for making me do this :) Thank you, -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
otherguy wrote:
On Saturday, June 28, 2003, at 03:43 PM, MyLists wrote:
That gets me halfway there
BF: Does it?
No, I don't think it does, upon further consideration and testing...
I thought it did b/c I read, and misinterpreted the UNION
documentation
BF: In your original question, you'd indicated that you only wanted
zips where *both* criteria were met -- enough CIRGs and enough
CILTs. By using a UNION, you'll be getting zips where *either* is met.
This is right.
The key for my situation is that I need ONLY the records that exist in
BOTH.
Any other thoughts for this, or am I bumping up against the limits of
SQL in general?
Well, keep in mind that although SQL can do a lot in a single statement,
it can't always do *everything* required for a business function in one
statement. In a future release (5.0, if I remember correctly) you'll be
able to do it with views. For now, I believe you'll need to use
temporary tables and multiple SQL statements.
Here's a sequence. None of the below has been tested; it will likely
require some tweaking. In particular, the UPDATEs I show you, if they
work at all, will only work in relatively new versions of MySQL (4.1 and
higher, maybe?). In versions 4.0.x, there are other techniques that I
think will work similarly, although I haven't used them; the sytnax is
different, though.
CREATE TEMPORARY TABLE cirg
SELECT z.quota_zip2.zipcode, SUM(c.quota_actual) AS cirg_actual,
SUM(c.quota_limit) AS cirg_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code =
c.agent_code AND z.appl = c.appl
WHERE c.appl = 'CIRG'
GROUP BY z.zipcode
HAVING cirg_actual >= cirg_limit;
CREATE TEMPORARY TABLE cilt
SELECT z.quota_zip2.zipcode, SUM(c.quota_actual) AS cilt_actual,
SUM(c.quota_limit) AS cilt_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code =
c.agent_code AND z.appl = c.appl
WHERE c.appl = 'CILT'
GROUP BY z.zipcode
HAVING cilt_actual >= cilt_limit;
CREATE TEMPORARY TABLE zips
SELECT cirg.zipcode, cirg_actual, cirg_limit, cilt_actual, cilt_limit
FROM cirg INNER JOIN cilt ON cirg.zipcode = cilt.zipcode
ORDER BY cirg.zipcode;
SELECT * FROM zips;
UPDATE listmaster SET status = 'WD' WHERE zipcode IN (SELECT zipcode
FROM zips);
DROP TABLE zips;
DROP TABLE cirg;
DROP TABLE cilt;
When views become available, you will be able to create 'cirg', 'cilt',
and 'zips' views like the temporary tables above and just leave them out
there; then you will always be able to execute the SELECT above as a
single statement.
Another approach, which is slightly simpler but harder to understand, is
to use a common temporary table for the CIRGs and CILTs.
CREATE TEMPORARY TABLE zipapp
SELECT z.quota_zip2.zipcode, z.appl, SUM(c.quota_actual) AS sum_actual,
SUM(c.quota_limit) AS sum_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code =
c.agent_code AND z.appl = c.appl
WHERE c.appl IN ('CIRG', 'CILT')
GROUP BY z.zipcode, z.appl
HAVING sum_actual >= sum_limit;
CREATE TEMPORARY TABLE zips
SELECT cirg.zipcode, cirg.sum_actual, cirg.sum_limit, cilt.sum_actual,
silt.sum_limit
FROM zipapp cirg INNER JOIN zipapp zilt ON cirt.zipcode = cilt.zipcode
WHERE cirt.appl = 'CIRT' AND cilt.appl = 'CILT'
ORDER BY z.zipcode;
SELECT * FROM zips;
UPDATE listmaster SET status = 'WD' WHERE zipcode IN (SELECT zipcode
FROM zips);
DROP TABLE zips;
DROP TABLE zipapp;
Another simplification that you can introduce would be to drop all
tables involved in a single statement (i.e., "DROP TABLE zips, zipapp"),
but that reduces portability.
Bruce Feist
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/PHP ?
I'm writing here in hopes of finding someone who uses this and might be able to help me. I am dealing with a recordset who's query is thus: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; My tables and keys are: table 1) people - primary key = peopleID table 2) make - primary key = makeID table 3) models - primary key = modelID - foreign key = make ID table 4) machines - primary key = machinesID - foreign keys = peopleID, modelID The above query (using Dreamweaver MX speak) is a "repeating region" behavior so that I get a "master" PHP page in web browser that displays all my records with the fields as I wish them (using the data from the 4 tables), that works fine --thanks all. At the end of each row (record) on that "master" page I am building 2 links: Update and Delete. The Update link will lead to a "detail page" of that particular record, identical visually to the particular record in the master page except that it will be presented in an editable/submit form -that will update the record in the database. Right now my problem is how to adjust the recordset query -above- to produce the result that will give that specific record/row that the link is next to. Right now I have the link working but I am using the same query as above and I am not getting the row that is "next to" the link, i.e., I'm not getting the correct record -but some other record. If anyone on this list has any knowledge and could help get over this hurdle I would be EXTREMELY grateful -been trying to figure this out for days. I apologize in advance for posting this here if that was wrong. I won't do again, I'm just looking for some help. I have several books and have been to other forums and the internet... still working on it. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_connections being ignored/overridden?
I regularly have users email me reporting that they've hit the max (usually begging for me to raise or remove the limit) An example (from this morning) is: SQL Error : 1226 User 'algalord' has exceeded the 'max_questions' resource (current value: 800) I similarly do get reports of connection limits too, so I have assumed the quotas operate properly. My server has two databases owned by root. All other databases are created via one script and permissions are set with the following statement: grant select, insert, update, delete, index, alter, create, drop on $dbname.* to $username@'%' identified by '$password' with max_queries_per_hour 800 max_updates_per_hour 0 max_connections_per_hour 200 (note the three php variables). I verified this particular users entry in mysql.user and it is correct.Users are mutually exclusive. A user has access to only their 1 database. My version is 4.0.13-standard-log. Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Gary Huntress" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 7:09 PM Subject: Re: max_connections being ignored/overridden? > At 18:15 -0400 6/28/03, Gary Huntress wrote: > >Yes, I am referring to MAX_CONNECTIONS_PER_HOUR, poorly worded on my part > >sorry. > > > >Users are granted access to only one database (barring errors in my scripts) > >and I did verify that his entry in mysql.user is 200 for max_connections. > >And the connections in the logfile are for the same user each time. > > > >.and they continue.about 10 per second :( > > Okay, well, this is weird then. > > You say other accounts get rejected when they hit the limit. > Do you have other such accounts that are set up exactly the same > way as the problematic user? > > What version of MySQL are you using? > > > > > > >Regards, > >Gary "SuperID" Huntress > >=== > >FreeSQL.org offering free database hosting to developers > >Visit http://www.freesql.org > > > > > > > > > >- Original Message - > >From: "Paul DuBois" <[EMAIL PROTECTED]> > >To: "Gary Huntress" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > >Sent: Saturday, June 28, 2003 5:45 PM > >Subject: Re: max_connections being ignored/overridden? > > > > > >> At 17:26 -0400 6/28/03, Gary Huntress wrote: > >> >I have some simple user quotas set to help balance my server load. I > >think > >> >I've implemented them properly because people do whine to me when they > >> >reach the query limit. > >> > > >> >I have max_connections set to 200, but right now I'm watching connections > >> >for 1 user scroll by at an alarming rate. 7800 in less than 5 minutes. > >> > > >> >What could I have done wrong for this user such that his max_connections > >> >value of 200 is being ignored? > >> > >> Maybe he's connecting as an account other than what you think, and that > >> account has no limit? > >> > >> By the way, max_connections is a server variable, not a user variable. > >> I assume you're really talking about MAX_CONNECTIONS_PER_HOUR, as used > > > in the GRANT statement? > >> > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
On Saturday, June 28, 2003, at 03:43 PM, MyLists wrote: otherguy wrote: That gets me halfway there Does it? Yes, it does. No, I don't think it does, upon further consideration and testing... I thought it did b/c I read, and misinterpreted the UNION documentation In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. This is right. No. The key is that each independent query was returing the results he wanted - so, the UNION statement will simple append these two results into one long dataset - the WHERE clause, criteria, or even the number of records is really not affected. So is this. If I just needed to know that quota had been met for EITHER, then this would work perfectly for me. The key for my situation is that I need ONLY the records that exist in BOTH. Any other thoughts for this, or am I bumping up against the limits of SQL in general? Thanks again! -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
- Original Message - From: "Bruce Feist" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 6:28 PM Subject: Re: Advanced Query Help (My brain hurts!) > otherguy wrote: > > > That gets me halfway there > > Does it? Yes, it does. >In your original question, you'd indicated that you only > wanted zips where *both* criteria were met -- enough CIRGs and enough > CILTs. By using a UNION, you'll be getting zips where *either* is met. No. The key is that each independent query was returing the results he wanted - so, the UNION statement will simple append these two results into one long dataset - the WHERE clause, criteria, or even the number of records is really not affected. Thanks, Dennis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
OK - one more freebie :-) How about a TEMP table? You can create a temp table, fill it with the results of your UNION statement and then JOIN it with this new UPDATE query - that should get you what you need. Note that this is a workaround for MySQL - in other DBRMS, you can use a sub-SELECT to query the UNION result set - but for the purposes of MySQL, you're going to have to JOIN on a TEMP table. Good Luck! Dennis - Original Message - From: "otherguy" <[EMAIL PROTECTED]> To: "MyLists" <[EMAIL PROTECTED]> Cc: "MySQL List" <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 6:17 PM Subject: Re: Advanced Query Help (My brain hurts!) > Thank you! > > That gets me halfway there, and not to my surprise, it's not even that > hard! I should've known that it wouldn't be. > > So the other part that I truly have no idea how to do the following: > > UPDATE this_other_table > SET satus = > WHERE zipcode IN (); > > can someone provide me with some pointers in the right direction? > > (SELECT > quota_zip2.zipcode > FROM > quota_zip2 INNER JOIN quota_control2 ON >(quota_zip2.agent_code = quota_control2.agent_code) AND >(quota_zip2.appl = quota_control2.appl) > WHERE > quota_control2.appl = "CIRG" > GROUP BY quota_zip2.zipcode > HAVING SUM(quota_control2.quota_actual) >= > SUM(quota_control2.quota_limit) ) > > UNION > > (SELECT > quota_zip2.zipcode > FROM > quota_zip2 INNER JOIN quota_control2 ON >(quota_zip2.agent_code = quota_control2.agent_code) AND >(quota_zip2.appl = quota_control2.appl) > WHERE > quota_control2.appl = "CILT" > GROUP BY quota_zip2.zipcode > HAVING SUM(quota_control2.quota_actual) >= > SUM(quota_control2.quota_limit) ) > > ORDER BY zipcode; > > Thanks! > -Cameron Wilhelm > > On Friday, June 27, 2003, at 06:42 PM, MyLists wrote: > > > How about a UNION statement? If the two queries are independently > > returning > > what you need, then you can just "append" the two results by using > > UNION. > > > > Good Luck! > > > > Dennis > > > > - Original Message - > > From: "otherguy" <[EMAIL PROTECTED]> > > To: "MySQL List" <[EMAIL PROTECTED]> > > Cc: "Terry Vanstory" <[EMAIL PROTECTED]> > > Sent: Friday, June 27, 2003 8:50 PM > > Subject: Advanced Query Help (My brain hurts!) > > > > > >> Hey guys, I'm about to dump a doozy on your collective knowledge and > >> goodwill, and hope for some help or some pointers. I'm not great with > >> advanced SQL, and I've gotten as far as my brain and the resources > >> I've > >> been using will allow me to get for the time being. > >> > >> I need help with two things: > >> 1) How can I effectively combine the following two queries? The only > >> difference between them is the `appl` condition. I effectively need > >> to > >> do an INNER JOIN on the zip code of the results of both queries so > >> that > >> only zip codes that exist in both result sets are returned: > >> > >> SELECT > >> quota_zip2.zipcode, > >> SUM(quota_control2.quota_actual) AS sum_actual, > >> SUM(quota_control2.quota_limit) AS sum_limit > >> FROM > >> quota_zip2 INNER JOIN quota_control2 ON > >>(quota_zip2.agent_code = quota_control2.agent_code) AND > >>(quota_zip2.appl = quota_control2.appl) > >> WHERE > >> quota_control2.appl = "CIRG" > >> GROUP BY quota_zip2.zipcode > >> HAVING sum_actual >= sum_limit > >> ORDER BY quota_zip2.zipcode; > >> > >> SELECT > >> quota_zip2.zipcode, > >> SUM(quota_control2.quota_actual) AS sum_actual, > >> SUM(quota_control2.quota_limit) AS sum_limit > >> FROM > >> quota_zip2 INNER JOIN quota_control2 ON > >>(quota_zip2.agent_code = quota_control2.agent_code) AND > >>(quota_zip2.appl = quota_control2.appl) > >> WHERE > >> quota_control2.appl = "CILT" > >> GROUP BY quota_zip2.zipcode > >> HAVING sum_actual >= sum_limit > >> ORDER BY quota_zip2.zipcode; > >> > >> I cannot change the where clause to > >> WHERE `appl` = "CIRG" OR `appl` = "CILT" > >> because there might be data that would result in a situation where the > >> the sum_actual would meet or exceed the sum_limit for a zip code > >> (using > >> both `appl`'s in the where), whereas running them separately would > >> result in the sum_actual not being met for one of the `appl`'s for > >> that > >> zip_code (it would have been exceeded for the other `appl`). > >> > >> 2) Once I have this query, how can I then update a third table based > >> on > >> it? I basically need to run: > >> UPDATE listmaster SET status = "WD" WHERE zipcode = >> results of the query from above>. > >> > >> I think that this would involve another inner join, but at this point > >> I'm _WAY_ over my head. > >> > >> I've included a dump of sample tables and data at the end of this > >> e-mail. > >> > >> For the record: > >> 1) I know that this whole thing would be easier to do programatically > >> (it would take a while, but even I could do it that way). > >> Unfortunately due to complications of the environment over which I > >> have > >> absolutely no c
Re: Advanced Query Help (My brain hurts!)
otherguy wrote: That gets me halfway there Does it? In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
Thank you!
That gets me halfway there, and not to my surprise, it's not even that
hard! I should've known that it wouldn't be.
So the other part that I truly have no idea how to do the following:
UPDATE this_other_table
SET satus =
WHERE zipcode IN ();
can someone provide me with some pointers in the right direction?
(SELECT
quota_zip2.zipcode
FROM
quota_zip2 INNER JOIN quota_control2 ON
(quota_zip2.agent_code = quota_control2.agent_code) AND
(quota_zip2.appl = quota_control2.appl)
WHERE
quota_control2.appl = "CIRG"
GROUP BY quota_zip2.zipcode
HAVING SUM(quota_control2.quota_actual) >=
SUM(quota_control2.quota_limit) )
UNION
(SELECT
quota_zip2.zipcode
FROM
quota_zip2 INNER JOIN quota_control2 ON
(quota_zip2.agent_code = quota_control2.agent_code) AND
(quota_zip2.appl = quota_control2.appl)
WHERE
quota_control2.appl = "CILT"
GROUP BY quota_zip2.zipcode
HAVING SUM(quota_control2.quota_actual) >=
SUM(quota_control2.quota_limit) )
ORDER BY zipcode;
Thanks!
-Cameron Wilhelm
On Friday, June 27, 2003, at 06:42 PM, MyLists wrote:
How about a UNION statement? If the two queries are independently
returning
what you need, then you can just "append" the two results by using
UNION.
Good Luck!
Dennis
- Original Message -
From: "otherguy" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Cc: "Terry Vanstory" <[EMAIL PROTECTED]>
Sent: Friday, June 27, 2003 8:50 PM
Subject: Advanced Query Help (My brain hurts!)
Hey guys, I'm about to dump a doozy on your collective knowledge and
goodwill, and hope for some help or some pointers. I'm not great with
advanced SQL, and I've gotten as far as my brain and the resources
I've
been using will allow me to get for the time being.
I need help with two things:
1) How can I effectively combine the following two queries? The only
difference between them is the `appl` condition. I effectively need
to
do an INNER JOIN on the zip code of the results of both queries so
that
only zip codes that exist in both result sets are returned:
SELECT
quota_zip2.zipcode,
SUM(quota_control2.quota_actual) AS sum_actual,
SUM(quota_control2.quota_limit) AS sum_limit
FROM
quota_zip2 INNER JOIN quota_control2 ON
(quota_zip2.agent_code = quota_control2.agent_code) AND
(quota_zip2.appl = quota_control2.appl)
WHERE
quota_control2.appl = "CIRG"
GROUP BY quota_zip2.zipcode
HAVING sum_actual >= sum_limit
ORDER BY quota_zip2.zipcode;
SELECT
quota_zip2.zipcode,
SUM(quota_control2.quota_actual) AS sum_actual,
SUM(quota_control2.quota_limit) AS sum_limit
FROM
quota_zip2 INNER JOIN quota_control2 ON
(quota_zip2.agent_code = quota_control2.agent_code) AND
(quota_zip2.appl = quota_control2.appl)
WHERE
quota_control2.appl = "CILT"
GROUP BY quota_zip2.zipcode
HAVING sum_actual >= sum_limit
ORDER BY quota_zip2.zipcode;
I cannot change the where clause to
WHERE `appl` = "CIRG" OR `appl` = "CILT"
because there might be data that would result in a situation where the
the sum_actual would meet or exceed the sum_limit for a zip code
(using
both `appl`'s in the where), whereas running them separately would
result in the sum_actual not being met for one of the `appl`'s for
that
zip_code (it would have been exceeded for the other `appl`).
2) Once I have this query, how can I then update a third table based
on
it? I basically need to run:
UPDATE listmaster SET status = "WD" WHERE zipcode =
results of the query from above>.
I think that this would involve another inner join, but at this point
I'm _WAY_ over my head.
I've included a dump of sample tables and data at the end of this
e-mail.
For the record:
1) I know that this whole thing would be easier to do programatically
(it would take a while, but even I could do it that way).
Unfortunately due to complications of the environment over which I
have
absolutely no control, that's not an option.
2) This database is not of my design.
3) This will eventually go into a nightly maintenance job, so query
execution time is not a big issue.
Finally, in advance, I really appreciate any time and effort any of
you
are willing to put in. Hopefully there's someone out there that
enjoys
figuring stuff like this out who has more expertise than I do. Any
assistance rendered will result in many thanks from me.
Thanks again,
-Cameron Wilhelm
-=-=-=-=-=-=-=BEGIN DUMP OF SAMPLE DATA=-=-=-=-=-=-=-=-=-
# Tables dumped 2003-06-27 19:08:11 -0600
# Created by CocoaMySQL (Copyright (c) 2002-2003 Lorenz Textor)
#
# Host: localhost Database: nbl_test
# **
# Dump of table listmaster
# --
CREATE TABLE `listmaster` (
`id` int(11) NOT NULL auto_increment,
`zipcode` char(5) default NULL,
`status` char(2) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM;
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES
("1","1","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES
("2","1","NC");
INSERT I
Re: max_connections being ignored/overridden?
At 18:15 -0400 6/28/03, Gary Huntress wrote: Yes, I am referring to MAX_CONNECTIONS_PER_HOUR, poorly worded on my part sorry. Users are granted access to only one database (barring errors in my scripts) and I did verify that his entry in mysql.user is 200 for max_connections. And the connections in the logfile are for the same user each time. .and they continue.about 10 per second :( Okay, well, this is weird then. You say other accounts get rejected when they hit the limit. Do you have other such accounts that are set up exactly the same way as the problematic user? What version of MySQL are you using? Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Gary Huntress" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 5:45 PM Subject: Re: max_connections being ignored/overridden? At 17:26 -0400 6/28/03, Gary Huntress wrote: >I have some simple user quotas set to help balance my server load. I think >I've implemented them properly because people do whine to me when they >reach the query limit. > >I have max_connections set to 200, but right now I'm watching connections >for 1 user scroll by at an alarming rate. 7800 in less than 5 minutes. > >What could I have done wrong for this user such that his max_connections >value of 200 is being ignored? Maybe he's connecting as an account other than what you think, and that account has no limit? By the way, max_connections is a server variable, not a user variable. I assume you're really talking about MAX_CONNECTIONS_PER_HOUR, as used > in the GRANT statement? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_connections being ignored/overridden?
Yes, I am referring to MAX_CONNECTIONS_PER_HOUR, poorly worded on my part sorry. Users are granted access to only one database (barring errors in my scripts) and I did verify that his entry in mysql.user is 200 for max_connections. And the connections in the logfile are for the same user each time. .and they continue.about 10 per second :( Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Gary Huntress" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 5:45 PM Subject: Re: max_connections being ignored/overridden? > At 17:26 -0400 6/28/03, Gary Huntress wrote: > >I have some simple user quotas set to help balance my server load. I think > >I've implemented them properly because people do whine to me when they > >reach the query limit. > > > >I have max_connections set to 200, but right now I'm watching connections > >for 1 user scroll by at an alarming rate. 7800 in less than 5 minutes. > > > >What could I have done wrong for this user such that his max_connections > >value of 200 is being ignored? > > Maybe he's connecting as an account other than what you think, and that > account has no limit? > > By the way, max_connections is a server variable, not a user variable. > I assume you're really talking about MAX_CONNECTIONS_PER_HOUR, as used > in the GRANT statement? > > > > > > > > >Regards, > >Gary "SuperID" Huntress > >=== > >FreeSQL.org offering free database hosting to developers > >Visit http://www.freesql.org > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: left join on more than one field
That works perfectly. Thank you. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 2:42 PM To: Eric Kilgore; [EMAIL PROTECTED] Subject: Re: left join on more than one field At 12:29 -0700 6/28/03, Eric Kilgore wrote: >Anyone know how to perform a left join by more than one field? > >I have several tables I'm working with. An example query is like this: > >SELECT tbl1.Name, tbl1.Location, tbl1.Case, tbl1.Class, tbl1.Type, >tbl3.Identity, tbl2.FileDate, tbl2.Status >FROM tbl2, tbl1 LEFT JOIN tbl3 ON tbl1.Case = tbl3.Case >WHERE tbl1.Case = tbl2.Case AND tbl1.Name LIKE '$Name%' > >This returns everything I need, except that it also returns rows in tbl3 >that I don't want (there can be multiple rows for the same Case). So I need >to limit by tbl1.Party = tbl3.Party as well. > >If I add this to my where clause I defeat the purpose of the left join for >the null entries in table 3 and I don't know how to create two left join >statements for the same table. I know this is possible with many different >tables but found no info on two fields from the same two tables. Add the condition to your ON clause: ON tbl1.Case = tel3.Case AND tbl1.Party = tbl3.Party > >Any help would be appreciated. > >Eric -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Table Issues
Very good question, I believe I do since it does work 90% of the time. Only occasionally it doesnt work, and thats why I need help - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Phil Dowson" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 4:50 pm Subject: Re: Temporary Table Issues > At 16:47 -0500 6/28/03, Phil Dowson wrote: > >Hi, > > > >I am experiencing a problem creating temporary tables within a MySQL > >database. When I run the following query: > > > >CREATE TEMPORARY TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY > >rmmtid; > >drop table rmb > > > >I get the error > > > >[server] ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database > >'database' > > Do you have the CREATE TEMPORARY TABLES privilege? > > > > >Now this should indicate a username/password/permissions problem, but when I > >use the query: > > > >CREATE TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY rmmtid; > > > >I get no error. > > > >Here is the part where I am losing my mind. I had the same issue about 3 or > >4 days ago, I checked with my service provider and they said the host was > >working fine. And it still didn't work. Just as I was composing an email to > >this list it started working again, like magic. Well now its happened again > >and I'm at a loss as to the reason why it doesn't work. > > > >I am trying to use the database either from the localhost or from a remote > >IP and have the same problem. I have plenty of disk space allocated to me. > > > >The version of MySQL is 4.013, it is running on a Linux OS Kernel version > >2.4.20-18.7, I am pretty sure its Redhat 7.2, I can find out if its > >relevant. > > > >Hope someone can help. > > > >Phil Dowson > >=== > >[EMAIL PROTECTED] > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Table Issues
At 16:47 -0500 6/28/03, Phil Dowson wrote: Hi, I am experiencing a problem creating temporary tables within a MySQL database. When I run the following query: CREATE TEMPORARY TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY rmmtid; drop table rmb I get the error [server] ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'database' Do you have the CREATE TEMPORARY TABLES privilege? Now this should indicate a username/password/permissions problem, but when I use the query: CREATE TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY rmmtid; I get no error. Here is the part where I am losing my mind. I had the same issue about 3 or 4 days ago, I checked with my service provider and they said the host was working fine. And it still didn't work. Just as I was composing an email to this list it started working again, like magic. Well now its happened again and I'm at a loss as to the reason why it doesn't work. I am trying to use the database either from the localhost or from a remote IP and have the same problem. I have plenty of disk space allocated to me. The version of MySQL is 4.013, it is running on a Linux OS Kernel version 2.4.20-18.7, I am pretty sure its Redhat 7.2, I can find out if its relevant. Hope someone can help. Phil Dowson === [EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Table Issues
Hi, I am experiencing a problem creating temporary tables within a MySQL database. When I run the following query: CREATE TEMPORARY TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY rmmtid; drop table rmb I get the error [server] ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'database' Now this should indicate a username/password/permissions problem, but when I use the query: CREATE TABLE IF NOT EXISTS rmb AS SELECT * FROM rmmt GROUP BY rmmtid; I get no error. Here is the part where I am losing my mind. I had the same issue about 3 or 4 days ago, I checked with my service provider and they said the host was working fine. And it still didn't work. Just as I was composing an email to this list it started working again, like magic. Well now its happened again and I'm at a loss as to the reason why it doesn't work. I am trying to use the database either from the localhost or from a remote IP and have the same problem. I have plenty of disk space allocated to me. The version of MySQL is 4.013, it is running on a Linux OS Kernel version 2.4.20-18.7, I am pretty sure its Redhat 7.2, I can find out if its relevant. Hope someone can help. Phil Dowson === [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_connections being ignored/overridden?
At 17:26 -0400 6/28/03, Gary Huntress wrote: I have some simple user quotas set to help balance my server load. I think I've implemented them properly because people do whine to me when they reach the query limit. I have max_connections set to 200, but right now I'm watching connections for 1 user scroll by at an alarming rate. 7800 in less than 5 minutes. What could I have done wrong for this user such that his max_connections value of 200 is being ignored? Maybe he's connecting as an account other than what you think, and that account has no limit? By the way, max_connections is a server variable, not a user variable. I assume you're really talking about MAX_CONNECTIONS_PER_HOUR, as used in the GRANT statement? Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join on more than one field
At 12:29 -0700 6/28/03, Eric Kilgore wrote: Anyone know how to perform a left join by more than one field? I have several tables I'm working with. An example query is like this: SELECT tbl1.Name, tbl1.Location, tbl1.Case, tbl1.Class, tbl1.Type, tbl3.Identity, tbl2.FileDate, tbl2.Status FROM tbl2, tbl1 LEFT JOIN tbl3 ON tbl1.Case = tbl3.Case WHERE tbl1.Case = tbl2.Case AND tbl1.Name LIKE '$Name%' This returns everything I need, except that it also returns rows in tbl3 that I don't want (there can be multiple rows for the same Case). So I need to limit by tbl1.Party = tbl3.Party as well. If I add this to my where clause I defeat the purpose of the left join for the null entries in table 3 and I don't know how to create two left join statements for the same table. I know this is possible with many different tables but found no info on two fields from the same two tables. Add the condition to your ON clause: ON tbl1.Case = tel3.Case AND tbl1.Party = tbl3.Party Any help would be appreciated. Eric -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_connections being ignored/overridden?
I have some simple user quotas set to help balance my server load. I think I've implemented them properly because people do whine to me when they reach the query limit. I have max_connections set to 200, but right now I'm watching connections for 1 user scroll by at an alarming rate. 7800 in less than 5 minutes. What could I have done wrong for this user such that his max_connections value of 200 is being ignored? Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/INNODB speed on large databases
OK, the picture is now a little bit simpler: 1. Your performance problem is coming mainy from one BIG database. 2. Although your servers have a lot of RAM (at least 4Gb), InnoDB can only use a small fraction because of addressing limitations of 32 bits CPU. Some random ideas: 1. Swith to a 64 bits system so that you can efficiently use more RAM 2. Do all tables in this BIG database need to be transaction safe ? If not, you can convert some (all ?) tables to MyISAM format. In this case, file caching will be done by the kernel outside of mysqld process allowing more efficient use of RAM. 3. Sponsor Innobase so that InnoDB use AWE memory on Linux/x86 (up to 64Gb). This has already been discussed on this mailing list: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&th=cddc8bdca9e4304a&seekm=bbkmji%2417qt%241%40FreeBSD.csie.NCTU.edu.tw 4. Make sure that you are really limited by RAM and disc I/O not CPU otherwise you won't gain anything with more RAM. 5. If you have more selects than updates/inserts you should consider replication (I use it, it rocks !) 6. If you have more selects than updates/inserts and identical selects are run several times, you could consider upgrading to MySQL 4.x. Its query cache may speed up your server (I have not tried it myself but there are been positive reports on this list). regards, Joseph Bueno Wendell Dingus wrote: Ahh, there's something I could have explained better. Each of the databases represents a remote office of a different customer. I specifically want/need to keep the data separate, it doubles as an offsite backup for these offices as well as would conflict if it were merged. I currently use replication to get the data from those offices up to my web server but not exactly in the way it was intended. I just turn on binary logging and a process every 30 minutes or so checks the latest /var/lib/mysql/systemname-bin.nnn to see if it contains anything and starts up a new binary log file, uses mysqlbinlog to grab the contents of that one, sends them to the web server, which integrates them with it's offiste copy. Works great since some offices have live internet connections and others are dialup, etc... Now then, I could divvy things up across multiple servers except that that one largest database is almost as slow at the customers office with live local (non-web) usage and that server has over 5GB of RAM. Similar specs to the web server I described otherwise and it only has that ONE database and not the ones of all the other customers. Anyway, beyond that, the LVS approach would still involve having 1 master write server and all the backend "real" servers being readonly (to the application) and kept updated via replication slaving from the master. Just across multiple actual databases and not one... From what I've read so far that is! Thanks! -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 2:45 PM To: Wendell Dingus Cc: [EMAIL PROTECTED] Subject: Re: MySQL/INNODB speed on large databases Maybe my question is too obvious but, since you have several databases instead of only one, have you considered moving some of them to a separate server ? Also, have you checked MySQL replication capabilities ? It is a very efficient way to distribute the load across several servers unless you do a lot of insert/updates and very few selects. Regards, Joseph Bueno Wendell Dingus wrote: Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
freebsd 5.1 + mysql 4.0.13
hi! i'm using mysql 4.0.13 from the standard freebsd 5.1 ports distribution, and i keep getting repeated random table corruptions. the same thing happens when building both with the native threading lib, and with linuxthreads. i figured it would be related to threading as it's always happening when the mysql server is loaded, that is has lots of concurrent queries on the same table going on. suddenly the table would get corrupted, and i have to run a "repair table" on it (this always nukes a few rows from the table). it's reproducable only in the sense that it keeps happening randomly ;) are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? tia richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/INNODB speed on large databases
Ahh, there's something I could have explained better. Each of the databases represents a remote office of a different customer. I specifically want/need to keep the data separate, it doubles as an offsite backup for these offices as well as would conflict if it were merged. I currently use replication to get the data from those offices up to my web server but not exactly in the way it was intended. I just turn on binary logging and a process every 30 minutes or so checks the latest /var/lib/mysql/systemname-bin.nnn to see if it contains anything and starts up a new binary log file, uses mysqlbinlog to grab the contents of that one, sends them to the web server, which integrates them with it's offiste copy. Works great since some offices have live internet connections and others are dialup, etc... Now then, I could divvy things up across multiple servers except that that one largest database is almost as slow at the customers office with live local (non-web) usage and that server has over 5GB of RAM. Similar specs to the web server I described otherwise and it only has that ONE database and not the ones of all the other customers. Anyway, beyond that, the LVS approach would still involve having 1 master write server and all the backend "real" servers being readonly (to the application) and kept updated via replication slaving from the master. Just across multiple actual databases and not one... From what I've read so far that is! Thanks! -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 2:45 PM To: Wendell Dingus Cc: [EMAIL PROTECTED] Subject: Re: MySQL/INNODB speed on large databases Maybe my question is too obvious but, since you have several databases instead of only one, have you considered moving some of them to a separate server ? Also, have you checked MySQL replication capabilities ? It is a very efficient way to distribute the load across several servers unless you do a lot of insert/updates and very few selects. Regards, Joseph Bueno Wendell Dingus wrote: > Hello, I've got a situation I'm hoping someone here can help me out with. We > have a web server serving up data via some PHP programs pulling from MySQL > (3.23.56 currently) in INNODB tables. There are 40+ (active) databases > totalling a bit over 28 Gigabytes at this point. As we add more users speed > is beginning to drop... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT weirdness
Dan Bowkley wrote: Hey there everyone, I'm trying to get a table of computer parts manufacturers populated with a list culled from my distributor and I'm having a devil of a time getting anything to work. I'm totally new to MySQL and don't really know my SELECT from a hole in the ground just yet...which is probably the entire problem. ;) Anyways. I'm trying to do this INSERT INTO `manufacturers` (`manufacturers_name`, `manufacturers_image`) VALUES (`CREATIVE_LABS`, `manufacturer_CREATIVE_LABS.gif`); to this table CREATE TABLE `manufacturers` ( `manufacturers_id` int(11) NOT NULL auto_increment, `manufacturers_name` varchar(32) NOT NULL default '', `manufacturers_image` varchar(64) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, PRIMARY KEY (`manufacturers_id`), KEY `IDX_MANUFACTURERS_NAME` (`manufacturers_name`) ) TYPE=MyISAM AUTO_INCREMENT=11 ; and keep getting this: Error SQL-query : INSERT INTO `manufacturers` ( `manufacturers_name` , `manufacturers_image` ) VALUES ( `CREATIVE_LABS` , `manufacturer_CREATIVE_LABS.gif` ) MySQL said: Unknown column 'CREATIVE_LABS' in 'field list'I know 'CREATIVE_LABS' isn't a colunm, and I didn't tell anyone that it was either. Why is this thing convinced that it is? help??? Back quotes are used for table and field names. Use single quotes for values: INSERT INTO `manufacturers` ( `manufacturers_name` , `manufacturers_image` ) VALUES ( 'CREATIVE_LABS' , 'manufacturer_CREATIVE_LABS.gif' ) Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT weirdness
Hey there everyone, I'm trying to get a table of computer parts manufacturers populated with a list culled from my distributor and I'm having a devil of a time getting anything to work. I'm totally new to MySQL and don't really know my SELECT from a hole in the ground just yet...which is probably the entire problem. ;) Anyways. I'm trying to do this INSERT INTO `manufacturers` (`manufacturers_name`, `manufacturers_image`) VALUES (`CREATIVE_LABS`, `manufacturer_CREATIVE_LABS.gif`); to this table CREATE TABLE `manufacturers` ( `manufacturers_id` int(11) NOT NULL auto_increment, `manufacturers_name` varchar(32) NOT NULL default '', `manufacturers_image` varchar(64) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, PRIMARY KEY (`manufacturers_id`), KEY `IDX_MANUFACTURERS_NAME` (`manufacturers_name`) ) TYPE=MyISAM AUTO_INCREMENT=11 ; and keep getting this: Error SQL-query : INSERT INTO `manufacturers` ( `manufacturers_name` , `manufacturers_image` ) VALUES ( `CREATIVE_LABS` , `manufacturer_CREATIVE_LABS.gif` ) MySQL said: Unknown column 'CREATIVE_LABS' in 'field list'I know 'CREATIVE_LABS' isn't a colunm, and I didn't tell anyone that it was either. Why is this thing convinced that it is? help???
Re: Insert statement with an ' in it
El Sáb 28 Jun 2003 12:24, Zachary Perschall escribió: > Help! I'm trying to do an insert statement where one of the fields > sometimes contains an apostrophe. The field type is a varchar. Everytime > that one of these values comes up with an apostrophe, it tells me there is > an error in my SQL statement (obviously because it thinks there are more > information than there are fields I'm inserting into) > > I'm sure this is something simple, but I can't seem to find the answer on > the web. Please help! > > -Zach If you are programming in C, then you can use mysql_real_escape_string(). See the manual. Regards. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join on more than one field
Anyone know how to perform a left join by more than one field? I have several tables I'm working with. An example query is like this: SELECT tbl1.Name, tbl1.Location, tbl1.Case, tbl1.Class, tbl1.Type, tbl3.Identity, tbl2.FileDate, tbl2.Status FROM tbl2, tbl1 LEFT JOIN tbl3 ON tbl1.Case = tbl3.Case WHERE tbl1.Case = tbl2.Case AND tbl1.Name LIKE '$Name%' This returns everything I need, except that it also returns rows in tbl3 that I don't want (there can be multiple rows for the same Case). So I need to limit by tbl1.Party = tbl3.Party as well. If I add this to my where clause I defeat the purpose of the left join for the null entries in table 3 and I don't know how to create two left join statements for the same table. I know this is possible with many different tables but found no info on two fields from the same two tables. Any help would be appreciated. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and delete
At 0:42 +0530 6/29/03, harsh wrote: > >thanks for help..:) It sounds as though you really want something more like this: DELETE FROM data WHERE name='harsh'; That will only result in deletion,I want it to be displayed and simultaneously deleted.more precisely the effect of following two commands in one SELECT from data where name='harsh'; DELETE from data where name='harsh'; You can't do it. You'll need to execute both statements. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and delete
> >thanks for help..:) > > It sounds as though you really want something more like this: > > DELETE FROM data WHERE name='harsh'; > That will only result in deletion,I want it to be displayed and simultaneously deleted.more precisely the effect of following two commands in one SELECT from data where name='harsh'; DELETE from data where name='harsh'; > > > >-- > >harsh > >http://www.cse.iitb.ac.in/~harsh > >-- > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Persistent annoying slave binlog corruption...
Recently, and pretty consistently our slave's relay logs have been getting garbage that is not in the master. The symptom is usually a truncated query with a few characters of garbage. The solution for now is to change master to to the errored master bin log and position and have it start replication from the spot where it is. When this happens the IO and SQL thread are found choked, with no error on show slave status, but the MySQL .err log shows the error. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database dump (remotely, How?)
On Sat, Jun 28, 2003 at 11:35:54AM -0600, Jerry M. Howell II wrote: > On Sat, Jun 28, 2003 at 06:14:07PM +1000, electroteque wrote: > > lol of course > > > > mysqldump -u username -p databasename > databasename.sql > > > > I am atempting to backup a database remotely. I added something like the > following. > > mysql> GRANT ALL PRIVILEGES ON db.* > -> TO david@'192.58.197.0/255.255.255.0'; > > And have access to the database from my home computer now but when I > use mysqldump -h hostname -A -p > mysql.sql it dumps only a part of > the backup and closes. Any clues as to what is going on or what I > need to do? I could always have it backup on the server and scp it > to my computer but hardheaded me would rather do it simply by > dumping it from the remote computer to my home puter via mysqldump > :). Thanks for any help. > Nevermind, I found the issue. Forgot to grant myself permisions to access, insert modify, etc the tables. -- Jerry M. Howell II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement with an ' in it
Well, the easiest thing is to "escape" any single quotes with a backslash character (\). PHP includes a function named addslashes() that does this. http://us4.php.net/manual/en/function.addslashes.php Same logic applies in other languages. Hope this helps. Andrew > Help! I'm trying to do an insert statement where one of the fields > sometimes contains an apostrophe. The field type is a varchar. Everytime > that one of these values comes up with an apostrophe, it tells me there > is an error in my SQL statement (obviously because it thinks there are > more information than there are fields I'm inserting into) > > I'm sure this is something simple, but I can't seem to find the answer > on the web. Please help! > > -Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/INNODB speed on large databases
Maybe my question is too obvious but, since you have several databases instead of only one, have you considered moving some of them to a separate server ? Also, have you checked MySQL replication capabilities ? It is a very efficient way to distribute the load across several servers unless you do a lot of insert/updates and very few selects. Regards, Joseph Bueno Wendell Dingus wrote: Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of RAM. 4 x 15K RPM drives RAID5. It's hitting load averages >12 during peak times and database query speeds are really starting to drag... I've talked some with the consulting folks at MySQL and have the intention of contracting them to help analyze our database layout, structure, and queries to help optimize things. Dealing with belt-tightening management on the other end though I want to get rolled out some new software and hardware before doing that. I've been spending time working with a test setup using the load-balancer routines at www.linuxvirtualserver.com and like what I see so far. My quandry is this though.. The existing server has 4GB but I can't get innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that route if it could be utilized, I've not figured out how though and assume that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a 64-bit address space sounds good. I'm sure I can get the LVS approach with a load balancer and 3 or 4 back-end "real" servers going without too much trouble. Where do I spend the money most wisely though? More and more and more RAM in each backend server or is anything over 2GB going to mostly not be used (Probably P4 2.4Ghz XEON boxes). Drive speed? I'm thinking I'll test out using fast SCSI drives and maybe some SATA drives RAID-0 stiped versus non-striped to see the speed difference. My whole goal is speed, speed, and more speed out of MySQL! The actual web applications place a very small load on the server, viewing top or mytop show that database access is where all the time is going and primarily on the 2 largest databases. A query against one of the other databases when the server is mainly idle happen in very few seconds or tenths. The same query (data layout is basically identical across all the databases) takes > 2 minutes sometimes on either of the largest 2 databases. So you see my concern? A 2 minute query on an _unloaded_ server is not going to improve no matter how many servers I put behind a load balancer. Again I'll pay the MySQL folks to analyze and hopefully come up with lots of braindead things we're doing and help performance a lot. I have the bosses ear for hardware _now_ though and want to move forward before that opportunity passes. I'll post the database specs at the end of this. If you were hosting that much data without a ton of inserts all day long but lots of selects where would you concentrate your efforts? Speed of CPU or speed/quantity of RAM or speed of drives or what? Would you load balance a quantity of servers or lean more towards a couple of much larger 64-bit servers? Running entirely out of RAM is very appealing but somehow I suspect that will be cost prohibitive :) Just buy reasonable hardware, load-balance it, and then get the MySQL folks to help optimize things? I sure don't want to spend a ton and then be told later that it would have been much better had I went with a different setup if you know what I mean :)Thanks in advance! If any additional statistics would help let me know! PS. Any pointers to any approaches to what I think I read about Google, _striping_ database data across multiple servers for speed? Also, I'm unfamiliar with them, but do PostgreSQL or Oracle or DB2 or something like that have anything that would specifically work better in this scenario? Mytop output after about 28 hours of uptime so this is just Friday during business hours access primarily: MySQL on localhost (3.23.56-Max-log) up 1+04:56:30 [13:29:15] Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 Threads Total: 33Active: 1 Cached: 0 Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 The databases themselves from phpMyAdmin: (This one is the main killer: 40 1.8 GB 5.5 GB 7.3 GB ) Database Table(s)DataIndexes Total xxx00 Bytes 0 Bytes 0 Bytes xx 44 88.4 KB 182.0 KB270.4 KB x 21 14.8 MB 10.0 MB 24.8 MB mysql 61.5 KB
Re: Insert statement with an ' in it
why don't you just insert your values after you escaped some special characters? specifically, you have to replace all ' by \' (prepend a single backslash character), and everything works fine! i guess you do your INSERTs from out of some programming language, like PHP, Perl or C. just use the appropriate str_replace() functions of that language to replace those apostrophes. -yves -Ursprüngliche Nachricht- Von: "Zachary Perschall" <[EMAIL PROTECTED]> An: <[EMAIL PROTECTED]> Gesendet: Samstag, 28. Juni 2003 20:24 Betreff: Insert statement with an ' in it Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement with an ' in it
Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach
MySQL/INNODB speed on large databases
Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of RAM. 4 x 15K RPM drives RAID5. It's hitting load averages >12 during peak times and database query speeds are really starting to drag... I've talked some with the consulting folks at MySQL and have the intention of contracting them to help analyze our database layout, structure, and queries to help optimize things. Dealing with belt-tightening management on the other end though I want to get rolled out some new software and hardware before doing that. I've been spending time working with a test setup using the load-balancer routines at www.linuxvirtualserver.com and like what I see so far. My quandry is this though.. The existing server has 4GB but I can't get innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that route if it could be utilized, I've not figured out how though and assume that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a 64-bit address space sounds good. I'm sure I can get the LVS approach with a load balancer and 3 or 4 back-end "real" servers going without too much trouble. Where do I spend the money most wisely though? More and more and more RAM in each backend server or is anything over 2GB going to mostly not be used (Probably P4 2.4Ghz XEON boxes). Drive speed? I'm thinking I'll test out using fast SCSI drives and maybe some SATA drives RAID-0 stiped versus non-striped to see the speed difference. My whole goal is speed, speed, and more speed out of MySQL! The actual web applications place a very small load on the server, viewing top or mytop show that database access is where all the time is going and primarily on the 2 largest databases. A query against one of the other databases when the server is mainly idle happen in very few seconds or tenths. The same query (data layout is basically identical across all the databases) takes > 2 minutes sometimes on either of the largest 2 databases. So you see my concern? A 2 minute query on an _unloaded_ server is not going to improve no matter how many servers I put behind a load balancer. Again I'll pay the MySQL folks to analyze and hopefully come up with lots of braindead things we're doing and help performance a lot. I have the bosses ear for hardware _now_ though and want to move forward before that opportunity passes. I'll post the database specs at the end of this. If you were hosting that much data without a ton of inserts all day long but lots of selects where would you concentrate your efforts? Speed of CPU or speed/quantity of RAM or speed of drives or what? Would you load balance a quantity of servers or lean more towards a couple of much larger 64-bit servers? Running entirely out of RAM is very appealing but somehow I suspect that will be cost prohibitive :) Just buy reasonable hardware, load-balance it, and then get the MySQL folks to help optimize things? I sure don't want to spend a ton and then be told later that it would have been much better had I went with a different setup if you know what I mean :)Thanks in advance! If any additional statistics would help let me know! PS. Any pointers to any approaches to what I think I read about Google, _striping_ database data across multiple servers for speed? Also, I'm unfamiliar with them, but do PostgreSQL or Oracle or DB2 or something like that have anything that would specifically work better in this scenario? Mytop output after about 28 hours of uptime so this is just Friday during business hours access primarily: MySQL on localhost (3.23.56-Max-log) up 1+04:56:30 [13:29:15] Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 Threads Total: 33Active: 1 Cached: 0 Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 The databases themselves from phpMyAdmin: (This one is the main killer: 40 1.8 GB 5.5 GB 7.3 GB ) Database Table(s)DataIndexes Total xxx00 Bytes 0 Bytes 0 Bytes xx 44 88.4 KB 182.0 KB270.4 KB x 21 14.8 MB 10.0 MB 24.8 MB mysql 61.5 KB 9.0 KB 10.5 KB xxx17 1.3 GB 2.6 GB 3.9 GB test 00 Bytes 0 Bytes 0 Bytes x 9205.8 MB92.1 MB 297.8 MB xx 16 465.4 KB184.0 KB649.4 KB xxx63 441.7
Re: mysql database dump (remotely, How?)
On Sat, Jun 28, 2003 at 06:14:07PM +1000, electroteque wrote: > lol of course > > mysqldump -u username -p databasename > databasename.sql > I am atempting to backup a database remotely. I added something like the following. mysql> GRANT ALL PRIVILEGES ON db.* -> TO david@'192.58.197.0/255.255.255.0'; And have access to the database from my home computer now but when I use mysqldump -h hostname -A -p > mysql.sql it dumps only a part of the backup and closes. Any clues as to what is going on or what I need to do? I could always have it backup on the server and scp it to my computer but hardheaded me would rather do it simply by dumping it from the remote computer to my home puter via mysqldump :). Thanks for any help. -- Jerry M. Howell II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems setting up Priveleges
At 12:58 -0400 6/28/03, J.A. Jones Tyler III wrote:
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
Where are these instructions found?
Got em here :
http://www.analysisandsolutions.com/code/mybasic.htm#installation
Yes, unless some other user table matches first, which may be
what is happening in your case.
Cool. Ok, that's new info for me. What is the table order that MySQL uses to
grant authentication? On that same note, if I understand your response, does
it stop the minute it finds a suitable entry (I guess it matches the user
name) or does it grab info from all tables for that username? If the latter,
does it grant the privileges inclusively (i.e. does it get everything that
they all grant individually (so you get different rights from different
tables) or does it only grant what all of the tables allow (only those
privileges that are granted on each table are granted)?
The server reads the grant tables into memory and sorts the entries into
a particular order. The sorting order is described here:
http://www.mysql.com/doc/en/Connection_access.html
When a client attempts to connect, the server starts looking through the
entries. When it finds one that matches both the client's host and the
username given by the client, it checks the password supplied by the
client. If the password matches, the connection is accepted. Otherwise
it's rejected.
This means that you might have an entry for 'NewRootUser'@'%', but if
another entry in the table matches earlier (for example, an anonymous
user entry of ''@'localhost' for a connection from localhost) but doesn't
have the right password, the connection will be rejected.
Stopping and starting the service should be unnecessary.
The FLUSH PRIVILEGES statement you're using causes the
server to reload the grant tables.
Excellent. Thanks for the info!
>
>Here are some of the things which I do to supposedly
>secure the MySQL install :
>
>delete from user where User='';
>update user set User='NewRootName',
>Password=password('NewPassword') where User='root';
>update user set host='%' where user='NewRootName';
>flush privileges;
What is the output of the following statement after you issue the
preceding statements:
SELECT Host, User, Password FROM user;
I get two entries, both for the same username (I've actually already
uninstalled MySQL for the umpteenth time, but I did what you asked before to
be sure it was there). The first instance of the username is matched to
'localhost' and the second instance is matched to '%'. In both cases the
password is the same (in this case it was 'lwtest').
Thanks a TON for your assistance...I've spent quite a bit of time working on
this daggone thing (as a side, I am 100% sure that I am doing something
wrong, I just can't figure out what the heck it is!)
JT
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems setting up Priveleges
- Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> > > > >I have gone into the user table in mysql db and placed > >both 'localhost' and '%' into the host entry. It was > >my understanding that '%' would allow access from any > >host for that particular username. > > Yes, unless some other user table matches first, which may be > what is happening in your case. > These comments you made have gotten me to thinking. When I was having problems, per the original query, I actually had two incidents of the same username in my user table. They were the only users in fact. Each was tied to a different domain. I continued to get errors. Perhaps it was grabbing the first entry (which was simply defined as 'localhost' for the host) and not going further? The reason I ask is that I had no problems when I put in a NEW username (and also tied it directly to the local host ip instead of the dns/host name.) I at first thought it was because I tied it directly to the ip, but it was also the only incident of that new username, so it could not get busted out like my original username. None of the above probably makes much senseand I probably am not using the right lingo...so, I apologize for that. But, I am learning slowly=) JT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems setting up Priveleges
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
> Where are these instructions found?
Got em here :
http://www.analysisandsolutions.com/code/mybasic.htm#installation
>
> Yes, unless some other user table matches first, which may be
> what is happening in your case.
Cool. Ok, that's new info for me. What is the table order that MySQL uses to
grant authentication? On that same note, if I understand your response, does
it stop the minute it finds a suitable entry (I guess it matches the user
name) or does it grab info from all tables for that username? If the latter,
does it grant the privileges inclusively (i.e. does it get everything that
they all grant individually (so you get different rights from different
tables) or does it only grant what all of the tables allow (only those
privileges that are granted on each table are granted)?
>
> Stopping and starting the service should be unnecessary.
> The FLUSH PRIVILEGES statement you're using causes the
> server to reload the grant tables.
Excellent. Thanks for the info!
> >
> >Here are some of the things which I do to supposedly
> >secure the MySQL install :
> >
> >delete from user where User='';
> >update user set User='NewRootName',
> >Password=password('NewPassword') where User='root';
> >update user set host='%' where user='NewRootName';
> >flush privileges;
>
> What is the output of the following statement after you issue the
> preceding statements:
>
> SELECT Host, User, Password FROM user;
I get two entries, both for the same username (I've actually already
uninstalled MySQL for the umpteenth time, but I did what you asked before to
be sure it was there). The first instance of the username is matched to
'localhost' and the second instance is matched to '%'. In both cases the
password is the same (in this case it was 'lwtest').
Thanks a TON for your assistance...I've spent quite a bit of time working on
this daggone thing (as a side, I am 100% sure that I am doing something
wrong, I just can't figure out what the heck it is!)
JT
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems setting up Priveleges
- Original Message - From: "J.A. Jones Tyler III" <[EMAIL PROTECTED]> > > I have gone into the user table in mysql db and placed > both 'localhost' and '%' into the host entry. It was > my understanding that '%' would allow access from any > host for that particular username. > Well, I finally specifically granted all priv's to username@"127.0.0.1' and was able to get it to work. Can someone give me the syntax for giving a user the right to access a dB or all dB's from any domain? I must be inputing it wrong. To recap, I was writing this : grant all privileges to dbname.* to username@"%" identified by 'password'; It took it and when I looked into the table, it showed it in there. But, whenever I tried to use php to connect, I'd get the error about access denied to [EMAIL PROTECTED] Only when I specifically entered the local host defaul ip as a host that username could connect from did it work. Thanks again for any help! jt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems setting up Priveleges
At 12:20 -0400 6/28/03, J.A. Jones Tyler III wrote:
Greetings,
I have installed MySQL 4.0.13 in a Windows 2000
machine. I am new to this and originally did the
install on a box that was secure from the internet and
ran some tests. I ran a forum software program and
also created a few databases on my own, all
successfully (although some troubleshooting was
generally required here and there=)
Now, I am preparing to move this to a production
server and am running into problems. I followed the
instructions on 'securing' the database and now
continually have problems with connecting to the
database.
Where are these instructions found?
In general, it does not seem to like
[EMAIL PROTECTED]
I have gone into the user table in mysql db and placed
both 'localhost' and '%' into the host entry. It was
my understanding that '%' would allow access from any
host for that particular username.
Yes, unless some other user table matches first, which may be
what is happening in your case.
Comically enough, most of the time that I go in and do
this editing, I subsequently get out of MySQL, stop
and restart the service and then, of course, it will
not even let me log in.
Stopping and starting the service should be unnecessary.
The FLUSH PRIVILEGES statement you're using causes the
server to reload the grant tables.
The error always revolves around the 127.0.0.1.
Here are some of the things which I do to supposedly
secure the MySQL install :
delete from user where User='';
update user set User='NewRootName',
Password=password('NewPassword') where User='root';
update user set host='%' where user='NewRootName';
flush privileges;
What is the output of the following statement after you issue the
preceding statements:
SELECT Host, User, Password FROM user;
(omit the Password output if you like, but it may be helpful here)
I have searched the internet for an answer to my problem and seen
where users are pointed to http://www.mysql.com/doc/en/Access_denied.html
But, quite frankly, these instructions must
not be for a Windows OS and simply do not work (or
they assume that the reader has other knowledge that I
do not have.)
Cannot comment on that without reading the original instructions.
Please provide a reference to where they can be found.
Any assistance on this issue will be much appreciated.
Thanks!
JT
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and delete
At 21:40 +0530 6/28/03, harsh wrote: Hi, I need to perform a query which deletes the selected results from the table, like SELECT * from data where name='harsh' (here i want something to delete it as well) if its possible with single query... thanks for help..:) It sounds as though you really want something more like this: DELETE FROM data WHERE name='harsh'; -- harsh http://www.cse.iitb.ac.in/~harsh -- -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table creation problems
At 18:11 +0200 6/28/03, anders thoresson wrote:
Hi,
I've three tables that my local MySQL server accepts, but when I try
to create the same three tables on my ISP's server, it doesn't work.
These are the three tables:
CREATE TABLE un_user
(
u_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
u_uname VARCHAR(20) NOT NULL UNIQUE,
u_pwd CHAR(20) NOT NULL,
u_fname VARCHAR(20) NOT NULL,
u_lname VARCHAR(35) NOT NULL,
u_email VARCHAR(50) NOT NULL,
u_phone VARCHAR(25) NOT NULL,
u_cellphone VARCHAR(25) NOT NULL,
u_editor ENUM("Y","N") DEFAULT "N" NOT NULL,
u_admin ENUM("Y","N") DEFAULT "N" NOT NULL
);
CREATE TABLE un_section
(
s_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
s_name VARCHAR(40) NOT NULL UNIQUE,
s_desc TEXT NOT NULL
);
CREATE TABLE un_issue
(
i_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
i_date DATE NOT NULL UNIQUE,
i_number INT NOT NULL,
i_editor INT,
UNIQUE (i_date, i_number)
);
For local development I'm using MySQL 3.23.55-NT on a Windows 2000
server. My ISP uses 3.22.25 on a SunOS 5.7 server.
Any suggestions?
UNIQUE is not allowed at the end of a column definition prior to MySQL 3.23.
You should declare the UNIQUE indexes in a separate clause. For example,
in the un_user table, change this:
u_uname VARCHAR(20) NOT NULL UNIQUE,
To this:
u_uname VARCHAR(20) NOT NULL,
UNIQUE (u_uname),
You'll need to make similar adjustments for the s_name and i_date
columns.
--
anders thoresson
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Now I have solved my problem!
At 17:56 +0900 6/28/03, Itoh Kohji wrote: Now I have solved the problem I asked you to help me! The problem was when to invoke mysql_install_db. I invoked it before l let the daemon start, following the instruction of a textbook on MySQL. I have found in a magazine that I shoulld have run the script after letting the mysql daemon start! That magazine is incorrect. mysql_install_db starts the server itself, feeds it some queries, and then shuts down the server. I don't recall your original message, but mysql_install_db is run before you start the server for the first time. Itoh Kohji -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems setting up Priveleges
Greetings,
I have installed MySQL 4.0.13 in a Windows 2000
machine. I am new to this and originally did the
install on a box that was secure from the internet and
ran some tests. I ran a forum software program and
also created a few databases on my own, all
successfully (although some troubleshooting was
generally required here and there=)
Now, I am preparing to move this to a production
server and am running into problems. I followed the
instructions on 'securing' the database and now
continually have problems with connecting to the
database.
In general, it does not seem to like
[EMAIL PROTECTED]
I have gone into the user table in mysql db and placed
both 'localhost' and '%' into the host entry. It was
my understanding that '%' would allow access from any
host for that particular username.
Comically enough, most of the time that I go in and do
this editing, I subsequently get out of MySQL, stop
and restart the service and then, of course, it will
not even let me log in.
The error always revolves around the 127.0.0.1.
Here are some of the things which I do to supposedly
secure the MySQL install :
delete from user where User='';
update user set User='NewRootName',
Password=password('NewPassword') where User='root';
update user set host='%' where user='NewRootName';
flush privileges;
I have searched the internet for an answer to my problem and seen
where users are pointed to http://www.mysql.com/doc/en/Access_denied.html
But, quite frankly, these instructions must
not be for a Windows OS and simply do not work (or
they assume that the reader has other knowledge that I
do not have.)
Any assistance on this issue will be much appreciated.
Thanks!
JT
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database dump
At 1:08 -0400 6/28/03, Asif Iqbal wrote: Can I dump a database while the database is running in mysql ? Yes. You can, for example, use the mysqldump program. mysqldump is a MySQL client program that, like all MySQL clients, requires the server to be running. -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08 There's no place like 127.0.0.1 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select and delete
Hi, I need to perform a query which deletes the selected results from the table, like SELECT * from data where name='harsh' (here i want something to delete it as well) if its possible with single query... thanks for help..:) -- harsh http://www.cse.iitb.ac.in/~harsh -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table creation problems
Hi,
I've three tables that my local MySQL server accepts, but when I try to
create the same three tables on my ISP's server, it doesn't work.
These are the three tables:
CREATE TABLE un_user
(
u_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
u_uname VARCHAR(20) NOT NULL UNIQUE,
u_pwd CHAR(20) NOT NULL,
u_fname VARCHAR(20) NOT NULL,
u_lname VARCHAR(35) NOT NULL,
u_email VARCHAR(50) NOT NULL,
u_phone VARCHAR(25) NOT NULL,
u_cellphone VARCHAR(25) NOT NULL,
u_editor ENUM("Y","N") DEFAULT "N" NOT NULL,
u_admin ENUM("Y","N") DEFAULT "N" NOT NULL
);
CREATE TABLE un_section
(
s_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
s_name VARCHAR(40) NOT NULL UNIQUE,
s_desc TEXT NOT NULL
);
CREATE TABLE un_issue
(
i_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
i_date DATE NOT NULL UNIQUE,
i_number INT NOT NULL,
i_editor INT,
UNIQUE (i_date, i_number)
);
For local development I'm using MySQL 3.23.55-NT on a Windows 2000 server.
My ISP uses 3.22.25 on a SunOS 5.7 server.
Any suggestions?
--
anders thoresson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table query oscommerce
Has any of the MySQL gurus here made any amendments to Oscommerce's DB structure or added any tailored queries? I have custom made 26 shipping modules for certain shipping specifications and now I have another one to cater for! product category type. The problem with this is that the product category (cat_id) is in a table not called during the checking_out process so it looks like I need to do a table join to pull associated cat_id wiht the product id, sounds like fun! If any of you out there have done such a thing with oscom and know any pitfall it would be rather useful :) Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
Well, the only thing that matters is that you are returning the same number of columns with, presumably, the same data types in both queries. Obviously, UNION wouldn't be helpful if you had 2 columns in the first query and 8 columns in the second. I'll leave it to you to find the documentation on UNION - it's not that difficult :-). Basically it's something like SELECT A, B, C FROM FOO UNION SELECT X, Y, Z FROM FOO Good Luck! Dennis - Original Message - From: "electroteque" <[EMAIL PROTECTED]> To: "MyLists" <[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]>; "otherguy" <[EMAIL PROTECTED]> Cc: "Terry Vanstory" <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 12:05 AM Subject: RE: Advanced Query Help (My brain hurts!) > would u have an example of how to use union then ? like on the second select > i need to reutn a heap of rows from a field from the first select > > -Original Message- > From: MyLists [mailto:[EMAIL PROTECTED] > Sent: Saturday, June 28, 2003 10:42 AM > To: MySQL List; otherguy > Cc: Terry Vanstory > Subject: Re: Advanced Query Help (My brain hurts!) > > > How about a UNION statement? If the two queries are independently returning > what you need, then you can just "append" the two results by using UNION. > > Good Luck! > > Dennis > > - Original Message - > From: "otherguy" <[EMAIL PROTECTED]> > To: "MySQL List" <[EMAIL PROTECTED]> > Cc: "Terry Vanstory" <[EMAIL PROTECTED]> > Sent: Friday, June 27, 2003 8:50 PM > Subject: Advanced Query Help (My brain hurts!) > > > > Hey guys, I'm about to dump a doozy on your collective knowledge and > > goodwill, and hope for some help or some pointers. I'm not great with > > advanced SQL, and I've gotten as far as my brain and the resources I've > > been using will allow me to get for the time being. > > > > I need help with two things: > > 1) How can I effectively combine the following two queries? The only > > difference between them is the `appl` condition. I effectively need to > > do an INNER JOIN on the zip code of the results of both queries so that > > only zip codes that exist in both result sets are returned: > > > > SELECT > > quota_zip2.zipcode, > > SUM(quota_control2.quota_actual) AS sum_actual, > > SUM(quota_control2.quota_limit) AS sum_limit > > FROM > > quota_zip2 INNER JOIN quota_control2 ON > >(quota_zip2.agent_code = quota_control2.agent_code) AND > >(quota_zip2.appl = quota_control2.appl) > > WHERE > > quota_control2.appl = "CIRG" > > GROUP BY quota_zip2.zipcode > > HAVING sum_actual >= sum_limit > > ORDER BY quota_zip2.zipcode; > > > > SELECT > > quota_zip2.zipcode, > > SUM(quota_control2.quota_actual) AS sum_actual, > > SUM(quota_control2.quota_limit) AS sum_limit > > FROM > > quota_zip2 INNER JOIN quota_control2 ON > >(quota_zip2.agent_code = quota_control2.agent_code) AND > >(quota_zip2.appl = quota_control2.appl) > > WHERE > > quota_control2.appl = "CILT" > > GROUP BY quota_zip2.zipcode > > HAVING sum_actual >= sum_limit > > ORDER BY quota_zip2.zipcode; > > > > I cannot change the where clause to > > WHERE `appl` = "CIRG" OR `appl` = "CILT" > > because there might be data that would result in a situation where the > > the sum_actual would meet or exceed the sum_limit for a zip code (using > > both `appl`'s in the where), whereas running them separately would > > result in the sum_actual not being met for one of the `appl`'s for that > > zip_code (it would have been exceeded for the other `appl`). > > > > 2) Once I have this query, how can I then update a third table based on > > it? I basically need to run: > > UPDATE listmaster SET status = "WD" WHERE zipcode = > results of the query from above>. > > > > I think that this would involve another inner join, but at this point > > I'm _WAY_ over my head. > > > > I've included a dump of sample tables and data at the end of this > > e-mail. > > > > For the record: > > 1) I know that this whole thing would be easier to do programatically > > (it would take a while, but even I could do it that way). > > Unfortunately due to complications of the environment over which I have > > absolutely no control, that's not an option. > > > > 2) This database is not of my design. > > > > 3) This will eventually go into a nightly maintenance job, so query > > execution time is not a big issue. > > > > Finally, in advance, I really appreciate any time and effort any of you > > are willing to put in. Hopefully there's someone out there that enjoys > > figuring stuff like this out who has more expertise than I do. Any > > assistance rendered will result in many thanks from me. > > > > Thanks again, > > -Cameron Wilhelm > > > > -=-=-=-=-=-=-=BEGIN DUMP OF SAMPLE DATA=-=-=-=-=-=-=-=-=- > > > > # Tables dumped 2003-06-27 19:08:11 -0600 > > # Created by CocoaMySQL (Copyright (c) 2002-2003 Lorenz Textor) > > # > > # Host: localhost Database: nbl_test > > # ** > > > > # Dump of table listmaster > > # -
Lookup used Version of MySql && MySql Foreign Keys and Referencial Integrity
Hello, I use mySQL on HP/UX 11.i - unfortunateley I am unable to lookup the used version. What is the command to do that? (according to mysql.info it is Version 3.23.42. ) Are foreign keys and referencial integrity supported by MySQL Version 3.23.42. ? If yes - why is the second "insert into" command allowed? (see below) -- Thanks in Advance and Best regards, Vince mailto:[EMAIL PROTECTED] drop table FremdeNLTB; drop table FNL_ErkennungTB; create table FremdeNLTB ( FN_ID int unsigned not null, Topic varchar(255), Name varchar(255), KatIK int unsigned, Primary Key (FN_ID), Index (FN_ID) ) Type=InnoDB ; create table FNL_ErkennungTB ( FNLE_ID int unsigned not null, FN_ID int unsigned not null, receivedFrom varchar(255), originFrom varchar(255), receivedBy varchar (255), originSentTo varchar (255), Primary Key (FNLE_ID), Index (FN_ID), Foreign Key (FN_ID) References FremdeNLTB(FN_ID) On Delete Cascade On Update Cascade ) Type=InnoDB ; insert into FremdeNLTB values (11,'lost+found','lost+found',999), (21,'MS/Windows','lost+found',999), (22,'MS/Windows','Cluster',999), (23,'MS/Windows','Plattform-Migration',999), (31,'MPE','lost+found',999), (41,'LINUX','lost+found',999), (51,'OpenView','lost+found',999), (52,'OpenView','SAM',999), (53,'OpenView','Omniback2',999), (61,'Storage/SAN','lost+found',999), (62,'Storage/SAN','XP',999), (63,'Storage/SAN','EMC',999), (64,'Storage/SAN','VA',999), (71,'Applications','lost+found',999), (72,'Applications','SAP',999), (73,'Applications','ORACLE',999) ; insert into FNL_ErkennungTB values (1,11,'','','','') ; insert into FNL_ErkennungTB values (123,123,'this','should','not be','possible!!!') ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't compile on NetBSD
please don't top-post.
# [EMAIL PROTECTED] / 2003-06-27 14:01:58 -0400:
> On Fri, 27 Jun 2003, Roman Neuhauser wrote:
> > # [EMAIL PROTECTED] / 2003-06-20 11:07:09 -0400:
> > > I am trying to compile MySQL 3.23.57 on NetBSD 1.6.1. I downloadded
> > > pthreads-1_60_beta6-mysql.tar.gz from your site and extracted it to
> > > mysql-3.23.57/ I ran ./configure --with-mit-threads in mysql-3.23.57 and
> > > everything went fine. However, when I run make, I get the following
> > > errors after a bunch of output:
> > > Making all in mit-pthreads
> > > make: don't know how to make all. Stop
> > you'll probably make it easier for yourself (at least in the long
> > run), and help the community, if you update the NetBSD package. The
> > fix might be as easy as modifying the value of ${DISTNAME} in
> > /usr/pkgsrc/databases/mysql-client/Makefile.common, but that depends
> > on how much is the original MySQL source patched in pkgsrc, and how
> > much it changed between 3.23.49 and 3.23.57.
> I can change DISTNAME in Makfile.common, but then I might not be able to
> uninstall it. I am relatively a novice, and I don't want to mess anything
> up.
Is the box just your playground, or is this part of your job? In the
first case: get ready to screw the system in more than a few ways;
reinstalling is quite easy. :) I'm serious: you can't realistically
expect to sustain the first install.
If the latter is true: you seriously need a spare box to play with
freely in order to learn the system.
> In order to use MySQL, will I need to download both the client and the
> server? I don't have a lot of space on my HDD, and I only want to
> download what is needed.
you don't download anything, the pkgsrc system should do all that
for you (if I got it right from the pkgsrc documentation... which
you have already read, haven't you?)
BTW, this is completely OT for this list, and you should really ask
these questions on the netbsd-users mailing list. You can subscribe
to it here:
http://www.netbsd.org/MailingLists/#descriptions
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Now I have solved my problem!
Itoh- Is the MySQL doc incorrect? Please advise. Martin - Original Message - From: "Itoh Kohji" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 4:56 AM Subject: Now I have solved my problem! > > Now I have solved the problem I asked you to help me! > > The problem was when to invoke mysql_install_db. > > I invoked it before l let the daemon start, following the > instruction of a textbook on MySQL. > > I have found in a magazine that I shoulld have run the > script after letting the mysql daemon start! > > Itoh Kohji > > > > -- > 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: update and order by in 3.23.51
Bob Ramsey <[EMAIL PROTECTED]> wrote: > > I'm using 3.23.51 and I'd like to update a field in a certain order. I've > got a table of images for a slide show and the format of the table is: > > image varchar(128) not null > order_number integer not null primary key > > So we might have something like: > > duck.jpg 1 > cat.jpg 2 > horse.jpg 3 > > I want the user to be able to insert a new picture anywhere and > automatically up date the order numbers of the other items. Apparently > this is easy in version 4, because you can just do: > > update slideshow set order_number=order_number +1 where order_number >1 > order by order_number desc > > Because you have to change 3->4, 2->3 so that you can insert a new number 2. > > Is there a good workaround for this in version 3? Select ordered data to the temporary table and then increase order_number. You can also drop primary key, update order_number and add primary key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More (different) problems with mysql.sock
Xavier Fern?ndez i Mar?n <[EMAIL PROTECTED]> wrote: > I am able to start the mysql daemon with: > # [usr/local/mysql/bin] safe_mysqld & > > Then, on /var/lib/mysql appears the 'mysql.sock'. > > But the problem is that I want the server running since I start my computer, > and not having to re-start every time. > > And when I start my computer, there is no 'mysql.sock' on /var/lib/mysql > neither on /tmp (I created a link). > > ?Is it because of my 'my.cnf' file?: > No, because mysqld is not running. > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > basedir=/var/lib/mysql > > > > I'm working with Mysql 4.0.12 on a Linux Mandrake 9.0. If you want the MySQL serveron starts on system bootup, check the following section of the manual: http://www.mysql.com/doc/en/Automatic_start.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: distributed database architecture for a large database
6/27/03 6:37:49 PM, Jeremy Zawodny <[EMAIL PROTECTED]> wrote: >> My read/update ratio would be something along the lines of 1:3, 3 >> updates for every read. So it is highly unusual, and more or less >> rules replication out of the picture. > >I'm unclear why you can't use replication for this. There must be an >assumption about what you're doing that we do not share. > >If you read from the slave and write to the master, why does this not >work? A slave would simply not be able to keep up, replication works really well if you have a lot of reads, and a small number of updates. This is reverse ways, and needs a different approach. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More (different) problems with mysql.sock
Hello, I am able to start the mysql daemon with: # [usr/local/mysql/bin] safe_mysqld & Then, on /var/lib/mysql appears the 'mysql.sock'. But the problem is that I want the server running since I start my computer, and not having to re-start every time. And when I start my computer, there is no 'mysql.sock' on /var/lib/mysql neither on /tmp (I created a link). ¿Is it because of my 'my.cnf' file?: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock basedir=/var/lib/mysql I'm working with Mysql 4.0.12 on a Linux Mandrake 9.0. Thank you, -- Xavier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Now I have solved my problem!
Now I have solved the problem I asked you to help me! The problem was when to invoke mysql_install_db. I invoked it before l let the daemon start, following the instruction of a textbook on MySQL. I have found in a magazine that I shoulld have run the script after letting the mysql daemon start! Itoh Kohji -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database dump
lol of course mysqldump -u username -p databasename > databasename.sql -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 3:08 PM To: [EMAIL PROTECTED] Subject: mysql database dump Can I dump a database while the database is running in mysql ? -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08 There's no place like 127.0.0.1 -- 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: How can a single row with a single column represent 2 values without bitwise data representation
> So, my question is how can multiple values for a single > field get represented in a single row and receive the > benefits of an index? How can this data be represented? Nice puzzle. At first I thought it couldn't be done, given all your constraints. But there is one way. You will have to build your own index. For each row, you will need to build a separate index entry for each possible subset of that row's views. If a row subscribed to views 5 and 2, for example, you would need three entries: 10010 = 18 1 = 16 00010 = 2 If someone subscribed to all 16 views, you would need -- let me see -- 65,536 index entries. That solves your problem as you've presented it to us. (Do I get a prize?) P.S. There might be a better way to solve the actual problem. Perhaps you should rethink your constraints. Maybe allow us more than one column? ...more than one table? ...more than one index? Cheers, John For example, in your example row On Friday 27 June 2003 05:50 pm, Dathan Vance Pattishall wrote: > Say you have a column in a search table by the name of > politics. > > Each political view is represented as an integer from 0 - > 16. A user can choose multiple political views, and those > views have to be represented in the db as a single row. > How can a single column for a single row of a searched > table represent multiple values while using an index? > > Current I convert the integer value 0-16 into a bitwise > representation. > > So if a user picks values 2,5 the integer entered into > the db, for the users row for column politics as > > 18 (this is the result of the bitwise manipulation) > > Thus when searching for a users politics and you want > users who have political views of 2,5,7, or 9 show up in > your search a query needs to be done in this manner using > the approach above. > > SELECT * from searched_table where politics & 338; > > What is the problem here? > An index is not available when using bitwise arithmetic > on tables so a resulting table scan occurs. > > So, my question is how can multiple values for a single > field get represented in a single row and receive the > benefits of an index? How can this data be represented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
