Re: Query optimizer-miss with unqualified expressions, bug or feature?

[Johan De Meersman]

- Original Message -
> From: "Shawn Green" 
> Subject: Re: Query optimizer-miss with unqualified expressions, bug or 
> feature?
> 
> On a more serious note, indexes with limited cardinality are less useful
> than those with excellent cardinality. Cardinality is an approximation
> (or calculation. It depends on your storage engine) of how many unique
> values there are in the index.

On a related note, are there any plans (and could you offer a rough timeframe?) 
to include bitmap indices in MySQL?


Thanks,
Johan

-- 
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: Query optimizer-miss with unqualified expressions, bug or feature?

[Roy Lyseng]

Hi Shawn,

On 19.10.15 22.33, shawn l.green wrote:



On 10/19/2015 3:48 PM, Roy Lyseng wrote:

Hi Ben,

On 19.10.15 16.07, Ben Clewett wrote:

Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and
then use
two starts: one on 'a > 0' and one on 'a < 0', taking a union of the
result?
Which might make a significant result to something?


That is correct. However, if the substitution type for BOOLEAN was
UNSIGNED TINYINT instead of TINYINT, the range 'a < 0' would not even be
needed, and the quite cumbersome UNION would be avoided. But the best
solution would of course be a two-valued boolean type, where 'a <> 0'
would easily be transformed to a = 1. It would also mean that statistics
for the columns would be better, with TINYINT each value has the
estimated probability 1/256, whereas a boolean value would have
probability 1/2.




256 possible values of BOOLEAN? I've heard of fuzzy logic but this is awesome!
Some new literal value names to consider: maybe, sort_of, nearly_always,
certainly, practically_never, likely, ...
*


Well, it is the practical consequence of using TINYINT as the substitution type 
for BOOLEAN...





On a more serious note, indexes with limited cardinality are less useful than
those with excellent cardinality. Cardinality is an approximation (or
calculation. It depends on your storage engine) of how many unique values there
are in the index.

If the Optimizer estimates (based on a calculation based on the Cardinality)
that more than about 30% of a table would need to be retrieved in random order
based on an index, then that index is disallowed.  Why? Because the physical
disk overhead of doing random access averages just slightly more than 3x the
overhead used to scan a much larger block of data.

http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html

An index on just a Boolean value would have at best a cardinality of 2. So, any
indexes on Boolean values should include other columns to help the index become
more selective.

http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html


You are right about the index use, so it would be interesting only with a 
significant skew, say 10% TRUE values. However, the optimizer is not only about 
indexing, but also about calculating the filtering effect of a predicate. Using 
a true BOOLEAN rather than a TINYINT would give a better estimate of the 
filtering effect, and thus of the estimated number of rows as the outcome of a 
query.





*Actually, fuzzy logic has lots of practical application in real world
situations. They are just not using the MySQL BOOLEAN data type to store the
value for comparison.



Thanks,
Roy

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

Re: Query optimizer-miss with unqualified expressions, bug or feature?

[shawn l.green]

On 10/19/2015 3:48 PM, Roy Lyseng wrote:

Hi Ben,

On 19.10.15 16.07, Ben Clewett wrote:

Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and
then use
two starts: one on 'a > 0' and one on 'a < 0', taking a union of the
result?
Which might make a significant result to something?


That is correct. However, if the substitution type for BOOLEAN was
UNSIGNED TINYINT instead of TINYINT, the range 'a < 0' would not even be
needed, and the quite cumbersome UNION would be avoided. But the best
solution would of course be a two-valued boolean type, where 'a <> 0'
would easily be transformed to a = 1. It would also mean that statistics
for the columns would be better, with TINYINT each value has the
estimated probability 1/256, whereas a boolean value would have
probability 1/2.




256 possible values of BOOLEAN? I've heard of fuzzy logic but this is 
awesome! Some new literal value names to consider: maybe, sort_of, 
nearly_always, certainly, practically_never, likely, ...

*


On a more serious note, indexes with limited cardinality are less useful 
than those with excellent cardinality. Cardinality is an approximation 
(or calculation. It depends on your storage engine) of how many unique 
values there are in the index.


If the Optimizer estimates (based on a calculation based on the 
Cardinality) that more than about 30% of a table would need to be 
retrieved in random order based on an index, then that index is 
disallowed.  Why? Because the physical disk overhead of doing random 
access averages just slightly more than 3x the overhead used to scan a 
much larger block of data.


http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html

An index on just a Boolean value would have at best a cardinality of 2. 
So, any indexes on Boolean values should include other columns to help 
the index become more selective.


http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html


*Actually, fuzzy logic has lots of practical application in real world 
situations. They are just not using the MySQL BOOLEAN data type to store 
the value for comparison.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: Query optimizer-miss with unqualified expressions, bug or feature?

[Roy Lyseng]

Hi Ben,

On 19.10.15 16.07, Ben Clewett wrote:

Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and then use
two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result?
Which might make a significant result to something?


That is correct. However, if the substitution type for BOOLEAN was UNSIGNED 
TINYINT instead of TINYINT, the range 'a < 0' would not even be needed, and the 
quite cumbersome UNION would be avoided. But the best solution would of course 
be a two-valued boolean type, where 'a <> 0' would easily be transformed to a = 
1. It would also mean that statistics for the columns would be better, with 
TINYINT each value has the estimated probability 1/256, whereas a boolean value 
would have probability 1/2.


Thanks,
Roy



Ben.


On 2015-10-19 14:19, Roy Lyseng wrote:

Hi Ben,

On 19.10.15 15.10, Ben Clewett wrote:


I have noticed that an unqualified boolean expression cannot be optimized by
MySQL to use an index in 5.6.24.

For example:

CREATE TABLE t (
   i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   a BOOLEAN NOT NULL,
   KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps
something else?


MySQL does not have a true boolean type, so this is actually interpreted as

  SELECT * FROM t WHERE a <> 0;

The optimizer is not able to see that "a <> 0" means "a = 1", and hence no
index will be used.

Thanks,
Roy



Thanks,

Ben Clewett.









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

Re: Query optimizer-miss with unqualified expressions, bug or feature?

[Ben Clewett]

Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and 
then use two starts: one on 'a > 0' and one on 'a < 0', taking a union 
of the result?  Which might make a significant result to something?


Ben.


On 2015-10-19 14:19, Roy Lyseng wrote:

Hi Ben,

On 19.10.15 15.10, Ben Clewett wrote:


I have noticed that an unqualified boolean expression cannot be 
optimized by

MySQL to use an index in 5.6.24.

For example:

CREATE TABLE t (
   i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   a BOOLEAN NOT NULL,
   KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps
something else?


MySQL does not have a true boolean type, so this is actually 
interpreted as


  SELECT * FROM t WHERE a <> 0;

The optimizer is not able to see that "a <> 0" means "a = 1", and 
hence no index will be used.


Thanks,
Roy



Thanks,

Ben Clewett.








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

Re: Query optimizer-miss with unqualified expressions, bug or feature?

[Roy Lyseng]

Hi Ben,

On 19.10.15 15.10, Ben Clewett wrote:


I have noticed that an unqualified boolean expression cannot be optimized by
MySQL to use an index in 5.6.24.

For example:

CREATE TABLE t (
   i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   a BOOLEAN NOT NULL,
   KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps
something else?


MySQL does not have a true boolean type, so this is actually interpreted as

  SELECT * FROM t WHERE a <> 0;

The optimizer is not able to see that "a <> 0" means "a = 1", and hence no index 
will be used.


Thanks,
Roy



Thanks,

Ben Clewett.







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

Query optimizer-miss with unqualified expressions, bug or feature?

[Ben Clewett]

I have noticed that an unqualified boolean expression cannot be 
optimized by MySQL to use an index in 5.6.24.


For example:

CREATE TABLE t (
  i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a BOOLEAN NOT NULL,
  KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps 
something else?


Thanks,

Ben Clewett.




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

Possible bug with event and delete...limit ?

[Johan De Meersman]

Hey list, 

I noticed a table that was trying to fill the disk before the weekend, so I 
quickly set up an event to gradually clean it out. Yesterday, however, I 
returned to find 400+ jobs in state "updating". I disabled the event, but the 
jobs hadn't cleared up today, so I had to kill them. 

I noticed, however, that the LIMIT statement I specified in the event wasn't 
present in the actual queries... Could that be a parser bug, or does the limit 
simply not show up in the process lists? Has anyone seen this before ? 

This is 5.5.30-1.1-log on Debian 64-bit. 

Thanks, 
Johan 


mysql> show create event jdmsyslogcleaner\G 
*** 1. row *** 
Event: jdmsyslogcleaner 
sql_mode: 
time_zone: SYSTEM 
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `jdmsyslogcleaner` ON 
SCHEDULE EVERY 30 SECOND STARTS '2014-09-19 19:14:21' ON COMPLETION PRESERVE 
DISABLE COMMENT 'Cleanup to not kill the disk' DO delete from syslog where 
logtime < "2014-07-20" limit 1 
character_set_client: latin1 
collation_connection: latin1_swedish_ci 
Database Collation: latin1_swedish_ci 
1 row in set (0.00 sec) 


mysql> select * from information_schema.processlist WHERE `INFO` LIKE 'DELETE 
FROM `cacti%' order by time; 
+---+---++---+-+---+--++
 
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | 
+---+---++---+-+---+--++
 
| 149192515 | cacti_net | host:49225 | cacti_net | Query | 21 | init | DELETE 
FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-24 08:48:28' | 
[...] 
| 148845878 | cacti_net | host:50186 | cacti_net | Query | 47345 | updating | 
DELETE FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-23 17:13:51' | 
+---+---++---+-+---+--++
 
411 rows in set (13.66 sec) 




-- 
What's tiny and yellow and very, very dangerous? 
A canary with the root password. 

bzr bug or corrupted 5.7 tree?

[Hartmut Holzgraefe]

I'm extremely confused by the following which I also filed as

  http://bugs.mysql.com/bug.php?id=72389

as it seems as if "bzr annotate" can no longer be trusted,
at least in the mysql-5.7 launchpad branch:

check the 5.6 "annotate" results of client/mysqlcheck.c


http://bazaar.launchpad.net/~mysql/mysql-server/5.6/annotate/head:/client/mysqlcheck.c#L524

against the 5.7 version


http://bazaar.launchpad.net/~mysql/mysql-server/5.7/annotate/head:/client/mysqlcheck.c#L524

both claim that this part of the code was last changed by changeset
1810.3949.1 from 2007:

  http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/1810.3494.1

while the part encoding '.' was actually removed from 5.7 code by
changeset 7416 recently:

  http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/7416

this is not a launchpad problem, the bzr annotate output on a local 5.7
branch is equally wrong ...

question is whether this is a corrupted tree or actually a bzr bug ...



-- 
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
SkySQL - The MariaDB Company | http://www.skysql.com/

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

link-bug

[hsv]

 2014/01/29 16:16 -0800, neubyr 
https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html -

Someone was not all awake when making this webpage up: four of the links under 
"Table of Contents" point to this same page.

(I was looking because I was thinking about Neubyr s problem, but I can make 
nothing of it.)


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

Re: severe build bug 5.5 viossl

[Nick Edwards]

yes I'm aware, it is why I made the comment about change

However still subject to corporate policy which means sticking with
mysql until they screw up on regular basis, so if they fail to fix
this by next version, I can take it to CIO and present my case, two
deliberate broken versions when fix made available to them months ago,
should, get me a win to move away from mysql.  (perhaps this is what
oracle wants anyway, since mysql earns them no money)


On 6/9/13, Claudio Nanni  wrote:
> Hi Nick,
>
> It seems it is fixed already in MariaDB:
> http://bugs.mysql.com/bug.php?id=68999
>
> "[9 Jun 9:34] Michael Widenius
>
> This was fixed in MariaDB 5.5 in May 2013 as part of our merge of
> MySQL 5.5 to MariaDB 5.5."
>
> Cheers
>
> Claudio
>
>
> 2013/6/9 Nick Edwards 
>
>> This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.
>>
>> The problem goes ignored by oracle
>>
>> 5.5.32 releases, same error, apply the same simple patch and builds
>>
>> /tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
>> /tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
>> undeclared (first use in this
>> function)
>> /tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
>> is reported only once
>> /tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears
>> in.)
>> make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
>> make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2
>>
>>
>> Question, does anyone at oracle even bother with bug tracking now days?
>> How can something that causes a fail of building with versions of
>> openssl less then 1.0.0
>> go un fixed for so long.
>>
>> Is this more proof that oracle DGAF about mysql?  should I move to
>> mariadb?
>>
>> because if we have to re patch a failed build on 5.5.33, we will I
>> think, since it shows oracle dont give a stuff
>>
>> For list archive, patch is:
>>
>>
>> --- mysql-5.5.32/vio/viossl.c   2013-05-17 01:47:14.0 +1000
>> +++ mysql-5.5.32a/vio/viossl.c  2013-06-09 15:38:06.0 +1000
>> @@ -172,8 +172,10 @@
>>SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
>>SSL_set_fd(ssl, vio->sd);
>>  #ifndef HAVE_YASSL
>> +#ifdef SSL_OP_NO_COMPRESSION
>>SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
>>  #endif
>> +#endif
>>
>>if ((r= connect_accept_func(ssl)) < 1)
>>{
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>>
>
>
> --
> Claudio
>

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

Re: severe build bug 5.5 viossl

[Claudio Nanni]

Hi Nick,

It seems it is fixed already in MariaDB:
http://bugs.mysql.com/bug.php?id=68999

"[9 Jun 9:34] Michael Widenius

This was fixed in MariaDB 5.5 in May 2013 as part of our merge of
MySQL 5.5 to MariaDB 5.5."

Cheers

Claudio


2013/6/9 Nick Edwards 

> This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.
>
> The problem goes ignored by oracle
>
> 5.5.32 releases, same error, apply the same simple patch and builds
>
> /tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
> /tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
> undeclared (first use in this
> function)
> /tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
> is reported only once
> /tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears
> in.)
> make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
> make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2
>
>
> Question, does anyone at oracle even bother with bug tracking now days?
> How can something that causes a fail of building with versions of
> openssl less then 1.0.0
> go un fixed for so long.
>
> Is this more proof that oracle DGAF about mysql?  should I move to mariadb?
>
> because if we have to re patch a failed build on 5.5.33, we will I
> think, since it shows oracle dont give a stuff
>
> For list archive, patch is:
>
>
> --- mysql-5.5.32/vio/viossl.c   2013-05-17 01:47:14.0 +1000
> +++ mysql-5.5.32a/vio/viossl.c  2013-06-09 15:38:06.0 +1000
> @@ -172,8 +172,10 @@
>SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
>SSL_set_fd(ssl, vio->sd);
>  #ifndef HAVE_YASSL
> +#ifdef SSL_OP_NO_COMPRESSION
>SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
>  #endif
> +#endif
>
>if ((r= connect_accept_func(ssl)) < 1)
>{
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Claudio

severe build bug 5.5 viossl

[Nick Edwards]

This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.

The problem goes ignored by oracle

5.5.32 releases, same error, apply the same simple patch and builds

/tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
/tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
undeclared (first use in this
function)
/tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
is reported only once
/tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears in.)
make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2


Question, does anyone at oracle even bother with bug tracking now days?
How can something that causes a fail of building with versions of
openssl less then 1.0.0
go un fixed for so long.

Is this more proof that oracle DGAF about mysql?  should I move to mariadb?

because if we have to re patch a failed build on 5.5.33, we will I
think, since it shows oracle dont give a stuff

For list archive, patch is:


--- mysql-5.5.32/vio/viossl.c   2013-05-17 01:47:14.0 +1000
+++ mysql-5.5.32a/vio/viossl.c  2013-06-09 15:38:06.0 +1000
@@ -172,8 +172,10 @@
   SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
   SSL_set_fd(ssl, vio->sd);
 #ifndef HAVE_YASSL
+#ifdef SSL_OP_NO_COMPRESSION
   SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
 #endif
+#endif

   if ((r= connect_accept_func(ssl)) < 1)
   {

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

RE: Bug in BETWEEN same DATETIME

[Rick James]

(To ramble in a slightly different direction...)

I claim that the world gained half a second when we went from "round time" to 
"square time" a few decades ago.  Before then, announcers on radio/tv would 
look at their round-shape analog clock to see what time it was; they would 
perform a ROUND() function before announcing the time.  Now they look at their 
square-shaped digital clock and perform FLOOR().  So, what you hear on radio/tv 
is half a second behind what you used to hear.   ;)

> -Original Message-
> From: h...@tbbs.net [mailto:h...@tbbs.net]
> Sent: Friday, May 24, 2013 11:08 AM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
> 
> >>>> 2013/05/24 09:49 -0400, shawn green >>>>
> Or we could coerce datetime values back to their date values when both
> are being used. The trick now becomes choosing between rounding the
> datetime value (times past noon round to the next date) or do we use
> the floor() function all the time.
> <<<<<<<<
> This is simply wrong. Timestamps are not numbers: we do not add
> timestamps, and when we subtract them we do not consider the difference
> something of the same type. Therefore, one does well to be wary when
> applying to a timestamp the notion "rounding".
> 
> But containment generally applys: an event on MAY 25th from 1pm to 4pm
> is within May 25th, which is within May,  When containment fails,
> then there is trouble: what is the first weekend of August? or the
> first week of August? better to say, the weekend or week of August 1st,
> or 2d, or ...; "day" is a "common divisor" to calendar-month, weekend,
> and week.
> 
> Therefore, when I learnt that in version 4 MySQL had gone from
> interpreting a comparison between DATE and a finer timestamp by the
> DATE to interpreting it by the finer timestamp I believed that MySQL
> was going the wrong way--that MySQL had gone from a realization of an
> intuitive sense of containing, as above, to one on which too much
> thought had been expended, with a loss of intuitive sense.
> 
> I consider the change of 2013/5/25-13 to 2013/5/25 to be truncation,
> not any sort of rounding; that is, it is a matter of notation, but one
> which intuitivly expresses containment.
> 
> These notions sometimes change over the years, and by nation. When the
> first public striking clock was set up in Milan, it pointed to hours I
> through XXIV, with sunset falling within the 24th hour--that is, the
> 24th hour ends with 24 o'clock s being struck. This persists to this
> day in the German expression "viertel sechs", which means that the
> sixth hour is one-fourth over, or, as we would say it, "quarter after
> five". (Like expressions are found amongst the Germans s neighbors, but
> in English never took root.) Nowadays we are are more inclined to
> associate both "quarter after five" and "quarter to six" ("dreiviertel
> sechs") with 5 o'clock than 6 o'clock; this accompanies the change of
> notation from 1 through 24 to 0 through 23.
> 
> I find MySQL s automatic conversion sometimes to be downright screwy;
> (version 5.5.8) consider "SELECT NULL" and "SELECT NULL UNION SELECT
> NULL"; in one of my views there is a complex wholly numeric expression
> that becomes "varbinary(32)".
> 
> 
> --
> 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: Bug in BETWEEN same DATETIME

[hsv]

 2013/05/24 09:49 -0400, shawn green 
Or we could coerce datetime values back to their date values when both are 
being used. The trick now becomes choosing between rounding the datetime value 
(times past noon round to the next date) or do we use the floor() function all 
the time.

This is simply wrong. Timestamps are not numbers: we do not add timestamps, and 
when we subtract them we do not consider the difference something of the same 
type. Therefore, one does well to be wary when applying to a timestamp the 
notion "rounding".

But containment generally applys: an event on MAY 25th from 1pm to 4pm is 
within May 25th, which is within May,  When containment fails, then there 
is trouble: what is the first weekend of August? or the first week of August? 
better to say, the weekend or week of August 1st, or 2d, or ...; "day" is a 
"common divisor" to calendar-month, weekend, and week.

Therefore, when I learnt that in version 4 MySQL had gone from interpreting a 
comparison between DATE and a finer timestamp by the DATE to interpreting it by 
the finer timestamp I believed that MySQL was going the wrong way--that MySQL 
had gone from a realization of an intuitive sense of containing, as above, to 
one on which too much thought had been expended, with a loss of intuitive sense.

I consider the change of 2013/5/25-13 to 2013/5/25 to be truncation, not any 
sort of rounding; that is, it is a matter of notation, but one which intuitivly 
expresses containment.

These notions sometimes change over the years, and by nation. When the first 
public striking clock was set up in Milan, it pointed to hours I through XXIV, 
with sunset falling within the 24th hour--that is, the 24th hour ends with 24 
o'clock s being struck. This persists to this day in the German expression 
"viertel sechs", which means that the sixth hour is one-fourth over, or, as we 
would say it, "quarter after five". (Like expressions are found amongst the 
Germans s neighbors, but in English never took root.) Nowadays we are are more 
inclined to associate both "quarter after five" and "quarter to six" 
("dreiviertel sechs") with 5 o'clock than 6 o'clock; this accompanies the 
change of notation from 1 through 24 to 0 through 23.

I find MySQL s automatic conversion sometimes to be downright screwy; (version 
5.5.8) consider "SELECT NULL" and "SELECT NULL UNION SELECT NULL"; in one of my 
views there is a complex wholly numeric expression that becomes "varbinary(32)".


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

Re: Bug in BETWEEN same DATETIME

[shawn green]

Hi Rick,

Thank you for continuing this. It brings up a few good points.

On 5/24/2013 12:17 PM, Rick James wrote:

For years (even decades), I have stayed out of trouble by assuming a 'date' 
represents the instant in time corresponding to midnight at the start of that 
day.  In MySQL (until 5.6), that is equivalent to a 1-second DATETIME.  I also 
assume midnight belongs to the day that it is the _start_ of.


There is also a strong desire to make a database server try to do exactly what 
the user tells it to do.

That is difficult, because of definitions and representation.
A common problem is comparing a FLOAT value to a 'decimal' value like 1.23.  
MySQL does a good job of covering some cases, but there are still cases between 
DECIMAL, FLOAT, DOUBLE, and literals that will register as inequality, to the 
surprise of the user.
I see the DATE problem as another thing where the user needs to understand the 
computer's algorithm, which, as Shawn points out is:

"We do one thing (make dates represent midnight on that date when they need to be 
compared to datetime values) and allow the users to decide how to handle the rest of the 
comparison according to their specific needs."


WHERE datetime_col >= '2013-01-01'
  AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY

but for predictability and reliability, this is one rewrite that may not always 
be true.

So, to be safe, one should perhaps say:
   WHERE datetime_col >= '2013-01-01 00:00:00'
 AND datetime_col <  '2013-01-01 00:00:00' + INTERVAL 1 DAY

IN_DATE (or maybe "ON_DAY") is an interesting idea.  I assume it would be 
transliterated by the parser into something like the expression above, then optimized 
based on which part(s) are columns and which are literals.


 '2013-05-14 17:00:00' >= '2013-01-01'
 AND '2013-05-14 17:00:00' <= '2013-05-14' + INTERVAL 12 HOUR

There's an extra second in that!  (I call it the "midnight bug".)



It includes the extra second only because your second comparison is 
using "<=" and not just "<"




I perceive (rightly or wrongly) that comparing a TIMESTAMP to something first 
converts the TIMESTAMP value to a string ('2013-...').  Shawn, perhaps this 
statement belongs as part of the 'algorithm' explanation?



Sort of. TIMESTAMP is not a universal constant (as you say later); it is 
timezone-aware. This causes all sorts of problems when the UTC value it 
keeps internally is used for date-literal comparisons on or about the 
time of Daylight Saving Time changes happen in the timezones that 
support them.


During the 'spring forward' period (which depends on hemisphere) there 
will be a gap of times that do not exist. The clock shifts from 
"01:59.59.99" to "03:00:00". During the 'fall back' period, the 
values will repeat the "02:00:00" to "02:59:59.99" range. Some 
queries will return multiple rows.


To be timezone agnostic, always store date values relative to UTC or 
store them as integer values using the FROM_UNIXTIME() and 
UNIX_TIMESTAMP() functions. Then convert the absolute time to a local 
timezone during presentation.


http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html


Yes, you might get in trouble if the same SELECT were run in two different timezones at 
the same time.  Or, TIMESTAMP might help you get the "right" answer.

There are something like 5 different "datetime" concepts.  MySQL covers 2 of 
them.
 DATETIME is a picture of _your_ clock.
 TIMESTAMP is an instant in the _universe_.
For these, and others, think of a recurring event on a calendar, a sporting 
event, an appointment (potentially in a diff timezone), train schedule, etc.



For an explanation between the DATETIME and TIMESTAMP data types, I 
encourage the others on this list to review:

http://dev.mysql.com/doc/refman/5.6/en/datetime.html

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

RE: Bug in BETWEEN same DATETIME

[Rick James]

For years (even decades), I have stayed out of trouble by assuming a 'date' 
represents the instant in time corresponding to midnight at the start of that 
day.  In MySQL (until 5.6), that is equivalent to a 1-second DATETIME.  I also 
assume midnight belongs to the day that it is the _start_ of.

> There is also a strong desire to make a database server try to do exactly 
> what the user tells it to do.
That is difficult, because of definitions and representation.
A common problem is comparing a FLOAT value to a 'decimal' value like 1.23.  
MySQL does a good job of covering some cases, but there are still cases between 
DECIMAL, FLOAT, DOUBLE, and literals that will register as inequality, to the 
surprise of the user.
I see the DATE problem as another thing where the user needs to understand the 
computer's algorithm, which, as Shawn points out is:

"We do one thing (make dates represent midnight on that date when they need to 
be compared to datetime values) and allow the users to decide how to handle the 
rest of the comparison according to their specific needs."

>>WHERE datetime_col >= '2013-01-01'
>>  AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY
>but for predictability and reliability, this is one rewrite that may not 
>always be true.
So, to be safe, one should perhaps say:
  WHERE datetime_col >= '2013-01-01 00:00:00'
AND datetime_col <  '2013-01-01 00:00:00' + INTERVAL 1 DAY

IN_DATE (or maybe "ON_DAY") is an interesting idea.  I assume it would be 
transliterated by the parser into something like the expression above, then 
optimized based on which part(s) are columns and which are literals.

> '2013-05-14 17:00:00' >= '2013-01-01'
> AND '2013-05-14 17:00:00' <= '2013-05-14' + INTERVAL 12 HOUR
There's an extra second in that!  (I call it the "midnight bug".)

I perceive (rightly or wrongly) that comparing a TIMESTAMP to something first 
converts the TIMESTAMP value to a string ('2013-...').  Shawn, perhaps this 
statement belongs as part of the 'algorithm' explanation?

Yes, you might get in trouble if the same SELECT were run in two different 
timezones at the same time.  Or, TIMESTAMP might help you get the "right" 
answer.

There are something like 5 different "datetime" concepts.  MySQL covers 2 of 
them.
DATETIME is a picture of _your_ clock.
TIMESTAMP is an instant in the _universe_.
For these, and others, think of a recurring event on a calendar, a sporting 
event, an appointment (potentially in a diff timezone), train schedule, etc.

> -Original Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Friday, May 24, 2013 6:50 AM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
> 
> Hello Rick,
> 
> On 5/23/2013 7:08 PM, Rick James wrote:
> > Watch out for CAST(), DATE(), and any other function.  In a WHERE
> clause, if you hide an indexed column inside a function, the index
> cannot be used for optimization.
> >
> >INDEX(datetime_col)
> >...
> >WHERE DATE(datetime_col) = '2013-01-01'
> > will not use the index!
> >
> > The workaround is messy, but worth it (for performance):
> >WHERE datetime_col >= '2013-01-01'
> >  AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY (or any of a
> > zillion variants)
> >
> > (Yeah, it seems like the optimizer could do the obvious
> transformation
> > for you.  Hint, hint, Shawn.)
> >
> 
> Or we could coerce datetime values back to their date values when both
> are being used. The trick now becomes choosing between rounding the
> datetime value (times past noon round to the next date) or do we use
> the
> floor() function all the time.
> 
> This has been discussed and the consensus was that the most predictable
> and performant behavior was to extend a date value to become a datetime
> value by associating it with midnight ().
> 
> Let's look at some examples:
> a)   '2013-05-14 07:00:00' = '2013-05-14'
> This is true as the datetime value is 7 hours after midnight.
> 
> b)'2013-05-14 07:00:00' >= '2013-05-14'
>AND '2013-05-14 07:00:00' < '2013-05-15'
> This is true as the time value is somewhen between both midnights.
> 
> c)'2013-05-14 07:00:00' > '2013-05-14' + INTERVAL 8 HOURS
> This is false. The offset applied to the date term means the time
> portion of the resulting datetime value is 0800, not . (0700 >
> 0800) is false.
> 
> d) And what if instead of comparing agai

Re: Bug in BETWEEN same DATETIME

[shawn green]

Hello Rick,

On 5/23/2013 7:08 PM, Rick James wrote:

Watch out for CAST(), DATE(), and any other function.  In a WHERE clause, if 
you hide an indexed column inside a function, the index cannot be used for 
optimization.

   INDEX(datetime_col)
   ...
   WHERE DATE(datetime_col) = '2013-01-01'
will not use the index!

The workaround is messy, but worth it (for performance):
   WHERE datetime_col >= '2013-01-01'
 AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY
(or any of a zillion variants)

(Yeah, it seems like the optimizer could do the obvious transformation for you. 
 Hint, hint, Shawn.)



Or we could coerce datetime values back to their date values when both 
are being used. The trick now becomes choosing between rounding the 
datetime value (times past noon round to the next date) or do we use the 
floor() function all the time.


This has been discussed and the consensus was that the most predictable 
and performant behavior was to extend a date value to become a datetime 
value by associating it with midnight ().


Let's look at some examples:
a)   '2013-05-14 07:00:00' = '2013-05-14'
This is true as the datetime value is 7 hours after midnight.

b)'2013-05-14 07:00:00' >= '2013-05-14'
  AND '2013-05-14 07:00:00' < '2013-05-15'
This is true as the time value is somewhen between both midnights.

c)'2013-05-14 07:00:00' > '2013-05-14' + INTERVAL 8 HOURS
This is false. The offset applied to the date term means the time 
portion of the resulting datetime value is 0800, not . (0700 > 0800) 
is false.


d) And what if instead of comparing against the FLOOR() of each date we 
rounded datetime values up or down to their nearest dates?

   '2013-05-14 17:00:00' = '2013-05-14'
This would be false because the datetime value would have rounded up to 
'2013-05-15'.



There is also a strong desire to make a database server try to do 
exactly what the user tells it to do. If the user wants to compare a 
value to another value with an equality check, we should do that.  It 
would be very odd behavior if an equality check suddenly turns into a 
ranged check.  I realize how much time it would save people to not need 
to include both ends of the range:

>WHERE datetime_col >= '2013-01-01'
>  AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY
but for predictability and reliability, this is one rewrite that may not 
always be true.


Perhaps a new operator like IN_DATE is what you are looking for?
(example)
   '2013-05-14 17:00:00' IN_DATE '2013-05-14'
This would be true.

But what if the date field were a due date and all submissions needed to 
be in by noon on those dates?  This IN_DATE operator would return true 
for submissions beyond the deadline, too (a wrong result).  But a direct 
comparison would be true:

   '2013-05-14 17:00:00' >= '2013-01-01'
   AND '2013-05-14 17:00:00' <= '2013-05-14' + INTERVAL 12 HOUR

Of course you could always write that as a stored function, too.
(pseudo declaration)
CREATE FUNCTION IN_DATE(dateval, datetimeval) (...)

(example usage)
IN_DATE('2013-05-14','2013-05-14 17:00:00')
This could be true, too.  But a stored function would interfere with the 
use of indexes to resolve the query.


Then we get into the problems of how would this operator or function 
would handle comparisons to TIMESTAMP columns and many other variations 
like how to see if a datetime is within a 2 day span instead of one. 
It's a major ball of wax to rewrite these queries in the optimizer just 
to avoid one extra line of SQL code per comparison. To us "Keep It 
Simple, Stupid!" (KISS) seemed like the better approach to the problem. 
We do one thing (make dates represent midnight on that date when they 
need to be compared to datetime values) and allow the users to decide 
how to handle the rest of the comparison according to their specific needs.


Respectfully,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

Re: Bug in BETWEEN same DATETIME

[Andrew Moore]

Personally I don't share your view that it's a bug. Omitting the time
results in midnight by default so this screws between because there's no
time between 00:00:00 and 00:00:00.

Are you having operational issues here or are you simply fishing for bugs?

WHERE  `transaction_date` = DATE(datetime)
or
WHERE  `transaction_date` = (new column stored as date)





On Thu, May 23, 2013 at 9:55 PM, Daevid Vincent  wrote:

> I just noticed what I consider to be a bug; and related, has this been
> fixed
> in later versions of MySQL?
>
> We are using:
> mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2
>
> If you use BETWEEN and the same date for both parts (i.e. you want a single
> day) it appears that the operator isn't smart enough to consider the full
> day in the cases where the column is a DATETIME
>
>
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_be
> tween
>
> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
>
> I actually have to format it like this to get results
>
> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
> 11:59:59'
>
> As it appears that in the first instance it defaults the time to 00:00:00
> always, as verified by this:
>
> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
>
> So,  I think it's probably safe to assume that if someone is using the
> BETWEEN on datetime columns, their intent more often than not is to get the
> full 24 hour period, not the 0 seconds it currently pulls by default.
>
> I also tried these hacks as per the web page above, but this doesn't yield
> results either
>
> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
> CAST('2013-04-16' AS DATE)
> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
> CAST('2013-04-16' AS DATETIME)
>
> This one works, but I fail to see how it's any more beneficial than using a
> string without the CAST() overhead?
>
> WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
> AND
> CAST('2013-04-16 11:59:59' AS DATETIME)
>
> Or is there some other magical incantation that is supposed to be used
> (without me manually appending the time portion)?
>

RE: Bug in BETWEEN same DATETIME

[Peterson, Timothy R]

You probably want
where cast(transaction_date as date) BETWEEN '2013-04-16' AND
'2013-04-16'
That works on my test case

You could also change the where clause to be >= date and < date+1



-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com] 
Sent: Thursday, May 23, 2013 3:56 PM
To: mysql@lists.mysql.com
Subject: Bug in BETWEEN same DATETIME

I just noticed what I consider to be a bug; and related, has this been
fixed
in later versions of MySQL?
 
We are using:
mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
5.2
 
If you use BETWEEN and the same date for both parts (i.e. you want a
single
day) it appears that the operator isn't smart enough to consider the
full
day in the cases where the column is a DATETIME
 
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operato
r_be
tween 
 
WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
 
I actually have to format it like this to get results
 
WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
11:59:59'
 
As it appears that in the first instance it defaults the time to
00:00:00
always, as verified by this:
 
WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
 
So,  I think it's probably safe to assume that if someone is using the
BETWEEN on datetime columns, their intent more often than not is to get
the
full 24 hour period, not the 0 seconds it currently pulls by default.
 
I also tried these hacks as per the web page above, but this doesn't
yield
results either
 
WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
CAST('2013-04-16' AS DATE)
WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
CAST('2013-04-16' AS DATETIME)
 
This one works, but I fail to see how it's any more beneficial than
using a
string without the CAST() overhead?
 
WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
AND
CAST('2013-04-16 11:59:59' AS DATETIME)
 
Or is there some other magical incantation that is supposed to be used
(without me manually appending the time portion)?

This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.


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

Re: Bug in BETWEEN same DATETIME

[Michael Dykman]

  >>  where cast(transaction_date as date) BETWEEN '2013-04-16' AND

This approach might be problematic in that it requires that every row in
the source table be examined so that it's transaction_date can be casted.
 The original formulation is more efficient as it allows an index on
transaction_date to be used, if one exists.
   >> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND
'2013-04-16 23:59:59'

Although you probably get the result you want by just incrementing the day
on the upper-limit.
WHERE `transaction_date` BETWEEN '2013-04-16 AND '2013-04-17'

 - michael dykman


On Thu, May 23, 2013 at 5:07 PM, Peterson, Timothy R <
timothy_r_peter...@uhc.com> wrote:

> You probably want
> where cast(transaction_date as date) BETWEEN '2013-04-16' AND
> '2013-04-16'
> That works on my test case
>
> You could also change the where clause to be >= date and < date+1
>
>
>
> -Original Message-
> From: Daevid Vincent [mailto:dae...@daevid.com]
> Sent: Thursday, May 23, 2013 3:56 PM
> To: mysql@lists.mysql.com
> Subject: Bug in BETWEEN same DATETIME
>
> I just noticed what I consider to be a bug; and related, has this been
> fixed
> in later versions of MySQL?
>
> We are using:
> mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
> 5.2
>
> If you use BETWEEN and the same date for both parts (i.e. you want a
> single
> day) it appears that the operator isn't smart enough to consider the
> full
> day in the cases where the column is a DATETIME
>
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operato
> r_be
> tween
>
> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
>
> I actually have to format it like this to get results
>
> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
> 11:59:59'
>
> As it appears that in the first instance it defaults the time to
> 00:00:00
> always, as verified by this:
>
> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
>
> So,  I think it's probably safe to assume that if someone is using the
> BETWEEN on datetime columns, their intent more often than not is to get
> the
> full 24 hour period, not the 0 seconds it currently pulls by default.
>
> I also tried these hacks as per the web page above, but this doesn't
> yield
> results either
>
> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
> CAST('2013-04-16' AS DATE)
> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
> CAST('2013-04-16' AS DATETIME)
>
> This one works, but I fail to see how it's any more beneficial than
> using a
> string without the CAST() overhead?
>
> WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
> AND
> CAST('2013-04-16 11:59:59' AS DATETIME)
>
> Or is there some other magical incantation that is supposed to be used
> (without me manually appending the time portion)?
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity
> to which it is addressed. If the reader of this e-mail is not the intended
> recipient or his or her authorized agent, the reader is hereby notified
> that any dissemination, distribution or copying of this e-mail is
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and delete this e-mail immediately.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: Bug in BETWEEN same DATETIME

[shawn green]

On 5/23/2013 4:55 PM, Daevid Vincent wrote:

I just noticed what I consider to be a bug; and related, has this been fixed
in later versions of MySQL?

We are using:
mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2

If you use BETWEEN and the same date for both parts (i.e. you want a single
day) it appears that the operator isn't smart enough to consider the full
day in the cases where the column is a DATETIME

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_be
tween

WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'

I actually have to format it like this to get results

WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
11:59:59'



From the Fine Manual...
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-conversion.html
###
 Conversion of DATE values:

Conversion to a DATETIME or TIMESTAMP value adds a time part of 
'00:00:00' because the DATE value contains no time information.

...
 Prior to MySQL 5.0.42, when DATE values are compared with DATETIME 
values, the time portion of the DATETIME value is ignored, or the 
comparison could be performed as a string compare. Starting from MySQL 
5.0.42, a DATE value is coerced to the DATETIME type by adding the time 
portion as '00:00:00'. To mimic the old behavior, use the CAST() 
function to cause the comparison operands to be treated as previously. 
For example:


date_col = CAST(datetime_col AS DATE)

###

That seems pretty clear to me as not a bug.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

RE: Bug in BETWEEN same DATETIME

[Rick James]

Watch out for CAST(), DATE(), and any other function.  In a WHERE clause, if 
you hide an indexed column inside a function, the index cannot be used for 
optimization.

  INDEX(datetime_col)
  ...
  WHERE DATE(datetime_col) = '2013-01-01'
will not use the index!

The workaround is messy, but worth it (for performance):
  WHERE datetime_col >= '2013-01-01'
AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY
(or any of a zillion variants)

(Yeah, it seems like the optimizer could do the obvious transformation for you. 
 Hint, hint, Shawn.)

> -Original Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Thursday, May 23, 2013 3:50 PM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
> 
> 
> 
> On 5/23/2013 4:55 PM, Daevid Vincent wrote:
> > I just noticed what I consider to be a bug; and related, has this
> been
> > fixed in later versions of MySQL?
> >
> > We are using:
> > mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
> > 5.2
> >
> > If you use BETWEEN and the same date for both parts (i.e. you want a
> > single
> > day) it appears that the operator isn't smart enough to consider the
> > full day in the cases where the column is a DATETIME
> >
> > http://dev.mysql.com/doc/refman/5.0/en/comparison-
> operators.html#opera
> > tor_be
> > tween
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
> >
> > I actually have to format it like this to get results
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-
> 16
> > 11:59:59'
> >
> 
>  From the Fine Manual...
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-
> conversion.html
> ###
>   Conversion of DATE values:
> 
>  Conversion to a DATETIME or TIMESTAMP value adds a time part of
> '00:00:00' because the DATE value contains no time information.
> ...
>   Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
> values, the time portion of the DATETIME value is ignored, or the
> comparison could be performed as a string compare. Starting from MySQL
> 5.0.42, a DATE value is coerced to the DATETIME type by adding the time
> portion as '00:00:00'. To mimic the old behavior, use the CAST()
> function to cause the comparison operands to be treated as previously.
> For example:
> 
> date_col = CAST(datetime_col AS DATE)
> 
> ###
> 
> That seems pretty clear to me as not a bug.
> --
> Shawn Green
> MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
> and Software, Engineered to Work Together.
> Office: Blountville, TN
> 
> --
> 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: Bug in BETWEEN same DATETIME

[Rick James]

I use this; it keeps me out of trouble whether I am using
* MySQL's DATE vs DATETIME vs TIMESTAMP
* Sybase dates (to minute or to millisecond, hence :59:59 does not work)
* leap year

WHERE dt >= ?
  AND dt <  ? + INTERVAL ? DAY

I fill in the first two "?" with the same starting date.

> -Original Message-
> From: Michael Dykman [mailto:mdyk...@gmail.com]
> Sent: Thursday, May 23, 2013 2:56 PM
> To: MySql
> Subject: Re: Bug in BETWEEN same DATETIME
> 
>   >>  where cast(transaction_date as date) BETWEEN '2013-04-16' AND
> 
> This approach might be problematic in that it requires that every row
> in the source table be examined so that it's transaction_date can be
> casted.
>  The original formulation is more efficient as it allows an index on
> transaction_date to be used, if one exists.
>>> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND
> '2013-04-16 23:59:59'
> 
> Although you probably get the result you want by just incrementing the
> day on the upper-limit.
> WHERE `transaction_date` BETWEEN '2013-04-16 AND '2013-04-17'
> 
>  - michael dykman
> 
> 
> On Thu, May 23, 2013 at 5:07 PM, Peterson, Timothy R <
> timothy_r_peter...@uhc.com> wrote:
> 
> > You probably want
> > where cast(transaction_date as date) BETWEEN '2013-04-16' AND
> > '2013-04-16'
> > That works on my test case
> >
> > You could also change the where clause to be >= date and < date+1
> >
> >
> >
> > -Original Message-
> > From: Daevid Vincent [mailto:dae...@daevid.com]
> > Sent: Thursday, May 23, 2013 3:56 PM
> > To: mysql@lists.mysql.com
> > Subject: Bug in BETWEEN same DATETIME
> >
> > I just noticed what I consider to be a bug; and related, has this
> been
> > fixed in later versions of MySQL?
> >
> > We are using:
> > mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
> > 5.2
> >
> > If you use BETWEEN and the same date for both parts (i.e. you want a
> > single
> > day) it appears that the operator isn't smart enough to consider the
> > full day in the cases where the column is a DATETIME
> >
> > http://dev.mysql.com/doc/refman/5.0/en/comparison-
> operators.html#opera
> > to
> > r_be
> > tween
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
> >
> > I actually have to format it like this to get results
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-
> 16
> > 11:59:59'
> >
> > As it appears that in the first instance it defaults the time to
> > 00:00:00
> > always, as verified by this:
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16
> 11:59:59'
> >
> > So,  I think it's probably safe to assume that if someone is using
> the
> > BETWEEN on datetime columns, their intent more often than not is to
> > get the full 24 hour period, not the 0 seconds it currently pulls by
> > default.
> >
> > I also tried these hacks as per the web page above, but this doesn't
> > yield results either
> >
> > WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
> > CAST('2013-04-16' AS DATE) WHERE `transaction_date` BETWEEN
> > CAST('2013-04-16' AS DATETIME) AND CAST('2013-04-16' AS DATETIME)
> >
> > This one works, but I fail to see how it's any more beneficial than
> > using a string without the CAST() overhead?
> >
> > WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS
> > DATETIME) AND
> > CAST('2013-04-16 11:59:59' AS DATETIME)
> >
> > Or is there some other magical incantation that is supposed to be
> used
> > (without me manually appending the time portion)?
> >
> > This e-mail, including attachments, may include confidential and/or
> > proprietary information, and may be used only by the person or entity
> > to which it is addressed. If the reader of this e-mail is not the
> > intended recipient or his or her authorized agent, the reader is
> > hereby notified that any dissemination, distribution or copying of
> > this e-mail is prohibited. If you have received this e-mail in error,
> > please notify the sender by replying to this message and delete this
> e-mail immediately.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
> 
> 
> --
>  - michael dykman
>  - mdyk...@gmail.com
> 
>  May the Source be with you.

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

Re: Bug in BETWEEN same DATETIME

[Andrew Moore]

Sorry, that was meant to be;

WHERE (new column stored as date) = '2013-04-16'


On Thu, May 23, 2013 at 10:16 PM, Andrew Moore  wrote:

> Personally I don't share your view that it's a bug. Omitting the time
> results in midnight by default so this screws between because there's no
> time between 00:00:00 and 00:00:00.
>
> Are you having operational issues here or are you simply fishing for bugs?
>
> WHERE  `transaction_date` = DATE(datetime)
> or
> WHERE  `transaction_date` = (new column stored as date)
>
>
>
>
>
> On Thu, May 23, 2013 at 9:55 PM, Daevid Vincent  wrote:
>
>> I just noticed what I consider to be a bug; and related, has this been
>> fixed
>> in later versions of MySQL?
>>
>> We are using:
>> mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2
>>
>> If you use BETWEEN and the same date for both parts (i.e. you want a
>> single
>> day) it appears that the operator isn't smart enough to consider the full
>> day in the cases where the column is a DATETIME
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_be
>> tween
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
>>
>> I actually have to format it like this to get results
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
>> 11:59:59'
>>
>> As it appears that in the first instance it defaults the time to 00:00:00
>> always, as verified by this:
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
>>
>> So,  I think it's probably safe to assume that if someone is using the
>> BETWEEN on datetime columns, their intent more often than not is to get
>> the
>> full 24 hour period, not the 0 seconds it currently pulls by default.
>>
>> I also tried these hacks as per the web page above, but this doesn't yield
>> results either
>>
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
>> CAST('2013-04-16' AS DATE)
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
>> CAST('2013-04-16' AS DATETIME)
>>
>> This one works, but I fail to see how it's any more beneficial than using
>> a
>> string without the CAST() overhead?
>>
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
>> AND
>> CAST('2013-04-16 11:59:59' AS DATETIME)
>>
>> Or is there some other magical incantation that is supposed to be used
>> (without me manually appending the time portion)?
>>
>
>

RE: date-IFNULL-sum bug?

[hsv]

 2012/10/08 14:52 -0700, Rick James 
Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR

No, those operations are well defined. Amongst the timestamp-functions there is 
constant reference to numeric context, and character context--and well there 
is, because there are no time-constants, only numerals and character strings 
taken for timestamps. It is also the only means of doing some things.


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

RE: date-IFNULL-sum bug?

[Rick James]

Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR


> -Original Message-
> From: h...@tbbs.net [mailto:h...@tbbs.net]
> Sent: Thursday, October 04, 2012 9:35 PM
> To: mysql@lists.mysql.com
> Subject: date-IFNULL-sum bug?
> 
> Can anyone explain this to me?
> The first one seems quite wrong; the rest make perfect sense.
> 
> mysql> select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
> +--+
> | ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
> +--+
> |11900 |
> +--+
> 1 row in set (0.00 sec)
> 
> mysql> select ifnull(date('1900/5/3'), date('1900/01/01'));
> +--+
> | ifnull(date('1900/5/3'), date('1900/01/01')) |
> +--+
> | 1900-05-03   |
> +--+
> 1 row in set (0.00 sec)
> 
> mysql> select date('1900/5/3') + 1;
> +--+
> | date('1900/5/3') + 1 |
> +--+
> | 19010503 |
> +--+
> 1 row in set (0.00 sec)
> 
> mysql> select date(date('1900/5/3') + 1);
> ++
> | date(date('1900/5/3') + 1) |
> ++
> | 1901-05-03 |
> ++
> 1 row in set (0.00 sec)
> 
> (5.5.8 under muSoft Windows)
> 
> 
> --
> 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

date-IFNULL-sum bug?

[hsv]

Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.

mysql> select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)

mysql> select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03   |
+--+
1 row in set (0.00 sec)

mysql> select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)

mysql> select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)

(5.5.8 under muSoft Windows)


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

MySQL Community Server 5.6.3 has been released (part 2 - bug fixes)

[Hery Ramilison]

Dear MySQL users,

This is the list of bug fixes. For the functional enhancements, see part
1 of this mail:

   Bugs fixed:

  * Incompatible Change: For socket I/O, an optimization for the
case when the server used alarms for timeouts could cause a
slowdown when socket timeouts were used instead.
The fix for this issue results in several changes:

   + Previously, timeouts applied to entire packet-level send
 or receive operations. Now timeouts apply to individual
 I/O operations at a finer level, such as sending 10 bytes
 of a given packet.

   + The handling of packets larger than max_allowed_packet
 has changed. Previously, if an application sent a packet
 bigger than the maximum permitted size, or if the server
 failed to allocate a buffer sufficiently large to hold
 the packet, the server kept reading the packet until its
 end, then skipped it and returned an
 ER_NET_PACKET_TOO_LARGE error. Now the server disconnects
 the session if it cannot handle such large packets.

   + On Windows, the default value for the
 MYSQL_OPT_CONNECT_TIMEOUT option to mysql_options() is no
 longer 20 seconds. Now the default is no timeout
 (infinite), the same as on other platforms.

   + Building and running MySQL on POSIX systems now requires
 support for poll() and O_NONBLOCK. These should be
 available on any modern POSIX system.
(Bug #54790, Bug #11762221, Bug #36225, Bug #11762221)

  * InnoDB Storage Engine: Replication: Trying to update a column,
previously set to NULL, of an InnoDB table with no primary key
caused replication to fail with Can't find record in 'table'
on the slave. (Bug #11766865, Bug #60091)

  * InnoDB Storage Engine: A failed CREATE INDEX operation for an
InnoDB table could result in some memory being allocated but
not freed. This memory leak could affect tables created with
the ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED setting. (Bug
#12699505)

  * InnoDB Storage Engine: Stability is improved when using BLOB
values within InnoDB tables in a heavily loaded system,
especially for tables using the ROW_FORMAT=DYNAMIC or
ROW_FORMAT=COMPRESSED setting. (Bug #12612184)

  * InnoDB Storage Engine: The server could halt if InnoDB
interpreted a very heavy I/O load for 15 minutes or more as an
indication that the server was hung. This change fixes the
logic that measures how long InnoDB threads were waiting,
which formerly could produce false positives. (Bug #11877216,
Bug #11755413, Bug #47183)

  * InnoDB Storage Engine: With the setting
lower_case_table_names=2, inserts into InnoDB tables covered
by foreign key constraints could fail after a server restart.
(Bug #11831040, Bug #60196, Bug #60909)

  * InnoDB Storage Engine: If the server crashed while an XA
transaction was prepared but not yet committed, the
transaction could remain in the system after restart, and
cause a subsequent shutdown to hang. (Bug #11766513, Bug
#59641)

  * InnoDB Storage Engine: With the setting
lower_case_table_names=2, inserts into InnoDB tables covered
by foreign key constraints could fail after a server restart.
This is a similar problem to the foreign key error in Bug
    #11831040 / Bug #60196 / Bug #60909, but with a different root
cause and occurring on Mac OS X.

  * Partitioning: The internal get_partition_set() function did
not take into account the possibility that a key specification
could be NULL in some cases. (Bug #12380149)

  * Partitioning: When executing a row-ordered retrieval index
merge, the partitioning handler used memory from that
allocated for the table, rather than that allocated to the
query, causing table object memory not to be freed until the
table was closed. (Bug #11766249, Bug #59316)

  * Partitioning: Attempting to use ALTER TABLE ... EXCHANGE
PARTITION to exchange a view with a (nonexistent) partition of
a table that was not partitioned caused the server to crash.
(Bug #11766232, Bug #60039)

  * Partitioning: Auto-increment columns of partitioned tables
were checked even when they were not being written to. In
debug builds, this could lead to a server crash. (Bug
    #11765667, Bug #58655)

  * Partitioning: The UNIX_TIMESTAMP() function was not treated as
a monotonic function for purposes of partition pruning. (Bug
    #11746819, Bug #28928)

  * Replication: A mistake in thread cleanup could cause a
replication master to crash. (Bug #12578441)

  * Replication: When using row-based replication and attribute
promotion or demotion (see Section 15.4.1.6.2, "Replication of
Columns Having Different Data Types"), memory allocated
internally for conversion of BLOB columns was not freed
afterwards. (Bug #12558519)

  * Replication: A memory

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 28-8-2011 4:08, shawn wilson wrote:

On Sat, Aug 27, 2011 at 17:33, Arthur Fuller  wrote:

I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column?


A NULL 'value' is special in most operations. It indicates that the 
value is undefined, unknown, uncertain. In this regard it's actually not 
a value.

SELECT 'Uncertain' = TRUE;
Result: 0
SELECT 'Uncertain' = FALSE;
Result: 1
SELECT 'Uncertain' = NULL;
Result: NULL

SELECT NULL = TRUE;
Result: NULL
SELECT NULL = FALSE;
Result: NULL
SELECT NULL = NULL;
Result: NULL

(Unfortunately someone decided to add the <=> operator:
SELECT NULL <=> NULL;
Result: 1
Even stranger is that it is documented as "NULL safe" !?!?)

The advantage to me for having NULL 'values' is that it is usually 
handled as a truly undefined value. (When you compare an undefined value 
with for example 2, the result cannot be TRUE or FALSE. The undefined 
value might be equal to 2, or might not be equal to 2. The result can 
only be undefined.)
To deal with NULL results inside expressions COALESCE() is a very useful 
function.



how does null effect an index? i had always assumed that, since there
is nothing there, that record wouldn't go into the index hence
wouldn't be processed when utilizing the index.


MySQL can use NULL in indexes when executing a query. If there are not 
enough different values in a column (low cardinality) it might be faster 
to do a full table search instead of first reading the index and then 
having to go through the table anyway.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[shawn wilson]

On Sat, Aug 27, 2011 at 17:33, Arthur Fuller  wrote:
> I agree 110%. It is completely pointless to index a column with that amount
> of NULLs. In practical fact I would go further: what is the point of a
> NULLable column? I try to design my tables such that every column is NOT
> NULL. In practice this is not realistic, but I try to adhere to this
> principle whenever I can. For example, it's possible to add a new Hire while
> not yet having determined which department s/he will work in, and hence
> which manager s/he will report to, but typically I deal with such scenarios
> by creating an "Undetermined" value in the corresponding lookup table.
>

maybe this should be a new thread, but...

what's the difference between defining a null value (ie, Undetermined
in your example is the same to you as null)? it would seem that this
would take up more space and take longer to process since null is a
built in (not-)value.

how does null effect an index? i had always assumed that, since there
is nothing there, that record wouldn't go into the index hence
wouldn't be processed when utilizing the index.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Michael Dykman]

It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index.  binary columns all suffer from this.

 - michael dykman


On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer  wrote:

>
> The "innocuous change" was to add an index for "is_robot" which is true
> for 6 out of 20,000 records and null for the rest.
>
> My complaint/question/observation is not how to optimize the query
> that went awry, but to be alarmed that a venerable and perfectly
> serviceable query, written years ago and ignored ever since, suddenly
> brought the system crashing down after making a seemingly innocuous
> change intended to make a marginal improvement on an unrelated query.
>
> I had previously believed that tinkering the schema by adding indexes was a
> safe activity.  It's as though I add a shortcut to my regular commute
> and caused a massive traffic jam when the entire traffic flow tried to
> follow me.
>
> (Both tables are "ok" according to analyze table)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>
>


-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: a lesson in query writing and (maybe) a bug report

[Arthur Fuller]

I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column? I try to design my tables such that every column is NOT
NULL. In practice this is not realistic, but I try to adhere to this
principle whenever I can. For example, it's possible to add a new Hire while
not yet having determined which department s/he will work in, and hence
which manager s/he will report to, but typically I deal with such scenarios
by creating an "Undetermined" value in the corresponding lookup table.

Arthur

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 27-8-2011 22:52, Dave Dyer wrote:

The "innocuous change" was to add an index for "is_robot" which is true
for 6 out of 20,000 records and null for the rest.


Not useful to add an index for that. I also wonder why the value is null 
(meaning: unknown, not certain) for almost all records.


If you want to use such a column in an index it's best to use and index 
base on multiple columns. This makes it more useful for use in queries.



My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly
serviceable query, written years ago and ignored ever since, suddenly
brought the system crashing down after making a seemingly innocuous
change intended to make a marginal improvement on an unrelated query.


Adding an index will most likely trigger some maintenance actions to 
make sure the table is healthy before adding the index.

The query optimizer has an extra index to take into account.


I had previously believed that tinkering the schema by adding
indexeswas a safe activity.


A database should be left alone for a long period. It needs monitoring 
and maintenance. Changes in the schema and even changes in the data can 
lead to changes in the behaviour.
You can make suggestions for the indexes to be used and you can even 
force the use of an index if the query optimizer makes the wrong 
decisions in a case.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a lesson in query writing and (maybe) a bug report

[Dave Dyer]

The "innocuous change" was to add an index for "is_robot" which is true
for 6 out of 20,000 records and null for the rest.

My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly 
serviceable query, written years ago and ignored ever since, suddenly
brought the system crashing down after making a seemingly innocuous
change intended to make a marginal improvement on an unrelated query.

I had previously believed that tinkering the schema by adding indexes was a 
safe activity.  It's as though I add a shortcut to my regular commute
and caused a massive traffic jam when the entire traffic flow tried to
follow me.

(Both tables are "ok" according to analyze table)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 27-8-2011 1:28, Dave Dyer wrote:


Can you post the EXPLAIN EXTENDED output for your before and after queries?
also, have you recently run an ANALYZE TABLE on the tables?


What was the result of ANALYZE TABLE?

What is the engine of the tables involved?


// before


Used keys:

p2.NULL, g.player2, p1.uid

In your original post you wrote: "The according to "explain", the query 
used gmtdate as an index, an excellent choice."
The explain output you posted later indicated that this is not the case 
(anymore).

gmtdate isn't listed as possible index, so what has changed?

> It seems odd that the query optimizer would choose to scan a 3.5
> million entry table instead of a 20,000 entry table.

Let's see.
Before: 28653 * 41 * 1 rows to consider = 1.1 M rows
After: 15292 * 67 * 1 rows to consider = 1.0 M rows

Conclusion: the query optimizer didn't choose to scan an entire table. 
In fact it found a way to have to look at 10% less rows.


For the final order by and limit it would be great to have a (partial) 
index to work with.
It's true that planning indexes isn't always an exact science. Generally 
speaking the goal is to construct both the query and the indexes in a 
way that you rule out as many rows as possible early on in the process.


From your query it becomes evident that you want the latest fifty 
matches between two players who both have the status is_robot "null".
Try to create indexes which cover as many of the columns which are 
involved in the join, where and order parts, and look at the cardinality 
of those indexes. This will determine how many records can be discarded 
in each join and keeps the number of records MySQL has to scan as low as 
possible.


Another way is a bit tricky, but can speed up queries a lot: you want 
the 50 most recent records, so analyse the data and see if you can 
predict how big your result set will be in a period of time. Let's 
assume that there are always between 10 and 50 of such records per day. 
If you want the top 50 it would be safe to limit the search for the last 
10 to 20 days.
Of course this requires an index which includes gmtdate, but it can make 
the result set before the limit a lot smaller.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a lesson in query writing and (maybe) a bug report

[Dave Dyer]

BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a lesson in query writing and (maybe) a bug report

[Dave Dyer]

BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Re: a lesson in query writing and (maybe) a bug report

[Dave Dyer]

>
>
>Can you post the EXPLAIN EXTENDED output for your before and after queries? 
>also, have you recently run an ANALYZE TABLE on the tables?

// before

mysql> explain extended select 
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
->   from players as p1, players as p2, gamerecord g
->   where (p1.uid = g.player1 and p2.uid = g.player2)
-> and (p1.is_robot is null and p2.is_robot is null)
->   order by gmtdate desc limit 50;
++-+---++-+-+-++---+--+---
---+
| id | select_type | table | type   | possible_keys   | key | key_len | ref 
   | rows  | filtered | Extra
   |
++-+---++-+-+-++---+--+---
---+
|  1 | SIMPLE  | p2| ALL| uid,uidindex| NULL| NULL| 
NULL   | 28653 |   100.00 | Using where; Using temporary; Using fi
lesort |
|  1 | SIMPLE  | g | ref| player2,player1 | player2 | 4   | 
tan2.p2.uid|41 |   100.00 |
   |
|  1 | SIMPLE  | p1| eq_ref | uid,uidindex| uid | 4   | 
tan2.g.player1 | 1 |   100.00 | Using where
   |
++-+---++-+-+-++---+--+---
---+
3 rows in set, 1 warning (0.00 sec)


// after


mysql> use tantrix_tantrix;
Database changed
mysql> explain extended select 
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
->   from players as p1, players as p2, gamerecord g
->   where (p1.uid = g.player1 and p2.uid = g.player2)
-> and (p1.is_robot is null and p2.is_robot is null)
->   order by gmtdate desc limit 50;
++-+---++--+-+-+---+---+--+---
---+
| id | select_type | table | type   | possible_keys| key | 
key_len | ref   | rows  | filtered | Extra
   |
++-+---++--+-+-+---+---+--+---
---+
|  1 | SIMPLE  | p1| ref| uid,uidindex,robot_index | robot_index | 
2   | const | 15292 |   100.00 | Using where; U
sing temporary; Using filesort |
|  1 | SIMPLE  | g | ref| player2,player1  | player1 | 
4   | tantrix_tantrix.p1.uid|67 |   100.00 |
   |
|  1 | SIMPLE  | p2| eq_ref | uid,uidindex,robot_index | uid | 
4   | tantrix_tantrix.g.player2 | 1 |   100.00 | Using where
   |
++-+---++--+-+-+---+---+--+---
---+
3 rows in set, 1 warning (0.11 sec)

mysql> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Dan Nelson]

In the last episode (Aug 26), Dave Dyer said:
> This is a cautionary tale - adding indexes is not always helpful or
> harmless.  I recently added an index to the "players" table to optimize a
> common query, and as a consequence this other query flipped from innocuous
> to something that takes infinite time.
> 
> select 
> p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
>  
>  from players as p1, players as p2, gamerecord g 
>  where (p1.uid = g.player1 and p2.uid = g.player2) 
>and (p1.is_robot is null and p2.is_robot is null) 
>  order by gmtdate desc limit 50
> 
> "players" is a table with 20,000 records, "gamerecord" is a table with 3.5
> million records, with "gmtdate" available as an index.  The according to
> "explain", the query used gmtdate as an index, an excellent choice.  When
> I added an index to "is_robot" on the players table, the query flipped to
> using it, and switched from a brisk report to an infinite slog.
> 
> I realize that selecting an index is an imprecise science, and I that can
> specify what index to use as a hint, but this particular flip was
> particularly disastrous.  It seems odd that the query optimizer would
> choose to scan a 3.5 million entry table instead of a 20,000 entry table.

Can you post the EXPLAIN EXTENDED output for your before and after queries? 
also, have you recently run an ANALYZE TABLE on the tables?

-- 
Dan Nelson
dnel...@allantgroup.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a lesson in query writing and (maybe) a bug report

[Dave Dyer]

This is a cautionary tale - adding indexes is not always helpful or harmless.  
I recently added an index to the "players" table to optimize a common query, 
and as a consequence this other query flipped from innocuous to something that 
takes infinite time.


select 
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
 
 from players as p1, players as p2, gamerecord g 
 where (p1.uid = g.player1 and p2.uid = g.player2) 
   and (p1.is_robot is null and p2.is_robot is null) 
 order by gmtdate desc limit 50


"players" is a table with 20,000 records, "gamerecord" is a table with 3.5 
million records, with "gmtdate" available as an index.   The according to 
"explain", the query used gmtdate as an index, an excellent choice.   When I 
added an index to "is_robot" on the players table, the query flipped to using 
it, and switched from a brisk report to an infinite slog.

I realize that selecting an index is an imprecise science, and I that can 
specify what index to use as a hint, but this particular flip was particularly 
disastrous.  It seems odd that the query optimizer would choose to scan a 3.5 
million entry table instead of a 20,000 entry table.


-- 
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 bug

[Velen Vydelingum]

It's something like :

conn.execute ("insert into tmptable select 
supplier_code,0,0,0,0,0-sum(amountpaid),0 from custpayments where 
paymentdate<='2010-12-02' and grnno not in (Select sale_id from 
saletrans_cons where paymode='Credit') group by supplier_code")



Veln



- Original Message - 
From: "Johnny Withers" 

To: "Velen Vydelingum" 
Cc: 
Sent: Sunday, July 24, 2011 17:41
Subject: Re: Query bug



What's your vb code for outputting the results look like?

On Jul 24, 2011 8:22 AM, "Velen Vydelingum"  wrote:

Hi,

I have the following query which is fine when I run it from the mysql 
shell

screen:

select supplier_code,0,0,0,0,0-sum(amountpaid),0 from custpayments where
paymentdate<='2010-12-02' and grnno not in (Select sale_id from
saletrans_cons where paymode='Credit') group by supplier_code

but when I use VB to send this same query the results exclude the last
record.

Can anyone tell me why I'm having this issue with VB?

Thanks.


Veln




--
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 bug

[Johnny Withers]

What's your vb code for outputting the results look like?

On Jul 24, 2011 8:22 AM, "Velen Vydelingum"  wrote:

Hi,

I have the following query which is fine when I run it from the mysql shell
screen:

select supplier_code,0,0,0,0,0-sum(amountpaid),0 from custpayments where
 paymentdate<='2010-12-02' and grnno not in (Select sale_id from
saletrans_cons where paymode='Credit') group by supplier_code

but when I use VB to send this same query the results exclude the last
record.

Can anyone tell me why I'm having this issue with VB?

Thanks.


Veln

Query bug

[Velen Vydelingum]

Hi,

I have the following query which is fine when I run it from the mysql shell 
screen:

select supplier_code,0,0,0,0,0-sum(amountpaid),0 from custpayments where  
paymentdate<='2010-12-02' and grnno not in (Select sale_id from saletrans_cons 
where paymode='Credit') group by supplier_code

but when I use VB to send this same query the results exclude the last record.

Can anyone tell me why I'm having this issue with VB?  

Thanks.


Veln

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

 Sveta Smirnova at Mysql just confirmed this bug in 5.5.13:
http://bugs.mysql.com/45670


On Wed, Jun 15, 2011 at 5:38 PM, Claudio Nanni wrote:

> No worries!
>
> I think I would have figured that out!
>
> I'll feedback you tomorrow.
>
> Thanks again
>
> Claudio
>
> 2011/6/15 Hank 
>
>> Oops... big typo in above steps... add the following line:
>>
>>  replicate-ignore-table=db.log
>>
>> to the SLAVE my.cnf, and restart the SLAVE server.
>>
>> The master does not need to be restarted or changed. Just the SLAVE.
>>
>> Sorry about that.
>>
>> -Hank Eskin
>>
>>
>>
>> On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni > >wrote:
>>
>> > Great investigation Hank,
>> > congratulations.
>> >
>> > I will try this tomorrow morning(11:20pm now)  and let you know if I can
>> > reproduce it on my environments.
>> >
>> > Thanks!
>> >
>> > Claudio
>> >
>> >
>> > 2011/6/15 Hank 
>> >
>> >> Two additional notes:
>> >>
>> >> 1.  Using the "replicate-wild-ignore-table" option in my.cnf produces
>> the
>> >> same results.
>> >>
>> >> 2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master
>> >>  is
>> >> removed and mysql restarted so "db.log" is no longer ignored in
>> >> replication,
>> >> this bug goes away and correct results are reported on the slave.
>> >>
>> >> -Hank Eskin
>> >>
>> >>
>> >> On Wed, Jun 15, 2011 at 4:38 PM, Hank  wrote:
>> >>
>> >> >
>> >> > This is a follow-up to my previous post.  I have been narrowing down
>> >> what
>> >> > is causing this bug.  It is a timing issue of a replication ignored
>> >> table
>> >> > with an auto-increment primary key values leaking over into a
>> >> non-ignored
>> >> > table with inserts immediately after the ignore table has had rows
>> >> inserted.
>> >> >
>> >> > Basically, data from the ignored table is corrupting a non-ignored
>> table
>> >> on
>> >> > the slave upon immediate inserts.
>> >> >
>> >> > Here is how to repeat:
>> >> >
>> >> > On a master issue:
>> >> >
>> >> > use db;
>> >> > drop table test;
>> >> > CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
>> >> >  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
>> >> > drop table log;
>> >> > CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
>> >> varchar(20),
>> >> >  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
>> >> >
>> >> > make sure those two tables are created on the slave through regular
>> >> > replication.
>> >> >
>> >> > on slave>
>> >> >
>> >> > desc test;
>> >> > desc log;
>> >> >
>> >> > Once replicated, on the master, add the following line to the
>> [mysqld]
>> >> > section of my.cnf, and then restart mysql.
>> >> >
>> >> >  replicate-ignore-table=db.log
>> >> >
>> >> > The on the master, issue the following statements as a copy/paste of
>> all
>> >> of
>> >> > them at once.
>> >> > It's critical that the statements are executed in immediate
>> succession
>> >> (no
>> >> > delays)
>> >> >
>> >> > insert into log values (null,"info1");
>> >> > insert into log values (null,"info2");
>> >> > insert into log values (null,"info3");
>> >> > insert into log values (null,"info4");
>> >> > insert into test values (1,null);
>> >> > insert into log values (null,"info5");
>> >> > insert into test values (1,null);
>> >> > insert into log values (null,"info6");
>> >> > insert into test values (2,null);
>> >> > insert into log values (null,"info7");
>> >> > insert into test values (2,null);
>> >> > insert into log values (null,"info8");
>> >> >
>> >> > Here are the results from the master (all correct):
>> >> >
>> >> > master>select * from log;
>> >> > +---+---+
>> >> > | id| log   |
>> >> > +---+---+
>> >> > | 4 | info1 |
>> >> > | 5 | info2 |
>> >> > | 6 | info3 |
>> >> > | 7 | info4 |
>> >> > | 8 | info5 |
>> >> > | 9 | info6 |
>> >> > | 44450 | info7 |
>> >> > | 44451 | info8 |
>> >> > +---+---+
>> >> > master>select * from test;
>> >> > ++-+
>> >> > | id | cnt |
>> >> > ++-+
>> >> > |  1 |   1 |
>> >> > |  1 |   2 |
>> >> > |  2 |   1 |
>> >> > |  2 |   2 |
>> >> > ++-+
>> >> > Here are the results from the slave:
>> >> >
>> >> > slave>select * from log;
>> >> >
>> >> > Empty set (0.00 sec)  <--- as expected, since it is ignored
>> >> >
>> >> > slave>select * from test;
>> >> > ++---+
>> >> > | id | cnt   |
>> >> > ++---+
>> >> > |  1 | 7 |   <-- should be "1", but has values from "log" on the
>> >> master
>> >> > |  1 | 8 |   <-- should be "2"
>> >> > |  2 | 9 |   <-- should be "1"
>> >> > |  2 | 44450 |   <-- should be "2"
>> >> > ++---+
>> >> >
>> >> > If there is the slightest delay between the inserts into "log" and
>> >> "test",
>> >> > the replication happens correctly.
>> >> >
>> >> > Thoughts?
>> >> >
>> >> > -Hank Eskin
>> >> >
>> >>
>> >
>> >
>> >
>> > --
>> > Claudio
>> >
>>
>
>
>
> --
> Claudio
>

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

No worries!

I think I would have figured that out!

I'll feedback you tomorrow.

Thanks again

Claudio

2011/6/15 Hank 

> Oops... big typo in above steps... add the following line:
>
>  replicate-ignore-table=db.log
>
> to the SLAVE my.cnf, and restart the SLAVE server.
>
> The master does not need to be restarted or changed. Just the SLAVE.
>
> Sorry about that.
>
> -Hank Eskin
>
>
>
> On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni  >wrote:
>
> > Great investigation Hank,
> > congratulations.
> >
> > I will try this tomorrow morning(11:20pm now)  and let you know if I can
> > reproduce it on my environments.
> >
> > Thanks!
> >
> > Claudio
> >
> >
> > 2011/6/15 Hank 
> >
> >> Two additional notes:
> >>
> >> 1.  Using the "replicate-wild-ignore-table" option in my.cnf produces
> the
> >> same results.
> >>
> >> 2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master
> >>  is
> >> removed and mysql restarted so "db.log" is no longer ignored in
> >> replication,
> >> this bug goes away and correct results are reported on the slave.
> >>
> >> -Hank Eskin
> >>
> >>
> >> On Wed, Jun 15, 2011 at 4:38 PM, Hank  wrote:
> >>
> >> >
> >> > This is a follow-up to my previous post.  I have been narrowing down
> >> what
> >> > is causing this bug.  It is a timing issue of a replication ignored
> >> table
> >> > with an auto-increment primary key values leaking over into a
> >> non-ignored
> >> > table with inserts immediately after the ignore table has had rows
> >> inserted.
> >> >
> >> > Basically, data from the ignored table is corrupting a non-ignored
> table
> >> on
> >> > the slave upon immediate inserts.
> >> >
> >> > Here is how to repeat:
> >> >
> >> > On a master issue:
> >> >
> >> > use db;
> >> > drop table test;
> >> > CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
> >> >  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> >> > drop table log;
> >> > CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
> >> varchar(20),
> >> >  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
> >> >
> >> > make sure those two tables are created on the slave through regular
> >> > replication.
> >> >
> >> > on slave>
> >> >
> >> > desc test;
> >> > desc log;
> >> >
> >> > Once replicated, on the master, add the following line to the [mysqld]
> >> > section of my.cnf, and then restart mysql.
> >> >
> >> >  replicate-ignore-table=db.log
> >> >
> >> > The on the master, issue the following statements as a copy/paste of
> all
> >> of
> >> > them at once.
> >> > It's critical that the statements are executed in immediate succession
> >> (no
> >> > delays)
> >> >
> >> > insert into log values (null,"info1");
> >> > insert into log values (null,"info2");
> >> > insert into log values (null,"info3");
> >> > insert into log values (null,"info4");
> >> > insert into test values (1,null);
> >> > insert into log values (null,"info5");
> >> > insert into test values (1,null);
> >> > insert into log values (null,"info6");
> >> > insert into test values (2,null);
> >> > insert into log values (null,"info7");
> >> > insert into test values (2,null);
> >> > insert into log values (null,"info8");
> >> >
> >> > Here are the results from the master (all correct):
> >> >
> >> > master>select * from log;
> >> > +---+---+
> >> > | id| log   |
> >> > +---+---+
> >> > | 4 | info1 |
> >> > | 5 | info2 |
> >> > | 6 | info3 |
> >> > | 7 | info4 |
> >> > | 8 | info5 |
> >> > | 9 | info6 |
> >> > | 44450 | info7 |
> >> > | 44451 | info8 |
> >> > +---+---+
> >> > master>select * from test;
> >> > ++-+
> >> > | id | cnt |
> >> > ++-+
> >> > |  1 |   1 |
> >> > |  1 |   2 |
> >> > |  2 |   1 |
> >> > |  2 |   2 |
> >> > ++-+
> >> > Here are the results from the slave:
> >> >
> >> > slave>select * from log;
> >> >
> >> > Empty set (0.00 sec)  <--- as expected, since it is ignored
> >> >
> >> > slave>select * from test;
> >> > ++---+
> >> > | id | cnt   |
> >> > ++---+
> >> > |  1 | 7 |   <-- should be "1", but has values from "log" on the
> >> master
> >> > |  1 | 8 |   <-- should be "2"
> >> > |  2 | 9 |   <-- should be "1"
> >> > |  2 | 44450 |   <-- should be "2"
> >> > ++---+
> >> >
> >> > If there is the slightest delay between the inserts into "log" and
> >> "test",
> >> > the replication happens correctly.
> >> >
> >> > Thoughts?
> >> >
> >> > -Hank Eskin
> >> >
> >>
> >
> >
> >
> > --
> > Claudio
> >
>



-- 
Claudio

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Oops... big typo in above steps... add the following line:

  replicate-ignore-table=db.log

to the SLAVE my.cnf, and restart the SLAVE server.

The master does not need to be restarted or changed. Just the SLAVE.

Sorry about that.

-Hank Eskin



On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni wrote:

> Great investigation Hank,
> congratulations.
>
> I will try this tomorrow morning(11:20pm now)  and let you know if I can
> reproduce it on my environments.
>
> Thanks!
>
> Claudio
>
>
> 2011/6/15 Hank 
>
>> Two additional notes:
>>
>> 1.  Using the "replicate-wild-ignore-table" option in my.cnf produces the
>> same results.
>>
>> 2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master
>>  is
>> removed and mysql restarted so "db.log" is no longer ignored in
>> replication,
>> this bug goes away and correct results are reported on the slave.
>>
>> -Hank Eskin
>>
>>
>> On Wed, Jun 15, 2011 at 4:38 PM, Hank  wrote:
>>
>> >
>> > This is a follow-up to my previous post.  I have been narrowing down
>> what
>> > is causing this bug.  It is a timing issue of a replication ignored
>> table
>> > with an auto-increment primary key values leaking over into a
>> non-ignored
>> > table with inserts immediately after the ignore table has had rows
>> inserted.
>> >
>> > Basically, data from the ignored table is corrupting a non-ignored table
>> on
>> > the slave upon immediate inserts.
>> >
>> > Here is how to repeat:
>> >
>> > On a master issue:
>> >
>> > use db;
>> > drop table test;
>> > CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
>> >  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
>> > drop table log;
>> > CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
>> varchar(20),
>> >  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
>> >
>> > make sure those two tables are created on the slave through regular
>> > replication.
>> >
>> > on slave>
>> >
>> > desc test;
>> > desc log;
>> >
>> > Once replicated, on the master, add the following line to the [mysqld]
>> > section of my.cnf, and then restart mysql.
>> >
>> >  replicate-ignore-table=db.log
>> >
>> > The on the master, issue the following statements as a copy/paste of all
>> of
>> > them at once.
>> > It's critical that the statements are executed in immediate succession
>> (no
>> > delays)
>> >
>> > insert into log values (null,"info1");
>> > insert into log values (null,"info2");
>> > insert into log values (null,"info3");
>> > insert into log values (null,"info4");
>> > insert into test values (1,null);
>> > insert into log values (null,"info5");
>> > insert into test values (1,null);
>> > insert into log values (null,"info6");
>> > insert into test values (2,null);
>> > insert into log values (null,"info7");
>> > insert into test values (2,null);
>> > insert into log values (null,"info8");
>> >
>> > Here are the results from the master (all correct):
>> >
>> > master>select * from log;
>> > +---+---+
>> > | id| log   |
>> > +---+---+
>> > | 4 | info1 |
>> > | 5 | info2 |
>> > | 6 | info3 |
>> > | 7 | info4 |
>> > | 8 | info5 |
>> > | 9 | info6 |
>> > | 44450 | info7 |
>> > | 44451 | info8 |
>> > +---+---+
>> > master>select * from test;
>> > ++-+
>> > | id | cnt |
>> > ++-+
>> > |  1 |   1 |
>> > |  1 |   2 |
>> > |  2 |   1 |
>> > |  2 |   2 |
>> > ++-+
>> > Here are the results from the slave:
>> >
>> > slave>select * from log;
>> >
>> > Empty set (0.00 sec)  <--- as expected, since it is ignored
>> >
>> > slave>select * from test;
>> > ++---+
>> > | id | cnt   |
>> > ++---+
>> > |  1 | 7 |   <-- should be "1", but has values from "log" on the
>> master
>> > |  1 | 8 |   <-- should be "2"
>> > |  2 | 9 |   <-- should be "1"
>> > |  2 | 44450 |   <-- should be "2"
>> > ++---+
>> >
>> > If there is the slightest delay between the inserts into "log" and
>> "test",
>> > the replication happens correctly.
>> >
>> > Thoughts?
>> >
>> > -Hank Eskin
>> >
>>
>
>
>
> --
> Claudio
>

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Great investigation Hank,
congratulations.

I will try this tomorrow morning(11:20pm now)  and let you know if I can
reproduce it on my environments.

Thanks!

Claudio


2011/6/15 Hank 

> Two additional notes:
>
> 1.  Using the "replicate-wild-ignore-table" option in my.cnf produces the
> same results.
>
> 2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master  is
> removed and mysql restarted so "db.log" is no longer ignored in
> replication,
> this bug goes away and correct results are reported on the slave.
>
> -Hank Eskin
>
>
> On Wed, Jun 15, 2011 at 4:38 PM, Hank  wrote:
>
> >
> > This is a follow-up to my previous post.  I have been narrowing down what
> > is causing this bug.  It is a timing issue of a replication ignored table
> > with an auto-increment primary key values leaking over into a non-ignored
> > table with inserts immediately after the ignore table has had rows
> inserted.
> >
> > Basically, data from the ignored table is corrupting a non-ignored table
> on
> > the slave upon immediate inserts.
> >
> > Here is how to repeat:
> >
> > On a master issue:
> >
> > use db;
> > drop table test;
> > CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
> >  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> > drop table log;
> > CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
> varchar(20),
> >  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
> >
> > make sure those two tables are created on the slave through regular
> > replication.
> >
> > on slave>
> >
> > desc test;
> > desc log;
> >
> > Once replicated, on the master, add the following line to the [mysqld]
> > section of my.cnf, and then restart mysql.
> >
> >  replicate-ignore-table=db.log
> >
> > The on the master, issue the following statements as a copy/paste of all
> of
> > them at once.
> > It's critical that the statements are executed in immediate succession
> (no
> > delays)
> >
> > insert into log values (null,"info1");
> > insert into log values (null,"info2");
> > insert into log values (null,"info3");
> > insert into log values (null,"info4");
> > insert into test values (1,null);
> > insert into log values (null,"info5");
> > insert into test values (1,null);
> > insert into log values (null,"info6");
> > insert into test values (2,null);
> > insert into log values (null,"info7");
> > insert into test values (2,null);
> > insert into log values (null,"info8");
> >
> > Here are the results from the master (all correct):
> >
> > master>select * from log;
> > +---+---+
> > | id| log   |
> > +---+---+
> > | 4 | info1 |
> > | 5 | info2 |
> > | 6 | info3 |
> > | 7 | info4 |
> > | 8 | info5 |
> > | 9 | info6 |
> > | 44450 | info7 |
> > | 44451 | info8 |
> > +---+---+
> > master>select * from test;
> > ++-+
> > | id | cnt |
> > ++-+
> > |  1 |   1 |
> > |  1 |   2 |
> > |  2 |   1 |
> > |  2 |   2 |
> > ++-+
> > Here are the results from the slave:
> >
> > slave>select * from log;
> >
> > Empty set (0.00 sec)  <--- as expected, since it is ignored
> >
> > slave>select * from test;
> > ++---+
> > | id | cnt   |
> > ++---+
> > |  1 | 7 |   <-- should be "1", but has values from "log" on the
> master
> > |  1 | 8 |   <-- should be "2"
> > |  2 | 9 |   <-- should be "1"
> > |  2 | 44450 |   <-- should be "2"
> > ++---+
> >
> > If there is the slightest delay between the inserts into "log" and
> "test",
> > the replication happens correctly.
> >
> > Thoughts?
> >
> > -Hank Eskin
> >
>



-- 
Claudio

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Two additional notes:

1.  Using the "replicate-wild-ignore-table" option in my.cnf produces the
same results.

2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master  is
removed and mysql restarted so "db.log" is no longer ignored in replication,
this bug goes away and correct results are reported on the slave.

-Hank Eskin


On Wed, Jun 15, 2011 at 4:38 PM, Hank  wrote:

>
> This is a follow-up to my previous post.  I have been narrowing down what
> is causing this bug.  It is a timing issue of a replication ignored table
> with an auto-increment primary key values leaking over into a non-ignored
> table with inserts immediately after the ignore table has had rows inserted.
>
> Basically, data from the ignored table is corrupting a non-ignored table on
> the slave upon immediate inserts.
>
> Here is how to repeat:
>
> On a master issue:
>
> use db;
> drop table test;
> CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
>  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> drop table log;
> CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20),
>  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
>
> make sure those two tables are created on the slave through regular
> replication.
>
> on slave>
>
> desc test;
> desc log;
>
> Once replicated, on the master, add the following line to the [mysqld]
> section of my.cnf, and then restart mysql.
>
>  replicate-ignore-table=db.log
>
> The on the master, issue the following statements as a copy/paste of all of
> them at once.
> It's critical that the statements are executed in immediate succession (no
> delays)
>
> insert into log values (null,"info1");
> insert into log values (null,"info2");
> insert into log values (null,"info3");
> insert into log values (null,"info4");
> insert into test values (1,null);
> insert into log values (null,"info5");
> insert into test values (1,null);
> insert into log values (null,"info6");
> insert into test values (2,null);
> insert into log values (null,"info7");
> insert into test values (2,null);
> insert into log values (null,"info8");
>
> Here are the results from the master (all correct):
>
> master>select * from log;
> +---+---+
> | id| log   |
> +---+---+
> | 4 | info1 |
> | 5 | info2 |
> | 6 | info3 |
> | 7 | info4 |
> | 8 | info5 |
> | 9 | info6 |
> | 44450 | info7 |
> | 44451 | info8 |
> +---+---+
> master>select * from test;
> ++-+
> | id | cnt |
> ++-+
> |  1 |   1 |
> |  1 |   2 |
> |  2 |   1 |
> |  2 |   2 |
> ++-+
> Here are the results from the slave:
>
> slave>select * from log;
>
> Empty set (0.00 sec)  <--- as expected, since it is ignored
>
> slave>select * from test;
> ++---+
> | id | cnt   |
> ++---+
> |  1 | 7 |   <-- should be "1", but has values from "log" on the master
> |  1 | 8 |   <-- should be "2"
> |  2 | 9 |   <-- should be "1"
> |  2 | 44450 |   <-- should be "2"
> ++---+
>
> If there is the slightest delay between the inserts into "log" and "test",
> the replication happens correctly.
>
> Thoughts?
>
> -Hank Eskin
>

Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

This is a follow-up to my previous post.  I have been narrowing down what is
causing this bug.  It is a timing issue of a replication ignored table with
an auto-increment primary key values leaking over into a non-ignored table
with inserts immediately after the ignore table has had rows inserted.

Basically, data from the ignored table is corrupting a non-ignored table on
the slave upon immediate inserts.

Here is how to repeat:

On a master issue:

use db;
drop table test;
CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
drop table log;
CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20),
 PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;

make sure those two tables are created on the slave through regular
replication.

on slave>

desc test;
desc log;

Once replicated, on the master, add the following line to the [mysqld]
section of my.cnf, and then restart mysql.

 replicate-ignore-table=db.log

The on the master, issue the following statements as a copy/paste of all of
them at once.
It's critical that the statements are executed in immediate succession (no
delays)

insert into log values (null,"info1");
insert into log values (null,"info2");
insert into log values (null,"info3");
insert into log values (null,"info4");
insert into test values (1,null);
insert into log values (null,"info5");
insert into test values (1,null);
insert into log values (null,"info6");
insert into test values (2,null);
insert into log values (null,"info7");
insert into test values (2,null);
insert into log values (null,"info8");

Here are the results from the master (all correct):

master>select * from log;
+---+---+
| id| log   |
+---+---+
| 4 | info1 |
| 5 | info2 |
| 6 | info3 |
| 7 | info4 |
| 8 | info5 |
| 9 | info6 |
| 44450 | info7 |
| 44451 | info8 |
+---+---+
master>select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
|  1 |   2 |
|  2 |   1 |
|  2 |   2 |
++-+
Here are the results from the slave:

slave>select * from log;

Empty set (0.00 sec)  <--- as expected, since it is ignored

slave>select * from test;
++---+
| id | cnt   |
++---+
|  1 | 7 |   <-- should be "1", but has values from "log" on the master
|  1 | 8 |   <-- should be "2"
|  2 | 9 |   <-- should be "1"
|  2 | 44450 |   <-- should be "2"
++---+

If there is the slightest delay between the inserts into "log" and "test",
the replication happens correctly.

Thoughts?

-Hank Eskin

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Very interesting. Waiting for update.
On Jun 15, 2011 4:51 AM, "Hank"  wrote:
>
> The slave is receiving "null" as the statement based insert, not an out of
> range number from the master.
>
> I've been doing more research all day on this bug and have a bit more
> information as to what's causing it.  I plan to write it up tomorrow and
> post it.
>
> Basically, everything works perfectly, until I add a
> "replication-ignore-table=xxx" statement in my.cnf where "xxx" is a
> different table with a unique id INT auto-increment as the single primary
> key  And then the values being inserted into the "test" table (above, not
> ignored) represent the last-insert-id of the replication *ignored* table
on
> the slave
>
> Yeah, pretty strange, I know.  But totally repeatable.
>
> -Hank
>
>
> 2011/6/14 Halász Sándor 
>
> > >>>> 2011/06/13 22:38 -0400, Hank >>>>
> > But that bug report was closed two years ago.  I have no idea if it's
the
> > server sending bad data or the slaves. I think it's the slaves, because
on
> > the slave error, it clearly is getting this statement:  "insert into
test
> > values (1,null)" to replicate, but when it is executed, the "null" is
> > converted into a random number.  But it's happening on all of my slaves,
a
> > mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
> > <<<<<<<<
> >
> > If the master were sending random big numbers, and replication on the
slave
> > in the usual way handled out-of-bound numbers when not allowed to fail,
then
> > 65535 would be an expected value for a signless 16-bit number. Of
course, if
> > this were true, the slave would be getting not that statement but
"insert
> > into test values (1,469422)".
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
> >
> >

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

The slave is receiving "null" as the statement based insert, not an out of
range number from the master.

I've been doing more research all day on this bug and have a bit more
information as to what's causing it.  I plan to write it up tomorrow and
post it.

Basically, everything works perfectly, until I add a
"replication-ignore-table=xxx" statement in my.cnf where "xxx" is a
different table with a unique id INT auto-increment as the single primary
key  And then the values being inserted into the "test" table (above, not
ignored) represent the last-insert-id of the replication *ignored* table on
the slave

Yeah, pretty strange, I know.  But totally repeatable.

-Hank


2011/6/14 Halász Sándor 

> >>>> 2011/06/13 22:38 -0400, Hank >>>>
> But that bug report was closed two years ago.  I have no idea if it's the
> server sending bad data or the slaves. I think it's the slaves, because on
> the slave error, it clearly is getting this statement:  "insert into test
> values (1,null)" to replicate, but when it is executed, the "null" is
> converted into a random number.  But it's happening on all of my slaves, a
> mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
> <<<<<<<<
>
> If the master were sending random big numbers, and replication on the slave
> in the usual way handled out-of-bound numbers when not allowed to fail, then
> 65535 would be an expected value for a signless 16-bit number. Of course, if
> this were true, the slave would be getting not that statement but "insert
> into test values (1,469422)".
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
>
>

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hal�sz S�ndor]

>>>> 2011/06/13 22:38 -0400, Hank >>>>
But that bug report was closed two years ago.  I have no idea if it's the
server sending bad data or the slaves. I think it's the slaves, because on
the slave error, it clearly is getting this statement:  "insert into test
values (1,null)" to replicate, but when it is executed, the "null" is
converted into a random number.  But it's happening on all of my slaves, a
mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
<<<<<<<<

If the master were sending random big numbers, and replication on the slave in 
the usual way handled out-of-bound numbers when not allowed to fail, then 65535 
would be an expected value for a signless 16-bit number. Of course, if this 
were true, the slave would be getting not that statement but "insert into test 
values (1,469422)".


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

That is the slave relay log dump I posted (and mis-labeled). Thanks.

-Hank

On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni wrote:

> You should also have a look at the slave relay log.
>
> But in any case sounds like a bug.
>
> Claudio
> On Jun 14, 2011 5:18 AM, "Hank"  wrote:
> > Both my master and slave bin logs look OK (I think)..
> >
> > master bin log:
> >
> > /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> > DELIMITER /*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > SET @@session.pseudo_thread_id=9/*!*/;
> > SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
> > @@session.unique_checks=1, @@session.autocommit=1/*!*/;
> > SET @@session.sql_mode=0/*!*/;
> > SET @@session.auto_increment_increment=1,
> > @@session.auto_increment_offset=1/*!*/;
> > /*!\C latin1 *//*!*/;
> > SET
> >
> @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
> > SET @@session.lc_time_names=0/*!*/;
> > SET @@session.collation_database=DEFAULT/*!*/;
> > BEGIN
> > /*!*/;
> > use test/*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > insert into test values (1,null)
> > /*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > COMMIT
> > /*!*/;
> >
> >
> > slave relay log:
> >
> > /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> > DELIMITER /*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > SET @@session.pseudo_thread_id=9/*!*/;
> > SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
> > @@session.unique_checks=1, @@session.autocommit=1/*!*/;
> > SET @@session.sql_mode=0/*!*/;
> > SET @@session.auto_increment_increment=1,
> > @@session.auto_increment_offset=1/*!*/;
> > /*!\C latin1 *//*!*/;
> > SET
> >
> @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
> > SET @@session.lc_time_names=0/*!*/;
> > SET @@session.collation_database=DEFAULT/*!*/;
> > BEGIN
> > /*!*/;
> > use test/*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > insert into test values (1,null)
> > /*!*/;
> > SET TIMESTAMP=1308012505/*!*/;
> > COMMIT
> > /*!*/;
> >
> >
> > -Hank
> >
> >
> > On Mon, Jun 13, 2011 at 10:38 PM, Hank  wrote:
> >
> >>
> >> Yes, it's basic out-of-the box mysql replication.
> >>
> >> This appears to be an instance of this bug:
> >> http://bugs.mysql.com/bug.php?id=45670
> >>
> >> But that bug report was closed two years ago. I have no idea if it's the
> >> server sending bad data or the slaves. I think it's the slaves, because
> on
> >> the slave error, it clearly is getting this statement: "insert into test
> >> values (1,null)" to replicate, but when it is executed, the "null" is
> >> converted into a random number. But it's happening on all of my slaves,
> a
> >> mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
> >> <http://bugs.mysql.com/bug.php?id=45670>
> >> -Hank
> >>
> >>
> >>
> >> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni <
> claudio.na...@gmail.com>wrote:
> >>
> >>> Hank,
> >>>
> >>> I can't reproduce it right now,
> >>> But it really seems a bug.
> >>> Just a shot in the dark, Are you sure you have statement based and not
> >>> mixed replication?
> >>> I don't even know if that would affect , just an idea.
> >>>
> >>> Claudio
> >>> On Jun 14, 2011 3:07 AM, "Hank"  wrote:
> >>> > Hello All,
> >>> >
> >>> > I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
> >>> 5.5.8
> >>> > 32 and 64-bit slaves (statement based replication).
> >>> >
> >>> > I'm finding an auto-increment field (part of a compound primary key)
> >>> updates
> >>> > correctly using "null" to insert the next value on the master.. but
> when
> >>> > this statement is replicated on the slaves, instead of inserting the
> >>> next
> >>> > value of the auto-increment field, it inserts 65535 for 'smallint'
> >>> > definitions of 'cnt' and seemingly high random numbers around

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

You should also have a look at the slave relay log.

But in any case sounds like a bug.

Claudio
On Jun 14, 2011 5:18 AM, "Hank"  wrote:
> Both my master and slave bin logs look OK (I think)..
>
> master bin log:
>
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> DELIMITER /*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> SET @@session.pseudo_thread_id=9/*!*/;
> SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
> @@session.unique_checks=1, @@session.autocommit=1/*!*/;
> SET @@session.sql_mode=0/*!*/;
> SET @@session.auto_increment_increment=1,
> @@session.auto_increment_offset=1/*!*/;
> /*!\C latin1 *//*!*/;
> SET
>
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
> SET @@session.lc_time_names=0/*!*/;
> SET @@session.collation_database=DEFAULT/*!*/;
> BEGIN
> /*!*/;
> use test/*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> insert into test values (1,null)
> /*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> COMMIT
> /*!*/;
>
>
> slave bin log:
>
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> DELIMITER /*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> SET @@session.pseudo_thread_id=9/*!*/;
> SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
> @@session.unique_checks=1, @@session.autocommit=1/*!*/;
> SET @@session.sql_mode=0/*!*/;
> SET @@session.auto_increment_increment=1,
> @@session.auto_increment_offset=1/*!*/;
> /*!\C latin1 *//*!*/;
> SET
>
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
> SET @@session.lc_time_names=0/*!*/;
> SET @@session.collation_database=DEFAULT/*!*/;
> BEGIN
> /*!*/;
> use test/*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> insert into test values (1,null)
> /*!*/;
> SET TIMESTAMP=1308012505/*!*/;
> COMMIT
> /*!*/;
>
>
> -Hank
>
>
> On Mon, Jun 13, 2011 at 10:38 PM, Hank  wrote:
>
>>
>> Yes, it's basic out-of-the box mysql replication.
>>
>> This appears to be an instance of this bug:
>> http://bugs.mysql.com/bug.php?id=45670
>>
>> But that bug report was closed two years ago. I have no idea if it's the
>> server sending bad data or the slaves. I think it's the slaves, because
on
>> the slave error, it clearly is getting this statement: "insert into test
>> values (1,null)" to replicate, but when it is executed, the "null" is
>> converted into a random number. But it's happening on all of my slaves, a
>> mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
>> <http://bugs.mysql.com/bug.php?id=45670>
>> -Hank
>>
>>
>>
>> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni wrote:
>>
>>> Hank,
>>>
>>> I can't reproduce it right now,
>>> But it really seems a bug.
>>> Just a shot in the dark, Are you sure you have statement based and not
>>> mixed replication?
>>> I don't even know if that would affect , just an idea.
>>>
>>> Claudio
>>> On Jun 14, 2011 3:07 AM, "Hank"  wrote:
>>> > Hello All,
>>> >
>>> > I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
>>> 5.5.8
>>> > 32 and 64-bit slaves (statement based replication).
>>> >
>>> > I'm finding an auto-increment field (part of a compound primary key)
>>> updates
>>> > correctly using "null" to insert the next value on the master.. but
when
>>> > this statement is replicated on the slaves, instead of inserting the
>>> next
>>> > value of the auto-increment field, it inserts 65535 for 'smallint'
>>> > definitions of 'cnt' and seemingly high random numbers around 469422
for
>>> > definitions of 'int' or 'bigint'.
>>> >
>>> > Easy to repeat:
>>> >
>>> > master:> CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT
NULL
>>> > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
>>> > master:> desc test;
>>> >> +---+-+--+-+-++
>>> > | Field | Type | Null | Key | Default | Extra |
>>> > +---+-+--+-+-++
>>> > | id | int(11) | NO | PRI | NULL | |
>>> > | cnt | int(11) | NO | PRI | NULL | auto_increment |
>>> > +---+---

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Both my master and slave bin logs look OK (I think)..

master bin log:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET TIMESTAMP=1308012505/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
use test/*!*/;
SET TIMESTAMP=1308012505/*!*/;
insert into test values (1,null)
/*!*/;
SET TIMESTAMP=1308012505/*!*/;
COMMIT
/*!*/;


slave bin log:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET TIMESTAMP=1308012505/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
use test/*!*/;
SET TIMESTAMP=1308012505/*!*/;
insert into test values (1,null)
/*!*/;
SET TIMESTAMP=1308012505/*!*/;
COMMIT
/*!*/;


-Hank


On Mon, Jun 13, 2011 at 10:38 PM, Hank  wrote:

>
> Yes, it's basic out-of-the box mysql replication.
>
> This appears to be an instance of this bug:
> http://bugs.mysql.com/bug.php?id=45670
>
> But that bug report was closed two years ago.  I have no idea if it's the
> server sending bad data or the slaves. I think it's the slaves, because on
> the slave error, it clearly is getting this statement:  "insert into test
> values (1,null)" to replicate, but when it is executed, the "null" is
> converted into a random number.  But it's happening on all of my slaves, a
> mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
>  <http://bugs.mysql.com/bug.php?id=45670>
> -Hank
>
>
>
> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni 
> wrote:
>
>> Hank,
>>
>> I can't reproduce it right now,
>> But it really seems a bug.
>> Just a shot in the dark, Are you sure you have statement based and not
>> mixed replication?
>> I don't even know if that would affect , just an idea.
>>
>> Claudio
>>  On Jun 14, 2011 3:07 AM, "Hank"  wrote:
>> > Hello All,
>> >
>> > I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
>> 5.5.8
>> > 32 and 64-bit slaves (statement based replication).
>> >
>> > I'm finding an auto-increment field (part of a compound primary key)
>> updates
>> > correctly using "null" to insert the next value on the master.. but when
>> > this statement is replicated on the slaves, instead of inserting the
>> next
>> > value of the auto-increment field, it inserts 65535 for 'smallint'
>> > definitions of 'cnt' and seemingly high random numbers around 469422 for
>> > definitions of 'int' or 'bigint'.
>> >
>> > Easy to repeat:
>> >
>> > master:> CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
>> > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
>> > master:> desc test;
>> >> +---+-+--+-+-++
>> > | Field | Type | Null | Key | Default | Extra |
>> > +---+-+--+-+-++
>> > | id | int(11) | NO | PRI | NULL | |
>> > | cnt | int(11) | NO | PRI | NULL | auto_increment |
>> > +---+-+--+-+-++
>> >
>> > master:> insert into test values (1,null);
>> > master:> select * from test;
>> > ++-+
>> > | id | cnt |
>> > ++-+
>> > | 1 | 1 | <--- looks good!
>> > ++-+
>> >
>> > slave:> desc test;
>> >> +---+-+--+-+-++
>> > | Field | Type | Null | Key | Default | Extra |
>> > +---+-+--+-+-++
>> > | id | int(11) | NO | PRI | NULL | |
>> > | cnt | int(11) | NO | P

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Yes, it's basic out-of-the box mysql replication.

This appears to be an instance of this bug:
http://bugs.mysql.com/bug.php?id=45670

But that bug report was closed two years ago.  I have no idea if it's the
server sending bad data or the slaves. I think it's the slaves, because on
the slave error, it clearly is getting this statement:  "insert into test
values (1,null)" to replicate, but when it is executed, the "null" is
converted into a random number.  But it's happening on all of my slaves, a
mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
<http://bugs.mysql.com/bug.php?id=45670>
-Hank


On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni wrote:

> Hank,
>
> I can't reproduce it right now,
> But it really seems a bug.
> Just a shot in the dark, Are you sure you have statement based and not
> mixed replication?
> I don't even know if that would affect , just an idea.
>
> Claudio
>  On Jun 14, 2011 3:07 AM, "Hank"  wrote:
> > Hello All,
> >
> > I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
> 5.5.8
> > 32 and 64-bit slaves (statement based replication).
> >
> > I'm finding an auto-increment field (part of a compound primary key)
> updates
> > correctly using "null" to insert the next value on the master.. but when
> > this statement is replicated on the slaves, instead of inserting the next
> > value of the auto-increment field, it inserts 65535 for 'smallint'
> > definitions of 'cnt' and seemingly high random numbers around 469422 for
> > definitions of 'int' or 'bigint'.
> >
> > Easy to repeat:
> >
> > master:> CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
> > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> > master:> desc test;
> >> +---+-+--+-+-++
> > | Field | Type | Null | Key | Default | Extra |
> > +---+-+--+-+-++
> > | id | int(11) | NO | PRI | NULL | |
> > | cnt | int(11) | NO | PRI | NULL | auto_increment |
> > +---+-+--+-+-++
> >
> > master:> insert into test values (1,null);
> > master:> select * from test;
> > ++-+
> > | id | cnt |
> > ++-+
> > | 1 | 1 | <--- looks good!
> > ++-+
> >
> > slave:> desc test;
> >> +---+-+--+-+-++
> > | Field | Type | Null | Key | Default | Extra |
> > +---+-+--+-+-++
> > | id | int(11) | NO | PRI | NULL | |
> > | cnt | int(11) | NO | PRI | NULL | auto_increment |
> > +---+-+--+-+-++
> >
> > slave:> select * from test;
> > +++
> > | id | cnt |
> > +++
> > | 1 | 469422 | < should be "1"
> > +++
> >
> > But the problem continues...
> >
> > master:> insert into test values (1,null);
> > master:> select * from test;
> > ++-+
> > | id | cnt |
> > ++-+
> > | 1 | 1 | <--- correct !
> > | 1 | 2 | <--- correct !
> > ++-+
> >
> > slave> select * from test;
> > +++
> > | id | cnt |
> > +++
> > | 1 | 469422 | < should be "1"
> > | 1 | 470673 | < should be "2"
> > +++
> >
> > Now if I repeat the entire scenario using "smallint" for the 'cnt' field,
> > here are the results:
> >
> > master> CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT
> NULL
> > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> > master> desc test;
> > +---+--+--+-+-++
> > | Field | Type | Null | Key | Default | Extra |
> > +---+--+--+-+-++
> > | id | int(11) | NO | PRI | NULL | |
> > | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
> > +---+--+--+-+-++
> > master:> insert into test values (1,null);
> > master:> select * from test;
> > ++-+
> > | id | cnt |
> > ++-+
> > | 1 | 1 | < correct!
> > ++-+
> >
> > slave> select * from test;
> > +++
> > | id | cnt |
> > +++
> > | 1 | 65535 | < sh

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Hank,

I can't reproduce it right now,
But it really seems a bug.
Just a shot in the dark, Are you sure you have statement based and not mixed
replication?
I don't even know if that would affect , just an idea.

Claudio
 On Jun 14, 2011 3:07 AM, "Hank"  wrote:
> Hello All,
>
> I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
5.5.8
> 32 and 64-bit slaves (statement based replication).
>
> I'm finding an auto-increment field (part of a compound primary key)
updates
> correctly using "null" to insert the next value on the master.. but when
> this statement is replicated on the slaves, instead of inserting the next
> value of the auto-increment field, it inserts 65535 for 'smallint'
> definitions of 'cnt' and seemingly high random numbers around 469422 for
> definitions of 'int' or 'bigint'.
>
> Easy to repeat:
>
> master:> CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
> AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> master:> desc test;
>> +---+-+--+-+-++
> | Field | Type | Null | Key | Default | Extra |
> +---+-+--+-+-++
> | id | int(11) | NO | PRI | NULL | |
> | cnt | int(11) | NO | PRI | NULL | auto_increment |
> +---+-+--+-+-++
>
> master:> insert into test values (1,null);
> master:> select * from test;
> ++-+
> | id | cnt |
> ++-+
> | 1 | 1 | <--- looks good!
> ++-+
>
> slave:> desc test;
>> +---+-+--+-+-++
> | Field | Type | Null | Key | Default | Extra |
> +---+-+--+-+-++
> | id | int(11) | NO | PRI | NULL | |
> | cnt | int(11) | NO | PRI | NULL | auto_increment |
> +---+-+--+-+-++
>
> slave:> select * from test;
> +++
> | id | cnt |
> +++
> | 1 | 469422 | < should be "1"
> +++
>
> But the problem continues...
>
> master:> insert into test values (1,null);
> master:> select * from test;
> ++-+
> | id | cnt |
> ++-+
> | 1 | 1 | <--- correct !
> | 1 | 2 | <--- correct !
> ++-+
>
> slave> select * from test;
> +++
> | id | cnt |
> +++
> | 1 | 469422 | < should be "1"
> | 1 | 470673 | < should be "2"
> +++
>
> Now if I repeat the entire scenario using "smallint" for the 'cnt' field,
> here are the results:
>
> master> CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL
> AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
> master> desc test;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-++
> | id | int(11) | NO | PRI | NULL | |
> | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
> +---+--+--+-+-++
> master:> insert into test values (1,null);
> master:> select * from test;
> ++-+
> | id | cnt |
> ++-+
> | 1 | 1 | < correct!
> ++-+
>
> slave> select * from test;
> +++
> | id | cnt |
> +++
> | 1 | 65535 | < should be "1"
> +++
>
> but this is different:
>
> master:> insert into test values (1,null);
> master:> select * from test;
> ++-+
> | id | cnt |
> ++-+
> | 1 | 1 | < correct!
> | 1 | 2 | < correct!
> ++-+
>
> slave> select * from test;
> ++---+
> | id | cnt |
> ++---+
> | 1 | 65535 | < should be "1", missing second record, too
> ++---+
> slave> show slave status;
>
>  Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default
> database: 'test'. Query: 'insert into test values (1,null)'
>
> .. at which point I have to restart the slave due to the error:
>
> slave> SET GLOBAL sql_slave_skip_counter=1; slave start;
> slave> select * from test;
> ++---+
> | id | cnt |
> ++---+
> | 1 | 65535 | < should be "1", still missing second record, too (of
> course)
> ++---+
>
>
> Now if I manually replicate the statements just on the slave - it works
> perfectly:
>
> slave:> truncate

Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Hello All,

I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8
32 and 64-bit slaves (statement based replication).

I'm finding an auto-increment field (part of a compound primary key) updates
correctly using "null" to insert the next value  on the master.. but when
this statement is replicated on the slaves, instead of inserting the next
value of the auto-increment field, it inserts 65535 for 'smallint'
definitions of 'cnt' and seemingly high random numbers around 469422 for
definitions of 'int' or 'bigint'.

Easy to repeat:

master:>  CREATE TABLE test  ( id int NOT NULL, cnt  int unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
master:>  desc test;
> +---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL||
| cnt   | int(11) | NO   | PRI | NULL| auto_increment |
+---+-+--+-+-++

master:>  insert into test values (1,null);
master:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |   <--- looks good!
++-+

slave:>  desc test;
> +---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL||
| cnt   | int(11) | NO   | PRI | NULL| auto_increment |
+---+-+--+-+-++

slave:> select * from test;
+++
| id | cnt|
+++
|  1 | 469422 | < should be "1"
+++

But the problem continues...

master:>  insert into test values (1,null);
master:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |   <--- correct !
|  1 |   2 |   <--- correct !
++-+

slave> select * from test;
+++
| id | cnt|
+++
|  1 | 469422 |  < should be "1"
|  1 | 470673 |  < should be "2"
+++

Now if I repeat the entire scenario using "smallint" for the 'cnt' field,
here are the results:

master> CREATE TABLE test (id int NOT NULL, cnt smallint unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
master> desc test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| id| int(11)  | NO   | PRI | NULL||
| cnt   | smallint(5) unsigned | NO   | PRI | NULL| auto_increment |
+---+--+--+-+-++
master:>  insert into test values (1,null);
master:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |   < correct!
++-+

slave> select * from test;
+++
| id | cnt|
+++
|  1 | 65535 |< should be "1"
+++

but this is different:

master:>  insert into test values (1,null);
master:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |< correct!
|  1 |   2 |  < correct!
++-+

slave> select * from test;
++---+
| id | cnt   |
++---+
|  1 | 65535 |   < should be "1", missing second record, too
++---+
slave> show slave status;

 Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default
database: 'test'. Query: 'insert into test values (1,null)'

.. at which point I have to restart the slave due to the error:

slave> SET GLOBAL sql_slave_skip_counter=1; slave start;
slave> select * from test;
++---+
| id | cnt   |
++---+
|  1 | 65535 |  < should be "1", still missing second record, too (of
course)
++---+


Now if I manually replicate the statements just on the slave - it works
perfectly:

slave:> truncate table test;
slave:>  insert into test values (1,null);
slave:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
++-+
slave:>  insert into test values (1,null);
slave:>  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
|  1 |   2 |
++-+

So something in the replication code is munging the 'null' into some random
value and trying to insert it.  Seems strange that direct statements would
work, but replicated statements do not.

Nothing really changed on my system, but for some reason, this all started
happening about a week or so ago. I've been running this 5.5.8/5.5.11
con

Re: Bug using 32-bit libmysqlclient on a 64-bit system?

[walter harms]

Am 13.06.2011 18:45, schrieb Alex Gaynor:
> Unfortunately the decision to run 32-bit libs on 64-bit systems is outside
> of my control.  Given that it *should* work I'm more interested in
> diagnosing whether this is a bug of some sort in libmysqlclient or a bug in
> my code/build procedure.


You should starting here: 
http://maketecheasier.com/run-32-bit-apps-in-64-bit-linux/2009/08/10

basicly you have to check that every lib you use is realy 32bit. Missing one is 
asking for
trouble: random bugs, etc.

Basicly everything else like running linux in a LXC Container, a vitual machine 
with qemu, or
simply buy a 32bit box is more maintainable than mixing 32 und 64 bit 
application. They can run
perfectly until some random momentum.

re,
 wh


> Alex
> 
> On Sat, Jun 4, 2011 at 10:06 AM, walter harms  wrote:
> 
>> It is basicly a not clever solution to run 32bit libs with a 64bit system.
>> You have to compile -m32 and all sort of things.
>> It is *way* better to compile with pure 64bit.
>>
>> re,
>>  wh
>>
>> Am 04.06.2011 02:18, schrieb Alex Gaynor:
>>> I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a
>> 64-bit),
>>> and a C program using the libmysqlclient API which behaves very
>> differently
>>> depending on which platform it is compiled for.  The program is:
>>>
>>
>>>
>>
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Bug using 32-bit libmysqlclient on a 64-bit system?

[Alex Gaynor]

Unfortunately the decision to run 32-bit libs on 64-bit systems is outside
of my control.  Given that it *should* work I'm more interested in
diagnosing whether this is a bug of some sort in libmysqlclient or a bug in
my code/build procedure.

Alex

On Sat, Jun 4, 2011 at 10:06 AM, walter harms  wrote:

> It is basicly a not clever solution to run 32bit libs with a 64bit system.
> You have to compile -m32 and all sort of things.
> It is *way* better to compile with pure 64bit.
>
> re,
>  wh
>
> Am 04.06.2011 02:18, schrieb Alex Gaynor:
> > I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a
> 64-bit),
> > and a C program using the libmysqlclient API which behaves very
> differently
> > depending on which platform it is compiled for.  The program is:
> >
>
> >
>

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

On Mon, 06 Jun 2011 18:54 +0200, "Johan De Meersman"
 wrote:
> > Excluding 'performance_schema' appears to eliminate the error. And it
> > seems does NOT cause a reliability-of-the-backup problem.
> 
> Hah, no, backing that up is utterly pointless.

that's a useful/final confirmation. thx.

> No, I do mean 3.3. Apparently the free downloadable version is quite a
> bit behind the commercial one. Maybe that's why I never noticed it
> backing up the performance schema, too :-)

i didn't catch that ZRM's commercial version was at 3.3!  thx.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Johan De Meersman]

- Original Message -
> From: ag...@airpost.net
> 
> Excluding 'performance_schema' appears to eliminate the error. And it
> seems does NOT cause a reliability-of-the-backup problem.

Hah, no, backing that up is utterly pointless. Never noticed it doing that.

It's basically a virtual schema that contains realtime information about the 
database, intended to replace a lot of "show tables" parsing and similar mayhem 
with simple select statements.


> I believe that's an Amanda 3.3 release you're referring to. ZRM is
> still at 2.2,

No, I do mean 3.3. Apparently the free downloadable version is quite a bit 
behind the commercial one. Maybe that's why I never noticed it backing up the 
performance schema, too :-)

They're not really that expensive, either, it might well be worth it to grab 
licenses and support.



-- 
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: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

On Mon, 06 Jun 2011 12:44 +0200, "Johan De Meersman"
 wrote:
> 
> I haven't bothered to look for the "bug", but it seems to me to be quite
> reasonable default behaviour to lock the whole lot when you're dumping
> transactional tables - it ensures you dump all tables from the same
> consistent view.

thanks for the comment.

> I would rather take this up with the ZRM people - it should "just work".

this,

  http://bugs.mysql.com/bug.php?id=61414

suggests the same.  so, i've already started that discussion as well,

  http://forums.zmanda.com/showthread.php?t=3703

Excluding 'performance_schema' appears to eliminate the error. And it
seems does NOT cause a reliability-of-the-backup problem.

> 3.3 came out last week, you may want to have a look at wether it's
> already been adressed there.

I believe that's an Amanda 3.3 release you're referring to. ZRM is still
at 2.2,

 http://www.zmanda.com/download-zrm.php
 "ZRM for MySQL, Version 2.2 is the Latest Stable Release"

and, i've MySQL-zrm-2.2.0-1.noarch installed.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Johan De Meersman]

I haven't bothered to look for the "bug", but it seems to me to be quite 
reasonable default behaviour to lock the whole lot when you're dumping 
transactional tables - it ensures you dump all tables from the same consistent 
view.

I would rather take this up with the ZRM people - it should "just work". 3.3 
came out last week, you may want to have a look at wether it's already been 
adressed there.

Harald's solution is, as usual, technically superior; but he keeps having 
trouble understanding people not wanting to change their entire setup because 
their solution isn't optimal :-)


-- 
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: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

Am 05.06.2011 23:55, schrieb ag...@airpost.net:
> i still have no idea why this is necessary.

take it or not

it is a professional solution which works for
databses with 20 GB every day here with rsync
without interrupt/lock mysqld a second

and it is much faster

> there seems to be a but, problem, misconfiguration, etc.
> wouldn't it make some sense to try to FIX it, rather than setting up a
> completely different server?

it takes 5 minutes starting a replication salve on the same machine
with its own socket/port

> perhaps someone with an idea of the problem and its solution will be
> able to chime in.

wait until it is fixed or think about a better solution
which will work in the future



signature.asc
Description: OpenPGP digital signature

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

i still have no idea why this is necessary.

there seems to be a but, problem, misconfiguration, etc.

wouldn't it make some sense to try to FIX it, rather than setting up a
completely different server?

perhaps someone with an idea of the problem and its solution will be
able to chime in.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

Am 05.06.2011 23:49, schrieb ag...@airpost.net:
> 
> On Sun, 05 Jun 2011 23:29 +0200, "Reindl Harald"
>  wrote:
>> i would use a replication slave and stop him for consistent backups
>> because dumb locks are not really a good solution independent
>> if this works "normally"
> 
> unfortunately, i have no idea what that means.
> 
> something's apparently broken with mysqldump -- enough so that lots of
> people are seeing and reporting this same error after the 5.1 -> 5.5
> upgrade.
> 
> why would setting up a replication slave be necessary or a good solution
> to the problem?

because there is no lock on any production table?
have fun using mysqldump with really hughe databases :-)

a replication slave is synchron, you can stop the slave, copy
the whole datadir and after starting the slave it will
make all changes from the binary log



signature.asc
Description: OpenPGP digital signature

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

On Sun, 05 Jun 2011 23:29 +0200, "Reindl Harald"
 wrote:
> i would use a replication slave and stop him for consistent backups
> because dumb locks are not really a good solution independent
> if this works "normally"

unfortunately, i have no idea what that means.

something's apparently broken with mysqldump -- enough so that lots of
people are seeing and reporting this same error after the 5.1 -> 5.5
upgrade.

why would setting up a replication slave be necessary or a good solution
to the problem?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

On Sun, 05 Jun 2011 23:30 +0200, "Reindl Harald"
 wrote:
> BTW
> WHY is everybody ansering to the list AND the author of the last post?
> this reults in get every message twice :-(

Reply -> sends to ONLY the From == h.rei...@thelounge.net
Reply to all sends to BOTH the From == h.rei...@thelounge.net AND the
list.

I suppose if the list manager software, or your client were configured
differently ...


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

BTW
WHY is everybody ansering to the list AND the author of the last post?
this reults in get every message twice :-(

Am 05.06.2011 23:26, schrieb ag...@airpost.net:
> fwiw, others are seeing this. e.g., in addition to the two bugs i'd
> already referenced,
> 
>  http://www.directadmin.com/forum/showthread.php?p=202053
> 
> and one
> 
>  
> http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command
> 
> claims a solution
> 
>  "Add --skip-add-locks to your mysqldump command"
> 
> which, having added as i mentioned above, to the [mysqldump] section of
> /etc/my.cnf, does NOT make a difference for me.
> 
> 
> On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
>  wrote:
>> the grant statements does nobody interest
>> mysql> select * from mysql.user where user='root' limit 1;
> 
> 
> and, my result for your cmd,
> 
> 
> mysql> select * from mysql.user where user='root' limit 1;
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> | Host  | User | Password  |
> Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
> Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
> Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
> Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
> Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
> Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv
> | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
> x509_issuer | x509_subject | max_questions | max_updates |
> max_connections | max_user_connections | plugin | authentication_string
> |
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> | localhost | root | *3..4 | Y  
> | Y   | Y   | Y   | Y   | Y
> | Y   | Y | Y| Y | Y  |
> Y   | Y  | Y  | Y| Y  |
> Y | Y| Y| Y 
>  | Y| Y| Y  | Y 
>  | Y  | Y| Y  | Y   
> | Y  |  || |
>  | 0 |   0 |   0 |  
>  0 || NULL  |
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> 1 row in set (0.06 sec)
> 
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

hm - bad

i would use a replication slave and stop him for consistent backups
because dumb locks are not really a good solution independent
if this works "normally"

Am 05.06.2011 23:26, schrieb ag...@airpost.net:
> fwiw, others are seeing this. e.g., in addition to the two bugs i'd
> already referenced,
> 
>  http://www.directadmin.com/forum/showthread.php?p=202053
> 
> and one
> 
>  
> http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command
> 
> claims a solution
> 
>  "Add --skip-add-locks to your mysqldump command"
> 
> which, having added as i mentioned above, to the [mysqldump] section of
> /etc/my.cnf, does NOT make a difference for me.
> 
> 
> On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
>  wrote:
>> the grant statements does nobody interest
>> mysql> select * from mysql.user where user='root' limit 1;
> 
> 
> and, my result for your cmd,
> 
> 
> mysql> select * from mysql.user where user='root' limit 1;
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> | Host  | User | Password  |
> Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
> Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
> Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
> Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
> Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
> Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv
> | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
> x509_issuer | x509_subject | max_questions | max_updates |
> max_connections | max_user_connections | plugin | authentication_string
> |
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> | localhost | root | *3..4 | Y  
> | Y   | Y   | Y   | Y   | Y
> | Y   | Y | Y| Y | Y  |
> Y   | Y  | Y  | Y| Y  |
> Y | Y| Y| Y 
>  | Y| Y| Y  | Y 
>  | Y  | Y| Y  | Y   
> | Y  |  || |
>  | 0 |   0 |   0 |  
>  0 || NULL  |
> +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
> 1 row in set (0.06 sec)
> 
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

fwiw, others are seeing this. e.g., in addition to the two bugs i'd
already referenced,

 http://www.directadmin.com/forum/showthread.php?p=202053

and one

 
http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command

claims a solution

 "Add --skip-add-locks to your mysqldump command"

which, having added as i mentioned above, to the [mysqldump] section of
/etc/my.cnf, does NOT make a difference for me.


On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
 wrote:
> the grant statements does nobody interest
> mysql> select * from mysql.user where user='root' limit 1;


and, my result for your cmd,


mysql> select * from mysql.user where user='root' limit 1;
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
| Host  | User | Password  |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv
| Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections | plugin | authentication_string
|
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
| localhost | root | *3..4 | Y  
| Y   | Y   | Y   | Y   | Y
| Y   | Y | Y| Y | Y  |
Y   | Y  | Y  | Y| Y  |
Y | Y| Y| Y 
 | Y| Y| Y  | Y 
 | Y  | Y| Y  | Y   
| Y  |  || |
 | 0 |   0 |   0 |  
 0 || NULL  |
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
1 row in set (0.06 sec)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

the grant statements does nobody interest
maybe use phpmyadmin for a clearer display

mysql> select * from mysql.user where user='root' limit 1;
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
| Host  | User | Password  | Select_priv | 
Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | 
Process_priv | File_priv | Grant_priv |
References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | 
Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | 
Show_view_priv | Create_routine_priv |
Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | 
Create_tablespace_priv | ssl_type | ssl_cipher
| x509_issuer | x509_subject | max_questions | max_updates | max_connections | 
max_user_connections | plugin |
authentication_string |
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
| localhost | root | * | Y   | Y   | Y   | Y
   | Y   | Y |
Y   | Y | Y| Y | Y  | Y 
  | Y  | Y  | Y
   | Y  | Y | Y| Y  
  | Y   | Y
| Y| Y  | Y   | Y   
   | Y| Y
| Y| Y  |  || | 
 | 0 |
  0 |   0 |0 || 
  |
+---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+
1 row in set (0.00 sec)



Am 05.06.2011 23:05, schrieb ag...@airpost.net:
> 
> hi,
> 
> On Sun, 05 Jun 2011 22:24 +0200, "Reindl Harald"
>  wrote:
>> have you checked you permissions-table if all privileges are active for root
> 
> i've got,
> 
>  mysql>  show grants for 'root'@'localhost';
>   
> ++
>   | Grants for root@localhost 
>   
>  |
>   
> ++
>   | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED
>   BY PASSWORD '*3...4' WITH GRANT OPTION  
>|
>   | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION  
>   
>  |
>   
> ++



signature.asc
Description: OpenPGP digital signature

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

hi,

On Sun, 05 Jun 2011 22:24 +0200, "Reindl Harald"
 wrote:
> have you checked you permissions-table if all privileges are active for root

i've got,

 mysql>  show grants for 'root'@'localhost';

++
| Grants for root@localhost 

   |

++
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED
BY PASSWORD '*3...4' WITH GRANT OPTION  
 |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION  

   |

++

and,

 mysql>  show grants for 'drupal_admin'@'localhost';

+--+
| Grants for drupal_admin@localhost 

 |

+--+
| GRANT USAGE ON *.* TO 'drupal_admin'@'localhost' IDENTIFIED BY
PASSWORD '*D...D'   
 |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX,
ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `drupal6`.* TO
'drupal_admin'@'localhost'  |

+--+
17 rows in set (0.00 sec)

are these sufficient?  these permissions worked fine as far as i could
tell for the v5.1.x install i had.

> and have you started "ymsql_upgrade" after all updates?

yes, i'd already executed 'mysql_upgrade', following the instructions
here:
 http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

checking,

 mysql_upgrade
  Looking for 'mysql' as: mysql
  Looking for 'mysqlcheck' as: mysqlcheck
  This installation of MySQL is already upgraded to 5.5.12, use --force
  if you still need to run mysql_upgrade


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[Reindl Harald]

have you checked you permissions-table if all privileges are active for root
and have you started "ymsql_upgrade" after all updates?

Am 05.06.2011 22:20, schrieb ag...@airpost.net:
> i use ZRM to do backups of my databases.
> 
> with v5.1.x, this works fine,
> 
>   mysql-zrm-scheduler --now --backup-set manual --backup-level 0
> 
> to execute a manual backup.
> 
> i recently upgraded from v5.1.x -> v5.5.12,
> 
>  mysqladmin -V
>   mysqladmin  Ver 8.42 Distrib 5.5.12, for Linux on i686
> 
> now, at exec of that backup cmd, i see an ERROR @ console,
> 
>   ...
>   manual:backup:INFO: PHASE START: Creating raw backup
>   manual:backup:INFO: Command used for raw backup is
>   /usr/share/mysql-zrm/plugins/socket-copy.pl
>   --mysqlhotcopy=/usr/bin --host="localhost" --port="3306"
>   --socket="/var/cache/mysql/mysql.sock" --quiet  mysql 
>   "/var/mysql-bkup/manual/20110605131003" >
>   /var/cache/tmp/bZvaQFwQY2 2>&1
>   manual:backup:INFO: raw-databases=mysql
>   manual:backup:INFO: PHASE END: Creating raw backup
>   manual:backup:INFO: PHASE START: Creating logical backup
>   manual:backup:WARNING: The database(s) drupal6
>   performance_schema will be backed up in logical mode since they
>   contain tables that use a transactional engine.
>   manual:backup:INFO: Command used for logical backup is
>   "/usr/bin"/mysqldump --opt --extended-insert --create-options
>   --default-character-set=utf8 --routines --host="localhost"
>   --port="3306" --socket="/var/cache/mysql/mysql.sock" --databases
>   drupal6 performance_schema  >
>   "/var/mysql-bkup/manual/20110605131003/backup.sql"
>   mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
>   user 'root'@'localhost' for table 'cond_instances' when using
>   LOCK TABLES
> --> manual:backup:ERROR: mysqldump did not succeed.
>Command used is "/usr/bin"/mysqldump --opt --extended-insert
>--create-options --default-character-set=utf8 --routines
>--host="localhost" --port="3306"
>--socket="/var/cache/mysql/mysql.sock" --databases drupal6
>performance_schema  >
>"/var/mysql-bkup/manual/20110605131003/backup.sql"manual:backup:INFO:
>PHASE START: Cleanup
>   manual:backup:INFO: backup-status=Backup failed
>   ...
> 
> 
> reading up on the error at,
> 
>  http://bugs.mysql.com/bug.php?id=33762
>  http://bugs.mysql.com/bug.php?id=49633
> 
> it looks to do with mysqldump itself.  i modified in /etc/my.cnf
> 
>   ...
>   [mysqldump]
>   quick
>   quote-names
>   max_allowed_packet = 8M
> +   skip-lock-tables
>   ...
> 
> but that doesn't seem to make any difference.
> 
> something's changed between 5.1.x & 5.5.x.
> 
> what do i need to modify to get past this error?
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES". a bug or my config?

[agd85]

i use ZRM to do backups of my databases.

with v5.1.x, this works fine,

  mysql-zrm-scheduler --now --backup-set manual --backup-level 0

to execute a manual backup.

i recently upgraded from v5.1.x -> v5.5.12,

 mysqladmin -V
  mysqladmin  Ver 8.42 Distrib 5.5.12, for Linux on i686

now, at exec of that backup cmd, i see an ERROR @ console,

...
manual:backup:INFO: PHASE START: Creating raw backup
manual:backup:INFO: Command used for raw backup is
/usr/share/mysql-zrm/plugins/socket-copy.pl
--mysqlhotcopy=/usr/bin --host="localhost" --port="3306"
--socket="/var/cache/mysql/mysql.sock" --quiet  mysql 
"/var/mysql-bkup/manual/20110605131003" >
/var/cache/tmp/bZvaQFwQY2 2>&1
manual:backup:INFO: raw-databases=mysql
manual:backup:INFO: PHASE END: Creating raw backup
manual:backup:INFO: PHASE START: Creating logical backup
manual:backup:WARNING: The database(s) drupal6
performance_schema will be backed up in logical mode since they
contain tables that use a transactional engine.
manual:backup:INFO: Command used for logical backup is
"/usr/bin"/mysqldump --opt --extended-insert --create-options
--default-character-set=utf8 --routines --host="localhost"
--port="3306" --socket="/var/cache/mysql/mysql.sock" --databases
drupal6 performance_schema  >
"/var/mysql-bkup/manual/20110605131003/backup.sql"
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
user 'root'@'localhost' for table 'cond_instances' when using
LOCK TABLES
--> manual:backup:ERROR: mysqldump did not succeed.
 Command used is "/usr/bin"/mysqldump --opt --extended-insert
 --create-options --default-character-set=utf8 --routines
 --host="localhost" --port="3306"
 --socket="/var/cache/mysql/mysql.sock" --databases drupal6
 performance_schema  >
 "/var/mysql-bkup/manual/20110605131003/backup.sql"manual:backup:INFO:
 PHASE START: Cleanup
manual:backup:INFO: backup-status=Backup failed
...


reading up on the error at,

 http://bugs.mysql.com/bug.php?id=33762
 http://bugs.mysql.com/bug.php?id=49633

it looks to do with mysqldump itself.  i modified in /etc/my.cnf

...
[mysqldump]
quick
quote-names
max_allowed_packet = 8M
+   skip-lock-tables
...

but that doesn't seem to make any difference.

something's changed between 5.1.x & 5.5.x.

what do i need to modify to get past this error?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Bug using 32-bit libmysqlclient on a 64-bit system?

[walter harms]

It is basicly a not clever solution to run 32bit libs with a 64bit system.
You have to compile -m32 and all sort of things.
It is *way* better to compile with pure 64bit.

re,
 wh

Am 04.06.2011 02:18, schrieb Alex Gaynor:
> I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a 64-bit),
> and a C program using the libmysqlclient API which behaves very differently
> depending on which platform it is compiled for.  The program is:
> 

> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Bug using 32-bit libmysqlclient on a 64-bit system?

[Alex Gaynor]

I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a 64-bit),
and a C program using the libmysqlclient API which behaves very differently
depending on which platform it is compiled for.  The program is:

#include 
#include 

#include 


int main() {
MYSQL *conn = mysql_init(NULL);
mysql_real_connect(conn, NULL, "root", NULL, "test_mysqldb",
0, NULL, 0);
mysql_real_query(conn, "SHOW COLLATION", strlen("SHOW COLLATION"));
MYSQL_RES *result = mysql_store_result(conn);
int n_fields = mysql_num_fields(result);
MYSQL_FIELD *fields = mysql_fetch_fields(result);
int i;
for (i = 0; i < n_fields; i++) {
printf("%s: %d\n", fields[i].name, fields[i].type);
}
mysql_free_result(result);
mysql_close(conn);
}


When run under 64-bit I get the expected result:

alex@devalex:/tmp$ ./test
Collation: 253
Charset: 253
Id: 8
Default: 253
Compiled: 253
Sortlen: 8

However when run under 32-bit I get something very unexpected:

alex@devalex:/tmp$ ./test32
Collation: 253
CHARACTER_SET_NAME: 142345400
COLLATIONS: 142345464
: 142345496
: 142345584
def: 1280069443


I'm not sure what the issue is, and it may very well be on my end, but any
debugging help you can provide would be great (this was originally extracted
from a bug in a Python MySQL driver I'm working on using the ctypes FFI).

Thanks,
Alex

Re: Interesting bug/oversight

[Johan De Meersman]

- Original Message -
> From: "Dan Nelson" 
> 
> I doubt that mysql calls anything other than gethostbyname() or
> getaddrinfo(), so your behaviour is probably dependant on whatever OS
> you are running and how often its local resolver re-checks resolv.conf.
> Usually that's only once when a program starts.  If you're running bind,
> nscd, or some other intermediate DNS client on your machine, bouncing that
> should work.  If not, you'll need to bounce mysql.

Yep, that was my first though, too. The documentation also confirms that the 
daemon calls gethostbyaddr() and gethostbyname(). However, as I said, it failed 
to switch to the new nameserver upon changing the resolv.conf, and didn't until 
I kicked the daemon in the olives.

Production machine also pointed to the wrong DNS server, but since I can't just 
restart that (badly written Java apps go boom) it still hasn't switched. Adding 
the correct entry to /etc/hosts does work around the issue, further confirming 
that yes, it probably does use the standard resolver.

Random *nix people in the meantime confirm that this is not only a MySQL 
problem; although I can't help but wonder if it would be possible to work 
around it in the flush hosts procedure.


-- 
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: Interesting bug/oversight

[Dan Nelson]

In the last episode (May 19), Johan De Meersman said:
> I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the
> performance impact, that's not an issue.
> 
> I just found out through failing logins that a server was still connecting
> to an old DNS server, and properly updated the resolv.conf.  Commandline
> host lookups then returned correct results.
> 
> However, even after repeated flush hosts commands, the MySQL kept
> returning wrong results.  Only after a full restart did it pick itself up
> and start doing proper lookups.  I strongly suspect that this is due to it
> internally caching the nameserver, too, and not refreshing that along with
> the host cache on a flush hosts command.
> 
> Can anyone confirm this is the case, and wether or not a bug has been
> logged about it?  I can't seem to find one.

I doubt that mysql calls anything other than gethostbyname() or
getaddrinfo(), so your behaviour is probably dependant on whatever OS you
are running and how often its local resolver re-checks resolv.conf.  Usually
that's only once when a program starts.  If you're running bind, nscd, or
some other intermediate DNS client on your machine, bouncing that should
work.  If not, you'll need to bounce mysql.

-- 
Dan Nelson
dnel...@allantgroup.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Interesting bug/oversight

[Johan De Meersman]

- Original Message -

> From: "Claudio Nanni" 

> Consider also the DNS TTL.
That should be irrelevant when changing DNS servers :-) 

> If you flush hosts in MySQL it'll ask again the OS to resolve a name
> , but if that is still in the DNS cache it could return that 'old'
> value instead of querying the newly updated NS.
I know, but it's another DNS server so not applicable. Also, I did verify on 
the commandline :-) 

> I'm not sure thou, may be test by restarting the name server cache
> deamon /etc/rc.d/init.d/nscd restart
Not running local caching. The host only runs MySQL which has it's own cache, 
so that would be a useless layer. 

Nice try :-) 

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

Re: Interesting bug/oversight

[Claudio Nanni]

Johan,

Consider also the DNS TTL.

If you flush hosts in MySQL it'll ask again the OS to resolve a name , but
if that is still in the DNS cache it could return that 'old' value instead
of querying the newly updated NS.

I'm not sure thou, may be test by restarting the name server cache
deamon */etc/rc.d/init.d/nscd
restart *

Claudio



2011/5/19 Johan De Meersman 

> Just encountered an interesting issue.
>
> I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the
> performance impact, that's not an issue.
>
> I just found out through failing logins that a server was still connecting
> to an old DNS server, and properly updated the resolv.conf. Commandline host
> lookups then returned correct results.
>
> However, even after repeated flush hosts commands, the MySQL kept returning
> wrong results. Only after a full restart did it pick itself up and start
> doing proper lookups. I strongly suspect that this is due to it internally
> caching the nameserver, too, and not refreshing that along with the host
> cache on a flush hosts command.
>
> Can anyone confirm this is the case, and wether or not a bug has been
> logged about it? I can't seem to find one.
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



-- 
Claudio

Interesting bug/oversight

[Johan De Meersman]

Just encountered an interesting issue. 

I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the 
performance impact, that's not an issue. 

I just found out through failing logins that a server was still connecting to 
an old DNS server, and properly updated the resolv.conf. Commandline host 
lookups then returned correct results. 

However, even after repeated flush hosts commands, the MySQL kept returning 
wrong results. Only after a full restart did it pick itself up and start doing 
proper lookups. I strongly suspect that this is due to it internally caching 
the nameserver, too, and not refreshing that along with the host cache on a 
flush hosts command. 

Can anyone confirm this is the case, and wether or not a bug has been logged 
about it? I can't seem to find one. 

-- 
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 Community Server 5.6.2 has been released (part 2 - bug fixes)

[Hery Ramilison]

Dear MySQL users,

This is the list of bug fixes. For the functional enhancements, see part 
1 of this mail:


   Bugs fixed:

 * Performance: InnoDB Storage Engine: An UPDATE statement for an
   InnoDB table could be slower than necessary if it changed a
   column covered by a prefix index, but did not change the
   prefix portion of the value. The fix improves performance for
   InnoDB 1.1 in MySQL 5.5 and higher, and the InnoDB Plugin for
   MySQL 5.1. (Bug #58912, Bug #11765900)

 * Incompatible Change: When auto_increment_increment is greater
   than one, values generated by a bulk insert that reaches the
   maximum column value could wrap around rather producing an
   overflow error.
   As a consequence of the fix, it is no longer possible for an
   auto-generated value to be equal to the maximum BIGINT
   UNSIGNED value. It is still possible to store that value
   manually, if the column can accept it. (Bug #39828, Bug
   #11749800)

 * Important Change: Partitioning: Date and time functions used
   as partitioning functions now have the types of their operands
   checked; use of a value of the wrong type is now disallowed in
   such cases. In addition, EXTRACT(WEEK FROM col), where col is
   a DATE or DATETIME column, is now disallowed altogether
   because its return value depends on the value of the
   default_week_format system variable. (Bug #54483, Bug
   #11761948)
   See also Bug #57071, Bug #11764255.

 * Important Change: Replication: The CHANGE MASTER TO statement
   required the value for RELAY_LOG_FILE to be an absolute path,
   while the MASTER_LOG_FILE path could be relative.
   The inconsistent behavior is resolved by permitting relative
   paths for RELAY_LOG_FILE, and by using the same basename for
   RELAY_LOG_FILE as for MASTER_LOG_FILE. For more information,
   see Section 12.5.2.1, "CHANGE MASTER TO Syntax." (Bug #12190,
   Bug #11745232)

 * Partitioning: InnoDB Storage Engine: The partitioning handler
   did not pass locking information to a table's storage engine
   handler. This caused high contention and thus slower
   performance when working with partitioned InnoDB tables. (Bug
   #59013)

 * InnoDB Storage Engine: The presence of a double quotation mark
   inside the COMMENT field for a column could prevent a foreign
   key constraint from being created properly. (Bug #59197, Bug
   #11766154)

 * InnoDB Storage Engine: It was not possible to query the
   information_schema.innodb_trx table while other connections
   were running queries involving BLOB types. (Bug #55397, Bug
   #11762763)

 * Partitioning: Failed ALTER TABLE ... PARTITION statements
   could cause memory leaks. (Bug #56380, Bug #11763641)
       See also Bug #46949, Bug #11755209, Bug #56996, Bug #11764187.

 * Replication: When using the statement-based logging format,
   INSERT ON DUPLICATE KEY UPDATE and INSERT IGNORE statements
   affecting transactional tables that did not fail were not
   written to the binary log if they did not insert any rows.
   (With statement-based logging, all successful statements
   should be logged, whether they do or do not cause any rows to
   be changed.) (Bug #59338, Bug #11766266)

 * Replication: Formerly, STOP SLAVE stopped the slave I/O thread
   first and then stopped the slave SQL thread; thus, it was
   possible for the I/O thread to stop after replicating only
   part of a transaction which the SQL thread was executing, in
   which case---if the transaction could not be rolled back
   safely---the SQL thread could hang.
   Now, STOP SLAVE stops the slave SQL thread first and then
   stops the I/O thread; this guarantees that the I/O thread can
   fetch any remaining events in the transaction that the SQL
   thread is executing, so that the SQL thread can finish the
   transaction if it cannot be rolled back safely. (Bug #58546,
   Bug #11765563)

 * Replication: mysqlbinlog printed USE statements to its output
   only when the default database changed between events. To
   illustrate how this could cause problems, suppose that a user
   issued the following sequence of statements:
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE mytable (column_definitions);
DROP DATABASE mydb;
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE mytable (column_definitions);
   When played back using mysqlbinlog, the second CREATE TABLE
   statement failed with Error: No Database Selected because the
   second USE statement was not played back, due to the fact that
   a database other than mydb was never selected.
   This fix insures that mysqlbinlog outputs a USE statement
   whenever it reads one from the binary l

Got It; Thank You; Re: Mysql Bug 04/01/11

[Thomas Dineen]

Got It; Thank You, Thank You, Thank You



On 4/1/2011 11:28 PM, Claudio Nanni wrote:


Hi Thomas,
Did you run the post install script?

http://kae.li/iiikj

Claudio

On Apr 2, 2011 2:20 AM, "Thomas Dineen" > wrote:

Re: Mysql Bug 04/01/11

[Reindl Harald]

Am 02.04.2011 13:11, schrieb Claudio Nanni:
> Reindl Harald,
> 
> I would appreciate if you could please lower your tones.
> and keep this list as professional as it has always been, this is not a
> nerds forum.
>
> *Problem:* 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't
> find file: './mysql/host.frm' (errno: 13)
> *Solution*: RTFM
> Next time breathe 10 times deep, and reply!

WTF?

"Keep in mind that I have approximately 50  hours into this Mysql server install
and still no results!" and not look ONE TIME in the basic-manual in this
50 hours is the wrong way, everytime, everywhere and with every software

peopole spent many hours to writing documentation!

this is the start BEFORE mailing-list:
http://dev.mysql.com/doc/refman/5.0/en/starting-server.html

yes, is something unclear after that there is a starting-point for questions
but not "i do not read docs, say me exactly where the problem is"





signature.asc
Description: OpenPGP digital signature

Re: Mysql Bug 04/01/11: http://dev.mysql.com/doc/refman/5.0/en/starting-server.html

[Reindl Harald]

Am 02.04.2011 02:18, schrieb Thomas Dineen:

> The following error occurs:
> 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
> './mysql/host.frm' (errno: 13)

BTW: you know google?
"Can't find file: './mysql/host.frm' (errno: 13)"

http://bugs.mysql.com/bug.php?id=1279
so this is a permissions-problem

i guess there is a data-dir in the tar.gz you "installed"
as root and you did not change its permissions

> Keep in mind that I have tried several dozen experiments over many late hours
> with a version host.frm from a older version of mysql with NO success! I have
> tried putting it in every possible location. Using 777 file permissions.

you should not blind copy files without any idea what you are doing

> root@Sun# pwd
> /usr/local/mysql
> root@Sun# ls
> bin infoman mysql-test  var
> host.frmlib my.cnf  share
> include libexec mysql   sql-bench

delete the host.from from here and everywhere you placed it
and please open your eyes if you too lazy read documentations

you see the folder "var"?
_

RTFM (google: "mysql /usr/local default datadir"):
http://dev.mysql.com/doc/refman/5.0/en/starting-server.html

If you get Errcode 13 (which means Permission denied) when starting mysqld, 
this means that the privileges of the
data directory or its contents do not permit server access. In this case, you 
change the permissions for the
involved files and directories so that the server has the right to use them. 
You can also start the server as root,
but this raises security issues and should be avoided.

On Unix, change location into the data directory and check the ownership of the 
data directory and its contents to
make sure the server has access. For example, if the data directory is 
/usr/local/mysql/var, use this command:

shell> ls -la /usr/local/mysql/var

If the data directory or its files or subdirectories are not owned by the login 
account that you use for running
the server, change their ownership to that account. If the account is named 
mysql, use these commands:

shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql/var




signature.asc
Description: OpenPGP digital signature

Re: Mysql Bug 04/01/11

[Claudio Nanni]

Reindl Harald,

I would appreciate if you could please lower your tones.
and keep this list as professional as it has always been, this is not a
nerds forum.
There is always someone that knows more than you but he's not "shouting" at
you everytime you say something wrong.
If you think that a question is not worth answering just dont answer,
while if you answer do it in a useful way, so that all the community can
only improve from it,
do you find this useful?

*Problem:* 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't
find file: './mysql/host.frm' (errno: 13)
*Solution*: RTFM

Next time breathe 10 times deep, and reply!

This is just my opinion.

Sincerely,

Claudio




2011/4/2 Reindl Harald 

>
> Am 02.04.2011 02:18, schrieb Thomas Dineen:
> >> Gentlemen:
> >>
> >> - Keep in mind that I have approximately 50  hours into this Mysql
> server install and still no
> >> results!
>
> what have you done the whole time?
> have you tried RTFM?
>
> we are not here to guide a blind one trough a basic setup
> because at the end you have something running and understodd
> nothing!
>
> sounds like you never before had used mysql and missing
> all the basics from the first chapters in the well written documentation
>
> >> - Regarding the Sun Freeware package mysql-5.0.67-sol10-x86-local.gz
> >>
> >> - When installed and started with the following command:
> >> /usr/local/mysql/bin/mysqld_safe --user=mysql &
> >>
> >> The following error occurs:
> >> 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find
> file: './mysql/host.frm' (errno: 13)
>
> you should read the manuals or use a operating system with packages
> which are idiot-proof, fedora does this for you:
>
> if [ ! -d "$datadir/mysql" ] ; then
>  # First, make sure $datadir is there with correct permissions
>  if [ ! -e "$datadir" -a ! -h "$datadir" ]
>  then
>  mkdir -p "$datadir" || exit 1
>  fi
>  chown mysql:mysql "$datadir"
>  chmod 0755 "$datadir"
>  [ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
>  # Now create the database
>  action $"Initializing MySQL database: " /usr/bin/mysql_install_db
> --datadir="$datadir" --user=mysql
>  ret=$?
>  chown -R mysql:mysql "$datadir"
>  if [ $ret -ne 0 ] ; then
>  return $ret
>  fi
> fi
>
> >> 1) Please identify where the missing file can be found,
> >> keeping in mind that I just installed and have no backup
> >> (of the current rev.)
>
> they CAN NOT BE FOUND if you have a fresh install and not used
> "mysql_install_db"
> and WHERE they are created depends on your configuration as said yesterday
>
> let me guess: you even have not spent any second for your my.cnf?
> > [mysqld]
> > datadir = /where/ever/you/want/your/databases/jesus/christ
>
> on my system typing mysql_ followed with 2 x TAB brings a list of commands
> since you think you must not use a environments package-system why
> in the world do you not look in your bin-folder at /usr/local/mysql/
> and read some docs to understand that the mysql-database is the userdb
>
> [root@srv-rhsoft:~]$ mysql_
> mysql_client_test   mysql_convert_table_format
>  mysql_fix_extensionsmysql_secure_installation
> mysql_tzinfo_to_sql mysql_upgrade_replication   mysql_zap
> mysql_configmysql_find_rows mysql_install_db
>  mysql_setpermission
> mysql_upgrade   mysql_waitpid
>
>


-- 
Claudio

Re: Mysql Bug 04/01/11

[Reindl Harald]

Am 02.04.2011 02:18, schrieb Thomas Dineen:
>> Gentlemen:
>> 
>> - Keep in mind that I have approximately 50  hours into this Mysql server 
>> install and still no
>> results!

what have you done the whole time?
have you tried RTFM?

we are not here to guide a blind one trough a basic setup
because at the end you have something running and understodd
nothing!

sounds like you never before had used mysql and missing
all the basics from the first chapters in the well written documentation

>> - Regarding the Sun Freeware package mysql-5.0.67-sol10-x86-local.gz
>> 
>> - When installed and started with the following command:
>> /usr/local/mysql/bin/mysqld_safe --user=mysql &
>> 
>> The following error occurs:
>> 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
>> './mysql/host.frm' (errno: 13)

you should read the manuals or use a operating system with packages
which are idiot-proof, fedora does this for you:

if [ ! -d "$datadir/mysql" ] ; then
 # First, make sure $datadir is there with correct permissions
 if [ ! -e "$datadir" -a ! -h "$datadir" ]
 then
  mkdir -p "$datadir" || exit 1
 fi
 chown mysql:mysql "$datadir"
 chmod 0755 "$datadir"
 [ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
 # Now create the database
 action $"Initializing MySQL database: " /usr/bin/mysql_install_db 
--datadir="$datadir" --user=mysql
 ret=$?
 chown -R mysql:mysql "$datadir"
 if [ $ret -ne 0 ] ; then
  return $ret
 fi
fi

>> 1) Please identify where the missing file can be found, 
>> keeping in mind that I just installed and have no backup 
>> (of the current rev.)

they CAN NOT BE FOUND if you have a fresh install and not used 
"mysql_install_db"
and WHERE they are created depends on your configuration as said yesterday

let me guess: you even have not spent any second for your my.cnf?
> [mysqld]
> datadir = /where/ever/you/want/your/databases/jesus/christ

on my system typing mysql_ followed with 2 x TAB brings a list of commands
since you think you must not use a environments package-system why
in the world do you not look in your bin-folder at /usr/local/mysql/
and read some docs to understand that the mysql-database is the userdb

[root@srv-rhsoft:~]$ mysql_
mysql_client_test   mysql_convert_table_format  mysql_fix_extensions
mysql_secure_installation
mysql_tzinfo_to_sql mysql_upgrade_replication   mysql_zap
mysql_configmysql_find_rows mysql_install_db
mysql_setpermission
mysql_upgrade   mysql_waitpid



signature.asc
Description: OpenPGP digital signature

Re: Mysql Bug 04/01/11

[Jigal van Hemert]

Hi,

On 2-4-2011 2:18, Thomas Dineen wrote:

Can't find file: './mysql/host.frm' (errno: 13)


http://tinyurl.com/3sc3ydx

--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
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 Bug 04/01/11

[Claudio Nanni]

Hi Thomas,
Did you run the post install script?

http://kae.li/iiikj

Claudio
On Apr 2, 2011 2:20 AM, "Thomas Dineen"  wrote:

Mysql Bug 04/01/11

[Thomas Dineen]

Gentlemen:

- Keep in mind that I have approximately 50  hours into this Mysql 
server install and still no

results!

- Regarding the Sun Freeware package mysql-5.0.67-sol10-x86-local.gz

- When installed and started with the following command:
/usr/local/mysql/bin/mysqld_safe --user=mysql &

The following error occurs:
110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './mysql/host.frm' (errno: 13)


1) Please identify where the missing file can be found, keeping in mind 
that I just installed and

have no backup (of the current rev.).

2) If we are installing in /usr/local/mysql where exactly dose this file 
belong,

(exact path please).

3) What exactly do the file ownersiip and permissions have to be?

Keep in mind that I have tried several dozen experiments over many late 
hours with
a version host.frm from a older version of mysql with NO success! I have 
tried putting

it in every possible location. Using 777 file permissions.

A snapshot of my system:
root@Sun# pwd
/usr/local/mysql
root@Sun# ls
bin infoman mysql-test  var
host.frmlib my.cnf  share
include libexec mysql   sql-bench

root@Sun# ls -la host.frm
-rwxrwxrwx   1 root root9064 Apr  1 16:39 host.frm

root@Sun# ls -la mysql
total 22
drwxrwxrwx   2 root root 512 Apr  1 16:34 .
drwxr-xr-x  13 bin  bin  512 Apr  1 16:39 ..
-rwxrwxrwx   1 root root9064 Apr  1 16:34 host.frm

- The entire transcript is shown below:

10401 16:39:27  mysqld started
110401 16:39:27 [Warning] option 'max_join_size': unsigned value 
18446744073709

551615 adjusted to 4294967295
110401 16:39:27 [Warning] option 'max_join_size': unsigned value 
18446744073709

551615 adjusted to 4294967295
110401 16:39:27 [Warning] option 'thread_stack': unsigned value 65536 
adjusted

to 131072
110401 16:39:27 [Warning] option 'thread_stack': unsigned value 65536 
adjusted

to 131072
InnoDB: Log scan progressed past the checkpoint lsn 0 36808
110401 16:39:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
110401 16:39:27  InnoDB: Starting an apply batch of log records to the 
database

...
InnoDB: Progress in percents: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 
49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 
75 76 77

 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
110401 16:39:27  InnoDB: Started; log sequence number 0 43655
110401 16:39:27 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './my

sql/host.frm' (errno: 13)
110401 16:39:27 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './my

sql/host.frm' (errno: 13)
110401 16:39:27 [ERROR] Fatal error: Can't open and lock privilege 
tables: Can'

t find file: './mysql/host.frm' (errno: 13)
110401 16:39:27  mysqld ended

110401 16:42:30  mysqld started
110401 16:42:30 [Warning] option 'max_join_size': unsigned value 
18446744073709

551615 adjusted to 4294967295
110401 16:42:30 [Warning] option 'max_join_size': unsigned value 
18446744073709

551615 adjusted to 4294967295
110401 16:42:30 [Warning] option 'thread_stack': unsigned value 65536 
adjusted

to 131072
110401 16:42:30 [Warning] option 'thread_stack': unsigned value 65536 
adjusted

to 131072
InnoDB: Log scan progressed past the checkpoint lsn 0 36808
110401 16:42:30  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
110401 16:42:30  InnoDB: Starting an apply batch of log records to the 
database

...
InnoDB: Progress in percents: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 
49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 
75 76 77

 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
110401 16:42:30  InnoDB: Started; log sequence number 0 43655
110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './my

sql/host.frm' (errno: 13)
110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './my

sql/host.frm' (errno: 13)
110401 16:42:30 [ERROR] Fatal error: Can't open and lock privilege 
tables: Can'

t find file: './mysql/host.frm' (errno: 13)
110401 16:42:30  mysqld ended

root@Sun#

--
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.0.67-sol10-x86-local Bug

[Joerg Bruehe]

Hi!


Reindl Harald wrote:
> Am 01.04.2011 04:28, schrieb Thomas Dineen:
>> Gentle People:
>>
>> Using the following startup command: /etc/init.d/mysql.server start
>> I get the following error:
>> 110331 18:49:41 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
>> './mysql/host.frm' (errno: 13)
> 
>> 1) Where do I find or how do I create the file host.frm
> 
> in a backup since your machine crashed

That file may still exist on your machine, you should first check what
"errno 13" means:

joerg:~$ fgrep 13 /usr/include/asm-generic/errno-base.h
#define EACCES  13  /* Permission denied */

> 
>> 2) What is the proper location (Full path please) for host.frm
> 
> in your datadir in the subfolder mysql
> 
>> 3) It would be easier to debug this if your error messages included the full 
>> path!
>> not something like ./ which provides no help in identifing where a file is 
>> required
> 
> it is a totally clear path because it depends on your
> configuration / distribution where your mysql-datadir
> is and ./folder/ is always the database-name

In addition to what Harald wrote:
The MySQL server is using this relative path to access the file, not an
absolute one. Translating the relative path to an absolute one just in
the error message would make analysis even more complicated (and
introduce a chance of pssible errors).

> 
> Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't 
> exist
> is full qualified database.table


Joerg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

-- 
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.0.67-sol10-x86-local Bug

[Reindl Harald]

an apply batch of log records to the 
> database.
> ..
> InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 
> 21
> 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 
> 48
>  49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 
> 74 7
> 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
> InnoDB: Apply batch completed
> 110331 19:06:48  InnoDB: Started; log sequence number 0 43655
> 110331 19:06:48 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
> './mys
> ql/host.frm' (errno: 13)
> 110331 19:06:48 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
> './mys
> ql/host.frm' (errno: 13)
> 110331 19:06:48 [ERROR] Fatal error: Can't open and lock privilege tables: 
> Can't
>  find file: './mysql/host.frm' (errno: 13)
> 110331 19:06:48  mysqld ended
> 
> 110331 19:19:10  mysqld started
> 110331 19:19:10 [Warning] option 'max_join_size': unsigned value 
> 184467440737095
> 51615 adjusted to 4294967295
> 110331 19:19:10 [Warning] option 'max_join_size': unsigned value 
> 184467440737095
> 51615 adjusted to 4294967295
> InnoDB: Log scan progressed past the checkpoint lsn 0 36808
> 110331 19:19:10  InnoDB: Database was not shut down normally!
> InnoDB: Starting crash recovery.
> InnoDB: Reading tablespace information from the .ibd files...
> InnoDB: Restoring possible half-written data pages from the doublewrite
> InnoDB: buffer...
> InnoDB: Doing recovery: scanned up to log sequence number 0 43655
> 110331 19:19:10  InnoDB: Starting an apply batch of log records to the 
> database.
> ..
> InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 
> 21
> 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 
> 48
>  49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 
> 74 7
> 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
> InnoDB: Apply batch completed
> 110331 19:19:10  InnoDB: Started; log sequence number 0 43655
> 110331 19:19:10 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
> './mys
> ql/host.frm' (errno: 13)
> 110331 19:19:10 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: 
> './mys
> ql/host.frm' (errno: 13)
> 110331 19:19:10 [ERROR] Fatal error: Can't open and lock privilege tables: 
> Can't
>  find file: './mysql/host.frm' (errno: 13)
> 110331 19:19:10  mysqld ended
> 
> root@Sun#
> 
> 
> 
> 
> 
> 
> SEND-PR: -*- send-pr -*-
> SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
> SEND-PR: will all comments (text enclosed in `<' and `>').
> SEND-PR:
> From: tdineen
> To: mysql@lists.mysql.com
> Subject: [50 character or so descriptive subject here (for reference)]
> 
>>Description:
> 
>>How-To-Repeat:
> 
>>Fix:
> 
> 
>>Submitter-Id: 
>>Originator:Owner
>>Organization:
> 
>>MySQL support: [none | licence | email support | extended email support ]
>>Synopsis: 
>>Severity: <[ non-critical | serious | critical ] (one line)>
>>Priority: <[ low | medium | high ] (one line)>
>>Category:mysql
>>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>>Release:mysql-5.0.67 (Source distribution)
> 
>>C compiler:gcc (GCC) 3.4.6
>>C++ compiler:  g++ (GCC) 3.4.6
>>Environment:
> 
> System: SunOS Sun5 5.10 Generic_137138-09 i86pc i386 i86pc
> Architecture: i86pc
> 
> Some paths:  /usr/bin/perl /usr/sfw/bin/gmake 
> /export/home/tools/gcc/usr/local/bin/gcc
> /export/home/tools/SolarisStudio12/solstudio12.2/bin/cc
> GCC: Reading specs from 
> /export/home/tools/gcc/usr/local/bin/../lib/gcc/i386-pc-solaris2.10/3.4.6/specs
> Configured with: ../configure --with-as=/usr/ccs/bin/as 
> --with-ld=/usr/ccs/bin/ld --enable-shared
> --enable-languages=c,c++,f77
> Thread model: posix
> gcc version 3.4.6
> Compilation info (call): CC='/usr/local/bin/gcc'  CFLAGS=''  CXX='g++'  
> CXXFLAGS=''  LDFLAGS='-fPIC
> -L/usr/local/lib -R/usr/local/lib -R/usr/lib -L/usr/lib -R/usr/openwin/lib 
> -L/usr/openwin/lib -L/usr/local/ssl/lib
> -R/usr/local/ssl/lib -L/usr/local/BerkeleyDB.4.2/lib 
> -R/usr/local/BerkeleyDB.4.2/lib -L/usr/X11R6/lib
> -R/usr/X11R6/lib'  ASFLAGS=''
> Compilation info (used): CC='/usr/local/bin/gcc'  CFLAGS='-O3 -DDBUG_OFF
> -DHAVE_RWLOCK_T'  CXX='g++' 
> CXXFLAGS='-O3 -DDBUG_OFF-fno-impl

mysql-5.0.67-sol10-x86-local Bug

[Thomas Dineen]

ed

110331 19:19:10  mysqld started
110331 19:19:10 [Warning] option 'max_join_size': unsigned value 
184467440737095

51615 adjusted to 4294967295
110331 19:19:10 [Warning] option 'max_join_size': unsigned value 
184467440737095

51615 adjusted to 4294967295
InnoDB: Log scan progressed past the checkpoint lsn 0 36808
110331 19:19:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
110331 19:19:10  InnoDB: Starting an apply batch of log records to the 
database.

..
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 
19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 
46 47 48
 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 
72 73 74 7

5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
110331 19:19:10  InnoDB: Started; log sequence number 0 43655
110331 19:19:10 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './mys

ql/host.frm' (errno: 13)
110331 19:19:10 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find 
file: './mys

ql/host.frm' (errno: 13)
110331 19:19:10 [ERROR] Fatal error: Can't open and lock privilege 
tables: Can't

 find file: './mysql/host.frm' (errno: 13)
110331 19:19:10  mysqld ended

root@Sun#






SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: tdineen
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

>Description:

>How-To-Repeat:

>Fix:


>Submitter-Id: 
>Originator:Owner
>Organization:

>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: 
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category:mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release:mysql-5.0.67 (Source distribution)

>C compiler:gcc (GCC) 3.4.6
>C++ compiler:  g++ (GCC) 3.4.6
>Environment:

System: SunOS Sun5 5.10 Generic_137138-09 i86pc i386 i86pc
Architecture: i86pc

Some paths:  /usr/bin/perl /usr/sfw/bin/gmake 
/export/home/tools/gcc/usr/local/bin/gcc 
/export/home/tools/SolarisStudio12/solstudio12.2/bin/cc
GCC: Reading specs from 
/export/home/tools/gcc/usr/local/bin/../lib/gcc/i386-pc-solaris2.10/3.4.6/specs
Configured with: ../configure --with-as=/usr/ccs/bin/as 
--with-ld=/usr/ccs/bin/ld --enable-shared --enable-languages=c,c++,f77

Thread model: posix
gcc version 3.4.6
Compilation info (call): CC='/usr/local/bin/gcc'  CFLAGS=''  CXX='g++'  
CXXFLAGS=''  LDFLAGS='-fPIC -L/usr/local/lib -R/usr/local/lib -R/usr/lib 
-L/usr/lib -R/usr/openwin/lib -L/usr/openwin/lib -L/usr/local/ssl/lib 
-R/usr/local/ssl/lib -L/usr/local/BerkeleyDB.4.2/lib 
-R/usr/local/BerkeleyDB.4.2/lib -L/usr/X11R6/lib -R/usr/X11R6/lib'  
ASFLAGS=''
Compilation info (used): CC='/usr/local/bin/gcc'  CFLAGS='-O3 
-DDBUG_OFF-DHAVE_RWLOCK_T'  CXX='g++'  CXXFLAGS='-O3 -DDBUG_OFF
-fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_RWLOCK_T'  
LDFLAGS='-fPIC -L/usr/local/lib -R/usr/local/lib -R/usr/lib -L/usr/lib 
-R/usr/openwin/lib -L/usr/openwin/lib -L/usr/local/ssl/lib 
-R/usr/local/ssl/lib -L/usr/local/BerkeleyDB.4.2/lib 
-R/usr/local/BerkeleyDB.4.2/lib -L/usr/X11R6/lib -R/usr/X11R6/lib '  
ASFLAGS=''

LIBC:
lrwxrwxrwx   1 root root   9 Jan 31 12:43 /lib/libc.so -> 
libc.so.1

-rwxr-xr-x   1 root bin  1401932 Oct  3  2008 /lib/libc.so.1
lrwxrwxrwx   1 root root  19 Jan 31 12:39 /usr/lib/libc.so 
-> ../../lib/libc.so.1
lrwxrwxrwx   1 root root  19 Jan 31 12:39 /usr/lib/libc.so.1 
-> ../../lib/libc.so.1
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--with-openssl' 'CC=/usr/local/bin/gcc' 'CPPFLAGS=-I/usr/local/include 
-I/usr/X11R6/include -I/usr/local/ssl/include 
-I/usr/local/include/ncurses -I/usr/local/BerkeleyDB.4.2/include 
-I/usr/openwin/include -I/usr/local/cups/include 
-I/usr/local/rrdtool-1.2.19/include -I/usr/local/mysql/include 
-I/usr/local/pgsql/include' 'CXX=g++' 'LDFLAGS=-fPIC -L/usr/local/lib 
-R/usr/local/lib -R/usr/lib -L/usr/lib -R/usr/openwin/lib 
-L/usr/openwin/lib -L/usr/local/ssl/lib -R/usr/local/ssl/lib 
-L/usr/local/BerkeleyDB.4.2/lib -R/usr/local/BerkeleyDB.4.2/lib 
-L/usr/X11R6/lib -R/usr/X11R6/lib'



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a crash bug

[Roy Lyseng]

Hi zhongtao,

thank you for reporting this bug. It has been filed as 
http://bugs.mysql.com/bug.php?id=60279


Regards,
Roy

On 24.02.11 08.08, tanzhongt wrote:

create table t1(a int);
create table t2(b int);
   PREPAREstmt FROM "select sum(b) from t2 group by b having b in (select b from 
t1)";
execute stmt;



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a crash bug

[tanzhongt]

Hi：
   I find a crash bug, version is  mysql 5.5.8

   Just try:
   use test;

   drop table if exists t1,t2;
   create table t1(a int);
   create table t2(b int);
  PREPAREstmt FROM "select sum(b) from t2 group by b having b in (select b from 
t1)";
   execute stmt;   --  crash
   zhongtao
   
2011-02-23

bug (or feature?) with mysqli_prepare

[Артем Лапковский]

I have a problem with mysqli_prepare function, it doesn't work properly
calling a stored procedure which contains prepare itself.

PHP example code:


My procedure:

PROCEDURE `real`.testproc_safe2_prep(IN param1 VARCHAR(255), IN param2
VARCHAR(255))
BEGIN

SET @query = 'SELECT field1 FROM test_table WHERE 1=1';

IF param1 IS NOT NULL Then
SET @query = CONCAT(@query, ' AND criteria1 = ', param1);
END IF;

IF param2 IS NOT NULL Then
SET @query = CONCAT(@query, ' AND criteria2 = ', param2);
END IF;

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

When i open php script i get smth like this:
http://i013.radikal.ru/1101/c3/362690f7380d.jpg

I have another procedure, that does all the same as the first one:

PROCEDURE `real`.testproc_safe2_case(IN param1 VARCHAR(255), IN param2
VARCHAR(255))
BEGIN

SELECT field1 FROM test_table
WHERE 1 = 1
AND CASE WHEN param1 IS NULL THEN 1
ELSE CASE WHEN criteria1 = param1 THEN 1
ELSE 0 END END = 1
AND CASE WHEN param2 IS NULL THEN 1
ELSE CASE WHEN criteria2 = param2 THEN 1
ELSE 0 END END = 1;


END

So, when i change this way:
$stmt = mysqli_prepare($db, "call testproc_safe2_case(?,?)");

everything works fine:
http://s52.radikal.ru/i138/1101/26/2e29daf0daa4.jpg

The procedures do the same thing, first using prepare and the second using
case. But why first doesn't work with mysqli_prepare?

Example of launching using console:

mysql> call testproc_safe2_prep(1,1);
+---+
| field1 |
+---+
| Kuznetsov |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call testproc_safe2_case(1,1);
+---+
| field1 |
+---+
| Kuznetsov |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table;
++---+---+---+
| id | field1 | criteria1 | criteria2 |
++---+---+---+
| 1 | Ivanov | 0 | 0 |
| 2 | Petrov | 0 | 1 |
| 3 | Sidorov | 1 | 0 |
| 4 | Kuznetsov | 1 | 1 |
++---+---+---+
4 rows in set (0.00 sec)

mysql> select version();
+-+
| version() |
+-+
| 5.0.45-community-nt |
+-+
1 row in set (0.00 sec)

How i can use mysqli_prepare to work properly with the first procedure?
Maybe it was fixed in new versions or it's not a bug?

Re: Very slow subselect (parser bug)?

[Johan De Meersman]

On Mon, Mar 15, 2010 at 4:22 PM, Pascal Gienger <
pascal.gien...@uni-konstanz.de> wrote:

> Using this result to reconstruct the first left outer join from the initial
> statement, I get this result:
>

Not so much a bug as a missing feature: the parser is currently unable to
recognize any subselect as being fully independent, and will thus execute it
for each and every row in your primary select.

You may or may not get better results by rewriting it so the subselect is a
virtual table. If that doesn't help, subselect into temptable and use that,
or do the reconstruction in code, or other dirty tricks. YMMV.


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