fulltext search on words inside words
Hi there, I am wondering if it is possible to find words inside words with the help of fulltext search. For example: Search for: Antenne Schould also find Stabantenne Is this possible? Google does that, so somehow there should be a way. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Thanx, Merlin Oh.. I am running MySQL 4.0.14 on Suse Linux 9. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Heikki Tuuri wrote: Deva, please post the complete output except the transaction data. Regards, Heikki Please let me know if there is any additional data I can provide to help resolve this. = 051005 7:18:17 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 31277, signal count 29361 --Thread 2450451904 has waited at btr0cur.c line 340 for 800.00 seconds the semaphore: X-lock on RW-latch at 0x448286bc created in file dict0dict.c line 3593 a writer (thread id 2450451904) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 347 Last time write locked in file btr0cur.c line 340 Mutex spin waits 294649, rounds 709548, OS waits 19013 RW-shared spins 10614, OS waits 5357; RW-excl spins 14659, OS waits 6152 TRANSACTIONS Trx id counter 0 1856601574 Purge done for trx's n:o 0 1856601271 undo n:o 0 0 History list length 9 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: section cut FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: complete io for buf page (read thread) ev set I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 11, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 56937 OS file reads, 79359 OS file writes, 13851 OS fsyncs 49.00 reads/s, 30490 avg bytes/read, 96.68 writes/s, 18.50 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 30091, free list len 19083, seg size 49175, is not empty Ibuf for space 0: size 30091, free list len 19083, seg size 49175, 24395 inserts, 318393 merged recs, 60206 merges Hash table size 4425293, used cells 3168, node heap has 4 buffer(s) 0.00 hash searches/s, 281.36 non-hash searches/s --- LOG --- Log sequence number 115 1818376139 Log flushed up to 115 1818375712 Last checkpoint at 115 1818375695 0 pending log writes, 0 pending chkp writes 7992 log i/o's done, 10.69 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1303861436; in additional pool allocated 12728448 Buffer pool size 65536 Free buffers 0 Database pages 65532 Modified db pages 14 Pending reads 22 Pending writes: LRU 0, flush list 0, single page 0 Pages read 91019, created 152, written 113129 91.49 reads/s, 0.00 creates/s, 135.55 writes/s Buffer pool hit rate 944 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 288 queries in queue Main thread process no. 1792, id 2434898496, state: doing insert buffer merge Number of rows inserted 26399, updated 7272, deleted 0, read 851771 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
2005/10/6, Ed Reed [EMAIL PROTECTED]: I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8|5 | NULL | +++--+ | 8|4 | a | +++--+ | 8|6 | NULL | +++--+ | 8|6 | a | +++--+ | 8|5 | a | +++--+ | 8|4 | b | +++--+ | 9|1 | NULL | +++--+ | 9|2 | NULL | +++--+ | 9|1 | a | +++--+ | 9|3 | NULL | +++--+ | 9|2 | a | +++--+ | 9|1 | b | +++--+ | 9|4 | NULL | +++--+ | 9|4 | a | +++--+ | 9|2 | b | +++--+ | 9|1 | c | +++--+ | 10 |1 | NULL | +++--+ | 10 |1 | a | +++--+ | 10 |2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b| +---+ | 8-5a| +---+ | 8-6a| +---+ | 9-1c| +---+ | 9-2b| +---+ | 9-3 | +---+ | 9-4a| +---+ | 10-1a | +---+ | 10-2| +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Financial return calculations help please
On Wed, Oct 05, 2005 at 11:23:00AM -0700, Mike Wexler wrote: Jim Seymour wrote: I have researched repeatedly and cannot find an answer to the following. I need to do something like the following (There is probably an easier way). end_date - start_date = diff / start_date = return for period The table contains 401k investment values. Ideas, pointers, etc.? I am using mysql v5.0.12 on Debian Linux. TIA, Jim Depending on how fancy you want to get, you would calculate either an IRR (Internal Rate of Return) that basically says, what interest rate would I need to get on the funds to end up with the same results I actually achived. Note that calculating an interest rate is going to require an iterative approximation. Thanks Mike, What I was really looking for is how to structure a query in mysql to arrive at the return on the investment. I know I completely left that out of my first post. Is it even possible? Thanks, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
Incoming from s. keeling: Incoming from Pooly: 2005/10/3, s. keeling [EMAIL PROTECTED]: I'd like to add a bit of history data to a table (who changed a record last, and when it was last changed). Is this the way to do it? [snip] alter table MEMBERS alter CHG_DATE set default CURRENT_DATE btw, you could do : Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 -- Any technology distinguishable from magic is insufficiently advanced. (*)http://www.spots.ab.ca/~keeling Please don't Cc: me. - - For the ChiComms: democracy human rights Taiwan Independence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
s. keeling wrote: Incoming from s. keeling: Incoming from Pooly: 2005/10/3, s. keeling [EMAIL PROTECTED]: I'd like to add a bit of history data to a table (who changed a record last, and when it was last changed). Is this the way to do it? [snip] alter table MEMBERS alter CHG_DATE set default CURRENT_DATE btw, you could do : Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 http://dev.mysql.com/doc/mysql/en/alter-table.html -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
Incoming from Arno Coetzee: s. keeling wrote: Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 http://dev.mysql.com/doc/mysql/en/alter-table.html Yes, I've read it. Your point? Specifically? I am encoutering error 1064, surrounding table names and column names with backticks solves nothing. Is there something _specific_ on that page I'm missing? Thanks for trying. -- Any technology distinguishable from magic is insufficiently advanced. (*)http://www.spots.ab.ca/~keeling Please don't Cc: me. - - For the ChiComms: democracy human rights Taiwan Independence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
On 06/10/2005, s. keeling wrote: Incoming from Arno Coetzee: s. keeling wrote: alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. http://dev.mysql.com/doc/mysql/en/alter-table.html Yes, I've read it. Your point? Specifically? If I read the manual (refered to above) correctly, the only thing you can put after alter table MEMBERS alter MEMBER_INFO is either SET DEFAULT literal or DROP DEFAULT. I think you have to use ALTER TABLE MEMBERS CHANGE MEMBER_INFO MEMBER_INFO VARCHAR(160) ...; with ... optionally NOT NULL and/or DEFAULT 'whatever' -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search on words inside words
Merlin wrote: I am wondering if it is possible to find words inside words with the help of fulltext search. Is this possible? Google does that, so somehow there should be a way. Somehow I don't think that Google runs on a single MySQL database. Full text indexes in MySQL mean that words (MySQL's definition of a word that is) are indexed, not parts of words. Google's purpose is to provide a searchable index, so they have built their own data structures for these features. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Fine tuning full-text search can be found at: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html The default stop words are at: http://dev.mysql.com/doc/mysql/en/fulltext-stopwords.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
s. keeling wrote: Incoming from Arno Coetzee: s. keeling wrote: Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 http://dev.mysql.com/doc/mysql/en/alter-table.html Yes, I've read it. Your point? Specifically? I am encoutering error 1064, surrounding table names and column names with backticks solves nothing. Is there something _specific_ on that page I'm missing? Thanks for trying. sorry ... bit busy on this side... had a quick look... give this a go... alter table MEMBERS MODIFY MEMBER_INFO varchar(160); hope this works -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi again, The query cache finally works after I've reverted to mm.mysql-2.0.12-bin.jar. The problem was that the query cache somehow wouldn't work within transactions with mysql-connector-java-3.1.10-bin.jar. Anyone care to dig further? Here is one working combination: Jboss 3.2.2 with transactions JDBC: mm.mysql-2.0.12-bin.jar (3.1.10 doesn't work for me) MySQL: 4.1.14 (Haven't tried 5.0) Extract from http://dev.mysql.com/doc/mysql/en/query-cache-how.html ' In MySQL 4.0, the query cache is disabled within transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache also works within transactions when using InnoDB tables (it uses the table version number to detect whether or not its contents are still current).' Regards, Al Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cursors in 5.0.13
On Wednesday 05 Oct 2005 15:29, Jeff Smelser wrote: On Wednesday 05 October 2005 04:05 am, Rob Hall wrote: Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? when loading a procedure, do show warnings after it.. It tells you what problems its running into.. So what error are you getting? None :-). No Warnings or Errors are returned. All I get is a count of 1 evertime when Distinct is used in the cursor select statement. Omit the DISTINCT and the count is 50511. Do a single select from the command line including the distinct and I get 28K+ entries returned. Looks to me like a bug in the cursor handling but I'd like someone to confirm this. -- Best regards, Rob Hall - Red Hat Certified Engineer Technical Team Leader Newsquest Digital Media -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: modifying duplicate unique keys with LOAD DATA INFILE
Dear Michael, thank you for your response. If a duplicate unique key is found when importing with LOAD DATA INFILE, How does that happen? I take it you are adding the imported data into an already populated table. True, that's what I meant to say. Don't alter the keys for the existing data! I see, I should not do that. Is it the case that the imported data is simply a set of new rows with no references to it? If so, there's no reason to preserve the old key for any of the imported rows. Unfortunately, this is not the case. The data in the key has a meaning, it represents a 64 bit µs timestamp. Is this bad design, would you not do something like this? This would be one solution: Use an AUTO_INCREMENT key for identification (I can afford those extra bytes, I just thought I wouldn't need it.), have the timestamp be non-unique and do as you described. If you already have the exported data and don't want to start over, you can probably accomplish the same thing with a temporary table. I don't have the data, I can freely change whatever has to be changed. In this case I shouldn't go for the temporary table, right? Still, thanks for your code, I learned from that, too. Best regards, Gerhard Prilmeier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Query Browser 1.1.15 BUG????
I download MySql Query Browser 1.1.15 and he is leaving me nervous... This is the problem: 1. I have 2 tabsheet result set. 2. I the first i make a query , like select * from table1. 3. In the second i am in the middle of a query, like select * from table1 where id = (What's is the id ??? Fuck, i forgot. Let me see the first tabsheet!!!) 4. I turn tho the first tabsheet e and i see that the id is 13, so, let me come back the the second tab sheet and finish the query... 5. WHAT??? where is my query??? DO you understand the problem??? OBS: - Sorry for my bad english - I deserve one IPod??
MySQL 5.0.13-rc: BUG in substring functions?
Hello All!
left(),right(),mid(),etc functions
+
with fixed point return value for function (or out parameter for sp)
---
result has been truncated
How to repeat:
mysql drop function if exists test;
mysql drop function if exists test1;
mysql delimiter //
mysql create function test () returns numeric(7,2) return left('77.247',5)//
mysql create function test1 () returns numeric(7,2) return '77.247'//
mysql delimiter ;
mysql select test(),test1();
++-+
| test() | test1() |
++-+
| 77.00 | 77.25 |
++-+
1 row in set (0.02 sec)
How to correct:
use global variable as intermediate storage (with side effect)
mysql drop function if exists test;
mysql delimiter //
mysql create function test () returns numeric(7,2) return
@z:=left('77.247',5)//
mysql delimiter ;
mysql select test(),test1();
++-+
| test() | test1() |
++-+
| 77.24 | 77.25 |
++-+
1 row in set (0.00 sec)
Is it a known bug?
WBR, Juri.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mac OS X / MySQL 4.1.14 compiled slowness issue
We are having slowness issues with MySQL on Mac OS X 10.4. Here is what has been done...1) Tiger's native version of mysql was installed.2) A delete was attempted on 1.5 million records Note: These records are heavily referenced to other tables3) 8+ hours later the delete was cancelled and it took 8+ hours torecover4) Same delete was done on a Sempron 2600+ XP with only 512M RAM. Took26 minutes.5) Version 4.1.14 of mysql was downloaded and isntalled by you viapackage manager. Same delete, same result (waited 10 hours this timethough).6) Version 4.0.26 was downloaded and compiled from source AFTER a fullreinstall of the OS (reinstall was for reasons not related to the DBproblem)7) Same delete, same result.Please help as we have no idea what's going on. Regards...Jim C. Knotts | Lead Systems ArchitectJCK Associatese. [EMAIL PROTECTED]w. http://www.jckassoc.comaim: [EMAIL PROTECTED]Professional Profile
Detect if table exists from within MySQL?
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
Thanks,
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
Ryan,
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the table
doesn't exist.
PB
Ryan Stille wrote:
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
Thanks,
-Ryan
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.11/121 - Release Date: 10/6/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question about stored procedures
Hi all, I am wondering if I can construct a query string inside a stored procedure and then execute it. I mean, what I want to do is given a set of IN parameters, be able to construct a huge where statement out of them an then complete the query string to execute. Is that possible? because if that is possible I could be able to abstract all the data logic from the program itself. I've been googling and reading books and references without success so far, but I think the functionality should be there, what I dont know is how to use it. Thanks in advance. sql - query - mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Detect if table exists from within MySQL?
If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the table doesn't exist. This causes my application (ColdFusion) to throw an exception. If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question about stored procedures
Yes you can do this using prepared statements inside stored proc. Guess this helps. http://forums.mysql.com/read.php?98,22770,31459 sujay -Original Message- From: Leonardo Javier Belén [mailto:[EMAIL PROTECTED] Sent: Thursday, October 06, 2005 9:47 PM To: Ryan Stille; mysql@lists.mysql.com Subject: A question about stored procedures Hi all, I am wondering if I can construct a query string inside a stored procedure and then execute it. I mean, what I want to do is given a set of IN parameters, be able to construct a huge where statement out of them an then complete the query string to execute. Is that possible? because if that is possible I could be able to abstract all the data logic from the program itself. I've been googling and reading books and references without success so far, but I think the functionality should be there, what I dont know is how to use it. Thanks in advance. sql - query - mysql -- 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: alter table
Incoming from Arno Coetzee: sorry ... bit busy on this side... had a quick look... give this a go... alter table MEMBERS MODIFY MEMBER_INFO varchar(160); hope this works It did. Much appreciated. -- Any technology distinguishable from magic is insufficiently advanced. (*)http://www.spots.ab.ca/~keeling Please don't Cc: me. - - For the ChiComms: democracy human rights Taiwan Independence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
Ryan Stille wrote: If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. Maybe you could use SHOW TABLES LIKE 'your_table'; -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
On Thursday 06 October 2005 10:57 am, Ryan Stille wrote:
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
5.X has information_schema..
However...
4.1, you can show tables like '%tab%' and you will get a row back if exists..
WARNING, myisam will be quick, innodb will be really slow, specially if the
tables are big..
Jeff
pgphkfOW9sFUY.pgp
Description: PGP signature
RE: Detect if table exists from within MySQL?
Maybe you could use SHOW TABLES LIKE 'your_table'; That's a great idea, I just tried it in several ways, like: IF EXISTS (SHOW TABLES LIKE 'cfgbiz') THEN SELECT siacnotifyto FROM cfgbiz ELSE SELECT '' as siacnotifyto END IF; -and- select IF((SHOW TABLES LIKE 'cfgbiz'),notifyto,'') FROM cfgbiz; But it looks like the SHOW TABLES statement just doesn't return like a regular SELECT statement does, because the above works if I use it like this: select IF(1,notifyto,'') FROM cfgbiz; -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: compare tables from two systems
There is a product that will do the job. It is MYdbPAL (www.it-map.com) which is a free license to MySQL users. You can extract all or partial datasets from 2 databases, compare them and produce a 'differences' dataset. This can, be viewed, edited, and if needed be applied to the target database to update it and put it back in synch. Tim Hayes -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 05 October 2005 23:25 To: Claire Lee; mysql@lists.mysql.com Subject: Re: compare tables from two systems - Original Message - From: Claire Lee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- 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 SQL statement to copy a blob from one table to another
Hello everyone: I have two tables that store a blob. They both have Id fields. What I would like to do is, given an id, copy the blob stored on table one to table two. I could do that in my java program by selecting and retrieving the blob from table 1 and then issuing a insert/replace statement to table 2. But it is not elegant. Hence I wonder if anyone would have a better suggestion of something better. table1: id - integer file - blob table2: id - integer file - blob I want to insert the blob file from table1 where id = 1 to table2. To select the blob I would issue: select file from table1 where id = 1 To insert I would do: insert into table2 (blob) values(?) I am using MySQL 4.1.12. Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A SQL statement to copy a blob from one table to another
C.F. Scheidecker Antunes wrote: I want to insert the blob file from table1 where id = 1 to table2. To select the blob I would issue: select file from table1 where id = 1 To insert I would do: insert into table2 (blob) values(?) INSERT INTO table2 (blob) SELECT file FROM table1 WHERE id=1 -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Deva, please post those transactions which have been active more than 800 seconds. The thread holding the RW-latch at 0x448286bc should be among those. Regards, Heikki - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 06, 2005 10:14 AM Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout) Heikki Tuuri wrote: Deva, please post the complete output except the transaction data. Regards, Heikki Please let me know if there is any additional data I can provide to help resolve this. = 051005 7:18:17 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 31277, signal count 29361 --Thread 2450451904 has waited at btr0cur.c line 340 for 800.00 seconds the semaphore: X-lock on RW-latch at 0x448286bc created in file dict0dict.c line 3593 a writer (thread id 2450451904) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 347 Last time write locked in file btr0cur.c line 340 Mutex spin waits 294649, rounds 709548, OS waits 19013 RW-shared spins 10614, OS waits 5357; RW-excl spins 14659, OS waits 6152 TRANSACTIONS Trx id counter 0 1856601574 Purge done for trx's n:o 0 1856601271 undo n:o 0 0 History list length 9 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: section cut FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: complete io for buf page (read thread) ev set I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 11, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 56937 OS file reads, 79359 OS file writes, 13851 OS fsyncs 49.00 reads/s, 30490 avg bytes/read, 96.68 writes/s, 18.50 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 30091, free list len 19083, seg size 49175, is not empty Ibuf for space 0: size 30091, free list len 19083, seg size 49175, 24395 inserts, 318393 merged recs, 60206 merges Hash table size 4425293, used cells 3168, node heap has 4 buffer(s) 0.00 hash searches/s, 281.36 non-hash searches/s --- LOG --- Log sequence number 115 1818376139 Log flushed up to 115 1818375712 Last checkpoint at 115 1818375695 0 pending log writes, 0 pending chkp writes 7992 log i/o's done, 10.69 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1303861436; in additional pool allocated 12728448 Buffer pool size 65536 Free buffers 0 Database pages 65532 Modified db pages 14 Pending reads 22 Pending writes: LRU 0, flush list 0, single page 0 Pages read 91019, created 152, written 113129 91.49 reads/s, 0.00 creates/s, 135.55 writes/s Buffer pool hit rate 944 / 1000 -- ROW OPERATIONS -- 8 queries inside InnoDB, 288 queries in queue Main thread process no. 1792, id 2434898496, state: doing insert buffer merge Number of rows inserted 26399, updated 7272, deleted 0, read 851771 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT -- 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: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Heikki Tuuri wrote: Deva, please post those transactions which have been active more than 800 seconds. The thread holding the RW-latch at 0x448286bc should be among those. Regards, Heikki The thread holding that RW-latch is the first of these. I've removed any sensitive information but left as much of the query as possible. ---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread id 2450451904 inserting, thread declared inside InnoDB 160 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 341 MySQL thread id 148, query id 22800 192.168.1.36 webserver update INSERT INTO emails_history_30_30(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856601294, ACTIVE 802 sec, process no 1792, OS thread id 2445059520 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 167, query id 22813 192.168.1.52 webserver statistics SELECT * FROM companies WHERE company_id = Trx read view will not see trx with id = 0 1856601295, sees 0 1856587424 ---TRANSACTION 0 1856601293, ACTIVE 802 sec, process no 1792, OS thread id 2450255424 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 163, query id 22802 192.168.1.52 webserver statistics SELECT * FROM emails_9_19 WHERE Trx read view will not see trx with id = 0 1856601294, sees 0 1856587424 ---TRANSACTION 0 1856601280, ACTIVE 802 sec, process no 1792, OS thread id 2451766592 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 170, query id 22788 192.168.1.37 webserver Sending data SELECT * FROM mta_servers WHERE ip IN( Trx read view will not see trx with id = 0 1856601281, sees 0 1856587424 ---TRANSACTION 0 1856601279, ACTIVE 802 sec, process no 1792, OS thread id 2446827840 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 169, query id 22786 192.168.1.37 webserver Sending data SELECT * FROM mta_servers WHERE ip IN( Trx read view will not see trx with id = 0 1856601280, sees 0 1856587424 ---TRANSACTION 0 1856601274, ACTIVE 803 sec, process no 1792, OS thread id 2449862464 waiting in InnoDB queue mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 1002 MySQL thread id 127, query id 22756 192.168.1.231 webserver update INSERT INTO emails_history_30_6(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856601269, ACTIVE 803 sec, process no 1792, OS thread id 2450058944 waiting in InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 162, query id 22751 192.168.1.232 webserver Sending data SELECT e.email_id, e.email_address, e.status, e.first_name, e.last_name, e.gender, e.dob, e.state, e.zipcode, e.country, e.list_source_id, e.bounce_timestamp, e.openclick, e.last_bounce_code FROM email.emails_17_13 AS e LEFT JOIN suppression_lists.client_284 AS csl ON e.email_address = csl.email_add Trx read view will not see trx with id = 0 1856601270, sees 0 1856587424 ---TRANSACTION 0 1856588861, ACTIVE 852 sec, process no 1792, OS thread id 2446238400 inserting, thread declared inside InnoDB 267 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 234 MySQL thread id 36, query id 7077 192.168.1.38 webserver update INSERT INTO emails_history_30_19(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856588853, ACTIVE 854 sec, process no 1792, OS thread id 2446434880 inserting, thread declared inside InnoDB 274 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 227 MySQL thread id 39, query id 7076 192.168.1.38 webserver update INSERT INTO emails_history_30_27(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856588834, ACTIVE 855 sec, process no 1792, OS thread id 2449665984 inserting, thread declared inside InnoDB 15 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 486 MySQL thread id 45, query id 7029 192.168.1.231 webserver update INSERT INTO emails_history_30_9(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856588820, ACTIVE 855 sec, process no 1792, OS thread id 2446631360 inserting, thread declared inside InnoDB 73 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 428 MySQL thread id 60, query id 6997 192.168.1.37 webserver update INSERT INTO emails_history_30_16(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856588589, ACTIVE 857 sec, process no 1792, OS thread id 2444273600 inserting, thread declared inside InnoDB 395 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 607 MySQL thread id 25, query id 6645 192.168.1.36 webserver update INSERT INTO emails_history_30_5(email_id, mailing_id, action_type, xtime) VALUES ( ---TRANSACTION 0 1856588555, ACTIVE 858 sec, process no 1792, OS thread id 2445845440 inserting, thread declared inside InnoDB 318 mysql tables in use 1, locked 1 1 lock struct(s), heap size
Re: How to avoid redundancy between PK and indices ?
I understand what you're saying. The problem is that if mysql attempted to do a query like you suggest: Select Count(*) From myTable Where a=1 And b=1 And c=1 And d=1 And e=1 And f=1; It can only use one index for the query, and hopefully, the optimizer will pick one of the six indexes with the fewest keys to scan. But even if it could virtualize the PK that way, it could still cause a scan of millions of records while limiting the table scan to one of the six non-unique keys. In other words, it would/could take alot of time to see if a record is unique upon inserting new records - not something you'd be happy with performance wise, I'm sure. Therefore, a true, concatenated key that enforces uniqueness and can operate immediately upon inserts is really necessary, regardless of what other indexes are on the columns. The type of query you're suggesting can be done with bitmapped indexes (Oracle has them), where the indexes values are stored as bitmaps, and you can combine them so Oracle uses multiple indexes in one query to quickly pair down the records to scan. Bitmapped indexes work very well with the cardinality of keys is less than 10,000 (number of unique key values). In a nutshell, think of a field for sex/gender and a table of 1 million records. A bitmapped index of that field would only be 125,000 bytes long (1 million bits) (one bit=one record), and to find all the M records, just map the on bits to the record number in the datatable. For fields with larger possible values (say, state of residence - 50 values), each location would be represented by 6 bits. Pretty simple concept, but great performance gains can be had over regular btree indexes. I think this is what you're getting at. When I asked the MySQL AB folks at the first conference in Orlando a couple of years ago about adding bitmapped index support in MySQL, they didn't really know what I was talking about. The developer I spoke to thought I was suggesting creating indexes on bitmapped images. No, not exactly. I hope they know what it is now, though, and have (or already have) considered adding support for it in MySQL. -Hank On 10/5/05, C.R. Vegelin [EMAIL PROTECTED] wrote: Hi Hank, You are quite right. I need separate non-unique indices on a, b, c, d, e and f to avoid table scans. And when each combi must be unique I need a Primary Key (a,b,c,d,e,f). And only Key a (a) seems to be redundant with the primary key ... Suppose there would be a PK (a,b,c,d,e,f) defined, without a separate PK index. And let's assume some rows like: columns:a b c d e f row1 has: 1 1 1 1 1 1 row2 has: 1 1 1 1 1 2 row3 has: 1 1 1 1 1 3 etc. Then checking on unique PK could be done by MySQL internally with: Select Count(*) From myTable Where a=1 And b=1 And c=1 And d=1 And e=1 And f=1; to avoid a duplicate primary key for row1, by using / joining the separate index tables. With this Select query, MySQL could / should make use of the 6 existing separate indices. Uniqueness can be fully guaranteed with these 6 non-unique indices in this case. In other words, a separate PK index is fully redundant in this case, right ? In addition, it would save space without the longer concatenate key of a+b+c+d+e+f. Thanks, Cor - Original Message - From: Hank [EMAIL PROTECTED] To: C.R. Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:57 PM Subject: Re: How to avoid redundancy between PK and indices ? It depends.. if this is your create table statement: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f) ); Then only one unique index is being created on the concatenate key of a+b+c+d+e+f. Queries on any fields other than A will cause a full table scan. On the other hand, if your create table is: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY a (a), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); This will create the primary key, plus six additional indexes, each of which is queryable. But in this case, the KEY a (a) non-unique index is redundent with the primary key, so to do what you want - a unique index on a+b+c+d+e+f PLUS the ability to independtly search the b c d e and f fields, here is the create table you'll need to use: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); -- -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Selecting a group of distinct items?
Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name datevalue Fred 2005-10-01 7 Fred 2005-10-02 10 Joe2005-10-01 4 Joe2005-10-01 10 and so on. New values get inserted every day, but old ones remain. I'd like a query that selects the value field for the most recent date for each name. Since I didn't know how, right now I'm selecting all the distinct names and looping over those in PHP to do queries that grab the latest value from each. But next I want to be able to sort that overall list by value, which just leads to more and more complexity with my workaround. If all else fails, I suppose I can have that loop create a temporary table with each name and then sort that, but it seems like there has to be a cleaner solution out there. Is there? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Database Internals Developer position at ANTs software, a database company in Burlingame, CA
Senior Database Kernel Developer ANTs software inc. has developed a high-speed relational database, the ANTs Data Server, which is based on our patent-pending lock-free datastructure technology. We are looking for a Senior Database Engine Developer with experience in developing online backup, replication and other core functionality to join our Database Engine Team. Requirements: 3+ years experience doing internals development of a commercially available database engine. 3+ years experience in database internals algorithms with focus on optimizer, parser, executor, and transaction processing. 3+ years experience in multithreaded programming We will consider someone with Operating Systems Internals Development background. C++ on Windows or Solaris. We are looking for someone who is a mid-to-senior level developer. This position is very specific for a database engine developer, not a database user. To apply, please send your resume to [EMAIL PROTECTED] Thanks Jane Benedict Staffing Manager ANTs software, inc. 650-931-0582 415-309-0606 www.ants.com Jane Benedict Staffing Manager ANTs Software, Inc. 650-931-0582 Cell - 415-309-0606 www.ants.com [EMAIL PROTECTED] ANTs software inc. has developed a high-speed relational database, the ANTs Data Server, which is based on our patent-pending lock-free datastructure technology.
Re: Selecting a group of distinct items?
SELECT DISTINCT(name), date, value FROM table_name ORDER BY date DESC; On Thu, 2005-10-06 at 17:33 -0400, Donnie Lynch wrote: Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name datevalue Fred 2005-10-01 7 Fred 2005-10-02 10 Joe2005-10-01 4 Joe2005-10-01 10 and so on. New values get inserted every day, but old ones remain. I'd like a query that selects the value field for the most recent date for each name. Since I didn't know how, right now I'm selecting all the distinct names and looping over those in PHP to do queries that grab the latest value from each. But next I want to be able to sort that overall list by value, which just leads to more and more complexity with my workaround. If all else fails, I suppose I can have that loop create a temporary table with each name and then sort that, but it seems like there has to be a cleaner solution out there. Is there? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a group of distinct items?
Manish Marathe wrote: SELECT DISTINCT(name), date, value FROM table_name ORDER BY date DESC; DISTINCT is not a function. The above (if it even works) is exactly equivalent to: SELECT DISTINCT name, date, value FROM table_name ORDER BY date DESC; and will match distinct rows (that is, combinations of name, date and value). -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a group of distinct items?
of course, although DISTINCT(name) works On Fri, 2005-10-07 at 12:29 +1300, Jasper Bryant-Greene wrote: Manish Marathe wrote: SELECT DISTINCT(name), date, value FROM table_name ORDER BY date DESC; DISTINCT is not a function. The above (if it even works) is exactly equivalent to: SELECT DISTINCT name, date, value FROM table_name ORDER BY date DESC; and will match distinct rows (that is, combinations of name, date and value). -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a group of distinct items?
Something like this ought to work (untested). If you're using 4.1 you will need a temporary table as a sub-query will not work. SELECT name, date, value FROM yourtable WHERE date = ( SELECT MAX(date) FROM yourtable yourtable2 WHERE yourtable.name = yourtable2.name ) Off the top of my head that seems the way to go, if anyone else cna do it better please speak up. Chris Donnie Lynch wrote: Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name datevalue Fred 2005-10-01 7 Fred 2005-10-02 10 Joe2005-10-01 4 Joe2005-10-01 10 and so on. New values get inserted every day, but old ones remain. I'd like a query that selects the value field for the most recent date for each name. Since I didn't know how, right now I'm selecting all the distinct names and looping over those in PHP to do queries that grab the latest value from each. But next I want to be able to sort that overall list by value, which just leads to more and more complexity with my workaround. If all else fails, I suppose I can have that loop create a temporary table with each name and then sort that, but it seems like there has to be a cleaner solution out there. Is there? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a group of distinct items?
Manish Marathe wrote: of course, although DISTINCT(name) works Yes, but writing it that way gives the impression that you are somehow applying DISTINCT only to the name column, which you're not. You're applying it to entire rows. Oh, and can you please stop top-posting. Jasper On Fri, 2005-10-07 at 12:29 +1300, Jasper Bryant-Greene wrote: Manish Marathe wrote: SELECT DISTINCT(name), date, value FROM table_name ORDER BY date DESC; DISTINCT is not a function. The above (if it even works) is exactly equivalent to: SELECT DISTINCT name, date, value FROM table_name ORDER BY date DESC; and will match distinct rows (that is, combinations of name, date and value). -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_real_query failed: Can't find file: ... .frm (errno: 24)
Hi, I am running an application using the C-API against mysql 4.0.20 on a redhat AS 3 server. The users using this application are getting numerous (and seemingly random) query errors. Has anyone seen these before? The frm files do exist. It's not from just this table. mysql_real_query failed: Can't find file: './sqllive/XHEAD.frm' (errno: 24) The query for this particular error was: select max(id) from XHEAD Regards, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump ONLY Schema
I've used msyqldump to retrieve the structure and data of my db, but can I use it to only spit out the structure, ie, the Create Table statements, but none of the inserts. Thanks. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log file full of Got an error reading communication packets
Hi, I too have a server whose log file is full of these errors. This server is also running MySQL 4.0.20. It is on Red Hat Enterprise Linux AS release 3 (Taroon). The errors are: (many thousands of them)... 051004 15:39:34 Aborted connection 349 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 15:39:56 Aborted connection 134 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 16:09:25 Aborted connection 351 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 16:18:19 Aborted connection 339 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 16:18:20 Aborted connection 325 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 17:32:06 Aborted connection 367 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 18:05:41 Aborted connection 662 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 19:20:55 Aborted connection 61 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 21:59:03 Aborted connection 246 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 22:08:19 Aborted connection 317 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) 051004 22:08:34 Aborted connection 314 to db: 'sqllive' user: 'sqllive' host: `localhost' (Got an error reading communication packets) My /etc/my.cnf is: cat /etc/my.cnf [client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=5M datadir=/usr/local/mysql/data [mysqldump] quick Regards, Ian Collins. Randy Paries wrote: Thanks for the reply and the kindly reprimand Info: DB Server Mysql 4.0.20 Mandrakelinux release 10.1 (Official) for i586 - 2.6.8.1-12mdksmp Application Servers Red Hat Linux release 9 Tomcat 5.0.24 These application servers connect to the DB via Tomcat or by perl scripts In my data directory I have a file called millhouse.unitnet.com.err My main db is unitnet It appears this is a good link that I received from GLEB http://dev.mysql.com/doc/mysql/en/communication-errors.html I am going down that path right now Thanks Randy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 8:19 AM To: Randy Paries Cc: mysql@lists.mysql.com Subject: Re: Log file full of Got an error reading communication packets Randy Paries [EMAIL PROTECTED] wrote on 09/21/2005 08:56:13 AM: Hello, My log file is getting filled with these. Can someone tell me what these mean? Thanks Randy //-snip-// 050122 20:28:00 Aborted connection 53561 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:28:49 Aborted connection 53612 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) 050122 20:29:20 Aborted connection 53671 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:32:14 Aborted connection 53910 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:34:47 Aborted connection 54111 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:34:48 Aborted connection 54116 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:35:11 Aborted connection 54142 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) 050122 20:38:45 Aborted connection 54468 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:39:46 Aborted connection 54572 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) 050122 20:40:54 Aborted connection 54584 to db: 'unitnet' user: 'paries' host: `local.flanders' (Got an error reading communication packets) 050122 20:42:09 Aborted connection 54794 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) 050122 20:43:33 Aborted connection 54936 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) 050122 20:44:40 Aborted connection 55004 to db: 'unitnet' user: 'paries' host: `local.krusty' (Got an error reading communication packets) Not unless you can tell us which application is making those log entries and which log file you just quoted. What you just did is a lot
mysql_real_query failed: Can't find file: ... .frm (errno: 24)
Hi, I am running an application using the C-API against mysql 4.0.20 on a redhat AS 3 server. The users using this application are getting numerous (and seemingly random) query errors. Has anyone seen these before? The frm files do exist. It's not from just this table. mysql_real_query failed: Can't find file: './sqllive/XHEAD.frm' (errno: 24) The query for this particular error was: select max(id) from XHEAD Regards, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump ONLY Schema
Hi Scott, This is covered in the MySQL manual at http://dev.mysql.com/doc/mysql/en/mysqldump.html The option you need is - --no-data, -d Do not write any row information for the table. This is very useful if you want to get a dump of only the structure for a table 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: Scott Klarenbach [mailto:[EMAIL PROTECTED] Sent: Friday, 7 October 2005 10:27 AM To: My SQL Subject: mysqldump ONLY Schema I've used msyqldump to retrieve the structure and data of my db, but can I use it to only spit out the structure, ie, the Create Table statements, but none of the inserts. Thanks. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_real_query failed: Can't find file: ... .frm (errno: 24)
Hi Ian, Have you checked the NIC? If your ethernet card or switch port is failing, this may be causing the issues that you have in the logs. The other issues with the tables, have you run mysqlcheck or myisamcheck against these tables to ensure they are not corrupt? http://dev.mysql.com/doc/mysql/en/table-maintenance.html could be useful. It will explain how to use mysqlcheck and/or myisamcheck in detail. Sorry if I am teaching you how to suck eggs. 8-) 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: Ian Collins [mailto:[EMAIL PROTECTED] Sent: Thursday, 6 October 2005 7:49 AM To: mysql@lists.mysql.com Subject: mysql_real_query failed: Can't find file: ... .frm (errno: 24) Hi, I am running an application using the C-API against mysql 4.0.20 on a redhat AS 3 server. The users using this application are getting numerous (and seemingly random) query errors. Has anyone seen these before? The frm files do exist. It's not from just this table. mysql_real_query failed: Can't find file: './sqllive/XHEAD.frm' (errno: 24) The query for this particular error was: select max(id) from XHEAD Regards, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_real_query failed: Can't find file: ... .frm (errno: 24)
Hi, I am running an application using the C-API against mysql 4.0.20 on a redhat AS 3 server. The users using this application are getting numerous (and seemingly random) query errors. Has anyone seen these before? The frm files do exist. It's not from just this table. mysql_real_query failed: Can't find file: './sqllive/XHEAD.frm' (errno: 24) The query for this particular error was: select max(id) from XHEAD Regards, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a group of distinct items?
Donnie Lynch [EMAIL PROTECTED] wrote on 10/06/2005 05:33:01 PM: Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name datevalue Fred 2005-10-01 7 Fred 2005-10-02 10 Joe2005-10-01 4 Joe2005-10-01 10 and so on. New values get inserted every day, but old ones remain. I'd like a query that selects the value field for the most recent date for each name. Since I didn't know how, right now I'm selecting all the distinct names and looping over those in PHP to do queries that grab the latest value from each. But next I want to be able to sort that overall list by value, which just leads to more and more complexity with my workaround. If all else fails, I suppose I can have that loop create a temporary table with each name and then sort that, but it seems like there has to be a cleaner solution out there. Is there? Thanks Actually, this is THE most frequently asked technique on the list. The problem is, if you are new, you can't see the pattern and don't know how to recognize that this is what you are doing. What you are trying to find is known as the group-wize maximum and is well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html That and several other common query patterns are in this section of the manual: http://dev.mysql.com/doc/mysql/en/examples.html HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
