Help, Function Sequence Error
Hey all, I'm having problems ! , my app was running fine until this morning. I receive following error : Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Function sequence error /clientenrollment.asp, line 67 the line 67 for that file is oRS1.movefirst here is a snip of the code : SNIP oRS1.open SELECT * from enrollment where ClientID='varclientID', DSN=CCMS;uid=web_user;pwd=web_user if oRS1.EOF then response.write FONT SIZE=3Client is not currently enrolled in any programs click a href=javascript:popUp1() class=submenuhere/a to add a program else 'LINE67: oRS1.movefirst response.write TABLE WIDTH=80% BORDER=1 CELLSPACING=0 response.write TRTD ALIGN=CENTERBProgram Name/B/TDTD ALIGN=CENTERBTherapist/B/TD ALIGN=CENTERBStart Date/B/TDTD ALIGN=CENTERBEnd Date/B/TDTD ALIGN=CENTERBFunding/B/TDTDnbsp;/TD ALIGN=CENTER/TR Do while not oRS1.EOF response.write TRTDoRS1(program_name)nbsp;/TDTDoRS1(providerID)nbsp; /TDTDoRS1(start_date)nbsp;/TDTDoRS1(end_date)nbsp;/TD TDoRS1(funderID)/TDTDnbsp; if oRS1(active) = 1 then response.write Font COLOR=REDActive/FONT/TR else response.write /TR End If oRS1.MoveNext Loop response.write /TABLE End If /SNIP I didnt change anything on the server, have tried a reboot, but to no avail, MYSQL is running on w2k 2000 server and the ASP with through IIS. Please if anyone has any idea what I am doing wrong, please let me know. Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed on query
You should be able to just use something like this: Select uid, username From tablename Where '$externalstringvar' like query; That's PHP syntax. You'll have to use whatever makes sense for your scripting or programming language in place of the $externalstringvar variable. The single-quotes are required. Note this will always do a full table scan, so if your table is big, it will be slow. ..chris - Original Message - From: thor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 4:22 AM Subject: Help needed on query I have a table like this one: +-+---+---+ | uid | username | query | +-+---+---+ | 1 | someuser | %qu1% | | 2 | anotheru | bla% | +-+---+---+ I get a string from external source, let's say the string is 'blahblah'. Now I need a query which will take the values from column 'query', treat them as patterns for matching and return row(s) which match the given string 'blahblah'. In this example, the matched data is obviously in second row. (bla% matches blahblah) Do I make any sense? Is this possible? Any help will be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update
I'd have to guess that MySQL is interpreting the set clause this way: Set events_ID = ( 512 AND ( term = 'changed' )) That is, a logical AND, so a zero result makes sense. Still, it seems a bit odd. - Original Message - From: Salada, Duncan [EMAIL PROTECTED] For one of two reasons (probably) - and I think you found a bug: 1. The syntax in your set clause is wrong. It should be set alternatepreference='C',AltContactID='1' *Notice the use of a comma instead of and* 2. The row that matched the conditions in the where clause may already be set to those values. MySQL will not update a row needlessly. I tried out your syntax on MySQL 3.23.47 (i.e. using and in the set clause) and got some unexpected results. Below is a synopsis of what happened when I ran a query with the and syntax on my machine. THE FIRST TIME update events_terms set events_ID=512 and term='changed' where ID=108; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 It says a row was changed and it was -- but not correctly! The events_ID column was set to 0 and the term column was not changed at all. That doesn't seem like the correct behavior. Then executed the query again... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL fails to correctly index decimal datatype columns
A test SQL script which duplicates this problem is available via anonymous FTP at the following location: Hostname: ftp.visi.com Directory: /users/chris Filename: test.sql Run the script like this to see the error: mysql -t temp test.sql The second select statement returns only 2 of the 6 rows that it should have found. This problem was reported by me and fixed in 3.23.23. It appears from the changelog that MySQL developers also had problems in this area with release 3.23.14. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Execute Queries through Files
Q1: I am not sure what you mean by executing queries through files. Do you mean being able to run a query from a script like this? #!/bin/sh mysql -t -p password -u usename databasename EOF Select * From mytable; EOF Q2: There are mailing list archives available at http://lists.mysql.com . ..chris - Original Message - From: Imrani Baig Shah Masroor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Execute Queries through Files I know that one can execute queries through files. can anyone please tell me how to do that. I am working on sgi-irix and mysql 3.22.*. also how can i view previous messages on mailing list since i just joined the list yesterday. SMIB, Univ of nebraska. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CPU maxed out when database grew larger than 5 GB
David, I don't know the answers to your biggest problem, but do know the answer to your last question: Yes, it seems perfectly normal to nail one CPU and take 2 minutes to show tables with 80,000 tables. Somewhere on your system is a directory that has 240,000 files (80,000 tables times 3 files per table if they are MyISAM tables). You are doing some serious multiple-level file system indirection to wade through all the inodes allocated across thousands of indirect and indirect-indirect blocks in the filesystem. Yikes! On the other hand, one database over 6GB should not be a problem, as far as I know but I don't know everything. What's your configuration file got for settings for various parameters such as caching and what-not? Maybe you're running out of memory and thrashing the swap devices? ..chris - Original Message - From: DAVID DECESARE [EMAIL PROTECTED] I have run into an intermittent problem since one of our MySQL databases grew over 5 GB. Basically, about once to twice a day the CPU will hit 100% utilization (and stay there) and MySQL will stop accepting queries (I couldn't even do a normal shutdown of MySQL). The problem happened more frequently as the database grew over 6 GB. I end up rebooting the computer each time this happens (since I can shutdown MySQL normally) and the problem goes away for about 10 hours. Here's a list of what I am running: MySQL version: mysql-3.23.36-sun-solaris2.7-sparc OS: Solaris 8 (04/01) System: SunFire 280; Single 750 MHz; 512 MB RAM; 18 GB 10,000 RPM internal drive Number of Tables in database: approx. 8,000 Last database size: 6.3 GB The reason I think my problem has to do with the size of the database is that we have another system that has not had any problems. It has ten times the number of tables (about 80,000), but the database size is only 3.2 GB. Again, no problems with this machine at all. Has anyone else seen this problem? I would like to try version 3.23.43. Unfortunately these are full production systems that I can't take offline (and I don't have a spare SunFire). I've read about tables growing over 4 GB so I don't know why I should be having this problem. My temporary fix is to remove tables not immediately needed to keep the database size under 3 GB. However we add about 500 MB of data every 24 hours so it doesn't last long. (One other quick note: On the system with 80,000 tables, running SHOW TABLES takes about 2 minutes on a dual SunFire box (maxing out one of the CPUs). Is that amount of time normal? I know 80,000 tables is a lot, but it's either that or create fewer tables having more than 1 billion rows each.) Thanks in advance for any help! David DeCesare - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date interval problem
This should be pretty close to what you need, if I understand your situation: Select * From mytable Where (querydate1 tabledatelow And querydate1 tabledatehigh) And (querydate2 tabledatelow And querydate2 tabledatehigh); I'm assuming you know which of the two dates in the table is the lower or bottom end of the interval (called tabledatelow above) and which is the later, higher, top end of the interval. If not, you'll have to use some min() and max() functions to pick the ends of the interval correctly. - Original Message - From: Bruno Grampa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 05, 2001 5:34 AM Subject: Date interval problem Hello, i have a table with two dates, that define an interval. In the query i have two more dates, and i want to select all the records that overlap that period. Any hint to solve this problem? Thanks, Bruno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very newbie insert question
One only needs to specify the column names if and only if there are not
values, in the correct order, for all of the columns in the table.
That is:
Create Table foo (
aid intnot nullprimary key,
last varchar(30),
firstvarchar(30),
emailvarchar(60)
);
Right:
Insert Into foo Values (1, '$lastname', '$firstname', '$email');
Insert Into foo (last, first, email) Values ('$lastname', '$firstname',
'$email');
Wrong:
Insert Into foo Values ('$lastname', '$firstname', '$email');
..chris
- Original Message -
From: Russell Miller [EMAIL PROTECTED]
To: sjs [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 03, 2001 7:53 PM
Subject: Re: very newbie insert question
I did not see the full schema so I can't give you a definite answer.
However, your syntax is wrong:
INSERT INTO email_addresses (last_name, first_name, email_address) VALUES
($lastname, $firstname, $email_address);
For each value you specify, you also have to specify a column name.
Did you set it up so the email_address field is the primary key?
HTH.
--Russell
- Original Message -
From: sjs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 03, 2001 7:51 PM
Subject: very newbie insert question
This is my first script, so I am sorry it is so newbie-ish.
I think I have the correct script to open the connection, open the
database
and the table. However, I cannot get the insert into to work. I have
read
several online sources and books, all of which are slightly different. It
is
not a parse error. Can you help?
Thanks
SJS
Script as follows:
html
headtitleAdding Your Record to the Database/title/head
body
center
?php
if((!$lastName) || (!firstName) ||(!emailAddress))
{
echo You have not entered all required informaton.br Please try again;
exit;
}
$lastName=addslashes($lastName);
$firstName=addslashes($firstName);
$email_address=addslashes($email_address);
@ $db=mysql_pconnect(, ,);
if (!$db)
{
echo Couldn't connect to the database.brIf problem persists, please
contact webmaster;
exit;
}
$db= mysql_select_db(emailDB);
if (!$db)
{
echo Sorry. Could not retrieve correct database.br If problem persists,
please contact webmaster;
}
$sql = INSERT INTO email_addresses VALUES ('$lastName', '$firstName',
'$email_address');
$result = mysql_query($sql);
if (!$result)
{
echo pSorry.brYour address was not successfully added.brIf problem
persists, please contact webmaster.;
}
else
{
echo pDONE DEAL!;
}
?
/body
/html
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: process lists
It appears (without verifying it myself), these are the approximate meanings: IdThe process or thread ID of this particular job or process in the database UserUsername of this job or process or connection HostFrom which host is the User connected dbWhat is the default database or selected database in use by the User CommandWhat's this process or thread or connection doing right now TimeSome sort of indication of time but I'm not sure in what units or start point StateSome sort of indication of what this process is doing right now InfoAdditional information about the command being run right now - Original Message - From: Griff Hamlin, III [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 03, 2001 6:42 PM Subject: process lists Hello all, Can anyone tell me how to read the output of the 'mysqladmin processlist' command? I cannot find any documentation on what each column really means. Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How transfer data from Paradox to mysql?
Paradox should allow you to export your data in some sort of flat file format, such as comma separated values (CSV). If so, you can use the MySQL mysqlimport program or the MySQL load data infile 'filename' into table... statement to load the flat files into your MySQL database. - Original Message - From: Miguel Enriquez Alvarado [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 15, 2001 3:40 PM Subject: How transfer data from Paradox to mysql? i use the paradox and i want move my apps to mysql, but how? Regards otro correo: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
If you want a count for a specific team: Select count(*) From table Where team = 'support'; If you want the counts for all the teams: Select team, count(*) From table Group By team; - Original Message - From: Jamie Burns [EMAIL PROTECTED] I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Woops! Nevermind. I didn't notice your data was denormalized. - Original Message - From: Chris Johnson [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 10:33 PM Subject: Re: surely an easy quick one If you want a count for a specific team: Select count(*) From table Where team = 'support'; If you want the counts for all the teams: Select team, count(*) From table Group By team; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql on HPUX10.20
The directory /usr/local/src/mysql-3.23.42-source-bin/var/ does not exist on your system. You probably do not really want your database files stored in such a cryptic directory name. You probably need to use the --basedir or --datadir parameters to mysqld to tell it where your databases really are. It would also be a good idea for whoever built the binary package to use more intelligent default path names for those values. - Original Message - From: [EMAIL PROTECTED] Hi... I downloaded the mysql v3.23.42 and I processed as the instruction in INTALL-BINARY file, but when I run mysqld, I got the following: # bin/mysqld 010913 15:49:43 Warning: setrlimit couldn't increase number of open files to more than 60 010913 15:49:43 Warning: Changed limits: max_connections: 50 table_cache: 64 bin/mysqld: Can't change dir to '/usr/local/src/mysql-3.23.42-source-bin/var/' (Errcode: 2) 010913 15:49:43 Aborting 010913 15:49:43 bin/mysqld: Shutdown Complete Does someone have some clue to solve this problem? Leandro. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql install failed on FreeBSD!!
What version of FreeBSD do you have installed? Was it upgraded from an earlier version? If so, was the ports collection upgraded as well? This sounds like you have incompatible versions of the system and the ports collection installed. - Original Message - From: Siva Namburi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 12, 2001 3:48 PM Subject: mysql install failed on FreeBSD!! Hi, I am pretty new to mysql and started installing it on freeBSD from usr/ports/. I got the following error. Can somebody help me why it is failing. thanks, siva === Installing for mysql-server-3.23.36 === mysql-server-3.23.36 depends on executable: mysql - not found ===Verifying install for mysql in /usr/ports/databases/mysql323-client === Building for mysql-client-3.23.36 make all-recursive Making all in include Making all in libmysql /bin/sh ../libtool --mode=link cc -DDBUG_OFF -O -pipe -o libmysqlclient.la -rp ath .iso.org.dod.internet.private.enterprises.clickarray/lib/mysql -version-info 10:0:0 libmysql.lo net.lo violite.lo password.lo get_password.lo errmsg.lo my_ init.lo my_static.lo my_malloc.lo my_realloc.lo my_creat e.lo my_delete.lo mf_tempfile.lo my_open.lo mf_casecnv.lo my _read.lo my_write.lo errors.lo my_error.lo my_getwd.lo my_div.l o mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo mf_wcomp.lo typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo mf_unixpath.lo my_fopen.lo my_fstream.lo mf_loadpath.lo my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo default.lo my_compress.lo array.lo my_once.lo list.lo my_net.lo cha rset.lo hash.lo getopt.lo getopt1.lo getvar.lo my_lib.lo strmov.lo strxmov.lo st rnmov.lo strmake.lo strend.lo strnlen.lo strfill.lo is_prefix. lo int2str.lo str2int.lo strinstr.lo strcont.lo strcend.lo bchange.lo bmove.lo bmove_upp.lo longlon g2str.lostrtoull.lo strtoll.lo llstr.lo ctype.lo dbug.lo -lz -lcrypt -lm libtool: link: only absolute run-paths are allowed *** Error code 1 Stop in /usr/ports/databases/mysql323-client/work/mysql-3.23.36/libmysql. *** Error code 1 Stop in /usr/ports/databases/mysql323-client/work/mysql-3.23.36. *** Error code 1 Stop in /usr/ports/databases/mysql323-client. *** Error code 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL: Unable to save result set
That error message is coming from PHP, not from MySQL. PHP generates that error message when, for some reason, the number of fields returned by a MySQL query was greater than zero, but the return from mysql_store_result() or mysql_use_result() was zero or NULL. The PHP code which checks those values is very dependent on the version of MySQL you are using. The check for valid fields is different for version before and after MySQL 3.22.24, and the query execution is different before and after MySQL version 3.21.99. Did you recently upgrade you MySQL server without getting a new PHP interpreter, or re-compiling your PHP interpreter with the newer MySQL client library? ..chris - Original Message - From: Catalin Tenita [EMAIL PROTECTED] Hello, Starting 2-3 days ago, when we or our users try to access some parts of our site we receive some PHP/SQL, as this: Warning: MySQL: Unable to save result set in /home/bestjobs/public_html/nb/insert_user.php on line 21 We did not change anything in the php scripta or database structure and when we've run the scripts on other server all os OK. So we believe this errors are caused by a sever problem. Can you tell me what kind of problems might be? Catalin Tenita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trouble connecting to MySQL server using MySQLGUI and VCC
The remote hosts you mention are not allowed to access the database. This is a permissions problem, in particular the value of hosts in one of the permissions tables. It sounds like they only allow connections from the localhost or their own network. ..chris - Original Message - From: Robert Anderson [EMAIL PROTECTED] I've been playing with MySQL and trying to get connected to my web-hosting company's MySQL server that they set up for me. In trying to use the MySQLGui and also the VCC, I get the error Host 'junior.dsl.neticus.com' is not allowed to connect to this MySQL server. And if I'm plugged in at work I get the same error with a different 'dsl.neticus.com' thing. I can telnet to the company and then use their 'mysql.pm' utility using the same parameters from the exact same workstation and locations. Any ideas? Thanks in advance, Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: WHERE IN ( MAX ) in MySQL
I think you will you have to do it in 2 steps using a temporary table. This is one of MySQL's biggest weak points. ..chris - Original Message - From: Mattias Jiderhamn [EMAIL PROTECTED] I have a table CREATE TABLE currency_rate ( currency_id VARCHAR(3), currency_date DATE, currency_rate NUMERIC(16,8), PRIMARY KEY (currency_id, currency_date) ); and want to list the latest registered rates of all currencies. Normally I would go about like this: SELECT currency_rate.currency_id, currency_date, currency_rate FROM currency_rate WHERE (currency_rate.currency_id, currency_date) IN ( SELECT currency_id, MAX(currency_date) FROM currency_rate GROUP BY currency_id ) But MySQL doesn't support nested selects... How do I solve this? The following gives me the last date but not the corresponding rate: SELECT currency_rate.currency_id, MAX(currency_date), currency_rate FROM currency_rate GROUP BY currency_id Mattias Jiderhamn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs Oracle vs Acess
I've used all three, Access, Oracle and MySQL. I have the least expertise with Access. My impressions are: * Access is very limited in what it can do with respect to real database manipulations, those contained in SQL. It does not handle large amounts of data well. Access is really a very fancy, scriptable (VB) GUI wrapped around a very simple database engine. * MySQL is a very fast, efficient fairly well featured database system, which can handle large amounts of data. It handles most standard SQL constructs. * Oracle is a very extensive database system, with many extensions, tools and features. It handles all standard SQL constructs and adds many non-standard SQL constructs. It is also a rather large footprint. Comparatively, MySQL lacks a number of features that Oracle has: subselects, views, referential integrity, cluster ability, etc. But for the most part, MySQL and Oracle are more similar than either is like Access. I'd guess that for the most part, moving the small projects to MySQL from Access will make future moves from MySQL to Oracle much easier than moving them from Access to Oracle. - Original Message - From: Gene Gurevich [EMAIL PROTECTED] This is a very generic quesiton and I would appreciate any info. I am an Oracle dba; our developers are now using access for their own small projects. When these projects grow and become too large for Access we move them to Oracle. The developers are now considering switching from Access to MySQL and I'm trying to figure out what are the advantages of that move. I have read some documentation and it looks like a big advantage of MySQL vs Access is multi-user access. There are also some features that are present in Oracle but missing in My SQL like views, subqueries and a few other. I was wondering if someone who has working experience in both Access and MYSQL (or ORacle and MySQL) could expand on the differences from practical standpoint. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql (command line program) enhancement request
I use MySQL a number of different ways: the C API, from PHP, from the command line, and often from shell scripts run by other users or from crontab entries. The latter two uses present a couple of problems that could be fixed easily through some minor changes to the code for the mysql program (mysql.cc). I'd like to see the following 2 changes: (1) Blank out the password in the environment variables while running for security. Otherwise, on any system where one can run a ps command (most Unix derivatives), any user can easily see the password when it is given on the command line to the '-p' argument. As there is no other reasonable way to provide the password, this is a common problem. The C/C++ code to do this is simple, small and efficient. (2) When running in a batch mode, one usually wants to know if the command worked or not. The default is to tell the user absolutely nothing (example 1 below). Adding the '-v' (verbose) flag gets the user some feedback -- the SQL that was submitted -- but says nothing about success (example 2 below). Adding a second 'v' (-vv) adds the results of the command to the feedback (example 3 below). This seems completely backwards. I know what SQL I submitted; I want to know if it worked. I'd like to see the meaning of the '-v' and '-vv' flags reversed, giving the results with the first 'v', and then echoing the SQL along with the results with the second 'v'. Example 1: 49 hostname:~ mysql -pmypass -e 'Update foo Set val = 1 Where id = 1;' temp 50 hostname:~ Example 2: 50 hostname:~ mysql -v -pmypass -e 'Update foo Set val = 1 Where id = 1;' temp -- Update foo Set val = 1 Where id = 1 -- 51 hostname:~ Example 3: 51 hostname:~ mysql -vv -pmypass -e 'Update foo Set val = 1 Where id = 1;' temp -- Update foo Set val = 1 Where id = 1 -- Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Bye 52 hostname:~ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: any sequence generator function?
Looking at this post and your previous one, you could just create a new table with the same definition as the old table except to make the field you want with the numbered field be an int auto_increment datatype. Then do the following SQL: Insert Into newtable Select field1, field2, NULL, field3, field4... From oldtable; You have to list all the fields in the old table EXCEPT the one you want auto incremented in a sequence. In that field's position you put NULL (or zero), and MySQL will increment the field for you as it does the inserts. ..chris - Original Message - From: Jaime Teng [EMAIL PROTECTED] Hi, Is there a number sequence generator/function in MySQL such that if I were to execute: UPDATE mytable SET id = thisfunction; it will update the table mytable and set the column id to a numbered sequence 1,2,3,4,5,6,7,8,9.? currently, i have a table with 2.5 million records and i want to change one of the field and sequence it and am doing it the hard way of updating one at a time. regards Jaime - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help renumbering unique
Do it this way: Create Table copy ( sessionid int not null auto_increment, username varchar(20 not null, start int not null, stop int not null, ipaddress varchar(15), Primary Key (sessionid), Index altkey (username, start, stop) ); Insert Into copy Select NULL, username, start, stop, ipaddress From youroriginaltablename; ..chris - Original Message - Jaime Teng wrote: Hi, I have a mysql table: +---+--+--+-+ | Field | Type | Null | Key | +---+--+--+-+ | sessionid | int(10) unsigned | | PRI | | username | char(20) | | MUL | | start | int(10) unsigned | | MUL | | stop | int(10) unsigned | | MUL | | ipaddress | char(15) | YES | | +---+--+--+-+ sessionid is a unique key. Is there a way to change the values of sessionid such that the values will be numbered from 1 and increments by 1? ie, if the numbers were 1,20,22,25,40,45,47. the new data will be 1,2,3,4,5,6,7,8,9. ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: duplicate delete query
Do you want to delete all records which have duplicates, or do you want to delete all the duplicates but leaving one copy of each duplicated record behind? - Original Message - From: Rory O'Connor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 30, 2001 4:50 PM Subject: duplicate delete query excuse me if this is too newbie...but I need to perform a duplicate delete on my database using the e-mail address as the key. I understnad how to use COUNT and DISTINCT together... select count(DISTINCT email) FROM table; but I can't seem to find any info on how to perform the actual deletion of the duplicate records. Any info is appreciated! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.31 configure fails with Berkeley DB 3.2.3h
Description: I just downloaded MySQL 3.23.31 and the "patched version" of Berkeley DB 3.2.3h from the www.mysql.com site. I ran configure with the option to enable the Berkeley DB, but it fails, telling me I don't have the proper version of the DB. Here are the last few lines from the configure output: checking for pthread_attr_setscope... yes checking can netinet files be included... yes checking character sets... default: latin1; compiled in: latin1 checking for BerkeleyDB... supplied configure: error: didn't find valid BerkeleyDB: '/usr/local/BerkeleyDB.3.2' doesn't look like a BDB directory (invalid version 3.2.3 (must be at least version 3.2.3g)) How-To-Repeat: Install from above named source distributions. Build BDB and then try to configure MySQL with it. Fix: It can probably be hacked to make it work, but should not need to. Submitter-Id: submitter ID Originator:Chris Johnson Organization: MySQL support: none Synopsis: MySQL 3.23.31 configure fails with Berkeley DB Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.31 (Source distribution) Environment: System: FreeBSD ocelot.claimlynx.com 4.2-RELEASE FreeBSD 4.2-RELEASE #0: Thu Jan 11 12:07:44 CST 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/OCELOT i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1169076 Nov 20 05:59 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jan 5 18:09 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 559196 Nov 20 05:59 /usr/lib/libc.so.4 Configure command: ./configure --enable-thread-safe-client --with-berkeley-db=/usr/local/BerkeleyDB.3.2 --prefix=/usr/local/mysql Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
