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