Fwd: our server got stuck
hi All,
Last month, one of the our server got stuck but We could n't find the
reason. Here I have attached the OS(Red Hat Enterprise Linux AS relese 4)
message log. I think issue may be in MySQL.
We are using MySQL Server version: 5.1.12-beta MySQL Community Server (GPL)
So if you can please send me what are the actions we can take for avoid this
type of scenarios in future.
Thanks
#--
Aug 14 22:18:48 localhost kernel: Unable to handle kernel NULL pointer
dereference at 0018 RIP:
Aug 14 22:18:48 localhost kernel:
a0156b14{:jbd:journal_commit_transaction+1140}
Aug 14 22:18:48 localhost kernel: PML4 46aec067 PGD 29203b067 PMD 0
Aug 14 22:18:48 localhost kernel: Oops: 0002 [1] SMP
Aug 14 22:18:48 localhost kernel: CPU 2
Aug 14 22:18:48 localhost kernel: Modules linked in: nfsd exportfs lockd
parport_pc lp parport autofs4 i2c_dev i2c_core sunrpc ds yenta_socket
pcmcia_core dm_mod button battery ac md5 ipv6 joydev ohci_hcd ehci_hcd tg3
floppy ext3 jbd mppVhba(U) qla2400(U) qla2xxx(U) qla2xxx_conf(U) aacraid(U)
mppUpper(U) sg sd_mod scsi_mod
Aug 14 22:18:48 localhost kernel: Pid: 2255, comm: kjournald Tainted: GF
2.6.9-5.ELsmp
Aug 14 22:18:48 localhost kernel: RIP: 0010:[a0156b14]
a0156b14{:jbd:journal_commit_transaction+1140}
Aug 14 22:18:48 localhost kernel: RSP: :01032adf7bb8 EFLAGS:
00010202
Aug 14 22:18:48 localhost kernel: RAX: 01025e9b0d98 RBX:
RCX: c100
Aug 14 22:18:48 localhost kernel: RDX: 0101c5721818 RSI:
01032adf6000 RDI: 0202
Aug 14 22:18:48 localhost kernel: RBP: R08:
01032adf6000 R09: 0100b58bdd80
Aug 14 22:18:48 localhost kernel: R10: 01032adf7b68 R11:
01032adf7b68 R12: 0101c5721b88
Aug 14 22:18:48 localhost kernel: R13: 0103277aeac0 R14:
0100cfe30e00 R15:
Aug 14 22:18:48 localhost kernel: FS: 002a95564b00()
GS:804bf400() knlGS:
Aug 14 22:18:48 localhost kernel: CS: 0010 DS: ES: CR0:
8005003b
Aug 14 22:18:48 localhost kernel: CR2: 0018 CR3:
cff6e000 CR4: 06e0
Aug 14 22:18:48 localhost kernel: Process kjournald (pid: 2255, threadinfo
01032adf6000, task 01032a8d67f0)
Aug 14 22:18:48 localhost kernel: Stack:
Aug 14 22:18:48 localhost kernel:010078d73088 1fd0
01032a8d67f0
Aug 14 22:18:48 localhost kernel:80132ff0 01032adf7c30
Aug 14 22:18:48 localhost kernel: Call
Trace:80132ff0{autoremove_wake_function+0}
80132ff0{autoremove_wake_function+0}
Aug 14 22:18:48 localhost kernel:
a0159898{:jbd:kjournald+250}
80132ff0{autoremove_wake_function+0}
Aug 14 22:18:48 localhost kernel:
80132ff0{autoremove_wake_function+0}
a0159798{:jbd:commit_timeout+0}
Aug 14 22:18:48 localhost kernel:80110c23{child_rip+8}
a015979e{:jbd:kjournald+0}
Aug 14 22:18:48 localhost kernel:80110c1b{child_rip+0}
Aug 14 22:18:48 localhost kernel:
Aug 14 22:18:48 localhost kernel: Code: f0 ff 43 18 8b 03 a8 04 74 52 49 8d
be 5c 01 00 00 e8 17 1a
Aug 14 22:18:48 localhost kernel: RIP
a0156b14{:jbd:journal_commit_transaction+1140} RSP
01032adf7bb8
Aug 14 22:18:48 localhost kernel: CR2: 0018
#
Aug 24 17:41:35 localhost kernel: Unable to handle kernel NULL pointer
dereference at RIP:
Aug 24 17:41:35 localhost kernel:
a0156b10{:jbd:journal_commit_transaction+1136}
Aug 24 17:41:35 localhost kernel: PML4 2cd832067 PGD 1f1216067 PMD 0
Aug 24 17:41:35 localhost kernel: Oops: [1] SMP
Aug 24 17:41:35 localhost kernel: CPU 3
Aug 24 17:41:35 localhost kernel: Modules linked in: parport_pc lp parport
autofs4 i2c_dev i2c_core sunrpc ds yenta_socket pcmcia_core dm_mod button
battery ac md5 ipv6 joydev ohci_hcd ehci_hcd tg3 floppy ext3 jbd mppVhba(U)
qla2400(U) qla2xxx(U) qla2xxx_conf(U) aacraid(U) mppUpper(U) sg sd_mod
scsi_mod
Aug 24 17:41:35 localhost kernel: Pid: 2312, comm: kjournald Tainted: GF
2.6.9-5.ELsmp
Aug 24 17:41:35 localhost kernel: RIP: 0010:[a0156b10]
a0156b10{:jbd:journal_commit_transaction+1136}
Aug 24 17:41:35 localhost kernel: RSP: 0018:01032ae27bb8 EFLAGS:
00010206
Aug 24 17:41:35 localhost kernel: RAX: 0102691752f0 RBX:
0101018779d0 RCX: c100
Aug 24 17:41:35 localhost kernel: RDX: c100 RSI:
0101018779d0 RDI: 0100cfe3dc00
Aug 24 17:41:35 localhost kernel: RBP: R08:
01032ae26000 R09: 0046
[EMAIL PROTECTED] log]# grep Aug 24 17:41 messages.2
Aug 24 17:41:35
Re: table based replication into a different db
Christian Parpart wrote: Hi all, i would like to replicate just tables beginning with a certain prefix while the replication slave host's database name also differs. i remember i once read something about it, but can't really find it on the net anymore, so does anyone have a hint for me here, or even know exactly what needs to be done? The manual explains it: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html http://dev.mysql.com/doc/refman/5.0/en/replication-options.html I think you need a combination of do/ignore and rewrite rules. But read *carefully* because a lot of the replicate-XYZ rules don't do what they seem to, in subtle ways that depend on the current default database and such. The manual does explain it, but it takes some deep thinking (or at least it took me some deep thinking). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Big SELECT: ordering results by where matches are found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So... Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'. 'speakers' is a table of speakers, with id, name and some text fields. 'topics' is a list of topics they address 'speakers_topics' relates the above two by pairs of id numbers 'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id. I want to search the data in the following order: name from 'speakers' topics text data from 'speakers' text data from 'articles' and 'other' ...and order the results according to where in that hierarchy a match is found. So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering. To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words. I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient. Can it all be done in one big query, perhaps with subqueries? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Revolution: an abrupt change in the form of misgovernment. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
Chris Sansom wrote: I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So... Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'. 'speakers' is a table of speakers, with id, name and some text fields. 'topics' is a list of topics they address 'speakers_topics' relates the above two by pairs of id numbers 'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id. I want to search the data in the following order: name from 'speakers' topics text data from 'speakers' text data from 'articles' and 'other' ...and order the results according to where in that hierarchy a match is found. So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering. To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words. I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient. Can it all be done in one big query, perhaps with subqueries? I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote: I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Oo-er. This sounds marvellous, and I /think/ I see what you're getting at, but it's a bit beyond anything I've done before - never used UNION for instance. Can you perhaps go into a little more detail? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote:
The entire UNION can then be ordered by relevance. You could also
just add in an arbitrary number in each UNION, to get the effect of
ordering by where in the hierarchy the match is found.
Actually, your pointing me towards UNION may have done the trick. I
read up on it on the MySQL docs site and I've ended up with this,
which actually covers more tables and fields than in my original post:
---
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against ('education')
)
order by relevance, division, sur, fore
) as tb
---
First, I did it without the outer select, and I got speakers repeated
if they were matched in more than one block. One of the comments on
the MySQL docs site suggested the 'wrapper', which I did initially
like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it
like this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Justice is incidental to law and order.
-- J. Edgar Hoover
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
more options for MySQL tools by MySQL
Hello all, I want to suggest one thing relating to MySQL Tools for 5.0(Administrator, Query browser etc.) - A new feature can be added to use command line options to synchronize structure and data, data transfer in many formats and scheduling above tasks on windows/linux and other OS. If these features will be available it will be better for users such as me to use only MySQL tools for all the needs. Please reply Thanks CPK -- Keep your Environment clean and green.
Best Practice - Encryption
Hi MySQL'ers -- I run multiple HIPAA compliant databases and a lot of very secure patient information in my MySQL databases. However, I am a bit of a security phreak and want to go a step further and encode the data INSIDE the database so that, in the very unlikely event that someone can bum-rush my security guards, break past the bullet-proof door walls, and rip out my hard drives through the locked rack cabinet, I want to ensure my data is safe. Also, I want to make it so that you cannot even LOOK at the data unless you know the correct encryption keys. I currently employ AES encryption for keeping passwords and such, and have been very successful in doing so. However, I have run into a snag and I am hoping that you all can suggest ways to rectify this or provide better ideas than I currently employ. Quick Rundown: MySQL 5.0.38 running on Debian Linux, Kernel 2.6, fully up-to-date. Server Side Language - ASP (yes, I know, don't say anything) Web Server - IIS 6.0, Windows Server 2003 SP2. ODBC - MyODBC 3.15.17 5 different firewalls (3x Linux (technically 1 firewall, 3 chains), 1 Windows, 1 hardware router firewall). 1024-bit SSL encryption from client to Web app, 256-bit SSL from Web app to MySQL (and the server will kick you if you are not using SSL). Here are the questions at hand: 1) When using any encryption method, I continuously get a Error Nr 2014: Commands out of sync. I have checked the manual, but it does not provide any helpful information, as I can be connecting only through the CLI on my Linux box and it gives me the same error. MySQL Query Browser also supplies said error. Question: What exactly are the correct command sequences so as I can rectify this error, and why do I get it from 3 different clients, even when I am the only one connected? 2) When using any encryption method, I can successfully ENCODE, DECODE, AES_ENCRYPT, and AES_DECRYPT all of the necessary data, however, the Query Browser (and hence, my app) cannot display the data as it seems as though the column length is NULL, even though you can expand the column and see the data. The CLI reports the data correctly. The fields in question are LONGTEXT. Is this a case where encoding or encryption is not worth the CPU clocks, or is the field just too long to encode? Should I consider moving these to VARCHAR() instead of LONGTEXT? 3) We do research-based data mining on these records, and of course, encryption will hinder this process. Would setting up temp tables with the unencrypted data be the best course of action for doing the research that is necessary? Thank you for your time and I look forward to discussing these issues with you. J.R.
Re: Big SELECT: ordering results by where matches are found
Chris Sansom wrote:
At 11:01 -0400 10/9/07, Baron Schwartz wrote:
The entire UNION can then be ordered by relevance. You could also
just add in an arbitrary number in each UNION, to get the effect of
ordering by where in the hierarchy the match is found.
Actually, your pointing me towards UNION may have done the trick. I read
up on it on the MySQL docs site and I've ended up with this, which
actually covers more tables and fields than in my original post:
---
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against
('education')
)
order by relevance, division, sur, fore
) as tb
---
First, I did it without the outer select, and I got speakers repeated if
they were matched in more than one block. One of the comments on the
MySQL docs site suggested the 'wrapper', which I did initially like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it like
this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve
this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
Looks like you've found the solution you need. The only other
suggestion I have is to use UNION ALL if you don't need to eliminate
duplicate rows in the UNION, because there's some overhead for checking
for them.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 5.1.3 RC is available!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 5.1.3 RC, a new release candidate of the Type-IV pure-Java JDBC driver for MySQL has been released. Version 5.1.3 is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon alpha release. It is now available in source and binary form from the Connector/J download pages at http://dev.mysql.com/downloads/connector/j/5.1.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) As always, we recommend that you check the change log http://dev.mysql.com/doc/refman/5.0/en/cj-news.html and Upgrading sections http://dev.mysql.com/doc/refman/5.0/en/cj-upgrading.html in the manual before upgrading as well as the CHANGES file in the download archive to be aware of changes in behavior that might affect your application. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing This is the release candidate of our implementation of the new JDBC-4.0 API, along with some new performance features. This release candidate, as any other pre-production release, should not be installed on production level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has worked very hard to ensure a high level of quality, protect your data by making a backup as you would for any other software pre-production release. MySQL Connector/J 5.1.3 includes the following new feature compared to 5.1.2: * Setting useBlobToStoreUTF8OutsideBMP to true tells the driver to treat [MEDIUM/LONG]BLOB columns as [LONG]VARCHAR columns holding text encoded in UTF-8 that has characters outside the BMP (4-byte encodings), which MySQL server can't handle natively. Set utf8OutsideBmpExcludedColumnNamePattern to a regex so that column names matching the given regex will still be treated as BLOBs The regex must follow the patterns used for the java.util.regex package. The default is to exclude no columns, and include all columns. Set utf8OutsideBmpIncludedColumnNamePattern to specify exclusion rules to utf8OutsideBmpExcludedColumnNamePattern. The regex must follow the patterns used for the java.util.regex package. * New methods on com.mysql.jdbc.Statement: setLocalInfileInputStream() and getLocalInfileInputStream(): * setLocalInfileInputStream() sets an InputStream instance that will be used to send datato the MySQL server for a LOAD DATA LOCAL INFILE statement rather than a FileInputStream or URLInputStream that represents the path given as an argument to the statement. This stream will be read to completion upon execution of a LOAD DATA LOCAL INFILE statement, and will automatically be closed by the driver, so it needs to be reset before each call to execute*() that would cause the MySQL server to request data to fulfill the request for LOAD DATA LOCAL INFILE. If this value is set to NULL, the driver will revert to using a FileInputStream or URLInputStream as required. * getLocalInfileInputStream() returns the InputStream instance that will be used to send data in response to a LOAD DATA LOCAL INFILE statement. This method returns NULL if no such stream has been set via setLocalInfileInputStream(). * The driver now connects with an initial character set of utf-8 solely for the purposes of authentication to allow usernames and database names in any character set to be used in the JDBC URL. * Errors encountered during Statement/PreparedStatement/CallableStatement.executeBatch() when rewriteBatchStatements has been set to true now return BatchUpdateExceptions according to the setting of continueBatchOnError. If continueBatchOnError is set to true, the update counts for the chunk that were sent as one unit will all be set to EXECUTE_FAILED, but the driver will attempt to process the remainder of the batch. You can determine which chunk failed by looking at the update counts returned in the BatchUpdateException. If continueBatchOnError is set to false, the update counts returned will contain all updates up-to and including the failed chunk, with all counts for the failed chunk set to EXECUTE_FAILED. Since MySQL doesn't return multiple error codes for multiple-statements, or for multi-value INSERT/REPLACE, it is the application's responsibility to handle determining which item(s) in the chunk actually failed. * Statement.setQueryTimeout()s now affect the entire batch for batched statements, rather than the individual statements that make up the batch. The following features are new, compared to the 5.0 series of Connector/J: * JDBC-4.0 ease-of-development features including auto-registration with the DriverManager via the
Really strange index/speed issues
Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bitwise logic
Hi, You can actually unpack them using some fairly cryptic stuff. I would only use this to unpack them once to re-store them as INT. Here's an example: CREATE TABLE ip (packed CHAR(4)); INSERT INTO ip (packed) VALUES (0xB16212C); mysql SELECT * FROM ip; ++ | packed | ++ | !, | ++ 1 row in set (0.00 sec) mysql SELECT - INET_NTOA( - (ord(substring(packed, 1, 1)) 24) + - (ord(substring(packed, 2, 2)) 16) + - (ord(substring(packed, 3, 3)) 8) + - (ord(substring(packed, 4, 4))) - ) AS unpacked - FROM ip; +-+ | unpacked| +-+ | 11.22.33.44 | +-+ 1 row in set (0.00 sec) Regards, Jeremy Baron Schwartz wrote: I think Gerald was suggesting you convert the IP's into integers and then do bitwise stuff on them. I don't know of a way to do what you're asking. Baron Wagner, Chris (GEAE, CBTS) wrote: Those functions concern dotted quad IP addresses, not packed binaries. Anybody know of some obscure MySQL functions to do bit logic on strings or get MySQL to recognize a char sequence as an integer? Gerald L. Clark wrote: Wagner, Chris (GEAE, CBTS) wrote: Hi. I've got IP addresses stored in the database as packed binaries. i.e. binary(4). I put them in there that way so that I could do bitwise logic on them to do subnet searches. e.g. ...WHERE `ip` 'mask' = 'network'. Only it turns out that as far as I can tell MySQL can't do bit logic on strings, only integers. I came up with an onerous SQL call that eats the 4 bytes and makes an integer out of them but there's got to be a better way. The worst case is that I redo the database to have all the IP's as integers. Anybody know of a way to make MySQL either do the bit logic on the string or convert/cast the string as an integer? Thanks. Try INET_ATON() and INET_NTOA(). -- Gerald L. Clark Supplier Systems Corporation -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Hi Chris, Chris Hemmings wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Did you run both queries multiple times and average the time taken? Otherwise, it seems likely that in one instance the data was cached, and in the other it was not. The query_cache being off does not affect caching in this sense. Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Jeremy Cole wrote: Hi Chris, Chris Hemmings wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Did you run both queries multiple times and average the time taken? Otherwise, it seems likely that in one instance the data was cached, and in the other it was not. The query_cache being off does not affect caching in this sense. Regards, Jeremy Jeremy, Thanks for the swift reply :-) I have tried the query multiple times... Just to double check, here are the timings for each, 5 times: SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9605 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9506 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9556 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9614 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9474 sec) SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0009 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Wierd huh? Would you like any of the mysqld runtime settings? Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. Still confused. Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows in set (0.01 sec) So, the slower query obvisouly has the larger
Implement a logging table; avoiding conflicting inserts
Hello Listies,
Given: MySQL 4.0.12, I need to implement a pageview log with a
resolution of 1 day.
I propose this table:
CREATE TABLE `pageviews` (
`id` int(11) NOT NULL auto_increment,
`date` date NOT NULL default '-00-00',
`url` char(120) NOT NULL default '',
`views` mediumint(9) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`,`url`),
KEY `url` (`url`)
) TYPE=InnoDB;
So that an update will look like:
UPDATE pageviews SET views=views+1 WHERE date='DATE' AND
url='ARTIST'
Of course I need to INSERT the record if one does not match my WHERE.
This would be easy if I had 4.1 -- INSERT ... ON DUPLICATE KEY UPDATE,
I think -- but I do not.
So, how should I write my queries so that when a new day dawns, I don't
have 2 connections racing to INSERT?
I suspect I could do something like this (in PHP, line numbers added:
01 $link = connect2Db();
02 $sql = SELECT * FROM pageviews WHERE date='DATE' AND
url='ARTIST' LOCK IN SHARE MODE;
03 if ( mysql_num_rows(mysql_query($sql,$link)) ) {
04 // UPDATE
05 } else {
06 // INSERT
07 }
Also, should I explicitly mysql_query('COMMIT',$link) on line 8?
Thanks all!
--
wellington
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database page corruption on disk occurring during mysqldump on a fresh database
Thank you for your replies. I attempted to restore again and most oddly, mysql complained that it couldn't restore to a particular table because it wasn't in the database, which, of course, it had to be because the restore itself had just recreated it. So I blew away the entire mysql directory on the disk, updated to 5.0.45, and then it did not complain when I restored that time. So far, it has not since. Hi This might be happening due to two reasons; 1 The system date might not be correct. 2. Some things wrong with log postion (Incorrect log position) Regards, Krishna Chandra Prajapati The checksum errors might be due to various reasons. We had similar issue where we restored the database multiple times, replaced the ram sticks nothing helped. Finally we drilled down the issue to the chassis. Recommend testing the restore on a different machine to rule out any hardware issue. -- Thanks Alex http://alexlurthu.wordpress.comhttp://alexlurthu.wordpress.com On 8/31/07, Maurice Volaski mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: A 64-bit Gentoo Linux box had just been upgraded from MySQL 4.1 to 5.0.44 fresh (by dumping in 4.1 and restoring in 5.0.44) and almost immediately after that, during which time the database was not used, a crash occurred during a scripted mysqldump. So I restored and days later, it happened again. The crash details seem to be trying to suggest some other aspect of the operating system, even the memory or disk is flipping a bit. Or could I be running into a bug in this version of MySQL? -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data corruption and server crash issues in replicated setup
Hi all, Starting Wednesday night, we observed several weird errors indicative of data corruption shortly before a CPU spike and complete crash on our master db server (opera.oursite.com). opera.oursite.com had crashed twice with signal 11 in recent weeks, but we had never observed any data corruption issues. This was about 15 minutes after an inadvertent and short-lived server id conflict between two slave servers (serenade.oursite.com and adagio.oursite.com). Shortly after, we replaced the master with sonata.oursite.com, we then did a full mysqldump from sonata.oursite.com and imported that dump onto our 3 other db servers and resumed slaving (with opera as a slave to sonata). Then Thursday morning, we brought opera back online as our master. See server list [1] and timeline [2]. Between Thursday and Saturday, we continued to observe apparent data corruption errors, now on sonata as well as opera, as well as many dropped and/or failed connections at various unexpected times, often one error immediately after the other. [3] We took opera offline completely on Sunday morning, with serenade as our new master. We continued to observe data integrity problems on sonata. We then completely disabled the use of slaves on Sunday night and haven't had any issues (yet). Note that adagio, no longer in production, never had any issues either. What insights might you have into this behavior? Might it be due to a known bug in MySQL 5.0.27? How would you go about investigating the cause of this? I am happy to provide any other information you might think relevant. Below is is a list of our DB servers for reference, a timeline of events, and an example of some of the errors we received. Any help you can provide is very much appreciated! Thanks, David [1] List of db servers opera.oursite.com - original master, currently out of production sonata.oursite.com - slave, then temporary master on Wednesday, currently out of production serenade.oursite.com - slave, now current master since Sunday morning adagio.oursite.com - slave that was brought up with serenade's server id originally [2] Timeline Wednesday, September 5th, 8:00 PM - We launch Adagio with conflicting srv ID Wednesday, September 5th, 8:01 PM - We stop adagio, launch with correct ID Wednesday, September 5th, 8:05 PM - We restart replication on adagio, catchup Wednesday, September 5th, 8:16 PM - Data corruption errors CPU spike on opera Wednesday, September 5th, 8:18 PM - Opera dies Wednesday, September 5th, 8:30 PM - Sonata becomes master Wednesday, September 5th, 8:40 PM - Opera comes back online after reboot Wednesday, September 5th, 9:30 PM - Sonata dies with signal 11 Wednesday, September 5th, 9:40 PM - Lost DB connections on sonata Wednesday, September 5th, 10:18 PM - Another lost DB connection on sonata Thursday, September 6th, 3:00 AM - Dump is performed on Sonata Thursday, September 6th, 4:00 AM - Dump imported on opera serenade adagio Thursday, September 6th, 5:00 AM - Opera becomes master again Serenade and Adagio replicate Thursday, September 6th, 3:00 PM - Sonata and import done Sonata back into production Thursday, September 6th, Afternoon - Sonata's replication lagging behind Lots of IO wait on sonata Sonata pulled out of production Thursday, September 6th, 7:05 PM TO 9:29 PM - More apparent data corruption errors and lost connections on opera Thursday, September 6th, 10:19 PM TO 11:20 PM - A ton of failed connections to opera Thursday, September 6th, 11:04 PM TO Friday, September 7th, 1:32 AM - More data corruption errors Friday, September 7th, 3:16 AM - Opera dies again with signal 11 Friday, September 7th, 6:37 AM - Opera dies again with signal 11 (and a bunch of failed connections) Friday, September 7th, 9:18 PM - A bunch more failed/lost connections Sunday, September 9th, 5:00 AM - Opera taken out of production - Sonata and Adagio are slaves, serenade master Sunday, September 9th, 3:06 PM - Incorrect key file error on sonata - work_music table (MyISAM) marked as crashed - more apparent DB corruption, this time on sonata Sunday, September 9th, 3:10 PM - 3:12 PM - Error 127 reading table work_music on sonata Sunday, September 9th, 10:13 PM - 11:39 PM - Error 134 reading table production_favs on sonata Sunday, September 9th, 11:39 PM - Slaves taken completely offline, serenade now the only master [3] Representative Errors (Note that the vast majority of our tables are MyISAM -- including the ones we had errors with) UPDATE work_music, (SELECT SUM(count) AS num_views, COUNT(*) AS num_viewers FROM workmusic_hits WHERE work_music_id='36079') AS hits SET work_music.__num_views=hits.num_views, work_music.__num_viewers=hits.num_viewers WHERE work_music.work_music_id='36079' [nativecode=1031 ** Table storage engine for 'hits' doesn't have this option]
Re: Using MAX function to fetch primary id
You can do it as long as there is only a single record with the max value. If there is more than 1 record with the same max value, there isn't a single record to pull. To do it, you would need to join on the results of the max query, and part of the join condition would be the max value SELECT id, count, cat_id FROM table_x JOIN ( SELECT cat_id, max(count) maxcount FROM table_x GROUP BY cat_id ) AS table_max ON table_x.cat_id=table_max.cat_id AND table_x.count=table_max.maxcount I'm not sure if I got the syntax perfect, but that the concept of how you would do it. On Sep 7, 2007, at 3:11 PM, Cathy Fusko wrote: Hi, I need to select a max value for a group of records and I also need the primary key for that record. I am wondering if this can be done with a single query? e.g Table_x id count cat_id 110 1 220 2 335 2 415 1 with SELECT id, cat_id, max(count) FROM table_x GROUP BY cat_id I would probably get the following result id cat_id max(count) 1 1 15 2 2 35 and what I would like to get is id cat_id max(count) 4 1 15 3 2 35 Is there a way to do this with single query ? cathy www.nachofoto.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What should be a simple query...
I have two tables, one called RMAs and the other called rma_line_items. The first one has the general details of the RMA (Return Merchandise Authorization) , the second holds the details of each item being returned. What I want is a listing of the RMA ids (which are unique in the RMAs table) which have more than one line item in the corresponding table. So I'm using: SELECT * FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id IN (SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1) and it's netting me nothing, which I know is not true. So to investigate I just ran the subselect: SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1 and I find it's not giving me but one row, the first one to match having more than one item. But there are plenty more RMAs that have more than one entry in the rma_line_items table and I need to get at them. What am I doing wrong? Any ideas? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
