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

[Johan De Meersman]

- Original Message -
> From: "Shawn Green" <shawn.l.gr...@oracle.com>
> 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

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

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

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

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

[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 nick.z.edwa...@gmail.com

 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

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 claudio.na...@gmail.com 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 nick.z.edwa...@gmail.com

 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

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

[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

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

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

[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

[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 eroomy...@gmail.com 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 dae...@daevid.com 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

[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

[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

[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

[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

[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

[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 dae...@daevid.com 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 leak could occur when re

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 Fullerfuller.art...@gmail.com  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

[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]

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

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

[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 ddyer-my...@real-me.net 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

[shawn wilson]

On Sat, Aug 27, 2011 at 17:33, Arthur Fuller fuller.art...@gmail.com 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

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

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

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

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

[Johnny Withers]

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

On Jul 24, 2011 8:22 AM, Velen Vydelingum ve...@biz-mu.com 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

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 joh...@pixelated.net

To: Velen Vydelingum ve...@biz-mu.com
Cc: mysql@lists.mysql.com
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 ve...@biz-mu.com 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: 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 claudio.na...@gmail.comwrote:

 No worries!

 I think I would have figured that out!

 I'll feedback you tomorrow.

 Thanks again

 Claudio

 2011/6/15 Hank hes...@gmail.com

 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 claudio.na...@gmail.com
 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 hes...@gmail.com
 
  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 hes...@gmail.com 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):
  
   masterselect * from log;
   +---+---+
   | id| log   |
   +---+---+
   | 4 | info1 |
   | 5 | info2 |
   | 6 | info3 |
   | 7 | info4 |
   | 8 | info5 |
   | 9 | info6 |
   | 44450 | info7 |
   | 44451 | info8 |
   +---+---+
   masterselect * from test;
   ++-+
   | id | cnt |
   ++-+
   |  1 |   1 |
   |  1 |   2 |
   |  2 |   1 |
   |  2 |   2 |
   ++-+
   Here are the results from the slave:
  
   slaveselect * from log;
  
   Empty set (0.00 sec)  --- as expected, since it is ignored
  
   slaveselect * 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 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 hes...@gmail.com 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 h...@tbbs.net

   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
 
 

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):

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

slaveselect * from log;

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

slaveselect * 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 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 hes...@gmail.com 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):

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

 slaveselect * from log;

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

 slaveselect * 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 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 hes...@gmail.com

 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 hes...@gmail.com 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):
 
  masterselect * from log;
  +---+---+
  | id| log   |
  +---+---+
  | 4 | info1 |
  | 5 | info2 |
  | 6 | info3 |
  | 7 | info4 |
  | 8 | info5 |
  | 9 | info6 |
  | 44450 | info7 |
  | 44451 | info8 |
  +---+---+
  masterselect * from test;
  ++-+
  | id | cnt |
  ++-+
  |  1 |   1 |
  |  1 |   2 |
  |  2 |   1 |
  |  2 |   2 |
  ++-+
  Here are the results from the slave:
 
  slaveselect * from log;
 
  Empty set (0.00 sec)  --- as expected, since it is ignored
 
  slaveselect * 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]

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 claudio.na...@gmail.comwrote:

 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 hes...@gmail.com

 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 hes...@gmail.com 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):
 
  masterselect * from log;
  +---+---+
  | id| log   |
  +---+---+
  | 4 | info1 |
  | 5 | info2 |
  | 6 | info3 |
  | 7 | info4 |
  | 8 | info5 |
  | 9 | info6 |
  | 44450 | info7 |
  | 44451 | info8 |
  +---+---+
  masterselect * from test;
  ++-+
  | id | cnt |
  ++-+
  |  1 |   1 |
  |  1 |   2 |
  |  2 |   1 |
  |  2 |   2 |
  ++-+
  Here are the results from the slave:
 
  slaveselect * from log;
 
  Empty set (0.00 sec)  --- as expected, since it is ignored
 
  slaveselect * 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]

No worries!

I think I would have figured that out!

I'll feedback you tomorrow.

Thanks again

Claudio

2011/6/15 Hank hes...@gmail.com

 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 claudio.na...@gmail.com
 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 hes...@gmail.com
 
  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 hes...@gmail.com 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):
  
   masterselect * from log;
   +---+---+
   | id| log   |
   +---+---+
   | 4 | info1 |
   | 5 | info2 |
   | 6 | info3 |
   | 7 | info4 |
   | 8 | info5 |
   | 9 | info6 |
   | 44450 | info7 |
   | 44451 | info8 |
   +---+---+
   masterselect * from test;
   ++-+
   | id | cnt |
   ++-+
   |  1 |   1 |
   |  1 |   2 |
   |  2 |   1 |
   |  2 |   2 |
   ++-+
   Here are the results from the slave:
  
   slaveselect * from log;
  
   Empty set (0.00 sec)  --- as expected, since it is ignored
  
   slaveselect * 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 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 hes...@gmail.com 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 hes...@gmail.com 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 hes...@gmail.com 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

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 claudio.na...@gmail.comwrote:

 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 hes...@gmail.com 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 hes...@gmail.com 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.comwrote:
 
  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 hes...@gmail.com 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

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]

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 h...@tbbs.net

  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: 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 wha...@bfs.de 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: 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 wha...@bfs.de 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

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
configuration for months now (since 5.5.8 was released).The PHP code
that does this hasn't changed one bit, and this is a simplified version of
the database and code that is running in production.

Additional note: If I drop the 'id' field, and the primary key is just the
auto-increment field, it works correctly in replication.

Any ideas?  Can anyone else replicate

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 hes...@gmail.com 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 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
 configuration for months now (since 5.5.8 was released). The PHP code
 that does this hasn't changed one bit, and this is a simplified version of
 the database and code that is running in production.

 Additional note: If I drop

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 claudio.na...@gmail.comwrote:

 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 hes...@gmail.com 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 table test;
  slave: insert into test values (1,null

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 hes...@gmail.com 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.comwrote:

 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 hes...@gmail.com 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

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?

[agd85]

On Mon, 06 Jun 2011 12:44 +0200, Johan De Meersman
vegiv...@tuxera.be 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]

- 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 18:54 +0200, Johan De Meersman
vegiv...@tuxera.be 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

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 21
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.sqlmanual: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: 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 21
   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.sqlmanual: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

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
h.rei...@thelounge.net 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]

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
 h.rei...@thelounge.net 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]

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
h.rei...@thelounge.net 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]

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
 h.rei...@thelounge.net 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]

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
 h.rei...@thelounge.net 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]

On Sun, 05 Jun 2011 23:30 +0200, Reindl Harald
h.rei...@thelounge.net 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?

[agd85]

On Sun, 05 Jun 2011 23:29 +0200, Reindl Harald
h.rei...@thelounge.net 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?

[Reindl Harald]

Am 05.06.2011 23:49, schrieb ag...@airpost.net:
 
 On Sun, 05 Jun 2011 23:29 +0200, Reindl Harald
 h.rei...@thelounge.net 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]

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: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: 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 stdio.h
#include string.h

#include mysql.h


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

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 

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 vegiv...@tuxera.be

 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

Re: Interesting bug/oversight

[Johan De Meersman]

- Original Message -

 From: Claudio Nanni claudio.na...@gmail.com

 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

[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: Dan Nelson dnel...@allantgroup.com
 
 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

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 log. (Bug #50914

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 tdin...@ix.netcom.com wrote:

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

[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

[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 h.rei...@thelounge.net


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

[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

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 tdin...@ix.netcom.com 
mailto:tdin...@ix.netcom.com wrote:

Re: mysql-5.0.67-sol10-x86-local Bug

[Reindl Harald]

 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:
 precise description of the problem (multiple lines)
How-To-Repeat:
 code/input/activities to reproduce the problem (multiple lines)
Fix:
 how to correct or work around the problem, if known (multiple lines)
 
Submitter-Id: submitter ID
Originator:Owner
Organization:
 organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support ]
Synopsis: synopsis of the problem (one line)
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:
 machine, os, target, libraries (multiple lines)
 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

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

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

mysql-5.0.67-sol10-x86-local Bug

[Thomas Dineen]

': 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:
precise description of the problem (multiple lines)
How-To-Repeat:
code/input/activities to reproduce the problem (multiple lines)
Fix:
how to correct or work around the problem, if known (multiple lines)

Submitter-Id: submitter ID
Originator:Owner
Organization:
organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support ]
Synopsis: synopsis of the problem (one line)
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:
machine, os, target, libraries (multiple lines)
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:
?
$db = mysqli_connect(localhost,user,password,real);
$stmt = mysqli_prepare($db, call testproc_safe2_prep(?,?));
mysqli_stmt_bind_param($stmt,'ss', $_GET['param1'],$_GET['param2']);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name);

while (mysqli_stmt_fetch($stmt)) {
echo $name;
echo  ;
}
mysqli_stmt_close($stmt);
?

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?

Very slow subselect (parser bug)?

[Pascal Gienger]

 |  0 | NULL  | Sflnh 
QK/Flkudduiwmkbdqz |

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

The same result, but not in 1 Minute but in less than the tenth of a 
second, including the inner select step.


Is this a bug in the SQL parser?

--
Pascal Gienger
University of Konstanz, IT Services Department (Rechenzentrum)
Electronic Communications and Web Services
Building V, Room V404, Phone +49 7531 88 5048, Fax +49 7531 88 3739

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