Re: SELECT and LIMIT question
Yes. I want to retrieve 5 of each. If I put LIMIT 5, the entire query retrieves only 5 for table1.col2 with value of 1 because 1 is the first in the IN (1,2,3) clause. If I put LIMIT 15, the query returns 15 rows same as above instead 5 for each 1, 2, and 3 values. This is because there are more than 15 rows in the table with value 1 in table1.col2. The query will start with table1.col2 with value of 2 only when the number of rows with the value (1) are exhausted and the limit is more than the count. ~Jay --- Michael Stassen [EMAIL PROTECTED] wrote: Why do you think it doesn't work with LIMIT? Do you want 5 of each? Michael Jay K wrote: Hi, I have multiple queries like this: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 desc LIMIT 5 and SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 desc LIMIT 5 The only difference is in the WHERE clause table1.col2 = x. I would like to have one statement instead of multiple statements and though about using IN clause like: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 IN (1,2,3) ORDER BY col3 desc LIMIT 5 It works without LIMIT, but I need to use the LIMIT. Any help is appreciated. Thanks in advance, Jay ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP address to searchable number
Scott, This may not be your exact solution, but if your network address is 24 bit then you could just check against the last octet, i.e. x = 10 and x = 100. James --- Scott Haneda [EMAIL PROTECTED] wrote: Given a IP range such as: 12.8.197.10 - 12.8.197.100 I want to store those 2 values in a database, before I insert a new value, I would like to test for the new values existence. Any idea what I should convert a IP address into in order to be able to operate on it with simple greater than, less than and equal to math? -- -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- 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: IP address to searchable number
From: Scott Haneda [EMAIL PROTECTED] Any idea what I should convert a IP address into in order to be able to operate on it with simple greater than, less than and equal to math? As a compromise between human readability and searchability you could store the IP-address as a series of zero-padded numbers in a VARCHAR field: 012.008.197.010 - 012.008.197.100 Now you can use string comparison operators such as , , =, BETWEEN and even LIKE to see if the IP-address is in the range 12.8.* (from_ip LIKE '012.008.%'). If you want to use normal IP-masks than you probably want to use unsigned integer using the inet_aton and inet_ntoa functions as suggested by others. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange query performance problem
Mysql 4.1.3 Windows XP SP1 All tables are InnoDB The query (1): select Product.id, LongAnswer.value, count(*) from LongAnswer inner join Answer on LongAnswer.answer=Answer.id inner join QuestionDefinition on Answer.question=QuestionDefinition.id inner join Survey on Answer.survey = Survey.id inner join DueReport on Survey.dueReport = DueReport.id inner join Product on Answer.product = Product.id where ( Product.id = 117 ) and(QuestionDefinition.id=2 ) and(DueReport.id=64 ) group by Product.id, LongAnswer.value; produces these results (see running time): +-+---+--+ | id | value | count(*) | +-+---+--+ | 117 | 3 | 47 | | 117 | 4 | 153 | +-+---+--+ 2 rows in set (0.92 sec) If I drop ( Product.id = 117 ) clause the larger rowset gets generated but it's lightning fast (2): select Product.id, LongAnswer.value, count(*) from LongAnswer inner join Answer on LongAnswer.answer=Answer.id inner join QuestionDefinition on Answer.question=QuestionDefinition.id inner join Survey on Answer.survey = Survey.id inner join DueReport on Survey.dueReport = DueReport.id inner join Product on Answer.product = Product.id where (QuestionDefinition.id=2 ) and(DueReport.id=64 ) group by Product.id, LongAnswer.value; +-+---+--+ | id | value | count(*) | +-+---+--+ | 64 | 3 |4 | | 64 | 4 | 196 | | 65 | 3 |3 | | 65 | 4 | 197 | | 66 | 3 |6 | | 66 | 4 | 194 | | 67 | 3 | 44 | | 67 | 4 | 156 | | 68 | 3 | 21 | | 68 | 4 | 179 | | 69 | 3 | 20 | | 69 | 4 | 180 | | 70 | 3 | 26 | | 70 | 4 | 174 | | 71 | 3 | 11 | | 71 | 4 | 189 | | 72 | 3 | 102 | | 72 | 4 | 98 | | 73 | 3 | 31 | | 73 | 4 | 169 | | 74 | 3 | 19 | | 74 | 4 | 181 | | 75 | 3 | 13 | | 75 | 4 | 187 | | 76 | 3 | 22 | | 76 | 4 | 178 | | 77 | 3 | 39 | | 77 | 4 | 161 | | 78 | 3 | 16 | | 78 | 4 | 184 | | 79 | 3 | 56 | | 79 | 4 | 144 | | 80 | 3 | 66 | | 80 | 4 | 134 | | 81 | 3 | 36 | | 81 | 4 | 164 | | 82 | 3 | 68 | | 82 | 4 | 132 | | 83 | 3 | 73 | | 83 | 4 | 127 | | 84 | 3 | 49 | | 84 | 4 | 151 | | 85 | 3 | 54 | | 85 | 4 | 146 | | 86 | 3 | 50 | | 86 | 4 | 150 | | 87 | 3 | 48 | | 87 | 4 | 152 | | 88 | 3 | 35 | | 88 | 4 | 165 | | 89 | 3 |9 | | 89 | 4 | 191 | | 90 | 3 |9 | | 90 | 4 | 191 | | 91 | 3 | 10 | | 91 | 4 | 190 | | 92 | 3 | 24 | | 92 | 4 | 176 | | 93 | 3 | 38 | | 93 | 4 | 162 | | 94 | 3 | 52 | | 94 | 4 | 148 | | 95 | 3 | 18 | | 95 | 4 | 182 | | 96 | 3 | 17 | | 96 | 4 | 183 | | 97 | 3 | 10 | | 97 | 4 | 190 | | 98 | 3 | 85 | | 98 | 4 | 115 | | 99 | 3 |3 | | 99 | 4 | 197 | | 100 | 3 |5 | | 100 | 4 | 195 | | 101 | 3 |7 | | 101 | 4 | 193 | | 102 | 3 | 22 | | 102 | 4 | 178 | | 103 | 3 | 23 | | 103 | 4 | 177 | | 104 | 3 | 22 | | 104 | 4 | 178 | | 105 | 3 |2 | | 105 | 4 | 198 | | 106 | 3 |3 | | 106 | 4 | 197 | | 107 | 3 |8 | | 107 | 4 | 192 | | 108 | 3 |9 | | 108 | 4 | 191 | | 109 | 3 | 21 | | 109 | 4 | 179 | | 110 | 3 | 25 | | 110 | 4 | 175 | | 111 | 3 | 10 | | 111 | 4 | 190 | | 112 | 3 | 113 | | 112 | 4 | 87 | | 113 | 3 | 54 | | 113 | 4 | 146 | | 114 | 3 | 69 | | 114 | 4 | 131 | | 115 | 3 | 68 | | 115 | 4 | 132 | | 116 | 3 |6 | | 116 | 4 | 194 | | 117 | 3 | 47 | | 117 | 4 | 153 | +-+---+--+ 108 rows in set (0.08 sec) explain select shows subtle differences but I am not experienced enough to interpret them properly? (1) ++-+++--++-+---+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra|
Re: my_thread_init
John McCaskey wrote: I believe that what you described is perfectly acceptable. The thing to keep in mind is the thread_init allocates thread specific memory for mysql, and the thread_end clears it. As such you should never execute any other mysql commands unless you have executed an init, and you should never init more than once without first ending, but assuming you get them all matched up right it is alright to have multiple init/end pairs within a thread. I do this in my own code, which has worked well for sometime now, so while the manual doesn't make it completely clear this is ok, I'm pretty sure it is. One note, I see you write my_thread_init/my_thread_end in your message, the correct names are mysql_thread_init and mysql_thread_end, the my_init function should be called once per process, not in each thread. I've looked carefully at our code, and for one sofware, which use MySQL 4.0.17, mysql_thread_init is an undefined symbol, but my_thread_init is not, for the second one (4.0.20a), that's the opposite. but we still call my_init only once. Thanks, -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I override skip-networking in 2nd conf file?
My MySQL 4.0 server tells me it reads these configuration files in the given order: /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf The first contains the skip-networking option, which I would like to override in the second file. The documentation states that skip-networking is a parameterless option (unlike e.g. log-bin=[file]), so I can't do skip-networking=false in the second file to switch networking on again. Is there some other way to do this? [As to why I want to do this: I have an automatic nightly software update running. Whenever the MySQL server package is updated, it stops my MySQL server, tries to install a new version, fails because I've edited /etc/mysql/my.cnf, does not update the software and does not restart the server. So I'd now like to keep the first config file as delivered and would like to make my settings in the /var/lib/mysql/my.cnf one which isn't overwritten by the distribution. To do this, I need to reenable networking, which is switched off by the standard dist file.] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access Denied
Since I see this question every single day hereI will post this link. http://dev.mysql.com/doc/mysql/en/Access_denied.html It is a decent page on the MySQL site that gives you reasons why you get Access Denied. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number of Rows in DB.
Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number of Rows in DB.
hi.. you can use... select count(1) from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
I'm want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extenderd inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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: Number of Rows in DB.
hi all.. I think there is no way to count all the rows in a DB, using a single query. we can say.. desc table_name; (gives all the tables in the DB) and then get no of rows for each table.. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: Number of Rows in DB. I'm want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extenderd inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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 puzzle (at least for me)
Hi, I could not do this... TABLE1 ID NAME price - -- 1 1stname 34 2 2ndname 0 TABLE2 date 1stname 2ndname --- --- --- 20041023 23.5 12.3 As a result I want to get out raws something like this... 1 34 1stname 23.5 2 0 2ndname 12.3 What is the SQL syntax for this puzzle...?? Or is it possible? cheers... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where to download older versions?
Hi, please, where can I download older version (4.0.20) of mysql for Windows? I searched archives, documentation, no advice. In fact, I only need libmysql.dll file - can anybody send me? thanks, Roman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join question
Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? Any help would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a puzzle (at least for me)
[snip] I could not do this... TABLE1 IDNAME price - -- 1 1stname 34 2 2ndname 0 TABLE2 date 1stname 2ndname --- --- --- 20041023 23.512.3 As a result I want to get out raws something like this... 1 34 1stname 23.5 2 0 2ndname 12.3 What is the SQL syntax for this puzzle...?? Or is it possible? [/snip] Not the way you have your tables constructed. There is no relationship mechanism between the two tables where a basic JOIN can take place. '1stname' is data in one table and is a column in the other table. Does that make sense to you? If you formed the 2nd table like this ...(watch out for keywords, such as 'NAME' and 'date'...you'll want to use something else) date nameprice --- --- --- 20041023 1stname 23.5 20041023 2ndname 12.3 ..you'd have something to hang your hat on. SELECT a.ID, a.price, a.NAME, b.price FROM TABLE1 a, TABLE2 b WHERE a.NAME = b.name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
In article [EMAIL PROTECTED], Martin Gainty [EMAIL PROTECTED] writes: You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause Forgive me for following the documentation!! You didn't, actually. You did put the ReviewerID = 2 clause into the WHERE clause and then complained that this restricted your result set ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: left join question
[snip] Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? [/snip] Yes, and the keys from table to table don't have to be the same, save for each JOIN.. SELECT a.id, a.amount, b.invoiceID FROM t1 a LEFT JOIN t2 b ON(a.id = b.id) LEFT JOIN t3 c ON(b.invoiceID = c.invoiceID) ---look Ma, different relation!) WHERE c.invoiceID IS NULL (c is empty for this query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Defined functions
Hi,
How to call a user defined function. I am using C as the programming
language. I write a small function in C. Created .dll. Then how to run it from mysql
pompt?. How to call or use it?. will any one of you help me in this.
Thanks,
Narasimha
-Original Message-
From: Aftab Jahan Subedar [mailto:[EMAIL PROTECTED]
Sent: Mon 9/6/2004 1:14 AM
To: [EMAIL PROTECTED]
Cc:
Subject: Re: Help needed with MySQL C API-based client (segfault)
have you tried ?
http://www.geocities.com/jahan.geo/mysql_c_by_example.html
Ruben Safir Secretary NYLXS wrote:
On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users. And I've stay susbscribed
but I haven't posted to it in many many months. iIn fact, I didn't notice
I wasn't any longer subscribed. So I'm writing this application in C
and GTK and I was thinking, it's finally time to learn to write some MYSQL
C API stuff.
I've written a lot of Oracle C programs in years past. And now I'm looking
at the C API stuff and wow, it is not readly understandable. I open up
the mysql mail file with mutt, and bang, this is on the top!
I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry. Checks the
potention errors, and maps the most basic column types to C types.
The docs say to look at examples in the source directory, but those
aren't yet clear to me to understand.
Ruben
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n,
mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d rows retrieved **\n,
numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
Re: left join question
- Original Message - From: Richard Reina [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 8:26 AM Subject: left join question Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? Any help would be greatly appreciated. That's not a problem. For example: select t1.cola, t2.colb, t3, colc from table1 t1 left join table2 t2 on t1.col1 = t2.col1 left join table3 t3 on t2.col4 = t3.col8 where t1.col5 = 'M' order by t2.colb; Note that the second line of the join - left join table3 t3 on t2.col4 = t3.col8 - has nothing preceding the keywords 'left join' on that same line; the 'on' clause is what connects table3 to one of the other tables in the join. table3 could be joined to either table1 or table2 via the 'on' clause. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL doesn't startup anymore
I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
What is written to ther error log? Marc wrote: I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Defined functions
[EMAIL PROTECTED] wrote: Hi, How to call a user defined function. I am using C as the programming language. I write a small function in C. Created .dll. Then how to run it from mysql pompt?. How to call or use it?. will any one of you help me in this. Thanks, Narasimha I don't clearly understand what you are trying to do. Are trying to create a programm that query a mysql server with the C api, _or_ are you trying to implement a new feature in your mysql server by adding a UDF to it ? the C api si here : http://dev.mysql.com/doc/mysql/en/C.html but for adding a UDF : http://dev.mysql.com/doc/mysql/en/Adding_functions.html if you add a UDF, make sure your server is linked dynamically and not statically. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a puzzle (at least for me)
- Original Message - From: Ferhat BINGOL [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 8:24 AM Subject: a puzzle (at least for me) Hi, I could not do this... TABLE1 ID NAME price - -- 1 1stname 34 2 2ndname 0 TABLE2 date 1stname 2ndname --- --- --- 20041023 23.5 12.3 As a result I want to get out raws something like this... 1 34 1stname 23.5 2 0 2ndname 12.3 What is the SQL syntax for this puzzle...?? Or is it possible? You obviously need to do a join to combine any two (or more) tables in MySQL. I assume you know that, although you haven't shown us any of your attempts at making this query work so I can't be sure. Your desired result is clearly trying to join one table to another when a data value in one of the tables is the same as a *column name* in the other table. You want MySQL to somehow realize that the data value '1stname' in TABLE1 needs to be replaced with the data values in the '1stname' COLUMN of TABLE2. That simply isn't possible. Joins are based on the idea that a piece of data in one table matches a piece of data in the other table, not a column name in the second table. The problem here is your data model, not SQL syntax. Unfortunately, I haven't got the foggiest idea what your data represents so I can't suggest a better way to organize your data. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Binary Field when UTF8 and Collate
We have a database that has one particular table, searchdata, that has the following columns BINARY key_low(5) BINARY key_high(5) VARCHAR searchss(255) The following query, when the SET QUERY UTF8 option is present, doesn't work because MySQL encodes the binary field. Any ideas how we get around this? We're running 4.1.5a gamma and the table encoding is set to utf8. SELECT searchsss FROM searchdata WHERE '$D6@/' BETWEEN key_low AND key_high; Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. **
Re: join syntax
Please reply to the list, rather than to me, personally. Keeping the thread on the list improves your odds of geting a solution, and allows others with similar questions to see the answers. Marco wrote: Michael Stassen wrote: What do you mean, it doesn't work? Michael Marco wrote: The query select * from a join b on a.x = b.y; works on mySQL 3.23.56 but doesn't work on mySQL 3.23.58. I had to change it in select * from a,b where a.x = b.y; Is it a well-known behavior or is it a bit strange? Thanks, Marco The join on syntax works on 3.23.56 but doesn't work on 3.23.58 Repeating yourself won't help. The query select * from a join b on a.x = b.y; is perfectly valid syntax and should produce the same result in 3.23.56 and 3.23.58. You need to say what you mean by doesn't work. Do you get an error message? If so, what is it?. Do you get unexpected results? If so, what are they, and what were you expecting? Either way, I'm guessing that what you've posted here is an example, rather than your real query. As your example is valid syntax, it seems likely that the problem stems from something in your real query. Therefore, it would be better to post the real query. In other words, we can't diagnose the problem until you describe in detail what is wrong. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple linked tables, fetch results
Hi there I have three linked tables, the_data (with the actual data I want), linked with an nn_link table to a category table. Then, that category table is linked n:1 to a display_category table. The other way around: Each display_category entry is linked to one category entry, which in turn is linked to several the_data entries (n:n). I hope that just made sense :-) Now I want to extract all the_data entries that are in all of a list of display_category entries (because those display_category entries are hierarchically ordered as well). For example, when I display the category 1-2-3 in that hierarchical order, I want to display all the entries that are in the display_category-ies 1, 2 and 3. What I managed to do so far though is just to extract all the entries that are in one of those categories... select * from ((( the_data inner join nn_link on the_data.id = nn_link.the_data_id ) inner join category on category.id = nn_link.category_id ) inner join display_category on category.id = display_category.category_id ) where display_category.id in (1, 2, 3); I tried with a group by as well: select * from ((( the_data inner join nn_link on the_data.id = nn_link.the_data_id ) inner join category on category.id = nn_link.category_id ) inner join display_category on category.id = display_category.category_id ) group by the_data.id having display_category.id in (1, 2, 3) and count(the_data.id) = 3; But unfortunately, I don't understand the results, even with help of the explain output - the counts were higher than I anticipated, I thought they would just represent the number of possible paths towards a the_data entry... Thanks in advance for your kind help Guido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie HELP. Using SELECT in Query Browser to retrieve records
Am new to mysql and have some difficulty in retrieveing data from 3 tables using the Query Browser(version 1.0.8). I have 3 tables (axkde, axuser,axdev). All 3 tables have 'id','sender','reply'fields. Id is unique in all 3 tables. Now I want a query to extract data from the 3 tables. Something like this: |sender_axkde|f_kde| sender_axuser|f_user| sender_axdev|f_dev| |...||.||||... | sowe |3 | sowe | 16 |sowe | 1 | |...||.||||... | joe | 15 | joe| 7 |joe| 52 | I wrote this sql and had syntax error SELECE axkde.sender, axuser.sender, axdev.sender COUNT (axkde.sender, axuser.sender, axdev.sender) AS f_kde,f_use,f_dev FROM axkde, axuser, axdev GROUP BY axkde.sender, axuser.sender, axdev.sender; I read the SQL manual but could not make any improvement on introducing the JOIN command in my query. Assisatnce is highly solicited. Rehards I want a select command that can retrieve 'sender' from the 3 tables _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
try: SHOW TABLE STATUS Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16 AM: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and LIMIT question
I believe you need to combine the results of 3 separate queries (each with a limit of 5) into a temp table and respond with the contents of the table you built. If I read this correctly (http://dev.mysql.com/doc/mysql/en/UNION.html) you could do the same thing with a UNION query and skip the temp table step (MySQL does it for you). But you must have be using a version that supports UNION queries. Each piece-wise query must have it's own LIMIT constraint in order to apply the limit to each value you are querying on. Using either method you could even want 10 of #1 but only 5 of #2 and only 3 of #3 and still make it work. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jay K [EMAIL PROTECTED] wrote on 10/14/2004 01:59:47 AM: Yes. I want to retrieve 5 of each. If I put LIMIT 5, the entire query retrieves only 5 for table1.col2 with value of 1 because 1 is the first in the IN (1,2,3) clause. If I put LIMIT 15, the query returns 15 rows same as above instead 5 for each 1, 2, and 3 values. This is because there are more than 15 rows in the table with value 1 in table1.col2. The query will start with table1.col2 with value of 2 only when the number of rows with the value (1) are exhausted and the limit is more than the count. ~Jay --- Michael Stassen [EMAIL PROTECTED] wrote: Why do you think it doesn't work with LIMIT? Do you want 5 of each? Michael Jay K wrote: Hi, I have multiple queries like this: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 desc LIMIT 5 and SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 desc LIMIT 5 The only difference is in the WHERE clause table1.col2 = x. I would like to have one statement instead of multiple statements and though about using IN clause like: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 IN (1,2,3) ORDER BY col3 desc LIMIT 5 It works without LIMIT, but I need to use the LIMIT. Any help is appreciated. Thanks in advance, Jay ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date range with empty rows (Was: Intra-table join)
In article [EMAIL PROTECTED], Partap Davis [EMAIL PROTECTED] writes: I'm graphing the data from this query using dates on the x axis. The input to my graph module (GD::Graph) requires a constant-length list. So if any days in my selection range have no data, I need to fill the space with an empy value. For example, say my date range is '2004-10-01' to '2004-10-05' and the query returns: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-04, 250 I have to do some date manipulation in perl afterward to check for missing values... If I could get a query that returned: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-03, NULL 2004-10-04, 250 2004-10-05, NULL That would be so much nicer in some cases. I'm thinking it would be sort of like the output from a LEFT JOIN if I had a table containing just a bunch of sequential dates...but I don't...and the dates can actually be arbitrary, so it would have to be a pretty big table, with no real data in it. I guess you'd need indeed a helper table, but it it doesn't need to be big. For the example above, you could do: CREATE TABLE helper (x INT); INSERT INTO helper VALUES (0), (1), (2), (3), (4); SELECT '2004-10-01' + INTERVAL x DAY, ... FROM helper LEFT JOIN yourtbl ON day = '2004-10-01' + INTERVAL x DAY ...; Do you see the picture? Your helper table needs as many rows as you want to return from your query, not more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date range with empty rows (Was: Intra-table join)
I have the same issue (missing dates) with my graphing widget, too. I worked around my problem by using script to create a temp table that contains all of the dates that cover the range of dates I want to chart then left join the data tables to my temp table. Sure it's a small memory load, and there's a bit of overhead involved in generating all of those date values, but it goes away right after I am through with it. If it becomes a problem, I will probably do as you thought about doing and will either create one long table of nothing but dates or I will massage my data after retrieval or I will change my widget. So far I am not running into any noticeable problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Partap Davis [EMAIL PROTECTED] wrote on 10/14/2004 01:35:31 AM: On Wed, 13 Oct 2004 20:25:48 -0700, Chris [EMAIL PROTECTED] wrote: create table test_table (d date not null, name char(32) not null, loc integer not null, type integer not null, amount integer, primary key (d, name, loc, type)); snip snip This is how I would do it: SELECT d as day, SUM(amount) as total, SUM(IF(1=loc,amount,0)) as loc1_total, SUM(IF(2=loc,amount,0)) as loc2_total FROM test_table GROUP BY d ; I'm not sure this is the best way to go, as that SUM(IF()) functionality seems a bit unclean to me, but it works. Thanks, that works great! Now, on a somewhat related note, I'm trying to get the data from mysql in a ready to use format with no post-processing required... I'm graphing the data from this query using dates on the x axis. The input to my graph module (GD::Graph) requires a constant-length list. So if any days in my selection range have no data, I need to fill the space with an empy value. For example, say my date range is '2004-10-01' to '2004-10-05' and the query returns: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-04, 250 I have to do some date manipulation in perl afterward to check for missing values... If I could get a query that returned: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-03, NULL 2004-10-04, 250 2004-10-05, NULL That would be so much nicer in some cases. I'm thinking it would be sort of like the output from a LEFT JOIN if I had a table containing just a bunch of sequential dates...but I don't...and the dates can actually be arbitrary, so it would have to be a pretty big table, with no real data in it. Does anyone know of some way to fake this date table? (again, I'm using mysql 4.0.16) ...stuck on these LEFT JOINS lately for some reason ;-) Thanks, -partap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Sujay, The output of DESCRIBE table_name; does not include number of rows. Perhaps you meant SHOW TABLE STATUS LIKE 'table_name'; or simply SHOW TABLE STATUS; as Michael suggested, to get output for each and every table. If so, it is important to note that the row count for InnoDB tables is an approximation, rather than an accurate count, so it won't help here. http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Michael, I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals? If you do a SHOW TABLE STATUS in the prod db and loop through the results, you can get the row counts for the MyISAM tables directly from the results and then do a SELECT COUNT(*) from the InnoDB tables to get their row counts. Now that you have numbers for each table, why not do the same thing in standby and compare the results table by table, rather than adding and comparing the total? WARNING: Note that any inserts or deletes on prod between the mysqldump and the counting will throw off the results. If you just want to verify that standby matches the dump, I suppose you could turn off extended inserts and count the number of inserts in the dumpfile with something like grep 'INSERT' dumpfile | wc -l then compare that total to the total obtained by looping through the tables in standby, as described above. But I'm still skeptical of the usefulness of that test. Michael Sujay Koduri wrote: hi all.. I think there is no way to count all the rows in a DB, using a single query. we can say.. desc table_name; (gives all the tables in the DB) and then get no of rows for each table.. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: Number of Rows in DB. I want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extended inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and LIMIT question
Right. (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 3 ORDER BY col3 DESC LIMIT 5) ORDER BY col3 DESC; should work. Notice that you can reorder the results after the UNIONs, if you want, with a final ORDER BY clause. UNION is available in MySQL 4.0.0 and up. Michael [EMAIL PROTECTED] wrote: I believe you need to combine the results of 3 separate queries (each with a limit of 5) into a temp table and respond with the contents of the table you built. If I read this correctly (http://dev.mysql.com/doc/mysql/en/UNION.html) you could do the same thing with a UNION query and skip the temp table step (MySQL does it for you). But you must have be using a version that supports UNION queries. Each piece-wise query must have it's own LIMIT constraint in order to apply the limit to each value you are querying on. Using either method you could even want 10 of #1 but only 5 of #2 and only 3 of #3 and still make it work. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Note that SHOW TABLE STATUS counts for innodb are educated guesses -- innodb has to do a table scan to get the actual count. On Thu, 14 Oct 2004 10:19:19 -0400, Michael Stassen [EMAIL PROTECTED] wrote: Sujay, The output of DESCRIBE table_name; does not include number of rows. Perhaps you meant SHOW TABLE STATUS LIKE 'table_name'; or simply SHOW TABLE STATUS; as Michael suggested, to get output for each and every table. If so, it is important to note that the row count for InnoDB tables is an approximation, rather than an accurate count, so it won't help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
Harals: Any resultset which ANDs any/all condition(s) would further restrict that resultset to meet the criteria I guess I just dont understand your definition of 'complaining' Martin- - Original Message - From: Harald Fuchs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 8:41 AM Subject: Re: Q: outer join w/restriction In article [EMAIL PROTECTED], Martin Gainty [EMAIL PROTECTED] writes: You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause Forgive me for following the documentation!! You didn't, actually. You did put the ReviewerID = 2 clause into the WHERE clause and then complained that this restricted your result set ;-) -- 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: Number of Rows in DB.
Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); [EMAIL PROTECTED] wrote: try: SHOW TABLE STATUS Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16 AM: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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: Number of Rows in DB.
Well basically what I did was simply move a script that I've been using on other databases and tried it on this new LARGE db. And luckily the number or rows were off by something like 30,000 so it was easy to figure out something went wrong (got total from phpMyAdmin) I then tweak my dumps so that it works. I'm just worried that some new data might be introduced into the db that might wreck my dumps again. So I wanted a way to compare the databases. To be honest if I'm off a couple of rows, that won't bother me too much. But if it's more than that I will need to investigate. Mike Michael, I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
I'm getting: 041014 08:55:53 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 041014 8:55:53 Can't start server: Bind on TCP/IP port: Address already in use 041014 8:55:53 Do you already have another mysqld server running on port: 3306 ? 041014 8:55:53 Aborting 041014 8:55:53 /usr/sbin/mysqld: Shutdown Complete 041014 08:55:53 mysqld ended == How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. --Marc On Thu, 2004-10-14 at 09:43, Victor Pendleton wrote: The error log should be located in your data directory if you have not specified another location. The name may be host.err. Marc wrote: Where is the error log? I'm searching for localhost.err, but nothing comes up. --Marc On Thu, 2004-10-14 at 08:00, Victor Pendleton wrote: What is written to ther error log? Marc wrote: I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
[snip] How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. [/snip] ps -aux from the command line -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
On unix? netstat -l(as root -- that's lower case L) Marc wrote: == How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. --Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
actually use netstat -l -p That way you get the process id then you can ps -eaf | grep (PID) to see what is listening. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
They do not... But I think I will just re-write my script in php command line and loop through it. It was just that I loved the simplicity of the bash script. [EMAIL PROTECTED] wrote: Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi, a problem about selecting...
I've a table and many fields. I want to search something in all fields. - select * from table where table.* like '%something%' did not work.. Is there a wayto dothis? Gokce AKKAYA HAZAROGLU OptikNet Bilgisayar [EMAIL PROTECTED] http://www.optiknet.com.tr
Re: SELECT and LIMIT question
Works great. Is the a limit on the size of the query. There are currently 50 queries (may go upto 300 in future) and each has 15 cols (250b each query) which makes the entire query 12kb (250 x 50). Thanks, Jay --- Michael Stassen [EMAIL PROTECTED] wrote: Right. (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 3 ORDER BY col3 DESC LIMIT 5) ORDER BY col3 DESC; should work. Notice that you can reorder the results after the UNIONs, if you want, with a final ORDER BY clause. UNION is available in MySQL 4.0.0 and up. Michael [EMAIL PROTECTED] wrote: I believe you need to combine the results of 3 separate queries (each with a limit of 5) into a temp table and respond with the contents of the table you built. If I read this correctly (http://dev.mysql.com/doc/mysql/en/UNION.html) you could do the same thing with a UNION query and skip the temp table step (MySQL does it for you). But you must have be using a version that supports UNION queries. Each piece-wise query must have it's own LIMIT constraint in order to apply the limit to each value you are querying on. Using either method you could even want 10 of #1 but only 5 of #2 and only 3 of #3 and still make it work. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
Thanks to all for the help. I did find a mysqld running using ps -aux and killed that. Looks like I'm all set. --Marc On Thu, 2004-10-14 at 11:33, Melanie wrote: I had this problem too: I identified the pid with ps -aux and then kill them manually. (kill -9 pid) hope it will help you. Marc wrote: I'm getting: 041014 08:55:53 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 041014 8:55:53 Can't start server: Bind on TCP/IP port: Address already in use 041014 8:55:53 Do you already have another mysqld server running on port: 3306 ? 041014 8:55:53 Aborting 041014 8:55:53 /usr/sbin/mysqld: Shutdown Complete 041014 08:55:53 mysqld ended == How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. --Marc On Thu, 2004-10-14 at 09:43, Victor Pendleton wrote: The error log should be located in your data directory if you have not specified another location. The name may be host.err. Marc wrote: Where is the error log? I'm searching for localhost.err, but nothing comes up. --Marc On Thu, 2004-10-14 at 08:00, Victor Pendleton wrote: What is written to ther error log? Marc wrote: I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
I had this problem too: I identified the pid with ps -aux and then kill them manually. (kill -9 pid) hope it will help you. Marc wrote: I'm getting: 041014 08:55:53 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 041014 8:55:53 Can't start server: Bind on TCP/IP port: Address already in use 041014 8:55:53 Do you already have another mysqld server running on port: 3306 ? 041014 8:55:53 Aborting 041014 8:55:53 /usr/sbin/mysqld: Shutdown Complete 041014 08:55:53 mysqld ended == How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. --Marc On Thu, 2004-10-14 at 09:43, Victor Pendleton wrote: The error log should be located in your data directory if you have not specified another location. The name may be host.err. Marc wrote: Where is the error log? I'm searching for localhost.err, but nothing comes up. --Marc On Thu, 2004-10-14 at 08:00, Victor Pendleton wrote: What is written to ther error log? Marc wrote: I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions question
I'm in the midst of writing out some code that will take data from a huge form and write it to the database. All the tables for insertions are innodb so i'm going (or trying too) with the one stop shop approach - transactions. Question: The first table I write too has as it's first column an auto-inc int. This recordID will serve as the recordID for all the other tables that will need insertions. Meaning the same RecordID must go into each other table. I need to get that recordID first before continuing along with the transaction. But, in my understanding (and I could be wrong) nothing gets written until the very end ? So how do I get the recordID into the other tables. Right now - i do the insert into the first table and that is fine. I imagine , haven't worked it out yet, but following that insertion I need to do a select statement and in the application layer assign that number to a variable. Anyway once again Im running my mouth , so can someone tell me if I'm right or wrong ? and if I'm neither how I can go about keeping the transaction as one step. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Well, here's a quick and dirty way to do it: #!/bin/sh mkdir /tmp/count chmod a+rwx /tmp/count cd /tmp/count mysqldump -T . production wc -l *.txt production rm *.sql *.txt mysqldump -T . standby wc -l *.txt standby diff production standby rm -rf /tmp/count Creates output like so: 20,21c20,21 3 users.txt 952 total --- 6 users.txt 955 total Eamon Daly - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:54 AM Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi, a problem about selecting...
Have a look at fulltext searches. This is described in the MySQL manual at http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html. If you put all of your columns in the column list following the FULLTEXT keyword, you will be able to use MATCH AGAINST expressions to find what you want. I'm assuming that all of the columns you are searching are character columns; I don't think you can put numeric or date/time columns in the FULLTEXT column list, although I have never tried it so I may be wrong. Rhino - Original Message - From: Gokce Hazaroglu To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 12:42 PM Subject: Hi, a problem about selecting... I've a table and many fields. I want to search something in all fields. - select * from table where table.* like '%something%' did not work.. Is there a way to do this? Gokce AKKAYA HAZAROGLU OptikNet Bilgisayar [EMAIL PROTECTED] http://www.optiknet.com.tr
How can I turn this into an update query?
Is there a way for me to change this select query into an update query? select t1.ID, t1.Title, t2.Title from products t1, systems t2 where t1.Title like CONCAT(t2.Title, %) A lot of products have the system title as the first part of the product title. I'm trying to update the product SystemID field and set it to the corresponding system ID. I'm sure there's a way to do this, but I'm not sure how. Any help is greatly appreciated. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost 1,000,000 rows. Not sure this is the most effecient way. But I do appreciate the effort! I think the idea of a php script that loops through show tables to get the table names and then does a Select COUNT(*) on each one and sums it up would probably be more effecient. Cheers, Mike Eamon Daly wrote: Well, here's a quick and dirty way to do it: #!/bin/sh mkdir /tmp/count chmod a+rwx /tmp/count cd /tmp/count mysqldump -T . production wc -l *.txt production rm *.sql *.txt mysqldump -T . standby wc -l *.txt standby diff production standby rm -rf /tmp/count Creates output like so: 20,21c20,21 3 users.txt 952 total --- 6 users.txt 955 total Eamon Daly - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:54 AM Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number of Rows in DB.
Would UNION help? Something like this: select count(ID) as Total from categories where ID 5 UNION select count(ID) as Total from products where ID 5 UNION select count(ID) as Total from systems where ID 5 Then you could just sum Total? -Original Message- They do not... But I think I will just re-write my script in php command line and loop through it. It was just that I loved the simplicity of the bash script. Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: keep field names unique across database?
[EMAIL PROTECTED] wrote: The second camp, as is described in the article, are more data-oriented. This practice was created in the original days of databases and programming design where ALL variables, including table names and field names, were global and needed to be absolutely unique. It's not just that. Part of this convention is that when you have foreign-key fields, you name them the same in both the defining table and the referring table. I.e. if you have a COMPANY table with CompanyID, CompanyName, etc., and an EMPLOYEE table, it can have a CompanyID that's now obviously (sic) related to COMPANY.CompanyID (you'd mark it as a FOREIGN KEY in EMPLOYEE). It also makes JOINs a little easier to write and read, because you'd be doing FROM COMPANY c, EMPLOYEE e WHERE ... AND c.CompanyID = e.CompanyID ... and so on - looking at the column names immediately gives you a clue about the key relationships.. It's a style thing that I've seen a lot of, and there's nothing wrong with this. As you say, it's data-oriented, but is really a naming-style issue. If you were following the object-oriented (quote-unquote!) style, the above query may be harder to read (assuming that you used names like just ID and Name in COMPANY): FROM COMPANY c, EMPLOYEE e WHERE ... AND c.ID = e.CompanyID -- 'uh, what's a c? Oh, yeah, a COMPANY.' ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions question
Sorry I found out about Last Insert_ID right after writing this. I guess the correct sequence is check manual then post to list ? Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm in the midst of writing out some code that will take data from a huge form and write it to the database. All the tables for insertions are innodb so i'm going (or trying too) with the one stop shop approach - transactions. Question: The first table I write too has as it's first column an auto-inc int. This recordID will serve as the recordID for all the other tables that will need insertions. Meaning the same RecordID must go into each other table. I need to get that recordID first before continuing along with the transaction. But, in my understanding (and I could be wrong) nothing gets written until the very end ? So how do I get the recordID into the other tables. Right now - i do the insert into the first table and that is fine. I imagine , haven't worked it out yet, but following that insertion I need to do a select statement and in the application layer assign that number to a variable. Anyway once again Im running my mouth , so can someone tell me if I'm right or wrong ? and if I'm neither how I can go about keeping the transaction as one step. Thank you, Stuart -- 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: Number of Rows in DB.
Pfft. Efficiency, schmefficiency. Here's a version in perl:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use MySQL::Config qw(parse_defaults);
die $0: specify exactly 2 arguments ($0 table_1 table_2)\n if @ARGV != 2;
my %mysql_cfg = parse_defaults my, ['client'];
my $user = $mysql_cfg{'user'} || $ENV{'USER'};
my $pass = $mysql_cfg{'password'};
my %tables;
foreach my $db (@ARGV) {
my $dbh = DBI-connect(DBI:mysql:database=$db, $user, $pass) || die;
for (@{ $dbh-selectall_arrayref('SHOW TABLE STATUS') }) {
$tables{$db}{$_-[0]} =
$dbh-selectrow_array(SELECT COUNT(1) FROM $_-[0]);
}
}
foreach my $table (sort keys %{ $tables{$ARGV[0]} }) {
printf(Table `%s` differs (%d rows in %s, %d rows in %s)\n,
$table,
$tables{$ARGV[0]}{$table}, $ARGV[0],
$tables{$ARGV[1]}{$table}, $ARGV[1])
if $tables{$ARGV[0]}{$table} != $tables{$ARGV[1]}{$table};
}
Run like so:
$ ./whee.pl production standby
Table `users` differs (6 rows in production, 8 rows in standby)
Eamon Daly
- Original Message -
From: Michael J. Pawlowsky [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 12:19 PM
Subject: Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost
1,000,000 rows.
Not sure this is the most effecient way. But I do appreciate the effort!
I think the idea of a php script that loops through show tables to get
the table names and then does a Select COUNT(*) on each one and sums
it up would probably be more effecient.
Cheers,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple but frustrating query
hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and LIMIT question
There is a HUGE limit on the size of the results of a query but I don't know exactly what yours will be. I do know it should be more than 2GB so I don't think your 4500 fields of data (300X15) will meet that limit. However, there is a practical limit on how many queries can be UNIONed into one result. One limit is due to the sheer length of the query (text size exceeds input buffer). Another may exist due to the actual # of SELECTS that need to be added together, but I can't recall hearing of that one specifically. Are you saying that you need to UNION the results of up to 300 different select statements? That's a lot of queries but depending on what you are using them for, I _can_ imagine a need for it. I would be interested in the practical reason for needing to perform such a large UNION, if you don't mind. Just curious, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jay K [EMAIL PROTECTED] wrote on 10/14/2004 12:43:50 PM: Works great. Is the a limit on the size of the query. There are currently 50 queries (may go upto 300 in future) and each has 15 cols (250b each query) which makes the entire query 12kb (250 x 50). Thanks, Jay --- Michael Stassen [EMAIL PROTECTED] wrote: Right. (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 DESC LIMIT 5) UNION (SELECT col1, col2, col3, col4 FROM table1, table2 WHERE table1.col1 = table2.col1 and table1.col2 = 3 ORDER BY col3 DESC LIMIT 5) ORDER BY col3 DESC; should work. Notice that you can reorder the results after the UNIONs, if you want, with a final ORDER BY clause. UNION is available in MySQL 4.0.0 and up. Michael [EMAIL PROTECTED] wrote: I believe you need to combine the results of 3 separate queries (each with a limit of 5) into a temp table and respond with the contents of the table you built. If I read this correctly (http://dev.mysql.com/doc/mysql/en/UNION.html) you could do the same thing with a UNION query and skip the temp table step (MySQL does it for you). But you must have be using a version that supports UNION queries. Each piece-wise query must have it's own LIMIT constraint in order to apply the limit to each value you are querying on. Using either method you could even want 10 of #1 but only 5 of #2 and only 3 of #3 and still make it work. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail
RE: simple but frustrating query
-Original Message- what we want is the value for the name field corresponding to the row with the most recent close_date. Based on that comment, I'd select name from TD order by close_date DESC limit 1 something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
in the query.
for example
select max(close_date), symbol, name from TD where symbol in
('aa','bb','cc','dd','ee')
in fact this is the real problem. for a single value of symbol, we can
do this query. but we want to feed in a list of values for symbol
something like this:
select max(close_date), symbol, name from TD where symbol in
(list of
characters) group by symbol;
this returns the max close_date value, but the name that is
returned is
garbage and seems to point to the earliest row in the table.
any suggestions?
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding DSN into Coldfusion Admin?
I am new to using MySql. I installed it on a seperate server on Win2003. That server has a 192.168.x.x address on my network. I am trying to add a DSN connection from Coldfusion to the database, but every time I try using the MySql driver/connection type, it says there doesn't seem to be a server running at 192.168.x.x:3306 What am I doing wrong? How can I check remotely to see if the server is actually running? Thanks very much!! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
How about select close_date, symbol, name from TD where symbol in (list of characters) limit 1 ORDER BY close_date desc Jeff Mathis [EMAIL PROTECTED] wrote: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in ( characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.joeaudette.com http://www.mojoportal.com
Re: simple but frustrating query
I'll be more explicit:
select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol, name order by symbol;
returns
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2002-05-03 | bb | drugs |
| 2002-02-05 | bb | medprovr|
| 2004-10-05 | cc | biotech |
| 2002-05-03 | cc | drugs |
| 2002-02-05 | cc | infosvcs|
+-++-+
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs|
+-++-+
which is wrong. what we want is
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
but we can't seem to fomrulate the query.
Jeff Mathis wrote:
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
in the query.
for example
select max(close_date), symbol, name from TD where symbol in
('aa','bb','cc','dd','ee')
in fact this is the real problem. for a single value of symbol, we can
do this query. but we want to feed in a list of values for symbol
something like this:
select max(close_date), symbol, name from TD where symbol in (list
of characters) group by symbol;
this returns the max close_date value, but the name that is returned
is garbage and seems to point to the earliest row in the table.
any suggestions?
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
telnet to it. MySQL will return a 'hello' packet. -Eric On Thu, 14 Oct 2004 12:37:08 -0600, Steve Grosz [EMAIL PROTECTED] wrote: I am new to using MySql. I installed it on a seperate server on Win2003. That server has a 192.168.x.x address on my network. I am trying to add a DSN connection from Coldfusion to the database, but every time I try using the MySql driver/connection type, it says there doesn't seem to be a server running at 192.168.x.x:3306 What am I doing wrong? How can I check remotely to see if the server is actually running? Thanks very much!! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
So what, a telnet 192.168.x.x:3306 ? Eric Bergen wrote: telnet to it. MySQL will return a 'hello' packet. -Eric On Thu, 14 Oct 2004 12:37:08 -0600, Steve Grosz [EMAIL PROTECTED] wrote: I am new to using MySql. I installed it on a seperate server on Win2003. That server has a 192.168.x.x address on my network. I am trying to add a DSN connection from Coldfusion to the database, but every time I try using the MySql driver/connection type, it says there doesn't seem to be a server running at 192.168.x.x:3306 What am I doing wrong? How can I check remotely to see if the server is actually running? Thanks very much!! Steve -- 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: Adding DSN into Coldfusion Admin?
If that's the case, its not working, I type a telnet 192.168.x.x:3306 and get a could not open a connection to the host even though Mysql-nt shows as running on the Win2003 server. Steve Eric Bergen wrote: telnet to it. MySQL will return a 'hello' packet. -Eric On Thu, 14 Oct 2004 12:37:08 -0600, Steve Grosz [EMAIL PROTECTED] wrote: I am new to using MySql. I installed it on a seperate server on Win2003. That server has a 192.168.x.x address on my network. I am trying to add a DSN connection from Coldfusion to the database, but every time I try using the MySql driver/connection type, it says there doesn't seem to be a server running at 192.168.x.x:3306 What am I doing wrong? How can I check remotely to see if the server is actually running? Thanks very much!! Steve -- 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: simple but frustrating query
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
.
OK I see what you want, this should do it
select close_date, symbol, name from TD
where symbol in ('aa','bb','cc') AND close_date = (SELECT max(close_date) FROM TD)
Jeff Mathis [EMAIL PROTECTED] wrote:
I'll be more explicit:
select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol, name order by symbol;
returns
+-++-+
| max(close_date) | symbol | name |
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2002-05-03 | bb | drugs |
| 2002-02-05 | bb | medprovr |
| 2004-10-05 | cc | biotech |
| 2002-05-03 | cc | drugs |
| 2002-02-05 | cc | infosvcs |
+-++-+
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name |
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs |
+-++-+
which is wrong. what we want is
+-++-+
| max(close_date) | symbol | name |
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
but we can't seem to fomrulate the query.
Jeff Mathis wrote:
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
in the query.
for example
select max(close_date), symbol, name from TD where symbol in
('aa','bb','cc','dd','ee')
in fact this is the real problem. for a single value of symbol, we can
do this query. but we want to feed in a list of values for symbol
something like this:
select max(close_date), symbol, name from TD where symbol in (
of characters) group by symbol;
this returns the max close_date value, but the name that is returned
is garbage and seems to point to the earliest row in the table.
any suggestions?
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
Re: Number of Rows in DB.
SQL: create TEMPORARY table A select count(1) as C from TABLE1; insert into A select count(1) as C from TABLE2; . select sum(C) from a; drop table A; Or create TEMPORARY table A (C integer); insert into A select count(1) from TABLE1 union all select count(1) from TABLE2 ... union all select count(1) from TABLEn; select sum(C) as count from A; drop table A; Santino At 6:54 -0400 14-10-2004, Michael J. Pawlowsky wrote: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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]
Converting date in MySQL
Hi, I am taking user input via a calendar widget (guess it's js) Apparently, mysql does not like the format MM/DD/ Then again I tried it around , still no dice. It's intended to go into a Date column. Is there a way I can correct it right within my query ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
On Thursday 14 October 2004 02:45 pm, Stuart Felenstein wrote: Apparently, mysql does not like the format MM/DD/ Then again I tried it around , still no dice. It's intended to go into a Date column. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html All, you every need to know if right there.. Jeff pgpS5L2p8iK4l.pgp Description: PGP signature
Re: Converting date in MySQL
Thanks , I know the page and have the links bookmarked! Stuart --- Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 14 October 2004 02:45 pm, Stuart Felenstein wrote: Apparently, mysql does not like the format MM/DD/ Then again I tried it around , still no dice. It's intended to go into a Date column. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html All, you every need to know if right there.. Jeff ATTACHMENT part 2 application/pgp-signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
On Thursday 14 October 2004 03:12 pm, Stuart Felenstein wrote: Thanks , I know the page and have the links bookmarked! So you got the answer from it right? Jeff pgpSsmcBOJscM.pgp Description: PGP signature
Re: simple but frustrating query
we really don't want to issue two queries. this should be able to be done in one, and without using temp tables, but maybe not. thanks for the help though jeff [EMAIL PROTECTED] wrote: It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD X-Mozilla-Status: 8000 X-Mozilla-Status2: WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts X-Mozilla-Status: 8000 X-Mozilla-Status2: INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
No :),. cause it seems that those formats are for outbound, db -. I was looking for the other direction. Stuart --- Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 14 October 2004 03:12 pm, Stuart Felenstein wrote: Thanks , I know the page and have the links bookmarked! So you got the answer from it right? Jeff ATTACHMENT part 2 application/pgp-signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
On Thursday 14 October 2004 03:35 pm, Stuart Felenstein wrote: No :),. cause it seems that those formats are for outbound, db -. I was looking for the other direction. Huh? It really doesnt matter does it? They work either way.. I use those functions all the time for inbound.. Jeff pgpssSczCg1j2.pgp Description: PGP signature
Re: Converting date in MySQL
For values headed into a SQL statement, use whatever functions are available to you in the language (PHP, PERL, Python, Java, VB Script,...) you are using to accept the user's input in order to make the commands you send MySQL correct. You just need to convert the date into -MM-DD hh:nn:ss format and MySQL will be as happy as a clam. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 10/14/2004 04:35:21 PM: No :),. cause it seems that those formats are for outbound, db -. I was looking for the other direction. Stuart --- Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 14 October 2004 03:12 pm, Stuart Felenstein wrote: Thanks , I know the page and have the links bookmarked! So you got the answer from it right? Jeff ATTACHMENT part 2 application/pgp-signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
On Thursday 14 October 2004 03:46 pm, [EMAIL PROTECTED] wrote: For values headed into a SQL statement, use whatever functions are available to you in the language (PHP, PERL, Python, Java, VB Script,...) you are using to accept the user's input in order to make the commands you send MySQL correct. You just need to convert the date into -MM-DD hh:nn:ss format and MySQL will be as happy as a clam. That, or str_to_date will work nicely for what he is looking for.. Which was my point.. Jeff pgpXlFpQANUPu.pgp Description: PGP signature
RE: Converting date in MySQL
From: Jeff Smelser [mailto:[EMAIL PROTECTED]
On Thursday 14 October 2004 03:35 pm, Stuart Felenstein wrote:
No :),. cause it seems that those formats are for
outbound, db -.
I was looking for the other direction.
Huh? It really doesnt matter does it? They work either way..
I use those functions all the time for inbound..
It does matter, though. You can't use DATE_FORMAT() to translate
'10/14/2004' into '2004-10-14.'
It looks like what the poster wants is STR_TO_DATE() (a la
STR_TO_DATE('10/14/2004', '%m/%d/%Y'), but that's not available until
MySQL 4.1.1.
Out of curiosity, how /would/ you do this? I'm assuming you're not using
STR_TO_DATE() (as I didn't even know it existed until I just checked),
though I may be incorrect.
As the last poster said, if you're not using 4.1.1, you're better off
setting the format in the calling script. You'd assumedly need to do
some error-checking, anyway.
--
Mike Johnson Smarter Living, Inc.
Web Developerwww.smarterliving.com
[EMAIL PROTECTED] (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
On Thursday 14 October 2004 04:00 pm, Mike Johnson wrote:
It does matter, though. You can't use DATE_FORMAT() to translate
'10/14/2004' into '2004-10-14.'
No, your right, that would be wrong.
It looks like what the poster wants is STR_TO_DATE() (a la
STR_TO_DATE('10/14/2004', '%m/%d/%Y'), but that's not available until
MySQL 4.1.1.
Yeah..
Out of curiosity, how /would/ you do this? I'm assuming you're not using
STR_TO_DATE() (as I didn't even know it existed until I just checked),
though I may be incorrect.
I did.. And its there.. but he didn't say that wasnt an option, just that he
didn't see anything think for inbound.. I was saying there was.. If he doesnt
have the version, its left to the client.. which he didn't specify.
My answers can only be as good as the questions.. He left a lot open..
Jeff
pgpowCDfiCISX.pgp
Description: PGP signature
Delete data from more than one table
I can delete data for one table with no problem: delete from table; I need to delete data for more than one table. I tried to run this query: delete from account, survey; //But the query crashes. Any ideas how to delete data for more than one query. TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete data from more than one table
if your schema can be set up to use pk/fk constraints, you can add an on delete cascade statement. if not, then its maybe safer to delete table by table anyway. Jerry Swanson wrote: I can delete data for one table with no problem: delete from table; I need to delete data for more than one table. I tried to run this query: delete from account, survey; //But the query crashes. Any ideas how to delete data for more than one query. TH -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
Jeff Mathis writes:
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs|
+-++-+
Jeff,
What version of MySQL are you using? I ran your query on
4.0.18 and got a different answer...
mysql select max(close_date), symbol, name from TD
- where symbol in ('aa','bb','cc') group by symbol;
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
3 rows in set (0.01 sec)
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
No, Shawn's answer is correct. You are starting from a false assumption. You are expecting that MAX(closedate) corresponds to a row. It does not. Consider the table Table=stuff: sym val note --- --- AAA 2 one AAA 2 two AAA 4 three AAA 6 four AAA 12 five AAA 7 six AAA 12 seven BBB 1 eight BBB 2 nine BBB 3 ten Now consider the query SELECT sym, note, MAX(val), MIN(val), AVG(val) FROM stuff GROUP BY sym; Which row should be returned for sym='AAA'? Do you see the problem? It is clear that, for sym=AAA, MIN(val) is 2, MAX(val) is 12, and AVG(val) is about 6.43. Which row is that? The answer is that it's not a row. MAX(), MIN(), and AVG() are aggregate functions. They do not return rows, they return summary stats about a set of rows. Many dbs wouldn't even allow a query like that, because we are asking for a column not included in the GROUP BY. Mysql allows that, but the manual warns that it is pointless to do so if the extra column does not have a unique value per group http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html. Finding the notes which correspond to the maximum val is fundamentally a 2 step process. First you must find the maximum val, then you must find the rows(s) which have that val. This is what Shawn was telling you. The manual suggests 3 ways to solve this problem http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. The most efficient solution, and the one that works in all versions of mysql, is to use a temporary table, as Shawn described. As you have mysql 4.1, you could accomplish the same thing with a subquery. In your case, that would be SELECT close_date, symbol, name FROM TD t1 WHERE close_date = (SELECT MAX(t2.close_date) FROM TD t2 WHERE t1.symbol = t2.symbol) AND symbol IN (list of characters); Note that this is still really a 2 step process. The subquery handles the first step, finding the max close_date, while the parent query handles step 2, finding the matching rows. There is a third way, the MAX-CONCAT trick. It does it in one query without subqueries, and is very inefficient. See the manual for the details. In other words, this wasn't such a simple query, after all. Michael Jeff Mathis wrote: we really don't want to issue two queries. this should be able to be done in one, and without using temp tables, but maybe not. thanks for the help though jeff [EMAIL PROTECTED] wrote: It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
4.1.3 and the innodb engine on solaris 5.8
Brad Eacker wrote:
Jeff Mathis writes:
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs|
+-++-+
Jeff,
What version of MySQL are you using? I ran your query on
4.0.18 and got a different answer...
mysql select max(close_date), symbol, name from TD
- where symbol in ('aa','bb','cc') group by symbol;
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
3 rows in set (0.01 sec)
Brad Eacker ([EMAIL PROTECTED])
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique key violations
Hi all... I am having a problem with unique key violations in one of my tables. This is the table structure: CREATE TABLE `optionaldata` ( `ForeignID` int(10) unsigned NOT NULL default '0', `FieldID` int(10) unsigned NOT NULL default '0', `Value` char(200) default NULL, UNIQUE KEY `CitizenID_FieldID_Value` (`ForeignID`,`FieldID`,`Value`), ) TYPE=MyISAM; I am getting quite a few rows in the table that are duplicates, ie: 1068715, 60, '[EMAIL PROTECTED]' 1068715, 60, '[EMAIL PROTECTED]' What I want to know is, should this be possible under any circumstances at all? I am using some UPDATE IGNORE calls to this table, but from what I read in the documentation, this still shouldn't happen. Any ideas? Many thanks in advance, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
well, obviously some, if not all, of what you are saying is true. your table example below though is not the same as mine. My table stores time series data. for every symbol, there are a series of rows all with different dates. there is a unique constraint on the combination of symbol and close_date. so, for every symbol, there is one and only one maximum date. i want that row and the name field it contains. your example using the subsquery works. when we used the subquery approach, we forgot to include the equivalent of t1.symbol = t2.symbol. if we use: select max(close_date), symbol, name from TD where symbol in (quoted char string) group by symbol, name order by symbol; we end up getting multiple rows for each symbol if the names change over time. but thats ok for now -- we can parse the query output within our application and get the one row with the most recent date. what we want to get is conceptually simple, but perhaps not so in terms of SQL. jeff Michael Stassen wrote: No, Shawn's answer is correct. You are starting from a false assumption. You are expecting that MAX(closedate) corresponds to a row. It does not. Consider the table Table=stuff: sym val note --- --- AAA 2 one AAA 2 two AAA 4 three AAA 6 four AAA 12 five AAA 7 six AAA 12 seven BBB 1 eight BBB 2 nine BBB 3 ten Now consider the query SELECT sym, note, MAX(val), MIN(val), AVG(val) FROM stuff GROUP BY sym; Which row should be returned for sym='AAA'? Do you see the problem? It is clear that, for sym=AAA, MIN(val) is 2, MAX(val) is 12, and AVG(val) is about 6.43. Which row is that? The answer is that it's not a row. MAX(), MIN(), and AVG() are aggregate functions. They do not return rows, they return summary stats about a set of rows. Many dbs wouldn't even allow a query like that, because we are asking for a column not included in the GROUP BY. Mysql allows that, but the manual warns that it is pointless to do so if the extra column does not have a unique value per group http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html. Finding the notes which correspond to the maximum val is fundamentally a 2 step process. First you must find the maximum val, then you must find the rows(s) which have that val. This is what Shawn was telling you. The manual suggests 3 ways to solve this problem http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. The most efficient solution, and the one that works in all versions of mysql, is to use a temporary table, as Shawn described. As you have mysql 4.1, you could accomplish the same thing with a subquery. In your case, that would be SELECT close_date, symbol, name FROM TD t1 WHERE close_date = (SELECT MAX(t2.close_date) FROM TD t2 WHERE t1.symbol = t2.symbol) AND symbol IN (list of characters); Note that this is still really a 2 step process. The subquery handles the first step, finding the max close_date, while the parent query handles step 2, finding the matching rows. There is a third way, the MAX-CONCAT trick. It does it in one query without subqueries, and is very inefficient. See the manual for the details. In other words, this wasn't such a simple query, after all. Michael Jeff Mathis wrote: we really don't want to issue two queries. this should be able to be done in one, and without using temp tables, but maybe not. thanks for the help though jeff [EMAIL PROTECTED] wrote: It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL
MySQL 4.1.5 upgrade going badly--mysqld won't start
I'm on a Win32 server, but I'm hoping from the error log contents that the question isn't Windows-specific. I'm having a Very Bad Day trying to upgrade a MySQL server from 4.0.18 to 4.1.5. After several problems resolved, I'm almost there--got the new engine to recognize the old dbs, etc. But now, after what I thought would be an ordinary removal/reinstall of the daemon (most of the problems related to the new daemon picking up defaults from an install into another location), the Win service won't start. Here's the relevant section of the error log: InnoDB: Error: log file .\ib_logfile0 is of different size 0 19922944 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 041014 18:20:37 [ERROR] Can't init databases 041014 18:20:37 [ERROR] Aborting 041014 18:20:37 [NOTE] MySQL: Shutdown complete (Trying to start the service from Windows gives Error 1037) Can I edit the cnf file? Clear the InnoDB logfile? I'm getting pretty desperate--site's been down a long time. Any help much appreciated. --Chris
RE: MySQL 4.1.5 upgrade going badly--mysqld won't start
Hi Chris, The appropriate sections of the my.cnf (or my.ini on windows) file to edit are # # Set the innodb engine parameters # innodb_data_home_dir = /u01/app/dmc/db/dmcpdb innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /u01/app/dmc/db/dmcpdb innodb_log_arch_dir = /u01/app/dmc/db/dmcpdb innodb_buffer_pool_size = 32M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 As you can see, the log file size is probably not the same as you had in the previous my.cnf. Your previous my.cnf must have been overwritten along with all the appropriate parameters. To get past the error message you are seeing, either change the innodb_log_file_size parameter or delete the log file and let innodb create it itself. All of this is described in the manual in chapter 16, the paragraph you need is probably 16.4 HTH and you get things going soon. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Christopher J. Mackie [mailto:[EMAIL PROTECTED] Sent: Friday, 15 October 2004 8:11 AM To: [EMAIL PROTECTED] Subject: MySQL 4.1.5 upgrade going badly--mysqld won't start I'm on a Win32 server, but I'm hoping from the error log contents that the question isn't Windows-specific. I'm having a Very Bad Day trying to upgrade a MySQL server from 4.0.18 to 4.1.5. After several problems resolved, I'm almost there--got the new engine to recognize the old dbs, etc. But now, after what I thought would be an ordinary removal/reinstall of the daemon (most of the problems related to the new daemon picking up defaults from an install into another location), the Win service won't start. Here's the relevant section of the error log: InnoDB: Error: log file .\ib_logfile0 is of different size 0 19922944 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 041014 18:20:37 [ERROR] Can't init databases 041014 18:20:37 [ERROR] Aborting 041014 18:20:37 [NOTE] MySQL: Shutdown complete (Trying to start the service from Windows gives Error 1037) Can I edit the cnf file? Clear the InnoDB logfile? I'm getting pretty desperate--site's been down a long time. Any help much appreciated. --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.5 upgrade going badly--mysqld won't start
David; Thanks, it did work. I thought it might--just wanted to be sure that deleting the logfile or editing the .cnf file wouldn't make things worse somehow. And thanks too for the gentle direction to the manual; I have been digging around for the last several hours, but mostly under the 'Windows' sections. Now that I'm once again a man of leisure, I'll try to commit the relevant sections to mind. Perhaps there should be a handy abbreviation: RTRPOTFM, for Read The Right Part Of TFM :-) Huge relief to have this working again: I can't thank you enough for the quick response. Best, --Chris -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 6:54 PM To: Christopher J. Mackie; [EMAIL PROTECTED] Subject: RE: MySQL 4.1.5 upgrade going badly--mysqld won't start Hi Chris, The appropriate sections of the my.cnf (or my.ini on windows) file to edit are # # Set the innodb engine parameters # innodb_data_home_dir = /u01/app/dmc/db/dmcpdb innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /u01/app/dmc/db/dmcpdb innodb_log_arch_dir = /u01/app/dmc/db/dmcpdb innodb_buffer_pool_size = 32M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 As you can see, the log file size is probably not the same as you had in the previous my.cnf. Your previous my.cnf must have been overwritten along with all the appropriate parameters. To get past the error message you are seeing, either change the innodb_log_file_size parameter or delete the log file and let innodb create it itself. All of this is described in the manual in chapter 16, the paragraph you need is probably 16.4 HTH and you get things going soon. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Christopher J. Mackie [mailto:[EMAIL PROTECTED] Sent: Friday, 15 October 2004 8:11 AM To: [EMAIL PROTECTED] Subject: MySQL 4.1.5 upgrade going badly--mysqld won't start I'm on a Win32 server, but I'm hoping from the error log contents that the question isn't Windows-specific. I'm having a Very Bad Day trying to upgrade a MySQL server from 4.0.18 to 4.1.5. After several problems resolved, I'm almost there--got the new engine to recognize the old dbs, etc. But now, after what I thought would be an ordinary removal/reinstall of the daemon (most of the problems related to the new daemon picking up defaults from an install into another location), the Win service won't start. Here's the relevant section of the error log: InnoDB: Error: log file .\ib_logfile0 is of different size 0 19922944 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 041014 18:20:37 [ERROR] Can't init databases 041014 18:20:37 [ERROR] Aborting 041014 18:20:37 [NOTE] MySQL: Shutdown complete (Trying to start the service from Windows gives Error 1037) Can I edit the cnf file? Clear the InnoDB logfile? I'm getting pretty desperate--site's been down a long time. Any help much appreciated. --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to Start MySQL on FreeBSD4.10 box
Hi! I'm setting up a development box with FreeBSD 4.10 and installed MySQL 4.0.12 on it. Everything in the install appeared to work smoothly. However, when I go to start MySQL I get an error that says: database list could not be retrieved So, how do I fix this? I've been unable to find any documentation that sheds any light on what I need to change. Thanks for any help. Lynette
Re: Adding DSN into Coldfusion Admin?
I have tried telnet web-server2:3306 Didn't work Dwalu Z. Khasu wrote: On Thu, 14 Oct 2004, Steve Grosz wrote: =If that's the case, its not working, I type a telnet 192.168.x.x:3306 =and get a could not open a connection to the host even though =Mysql-nt shows as running on the Win2003 server. = =Steve = Try 'telnet host port'. - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Adding DSN into Coldfusion Admin?
Hi Steve, You haven't got a firewall running have you? Is the MS Internet Connection firewall switched on? Sounds like some sort of firewall issue though. Generally you won't get a response from the server if it is protected. Just a thought, I'm more of a unix bod but have seen this on my own PC. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Steve Grosz [mailto:[EMAIL PROTECTED] Sent: Friday, 15 October 2004 9:29 AM To: [EMAIL PROTECTED] Subject: Re: Adding DSN into Coldfusion Admin? I have tried telnet web-server2:3306 Didn't work Dwalu Z. Khasu wrote: On Thu, 14 Oct 2004, Steve Grosz wrote: =If that's the case, its not working, I type a telnet 192.168.x.x:3306 =and get a could not open a connection to the host even though =Mysql-nt shows as running on the Win2003 server. = =Steve = Try 'telnet host port'. - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- 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]
SP with PHP
HyaFirst of all, sorry for my EnglishI have the latest version of PHP, MySQL and Apache. I tried to look for how to call a stored procedure from a PHP script.?php mysql_connect(HOST,USER,PASS); mysql_select_db(DB); $result = mysql_query( ? );...Is it works like this? Just call the SP as a single SQL instruction? I didnt get the idea... or I need to use another comand?I tried to look in the PHP and MySQL documentation... didnt find.Somebody help me, please!Thanks!PaulWalla! Mail - get your free 1G mail today -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
Did you tried without the : between web-server2 and 3306? i.e. put a space insted of : B On Thursday 14 October 2004 19:58, Steve Grosz wrote: I have tried telnet web-server2:3306 Didn't work Dwalu Z. Khasu wrote: On Thu, 14 Oct 2004, Steve Grosz wrote: =If that's the case, its not working, I type a telnet 192.168.x.x:3306 =and get a could not open a connection to the host even though =Mysql-nt shows as running on the Win2003 server. = =Steve = Try 'telnet host port'. - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tables and performance
Hello, I have 2 questions. Is there a maximum number of tables that can be created in a database? Does a very large number of tables in a database affect MySQL's performance? Thanks, -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
On Thu, 14 Oct 2004, Steve Grosz wrote: =I have tried telnet web-server2:3306 = =Didn't work = Neither the DOS nor Unix man pages for telnet mention a colon--Why do you think you need one? Let me try again, how about 'telnet web-server 3306'? ... ... =Dwalu Z. Khasu wrote: [snip] = = = Try 'telnet host port'. = -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Modify type
I have field date type of datetime. I need to modify to timestamp. If I alter the table and mofiy the field will this crash the data in the field. TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
On Thu, 14 Oct 2004, Dwalu Z. Khasu wrote: =Neither the DOS nor Unix man pages for telnet mention a colon--Why do you =think you need one? Let me try again, how about 'telnet web-server 3306'? = Sorry for the typo. For your example, that should've been: telnet web-server2 3306 Are you sure you're connecting to the appropriate box? Should your command string be telnet db-server 3306 instead? - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is 'start' a keyword?
I tried to create a table with a column named 'start'. This word does not appear here: http://dev.mysql.com/doc/mysql/en/Reserved_words.html But, my effort to create such a column yeilded a syntax error, and I did subsequently learn of a 'start' directive, such as in: START TRANSACTION, COMMIT, and ROLLBACK Syntax START SLAVE Syntax Is the URL I cited intended to be a canonical list of keywords I can't use for table/column names? -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
