Re: Accountability with MySQL
From: James Harvard At 7:28 pm +0100 14/3/06, Martijn Tonies wrote: Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. At 1:49 pm -0500 14/3/06, Rhino wrote: A null means unknown or not applicable and is a perfectly valid value to use in many, many situations. It's not often you can say that two people are quite literally arguing about nothing! :-) James Harvard (... being flippant because the actual arguments started going over my head about half-a-dozen posts ago...) We're arguing about whether or not your database design (as stored on disk) should contain NULLs. IMO: no, or at least as few as possible. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies wrote: We're arguing about whether or not your database design (as stored on disk) should contain NULLs. IMO: no, or at least as few as possible. I believe the answer really is - *it depends*. You are both right, really. Martijn, yes, according to academia and proper database design, you should not really be using NULLs, (Rhino, see the writings of Chris Date and Fabian Pascal somewhere like www.dbdebunk.com, www.thethirdmanifesto.com etc.). Here's a good one: http://www.dbdebunk.citymax.com/page/page/1396241.htm Of course, you're still going to pay $10-$15 for this.. However, what is good for academia is *not* always good for the real world. Why store Unknown in a column for a table that could store millions, or billions, of rows, when you could simply allow a NULL (and save a *bunch* of disk space across your schemas) in the real world. If you can allow for the use of the NULL in the application, and *not* try to interpret it 5 ways from Sunday, then, in my honest opinion, a NULL would be acceptable. Short answer, as long as you do it without trying to get your application to handle them in many different ways, I don't see a great problem (when weighing them against extra costs, in both space and speed). If you can get away with not using NULL as well, then great. This argurment will, however, carry on between academia and the real world for at least the next 4000 years. :) Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
From: Mark Leith Martijn Tonies wrote: We're arguing about whether or not your database design (as stored on disk) should contain NULLs. IMO: no, or at least as few as possible. I believe the answer really is - *it depends*. You are both right, really. Martijn, yes, according to academia and proper database design, you should not really be using NULLs, (Rhino, see the writings of Chris Date and Fabian Pascal somewhere like www.dbdebunk.com, www.thethirdmanifesto.com etc.). Here's a good one: http://www.dbdebunk.citymax.com/page/page/1396241.htm Got that one ;) The book I quoted is from Date, btw, and I enjoy reading it ... Of course, you're still going to pay $10-$15 for this.. However, what is good for academia is *not* always good for the real world. Why store Unknown in a column for a table that could store millions, or billions, of rows, when you could simply allow a NULL (and save a *bunch* of disk space across your schemas) in the real world. Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). If you can allow for the use of the NULL in the application, and *not* try to interpret it 5 ways from Sunday, then, in my honest opinion, a NULL would be acceptable. Short answer, as long as you do it without trying to get your application to handle them in many different ways, I don't see a great problem (when weighing them against extra costs, in both space and speed). If you can get away with not using NULL as well, then great. I guess I can agree with that one. This argurment will, however, carry on between academia and the real world for at least the next 4000 years. :) Until it gets replaced by a better model, but I don't expect that anytime soon ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
But (in at least some situations) is not appropriate to record that you know that you don't have a value? I think the words of Donald Rumsfeld are appropriate here: There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. At 12:02 pm +0100 16/3/06, Martijn Tonies wrote: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. snip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
But (in at least some situations) is not appropriate to record that you know that you don't have a value? Sure it would. It it's needed. But the answer doesn't have to be a NULL. Remember the example about recording the fact that a student did not make a test for whatever reason? It was then said you could store null for the test score. I then replied that that was a bit backward IMO, cause you're using the test scores table to store that a student did not take a test, which is wrong, given that each row in a table should mean exactly the same thing (theory knocking at the door once again) and that makes a design more clear. I think the words of Donald Rumsfeld are appropriate here: There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. I think we'll have a hard time storing Donalds knows and unknowns in a database... :-) At 12:02 pm +0100 16/3/06, Martijn Tonies wrote: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. snip Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Coalesce (was: Re: Accountability with MySQL)
Ah well, SQL (these days) provides plenty of functions to avoid the problems. Coalesce being one of them... How often do people write: (probably not valid MySQL) select firstname || coalesce(middlename || ' ', ' ') || lastname from ... Your syntax isn't quite correct; coalesce doesn't use a concatenation symbol, it uses a list of arguments like this: coalesce(Salary, commission, bonus) Yes, the reason I was using || ' ' there was when middle name was not null, it would already add a space, and when it's null, it would just return a space. Given that NULL || ' ' returns NULL anyways, this would be give me the results for all cases, I think :) -- Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
From: [EMAIL PROTECTED] Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Using NULLs as well as de-normalization brings the risk of integrity problems to your storage, storing what is right is only a good thing. And when it comes to having to writing JOINs for all your queries, lo and behold, I bring you the wonder of the VIEW. ;-) Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:32:45: From: [EMAIL PROTECTED] Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Using NULLs as well as de-normalization brings the risk of integrity problems to your storage, storing what is right is only a good thing. And when it comes to having to writing JOINs for all your queries, lo and behold, I bring you the wonder of the VIEW. ;-) The VIEW eases the syntax, but does it do anything for performance? Surely it must be much slower to read 11 different tables (Master record containing all NOT NULL fields, and 10 slave records which may or may not contain relevant fields)? Ignoring caching, you are going to have at least one disk access for every NULL field (index lookup which fails) and two for every non NULL field (index lookup, data lookup) for every null field. This means that you have multiplied your number of disk accesses (ignoring caching, again) by 6-11 times (assuming the master record takes two disk accesses). That again seems a very high price to pay for theoretical elegance. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Using NULLs as well as de-normalization brings the risk of integrity problems to your storage, storing what is right is only a good thing. And when it comes to having to writing JOINs for all your queries, lo and behold, I bring you the wonder of the VIEW. ;-) The VIEW eases the syntax, but does it do anything for performance? Surely it must be much slower to read 11 different tables (Master record containing all NOT NULL fields, and 10 slave records which may or may not contain relevant fields)? Ignoring caching, you are going to have at least one disk access for every NULL field (index lookup which fails) and two for every non NULL field (index lookup, data lookup) for every null field. This means that you have multiplied your number of disk accesses (ignoring caching, again) by 6-11 times (assuming the master record takes two disk accesses). That again seems a very high price to pay for theoretical elegance. Keeping your design clean is hardly sacrificing anything. The most important feature of your database and the database design is the integrity of your data. Period. De-normalization, NULLs et all make this task much harder, as well as understanding your design for the guy that takes over your job ... Performance comes next, first thing to do there is getting your indices straight. Next thing is optimizing the slow stuff - if you can go around by keeping, for example, (these much smaller slave) tables in memory, and this works out fine, then do so. If all your queries are optimized, the next thing to do is to throw hardware at it. This is very cheap comparing to your hourly rate. ;-) Obviously, if your queries and design are valid and the database system is still slow, you can also complain with the creators of the database system - performance, after all, is a physical attribute, not a database design issue perse. During the years, many database systems have seen many improvements to bring better performance to its users. Only recently, for example, the Firebird DBMS implemented a new on-disk structure for it's indices greatly enhancing performance for indices with a large number of duplicates. In the past, people said: you have to modify your design such and so, with all risks included. Now, you can keep your design as clear as possible and enjoy greater performance, just because someone thought of something clever. If no-one ever complained, this particular piece of code wouldn't have changed. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Also, surely if you left join to a table then if there's no matching row you get a null value in your result set. So I'm not sure what the difference is between getting that null because you store it in a column in the primary table, and deriving it by left joining to a related table with no matching rows. James Harvard At 11:27 am + 16/3/06, [EMAIL PROTECTED] wrote: As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
James, Also, surely if you left join to a table then if there's no matching row you get a null value in your result set. So I'm not sure what the difference is between getting that null because you store it in a column in the primary table, and deriving it by left joining to a related table with no matching rows. Handling nulls in your resultsets makes a heck of a lot more sense than also storing these NULLs :-) Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
True Propositions (was Re: Accountability with MySQL)
Read the literature on how to design databases. What you do is storing true propositions. That is, each attribute defines a certain true proposition. For example: Employee Martijn has Employee# 14. You should be able to derive these sentences from every row. Putting a NULL or N/A in there fails to meet this requirement. Perhaps my theory is a bit rusty but I have never heard of this true propositions business before. Do you have a citation where I can read about this? I would certainly recommend this one: http://www.amazon.com/exec/obidos/ASIN/0321197844/databasede095-20/103-0880452-0854264?creative=327641camp=14573adid=0VQ1KDVJ56MV7389RPEQlink_code=as1 Hmm, I had hoped for a citation to a free online document that I could read. I don't fancy the thought of spending $100+ US on a book just to argue the fine points of database design Then again, I didn't ask for a link to a free source of this information so I can't fault you for your link, it (presumably) answers the question. You will find the true-thingy also in here: http://www.dbdebunk.com/page/page/2928212.htm Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the proper (least expensive) way to do this
RedRed!com IT Department wrote: So, for my peace of mind, I would need to follow these procedures to ensure Martijn's issue doesn't happen: 1. lock the table 2. execute my select 3. insert if it does not already exist 4. unlock the table On a high level, is this a correct list of procedures? Sean I would just do the insert, and check for a duplicate key error. If I get the duplicate key error, then update the record if necessary. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Accountability with MySQL
And now we are down to reality. This is a MySQL list. Views are a wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you define the
logical view. {Not poking at the development team I think 5.x is a huge step
forward}.
So you can't hide the underlying structure currently in MySQL. Like all
other things we have to commingle best practices with pragmatism. In other
words do what makes sense while at the same time acquiring a good knowledge
of fundamentals. {Not just slap name address phone1 phone n in some
table with an auto-increment ID field and say your done with the design.}
By the way, Date and Codd {both way above me in math and theory} had this
discussion ~20 years ago over a 6 month period in pages and pages of
Database magazine. In the end I believe they agreed to disagree.
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 5:33 AM
To: mysql@lists.mysql.com
Subject: Re: Accountability with MySQL
From: [EMAIL PROTECTED]
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32:
Well, the question still is if you should store unknown at all ;)
Not according to Date: you should store what is known. See the remarks
about the true propositions, from which relational databases are
derived
(but you probably know that).
As someone totally unread in the theory of databases, that seems unduly
puritanical. I assume that what Date would propose is that you have
another table (related by master key) in which, if you do not know
something, you do not enter it. But this means that if you have 10
different pieces of potentially but not necessarily available information
about a single master record (e.g. a person), you have to do a 10-way join
in order to retrieve all the information about them. Replacing a
theoretically ugly null flag with a 10 way join strikes me, as an engineer
rather than a theoretician, the wrong side of the elegance/practicality
trade-off.
Using NULLs as well as de-normalization brings the risk of
integrity problems to your storage, storing what is right is only
a good thing.
And when it comes to having to writing JOINs for all your queries,
lo and behold, I bring you the wonder of the VIEW.
;-)
Martijn Tonies
Upscene Productions
http://www.upscene.com
--
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]
On MySQL Views (Re: Accountability with MySQL)
Hello Gordon,
And now we are down to reality. This is a MySQL list. Views are a
wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you define the
logical view. {Not poking at the development team I think 5.x is a huge
step
forward}.
Can you elaborate on that?
Any real world experience with MySQL views?
The thing that annoys the blo*** cr** out of me with the
current implementation is the maintainability of views -
as the complete query structure is lost once you've created
a view, so you need to run/modify it from SQL statement
all the time :-(
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1064: update .....select nested.
update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where column2='[EMAIL PROTECTED]' And column1 = '[EMAIL PROTECTED]' Or: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where b.column2=a.column1 And column1 = '[EMAIL PROTECTED]' Regards! -Mensaje original- De: Truong Tan Son [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 15 de Marzo de 2006 07:22 p.m. Para: mysql@lists.mysql.com Asunto: Error 1064: update .select nested. Dear Sir, MySQL-4.1.12 on RedHat Linux EL4: mysql update TABLE1 set FIELD1=(select FIELD2 from TABLE2 where mysql COLUMN2= '[EMAIL PROTECTED]') where COLUMN1='[EMAIL PROTECTED]'; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select FIELD2 from TABLE2 where COLUMN2='[EMAIL PROTECTED] mysql For MySQL -5.0.18 is Ok Syntax error for version 4.1.12 ? Thanks you and best regards, -- -- 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: Error 1064: update .....select nested.
AAAUUUGGGHHH!!! You used the dreaded comma separated list !!! ;-) A more explicit way to write the same thing posted by Sr. Cruz... update TABLE1 a INNER JOIN TABLE2 b on a.id = b.other_id set a.FIELD1=b.FIELD2 WHERE a.column1='literal'; Actually posting a real query (instead of a cleansed column1...column2 query) would have resulted in you getting better answers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ing. Edwin Cruz [EMAIL PROTECTED] wrote on 03/16/2006 10:24:05 AM: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where column2='[EMAIL PROTECTED]' And column1 = '[EMAIL PROTECTED]' Or: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where b.column2=a.column1 And column1 = '[EMAIL PROTECTED]' Regards! -Mensaje original- De: Truong Tan Son [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 15 de Marzo de 2006 07:22 p.m. Para: mysql@lists.mysql.com Asunto: Error 1064: update .select nested. Dear Sir, MySQL-4.1.12 on RedHat Linux EL4: mysql update TABLE1 set FIELD1=(select FIELD2 from TABLE2 where mysql COLUMN2= '[EMAIL PROTECTED]') where COLUMN1='[EMAIL PROTECTED]'; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select FIELD2 from TABLE2 where COLUMN2='[EMAIL PROTECTED] mysql For MySQL -5.0.18 is Ok Syntax error for version 4.1.12 ? Thanks you and best regards, -- -- 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 JDBC Exception: No operations allowed after connection closed
The fact that I use JOTM with Tomcat forces me to use a different data source factory from the one described in the MySQL document Using Connector/J with Tomcat, (http://dev.mysql.com/doc/refman/4.1/en/cj-tomcat-config.html) - org.objectweb.jndi.DataSourceFactory vs. org.apache.commons.dbcp.BasicDataSourceFactory. The data source factory required for JOTM is specified in the following document: How to use JDBC and transactions in Tomcat with JOTM (http://jotm.objectweb.org/current/jotm/doc/howto-tomcat-jotm.html) Different data source factories translate into different data sources and connections (XA pool vs. DBCP) so I've been wondering if anybody knows what parameters should be used to configure XA pool running in Tomcat to use the new MySQL driver recommendations (autoReconnect being an obsoleted parameter)? -Original Message- From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tue, 14 Mar 2006 13:37:55 -0500 Subject: MySQL JDBC Exception: No operations allowed after connection closed I use Tomcat 5.0.28 on both Fedora and FreeBSD, MySQL 4.1.14, J/Connector JDBC driver for MySQL 3.1.11 and JOTM 2.0.10. From time to time I get the MySQL JDBC driver exception No operations allowed after connection closed, after which DB calls from my application keep failing. The stack trace of the exception is as follows: java.sql.SQLException: No operations allowed after connection closed. at com.mysql.jdbc.Connection.checkClosed(Connection.java:1831) at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:4866) at org.enhydra.jdbc.core.CoreConnection.setAutoCommit(CoreConnection.java:23 4) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.setAutoCommit(Standa rdXAConnectionHandle.java:123) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta ndardXAConnectionHandle.java:220) at org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta ndardXAConnectionHandle.java:197) at com.kc.ppt.dao.mysql.UserDeviceDAOImpl.updatePhoneNumber(UserDeviceDAOImp l.java:499) at com.kc.ppt.bo.UserDeviceBO.updateUserDeviceNumber(UserDeviceBO.java:187) at com.kc.ppt.bo.UserBO.addUserProfile(UserBO.java:188) at com.kc.ppt.bapi.BAPIImpl.addUserProfile(BAPIImpl.java:97) at com.kc.ppt.servlet.CreateUserProfileServlet.processRequest(CreateUserProf ileServlet.java:262) at com.kc.ppt.servlet.CreateUserProfileServlet.doPost(CreateUserProfileServl et.java:123) at javax.servlet.http.HttpServlet.service(HttpServlet.java:709) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicat ionFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilte rChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve .java:214) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCont extValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve .java:152) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java: 137) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java: 118) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:102) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:535) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:102) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.j ava:109) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon text.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52 0) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) When defining datasources in the application's context.xml I tried both the new recommended configuration for MySQL J/Connector JDBC driver (shown for Resource name=jdbc/primaryDS) and the autoreconnect=true approach (shown for Resource name=jdbc/primaryDS). Please note that I did not mix those two approaches, I just used different datasources to indicate configurations that I used. Does anybody know if the new MySQL J/Connector configuration works with Tomcat? Here's my context.xml file: Context path=/myapp docBase=myapp debug=5 reloadable=true
max_allowed_packet
Hi, I'm trying to see what speed I will get from a simple query like: select * from bigTable and I noticed the server- client transfer is always 512 bytes. (iostat tells me I read 6348.80*1024 bytes/sec in a given time period, and 12697.60 reads/sec, which translates to exactly 512) I set the max_allowed_packet on the server side (through my.cnf and restarting the server): show variables like '%allowed%'; ++-+ | Variable_name | Value | ++-+ | max_allowed_packet | 1047552 | ++-+ 1 row in set (0.00 sec) and I am running the client as: mysql --max_allowed_packet=1M smdev -e select * from MyTable /dev/null What am I missing? Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert performance
I need some help with improving INSERT performance. I am using JDBC. I have minimized my indices as much as I can, changed to use batching, and setup a thread pool for each transacted batch. Note that I am only using InnoDB. Now I am wondering what can be tweaked in the server properties. Right now I have two log files, the log file size 512M and my buffer pool is 1.2G. 6 threads seems to work best for a single CPU. As an aside, even though I have minimized index usage as much as I can, I still notice that every subsequent set of inserts I do takes a little longer each time. For example, the fourth set of 1 million inserts takes at least twice as long as the first set of inserts. Do we have the ability to play with the Btree page depth or anything like that? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NULL columns
When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL columns
When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? In the cases that you really want to store NULL ;-) No, it's not the word null. Of course space is consumed. How much depends, see, for example: http://dev.mysql.com/doc/refman/5.0/en/static-format.html or http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html This is for MyISAM, InnoDB behaves differently. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL columns
Hi! - Original Message - From: Martijn Tonies [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 8:59 PM Subject: Re: NULL columns When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? In the cases that you really want to store NULL ;-) No, it's not the word null. Of course space is consumed. How much depends, see, for example: http://dev.mysql.com/doc/refman/5.0/en/static-format.html or http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html This is for MyISAM, InnoDB behaves differently. InnoDB's old table format in 4.1 and earlier kept a fixed-length column the same length even when the value NULL was stored. That, of course, wasted a lot of space. The advantage was less fragmentation in updates. InnoDB's new table format in 5.0 does not use any space to store a NULL. The column itself needs one bit to indicate whether the value is NULL or not. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case sensitivity problem (I think) on OS X
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Errors 1005 and 1025 - but not foreign keys
I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. How can I get rid of this mess? Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
At 15:22 -0600 3/16/06, Sterling Anderson wrote: I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | | utf8_unicode_ci| utf8| 192 | | Yes | 8 | | utf8_icelandic_ci | utf8| 193 | | Yes | 8 | | utf8_latvian_ci| utf8| 194 | | Yes | 8 | | utf8_romanian_ci | utf8| 195 | | Yes | 8 | | utf8_slovenian_ci | utf8| 196 | | Yes | 8 | | utf8_polish_ci | utf8| 197 | | Yes | 8 | | utf8_estonian_ci | utf8| 198 | | Yes | 8 | | utf8_spanish_ci| utf8| 199 | | Yes | 8 | | utf8_swedish_ci| utf8| 200 | | Yes | 8 | | utf8_turkish_ci| utf8| 201 | | Yes | 8 | | utf8_czech_ci | utf8| 202 | | Yes | 8 | | utf8_danish_ci | utf8| 203 | | Yes | 8 | | utf8_lithuanian_ci | utf8| 204 | | Yes | 8 | | utf8_slovak_ci | utf8| 205 | | Yes | 8 | | utf8_spanish2_ci | utf8| 206 | | Yes | 8 | | utf8_roman_ci | utf8| 207 | | Yes | 8 | | utf8_persian_ci| utf8| 208 | | Yes | 8 | | utf8_esperanto_ci | utf8| 209 | | Yes | 8 | | utf8_hungarian_ci | utf8| 210 | | Yes | 8 | ++-+-+-+--+-+ ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote: This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | ... ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). So, it sounds like it may be more logical for me to use utf8_bin universally or perhaps a different character set like latin1? I'm curious because we contracted someone at DoIT (the UW) to migrate an Oracle DB to MySQL, and they chose to use utf8. Now I'm having this problem. I almost contacted you directly. Not sure if you recognize my name, I'm with WNPRC. -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ 608.577.7208 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
At 16:01 -0600 3/16/06, Sterling Anderson wrote: On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote: This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | ... ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). So, it sounds like it may be more logical for me to use utf8_bin universally or perhaps a different character set like latin1? I'm curious because we contracted someone at DoIT (the UW) to migrate an Oracle DB to MySQL, and they chose to use utf8. Now I'm having this problem. The character set is not the issue. The collation is. That's what determines the comparison and sorting properties. DoIT's choice of utf8 is neutral, and it makes sense that they chose to use the default case-insensitive collation unless you specified in advance that you require case sensitive comparisons. Changing to latin1 won't in itself make any difference because its default collation is case insensitive, too. You do have the choice of using an explicitly case-sensitive collation, though: latin1_general_cs -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication: slaves don't change to new log file properly
Our mysql master machine crashed (hardware problem) and rebooted. When it came back up, it started a new master log file. The slaves, however, were all stuck on the last log file. They still showed both replication threads running, no errors, but they just did not advance. I had to manually run STOP SLAVE; CHANGE MASTER TO ...; START SLAVE on all of them and then they picked up properly. Why didn't the slaves figure out there was a new log file? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(SOLVED) Re: Error 1064: update .....select nested.
Dear Sir, All solutions is very good ! update TABLE1 a INNER JOIN TABLE2 b on a.id = b.other_id set a.FIELD1=b.FIELD2 WHERE a.column1='literal'; Or update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where column2='[EMAIL PROTECTED]' And column1 = '[EMAIL PROTECTED]' Or update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where b.column2=a.column1 And column1 = '[EMAIL PROTECTED]' Thanks you very much. - Original Message - From: [EMAIL PROTECTED] To: Ing. Edwin Cruz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; 'Truong Tan Son' [EMAIL PROTECTED] Sent: Thursday, March 16, 2006 10:51 PM Subject: RE: Error 1064: update .select nested. AAAUUUGGGHHH!!! You used the dreaded comma separated list !!! ;-) A more explicit way to write the same thing posted by Sr. Cruz... update TABLE1 a INNER JOIN TABLE2 b on a.id = b.other_id set a.FIELD1=b.FIELD2 WHERE a.column1='literal'; Actually posting a real query (instead of a cleansed column1...column2 query) would have resulted in you getting better answers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ing. Edwin Cruz [EMAIL PROTECTED] wrote on 03/16/2006 10:24:05 AM: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where column2='[EMAIL PROTECTED]' And column1 = '[EMAIL PROTECTED]' Or: update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2 where b.column2=a.column1 And column1 = '[EMAIL PROTECTED]' Regards! -Mensaje original- De: Truong Tan Son [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 15 de Marzo de 2006 07:22 p.m. Para: mysql@lists.mysql.com Asunto: Error 1064: update .select nested. Dear Sir, MySQL-4.1.12 on RedHat Linux EL4: mysql update TABLE1 set FIELD1=(select FIELD2 from TABLE2 where mysql COLUMN2= '[EMAIL PROTECTED]') where COLUMN1='[EMAIL PROTECTED]'; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select FIELD2 from TABLE2 where COLUMN2='[EMAIL PROTECTED] mysql For MySQL -5.0.18 is Ok Syntax error for version 4.1.12 ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ROLLBACK question.
Dear Sir, How to know MySQL enable for ROLLBACK ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ROLLBACK question.
Hi, How to know MySQL enable for ROLLBACK ? Use InnoDB tables. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with selecting my max bid ..
Hi. I have the following table | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp | Bid_Status | +-+-+--++-++-+ | 1 |3 | 3 |12 | 2 | NULL | 1 | | 2 |3 | 3 |12 | 3 | NULL | 1 | | 3 | 24 | 3 |12 | 4 | NULL | 1 | | 4 | 24 | 3 |12 | 5 | NULL | 1 | | 5 | 24 | 3 |12 | 6 | NULL | 1 | | 6 |3 | 24 |14 | 4 | NULL | 1 | | 7 |3 | 3 |13 | 4 | NULL | 1 | | 8 | 24 | 3 | 12 |7 | NULL | 1 | | 9 | 24 | 3 |13 | 59000 | NULL | 1 | | 10 | 24 | 3 |12 | 8 | NULL | 1 | | 11 | 24 | 3 |13 | 6 | NULL | 1 | where auto_dealer_id is the dealer who put the car on auction , auto_id is the id of the car on auction, bid_amount is the amount did on the car but dealer_id what I want is to get the max bid placed for each car and the dealer_id who placed it. I currnetly have the following: mysql SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount' FROM bids WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id; +---+-+++ | dealer_id | auto_id | bid_id | bid_amount | +---+-+++ | 3 | 12 | 1 | 8 | | 3 | 13 | 7 | 6 | +---+-+++ But this output is wrong because if you refer to the soucrce table above the dealer_id should be 24 in both cases.. What have I missed ? Many Thanks Gregory Machin -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
Re: problem with selecting my max bid ..
Gregory Machin wrote: Hi. I have the following table | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp | Bid_Status | +-+-+--++-++-+ | 1 |3 | 3 |12 | 2 | NULL | 1 | | 2 |3 | 3 |12 | 3 | NULL | 1 | | 3 | 24 | 3 |12 | 4 | NULL | 1 | | 4 | 24 | 3 |12 | 5 | NULL | 1 | | 5 | 24 | 3 |12 | 6 | NULL | 1 | | 6 |3 | 24 |14 | 4 | NULL | 1 | | 7 |3 | 3 |13 | 4 | NULL | 1 | | 8 | 24 | 3 | 12 |7 | NULL | 1 | | 9 | 24 | 3 |13 | 59000 | NULL | 1 | | 10 | 24 | 3 |12 | 8 | NULL | 1 | | 11 | 24 | 3 |13 | 6 | NULL | 1 | where auto_dealer_id is the dealer who put the car on auction , auto_id is the id of the car on auction, bid_amount is the amount did on the car but dealer_id what I want is to get the max bid placed for each car and the dealer_id who placed it. I currnetly have the following: mysql SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount' FROM bids WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id; +---+-+++ | dealer_id | auto_id | bid_id | bid_amount | +---+-+++ | 3 | 12 | 1 | 8 | | 3 | 13 | 7 | 6 | +---+-+++ But this output is wrong because if you refer to the source table above the dealer_id should be 24 in both cases.. What have I missed ? Many Thanks Gregory Machin GROUP BY does not return rows. It returns group names and aggregate stats. You are grouping on auto_id, so you get random (actually, the first found) values for dealer_id and bid_id for each group. Many systems wouldn't even allow this query. Mysql does allow you to select columns not present in the GROUP BY clause as a convenience, but you are warned only to use columns with unique values per group http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html. Yours is a FAQ, however, with 3 solutions in the manual http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
