Re: mysql-5.1 64bit and windows 7

[Reindl Harald]

Am 01.01.2014 23:30, schrieb Elim Qiu:
 I installed mysql 5.1 to windows 7 using mysql-5.1.73-winx64.msi and got
 confused on how to relocate the datadir.
 
 my basedir was F:/MySQL and datadir was F:/MySQL/data
 
 It works fine and I'm trying to relocate the datadir:
 
 (0) stop mysql service
 (1) copy F:/MySQL/data to F:/DBData/MySQL/data

maybe you better have *moved* the folder

 (2) modify F:/MySQL/my.ini change the line
 datadir=F:/MySQL/Data/
 into
 datadir=F:/DBData/MySQL/Data/
 (3) restart the service and it says access permission denied 

compare permissions / owner / ACL's
what are the permissions of the *parent folders*

 even I used cmd window with win admin privileges

this does not matter in context of a service
fankly a sane OS would even forbid runnign a service as admin

 I've doe such on XP 32bit mysql many times without any problem. So what
 wrong above?

that even microsoft improved their handling of permissions



signature.asc
Description: OpenPGP digital signature

Re: Mysql 5.1 union with group by for results

[Jesper Wisborg Krogh]

Hi Machiel,

On 8/11/2013 20:04, Machiel Richards wrote:

Good day all

   I am hoping someone can assist me in the following.

One of our servers were running mysql 5.0 still and as part of 
a phased upgrade route we have upgraded to version 5.1.


However since the upgrade, the query below gives us an error 
stating that the syntax is incorrect and I simply cant seem to find 
out what is actually wrong as all tests and changes have been giving 
us the same.


I have tried many suggestions from the net but to no avail.

The query is as follows:


Using a shorter but equivalent query, you have:

   (SELECT t.id, t.name, SUM(val) FROM t1 t)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t)
   GROUP BY t.id, t.name;

That does not work in 5.0 either (at least in 5.0.96):

   ERROR 1064 (42000): 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 'GROUP BY t.id, t.name' at line 4


The issue is that you are trying grouping the entire UNION result, but 
at that point there is no SELECT any longer - there is just the result 
set. You are also referencing tables that exists inside each of the 
SELECT statements, but at the time the GROUP BY is reached, there are no 
tables. Note that as written the two SELECT parts will also give 
non-deterministic results as you have an aggregate function but no GROUP 
BY, so the values of id and val1 will be random.


What you probably want instead is either:

   (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);

or

   SELECT a.id, a.name, SUM(val)
  FROM (
(SELECT t.id, t.name, t.val FROM t1 t)
   UNION
(SELECT t.id, t.name, t.val FROM t2 t)
   ) a
 GROUP BY a.id, a.name;


On a side note:



 AND SUBSTRING(t.Day,1,7) = '2013-08'
 AND SUBSTRING(t.Day,1,7) = '2013-11')


Assuming t.Day is a date, datetime, or timestamp column, you can rewrite 
that WHERE clause to something like (depending on the exact data type):


   t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'

or

   t.Day = '2013-08-01 00:00:00' AND t.Day  '2013-12-01 00:00:00'


That way you will be able to use an index for that condition.

Best regards,
Jesper Krogh
MySQL Support

Re: Mysql 5.1 union with group by for results

[Johan De Meersman]

- Original Message -
 From: Machiel Richards machiel.richa...@gmail.com
 
 ERROR 1064 (42000): 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 'GROUP BY t.AccountID,


I suspect your query has never been doing what you think at all, and you need 
to 


  select [fields] from (
select fields1 blahblah
UNION
select fields2 blahblah) unionized
  group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

[Machiel Richards]

Happiness, that gave me what I was looking for. Thank you Johan.

I have tested the option you gave me but my brackets was in the wrong place.




On 08/11/2013 13:23, Johan De Meersman wrote:

- Original Message -

From: Machiel Richards machiel.richa...@gmail.com

ERROR 1064 (42000): 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 'GROUP BY t.AccountID,


I suspect your query has never been doing what you think at all, and you need to


   select [fields] from (
 select fields1 blahblah
 UNION
 select fields2 blahblah) unionized
   group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

[Machiel Richards]

Hi Jesper

I was just discussing this with the development manager now and the 
following was noted.


- The query was written for mysql 4.0 originally and it seems 
that in version 5.0 they had enabled some legacy support stuff ( I am 
not too familiar with this as it is before my mysql time ;-) ).


- I have now explained to them what the problem is and they 
will be working with the developers to rewrite all these queries.


Regards
Machiel.





On 08/11/2013 13:27, Jesper Wisborg Krogh wrote:

Hi Machiel,

On 8/11/2013 20:04, Machiel Richards wrote:

Good day all

   I am hoping someone can assist me in the following.

One of our servers were running mysql 5.0 still and as part 
of a phased upgrade route we have upgraded to version 5.1.


However since the upgrade, the query below gives us an error 
stating that the syntax is incorrect and I simply cant seem to find 
out what is actually wrong as all tests and changes have been giving 
us the same.


I have tried many suggestions from the net but to no avail.

The query is as follows:


Using a shorter but equivalent query, you have:

   (SELECT t.id, t.name, SUM(val) FROM t1 t)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t)
   GROUP BY t.id, t.name;

That does not work in 5.0 either (at least in 5.0.96):

   ERROR 1064 (42000): 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 'GROUP BY t.id, t.name' at line 4


The issue is that you are trying grouping the entire UNION result, but 
at that point there is no SELECT any longer - there is just the result 
set. You are also referencing tables that exists inside each of the 
SELECT statements, but at the time the GROUP BY is reached, there are 
no tables. Note that as written the two SELECT parts will also give 
non-deterministic results as you have an aggregate function but no 
GROUP BY, so the values of id and val1 will be random.


What you probably want instead is either:

   (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);

or

   SELECT a.id, a.name, SUM(val)
  FROM (
(SELECT t.id, t.name, t.val FROM t1 t)
   UNION
(SELECT t.id, t.name, t.val FROM t2 t)
   ) a
 GROUP BY a.id, a.name;


On a side note:



 AND SUBSTRING(t.Day,1,7) = '2013-08'
 AND SUBSTRING(t.Day,1,7) = '2013-11')


Assuming t.Day is a date, datetime, or timestamp column, you can 
rewrite that WHERE clause to something like (depending on the exact 
data type):


   t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'

or

   t.Day = '2013-08-01 00:00:00' AND t.Day  '2013-12-01 00:00:00'


That way you will be able to use an index for that condition.

Best regards,
Jesper Krogh
MySQL Support




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: MySQL 5.1: incorrect arithmetic calculation

[Rick James]

They are both right.  It is a matter of how many decimal places you want to 
display:

mysql SELECT 365 * 1.67 * ( 1 - 0.10);
+--+
| 365 * 1.67 * ( 1 - 0.10) |
+--+
| 548.5950 |
+--+
1 row in set (0.00 sec)

mysql SELECT ROUND(365 * 1.67 * ( 1 - 0.10), 2);
++
| ROUND(365 * 1.67 * ( 1 - 0.10), 2) |
++
| 548.60 |
++
1 row in set (0.00 sec)

 -Original Message-
 From: Alex Keda [mailto:ad...@lissyara.su]
 Sent: Thursday, February 14, 2013 9:36 PM
 To: mysql@lists.mysql.com
 Subject: MySQL 5.1: incorrect arithmetic calculation
 
 bkp0# mysql h5000_bill
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 1643184
 Server version: 5.1.68-log FreeBSD port: mysql-server-5.1.68
 
 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
 reserved.
 
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective owners.
 
 Type 'help;' or '\h' for help. Type '\c' to clear the current input
 statement.
 
 mysql set names utf8;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql SELECT * FROM `WorksCompliteAgregate` WHERE (`ContractID` =
 10369
 AND `Month` = 497);
 +--++---+---+--++--
 ---++
 | ID   | ContractID | Month | ServiceID | Comment  | Cost   |
 Discont | Amount |
 +--++---+---+--++--
 ---++
 | 10551851 |  10369 |   497 | 1 | №20440 |   1.67 | 0.10
 |365 |
 | 10551854 |  10369 |   497 | 2 | №20441 | 150.00 | 1.00
 |  1 |
 +--++---+---+--++--
 ---++
 2 rows in set (0.00 sec)
 
 mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM
 `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);
 ++
 | Summ   |
 ++
 | 548.59 |
 ++
 1 row in set (0.00 sec)
 
 mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM
 `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);
 ++
 | Summ   |
 ++
 | 548.594985 |
 ++
 1 row in set (0.00 sec)
 
 mysql SELECT 365 * 1.67 * ( 1 - 0.10);
 +--+
 | 365 * 1.67 * ( 1 - 0.10) |
 +--+
 | 548.5950 |
 +--+
 1 row in set (0.00 sec)
 
 mysql
 ===
 
 but, my desktop calculator gives the result 548.60
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: incorrect arithmetic calculation

[Alex Keda]

15.02.2013 15:07, Alex Keda пишет:

OK. But, how about:
mysql SELECT 365 * 1.67 * ( 1 - 0.10);
+--+
| 365 * 1.67 * ( 1 - 0.10) |
+--+
| 548.5950 |
+--+
1 row in set (0.00 sec)

mysql
??


sorry, I'm too many work... =)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: incorrect arithmetic calculation

[Johan De Meersman]

- Original Message -
 From: Alex Keda ad...@lissyara.su
 

 mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM 
 `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);

Based off the select you printed, this comes to EXACTLY 548.595 for the first 
row and 0 for the second row.


 mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM 
 `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);

The more detailed result here, though, seems to suggest that there's a longer 
fraction in your table than is printed by your select. Would your column happen 
to be a Float?

 but, my desktop calculator gives the result 548.60

Which is the correct rounding for 548.595. Check if your column is a float, and 
if it is, go google for floating point mathematics. They do not work the way 
you think they do. Use decimal(n,m) for money - or any discrete number, for 
that matter. Floats are not exact values, they are APPROXIMATE values.

https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html may also give 
you an idea of what goes wrong.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: incorrect arithmetic calculation

[Alex Keda]

15.02.2013 14:43, Johan De Meersman пишет:


- Original Message -

From: Alex Keda ad...@lissyara.su

mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM 
`WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);

Based off the select you printed, this comes to EXACTLY 548.595 for the first 
row and 0 for the second row.



mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM 
`WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);

The more detailed result here, though, seems to suggest that there's a longer 
fraction in your table than is printed by your select. Would your column happen 
to be a Float?


but, my desktop calculator gives the result 548.60

Which is the correct rounding for 548.595. Check if your column is a float, and 
if it is, go google for floating point mathematics. They do not work the way 
you think they do. Use decimal(n,m) for money - or any discrete number, for 
that matter. Floats are not exact values, they are APPROXIMATE values.

https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html may also give 
you an idea of what goes wrong.



OK. But, how about:
mysql SELECT 365 * 1.67 * ( 1 - 0.10);
+--+
| 365 * 1.67 * ( 1 - 0.10) |
+--+
| 548.5950 |
+--+
1 row in set (0.00 sec)

mysql
??

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: incorrect arithmetic calculation

[Johan De Meersman]

- Original Message -
 From: Alex Keda ad...@lissyara.su
 To: mysql@lists.mysql.com
 Sent: Friday, 15 February, 2013 12:16:18 PM
 Subject: Re: MySQL 5.1: incorrect arithmetic calculation
 
 sorry, I'm too many work... =)

Heh :-) I was thinking, why would that not be correct? It's exactly what the 
desktop calculator gives upon copy/pasting the arithmetic.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: incorrect arithmetic calculation

[misiaq]

From: Alex Keda ad...@lissyara.su
To: mysql@lists.mysql.com; 
Sent: 6:37 Piątek 2013-02-15
Subject: MySQL 5.1: incorrect arithmetic calculation

( ... cut ...)
 
 but, my desktop calculator gives the result 548.60
 

1. your desktop calculator is wrong
2. correct result is 548.595, variations (548.59 and 548.594985) are related to 
various data types and rounding related issues while multiplying and dividing 
by 100

http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html 

Regards,
m



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.1: Views, queries, updates and performance issues

[Arthur Fuller]

At first blush, your problem would appear to concern the lack of index-use.
 That's where I would begin my investigation. It might be painstaking, but
I would do something like this:

For each view
Look at the Join(s) and see what columns are being joined
Look at the tables and see what columns are being indexed
If any columns are not indexed that should be, create said index(es)
Next view

Of course, this process could be optimized by looking at the views in terms
of their frequency of use.

Finally, you didn't mention what sort of front end you're using. It's
possible that you might benefit by using stored procedures rather than
views. That switch would of course cost you some time invested in changing
the front end to pass explicit parameters.

Hope this helps,
Arthur

On Thu, Dec 29, 2011 at 12:50 PM, Bruce Ferrell bferr...@baywinds.orgwrote:

 Hi all,

 I've got some semi-general questions on the topics in the title.  What I'm
 looking for is more in the line of theory than query specifics.  I am but a
 poor peasant boy.

 What I have is an application that makes heavy use of views.  If I
 understand views correctly (and I may not), views are representations of
 queries themselves. The guy who wrote
 the app chose to do updates and joins against the views instead of against
 the underlying tables themselves.

 I've tuned to meet the gross memory requirements and  mysqltuner.pl is
 saying that 45% of the joins are without indexes. With the slow query logs
 on and queries_without_indexes,
 I'm frequently seeing updates that often take more that 2 seconds to
 complete... Often MUCH longer (how does 157 seconds grab you?).

 So, with that background, what would you do next and is it possible this
 use of views, in this way is a significant contributor to the problem?

 Bruce Ferrell


-- 
Cell: 647.710.1314

Thirty spokes converge on a hub
but it's the emptiness
that makes a wheel work
   -- from the Daodejing

Re: Mysql 5.1 - 5.0

[Johan De Meersman]

Just like that, not advisable. There's upgrade scripts in the packages that 
should handle 5.0 to 5.1; but your safest bet is still going to be a clean 
mysqldump and import.

- Original Message -
 From: Brent Clark brentgclarkl...@gmail.com
 To: mysql@lists.mysql.com
 Sent: Thursday, 10 March, 2011 2:07:11 PM
 Subject: Mysql 5.1 - 5.0
 
 Hiya
 
 We have client that is using Ubuntu, therefore MySQL is 5.1, but
 where I
 work, we still standardise on Debian Lenny (upgrading to Squeeze, is
 in
 the pipeline), therefore, MySQL is 5.0.
 
 What I would like to know is, can I just make a copy of the mysql
 database files and copy them the Lenny box?
 
 If anyone could help / answer, it would be appreciated.
 
 Kind Regards
 Brent Clark
 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 5.1 Rolling back a transaction containing Create Alter Drop table statements

[Baron Schwartz]

On Mon, Mar 16, 2009 at 10:25 AM, Hatem Nassrat hatem.nass...@gmail.com wrote:
 Hi,

 Is there any way to rollback a transaction in MySQL 5.1 that contains
 Create, Alter, Drop, table statements.

No.  Sorry.

-- 
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 5.1 queries 1000x slower than 5.0

[Baron Schwartz]

On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote:
 Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30
 because after all, it was Saturday and what else is there to do on a
 Saturday?

 I'm running it on XP Pro with 3gb ram and used the my.ini for very large
 system. I decided not to tweak any of these settings except for the datadir
 and I commented out skip-networking since my old my.ini file didn't have it
 either. I'm using only MyISAM tables so skip-innodb is enabled.

Why didn't you re-use your my.ini from your 5.0 system?

 Well to make a sad story short, I ran my application last night and returned
 this morning only to find it still running. Select queries that would run on
 1 table to return 1 row should take under a second, now takes over an hour.
 An Explain shows that it is using the index.

 The query goes something like this:
 select purch_date from items where prod_code='ABC' and ((store_id='A' and
 purch_date'2007-01-01') or (store_id='B' and purch_date'2007-01-05') or
 (store_id='C' and purch_date'2007-01-09')) and (col1 is null or col2 is
 null or col3 is null or col4 is null or col5 is null or col6 is null) order
 by purch_date limit 1;

 There are 2 compound keys: prod_code,purch_date,store_id and
 prod_code,purch_date,store_id
 The table items has approx 30 million rows in it and there are approx 5,000
 rows for 'ABC'.

We have a client in a similar situation but I'm still waiting for
access to the server to investigate the problem.

I suspect that EXPLAIN is lying, in the client's case, but I can't
know until I get on the box and see.  In your case I can't comment.
It sounds like the same thing but it might not be.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
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 5.1 queries 1000x slower than 5.0 - OPTIMIZER BUG

[mos]

At 03:25 PM 1/11/2009, you wrote:

On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote:
 Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 
5.1.30

 because after all, it was Saturday and what else is there to do on a
 Saturday?

 I'm running it on XP Pro with 3gb ram and used the my.ini for very large
 system. I decided not to tweak any of these settings except for the datadir
 and I commented out skip-networking since my old my.ini file didn't have it
 either. I'm using only MyISAM tables so skip-innodb is enabled.

Why didn't you re-use your my.ini from your 5.0 system?


I wanted to see what the default settings would do.


 Well to make a sad story short, I ran my application last night and 
returned
 this morning only to find it still running. Select queries that would 
run on

 1 table to return 1 row should take under a second, now takes over an hour.
 An Explain shows that it is using the index.

 The query goes something like this:
 select purch_date from items where prod_code='ABC' and ((store_id='A' and
 purch_date'2007-01-01') or (store_id='B' and purch_date'2007-01-05') or
 (store_id='C' and purch_date'2007-01-09')) and (col1 is null or col2 is
 null or col3 is null or col4 is null or col5 is null or col6 is null) order
 by purch_date limit 1;

 There are 2 compound keys: prod_code,purch_date,store_id and
 prod_code,purch_date,store_id
 The table items has approx 30 million rows in it and there are approx 5,000
 rows for 'ABC'.

We have a client in a similar situation but I'm still waiting for
access to the server to investigate the problem.

I suspect that EXPLAIN is lying, in the client's case, but I can't
know until I get on the box and see.  In your case I can't comment.
It sounds like the same thing but it might not be.


I discovered the problem I'm having is the query optimization in 5.1! MYSQL 
5.01 had no problem with these queries.


My MyISAM table checked out ok and I optimized it to rebuild the indexes 
under 5.1.  So there is nothing wrong with the table.


I started playing with the SQL and noticed if I take out either the Limit 
1 clause or the Order by clause, it runs in about a second.
So it works fine if *either* the Limit 1 or Order by clause is used, 
but NOT BOTH! If both are used the query time goes from under 1 second to 
around 1 hour. This is odd because it looks like it is doing a full table 
scan and ignoring where prod_code='ABC'  which should limit the rows 
returned to 14,000 rows. So sorting on 14,000 rows and putting a limit on 
it should not make the query run 1000x slower.


I started tweaking the SQL and took out most of the Where Clause to get:

select purch_date from items where prod_code='ABC'  order  by purch_date 
limit 1;


and it takes 10 seconds. There are 14,000 rows where prod_code='ABC'.  This 
is pretty slow since it is returning only 1 row. An Explain show it is 
using the ix_Date Key (columns Purch_Date,Store_Id,Prod_Code) because of 
the sort clause. It is using the index for sorting so it has to scan a lot 
more rows because now it is scanning all products and not just 
prod_code='ABC'.  So the Order By clause is superseding the Where clause!


If I remove the Order By clause to get
select purch_date from items where prod_code='ABC'   limit 1;

then the Explain uses the ix_Main index (columns Prod_Code, Store_Id, 
purch_date) and gets the results in 0 ms.
I am resetting the query cache between tests to make sure the results are 
accurate.


I then went back to the original SQL statement and forced MySQL not to use 
the date index for sorting. So instead of taking more than an hour to 
complete it now completes in 90ms. I may have to do the same with other SQL 
statements if this problem crops up there too.


Mike  



--
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 5.1 queries 1000x slower than 5.0 - OPTIMIZER BUG

[Baron Schwartz]

On Sun, Jan 11, 2009 at 9:31 PM, mos mo...@fastmail.fm wrote:
 At 03:25 PM 1/11/2009, you wrote:

 On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote:
  Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to
  5.1.30
  because after all, it was Saturday and what else is there to do on a
  Saturday?
 
  I'm running it on XP Pro with 3gb ram and used the my.ini for very large
  system. I decided not to tweak any of these settings except for the
  datadir
  and I commented out skip-networking since my old my.ini file didn't have
  it
  either. I'm using only MyISAM tables so skip-innodb is enabled.

 Why didn't you re-use your my.ini from your 5.0 system?

 I wanted to see what the default settings would do.

Well, then you're not comparing apples to apples!

 I discovered the problem I'm having is the query optimization in 5.1! MYSQL
 5.01 had no problem with these queries.

Then you should file a bug report against this.  It sounds like you
have a pretty easily reproduceable test case, and that's what they
need to fix bugs.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
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 5.1 Function Creation

[Moon's Father]

Make sure your log_bin_trust_function_creator is on.

On Sun, Sep 28, 2008 at 3:04 AM, Jesse [EMAIL PROTECTED] wrote:

 I'm trying to use existing functions from a restored database from 5.0xx to
 5.1, and get an error about the mysql.proc table is missing or corrupt. The
 mysql.proc table appears to be there, and does not appear to be corrupt.  I
 did a grant select on mysql.proc to user, and that did not make any
 difference, as it has in the past.  So, I decided that I'd delete the
 function from the database, and try to add it back in, and when I do, I get
 an error, Failed to CREATE FUNCTION.

 The code that I'm trying to execute is as follows:

 CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE)
   RETURNS int(11)
   DETERMINISTIC
   CONTAINS SQL
   SQL SECURITY DEFINER
   COMMENT ''
 BEGIN
  DECLARE today DATE;
  SELECT CampStartDate INTO today FROM config;
  RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0;
 END;

 Any ideas what's going on?

 Jesse

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]




-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Re: MySQL 5.1

[Jacques Marneweck]

Sid Lane wrote:
any update on the 5.1 general release date?  is it still on target for 
Q4 -

Q1?  any narrower window?

Last I heard, Q2 next year.

Regards
--jm


On 8/30/06, Colin Charles [EMAIL PROTECTED] wrote:


Logan, David (SST - Adelaide) wrote:

Hi!

 Does anybody have any idea when 5.1 may come to General Release? I am
 particularly interested in MySQL Cluster as I have several databases
 (around 50) totalling 26Gb and would like to consider moving to this
 version because of the Cluster Disk Data Storage and the fact the
 current hardware doesn't have the RAM requirements to hold these
 databases.

I expect late (Q4/2006) to early (Q1/2007) as a good time for release.

 I plan to store the data on a SAN and was wondering if anybody had any
 experience with this as yet?

This is why you're after 5.1, so you get disk as opposed to memory only
based storage then
--
Colin Charles, Community Engineer
MySQL AB, Melbourne, Australia, www.mysql.com
Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528
Mobile: +614 12 593 292 / Skype: colincharles

MySQL Forge: http://forge.mysql.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]








--
Jacques Marneweck
http://www.powertrip.co.za/
http://www.powertrip.co.za/blog/

#include std/disclaimer.h


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 5.1

[Sid Lane]

any update on the 5.1 general release date?  is it still on target for Q4 -
Q1?  any narrower window?

On 8/30/06, Colin Charles [EMAIL PROTECTED] wrote:


Logan, David (SST - Adelaide) wrote:

Hi!

 Does anybody have any idea when 5.1 may come to General Release? I am
 particularly interested in MySQL Cluster as I have several databases
 (around 50) totalling 26Gb and would like to consider moving to this
 version because of the Cluster Disk Data Storage and the fact the
 current hardware doesn't have the RAM requirements to hold these
 databases.

I expect late (Q4/2006) to early (Q1/2007) as a good time for release.

 I plan to store the data on a SAN and was wondering if anybody had any
 experience with this as yet?

This is why you're after 5.1, so you get disk as opposed to memory only
based storage then
--
Colin Charles, Community Engineer
MySQL AB, Melbourne, Australia, www.mysql.com
Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528
Mobile: +614 12 593 292 / Skype: colincharles

MySQL Forge: http://forge.mysql.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 5.1

[Colin Charles]

Logan, David (SST - Adelaide) wrote:

Hi!


Does anybody have any idea when 5.1 may come to General Release? I am
particularly interested in MySQL Cluster as I have several databases
(around 50) totalling 26Gb and would like to consider moving to this
version because of the Cluster Disk Data Storage and the fact the
current hardware doesn't have the RAM requirements to hold these
databases.


I expect late (Q4/2006) to early (Q1/2007) as a good time for release.


I plan to store the data on a SAN and was wondering if anybody had any
experience with this as yet?


This is why you're after 5.1, so you get disk as opposed to memory only 
based storage then

--
Colin Charles, Community Engineer
MySQL AB, Melbourne, Australia, www.mysql.com
Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528
Mobile: +614 12 593 292 / Skype: colincharles

MySQL Forge: http://forge.mysql.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 5.1

[Jacques Marneweck]

Logan, David (SST - Adelaide) wrote:

Hi Folks,
 
Does anybody have any idea when 5.1 may come to General Release? I am

particularly interested in MySQL Cluster as I have several databases
(around 50) totalling 26Gb and would like to consider moving to this
version because of the Cluster Disk Data Storage and the fact the
current hardware doesn't have the RAM requirements to hold these
databases.
  

Robin from MySQL has said Q1 2007.  Kai and Max have both mentioned Q4 2006.
 
I plan to store the data on a SAN and was wondering if anybody had any

experience with this as yet?
 
I have current thoughts along the lines of
 
2 x Pentium 4 1Ghz, 1Gb RAM as management nodes

4 x Pentium 4 1Ghz, 1Gb RAM as SQL nodes
2 x Pentium 4 1Ghz (x 8 cpus), 8Gb RAM as data nodes
  
You will have two copies of the data on those two data nodes.  Kai 
demonstrated MySQL Cluster on 5.1.11 running four data nodes on his 
laptop, which basically allows one to have two copies of data mirrored 
over two nodes (see quickly hacked picture).  At least with having more 
data nodes one had two copies of data you reduce the single point of 
failure.  Having more data nodes allows you to split up data across 
nodes, etc.


Regards
--jm
 
The databases are currently running, replicating and serving around 800

queries per second utilising a 100M network.
 
Thanks

---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia 
invent   
---
  

--
Jacques Marneweck
http://www.powertrip.co.za/
http://www.powertrip.co.za/blog/

#include std/disclaimer.h



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 5.1

[David Griffiths]

The cluster engine has been available since the 4.0 tree, I believe. You can 
begin using it immediately with 5.0 (which is GA).

David

Logan, David (SST - Adelaide) wrote:

Hi Folks,
 
Does anybody have any idea when 5.1 may come to General Release? I am

particularly interested in MySQL Cluster as I have several databases
(around 50) totalling 26Gb and would like to consider moving to this
version because of the Cluster Disk Data Storage and the fact the
current hardware doesn't have the RAM requirements to hold these
databases.
 
I plan to store the data on a SAN and was wondering if anybody had any

experience with this as yet?
 
I have current thoughts along the lines of
 
2 x Pentium 4 1Ghz, 1Gb RAM as management nodes

4 x Pentium 4 1Ghz, 1Gb RAM as SQL nodes
2 x Pentium 4 1Ghz (x 8 cpus), 8Gb RAM as data nodes
 
The databases are currently running, replicating and serving around 800

queries per second utilising a 100M network.
 
Thanks

---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia 
invent   
---
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]