Re: locked non-existent row
On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley wrote: While a transaction in one thread tries to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? In the manual it is called gap locking: http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query Optimization
On Aug 30, 2011 6:46 PM, Brandon Phelps wrote: SELECT sc.open_dt, sc.close_dt, sc.protocol, INET_NTOA( sc.src_address ) AS src_address, sc.src_port, INET_NTOA( sc.dst_address ) AS dst_address, sc.dst_port, sc.sent, sc.rcvd, spm.desc AS src_port_desc, dpm.desc AS dst_port_desc FROM firewall_connections AS sc If this is a firewall connection log I presume open_dt is the time a connection was opened and is always going to be less than close_dt. Right? WHERE (open_dt = '2011-08-30 00:00:00' OR close_dt = '2011-08-30 00:00:00') AND (open_dt = '2011-08-30 12:36:53' OR close_dt = '2011-08-30 12:36:53') In that case your logic here simplifies to: WHERE open_dt = '2011-08-30 00:00:00' AND close_dt = '2011-08-30 12:36:53' Now add an index over open_dt and close_dt and see what happens. Jochem
Re: how things get messed up
On 2/10/10, Ilya Kazakevich wrote: There was a joke in russian PHP club: why strore images in database? Are you going to have a full text search on them? Yes. That is what EXIF data is for, isn't it? And considering this is about PDFs any inability of a database engine to do a full text search on them surely is a limitation of that database, not a conceptual disqualification of storing binary data in a database. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL View
On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL View
On Tue, Feb 10, 2009 at 1:47 PM, Steven Buehler wrote: From: Jochem van Dieten: What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? No. I am explicitly not saying how you should use views. I am just telling you how they work. But to give you some examples of how you could use views (I am still not saying how you should use views): 1. Use views to replace repetitive elements in queries. If you have lots of queries that perform the same join or filter, put it in a view. That has no semantic value, but you save yourself some typing. 2. Use views to manage permissions. If people have only access to a subset of the data, revoke their permissions on the table and define a view that has exactly the data that they have access to. Then give them permissions on the view. 3. Use views to define new schema elements that have meaning. If you have a normalized schema an invoice may be spread over a dozen tables (customer, invoice, invoiceline, item, price, shipping, payment, account etc.). You can define a view with all the proper joins and filters that groups that together so you get all the data at once. (Some people may argue that this is the same as no. 1, but I think it is an important distinction that the view represents an actual object: an invoice as you print and send them.) 4. your great view usage here Even if I reboot the computer, the view will still be there when it comes back up too? Yes, views are persitent. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updatable view using subquery??
On Mon, Feb 9, 2009 at 1:17 PM, blue.trapez...@gmail.com wrote: mysql CREATE VIEW v_aa AS - SELECT * - FROM flight AS f - WHERE f.RouteID IN - (SELECT r.RouteID - FROM route AS r - WHERE r.To= - (SELECT a.AirportID - FROM airport AS a - WHERE a.AirportCode='SIN') - ) - ORDER BY FlightID DESC; Query OK, 0 rows affected (0.02 sec) mysql insert into v_aa - values (1,1141,3145); Query OK, 1 row affected (0.00 sec) But according to the MySQL manual, a view is not updatable if it contains any of the following:...subquery in the select list. Your subquery is not in the select list, it is in the where. A subquery in the select list would be: CREATE VIEW v_aa AS SELECT * , (SELECT MAX(x) FROM y) AS z FROM flight This is not updatable because there is no sensible way to propagate changes to the y base table. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote: Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Search the manual for group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: feature request: statement SELECT...(INSERT|UPDATE) :)
On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes into a language is a very serious question that needs a great discussion but one never can tell, may be mysql developers will be interested in my proposal ;) I think it would be a good idea to look at the way other databases can do this and see if there is some common syntax that could also be implemented by MySQL. For instance PostgreSQL implements a INSERT / UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the functionality you want, but with a different syntax. If there are no other (better) competing implementation syntaxes (I don't know any, but maybe other list members do), I would like MySQL to adopt the PostgreSQL example. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating temp file, modifying data and putting into other table
On Jan 17, 2008 9:02 PM, Kerry Frater wrote: Thanks for the input Jochem. If you wish to ignore my code and continue with your own code that of course is fine with me. But why do you expect me to continue to help you if you ignore me anyway? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating temp file, modifying data and putting into other table
On Jan 17, 2008 2:22 PM, Kerry Frater wrote: Can someone please advise. I am looking to create a multiuser friendly way of getting a subset number of rows from a table into another whilst making a modification. create temporary table Ttable1 (select * from masterlist where ref='ABCDE'); update Ttable1 set ref='SMI0C001'; insert into sublist select * from Ttable1; drop Ttable1; How about: INSERT INTO sublist (ref, field1, field2, field3) SELECT 'SMI0C001' , field1 , field2 , field3 FROM masterlist WHERE ref='ABCDE' ; Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reply-to is to originator rather than to list
On 10/21/07, Rob Wultsch wrote: I was previously on a list where the reply-to was setup as it is on the mysql list, with the originator receiving a response rather than list. It ended up that that setting was the default, and had not been changed when the list was setup. Is there a good reason why the reply-to is setup as it is on this list? If you could explain why the answer in the FAQ doesn't satisfy you we might be of more assistance. I forget to change the destination address for most every email I write, I would guess I am not alone, and I do not think that this is good for the list. I believe it is excellent for the list as it raises the bar. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Warehousing and MySQL vs PostgreSQL
On 7/26/07, Andrew Armstrong wrote: * Table 1: 80,000,000 rows - 9.5 GB * Table 2: 1,000,000,000 rows - 8.9 GB This is a generic star schema design for data warehousing. I have read that it is better if perhaps partitioning is implemented, where new data is added to a partitioned table (eg, that represents a single day) and then when those samples expire - simply drop the partition. I believe partitioning would solve issues with SELECT and INSERT performance because the actual index tree size (and data in the table itself) would be reduced. While partitioning will most likely alleviate your DML woes, partially by breaking it up and partially by changing DML to DDL, we can not make any reasonable statement about your SELECT performance since we don't know what type of queries you will be running. If your queries are going to cross all partitions partitioning is not going to help you much, if your queries typically only touch one partition it will help a lot. I am a bit hesitant however to go with PostgreSQL because the partitioning system seems a bit less easier to work with than MySQL (5.1's) implementation; as I would need to maintain my own master table for clients to query, and I do not think partition pruning is in use at this time (eg, the analyser can ignore partitions that wont contain any matching data based on the query being issued). The PostgreSQL planner will prune every partition it can determine not to have any matching data. Whether it can determine so depends on the query and it can be a bit picky (e.g. if you pick the wrong datatype for a timestamp it may not be a candidate for pruning because it has a timezone, which is a bit unexpected if you are not used to timestamps having a timezone at all). Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: off-topic unsubscribe concern
On 7/8/07, Mogens Melander wrote: On Fri, July 6, 2007 17:55, Michael Dykman wrote: I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages..They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? I've seen quite few lately. Everybody who doesn't remove unnecessary junk from his messages before he posts them is 'suffering'. Messages send to you from the list include your customized unsubscribe link. If you include full messages when responding to them you are sending out your own custom unsubscribe link to all subscribers of this list, including all harvesters, public archives and broken preload-cachers. Since you are sending out your custom unsubscribe link quite literally by the thousands, are you really surprised the link is followed occasionally? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] If you don't want people to follow this link, why are you including it? To stop the 'suffering', stop including junk that doesn't belong in outgoing email. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
On 3/27/07, Tim Lucia wrote: -Original Message- From: Maciej Dobrzanski Sent: Tuesday, March 27, 2007 6:46 AM To: mysql@lists.mysql.com Subject: Re: Why doesn't the InnoDB count() match table status? MyISAM and InnoDB (and there are plenty more). RDBMS is not an Office spreadsheet, you cannot simply change software as from OpenOffice Calc to MS Excel and expect everything will work the same. The migration can be done You can and ought to be able to expect it. After all, SQL is a standard. But the problem is not with the result of the query, but with the speed. And the speed of an implementation is not standardized by the ISO/IEC. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/5/07, Jim C. wrote:
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role VARCHAR NOT NULL,
CONSTRAINT role_check CHECK role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'),
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
Well, here is what I got:
mysqlDump.postgres.working.sql 1398L, 13611680C written
[EMAIL PROTECTED] jims]$ psql inscom mysqlDump.postgres.working.sql 21 |
grep ERROR
ERROR: syntax error at or near , at character 164
ERROR: syntax error at or near role at character 218
Should be
CONSTRAINT role_check CHECK (role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'))
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/5/07, Jim C. wrote: When I uncomment some of these statements I get an error in regards to a comma. What I'm afraid of is that perhaps there is a compatibility issue such that an INSERT command on Postgres can't take as many records as MySQL. What version are you running? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/2/07, Jim C. [EMAIL PROTECTED] wrote:
I'm having to move some data from MySQL to Postgres. I used mysqldump
--compatible=postgresql, but the compatibility is extremely lacking.
It looks more like the person that designed the schema has payed very
little attention to the SQL standard. You can not blame anyone but the
designer for naming a field 'role' (which is a keyword in the SQL
standard) or using a non-standard set field type instead of a proper
lookup table.
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role
set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest')
NOT NULL default ''
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role VARCHAR NOT NULL,
CONSTRAINT role_check CHECK role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'),
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
If that doesn't work, rename the role field. And it might be a good
idea to give this table a primary key.
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?
On 1/1/07, mos wrote: At 12:49 PM 1/1/2007, Jochem van Dieten wrote: On 1/1/07, mos wrote: http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/ http://tweakers.net/reviews/649/6 Has this been fixed? As the article on the MySQL Performance Blog mentioned, a fix from InnoDB has been integrated into 5.30. 5.0.30 I meant. Tweakers.net has already tested this fix and it does show some improvement, but it still has a long way to go: http://tweakers.net/reviews/661/6 Yes Innodb has a long ways to go and I'm wondering if it is fixable so the performance is more linear. As it is, performance in the Tweakers' charts drop dramatically (tanks?) after 7 concurrent users even for version 5.03. I know Innodb works best if the table fits into memory, but for me that isn't practical (at least on one machine) because the tables will grow over time and I don't want to crash into a wall when the table exceeds memory capacity of the machine. The tweakers.net tests are with all data in memory. It is very well possible that the scaling behaviour of an I/O bound InnoDB application is very different. I would expect it to show a lower peak performance, but also a smaller drop-off after the peak. So I'm wondering how high traffic websites that use Innodb can overcome this problem? Google GMail, Craigs List, TIcket Master, Yahoo etc all have high number of updates per second, so there must be an InnoDb solution, right? What exactly do they use MySQL for? For instance, doesn't Google just write e-mail on a filesystems (GFS is essentially append-only) and only keep a small amount of meta-data somewhere that is actually updated? Design-wise I would expect their infrastructure to have much more in common with Dovecot then an email-in-a-database solution. I know these questions are pretty much rhetorical, but I thought I'd bounce this off of you guys to see what the best approach is for a high traffic transactional web site. If you were going to write one of these web sites I mentioned, would you still use InnoDb? I probably wouldn't use a relational database at all. Convenient as they may be due to their standardized interface (SQL), they perform much worse then a dedicated solution. And if I were to use a relational database, I would probably scale out instead of up. You might find ftp://ftp.research.microsoft.com/pub/tr/TR-2004-107.pdf an interesting read. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?
On 1/1/07, mos wrote: Is there a problem with InnoDb scaling with multi-processor CPU's? Apparently after reading the Tweakers.net article, with only 40 simultaneous users the performance of MySQL 5 will collapse. http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/ http://tweakers.net/reviews/649/6 Has this been fixed? As the article on the MySQL Performance Blog mentioned, a fix from InnoDB has been integrated into 5.30. Tweakers.net has already tested this fix and it does show some improvement, but it still has a long way to go: http://tweakers.net/reviews/661/6 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: interesting benchmark at tweakers.net
On 12/19/06, David Sparks wrote: I noticed an interesting benchmark at tweakers.net that shows mysql not scaling very well on hyperthreading and multicore cpus (see links at end of email). Does anyone know what engine they are using for their tests? (Innodb, myisam, berkdb heheh) InnoDB, the first installment of the series of tests had the following configuration: http://tweakers.net/reviews/620/2 And I would venture that these results are not because they did horrible things to their MySQL configuration. On the second installment of the series http://tweakers.net/reviews/633/7 engineers from Sun were brought in and they consulted with engineers from MySQL and on the last installment Peter Zaitsev of the MySQL Performance Blog did a review of their configuration: http://tweakers.net/reviews/660/6 In fact they seem to show that postgres is a faster db overall. This goes against my personal experience where I benchmarked a real world app we have and found mysql 10 to 100 times faster In my experience such a large performance difference between PostgreSQL and MySQL can always be attributed to some obvious difference in the internals. For instance because PostgreSQL can use indexes on datatypes that MySQL can not (or doesn't even have) or because queries are very repetitive and MySQL can use the query cache which PostgreSQL does not have. Without such obvious factors I am very inclined to attribute differences of the magnitude you are claiming to a difference in knowledge of the databases at hand. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX() and GROUP BY question
On 11/10/06, [EMAIL PROTECTED] wrote: Hello everybody Can you explain me please how to get the entire row where ID is maximum per given status_id Mysql 4.0.xx Have you checked the manual? There is an entire page specifically about the group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. Please share your analysis with us. Jochem
Re: MyISAM vs InnoDB
On 11/2/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: Is there a better open source database out there for that amount of data? Several. MySQL's own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. Jochem
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: At 02:27 PM 11/1/2006, Jochem van Dieten wrote: What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. There is a big difference between a 20 TB database and a 20 TB table! Unless you're storing huge blobs, a table of over 1TB will have hundreds of millions of rows (billions?), and that means huge index trees that need to be maintained. Indexes scale with ln(O). The difference between 100 million rows and 1 billion rows is maybe 10%. And if you are worried about your indexes getting asymmetric use a hash index instead of a B-tree. Though realistically you would partition the data and then your indexes get partitioned too. If PostgreSQL can put 20 TB into a table and still have reasonably fast inserts and queries, then I'll take my hat off to them. It can if you design your queries to make use of the indexes and the partitioning. But first I need to see proof that they can accomplish this. So if you have any sites or white papers you'd like to share, go ahead. Keep in mind we're talking about TB tables here, not databases. Google the PostgreSQL and PostGIS mailinglists. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Locks
On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
On 9/7/06, [EMAIL PROTECTED] wrote: I need to know the number of rows that a query will return before actually executing the query. So I am sending select count(*) before sending select *. Actually I need to reject queries if the number of records that it will return is huge, to avoid my server running out of memory. My application has a huge database of around 10 millions. The selects with INNODB falls drastically as the size of records grow. A select count(*) that takes 4 secs with 1 million records takes 40 secs with 3 million records. Just read the fine manual, everything is explained there: http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html If you want more information on the fundamentals behind multiversioning I would recommend reading chapter 5 of Concurrency Control and Recovery in Database Systems http://research.microsoft.com/pubs/ccontrol/ If you want more information about the InnoDB specific implementation details there is always the source. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
On 8/19/06, Wai-Sun Chia wrote: On 8/19/06, Jochem van Dieten wrote: Tweakers.net did a benchmark comparing a trace of the queries generated by their own website on a T1 to a dual Opteron. The article is in Dutch, but the graphs speak for themselves: http://tweakers.net/reviews/633/7 http://tweakers.net/reviews/633/8 Wow! The graphs speak for themselves... CoolThreads suddenly don't seem so cool after all! :-( Linear scalability is good The graphs showing linear scalability are from PostgreSQL, the graphs for MySQL are the ones on the bottom that show a rather spectacular meltdown when the load increases. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
On 8/12/06, Miles Teg wrote: Sun also has some awesome CoolThreads offerings (SPARC architecture), but I haven't had a chance to benchmark one yet. With 32 concurrent threads on a single 8 core 4 way threaded cpu, I'd like to see how MySQL's performance is on those systems. Does anyone have any experience with or information about CoolThreads systems running MySQL? Tweakers.net did a benchmark comparing a trace of the queries generated by their own website on a T1 to a dual Opteron. The article is in Dutch, but the graphs speak for themselves: http://tweakers.net/reviews/633/7 http://tweakers.net/reviews/633/8 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
On 7/28/06, Dan Nelson wrote: In the last episode (Jul 28), leo huang said: So, the deleted rows' disk space in tablespace can't re-use when I use Innodb, can it? And the tablespace is growing when we update the tables, even the amount of rows do not increase. It can be re-used after the transaction has been committed After all transactions that were started before the transaction that did the delete committed have either been committed or rolled back. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Docs team looking for tech writer
On 6/20/06, Stefan Hinz wrote: The MySQL documentation team is looking for another technical writer. For this we need the best and the most dedicated people around. You may work from anywhere in the world as long as you have the necessary skills and technical facilities to communicate across the Internet. Projects are coordinated from Germany, but our team members are expected to work independently. You should be prepared to work intensively with our developers when writing new documentation (in English, so you should be a native English speaker) Are you sure this is a requirement? To be able to do the job applicants might need to be fluent in English. Requiring that applicants have spoken English since early childhood (the definition of native English speaker) goes well beyond that: why can somebody who learned English later not be fluent in English? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (GPL License)
On 6/8/06, mos wrote: At 08:15 PM 6/7/2006, you wrote: I believe that if you are only using MySQL for your company's internal needs, whether from a web server or for deployment to other company-owned locations, you don't need a commercial license. Unfortunately that's not what MySQL AB licensing person told me. The license is more strict than that. If your company distributes an application that uses MySQL database inside the company (even inside the same building), and you don't give the other dept the source code (so it falls outside the gpl license) then the dept receiving the application needs to have a MySQL license. In other words, the complete application source code must follow the application. If both departments are registered as the same 'legal person' whatever one department owns is automatically owned by the other department too. So as long as both departments are under the same registration at the Chamber of Commerce (or however that legally works in your jurisdiction), this is not distribution as intended in the GPL. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ugly SQL for a report...
On 5/29/06, Cory wrote: I have the following query that is running VERY slowly. Anyone have any suggestions? --- SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF ,(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE ID_pnr=pnr.ID) services ,(SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE pnr.reservationdatetime='2000-05-29 00:00:00' AND pnr.reservationdatetime='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY reservationdatetime; That is indeed very ugly. If you prettify it by adding proper indenting etc. I might be inclined to read it and think about an answer. Jochem
Re: ugly SQL for a report...
On 5/29/06, Cory Robin wrote: SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF , (SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE ID_pnr=pnr.ID) services , (SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE pnr.reservationdatetime='2000-05-29 00:00:00' AND pnr.reservationdatetime='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY reservationdatetime; One way to avoid the repeated COUNT might be to use a nested FROM that pre-calculates the count as a pseudo-column: SELECT pnr_precalc.ID ID_pnr, pnr_precalc.reservationdatetime, pnr_precalc.conf_number, pnr_precalc.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC SEPARATOR 'br') names, (SELECT SUM(pf.base_fare * pnr_precalc.passengercount) FROM pnr_fares pf WHERE pf.ID_pnr=pnr_precalc.ID ) base_fare, (SELECT SUM(pf.tax*pnr_precalc.passengercount) FROM pnr_fares pf WHERE pf.ID_pnr=pnr_precalc.ID ) tax , ( SELECT SUM(psf.segfee_amount*pnr_precalc.passengercount) FROM pnr_segments_fees psf INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr_precalc.ID ) XF, (SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE ID_pnr=pnr_precalc.ID) services , (SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr_precalc.ID AND bln_payment='1' AND transaction_type='sale') payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE ID_pnr=pnr_precalc.ID AND bln_payment='1' AND transaction_type='credit') credits FROM ( SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, pnr.created_by, (SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID ) passengercount FROM pnr ) pnr_precalc INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE pnr_precalc.reservationdatetime = '2000-05-29 00:00:00' AND pnr_precalc.reservationdatetime = '2006-05-29 23:59:59' GROUP BY pnr_precalc.ID ORDER BY reservationdatetime; (You probably have to fix this since I can not test it.) If you see too many loops over pnr_service_fees and pnr_payments_credits in your explain output you can rework them in the same way. If this doesn't help, we are going to need a lot more from the schema and the explain output. Jochem
Re: OOC: Reply / Return Address of this List
On 4/18/06, Jason Teagle wrote: 1. Please always reply to the List. Who runs this list? Could it please be configured to send replies back to the list rather than the individual? Please read the faq before rehashing issues that have been beaten to death. Jochem
Re: RIGHT JOIN better than INNER JOIN?
On 3/21/06, Robert DiFalco wrote: I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. If so, it this peculiar to MySQL or would this be true with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem
Re: A difficult query- urgent for me
On 1/15/06, [EMAIL PROTECTED] wrote: Actually there is a table with columns a and b . So i want if a contains a particular word than a's value should return else 'b' value should return. SELECT CASE WHEN a = 'Good' THEN a ELSE b END FROM table Jochem
Re: 'LIKE' for numbers
On 12/18/05, Andy Pieters [EMAIL PROTECTED] wrote: Is there any operator for mysql that behaves like LIKE but can act on numbers. No. But with a bit of creativity you can use arithmetic to come to a predictae that does the same: SELECT * FROM table WHERE floor(log10(floor(x / y))) floor(log10(floor(x / (y + 1 (You might want to verify that this works as intended, including NULL's, 0's and negatives.) Jochem
Re: Heikki: What will become of InnoDb once MySQL license runs out
On 11/6/05, mos wrote: Sure but if people have commercial applications that use InnoDb, then what? Is there a surprise tax waiting for them next year? Nothing changes for the licenses you already have. If you have an application that is both incompatible with the GPL and depends on InnoDB and you want to buy new licenses you might find them more expensive or unavailable. If Oracle is that much in favor of continuing the InnoDb contract with MySQL, why didn't they pre-announce it saying the terms and conditions would be the same as before. Or are they going to change the contract so they collect $500 or even $1000 extra for every commercial application that is distributed with InnoDb? If this happens, what alternative will MySQL be offering their users who need transactions and RI? An upgrade to PostgreSQL? Even if Oracle wants to shut that down and buys RedHat, Fujitsu, NTT, EnterpiseDB, Pervasive etc., it is still BSD licensed :) Nothing has changed. You had a single-vendor solution where the vendor could do with their prices what it wanted to do. You have a single-vendor solution where the vendor can do with their prices what it needs to do. It's a lot like seeing a neighboring army surround your oil fields and then hear them say, No cause for alarm! We're here to help you improve your pumping efficiencies!. You just have to wonder how sincere are they? Should I trust Larry Ellison with the deed to my house? I can't help but smile by the thought of Larry Ellison becoming an Open Source convert who does not want to license InnoDB to MySQL at all but just releases the next version as GPL-only. If you buy a share you can go ask him himself during the next shareholder event :) Jochem
Re: Can't reference column aliases
On 11/7/05, mos wrote: Why isn't there a way to reference column aliases in the columns list or where clause? Because the SQL standad says so. See chapter 7 of ISO/IEC 9075-2:2003. select if(score50,-5,0) failing_score, if(score50, 1, 0) passing_score, attendance/totaldays Percent_Attendance , failing_score/passing_score*percent_attendance from schoolwork (There is no logic to the columns so please don't try to replace the code with something simpler) Not simpler, but equivalent and preventing double execution: SELECT *, failing_score/passing_score*percent_attendance FROM ( SELECT if(score50,-5,0) failing_score, if(score50, 1, 0) passing_score, attendance/totaldays Percent_Attendance FROM schoolwork ) tmp_result Jochem
Re: Heikki: What will become of InnoDb once MySQL license runs out
On 11/5/05, Ezra Taylor wrote: To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us I think InnoDB/Oracle is going to meet all their obligations to their paying customers, licensers and licensees. If there is a mismatch between what you consider 'fair' and the actual obligations of InnoDB/Oracle you have not done your homework before choosing a database and I hope it will be a valuable lesson. Jochem
Re: Heikki: What will become of InnoDb once MySQL license runs out
On 11/5/05, Mark wrote: Is there anyone who can shed some light on this without the anti-Orcacle hysteronics? No. Those who know have to go through proper channels. Oracle is a public company and the disclosure of its future actions has to go through proper channels or it will incur the wrath of the SEC. This mailinglist is not a proper channel, so all you will get here are links to the official press release and a rehash of previous speculation. (Rehashes are rarely better then the original, so if you care for the quality of the speculation the archive of this list is a good place to start.) Jochem
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
On 11/1/05, Kevin Burton wrote: MyISAM has a cool feature where it keeps track of the internal row count so that SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so. The same query on INNODB is O(logN) since it uses the btree to satisfy the query. Are you sure? Finding a single record using an index may be O(logN), but wouldn't reading all of the index be O(N)? Jochem
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 10/20/05, Martijn Tonies wrote: That doesn't help: check constraints are evaluated only on insert and update, not on delete. That's why you need an assertion. Hmmm, would that be SQL standard? Or implementation specific? It is at the very least implied in the SQL standard. From a logical point of view, I don't see why a check constraint shouldn't be checking on all operations. A CHECK is a row constraint. If there is no row, there is no constraint. IIRC a CHECK constraint is only allowed to refer to the row itself in the SQL standard, so that behaviour is perfectly logical. An ASSERTION is a table constraint that is the equivalent of a CHECK constraint for a row. If you wanted to maintain some sort of constraint that for every distinct value in some column there are at least X and at most Y occurences, an ASSERTION is the way to go. An ASSERTION is a table constraint so it exists as long as the table exists, even when there are no rows in the table. Back in reality you don't enforce this using DDL. Apart from the fact that I wouldn't know a single database that implements ASSERTIONs according to the SQL standard, can you imagine having to run some SELECT fk FROM table GROUP BY fk HAVING COUNT(fk) NOT BETWEEN x AND y on every commit? This is something you enforce with triggers or circumvent by using a stored procedure for all DML operations. Just take care you use a serializable transaction if your database is more concurrent then is good for you. BTW, with regard to the original question of how good different RDBMS's are in enforcing data integrity: apart from Access pretty much anything is better then MySQL. Even if we assume that you run MySQL 5 in strict mode, the absence of CHECK constraints is a huge problem. My personal preference would be PostgreSQL. And the best kept secret of data integrity is http://www.postgresql.org/docs/8.0/static/sql-createdomain.html Jochem
Re: Multiple INNER JOINS
On 10/19/05, Peter Brawley wrote: I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. That query generates no error in 5.0.13. There have been several cascading join bugs, some fixed, some not. What version are you running? This particular one was fixed in 5.0.12. The fix poses a serious backward compatibility risk for every query using USING or NATURAL, so unless you are exclusively developing for 5 and higher, you should not use them. The shortest explanation of the problem is to just try the following queries: CREATE TABLE test (ID INT); INSERT INTO test (ID) VALUES (1); SELECT * FROM test t1 JOIN test t2 USING (ID); SELECT ID FROM test t1 JOIN test t2 USING (ID); SELECT t1.ID FROM test t1 JOIN test t2 USING (ID); SELECT t2.ID FROM test t1 JOIN test t2 USING (ID); SELECT * FROM test t1 NATURAL JOIN test t2; SELECT ID FROM test t1 NATURAL JOIN test t2; SELECT t1.ID FROM test t1 NATURAL JOIN test t2; SELECT t2.ID FROM test t1 NATURAL JOIN test t2; DROP TABLE test; Writing the full join condition will indeed work. Jochem
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 20 Oct 2005 10:13:56 +0200, Harald Fuchs wrote: Jochem van Dieten writes: Back in reality you don't enforce this using DDL. Apart from the fact that I wouldn't know a single database that implements ASSERTIONs according to the SQL standard, can you imagine having to run some SELECT fk FROM table GROUP BY fk HAVING COUNT(fk) NOT BETWEEN x AND y on every commit? This is something you enforce with triggers or circumvent by using a stored procedure for all DML operations. If some multiplicity is one of your business rules, it doesn't matter if you put it into a trigger, a stored procedure, or an assertion - you'll have to run the query above on every commit anyway. If you put it in a trigger or a stored procedure you can reduce it to: SELECT COUNT(fk) FROM table WHERE fk = NEW.fk GROUP BY fk SELECT COUNT(fk) FROM table WHERE fk = OLD.fk GROUP BY fk Unles you are bulk loading this performs much better. Jochem
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 10/19/05, Martijn Tonies [EMAIL PROTECTED] wrote: First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. In MySQL triggers are the only way. In SQL you might be able to use an assertion depending on your exact needs. (Don't know if there is any database that actually implements them per the standard.) The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? Jochem
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 10/19/05, Martijn Tonies [EMAIL PROTECTED] wrote: First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? That depends on your check constraint implementation. Firebird, for example, allows you to reference other tables in SQL statement in your CHECK constraints. So, you could do: exists( select count(*) as cnt from mychildtable where parentid = mytable.parentid and cnt between 1 and 3) That doesn't help: check constraints are evaluated only on insert and update, not on delete. That's why you need an assertion. Jochem
Re: MySQL 5.0.x
On 7/14/05, Joerg Bruehe wrote: Rick Robinson wrote: However, the online manual is not cloned, so while we are building 5.0.9 there can also be new text for 5.0.10 changes that gets integrated into the online manual, and this may become visible earlier than 5.0.9 gets published. Why are the online manuals not cloned and versioned? One of the things I like about the documentation of most other databases compared to MySQL is that it is tightly coupled to a specific version of the software. Instead of searching through the documentation and having to find out which part is valid for which version on each page again, I can at the first step select the version of the database I am using and after that I only get information about that version. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Hi Glen, could I ask a favor regarding the MYSQL list?]
On 7/12/05, Gleb Paharenko wrote: auto_parser wrote: Would you be able to forward the following message to the mysql list. I keep getting bounce-backs with the following: Recipient: mysql@lists.mysql.com Reason:Mail from HELO priv-edtnes27.telusplanet.net rejected because it does not accept bounces. This violates RFC 821/2505/2821 http://www.rfc-ignorant.org/ After contacting rfc-ignorant.org, they assured me it is a mistake in the way mysql is implementing the RFC protocol, and that I'm not on any black lists. The problem is not a blacklist. The problem is that priv-edtnes27.telusplanet.net resolves to a RFC 1918 address. Your message does not make clear which MTA gives this error, but if it is MySQL they are right to reject it. And if it isn't MySQL, you are barking up the wrong tree. As I can't contact the list, I can't resolve the issue. Sure you can: fix your DNS mail setup. Public facing DNS servers should not advertise RFC 1918 addresses. MUAs/MTAs should use the correct hostname in the HELO. I have a request table, from which I'm pulling all records. Each request record has a part number, and for each requested part number, I'd like to query the inventory table for all matching inventory items for that part number, and calculate the potential value of the inventory. Each inventory record has 4 price fields, and it is the HIGHEST value of these fields used in the calculation. As you can tell by running the query, it is incredibly expensive. We have potentially millions of inventory records and 10,000+ requests, so the query is going to take several minutes to complete. Can you show the EXPLAIN output? Which version are you using? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: effective handling of fuzzy dates (MySQL/PHP)
On 6/29/05, me you wrote: For the most part, the data entered is correct and uses the full -MM-DD format, however, I've got numerous dates that are incomplete. For example: an event happened in 1967, but the exact month and day are not known. I've been storing that data, in other forms, as 1967-00-00 but now dropping it into MySQL, when extracting that information using PHP, I'm running into errors where a date like '1967-00-00' would be deemd as having occured in 1966? As far as the data goes, here's some sample dates that I need to be able to handle. AS ENTERED EQUIVILANT LONG FORM --- 1965-08-12 August 12, 1965 1965-08-00 August 1965 1965-00-00 1965 I prefer to store fuzzy dates as both a date and an interval (precision). You can also use a start and an end date instead of a start and an interval. So in the case of your three examples I would store: 1965-08-12 0 August 12, 1965 1965-08-00 1 monthAugust 1965 1965-00-00 1 year1965 Advantage of doing this is that you can also store dates with a precision of 'second half of 1913'. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
On 6/26/05, 2wsxdr5 wrote: Can someone tell me why this query works... SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 Why this construction and not simply: SELECT UserKey FROM Gift GROUP BY UserKey HAVING Count(GiftKey) = 3 And this one doesn't? SELECT UserKey, UserID, FROM User WHERE UserKey IN (SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 ) How do you mean doesn't work? Does it give an unexpected result or an error? User info about the users UserKey is the key Gift list of gifts each user has on their wish list foreign key is UserKey Event ---gift giving events for users. foreign key is UserKey Emails email addresses users have sent a message to about their wish list. UserKey is the foreign key here too. The relationship between user and the other 3 tables is a 1 to many. I have the following query that I need to adjust some. SELECT u.UserKey, UserID, Count(distinct g.GiftKey) gifts, Count(distinct ev.EventKey) events, Count(distinct e.Email) Emails FROM User u NATURAL LEFT JOIN Gift g LEFT JOIN Emails e ON e.Userkey = u.UserKey LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey GROUP BY UserID What I really want is only the users where the gifts count is 3, the Event count is 1, the Emails count is 5 and and only count emails if e.Verified is = 1 I think you mean the following: SELECT * FROM User INNER JOIN ( SELECT UserKey, Count(UserKey) AS gifts FROM Gift GROUP BY UserKey HAVING Count(UserKey) 3 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS events FROM Event GROUP BY UserKey HAVING Count(UserKey) 1 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS emails FROM Emails WHERE Verified = 1 GROUP BY UserKey HAVING Count(UserKey) 5 ) USING (Userkey) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combining several sum queries
On 6/22/05, David Kagiri wrote: When i run the queries below they all work just fine SELECT sum(consultation)+ sum(laboratory) FROM nairobi,familymembers WHERE familymembers.dependantid = nairobi.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM riftvalley,familymembers WHERE familymembers.dependantid = riftvalley.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers WHERE familymembers.dependantid = coast.memberid and familymembers.memberid = AKI1 But i would like to run the querys above as one query that will return a value that will be equivalent to the sum of the values of those queries. i hope its possible. is it? i am using MySQL version 4.1.7 SELECT sum(consultation)+ sum(laboratory) FROM familymembers fm INNER JOIN coast ON fm.dependantid = coast.memberid INNER JOIN riftvalley ON fm.dependantid = riftvalley.memberid INNER JOIN nairobi ON fm.dependantid = nairobi.memberid WHERE fm.memberid = AKI1 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
On 6/21/05, Sebastian wrote: i never understand why people use datetime anyway.. unix timestamp is so much easier to work with. Unix epoch is by definition UTC. Sometimes I want to work with dates in some local timezone. In other databases that have a more complete implementation of the SQL standard you can do really neat tricks with that. Just look at the following examples from PostgreSQL: jochemd= select '2005-06-15 00:00:00'; ?column? 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 17:00:00 jochemd= set TimeZone = 'EST'; jochemd= select '2005-06-15 00:00:00'; ?column? - 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 22:00:00 While I can't use this functionality in MySQL (yet?), I use it enough in other databases to always use a timestamp datatype instead of a epoch to keep code as uniform as possible. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity (big 'O')
On 6/21/05, Dan Bolser wrote: I am interested in the theoretical time / space complexity of SQL queries on indexed / non-indexed data. I doubt this is the right list for theory. Specifically I want to know the complexity of a query that does a 'cross tabulation' SELECT X, SUM(if(Y=1,Z,0)) AS s1, SUM(if(Y=2,Z,0)) AS s2, SUM(if(Y=3,Z,0)) AS s3, ... FROM T1 GROUP BY X; Assuming both X and Y are indexed, how does the complexity grow with increasing 's' (more if clauses). In MySQL: I bet the indexes don't matter and the complexity grows less then linear. The EXPLAIN output will tell you why. Can anyone point me to a good online guide to complexity of SQL? The language SQL or some implementation? Consider looking at PostgreSQL instead of MySQL as your test system. I find the tools to look inside much better: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg17592.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - Joining adjacent rows?
On 6/21/05, comex wrote: I have a table: create table example(time datetime, username varchar(255)); Please tell me you didn't actualy use time as identifier :) timeusername 2005-06-21 15:58:02 user1 2005-06-21 14:58:02 user1 2005-06-21 11:57:51 user2 2005-06-21 10:57:51 user1 2005-06-21 09:57:51 user1 I want it, however, to return: COUNT(*)usernamemaxtime 2 user1 2005-06-21 15:58:02 1 user2 2005-06-21 11:57:51 2 user1 2005-06-21 10:57:51 SELECT username, MAX(time), COUNT(username) FROM ( SELECT username, time, (SELECT COUNT(e2.time) FROM example e2 WHERE e2.time e1.time AND e2.username e1.username) AS usersession FROM example e1) AS intermed GROUP BY username, usersession Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
On 6/17/05, [EMAIL PROTECTED] wrote: There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. That is not the only problem: there is no guarantee the subquery will only return one record. So even if MySQL wouldn't have this limitation you would still run the risk of an executor error when the subquery returns more then one record. Try this: UPDATE table1 a, table2 b SET a.field1 = b.field1 WHERE b.field2 = 'Some Value' AND a.field2 = 'Another Value' Jochem PS Please use single quotes to delimit strings, sticking to the SQL standard makes it easier to read. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Kevin Burton wrote: Jeff Smelser wrote: Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. WOW! That's just insane! This seriously has to be fixed in 5.0 or sooner... Chill out man. It is not like it is returning the wrong results. It is a performance issue and if it is really that bad, everybody will find it during testing. Also, let's not mistake the means for the goal. Using indexes is just a way to solve it and there may be other fixes. The goal is to improve performance. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). If that would break something, another option would be that only insert delayed queries would use the saved timestamp, all other queries would ignore it. Wouldn't it be confusing to have different behaviour of the NOW function for INSERT and INSERT DELAYED statements? And in the most conservative option, there could be a seperate function like QNOW() or something that returned when the query was received by the SQL server rather than the normal NOW() processing. I would prefer this option. Changing it for NOW() as a whole only makes MySQL deviate further from the standard and has backward compatibility issues. Changing it just for NOW() in combination with INSERT DELAYED is potentially confusing. So if you really need a new function, this seems like the right idea. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Keith Ivey wrote: I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next Inconsistencies in the capitalization aren't necessarily introduced by a programmer. Especially when migrating databases or using mapping tools it is often the tools that introduce them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Roger B.A. Klorese wrote: If you're the first person this has bothered He isn't, search the bugbase. (Including reports that are closed because it is documented, without providing a fix, workaround or even recategorizing as feauture request.) and if the limitations don't provide inconsistency with a standard They are. (General rule 2c of section 11.5 of ISO/IEC 9075-2:2003) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Bartis, Robert M (Bob) wrote: Its an email alias. You're asking for help from people you don't even know. You should therefore present your needs clearly and concisely. You should expect there to be questions. You should expect to not always get timely information. you should expect to get wrong answers from time-to-time. I love How to ask questions the smart way: http://www.catb.org/~esr/faqs/smart-questions.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
On 6/9/05, Martijn Tonies wrote: http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... And it depends on which *minor* release it is. 4.1.0 is different from 4.1.1 is different from 4.1.2 is different from 4.1.3 is different from 4.1.6. And it depends on how long ago you sacrificed a goat and the position og te moon. Damn man... The guy who thought this up should be smacked in the head! Should be made to fix it :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, [EMAIL PROTECTED] wrote: Okay, so I understand the idea about one field being the creation time, and the other being the last modified time (which a particularly pedantic application might regard as being one-and-the-same, at time of first-creation) and so I see you might want to _store_ that fact in both fields at time of creation: but even so, there is a fundamental difference of type between the two fields, that remains, that is much more important than the fact you can declare them both as DEFAULT NOW()... Time of creation must never change; or it's existence is useless. Time of modification must _always_ change; or it's existence is useles. Not necessarily. Sometimes you want to differentiate between 'Last user modification' and 'Last process modification'. One way to handle that is in a trigger based upon the group membership of the user making the change. That kind of logic can only really be enforced by external business rules built into the code, anyway, can't it? No. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On 5/29/05, Philip George [EMAIL PROTECTED] wrote: +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | +--++---+--+ how can i also show a grand total for the ticket (without changing the table structure)? i've tried with no success to use SUM() to do this. would i even use SUM()? This has got nothing to do with joins, you can have the same problem in a single table: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update / subquery
On 5/29/05, Lieven De Keyzer wrote: From: Chris Lieven De Keyzer wrote: UPDATE account SET role_id = (SELECT role_id FROM role WHERE rolename = admin) WHERE username = test This gives me an: ERROR 1064 (0): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT role_id from role WHERE rolename=admin)' at line 1 Syntax looks fine to me. There is no guarantee that the subselect will return exactly one row. If there are ppoper constraints MySQL might get to the conclusion that thois subselect can not return more then one row, but how should it handle the case where it returns no row at all? Sub-queries are only supported in 4.1.x+ , I'm guessing that's your problem. I've got mysql-4.1.10 installed. Its too early to think joins on non-keys entirely through, but I think you are looking for something along the lines of: UPDATE account a, role r SET a.role_id = r.role_id WHERE a.user_name=admin AND r.rolename = admin; If not, use a variable. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance on single column index with few distinct values
On 5/28/05, Terence wrote: Master ID is used to distinguish multiple helpdesks. In this table there are 100k records, but only 10 distinct master_id's. ticket_id master_id 1 1 2 1 3 2 4 2 5 3 ... ... SELECT * FROM helpdesk_tickets ht, helpdesk_category_master hcm, helpdesk_sub_category_master hscm WHERE ht.master_id = '1' AND ht.category_id = hcm.category_id AND ht.sub_category_id = hscm.sub_category_id ORDER BY ticket_id DESC LIMIT 0,10 With proper foreign keys all tickets are guaranteed to match categories and subcategories. That means you can push down the LIMIT to just the helpdesk_tickets table: SELECT * FROM ( SELECT * FROM helpdesk_tickets WHERE master_id = 1 ORDER BY ticket_id DESC LIMIT 0,10) NATURAL JOIN helpdesk_category_master NATURAL JOIN helpdesk_sub_category_master With an index on (master_id, ticket_id) this should (nearly) constant-time. I have thought of options such as using temporary tables to just grab the last 10 tickets and then do an IN query, however I need to display totals, so that would require me to run the query again. At which point does running the query again become faster then your current method? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On 5/29/05, Philip George wrote: On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. The join is irrelevant. Your join returns a resultset and you can just pretent that resultset is a single table: SELECT field1, field2, field3 FROM (very complicated join) AS simpletable GROUP BY ... WITH ROLLUP Just copy-pate your join into this and fix the fieldnames. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On 5/29/05, Philip George wrote: On 5/29/05, Philip George wrote: On 5/29/05, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. actually i should say that there are no examples of SUM() or AVG() -- or any of the other GROUP BY functions -- that are used with a join on that page. You can't always solve your problems by following an example. Sometimes you have to recognize the patterns, apply your own knowledge and extend the examples. The MySQL documentation, with its focus on examples instead of formal definitions, isn't the easiest for that, so I would strongly suggest learning SQL from a source that pays more attention to formal definitions. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LENGTH() and UTF-8
On 5/9/05, Andreas Steichardt wrote: We are storing UTF-8 data in out mysql database and we need to get the length of the data. But length() doesn't return the number of characters but the pure number of bytes. Look at OCTET_LENGTH() and CHAR_LENGTH(). (While OCTET_LENGTH() is a synonym, it is the SQL standard way of getting the length of a string in bytes.) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: amPiguous!
On 5/7/05, Dan Bolser wrote: Why are columns included in the join between two tables ambigious? Because MySQL does not follow the SQL standard (ISO/IEC 9075-2:2003). select pk from a inner join b using (pk); ERROR 1052 (23000): Column 'pk' in field list is ambiguous!!! Is this a bug, or is it like this for a reason? It drives me nuts, because it is not ambigious (as far as I can tell). Am I a fool? It is not ambiguous according to the SQL standard. If this behaviour of MySQL is documented it is an omission in the MySQL implementation that you get the error. If this behaviour is not documented, it is a bug. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: amPiguous!
On 5/7/05, Chris wrote: Somethign else to think about as well, look at this slight modification: select pk from a left join b using (pk); Now, it's not likely this is a valid query for your table structure It is very likely it is. It is even an example in the MySQL manual. but, in this instance, a.pk and b.pk are not necessarily the same. b.pk could potentially be NULL while a.pk was not There is nothing ambiguous about this example. The SQL standard is very clear about the way field names should be resolved in in joins. In this case the relevant quote is: quote 7.7 joined table (..) Syntax Rules (..) 7) If NATURAL is secified or if a join specification immediately containing a named columns join is specified, then: (..) d) If there is at least one corresponding join column, then let SLCC be a select list of derived columnss of the form COALESCE ( TA.C, TB.C ) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in RT1. /quote ISO/IEC 9075-2:2003 In a named columns join (i.e. a join with the USING keyword) every column named in the join is only present once in the resultset. And since the selection mechanism for the value uses COALESCE there is absolutely no ambiguity in which value gets choses: never the NULL. Dan is absolutely correct to expect his syntax to work. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: amPiguous!
On 5/7/05, Dan Bolser wrote: On Sat, 7 May 2005, Jochem van Dieten wrote: On 5/7/05, Dan Bolser wrote: select pk from a inner join b using (pk); ERROR 1052 (23000): Column 'pk' in field list is ambiguous!!! Is this a bug, or is it like this for a reason? It drives me nuts, because it is not ambigious (as far as I can tell). Am I a fool? It is not ambiguous according to the SQL standard. If this behaviour of MySQL is documented it is an omission in the MySQL implementation that you get the error. If this behaviour is not documented, it is a bug. Perhaps the omission is documented? Should I try to log this as a bug? Always get it into the system. Even if it is considered not a bug but a feature I think it warrants a documentation update. But the problem with fixing this is that it is not backward compatible and will break for everybody who qualifies his field names. Out of interest, what would happen in the following case... select pk from a inner join b on a.pk = b.pk+1; Would that be 'correctly' ambigious according to the sql specification? That would indeed be ambiguous according to the SQL standard. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert wrote: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? Because the SQL standard says so. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert wrote: From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P I consider it a good enough explanation of why MySQL doesn't allow it. As to why the SQL standard doesn't allow it: NULL doesn't fit particularly well in relational theory and there has probably been considerable pressure from certain vendors (imagine the problems when an empty string is indistinguishable from a NULL so both '' = '' and NULL NULL must be true, but now not just for some wacky varchar but for your primary key). Just speculation of course :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] wrote: I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? Do this in whichever scripting language you are using. You can do this in SQL if your list of IDs is monotomously increasing (no gaps), but it is rather ugly: SELECT a.ID, a.Data, b.ID, b.Data FROM table a LEFT JOIN table b ON (a.ID + Ceiling((SELECT MAX(ID) FROM table) / 2) = b.ID) WHERE a.ID = Ceiling((SELECT MAX(ID) FROM table) / 2) ORDER BY a.ID Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
On Tue, 1 Mar 2005 18:09:37 -0600, Alfredo Cole wrote: El Mar 01 Mar 2005 17:32, Gary Richardson escribió: InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; This is the InnoDB related stuff from my.cnf: innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=192M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=32M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 I am using the syntax as you describe it. If the transactions you are using insert thousands of records (or more) it is probably faster to leave the default value for innodb_flush_log_at_trx_commit. It is recommended anyway for data security. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql vs postgresql
On Fri, 25 Feb 2005 11:21:26 -0600, mos wrote: http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/MySQL-PostgreSQL_features.html There is a reason this page was removed from the MySQL site: some of it was never correct in the first place, and the rest was severly outdated. Don't you think it is childish to link to documentation from 2003? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
On Wed, 23 Feb 2005 00:22:55 +0200, Heikki Tuuri wrote: a buggy fsync() in Linux is one of the possible reasons here. If an InnoDB tablespace gets corrupt in a power outage, it is most probably caused by a bad fsync() implementation or configuration in the operating system or hardware. An fsync() call should write the data physically to disk or to a battery-backed, non-volatile disk cache. If it fails to do so, then any database, PostgreSQL or anything, can get corrupt. Can they? Even if the absolute ordering of writes is maintained, only full pages are written and each page has a checksum? It seems to me that then you might lose transactions that have not yet committed to disk, but your database would not get corrupt. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Plus sign doesn't concatenate strings in MySQL?
On Sat, 15 Jan 2005 11:11:05 -0500, Robert Alexander wrote: Each language is going to have its own personality. If they all did things the same way, we wouldn't have the wealth of different ones to choose from. DBMS's are not languages, they are implementations. Might not be a good idea, Dave, to take MS products as examples of what accepted standards are. MS has a long history of lack of respect for established standards. I suspect MySQL is more ANSI compliant than MS SQL Server. Can you substantiate that suspicion? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Plus sign doesn't concatenate strings in MySQL?
On Sat, 15 Jan 2005 11:37:02 -0500, Dave Merrill wrote: This is my first foray into different flavors of sql, and I'm discovering how incompatible they really are. I expected that core basics would be the same, with each manufacturer adding some proprietary extensions, and failing to support a (hopefully small) subset of standard features. DBMS's are slowly getting there. What you are (I am) seeing is that whenever vendors introduce new features that are in the standard, they follow the syntax from the standard. Yukon will have SQL standard recursive queries, MySQL will have a standard implementation of SQL-PSM, PostgreSQL got a standard information schema etc. It is not going as fast as I would like and vendors are especially reluctant to rewrite existing features to use standard syntax (in casu MySQL and the horrible overloading off timestamp behaviours), but it isn't that long ago that we wouldn't even consider outer join syntax as standard. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create sequence
On Thu, 13 Jan 2005 11:21:31 -0600, Scott Purcell wrote: I would like to create a sequence object in mysql that I could use in multiple tables through a application I am developing. Sequences are currently not supported in MySQL. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Sql question
On Fri, 03 Dec 2004 10:58:30 -0700, Steve Grosz wrote: I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 Use cfqueryparam for all your parameters. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. The only way to do that is client side encryption. Otherwise a sufficiently privileged user can still see the data. (Even if it is just by sniffing the network traffic or attaching a custom debugger to the running process.) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 13:07:11 +0100, Jonas Ladenfors wrote: Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access that seems interesting. Here it is (not MySQL but the mind-work might be interesting). http://www.securityfocus.com/infocus/1743 It does not meet your initial requirement that there would be no root user with the ability to access the data. But if that is OK most databases have it one way or another, usually through a view + procedure. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List annoyance
On Wed, 10 Nov 2004 07:42:29 +, Stephen Moretti (cfmaster) wrote: Why is this list reply to sender and not reply to list? Why don't you read the FAQ? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering data from postgresql to MySQL
On Mon, 18 Oct 2004 18:08:24 +0800, Patrick Hsieh wrote: I am planing to transfer data from postgresql to mysql. Is there any useful tools, scripts or utilities to achieve this? pg_dump First dump the schema, edit that until you have something MySQL understands. Then dump the data using the -d option so you have full inserts instead of the usual COPY syntax and feed the file to the MySQL command line client. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
On Fri, 15 Oct 2004 10:26:22 -0600, Steve Grosz wrote: Ok, with leaving the : off the end, and just typing telnet web-server2 3306 I get some jibberish on the screen, and a 'connection lost' message after a few seconds. That's coming from a machine on the same side of the firewall as the Sql server. For the other IIS server and Coldfusion server to the Sql server, still get a 'can't connect message'. I'm pretty sure its not a firewall issue, but I'll have to take another look. Repeat that telnet test from the machine running ColdFusion. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding DSN into Coldfusion Admin?
On Fri, 15 Oct 2004 11:36:23 -0600, Steve Grosz wrote: I also get the jibberish, and a connection lost error message. Is it something in MySql server that I'm not setting up correctly? That probably means you can reach the MySQL server and the problem is on OSI-layer 5-8 :-) Could you go into the ColdFusion Administrator and go to the Settings Summary. From there, copy the settings and post them together with the exact text of the error message. (And please, just make a literal copy and don't obfuscating IP addresses etc., they are RFC 1918 addresses so we can't reach them anyway.) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On 05 Oct 2004 11:46:18 +0200, Harald Fuchs wrote: Martijn Tonies writes: MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) Nope. A standards-compliant database is _required_ to store the structure of its objects in its internal information_schema There is no data stored in the INFORMATION_SCHEMA at all: quote 4.2 Introduction to the Information Schema. The views of the Information Schema are viewed tables /quote ISO/IEC 9075-11:2003 not some SQL string. The VIEWS view in the INFORMATION_SCHEMA is derived from the VIEWS base table in the DEFINITION_SCHEMA. Part of the definition of the that reads: quote 6.66 VIEWS base table (..) Definition CREATE TABLE VIEWS ( TABLE_CATALOGINFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, VIEW_DEFINITION INFORMATION_SCHEMA.CHARACTER_DATE, /quote ISO/IEC 9075-11:2003 So what would VIEW_DEFINITION store exactly if not the query expression that defines a view? Since the is no requirement to have an accessible DEFINITION_SCHEMA there may be a mechanism to recreate the definition on the fly from other information, but the same goes for the other view related base tables in the DEFINITION_SCHEMA. I see no requirement to store only the structure and not the SQL string. While I don't really care about the way the structure of a view is returned, I would very much like for it to be without those backticks. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On 05 Oct 2004 12:02:44 +0200, Harald Fuchs wrote: Martijn Tonies [EMAIL PROTECTED] writes: I was asking if the view-source can be stored, so that it can be retrieved the way I created it. Do you agree or disagree? I disagree. A proper information_schema implementation is much more than a stored SQL string - it's a standardized way to access meta information about your DB objects. Thus it must parse the SQL DDL strings and store its meaning. Additionally storing the SQL string _as entered_ would be redundancy. No, it is not redundant. If I look at the information in the VIEW_COLUMN_USAGE, VIEW_ROUTINE_USAGE, VIEW_TABLE_USAGE and VIEWS views in the INFORMATION_SCHEMA (or their equivalent base tables in the DEFINITION_SCHEMA), they do not contain sufficient information to reconstruct anything but trivial views. SHOW CREATE VIEW could be implemented by reconstructing some standard representation of SQL DDL, but this would not always be exactly what you entered. I agree. One could imagine that views are stored in whatever format comes out of the parser so they are easily integrated into the execution plan at runtime. Obviously when you convert them back to a human readable query expression that query expression will be formatted differently. That seems perfectly reasonable to me. But since the INFORMATION_SCHEMA simply does not have the information required to reconstruct a view, you need something else too. And just as it would be perfectly reasonable to use the query tree, it would also be perfectly reasonable to take the original query expression for that. Both have advantages and disadvantages. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On Tue, 5 Oct 2004 00:27:45 +0530, Eldo Skaria wrote: I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. But that doesn't make it right. Specifically, I seem to remember from the SQL standard that the use of database.table.field is undesired (or schema.table.field if you don't use MySQL) and instead database.table should be aliassed. In that case it should be: CREATE VIEW test.myview2 AS select alias.c1 AS t0 from test.t alias; Further, I would like to second Martijn's opinion regarding backticks. It is bad enough that the SQL standard double quote isn't used, let's not make it worse by including unwanted backticks. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Licensing issues
On Tue, 31 Aug 2004 08:23:01 -0400, Scott Hamm [EMAIL PROTECTED] wrote: Since I'm trying to implement a new database replacing Access Database for intranet environment at my work, do I still need a license since I am not exporting my works out of my company. If you don't redistribute, you don't need a license. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
On Tue, 31 Aug 2004 18:47:57 -0400, Jacob, Raymond A Jr wrote: http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid So far they are. then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. No. None of this allows Stored Procedures to call back to MySQL and update some data in a totally different table in the same transaction. They are still only functions that can only work with what is passed to them, they can not reach out and get additional input from elsewhere in the database. MySQL 5 has the foundations for a stored procedure implementation, but it will be a while. If you need stored procedures now, you need a different database. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct date query syntax
On Thu, 26 Aug 2004 11:31:46 -0500, Yong Wang [EMAIL PROTECTED] wrote: I have a database which contains date attribute in string format (like 2004-08-12). I want to genearte a report based on period time. I use the syntax: date1 ='2004-08-12' date2='2004-08-18' SELECT * FROM account WHERE (TO_DAYS(date) = TODAYS(date1)) and (TO_DAYS(date) = TO_DAYS(date2)); Is date the name of your field? Change it, it is a reserved word in SQL. The report script complains the condition after WHERE clause. The reason I use TO_DAYS is that I want to convert string date data into integer for comparison. Can I use TO_DAYS() like this way ? Even if you can, don't: it isn't needed. Just use a plain BETWEEN predicate without functions: SELECT * FROM account WHERE date BETWEEN date1 AND date2 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by with one exception
On Wed, 11 Aug 2004 23:40:27 -0700, Scott Haneda [EMAIL PROTECTED] wrote: I made a mailing list archiver, I thread discussions by subject. I chose to not use message-id's since so many people hijack threads. Why not select/group on subject, and then thread on messageid. Given this case: Subject ID RE: Order by with one exception 1 RE: Order by with one exception 2 RE: Order by with one exception 3 Order by with one exception 4 RE: Order by with one exception 5 RE: Order by with one exception 6 RE: Order by with one exception 7 As you can see, these are in correct order, but in this case, I want to push the one without the Re: to the top. I can not just order by subject, id, since not a subject could start with a letter after R. Suggestions? SELECT * FROM table ORDER BY subject NOT LIKE 're:%', ID Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Wed, 11 Aug 2004 10:00:32 -0500, Josh Trutwin wrote: On Tue, 10 Aug 2004 23:34:49 +0200 Jochem van Dieten wrote: On Tue, 10 Aug 2004 16:00:12 -0500, Josh Trutwin wrote: MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? Why in the world do I need to remember SHOW TABLES? Why can't the standard information schema work? :-) Ah, because SHOW TABLES and exit or quit makes sense SHOW TABLES does not make sense. How are you going to join the output of SHOW TABLES against the output of SHOW COLUMNS and SHOW INDEXES? SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense. And as for easy remembering: I prefer to remember just one standard, instead of the idiosyncracies of each product. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
