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: Help with an SQL query
Rhino wrote: I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino Wow, excellent explanation. By the way, my apologies... I am using MySQL 5.0.15. I would be interested in knowing how to use temp tables to simulate subqueries though. I found out that the sql statement, although it returns the correct result, does not work in a view because MySQL Views does not allow subqueries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Heikki: What will become of InnoDb once MySQL license runs out
-Original Message- From: Fabricio Mota [mailto:[EMAIL PROTECTED] Sent: zaterdag 5 november 2005 3:56 To: Ezra Taylor Cc: mysql@lists.mysql.com Subject: RES: Heikki: What will become of InnoDb once MySQL license runs out To Mysql users: Just to remind you all, Oracle is a business that ex- pects 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 then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor Is there anyone who can shed some light on this without the anti-Orcacle hysteronics? What will become of InnoDb once MySQL license runs out, is still a pertinent question. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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: Problem with load data and NULL
In article [EMAIL PROTECTED], Barbara Deaton [EMAIL PROTECTED] writes: All, I need help with using load data to load a text file that is created by another application. I have a text file that contains 2 columns, date and smallint and 2 rows: d,e 2005-12-31, ,2 In the above example the first row, column e should be NULL and the second row column d should be NULL. The application that outputs this text files does not output a \N for null it leave the data empty. So when I load the data into MySQL, even though I have created the table as: CREATE TABLE `a` (`d` date default NULL,`e` smallint(6) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 The Load Data commands inserts a 0 for my values so the table looks like: ++--+ | d | e| ++--+ | 2005-12-31 |0 | | -00-00 |2 | ++--+ What can I say on the LOAD DATA command so the data is loaded as NULL and not 0? You could use a temporary table for that: CREATE TEMPORARY TABLE tmp ( d TEXT NOT NULL, e TEXT NOT NULL ); LOAD DATA LOCAL INFILE 'yourfile' INTO TABLE tmp FIELDS TERMINATED BY ','; INSERT INTO a (d, e) SELECT CASE d WHEN '' THEN NULL ELSE d END, CASE e WHEN '' THEN NULL ELSE e END FROM tmp; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN problem
I have the following tables: languages language_id int, language_title varchar(80) objects object_id int object_language_id int object_title varchar(100) I want to select ALL available languages, and match the translated object (if it is exists) for a specific object_id. If it does not exists for that specific language_id, I want to have NULL. Because I'm using mysql 4.0, no subqueries allowed. Is there any way to rewrite the following? SELECT language_id, language_title, (SELECT object_title FROM objects WHERE object_id = ? AND object_language = l.id) AS object_title FROM languages l Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN problem
2005/11/5, Guy Brom [EMAIL PROTECTED]: I have the following tables: languages language_id int, language_title varchar(80) objects object_id int object_language_id int object_title varchar(100) I want to select ALL available languages, and match the translated object (if it is exists) for a specific object_id. If it does not exists for that specific language_id, I want to have NULL. Have a try with : Select language_id,language_title,object_id,object_title FROM languages LEFT JOIN objects ON objects.object_language_id=languages.language_id -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
I'm glad the explanation helped. I figured that the solution alone wouldn't be very useful if it wasn't explained since it is not obvious to most people how correlated queries work :-) I really wasn't trying to center you out with my rant about MySQL version. It's been a long-running frustration for me; I've tried to urge people to give this information in their notes to the list but an awful lot of people never think to mention their version. Then, an already lengthy reply gets even longer as you try to explain: If you have Version 3.x, the solution is this If you have Version 4.x the solution is this. If you have Version 5.x, the solution is this. You get the picture. Okay, let me explain the temp tables approach now. Most subqueries are not correlated so the subquery gets done once and then its result gets plugged into the outer query. For example, given a table called Employee in the Sample database which has one row for each employee and each row contains an employee number, last name, and salary, this query will return the employee number and last name of every employee who makes more than the average salary for all employees in the table: --- select empno, lastname from Sample.Employee where salary (select avg(salary) from Sample.Employee); --- If subqueries are supported, as in Version 4.1.x or above of MySQL, you just run that exact query and you will get a list of all of the people who make more than the average salary. However, if you are using an older version of MySQL which doesn't support subqueries, you need to use a temporary table approach. Basically, you create a temporary table that contains the same results that the subquery would have given you, then join it to the outer query. [Many, but not all, subqueries can be rewritten as joins.] For example: --- #Create the temporary table that will contain the average salary for all employees. create table if not exists temp_average_salary (average_salary decimal(9,2)); #Populate the temporary table with the average salary for all employees. insert into temp_average_salary(select avg(salary) from Sample.Employee); #Do a join of the employee table and the temporary table to obtain only employees who make #more than the average salary. select empno, lastname, salary from Sample.Employee e inner join temp_average_salary t where e.salary t.average_salary; --- This would give the same result as the original solution that used subqueries although there is obviously more work to do in accomplishing the desired result. Initially, I was almost certain that this approach could not be made to work for your problem due to its correlated nature but a couple of experiments persuaded me that it was possible after all. It's actually quite similar to the example I gave above, except that this time the temporary table is not just a single value on a single row but a table containing one row for each VBS_id, showing the max (latest) date for any row containing that VBS_id. Here's the definition of the table and the correct method of populating the table: --- create table if not exists temp_table (VBS_id int not null, max_date date not null); insert into temp_table (select VBS_id, max(date) from VBS_table group by VBS_id); --- If you display the contents of the temporary table, you get: --- VBS_idmax_date 112005-10-08 122005-10-08 --- Now, you simply join the VBS_table and the temp_table, as follows: --- select v.VBS_id, v.date, v.weight from VBS_table v inner join temp_table t on v.VBS_id = t.VBS_id and v.date = t.max_date; --- The result is exactly what you wanted: --- VBS_iddateweight 112005-10-0811.52 122005-10-0810.50 --- You could apply this solution to versions of MySQL that don't have subquery support; just remember that you need to re-create the temp_table immediately before you do the join each time; otherwise, you are going to determine whatever weights were in effect whenever you first populated the temp_table, not the current weights. In your case though, you can make life better for yourself. Since you have view support, you can simply create a view that is the equivalent to the temp_table and then join the view to the VBS_table to get the data you want. Since the view is always up-to-date, you eliminate the need to have the temp_table at all. Something like this: --- create view VBS_max_dates (VBS_id, max_date) as select VBS_id, max(date) from VBS_table group by VBS_id; select v.VBS_id, v.date, v.weight from VBS_table v inner join VBS_max_dates z on v.VBS_id = z.VBS_id and v.date = z.max_date; --- Note that the view is only created once, not each time you are about to do the join!! One other solution jumps to mind that should be just as good. Instead of creating temp tables or views, why not just put the original correlated subquery that I gave you into a stored procedure? Then, all you'd need to do is call that stored procedure every time you need
Re: LEFT JOIN problem
Have a look at the reply I just wrote for Gobi, entitled Re: Help with an SQL query. You'll find an explanation there for how to use temporary tables and joins instead of subqueries in MySQL versions that don't support subqueries. Rhino - Original Message - From: Guy Brom [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 05, 2005 10:28 AM Subject: LEFT JOIN problem I have the following tables: languages language_id int, language_title varchar(80) objects object_id int object_language_id int object_title varchar(100) I want to select ALL available languages, and match the translated object (if it is exists) for a specific object_id. If it does not exists for that specific language_id, I want to have NULL. Because I'm using mysql 4.0, no subqueries allowed. Is there any way to rewrite the following? SELECT language_id, language_title, (SELECT object_title FROM objects WHERE object_id = ? AND object_language = l.id) AS object_title FROM languages l Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
Bruce Martin wrote: When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; First, your hostname is blank. That won't work. You need to either specify a hostname, or use the wildcard character, '%'. See the manual for details http://dev.mysql.com/doc/refman/5.0/en/connection-access.html. Second, why are you making testuser equivalent to root? Don't give a user any more privileges than necessary. For a test user, I'd suggest starting with just GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost' IDENTIFIED BY 'some_password'; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? What makes you think it didn't work? You didn't get an error message. Instead, you got Query OK. Looks like it worked, to me. To test it further I try to log in as testUser but it tells me access denied for user [EMAIL PROTECTED] That's not the whole error message. Please show us your exact comand, and copy/paste the exact error message. We could try to guess what's wrong (probably there is no [EMAIL PROTECTED]), but it's better not to guess. Even if I grant the testUser @ localhost. No spaces. Again, show us the exact GRANT command, the exact connection command, and the exact error message. I can look in the user table and sure enough user [EMAIL PROTECTED] is there. Better yet, you can verify a user's privileges with the SHOW GRANTS command: SHOW GRANTS FOR [EMAIL PROTECTED]; Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
Mark wrote: -Original Message- From: Bruce Martin [mailto:[EMAIL PROTECTED] Sent: zaterdag 5 november 2005 3:58 To: mysql@lists.mysql.com Subject: Add New User When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? Because, afterwards, you forgot to say: FLUSH PRIVILEGES; Perhaps? - Mark No. FLUSH PRIVILEGES is not needed with GRANT http://dev.mysql.com/doc/refman/5.0/en/grant.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
