return integer for positive values
I have a table with a list of pollutants. I have a table of locations, site names, counties, etc. I can join the these tables together and get a list of of all the pollutants at a site. But, what I am really wanting is a list of all the pollutants with a integer field, zero for pollutant not here, 1 for pollutant here. So that instead of the list I get now: benzaldehyde freon formaldehyde I would get: lead 0 acetone 0 benzaldehyde 1 butane 0 freon 1 formaldehyde 1 How can I return an integer for positive values like this in a query? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: return integer for positive values
I am at a higher level than that. Here is my query: SELECT distinct emt.group_name FROM epa.aq_ambient_nad83 aq_, epa.aq_ambient_pollutants pol, epa.emtb_pollutant_group emt, epa.pollutant pt WHERE (aq_.siteid = pol.siteid) AND (emt.group_code = pt.ambient_group_code) AND (pol.paramcode = pt.paramcode) AND AQ_.SITEID = cfqueryparam cfsqltype=CF_SQL_INTEGER value=#FORM.siteID# This gives me the list of pollutants at one site. If I remove the And aq.siteid part, then I get a list of all the pollutants. --ja On Thu, 5 Apr 2007, Jerry Schwartz wrote: I'm not sure I quite follow. Is there a concentration for each location for each of a master list of pollutants? What concentration would be considered not there? In any case, look at a construct like (ABS(concentration) = tolerance), where tolerance can be either a constant or a field associated with a particular pollutant. That construct will return TRUE or FALSE, which evaluate to 1 and 0, respectively. I hope that helps. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 05, 2007 2:47 PM To: mysql@lists.mysql.com Subject: return integer for positive values I have a table with a list of pollutants. I have a table of locations, site names, counties, etc. I can join the these tables together and get a list of of all the pollutants at a site. But, what I am really wanting is a list of all the pollutants with a integer field, zero for pollutant not here, 1 for pollutant here. So that instead of the list I get now: benzaldehyde freon formaldehyde I would get: lead 0 acetone 0 benzaldehyde 1 butane 0 freon 1 formaldehyde 1 How can I return an integer for positive values like this in a query? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Read_only Mode
Only allow your users select rights. --ja On Wed, 28 Jun 2006, Clyde Lewis wrote: All, Does MySQL have an option where the database can startup in READ-ONLY mode? The idea is to have the server running with users connected, but now allowing any updates to me applied to the database. I've looked through the documentation, but was not able to find such a feature. If someone can point me in the right direction, it would be greatly appreciated. Thanks in advance, *** Clyde Lewis Database Administrator -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysqlclient_r.so not being created
Any idea why libmysqlclient_r.so is not being created when I compile after a configure --without-server? I did two other boxes this morning and everything went just fine. Now this one (RH 9) is not getting it's libraries created. The compile/install looks like it works just fine. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start server after power outage
That happens sometimes. Do a mysqladmin shutdown and then try to restart it. It should start just fine. --ja On Wed, 8 Feb 2006, Michael Satterwhite wrote: I use a local MySQL server (we're using 4.0) for development. It's been running cleanly for months - and the configuration file has not changed. This morning we had a major power problem causing the machine to come down in an unorderly manner. I can no longer start MySQL, it stops as soon as it starts. The log messages I'm seeing are: Feb 8 11:34:34 photon mysqld_safe[9615]: started Feb 8 11:34:34 photon mysqld[9619]: 060208 11:34:34 Can't start server: Bind on TCP/IP port: Cannot assign requested address Feb 8 11:34:34 photon mysqld[9619]: 060208 11:34:34 Do you already have another mysqld server running onport: 3306 ? Feb 8 11:34:34 photon mysqld[9619]: 060208 11:34:34 Aborting Feb 8 11:34:34 photon mysqld[9619]: Feb 8 11:34:34 photon mysqld[9619]: 060208 11:34:34 /usr/sbin/mysqld: Shutdown Complete Feb 8 11:34:34 photon mysqld[9619]: Feb 8 11:34:34 photon mysqld_safe[9621]: ended There shouldn't be anything running on port 3306 (there wasn't before the power outage). My guess is that there is something from the previous run of mysql that's in an indeterminant state - maybe a file that needs to be cleared or deleted. Unfortunately, I have absolutely no idea where to start on finding it. Would someone be so kind as to help? All help will be greatly appreciated. ---Michael -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum of time?
On Mon, 24 Oct 2005 [EMAIL PROTECTED] wrote: Martijn Tonies [EMAIL PROTECTED] wrote on 10/24/2005 10:16:21 AM: Michael Stassen wrote: snip Second, no, it won't overflow: mysql SELECT SEC_TO_TIME(60*60*24*5); +-+ | SEC_TO_TIME(60*60*24*5) | +-+ | 120:00:00 | +-+ 1 row in set (0.00 sec) mysql SELECT SEC_TO_TIME(60*60*24*50); +--+ | SEC_TO_TIME(60*60*24*50) | +--+ | 1200:00:00 | +--+ 1 row in set (0.00 sec) SEC_TO_TIME() is not limited to 24 hours. I should have added that the limits of a TIME column are documented in the manual http://dev.mysql.com/doc/refman/4.1/en/time.html: TIME values may range from '-838:59:59' to '838:59:59'. The reason for which the hours part may be so large is that the TIME type may be used not only to represent a time of day (which must be less than 24 hours), but elapsed time or a time interval between two events as well. (Note that this interval may be much greater than 24 hours, or even negative.) That's actually a very weird definition for a TIME datatype :-) It should have an interval datatype for such operations. So some care may be needed if you will be storing the result, because SEC_TO_TIME() can return a time outside of a TIME column's allowable range. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com The interval datatype? I don't see interval as an option for MySQL. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html. Yes, interval fields are defined as part of SQL2003 but MySQL doesn't have them (yet) which is probably why the TIME datatype has such a wide range. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hey I have another question. If I was running MySQL 5, would this be a great thing to create as a view? That way I could just send the userID as a select for the hours and get them back? Or would this be a waste as it is easy to get with a query anyway? If so, when should I use a view? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum of time?
I have a table doing time tracking. I don't use timestamps, I use datetime fields to record punch in times and punch out times. I have this query that computes the amount of time between a punch in and punch out: select SEC_to_time(unix_timestamp(TTendTime) - unix_timestamp(TTstartTime)) as endtime FROM TimeTracking WHERE TTperson = 1 and date(TTstartTime) = '2005-10-19' And this works great except for when people punch in and out several times in one day. Is there any way I can total a number of records into one total time? In this example case, I am TTperson #1 and I punched in and out five times this day. I know I can do it in the code, but if I can do it in sql, life would be better for me. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query
I have a table of votes with four fields, a primary key, and userID, that are just there for tracking purposes. But then I have questionID and vote fields. For each question, a user could pick a number of it's importance from 1 to 5. That number is what gets stored in the vote field. So, now I want to tabulate the votes. I thought I could just do a count with a group by but that isn't working. What I want to do is for each question, get a count of how many ones, how many twos, how many threes, etc. Advice? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication general question
I have two mysql boxes setup. Fast machines, I think they are dual 3ghz with boat loads of ram. They are not real busy servers but they have some pretty good sized tables, one of them with a few million rows. My question is, I have Nagios setup to monitor the seconds behind master on the backup server. Usually the boxes are pretty current, within ten or twenty seconds. Other times though they seem to get way behind, like I just bumped the nagios warning email level up to 600 seconds. It doesn't seem to have anything to do really with usage because it usually happens in the middle of the night. Does Mysql do re-indexing or something? What could I do to figure out why the replicatant box is getting so far behind? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication general question
I don't think network latency would be an issue. This is within a protected
network dmz so it has it's own switch.
Here is the nagios script stuff. Might be more than what you need but let me
know if you are able to use some of it.
I have two on the server. first, I have:
[EMAIL PROTECTED] jabbott]# more secondsBehind.sh
#!/bin/sh
mysql -pMYSECRET -e show slave status\G | grep Seconds
Then I have this that I run in the rc.local. This sets up a port that listens
for a connection on port 5151. I have hole open in my firewall into my dmz for
port 5151
[EMAIL PROTECTED] jabbott]# more socket.pl
#!/usr/bin/perl
use IO::Socket;
$server_port = 5151;
$server = IO::Socket::INET-new(LocalPort = $server_port,
Type = SOCK_STREAM,
Reuse = 1,
Listen= 10)
or die Could not be a tcp server on port $server_port : [EMAIL PROTECTED];
while ($client = $server-accept ()) {
my $sysArg = `/home/jabbott/secondsBehind.sh`;
# uncomment the next line for debugging
print $client is the new connection\n\n;
print $client $sysArg\n;
print connect \n;
close ($client);
}
close ($server);
Then, on the Nagios side I have this:
$ cat /usr/lib/nagios/plugins/mysql-replication-lag.pl
#!/usr/bin/perl -w
use strict;
use lib nagios/plugins ;
use utils qw($TIMEOUT %ERRORS);
use IO::Socket;
$ENV{'PATH'}='';
$ENV{'BASH_ENV'}='';
$ENV{'ENV'}='';
my ($ip_address,$port,$warn,$critical) = @ARGV;
# Just in case of problems, let's not hang Nagios
$SIG{'ALRM'} = sub {
print No Answer from Client\n;
exit $ERRORS{UNKNOWN};
};
alarm($TIMEOUT);
my $sock = new IO::Socket::INET(
PeerAddr = $ip_address,
PeerPort = $port,
Proto= 'tcp',
);
unless ($sock) {
print Socket could not be created. Reason: $!\n;
exit $ERRORS{'UNKNOWN'};
}
my $result = $sock || Could not read socket\n;
close($sock);
alarm(0);
print $result;
unless ($result =~ /^\s*Seconds_Behind_Master:\s*/i) {
exit $ERRORS{'UNKNOWN'};
}
$result =~ s/\D//g;
exit $ERRORS{'CRITICAL'} if ($result$critical);
exit $ERRORS{'WARNING'} if ($result$warn);
exit $ERRORS{'OK'}
1;
__END__
On Thu, 23 Jun 2005, James Green wrote:
Checked for network latency? I have replication running on similar
hardware hooked up to the same switch, and have never seen it rise above
0 seconds behind.
Not that I check often, I have no need to...
Is your nagios script open for public use - I was about to have to write
something for this task myself.
Thanks,
James
On 23/6/2005, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I have two mysql boxes setup. Fast machines, I think they are dual 3ghz
with boat loads of ram. They are not real busy servers but they have some
pretty good sized tables, one of them with a few million rows.
My question is, I have Nagios setup to monitor the seconds behind master on
the backup server. Usually the boxes are pretty current, within ten or
twenty seconds. Other times though they seem to get way behind, like I just
bumped the nagios warning email level up to 600 seconds. It doesn't seem to
have anything to do really with usage because it usually happens in the
middle of the night. Does Mysql do re-indexing or something? What could I
do to figure out why the replicatant box is getting so far behind?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication stopped
I took a couple of days vacation last week and naturally the /data partition of my database server filled up because of something I was running and had forgotten about. So my backup guy, thinking to help out, gzipped all my .bin logs. Now I am noticing replication seems to have stopped on my other server. Not sure if the two events are related or not. It could be that things got all hosed up not because of the gzipping but because the partition filled up in the first place? Anyway, what do I do now? Do I need to do a new dump and start replication all over again? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help?
I have two tables, cutting out the extra stuff they boil down to: users: userID int, username varchar(11), realname varchar(40) logins: ID int, lastLogin timestamp So, what I am doing is: select user.id, username, realname, lastLogin from users left join logins on users.id = logins.id group by username order by lastLogin DESC What I want is all the users, no matter if they have logged in or not. That is what the left join does. But, if they have logged in, I want the last login date. Right now I get the first login date. Changing DESC to ASC only changes the display order of the return set. I have added DESC and ASC to the group by, but that doesn't work at all. Advice? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: email notification
If we get triggers in mysql 5, couldn't you setup something like this? --ja On Tue, 31 May 2005, Philip Denno wrote: I doubt you would find this feature in any database. You would have to implement at the application code level. Basically whenever you insert into a table have the application framework send an e-mail. The log4j package provides this kind of functionality. See http://logging.apache.org/ And look for information on the SMTP appender. Cheers, Philip. -Original Message- From: Jayson [mailto:[EMAIL PROTECTED] Sent: May 31, 2005 6:59 AM To: mysql@lists.mysql.com Subject: email notification I'm looking for a feature in mysql where it will email me if ther are any changes in a particular database or table. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question
I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
something is pegging mysql
It has been a rough day today. I am using mysql with coldfusion. Something somehwere on my site is causing mysql to take up 100% of the cpu. This causes coldfusion to lock. Is there any sort of query log I can look at to see what queries have run in, say, the last hour? If I see what queries are running I could track down the page and either fix a defective query or change it so I am not getting killed. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, table db already exists
Hello All,
Here is a problem I have been trying to debug for a few days. I had a mysql
server running for about a month. I did a mysql dump of all the tables on the
master and setup a second machine to be the slave, loading all the tables. I
set the replication stuff up in the slave my.cnf. Now as soon as I start the
slave I get good data flow between the two machines but the slave does not
update with the error below. Is this because I did not do a binary snapshot of
the master machine to setup the slave? I originally was not going to setup
replication, just going to do dumps of tables, but then changed directions.
Thanks! If you need more info, please ask.
--ja
mysql show slave status \G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rhea-bin.17
Read_Master_Log_Pos: 405948310
Relay_Log_File: dione-relay-bin.02
Relay_Log_Pos: 121
Relay_Master_Log_File: rhea-bin.01
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1050
Last_Error: Error 'Table 'db' already exists' on query.
Default database: 'mysql'. Query: ' CREATE TABLE db ( Host char(60) binary
DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User
char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, Update_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, Drop_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') DEFAULT 'N'
NOT NULL, References_priv enum('N','Y') DEFAULT 'N' NOT NULL, Index_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host
(Host,Db,User), KEY User (User) ) comment='Database privileges''
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 11454541057
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 12172794
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1043 Bad handshake
An answer to my own question. I have solved this problem which seems to be due
to the way mysql 4.1.7 stores it's passwords. I used the mysql function
OLD_PASSWORD to make mysql store it's passwords in the pre 4.1.x method.
Basically the syntax is:
mysql UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host =
'some_host' AND User = 'some_user';
mysql FLUSH PRIVILEGES;
Now I seem to be able to connect.
--ja
On Wed, 17 Nov 2004, Gleb Paharenko wrote:
Hello.
Did you use mysql client program from 4.1.7 installation? When you use
it from 4.1.0 or older (on another machine) there some differences in
authentication handshake.
[EMAIL PROTECTED] wrote:
I have done some looking in the archive for this but can't seem to find
anything recent that seems to apply.
I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with:
CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro
-felide-constructors -fno-exceptions -fno-rtti ./configure --prefix
/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static
--datadir /web/data
It starts just fine. This is a new install and I haven't put any data in
it so I don't think that I need to do any sort of permission fixing (Isn't
that just for migrated data?) I can connect to it via local host just fine
but if I try to connect from another machine running 4.1.? I get:
bash-2.05$ mysql landfill -u root -p -h 192.168.1.100
Enter password:
ERROR 1043: Bad handshake
bash-2.05$
Any idea what I am doing wrong?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1043 Bad handshake
I have done some looking in the archive for this but can't seem to find anything recent that seems to apply. I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with: CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix /usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --datadir /web/data It starts just fine. This is a new install and I haven't put any data in it so I don't think that I need to do any sort of permission fixing (Isn't that just for migrated data?) I can connect to it via local host just fine but if I try to connect from another machine running 4.1.? I get: bash-2.05$ mysql landfill -u root -p -h 192.168.1.100 Enter password: ERROR 1043: Bad handshake bash-2.05$ Any idea what I am doing wrong? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Images
I have heard also that it helps to keep the table with the blobs having a low number of fields. Like just a primary key and the blob field. Have all your other metadata in a seperate table. Especially if you are going to be occasionally doing queries of just the metadata and don't want to always retrieve the image. Comments? I am doing some blob storage in Oracle with Cold Fusion. Using some purcha$ed java code to get the image in and out. Anyone else doing coldfusion with mysql blobs? How do you retrieve/display the images? --ja On Tue, 28 Sep 2004, Ed Lazor wrote: What do you mean chunked? I figured it would be easier to track about 32,000 images in MySQL than in files, so I setup a test to see what the performance difference is and if storing in MySQL would actually work. Everything is working and it's a lot easier to keep track of the images in MySQL. I ran some performance tests using Apache's ab though and there's a huge performance difference - 38 times faster grabbing the file. It could be my test system here at home. I've asked my ISP to upgrade my production server to PHP5 so that I can run tests from there. There could also be performance hits in the script that grabs the images from MySQL, because I tried using OOP. I was told OOP would be a faster approach, but I'm pretty new to it and may not have done something correctly. I'm going to create some non-OOP scripts to cross-reference the tests. I only have one production server, so I won't be able to separate / dedicate servers. Your mention of chunking sounds promising though. Actually, I'd appreciate any ideas or recommendations you have. Thanks, Ed -Original Message- From: DreamWerx [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 28, 2004 8:54 AM To: GH Cc: Jigal van Hemert; [EMAIL PROTECTED] Subject: Re: Images Separate/dedicated servers for web/database. All the data was chunked to allow faster streaming/lower overhead (large images/files).. If you have more specific questions I can answer them.. On Tue, 28 Sep 2004 11:49:27 -0400, GH [EMAIL PROTECTED] wrote: Is there anything special in your setup that you did to have such good performance? On Tue, 28 Sep 2004 11:47:26 -0400, DreamWerx [EMAIL PROTECTED] wrote: It's an argument that can go on forever... We have 10's of thousands of images in mysql databases.. very fast/reliable.. easy to replicate, stream, etc.. On Tue, 28 Sep 2004 08:42:13 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: From: GH [EMAIL PROTECTED] I was wondering how to get images into and out of a Mysql database was told it was possible. I have mysql 4.0.18 The general opinion is that files should be stored in a file system and not a database. There are circumstances that you might want to store binary data in a database. Take a look at the BLOB column types (http://dev.mysql.com/doc/mysql/en/BLOB.html). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with a select. Regex?
What I am trying to do is select the hostname out of a refering url. Such as:
http://www.google.com/search?hl=enie=UTF-8q=%22Third+Park+Point+Beach+Water+Contact+Advisory+Posted%3B+May+Indicate+Long-Term+Problem%22btnG=Google+Search
All I really want to get is:
http://www.google.com/
So I have:
Select DISTINCT LEFT(referer, LOCATE('/',referer)-1) referer , count(*) as refCount
FROM NNtracking
WHERE referer != ''
GROUP BY referer
Limit 10
but that only selects me:
http:/
I have tried a couple of REGEXP ^/ variations but either I am on the wrong track or I
can't get the syntax right.
Advice?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fresh 4.1.2 install on Redhat 9
Hello List, I have done some googling around but can't find an answer to this one. Brand new box, installed with RedHat 9 and trying to run 4.1.2. This is what I get. --ja [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user mysql --log Installing all prepared tables 040618 11:06:51 Warning: Asked for 196608 thread stack, but got 126976 ./bin/mysqld: File './mySnort.log' not found (Errcode: 13) 040618 11:06:51 Could not use mySnort.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. ./bin/mysqld: File './mySnort-bin.1' not found (Errcode: 13) 040618 11:06:51 Could not use mySnort-bin for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. ERROR: 1049 Unknown database 'mysql' 040618 11:06:51 Aborting 040618 11:06:51 ./bin/mysqld: Shutdown Complete Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! [EMAIL PROTECTED] mysql]# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
You might want to read it again. He is saying the performance and consistancy exists in the database whereas it doesn't in a file system. --ja On Thu, 20 May 2004, David Blomstrom wrote: [EMAIL PROTECTED] wrote: Another perspective on the subject of BLOB vs. Links. Links are easier to implement and may be an OK way to start. However, a file system is really a crude database, and I emphasize crude. It's not very good at handling high transaction rates, access from multiple machines, or volume. If your application grows quickly and before you know it you have hundreds of folders with thousands of files in each - your file system will slow to a crawl. All the performance, security, and consistancy features developers have worked so hard to put into database engines don't or barely exist in file systems. So - if you go the link approach - you'll be fine for a while, but when you see the directory structure starting to buckle - it might be time to give BLOBs another look. I'm confused. It sounds like you're basicallly saying that databases slow down as they grow bigger. That's logical. But then you suggest that, when a database begins to get too big, BLOBs may be better than storing links. I don't understand that. How can storing images as BLOBs be more efficient that creating a field that simply stores links to those images? Or am I missing something? __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
I have an application where I serve out blobs. I store the PDF files in blob fields for two reasons. 1. I want to make an interface where staff can upload pdf's into the server but not actually give them access to the server. I use my code to maintain the security of who gets to write to what. 2. The pdf's are date sensitive press releases and public notices. They must show up on our web site on a certain date and they MUST not be able to be accessed on our site after that time. Having them stored as blobs I use a cfm page to fetch them out of the database and it is easy for me to write the sql to ~WHERE date or date If they were on the file system someone could have bookmarked the location of the pdf and unless I have something written to go in and prune files out, it could still be gotten. --ja On Wed, 19 May 2004, David Blomstrom wrote: --- Greg Willits [EMAIL PROTECTED] wrote: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AND + OR Logic
On Thu, 12 Feb 2004, John Berman wrote:
Try adding some ()s:
SELECT * FROM marriage WHERE (groom_surname like (' globsurname ') or
bride_surname like (' globsurname ')) and community like (' community
')
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sloooow sql query
Got any indexes? --ja On Wed, 11 Feb 2004, nyem wrote: Hello, I have this table which stores an ever changing price of items. CREATE TABLE shop ( articleINT(4) UNSIGNED ZEROFILL NOT NULL default '', date DATE NOT NULL default '-00-00', price DOUBLE(16,2) NOT NULL default '0.00', KEY article (article,date) ); And I used this query to get the percentage change in price of the items: SELECT s1.article, s2.date as prevDate, s2.price as prevPrice, s1.date as lastDate, s1.price as lastPrice, ROUND(((s1.price-s2.price)/s2.price)*100, 1) as percentChange FROM shop s1, shop s2 WHERE s1.date=( SELECT MAX(s3.date) FROM shop s3 WHERE s1.article=s3.article GROUP BY article ) AND s2.date=( SELECT MAX(s4.date) FROM shop s4 WHERE s1.article=s4.article AND s4.dates1.date GROUP BY article ) ORDER BY percentChange DESC; It works fine on small number of rows, but when the table reaches 400 rows the time it took to execute the query was 16 sec. And my cpu shot up to 100% whenever I populate 1000 rows. What have I done wrong here? regards, nyem -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't seem to reset password
I have been reading and following the instructions from: http://www.mysql.com/doc/en/Resetting_permissions.html But, if I do: ./bin/mysqld_safe --skip-grant-tables And then I: mysqladmin -u root password 'mypassword' I get: mysqladmin: unable to change password; error: 'You must have privileges to update tables in the mysql database to be able to change passwords for others' this is a 3.2 install that was working just fine but I just did an upgrade to 4.1. I think I did the ?update permissions? script ok when I did the install. But is this the problem? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dirt Slow Query On Datetime Range
Change your query to use BETWEEN rather that = and =. --ja On Tue, 11 Nov 2003, Michael Shuler wrote: OK, I give up. To anyone out there who can help me, please explain why this query runs slower than dirt. The table has about 1,300,000 records in it, which is not supposed to be a big deal for MySQL to deal with. I have tried it with MyISAM and then changed it to InnoDB which made it even slower but at least the rest of my queries can continue and not be blocked. This query takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC. In my opinion it should be 10x faster than that at the very least. This table is used for RADIUS accounting, all I want to do is find the peak utilization port utilization for the day. The only way I have figured out how to do this is take samples every 5 min and store the highest one. Here is the table: CREATE TABLE `ServiceRADIUSAccounting` ( `RadAcctId` bigint(21) NOT NULL auto_increment, `AcctSessionId` varchar(32) NOT NULL default '', `AcctUniqueId` varchar(32) NOT NULL default '', `UserName` varchar(64) NOT NULL default '', `Realm` varchar(64) default '', `NASIPAddress` varchar(15) NOT NULL default '', `NASPortId` int(12) default NULL, `NASPortType` varchar(32) default NULL, `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00', `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00', `AcctSessionTime` int(12) default NULL, `AcctAuthentic` varchar(32) default NULL, `ConnectInfo_start` varchar(32) default NULL, `ConnectInfo_stop` varchar(32) default NULL, `XmitSpeed` varchar(6) default NULL, `RecvSpeed` varchar(6) default NULL, `AcctInputOctets` int(12) default NULL, `AcctOutputOctets` int(12) default NULL, `CalledStationId` varchar(11) NOT NULL default '', `CallingStationId` varchar(11) NOT NULL default '', `AcctTerminateCause` varchar(32) NOT NULL default '', `ServiceType` varchar(32) default NULL, `FramedProtocol` varchar(32) default NULL, `FramedIPAddress` varchar(15) NOT NULL default '', `AcctStartDelay` int(12) default NULL, `AcctStopDelay` int(12) default NULL, PRIMARY KEY (`RadAcctId`), KEY `UserName` (`UserName`), KEY `FramedIPAddress` (`FramedIPAddress`), KEY `AcctSessionId` (`AcctSessionId`), KEY `AcctUniqueId` (`AcctUniqueId`), KEY `AcctStartTime` (`AcctStartTime`), KEY `AcctStopTime` (`AcctStopTime`), KEY `NASIPAddress` (`NASIPAddress`), KEY `Realm` (`Realm`), KEY `RealmAndStart` (`Realm`,`AcctStartTime`) ) TYPE=InnoDB AUTO_INCREMENT=4468368 ; And here is the query: SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm = 'testreal.com') AND (AcctStartTime = '2003-11-11 15:30:00' AND AcctStopTime = '2003-11-11 15:30:00') When I do an explain I get: tabletype possible_keys key key_len ref rows Extra ServiceRADIUSAccounting ref AcctStartTime,AcctStopTime,Realm,RealmAndStart RealmAndStart 65 const 73394 Using where Perhapse my InnoDB file needs to be optimized if such a thing exists. I don't know why this takes so long but I can definitly use some help. Thanks! Michael Shuler -- 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]
a different compile error
I am having some compile problems too: make[4]: Entering directory `/usr/src/mysql-4.1.0-alpha/sql' source='sql_lex.cc' object='sql_lex.o' libtool=no \ depfile='.deps/sql_lex.Po' tmpdepfile='.deps/sql_lex.TPo' \ depmode=gcc /bin/bash ../depcomp \ gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I../include -I../regex -I. -O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T -c -o sql_lex.o `test -f sql_lex.cc || echo './'`sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:83: `symbols' undeclared (first use this function) sql_lex.cc:83: (Each undeclared identifier is reported only once sql_lex.cc:83: for each function it appears in.) sql_lex.cc:85: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX *, unsigned int, bool)': sql_lex.cc:140: implicit declaration of function `int get_hash_symbol(...)' sql_lex.cc:140: initialization to `SYMBOL *' from `int' lacks a cast make[4]: *** [sql_lex.o] Error 1 make[4]: Leaving directory `/usr/src/mysql-4.1.0-alpha/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/usr/src/mysql-4.1.0-alpha/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/mysql-4.1.0-alpha/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.1.0-alpha' make: *** [all] Error 2 This is mysql 4.1 on solaris with gcc. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X
Download http://cocoamysql.sourceforge.net/ It's really great. Painless install. I spent hours trying to get mysqlcc to install and I was ending up playing the one library short game where it seems like if you install just one more thing it will work, but then it never does. cocoamysql installed fast and easy. It is still missing a few features but I guess they are coming soon. --ja On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote: After a fruitless search for MySqlCC for Mac OS X, I downloaded and built qt and MySqlCC. However, I ran into some errors on the CC build. Am I duplicating effort here? I know that we are a small market but it is a form of Unix and if I could get some assistance I would like to give this a try. Ed -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query time in ~3M row table
I have a question about how long queries should be taking and if my server is too small for what I want to be doing. I have a table setup to record stats from an apache web server. I import the file currently once per month. Here is my table: mysql describe hitStats; +-+--+---+--+-+-++ | Field | Type | Collation | Null | Key | Default | Extra | +-+--+---+--+-+-++ | hostIP | varchar(24) | latin1_swedish_ci | | | | | | apacheDate | datetime | latin1_swedish_ci | | MUL | -00-00 00:00:00 | | | status | int(11) | binary| | | 0 | | | bytes | varchar(20) | latin1_swedish_ci | | | | | | contentType | varchar(40) | latin1_swedish_ci | | | | | | url | varchar(255) | latin1_swedish_ci | | MUL | | | | referer | text | latin1_swedish_ci | | | | | | agent | text | latin1_swedish_ci | | | | | | statID | int(11) | binary| | PRI | NULL| auto_increment | +-+--+---+--+-+-++ As for what it has in it: mysql select count(*) from hitStats; +--+ | count(*) | +--+ | 2749862 | +--+ 1 row in set (0.00 sec) So, about 3 million rows. I have this running on a Sun Blade 150 workstation w/ Solaris 9. I think it has 1G memory. MySql 4.1 is the only thing running on this server. I have another server setup just like this one only with mysql 3.x and the times were similar last month when I was testing it. I have read the http://www.mysql.com/doc/en/Server_parameters.html page and think that I did what it told me to. When I try to do any sort of query the times are really long. Such as: mysql select count(*) from hitstats where year(apacheDate) = 2003 and month(apacheDate) = 9; +--+ | count(*) | +--+ | 988759 | +--+ 1 row in set (25.17 sec) Running top shows: last pid: 22934; load averages: 0.42, 0.13, 0.08 35 processes: 34 sleeping, 1 on cpu CPU states: 12.4% idle, 87.6% user, 0.0% kernel, 0.0% iowait, 0.0% swap Memory: 768M real, 405M free, 433M swap in use, 642M swap free PID USERNAME THR PRI NICE SIZE RES STATETIMECPU COMMAND 22921 mysql 13 590 414M 201M sleep3:39 67.73% mysqld So I guess my question is, do I not have a fast enough server? Is this database too much for the machine I am running it on? Or do I not have it optimized or is something else going wrong? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query time in ~3M row table
Yes, it is an indexed field: mysql explain select count(*) from hitstats where year(apacheDate) = 2003 and - month(apacheDate) = 9; ++-+--+---+---+---+-+--+-+--+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows| Extra| ++-+--+---+---+---+-+--+-+--+ | 1 | SIMPLE | hitstats | index | NULL | dateIndex | 8 | NULL | 2749862 | Using where; Using index | ++-+--+---+---+---+-+--+-+--+ 1 row in set (0.00 sec) Using between is much faster(?!) Still though, the query is slow when I add antoher part in, such as: mysql select count(*) from hitstats where url like '/water/index.html' AND ApacheDate between '2003-09-01' and '2003-10-01'; +--+ | count(*) | +--+ | 2396 | +--+ 1 row in set (14.68 sec) mysql explain select count(*) from hitstats where url like '/water/index.html' AND ApacheDate between '2003-09-01' and '2003-10-01'; ++-+--+---++--+-+--+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---++--+-+--+--+-+ | 1 | SIMPLE | hitstats | range | urlIndex,dateIndex | urlIndex | 255 | NULL | 5368 | Using where | ++-+--+---++--+-+--+--+-+ 1 row in set (0.00 sec) I have two indexes, would it be better if I did the two fields in one index? --ja On Tue, 4 Nov 2003, Brent Baisley wrote: You're searching on a calculation so I'm pretty sure that MySQL is not using an index for the search. You should use explain in front of your query to see if MySQL is using indexes. You do have that date field indexed, don't you? You should search on ApacheDate between 9/1/2003 and 10/1/2003. Something like this: select count(*) from hitstats where apacheData between '2003-09-01' and '2003-10-01'; I think that's right. The 'between' is not inclusive of the second parameter. On Tuesday, November 4, 2003, at 05:09 PM, [EMAIL PROTECTED] wrote: When I try to do any sort of query the times are really long. Such as: mysql select count(*) from hitstats where year(apacheDate) = 2003 and month(apacheDate) = 9; +--+ | count(*) | +--+ | 988759 | +--+ 1 row in set (25.17 sec) -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
count max days (fwd)
I sent this out on last friday but I never actually saw it post to the list so maybe I messed up the address. If this is showing up a second time, sorry. I have a database where I want to count the number of days where a field gets to a certain value. I am using two seperate queries to count the number of days that get to that value. Right now I am using: SELECT count(DISTINCT aqiValues.readingDate) as greenCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = 'Twin Cities' AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 0 and 51 Then I am also using: SELECT count(DISTINCT aqiValues.readingDate) as yellowCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = ? AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 51 and 100 My problem is I only want to count the day high value. So if it is 25 in the morning and 52 in the afternoon I only want to count the 52 (the yellowcount query) to return results. I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP error. I must be putting it in the wrong place. Maybe I need to somehow make this only one query? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: count max days (fwd)
The trouble with this is it returns 1. It seems to pick the max value for the year instead of by day. --ja On Mon, 20 Oct 2003, Franz, Fa. PostDirekt MA wrote: Hi, maybe I didn't understand the question wrong. I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP error. That is what I try to prevent here: SELECT aqiValues.readingDate, MAX(aqiValues.aqiNumber) FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber aqiRegions.showOnSite = 1 GROUP BY aqiValues.readinDate; I know this i'nt the whole query, but it might be a hint. I hope this helps Klaus -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed Font
I dunno, the font seems fairly fixed when I view it in pine --ja On Fri, 17 Oct 2003, Jerry Rocteur wrote: Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Than this ? mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Just a thought. Jerry -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
count max days
I have a database where I want to count the number of days where a field gets to a certain value. Right now I am using: SELECT count(DISTINCT aqiValues.readingDate) as greenCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = 'Twin Cities' AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 0 and 51 Then I am also using: SELECT count(DISTINCT aqiValues.readingDate) as yellowCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = ? AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 51 and 100 My problem is I only want to count the day high value. So if it is 25 in the morning and 52 in the afternoon I only want to the yellowcount query to return results. I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP error. I must be putting it in the wrong place. Maybe I need to somehow make this only one query? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 and sub-seletcs
I am running it on a production server for about two weeks. So far it has been very stable. --ja On Thu, 9 Oct 2003, boka wrote: Bob Brands wrote: No, its supported by version 4.1 MySQL 4.1 -- Alpha release (use this for new development) ... is this stable enough to use it on production systems ? btw. is there any workaround for this functionality in 3.23.58 ? greetz boka -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Injections
As I understand it, and I confess I am not totally sure I do, sql injection is more of a problem in your page code than what database (/version) you run. --ja On Thu, 9 Oct 2003, Dwight Trumbower wrote: Are there any SQL injection threats for MySQL less than 4.1? Dwight Trumbower T Systems Corp Custom Database Solutions [EMAIL PROTECTED] 920-667-4438 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
Subqueries don't show up until v4.1 which I have running on one of my servers. Runs great and I have it into production. --ja On Wed, 8 Oct 2003, Rory McKinley wrote: Problem 1 : Your query contains a subquery : SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) subqueries are (AFAIK) not supported in the latest production version of MySQL (4.0.15) but are coming soon...might already be in a beta ver Two possible solutions - 1) If there are a fixed number of artifacts per product (n): SELECT * FROM artifacts WHERE product_id = 1 ORDER BY plan_submission_number DESC LIMIT 1, n This retrieves the n most recent records for the specific product_id But obviously this only works if n is fixed... 2)Otherwise the only solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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]
compile problem
Hope I am posting to the right list. I am new here. I have a sun system: SunOS spot 5.6 Generic_105181-05 sun4u sparc SUNW,Ultra-4 With gcc installed: # gcc -v Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.7.2.3/specs gcc version 2.7.2.3 I am trying to compile: mysql-4.1.0-alpha and I a getting this error: gcc -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -o isamchk isamchk.o sort.o libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lpthread -lthread -lz -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread source='isamlog.c' object='isamlog.o' libtool=no \ depfile='.deps/isamlog.Po' tmpdepfile='.deps/isamlog.TPo' \ depmode=gcc /bin/ksh ../depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../include -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -c `test -f isamlog.c || echo './'`isamlog.c In file included from /usr/include/sys/wait.h:24, from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.7.2.3/include/stdlib.h:22, from ../include/my_global.h:235, from ../include/my_base.h:26, from ../include/nisam.h:27, from isamdef.h:20, from isamlog.c:23: /usr/include/sys/resource.h:148: warning: `struct rlimit64' declared inside parameter list /usr/include/sys/resource.h:148: warning: its scope is only this definition or declaration, /usr/include/sys/resource.h:148: warning: which is probably not what you want. /usr/include/sys/resource.h:149: warning: `struct rlimit64' declared inside parameter list isamlog.c: In function `set_maximum_open_files': isamlog.c:715: storage size of `rlimit64' isn't known make[2]: *** [isamlog.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.1.0-alpha/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.1.0-alpha' make: *** [all] Error 2 I was only able to get this far because I changed this in configure: #CXXFLAGS=$CXXFLAGS -fno-implicit-templates -fno-exceptions -fno-rtti to: CXXFLAGS=$CXXFLAGS -fno-implicit-templates -fno-rtti Any ideas what I can do? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
