tag:blogger.com,1999:blog-81321022219577803852024-02-20T18:13:53.694+02:00my mysql related ramblingsA blog about MySQL and other bits and piecesUnknownnoreply@blogger.comBlogger35125tag:blogger.com,1999:blog-8132102221957780385.post-37553139397213006322014-08-01T09:51:00.001+02:002014-08-01T13:26:36.135+02:00Basic Windows MySQL Installation Without InstallerIt dawned on me that most folks are using the Installer these days.<br />
<br />
As I need quick access to every MySQL version, using an Installer is never an option.<br />
And for folks wanting 100% control over their setups, they may not want an installer doing things.<br />
<br />
So this shows how to setup an instance manually.<br />
<ol>
<li>download non-installer .zip version from dev.mysql.com</li>
<li>create a directory c:\mysql and c:\mysql\tmp</li>
<li>unzip the .zip into c:\mysql</li>
<li>move the data directory into c:\mysql for easier future upgrades</li>
<li>create a basic my.ini</li>
<li>install the service</li>
<li>start the service</li>
</ol>
<br />
<div>
<br /></div>
<div>
Here I'll show each step with more detail. I purposely leave out things like post-installation security, to keep it simple.</div>
<div>
<br /></div>
<h3>
1. Create a directory.</h3>
<div>
Decide where you will put the installation and datadir. I use c:\mysql and c:\mysql\data</div>
<div>
since I truly despise the "windows way" with long paths such as "c:\Program Files\MySQL Server 5.6" ...</div>
<div>
<br /></div>
<div>
<blockquote class="tr_bq">
C:\>mkdir mysql<br />
C:\>cd mysql<br />
C:\mysql>mkdir tmp<br />
C:\mysql>dir<br />
Volume in drive C has no label.<br />
Volume Serial Number is 802E-2730<br />
Directory of C:\mysql<br />
2014/08/01 09:31 <DIR> .<br />
2014/08/01 09:31 <DIR> ..<br />
2014/08/01 09:31 <DIR> tmp<br />
0 File(s) 0 bytes<br />
3 Dir(s) 74 040 700 928 bytes free</blockquote>
</div>
<div>
<blockquote class="tr_bq">
<br /></blockquote>
</div>
<h3>
2. Download the non-installer .zip version.</h3>
<div>
Use a browser to download the latest version, for example: </div>
<div>
<a href="http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.20-win32.zip"> http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.20-win32.zip</a></div>
<div>
<br /></div>
<div>
<br /></div>
<h3>
3. Extract the zip file.</h3>
<div>
<br /></div>
<div>
<div>
I use 7zip or winrar, but windows explorer can also be used to extract the .zip file right here.</div>
<div>
<br /></div>
</div>
<div>
<br /></div>
<h3>
4. Move the data directory</h3>
<div>
The non-installer .zip comes with a data directory which I will use in this installation.</div>
<div>
As you might want to upgrade the instance later, I prefer to put the datadir a separate location to the version just downloaded.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<blockquote class="tr_bq">
C:\mysql>dir<br />
Volume in drive C has no label.<br />
Volume Serial Number is 802E-2730<br />
Directory of C:\mysql<br />
2014/08/01 08:11 <DIR> .<br />
2014/08/01 08:11 <DIR> ..<br />
2014/08/01 08:11 <DIR> tmp<br />
2014/08/01 08:11 <DIR> mysql-5.6.20-win32<br />
2014/08/01 07:52 353 970 000 mysql-5.6.20-win32.zip<br />
1 File(s) 353 970 000 bytes<br />
4 Dir(s) 73 969 897 472 bytes free<br />
C:\mysql><b>move mysql-5.6.20-win32\data data</b><br />
1 dir(s) moved.</blockquote>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span></div>
</div>
<div>
<br /></div>
<h3>
<br /><div>
<span class="Apple-tab-span" style="font-weight: normal; white-space: pre;"><span style="font-size: small;"><br /></span></span></div>
5. Write the my.ini.</h3>
<div>
I'll keep the my.ini in the datadir, to lesson complexity.</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span></div>
<blockquote class="tr_bq">
<br />
C:\mysql>notepad data\my.ini<br />
C:\mysql>type data\my.ini<br />
<b>[mysqld]<br />datadir=c:/mysql/data<br />tmpdir=c:/mysql/tmp<br />log-error=c:/mysql/data/mysql.err<br />port=3306<br />slow-start-timeout=0<br />log-warnings=2</b></blockquote>
<div>
<br /></div>
<div>
<br /></div>
<h3>
6. Install mysqld as a service.</h3>
<div>
This part seems confusing due to the options used. To keep things clear, I use a specific service name for each version, so that I know what it is later.</div>
<div>
You must be running cmd.exe as an administrative user to do this.</div>
<div>
<div>
<br /></div>
<blockquote class="tr_bq">
<br />
C:\mysql>cd mysql-5.6.20-win32<br />
C:\mysql\mysql-5.6.20-win32>cd bin<br />
C:\mysql\mysql-5.6.20-win32\bin><b>mysqld.exe --install MySQL_5620 --defaults-file=c:/mysql/data/my.ini --local-service</b><br />
Service successfully installed.<br />
C:\mysql\mysql-5.6.20-win32\bin>cd..\..\</blockquote>
</div>
<div>
<br /></div>
<div>
<br /></div>
<h3>
7. Start the service.</h3>
<div>
<br /></div>
<div>
<blockquote class="tr_bq">
<br />
C:\mysql><b>sc start MySQL_5620</b><br />
SERVICE_NAME: MySQL_5620<br />
TYPE : 10 WIN32_OWN_PROCESS<br />
STATE : 2 START_PENDING<br />
(NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)<br />
WIN32_EXIT_CODE : 0 (0x0)<br />
SERVICE_EXIT_CODE : 0 (0x0)<br />
CHECKPOINT : 0x1<br />
WAIT_HINT : 0x1f40<br />
PID : 4352<br />
FLAGS :</blockquote>
<div>
<br /></div>
<div>
If you have larger innodb settings, give it a few seconds/minutes to start, then check it.</div>
<div>
<br /></div>
</div>
<div>
<br /></div>
<div>
<blockquote>
C:\mysql><b>mysql-5.6.20-win32\bin\mysql.exe --no-defaults -h127.0.0.1 -uroot -P3306</b><br />
Welcome to the MySQL monitor. Commands end with ; or \g.<br />
Your MySQL connection id is 1<br />
Server version: 5.6.20 MySQL Community Server (GPL)<br />
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.<br />
Oracle is a registered trademark of Oracle Corporation and/or its<br />
affiliates. Other names may be trademarks of their respective<br />
owners.<br />
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.<br />
mysql> \s<br />
--------------<br />
mysql-5.6.20-win32\bin\mysql.exe Ver 14.14 Distrib 5.6.20, for Win32 (x86)<br />
Connection id: 1<br />
Current database:<br />
Current user: root@localhost<br />
SSL: Not in use<br />
Using delimiter: ;<br />
Server version: 5.6.20 MySQL Community Server (GPL)<br />
Protocol version: 10<br />
Connection: 127.0.0.1 via TCP/IP<br />
Server characterset: latin1<br />
Db characterset: latin1<br />
Client characterset: cp850<br />
Conn. characterset: cp850<br />
TCP port: 3306<br />
Uptime: 2 min 37 sec<br />
Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.031<br />
--------------</blockquote>
</div>
<div>
<br /></div>
<div>
The way I personally stay sane with >50 versions on my machine is to follow simple rules:</div>
<div>
<br /></div>
<div>
<ul>
<li>not any MySQL products 'installed' on my work machine.</li>
<li>not any global my.cnf and my.ini lurking around.</li>
<li>always use --no-defaults when running mysql programs, in case I broke the last rule.</li>
<li>not any mysql program in the path.</li>
</ul>
</div>
<div>
<br /></div>
<div>
Note, the <a href="http://dev.mysql.com/doc/refman/5.6/en/windows-install-archive.html">manual pages</a> cover everything here, and even more verbosely.</div>
<div>
<a href="http://dev.mysql.com/doc/refman/5.6/en/windows-start-service.html">Starting as a service</a></div>
<div>
<br /></div>
<div>
The reason I prefer this method is that upgrades are generally easier. You simply download the next 5.6.21, extract it, delete the existing MySQL_5620 service, create a new MySQL_5621 service using same command, and run mysql_upgrade once it's started.</div>
<div>
<br /></div>
<div>
<br /></div>
sbesterhttp://www.blogger.com/profile/01382626013503952498noreply@blogger.com5tag:blogger.com,1999:blog-8132102221957780385.post-72272501676105692332013-01-04T07:43:00.001+02:002013-01-04T07:43:44.873+02:00protocol speed comparison on windows<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
Comparison of Protocols</h3>
<br />
A while ago I wrote a small random function tester to fuzz test native<a href="http://dev.mysql.com/doc/refman/5.5/en/geometry-property-functions.html" target="_blank"> functions</a> such as linestring, polygon, astext, etc. The queries it sends are generally small (100 bytes or less) and a totally CPU bound workload, since no data/tables are accessed.<br />
<br />
As this was pretty much an open-ended test, simply pumping random data into the functions, I had planned to let it run for a few days and see if any problems arose.<br />
<br />
I benchmarked all the ways to <a href="http://dev.mysql.com/doc/refman/5.5/en/connecting.html" target="_blank">connect</a> on windows; TCP/IP, <a href="http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_enable-named-pipe" target="_blank">named pipe</a>, <a href="http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_shared-memory" target="_blank">shared memory</a>, and <a href="http://dev.mysql.com/doc/refman/5.5/en/libmysqld.html" target="_blank">embedded</a> server.<br />
<br />
5.5.29 client and server are used here in all tests. Roughly 345 million queries are sent via 8 threads. Below is a graph to show the QPS of each protocol for the run:<br />
<br />
Averages:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSzHzmKi4ub_y_JSk4Oraq6LKY96aFD8JS3zSmqMPL5j0xmduVxrYqztw-9EClYiz-TVDpYuKWLeMQ9854p5wlcJ5PTQ3AwMX4lbpI4O4PQEPUY2EZk4576k-pYfqs3DlRlPd8rk8ArBA/s1600/protocol_speed_comparison.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="372" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSzHzmKi4ub_y_JSk4Oraq6LKY96aFD8JS3zSmqMPL5j0xmduVxrYqztw-9EClYiz-TVDpYuKWLeMQ9854p5wlcJ5PTQ3AwMX4lbpI4O4PQEPUY2EZk4576k-pYfqs3DlRlPd8rk8ArBA/s640/protocol_speed_comparison.jpg" width="640" /></a></div>
<br />
<br />
The QPS taken every minute is<a href="https://docs.google.com/spreadsheet/ccc?key=0AiHR2wmOxixvdHlhMFVWbzBTNWh1NWtzQ2puRDB4RlE" target="_blank"> here</a>.<br />
As we see, libmysqld can do nearly 8x the throughput of tcp/ip in this test. This matters, when you're running hundreds of billions of small fast queries.<br />
<h3 style="text-align: left;">
Conclusion </h3>
Embedded speed is clearly superior. For this reason, I always try writing QA/testing code in C/C++ if I think it might need to be run billions of times. But you lose ability to monitor the embedded server status from a mysql client, which is annoying. However, distributing an embedded server application is far easier as it's self-contained. :)<br />
<br />
Shared memory doesn't care to enforce <a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_wait_timeout" target="_blank">wait_timeout</a>, so you may want an idle-connection-killer script<br />
looping in the background. Also, shared memory connection isn't very stable at high concurrency. This stability issue is already being dealt with so that's a plus.<br />
<br />
Happy testing!</div>
Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-8132102221957780385.post-55088599057271612912012-09-10T18:55:00.000+02:002012-09-10T18:55:37.722+02:00How to obtain all executing queries from a core file<div dir="ltr" style="text-align: left;" trbidi="on">When investigating core files from crashes, one can quite easily<a href="http://www.mysqlperformanceblog.com/2012/09/09/obtain-last-executed-statement-from-optimized-core-dump/" target="_blank"> figure out which query</a> crashed, as we've seen.<br />
<br />
Sometimes you want to just list all the currently executing statements, this is useful for diagnosing hangs or corruptions.<br />
<br />
At least GDB 7 supports python <a href="http://sourceware.org/gdb/current/onlinedocs/gdb/Extending-GDB.html#Extending-GDB" target="_blank">macros</a>, which can help us a lot here. I use a core file from 5.5.27, also a non-debug build but not "stripped". So it's a standard build made with <a href="http://gcc.gnu.org/onlinedocs/gcc/Debugging-Options.html" target="_blank">-g</a> allowing us to reference symbols.<br />
<br />
I wrote a simplistic macro to iterate through mysqld's global "threads" variable.<br />
This is what my ~./.gdbinit looks like:<br />
<br />
<br />
<blockquote>set history filename gdb_history.txt<br />
set history size 32000<br />
set history save on<br />
set pagination off<br />
set logging overwrite on<br />
set logging on<br />
set print elements 1024<br />
set print pretty on<br />
set print object on<br />
define print_thds<br />
set $thd = ($arg0)->first<br />
while ( $thd != 0 && $thd->next != 0)<br />
<br />
set $thread_id=$thd->thread_id<br />
set $cmd=$thd->command<br />
set $proc_info=$thd->proc_info ? $thd->proc_info : ""<br />
set $query = $thd->query_string->string<br />
printf "thread id: %lu\n",$thread_id<span class="Apple-tab-span" style="white-space: pre;"> </span>print $cmd<br />
print $proc_info<br />
print $query.str<br />
printf "\n\n" <br />
set $thd=$thd->next<br />
end<br />
end<br />
document print_thds<br />
print_thds <list>: Dumps the query_strings in a list of THD objects<br />
end</list></blockquote><div><br />
</div><div>Loading a corefile and executing the macro, see now how it works :</div><div><br />
</div><div><div>[sbester@fc14 mysql-advanced-5.5.27-linux2.6-x86_64]$ gdb ./bin/mysqld ./core.5376</div><div>GNU gdb (GDB) Fedora (7.2-52.fc14)</div><div>Copyright (C) 2010 Free Software Foundation, Inc.</div><div>License GPLv3+: GNU GPL version 3 or later <http: gnu.org="gnu.org" gpl.html="gpl.html" licenses="licenses"></http:></div><div>This is free software: you are free to change and redistribute it.</div><div>There is NO WARRANTY, to the extent permitted by law. Type "show copying"</div><div>and "show warranty" for details.</div><div>This GDB was configured as "x86_64-redhat-linux-gnu".</div><div>For bug reporting instructions, please see:</div><div><http: bugs="bugs" gdb="gdb" software="software" www.gnu.org="www.gnu.org">...</http:></div><div>SIGINT is used by the debugger.</div><div>Are you sure you want to change it? (y or n) [answered Y; input not from terminal]</div><div>Reading symbols from /home/sbester/mysql/5.5/mysql-advanced-5.5.27-linux2.6-x86_64/bin/mysqld...done.</div><div>[New Thread 5417]</div><div><cut></cut></div><div>[New Thread 5376]</div><div>Reading symbols from /lib/modules/2.6.35.14-106.fc14.x86_64/vdso/vdso.so...Reading symbols from /usr/lib/debug/lib/modules/2.6.35.14-106.fc14.x86_64/vdso/vdso.so.debug...done.</div><div>done.</div><div><cut></cut></div></div><div><div>(gdb) print_thds threads</div><div>thread id: 22</div><div>$1 = COM_QUERY</div><div>$2 = 0x9ae27c "init"</div><div>$3 = 0x7fffc801dc80 "/* 50605 */ delete ignore from `t1` order by ( `b`<> null )"</div><div><br />
</div><div><br />
</div><div>thread id: 21</div><div>$4 = COM_QUERY</div><div>$5 = 0xa0aeda "update"</div><div>$6 = 0x1880850 "/* 50605 explain extended */ insert ignore into `t1` set `a`=`a`"</div><div><br />
</div><div><br />
</div><div>thread id: 20</div><div>$7 = COM_SLEEP</div><div>$8 = ""</div><div>$9 = 0x0</div><div><br />
</div><div><br />
</div><div>thread id: 19</div><div>$10 = COM_SLEEP</div><div>$11 = ""</div><div>$12 = 0x0</div><div><br />
</div><div><br />
</div><div>thread id: 18</div><div>$13 = COM_QUERY</div><div>$14 = 0xa10e30 "updating"</div><div>$15 = 0x7fffd800e540 "/* 50605 */ delete ignore from `t1` where ( ( `b` ) ) >= ( ( `a` is null ) )"</div><div><br />
</div><div><br />
</div><div>thread id: 17</div><div>$16 = COM_SLEEP</div><div>$17 = ""</div><div>$18 = 0x0</div><div><br />
</div><div><br />
</div><div>thread id: 16</div><div>$19 = COM_QUERY</div><div>$20 = 0x9ae27c "init"</div><div>$21 = 0x7fffd0017930 "/* 50605 explain extended */ update ignore`t3` set `a` = 4.7 order by ( ( `a` is null ) sounds like ( `a` ) ) desc limit 8"</div><div><br />
</div><div><br />
</div><div>thread id: 15</div><div>$22 = COM_QUERY</div><div>$23 = 0x9be1e5 "Updating"</div><div>$24 = 0x7fffc8005230 "/* 50605 explain partitions */ update `t3` set `a` = `a` where ( `a` ) limit 1"</div><div><br />
</div><div><br />
</div><div>thread id: 14</div><div>$25 = COM_SLEEP</div><div>$26 = ""</div><div>$27 = 0x0</div><div><br />
</div><div><br />
</div><div>thread id: 13</div><div>$28 = COM_QUERY</div><div>$29 = 0x9ae27c "init"</div><div>$30 = 0x7fffc0045e10 "/* 50605 explain extended */ update `t1` set `b` = 'e' order by ( `a` not like `b` ) desc limit 7"</div><div><br />
</div><div><br />
</div><div>thread id: 12</div><div>$31 = COM_QUERY</div><div>$32 = 0x9be1e5 "Updating"</div><div>$33 = 0x1850f80 "/* 50605 */ update ignore`t3` set `b` = `b` where ( ( `b` and `a` ) ) = ( ( ( `a` is null )<> ( `a` ) ) ) limit 7"</div><div><br />
</div><div><br />
</div><div>thread id: 11</div><div>$34 = COM_QUERY</div><div>$35 = 0x9ae27c "init"</div><div>$36 = 0x7ffff0005760 "/* 50605 explain /*!50605 format= json*/ */ update ignore`t2` set `a` = -1.4 where ( ( `a`<= `a` ) ) < ( CONCAT( ( `b` ) , ( `a` xor null ) , ( `a` is null ) ) ) order by ( null ) desc limit 5"</div><div><br />
</div><div><br />
</div><div>thread id: 9</div><div>$37 = COM_QUERY</div><div>$38 = 0x9ae27c "init"</div><div>$39 = 0x7fffd8005140 "/* 50605 explain */ delete from `t2` order by ( ( `b` ) = ( `b` sounds like -4.6 ) ) desc"</div><div><br />
</div><div><br />
</div><div>thread id: 8</div><div>$40 = COM_QUERY</div><div>$41 = 0x9be1e5 "Updating"</div><div>$42 = 0x7fffdc004d40 "/* 50605 explain /*!50605 format= json*/ */ update `t1` set `a` = 'deg' where ( `a`<`a` )"</div><div><br />
</div><div><br />
</div><div>thread id: 7</div><div>$43 = COM_QUERY</div><div>$44 = 0x9ae27c "init"</div><div>$45 = 0x7fffd00048e0 "/* 50605 explain extended */ update `t2` set `a` = 8 order by ( `b`>`b` ) asc limit 4"</div><div><br />
</div><div><br />
</div><div>thread id: 5</div><div>$46 = COM_QUERY</div><div>$47 = 0xa10e30 "updating"</div><div>$48 = 0x7fffcc034000 "/* 50605 explain /*!50605 format=traditional */ */ delete ignore from `t3`"</div><div><br />
</div><div><br />
</div><div>thread id: 4</div><div>$49 = COM_QUERY</div><div>$50 = 0x9ae27c "init"</div><div>$51 = 0x7fffc001b8d0 "/* 50605 */ delete from `t1` where ( ( ( `a` ) < ( ( `b` is null )<= ( `a`<> null ) ) ) ) & ( OCTET_LENGTH( ( `b` ) ) ) order by ( `b` ) desc limit 7"</div><div><br />
</div><div><br />
</div><div>thread id: 3</div><div>$52 = COM_SLEEP</div><div>$53 = ""</div><div>$54 = 0x0</div><div><br />
</div><div><br />
</div><div>thread id: 2</div><div>$55 = COM_QUERY</div><div>$56 = 0x9ae27c "init"</div><div>$57 = 0x7fffc0004a90 "/* 50605 explain /*!50605 format= json*/ */ update ignore`t3` set `b` = null order by ( 1.5 ) limit 8"</div><div><br />
</div><div><br />
</div><div>thread id: 1</div><div>$58 = COM_QUERY</div><div>$59 = 0xa0aeda "update"</div><div>$60 = 0x7fffcc004a90 "/* 50605 explain extended */ insert ignore into `t1` set `a`=6"</div><div><br />
</div></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-66335964761012259882010-09-29T20:32:00.000+02:002010-09-29T20:32:03.656+02:00A Quick Review of Stack Traces<div style="text-align: justify;">I'll try to pass on some basic knowledge about those confusing <a href="http://en.wikipedia.org/wiki/Stack_trace">stack traces</a> we sometimes see in the <a href="http://dev.mysql.com/doc/refman/5.1/en/error-log.html">mysql error logs</a>. What can you tell from them, what are they useful for, and how to validate them?</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><span class="Apple-style-span" style="font-size: x-large;"><span class="Apple-style-span" style="color: #073763;">Debugging Crashes</span></span></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">We tried to improve postmortem debugging of crashes + <a href="http://dev.mysql.com/doc/refman/5.1/en/using-stack-trace.html">stack traces</a> in the error log:</div><div style="text-align: justify;">o) old versions of mysqld only printed <a href="http://bugs.mysql.com/bug.php?id=31891">numerical numbers</a> instead of function names (if you're lucky!)</div><div style="text-align: justify;">o) some <a href="http://bugs.mysql.com/bug.php?id=26893">platforms</a>/architectures printed <a href="http://bugs.mysql.com/bug.php?id=47391">no stack trace</a> what-so-ever!</div><div style="text-align: justify;">o) <a href="http://bugs.mysql.com/bug.php?id=35987">faulty</a> <a href="http://bugs.mysql.com/bug.php?id=52850">implementations</a> of the crash <a href="http://bugs.mysql.com/bug.php?id=35661">error reporting</a> and running <a href="http://bugs.mysql.com/bug.php?id=51817">query</a>.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">So, the result is that on modern supported platforms and recent versions of mysqld, you <i>should</i> get a useful stack trace. A simple example from 5.5.5 on Windows:</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"></div><pre>mysqld.exe!mysql_admin_table()[sql_table.cc:5150]
mysqld.exe!mysql_optimize_table()[sql_table.cc:5226]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3107]
mysqld.exe!mysql_parse()[sql_parse.cc:5911]
mysqld.exe!dispatch_command()[sql_parse.cc:1138]
mysqld.exe!do_command()[sql_parse.cc:807]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:1196]
mysqld.exe!handle_one_connection()[sql_connect.cc:1136]
mysqld.exe!pthread_start()[my_winthread.c:62]
mysqld.exe!_callthreadstartex()[threadex.c:348]
mysqld.exe!_threadstartex()[threadex.c:331]
kernel32.dll!BaseThreadStart()
</pre><br />
It should be obvious that <a href="http://bugs.mysql.com/bug.php?id=54783">OPTIMIZE TABLE</a> crashed here, since the function name is clear.<br />
<div style="text-align: justify;">The reason I always use windows stack traces in bug reports is because they are readable. Most GDB and linux error log stack traces are not readable by the human brain at a glance, and therefore not memorable. One reason is <a href="http://bugs.mysql.com/bug.php?id=26467">excessive wrapping</a>, another reason is offsets and arguments to the functions are irrelevant and not useful for search engine indexes or the average Joe trying to find a bug report matching a stack trace.</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">In the old days, a crash only this printed (only 32-bit), and you had to <a href="http://dev.mysql.com/doc/refman/5.0/en/resolve-stack-dump.html">resolve it yourself</a>:</div><div style="text-align: justify;"><br />
</div><pre>0x81a0705
0x8407eef
0x8408a8f
0x8408b75
0x8408dd2
0x8409af7
0x83e7fc9
0x83eefc3
0x826014c
0x826035c
0x825ec43
0x82106fd
0x81b86da
0x81bd78b
0x81bdd16
0x81bfae2
</pre><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">Which, on a side note is not always possible to do properly with optimized binaries so you get a <span class="Apple-style-span" style="color: red;">partially bogus looking</span> stack and you might be tempted to wrongly suspect faulty hardware, foul play, or bad binaries:<br />
<br />
<pre>0x81a0705 handle_segfault + 805
<span class="Apple-style-span" style="color: red;">0x8407eef my_write + 671
0x8408a8f init_key_cache + 1279
0x8408b75 init_key_cache + 1509
0x8408dd2 init_key_cache + 2114</span>
0x8409af7 flush_key_blocks + 55
0x83e7fc9 flush_blocks + 41
0x83eefc3 mi_repair_by_sort + 451
0x826014c ha_myisam::repair(THD*, st_mi_check_param&, bool) + 1900
0x826035c ha_myisam::enable_indexes(unsigned int) + 364
0x825ec43 ha_myisam::end_bulk_insert() + 99
0x82106fd mysql_insert(THD*, TABLE_LIST*, List<item>&, List<list><item> >&, List<item>&, List<item>&, enum_duplicates, bool) + 5037
0x81b86da mysql_execute_command(THD*) + 9610
0x81bd78b mysql_parse(THD*, char const*, unsigned int, char const**) + 379
0x81bdd16 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1238
0x81bfae2 handle_one_connection + 2578
</item></item></item></list></item></pre><br />
Using a technique that involves disassembling the mysqld binary into ASM and piecing together C/C++ source code/comments, it's quite possible to find those inlined functions, expanded macros, or functions that have no name in the symbols file. A nice topic for another posting. So, after manually inspecting the binary + numeric offsets, I could get a <span class="Apple-style-span" style="color: #38761d;">proper stack</span> trace:<br />
<br />
<pre>0x81a0705 handle_segfault
<span class="Apple-style-span" style="color: #38761d;">0x8407eef unlink_block
0x8408a8f free_block
0x8408b75 flush_cached_blocks
0x8408dd2 flush_key_blocks_int</span>
0x8409af7 flush_key_blocks
0x83e7fc9 flush_blocks
0x83eefc3 mi_repair_by_sort
0x826014c ha_myisam::repair
0x826035c ha_myisam::enable_indexes
0x825ec43 ha_myisam::end_bulk_insert
0x82106fd mysql_insert
0x81b86da mysql_execute_command
0x81bd78b mysql_parse
0x81bdd16 dispatch_command
0x81bfae2 handle_one_connection
</pre><br />
This is simply a bulk insert performing a 'repair by sort'. It crashed in the keycache when flushing blocks, perhaps due to a memory corruption or overrun of something. I remember fulltext indexes or large table having this problem..<br />
<br />
<span class="Apple-style-span" style="font-size: x-large;"><span class="Apple-style-span" style="color: #073763;">Some Identifying Elements of a Stack Trace</span></span><br />
If your 5.1.<recent> or 5.5.<recent> server ever crashes, please keep the stack trace as it can help identify exactly what the problem is, and you can <a href="http://www.google.co.za/search?hl=en&newwindow=1&safe=off&q=site:bugs.mysql.com+crash+%22Prepared_statement::execute()%22&aq=f&aqi=&aql=&oq=&gs_rfai=">search google</a> for clues. </recent></recent><br />
<ul><li><b>prepared statements</b></li>
</ul><div>Easily distinguishable by looking for the functions similar to this:<br />
<pre>.....
mysqld-debug.exe!Prepared_statement::execute()[sql_prepare.cc:3050]
mysqld-debug.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2393]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2935]
.....
</pre></div><ul><li><b>stored routines and their call depth</b></li>
</ul><div>Seeing sp_* is a sign of some stored routine activity You can even see how many SP calls there are nested, whether they called triggers. Takes some intuition to follow.<br />
<br />
</div><div><pre>.......
06 mysqld_debug!sp_instr_stmt::exec_core
07 mysqld_debug!sp_lex_keeper::reset_lex_and_exec_core
08 mysqld_debug!sp_instr_stmt::execute
09 mysqld_debug!sp_head::execute
0a mysqld_debug!sp_head::execute_procedure
0b mysqld_debug!mysql_execute_command
.......
</pre></div><ul><li><b>storage engine code (archive, innodb, myisam, merge)</b></li>
</ul><div>InnoDB mostly asserts, and this is clearly identified in the error log before a stack trace.</div><div>"070223 21:47:40 InnoDB: Assertion failure in thread 1655241648 in file row0mysql.c line 3228"</div><div><br />
</div><div>Archive crashes can be easily seen by ha_archive functions:</div><div><pre>.....
mysqld.exe!ha_archive::free_share()[ha_archive.cc:411]
mysqld.exe!ha_archive::open()[ha_archive.cc:498]
mysqld.exe!handler::ha_open()[handler.cc:2059]
.....
</pre></div><ul><li><b>query cache</b></li>
</ul><div>Any thing involving the Query_Cache class functions:<br />
<pre>.....
#5 0x000000000065f390 in Query_cache::insert_table ()
#6 0x000000000065f63a in Query_cache::register_tables_from_list ()
#7 0x000000000065f6a5 in Query_cache::register_all_tables ()
#8 0x000000000065fc0a in Query_cache::store_query ()
#9 0x000000000058cd38 in mysql_execute_command ()
.....
</pre></div><ul><li><b>mysql functions (string, math, datetime, comparative)</b></li>
</ul><div>Look out for specific Item_func* methods...</div><div><pre>.....
mysqld.exe!Arg_comparator::compare_binary_string()[item_cmpfunc.cc:1158]
mysqld.exe!Item_func_eq::val_int()[item_cmpfunc.cc:1692]
mysqld.exe!Item::val_bool()[item.cc:184]
mysqld.exe!Item_cond_and::val_int()[item_cmpfunc.cc:4222]
.....
</pre></div><ul><li><b>first calling function in the application</b></li>
</ul><div>You can nearly always expect a valid stack trace to have these functions at the bottom:<br />
<pre style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">.....
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2256]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5974]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:872]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]</pre></div><ul><li><b>last calling functions before the crash</b></li>
</ul><div>Crashing is usually handled by mysqld's segfault handler. It depends on the OS and environment. Most of the time you'll have:</div><br />
<pre>mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2048]
mysqld-debug.exe!raise()[winsig.c:597]
mysqld-debug.exe!abort()[abort.c:78]
......
</pre><br />
or<br />
<br />
<pre>0 mysqld 0x00579d3e my_print_stacktrace + 44
1 mysqld 0x00100f78 handle_segfault + 836
......
</pre><br />
<br />
<span class="Apple-style-span" style="color: #073763;"><span class="Apple-style-span" style="font-size: x-large;">Debugging Hangs</span></span><br />
<br />
When mysqld hangs or flatlines the CPU and logging in or killing queries doesn't help, you'd better either create a corefile, break into the process with a debugger, or just use the <a href="http://poormansprofiler.org/">PMP</a>.<br />
<br />
You'll probably need stack trace of all the threads to determine what is going on:<br />
<br />
<ol><li>A single thread is looping endlessly in some loop</li>
<li>Multiple threads are hitting a hot mutex, or totally deadlocked, waiting for each other.</li>
</ol><br />
<br />
If the deadlock is in innodb you often get useful innodb outputs in the error log<br />
for each waiting thread. But it can be extremely helpful to get full stack traces too.<br />
<br />
<pre>--Thread 3003468656 has waited at fsp/fsp0fsp.c line 2204 for 556.00 seconds the
semaphore:
X-lock on RW-latch at 0xb759ceb0 created in file fil/fil0fil.c line 1061</pre><pre></pre><pre>That's all for now.!</pre></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-8132102221957780385.post-7543809017119941312010-06-16T09:53:00.002+02:002010-06-16T10:31:45.909+02:00quick update from down under...<div style="text-align: justify;">I'm pleased to report that so far the Soccer World Cup has been pulled off rather successfully, with only minor incidents reported. I have however noticed local news showing some 'feel-good' stories that are obviously written to give a <a href="http://www.news24.com/SouthAfrica/News/Dbn-cops-find-missing-ring-20100615">false impression</a> to the international media of the real situation here. Let's hope the unions and Eskom workers don't mess things up by holding a gun to the Country's head with <a href="http://www.sport24.co.za/Soccer/WorldCup/NationalNews/4-stadiums-hit-by-strike-20100615">protesting/striking</a> too much in the international media's light.. Hold thumbs.. </div><div style="text-align: justify;"><br /></div><div style="text-align: justify;">BTW, I hate the <a href="http://en.wikipedia.org/wiki/Vuvuzela">vuvuzela</a> :)</div><div style="text-align: left;"><br /></div><div style="text-align: left;"><br /></div><div style="text-align: justify;">I thought I should share two important MySQL bugs with you today. In case you ever used YaSSL to establish <a href="http://dev.mysql.com/doc/refman/5.1/en/secure-connections.html">SSL connections</a>, you were at risk of hitting random crashes due to <a href="http://bugs.mysql.com/bug.php?id=34236">bug #34236</a> (Various possibly related SSL crashes) if more than one concurrent connection was ever made. The reason is the YaSSL code was built without mutexes as if for single threaded apps...</div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;">Next bug I think is widespread enough to mention is optimizer/query plan related. Examine the testcase on <a href="http://bugs.mysql.com/bug.php?id=48537">bug #48537</a> (difference of index selection between rpm binary and .tar.gz, windows vs linux..) And read the changeset notes:</div><pre><br /><br /> On Intel x86 machines index selection by the MySQL query<br /> optimizer could sometimes depend on the compiler version and<br /> optimization flags used to build the server binary.<br /><br /> The problem was a result of a known issue with floating point<br /> calculations on x86: since internal FPU precision (80 bit)<br /> differs from precision used by programs (32-bit float or 64-bit<br /> double), the result of calculating a complex expression may<br /> depend on how FPU registers are allocated by the compiler and<br /> whether intermediate values are spilled from FPU to memory. In<br /> this particular case compiler versions and optimization flags<br /> had an effect on cost calculation when choosing the best index<br /> in best_access_path().<br /><br /> A possible solution to this problem which has already been<br /> implemented in mysql-trunk is to limit FPU internal precision<br /> to 64 bits. So the fix is a backport of the relevant code to<br /> 5.1 from mysql-trunk.<br /></pre><br /><br /><div style="text-align: justify;">Now I'll get back to enjoying the public holiday and bugs reporting ;-)</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-31350335429372256482010-05-14T08:14:00.003+02:002010-05-14T08:53:22.183+02:00vmstat/iostat replacement for windows ?I dislike the old <a href="http://technet.microsoft.com/en-us/library/cc787707(WS.10).aspx">perfmon</a> interface and it's unreadable graphs and logs. For a long time I've been searching for a basic <a href="http://linux.die.net/man/8/vmstat">vmstat</a> and/or <a href="http://linux.die.net/man/1/iostat">iostat</a> windows port, and one that doesn't rely on that nonsensical cygwin. If anybody knows of one, please leave a comment.<div><br /></div><div>Here's a proof on concept I cooked up in 20 minutes using the <a href="http://msdn.microsoft.com/en-us/library/aa373214(VS.85).aspx">PDH</a> (performance data helper) functions. In a nutshell, it queries the PDH counters directly and I'm be free to display</div><div>the data however I like.</div><div><br /></div><div>Here's what I got so far:</div><br /><pre><br />proc_q_len pagefile interrupt/s cswitch/s %cpu_user %cpu_sys %cpu_idle %disk_busy %disk_read %disk_write<br /> 10 2308780032 2016 2555 64 26 9 100 0 100<br /> 3 2308911104 2863 3669 62 13 23 100 1 100<br /> 0 2309206016 1857 3057 79 10 9 2 0 2<br /> 0 2310217728 2579 3664 64 9 26 100 0 100<br /> 1 2309140480 2195 2985 71 3 25 100 0 100<br /> 2 2309140480 2241 3042 76 4 18 100 0 100<br /></pre><br /><div><br /></div><div>Not perfect, and I'm still trying to devise a proper layout for the stuff I want to display.</div><div>The columns shown correspond to the counters:</div><div><div><ul><li>\System\Processor Queue Length</li><li>\Process(_Total)\Page File Bytes</li><li>\Processor(_Total)\Interrupts/sec</li><li>\System\Context Switches/sec</li><li>\Processor(_Total)\% User Time</li><li>\Processor(_Total)\% Privileged Time</li><li>\Processor(_Total)\% Idle Time</li><li>\PhysicalDisk(_Total)\% Disk Time</li><li>\PhysicalDisk(_Total)\% Disk Read Time</li><li>\PhysicalDisk(_Total)\% Disk Write Time</li></ul></div></div><div><br /></div><div>The general flow of code is like this (there are <a href="http://msdn.microsoft.com/en-us/library/aa371886(v=VS.85).aspx">samples</a> online):</div><div><br /></div><div>1. <a href="http://msdn.microsoft.com/en-us/library/aa372652(VS.85).aspx">PdhOpenQuery</a></div><div>2. <a href="http://msdn.microsoft.com/en-us/library/aa372204(VS.85).aspx">PdhAddCounter</a> for each counter</div><div>3. for each X seconds:</div><div>3.1 <a href="http://msdn.microsoft.com/en-us/library/aa372563(VS.85).aspx">PdhCollectQueryData</a> </div><div>3.2 <a href="http://msdn.microsoft.com/en-us/library/aa372637(VS.85).aspx">PdhGetFormattedCounterValue</a> for each counter</div><div>3.3 print the result for each counter</div><div>4. <a href="http://msdn.microsoft.com/en-us/library/aa372558(VS.85).aspx">PdhCloseQuery</a></div><div><br /><div><br /></div><div><br /></div></div>Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-8132102221957780385.post-36915126496379947352010-05-03T16:40:00.003+02:002010-05-03T17:01:19.973+02:00Beware of RBR and tables without indexes<span class="Apple-style-span" style="font-size:small;"></span><span><span>I always knew RBR and unindexed tables didn't play along very well, but never realized just how much you can distress a slave can in some cases.<br />Consider this statement (yeah yeah, i know :)</span></span><div><span><span><br /></span></span></div><div><span><span></span></span></div><blockquote><div><span><span>mysql> delete from t1 order by rand(); </span></span></div><div><span><span>Query OK, 78130 rows affected (2.61 sec)</span></span></div></blockquote><div><span><span></span></span></div><div><span><span><br /></span></span></div><div><span><span>t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:</span></span></div><div><span><span><br /></span></span></div><blockquote><div><span><span>---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423</span></span></div><div><span><span><br /></span></span></div><div><span><span>Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s</span></span></div></blockquote><div><span><span></span></span></div><div><span><span><br />Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to complete. It would be nice if there was a way to prevent this situation from happening.</span></span></div>Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-8132102221957780385.post-19039035025126513902010-02-12T16:22:00.005+02:002010-02-22T15:02:27.341+02:00debugging mysqld corefile on AIX<div>I recently had the pleasure of logging into an AIX 5.3 machine for the first time ever, to debug a corefile.</div><div><br /></div><div>Firstly, having the mysqld binary and core is not enough, unless you have an identical machine on which to study the corefile. Library mismatches can be a problem.. IBM was kind enough to provide the <a href="http://publib.boulder.ibm.com/infocenter/aix/v6r1/index.jsp?topic=/com.ibm.aix.cmds/doc/aixcmds5/snapcore.htm">snapcore</a> utility to solve this easily.</div><div><br /></div><div>Snapcore will gather all the libraries and create a single archive contain libs, binary, core.</div><div><br /></div><div>So we now have a file called something like: snapcore_555060.pax</div><div>On our dev box, extract the pax archive: </div><div><br /></div><div><blockquote>gunzip snapcore_555060.pax.Z</blockquote><blockquote>pax -r -f snapcore_555060.pax</blockquote></div><div><br /></div><div>On your dev AIX box, make sure you have <a href="http://docs.sun.com/app/docs/doc/819-5257">DBX</a> installed!!</div><div><br /></div><div><div></div><blockquote><div>bash-3.00# lslpp -l | grep bos.adt.debug</div><div>bos.adt.debug 5.3.8.0 COMMITTED Base Application Development</div></blockquote><div></div><div><br /></div><div>Now, we are ready to debug a core. But we have to instruct DBX to read the libraries</div><div>that we got from the pax archive, instead of the default libraries on this system.</div><div><br /></div><div>dbx -F -p /usr=/home/sbester/core/usr ./mysqld ./core</div><div><br /></div><div><br /></div><div>And since this isn't a <a href="http://blogs.sun.com/dbx/entry/gdb_vs_dbx_commands_mapping">DBX tutorial</a>, I'll stop here, but you'll use normal DBX commands to print a stack trace, move up/down frames, print variables, list the source code.</div><div><br /></div><div><br /></div><div><br /></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-40464112632958064602009-12-01T20:20:00.002+02:002009-12-01T20:21:53.157+02:00a little challengeHow do you make mysqld write a DELETE to the binlog just by entering a SELECT statement ?<br />No triggers and no stored routines/functions are involved.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-11131447633365093812009-08-27T07:53:00.004+02:002009-08-27T08:06:34.754+02:00partitioning + auto_increment is buggy!Folks, I just want to warn you how buggy <a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning.html">partitioned</a> tables with negative values are. <span style="font-weight: bold;">Never</span> use negative values for InnoDB <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html">auto_increment</a> columns!!!!<br /><br /><ul><li><a href="http://bugs.mysql.com/bug.php?id=46902">Assertion failed: next_insert_id >= auto_inc_interval_for_cur_row.minimum()</a></li><li><a href="http://bugs.mysql.com/bug.php?id=46909">partitioned innodb tables end up with duplicate primary key values!</a></li><li><a href="http://bugs.mysql.com/bug.php?id=43988">AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31</a></li><li><a href="http://bugs.mysql.com/bug.php?id=45823">Assertion failure in file row/row0mysql.c line 1386</a></li></ul>There are more bugs than I listed here..Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-36105233174943353922009-06-27T13:29:00.004+02:002009-06-27T13:36:47.310+02:005.1 doesn't solve all merge table hell from 5.0.This week I've had to revisit <a href="http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html">merge tables</a> once again due to customers experiencing problems. Although 5.1 merge table implementation is a <span style="font-weight: bold;">huge</span> improvement over 5.0, there still remains some critical bugs.<br /><br />My list is still growing:<br /><br /><a href="http://bugs.mysql.com/bug.php?id=45800">bug #45800</a>: crash when replacing into a merge table and there is a duplicate<br /><a href="http://bugs.mysql.com/bug.php?id=45781">bug #45781</a>: infinite hang/crash in "opening tables" after handler tries to open merge table<br /><a href="http://bugs.mysql.com/bug.php?id=45796">bug #45796</a>: invalid memory reads and writes when altering merge and base tables<br /><a href="http://bugs.mysql.com/bug.php?id=45777">bug #45777</a>: check table doesn't show all problems for merge table compliance in 5.1<br /><br />Not to mention a few <a href="http://bugs.mysql.com/bug.php?id=45799">feature requests</a>, and even <a href="http://dev.mysql.com/doc/refman/5.1/en/merge-table-problems.html">documentation clarification</a> for some manual sections.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-89731370445038972482009-06-19T07:38:00.002+02:002009-06-19T07:47:43.007+02:00some useful additions to query generatorI've been on vacation this week, and decided to fine-tune some old QA code. Opened the manual to see the syntax for a select statement, and afterwards added to my random select generator the following:<br /><br /><ul><li>all index hints (force, use, ignore, for join, for order by, for group by)</li><li>lock in share mode, for update</li><li>key_block_size for individual indexes</li><li>hash, btree, rtree for individual indexes</li><li>unique, fulltext, spatial for indexes</li></ul>Especially important is the 'lock in share mode' addition. The reason is InnoDB<br />has many serious bugs with this locking mode (insert ... select, and others) in read committed mode.<br /><br />So, I don't need multitable delete or update to reproduce those bugs, since I can just do a simple select locking in share mode. For example, the following bugs previously went without proper testcase until I discovered this:<br /><br /><a href="http://bugs.mysql.com/bug.php?id=39320">assert btr/btr0pcur.c line 217 -innodb_locks_unsafe_for_binlog or read committed</a><br /><a href="http://bugs.mysql.com/bug.php?id=45357">5.1.35 crashes with Failing assertion: index->type & DICT_CLUSTERED</a><br /><a href="http://bugs.mysql.com/bug.php?id=41756">Strange error messages about locks from InnoDB</a><br /><blockquote></blockquote>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-89703790916398290472009-06-02T07:47:00.002+02:002009-06-02T07:53:16.678+02:00some bug statsSo I did some checking at the number of bugs I've reported since start of 2005. Seems I'm at the top of my game here!<br /><br /><ul><li>P1 Server bugs: 180 (next runner up PeterG with 127)</li><li>P1 + P2 Server bugs: 321 (next runner up PeterG with 311)</li></ul>Interesting to note that most of my bug filing happened after 2006, but I started working at MySQL in 2005, so that's why I've used that start date.<br /><br />The runner ups mostly report bugs in alpha versions, falcon, maria, and beta versions of mysql.<br />Nearly all of my bugs are in the current GA versions since that is what most of our customers use.<br /><br />On occasion I go off on a tangent and try break the subquery optimizations in 6.0, but this<br />is only a small percentage of the total.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-70724626092430971322009-05-11T10:35:00.003+02:002009-05-11T10:43:28.421+02:00been a shocking week for 5.1.35so last week i started tweaking some of my old 'rainbow' scripts, and found 1 bug for each day of the week. there are a few more in the pipeline still...<br /><br /><a href="http://bugs.mysql.com/bug.php?id=44774">Bug #44774</a> (load_file function produces valgrind warnings)<br /><a href="http://bugs.mysql.com/bug.php?id=44768">Bug #44768</a> (SIGFPE crash when selecting rand from a view containing null)<br /><a href="http://bugs.mysql.com/bug.php?id=44767">Bug #44767</a> (invalid memory reads in password() and old_password() functions)<br /><a href="http://bugs.mysql.com/bug.php?id=44766">Bug #44766</a> (valgrind error when using convert() in a subquery)<br /><a href="http://bugs.mysql.com/bug.php?id=44684">Bug #44684</a> (valgrind reports invalid reads in Item_func_spatial_collection::val_str)<br /><a href="http://bugs.mysql.com/bug.php?id=44672">Bug #44672</a> (Assertion failed: thd->transaction.xid_state.xid.is_null())<br /><a href="http://bugs.mysql.com/bug.php?id=44664">Bug #44664</a> (valgrind warning for COMMIT_AND_CHAIN and ROLLBACK_AND_CHAIN)<br /><a href="http://bugs.mysql.com/bug.php?id=44633">Bug #44633</a> (Automatic search depth and nested join's results in server crash - v2)<br /><br />so, it seems i am still useful for bug finding, even with old tools i created pre-5.1 GAUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-41323235861958607672009-01-10T20:07:00.002+02:002009-01-10T20:10:54.090+02:00MXit !Today I started writing a PC client for MXit, because the existing ones suck. Since the company uses a proprietary variation of the jabber protocol to reduce bandwidth usage, I have to decode the protocol myself.. Will post details of it later...<br /><br />Get MXit <a href="http://www.mxit.co.za/web/downloadmxit.htm">http://www.mxit.co.za/web/downloadmxit.htm</a>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-8132102221957780385.post-47875218907011187002009-01-05T14:01:00.005+02:002009-02-21T10:14:20.714+02:00Kilimanjaro preparationsSo I have started training to get fit enough to gracefully reach the top of <a href="http://en.wikipedia.org/wiki/Kilimanjaro">Kilimanjaro</a> in 2009.<br />Information on the <a href="http://en.wikipedia.org/wiki/Mount_Kilimanjaro_climbing_routes">various routes</a> here.<br /><br />Here I will keep a log of certain walks I do, and their timings.<br />Note that time_down usually includes time spent at the summit.<br /><pre><br />+------------+------------+------------+-----------+------------+------------------------------------------------------+<br />| date | start_time | time_there | time_back | time_total | venue |<br />+------------+------------+------------+-----------+------------+------------------------------------------------------+<br />| 2008-12-27 | 16:37:00 | 00:47:00 | 00:32:00 | 01:19:00 | Lions Head |<br />| 2008-12-28 | 05:47:00 | 00:48:00 | 00:35:00 | 01:23:00 | Lions Head |<br />| 2008-12-30 | 12:57:00 | 00:42:00 | 00:30:00 | 01:12:00 | Lions Head |<br />| 2008-12-31 | 12:24:00 | 00:39:00 | 00:30:00 | 01:09:00 | Lions Head |<br />| 2009-01-02 | 12:09:00 | 00:37:00 | 00:28:00 | 01:05:00 | Lions Head |<br />| 2009-01-05 | 12:45:00 | 00:39:00 | 00:22:00 | 01:01:00 | Lions Head |<br />| 2009-01-07 | 17:19:00 | 00:37:00 | 00:21:00 | 00:58:00 | Lions Head |<br />| 2009-01-14 | 12:10:00 | 01:01:00 | 01:29:00 | 02:30:00 | Home->La Med (via KloofNek)->Joburg (via Sea Point) |<br />| 2009-01-24 | 06:00:00 | 01:05:00 | 00:47:00 | 01:52:00 | Platteklip |<br />| 2009-01-29 | 13:30:00 | 00:38:00 | 00:27:00 | 01:05:00 | Lions Head |<br />| 2009-02-21 | 07:26:00 | 00:58:00 | 01:31:00 | 02:29:00 | Home->La Med (via KloofNek)->Joburg) (via Sea Point) |<br />+------------+------------+------------+-----------+------------+------------------------------------------------------+<br />11 rows in set (0.03 sec)<br /></pre><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsPlxO56IqGBvpvmPFCPswAaNsl03EHMu1hBQjC8p2DTQ-GhDBL_JF5zoSrg6gwQA3H2J1OIOELYIlygoZOMwZcqgB9tDx9TBxw2I65OIYgDSfuIuua4-uaC-wtUMbdd6Do8r7SCFjJ8c/s1600-h/100_1222_resample_040.jpg"><img style="cursor:pointer; cursor:hand;width: 200px; height: 150px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsPlxO56IqGBvpvmPFCPswAaNsl03EHMu1hBQjC8p2DTQ-GhDBL_JF5zoSrg6gwQA3H2J1OIOELYIlygoZOMwZcqgB9tDx9TBxw2I65OIYgDSfuIuua4-uaC-wtUMbdd6Do8r7SCFjJ8c/s200/100_1222_resample_040.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5287340118376143570" /></a><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnuh5n8E8BstMgnauQYds-dERowVJvvJHqk5lgY_sjw6er3RKBBiUKBQLZQbrGq3e_-h3MYnyXnVEkCTKb6Gdp48B3LiQKXxZbFvYi8Xf0ekh7oNNiiXrXxLCyzc2f4fh5_6ue06E75cI/s1600-h/100_1223_resample_041.jpg"><img style="cursor:pointer; cursor:hand;width: 200px; height: 150px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnuh5n8E8BstMgnauQYds-dERowVJvvJHqk5lgY_sjw6er3RKBBiUKBQLZQbrGq3e_-h3MYnyXnVEkCTKb6Gdp48B3LiQKXxZbFvYi8Xf0ekh7oNNiiXrXxLCyzc2f4fh5_6ue06E75cI/s200/100_1223_resample_041.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5287340482674372594" /></a><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSm1j4zesY-zfqJg4infwYrWWO_u-8RrZlsITLVouz39V9RpwBu-8mwg9Z4HWTVfKgP1MgNj1SeKhxDwpGxhLylhSy5GypflO7YnWAvVsmWtV98_f64HhzB7L1_j68R8x9neZFNiK0cJM/s1600-h/100_1203_resample_021.jpg"><img style="cursor:pointer; cursor:hand;width: 200px; height: 150px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSm1j4zesY-zfqJg4infwYrWWO_u-8RrZlsITLVouz39V9RpwBu-8mwg9Z4HWTVfKgP1MgNj1SeKhxDwpGxhLylhSy5GypflO7YnWAvVsmWtV98_f64HhzB7L1_j68R8x9neZFNiK0cJM/s200/100_1203_resample_021.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5287341169971822514" /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-26667416589242372232008-09-20T06:34:00.005+02:002010-04-29T15:39:01.975+02:00innodb index page formatToday I had to decode an innodb index page, so I documented the entire process here:<br /><br /><pre>E:\mysql-enterprise-gpl-5.0.66a-winx64\bin>mysqld-nt --console --skip-grant-tables --skip-name-resolve<br />InnoDB: The first specified data file .\ibdata1 did not exist:<br />InnoDB: a new database to be created!<br />080919 14:29:00 InnoDB: Setting file .\ibdata1 size to 10 MB<br />InnoDB: Database physically writes the file full: wait...<br />080919 14:29:00 InnoDB: Log file .\ib_logfile0 did not exist: new to be created<br />InnoDB: Setting log file .\ib_logfile0 size to 5 MB<br />InnoDB: Database physically writes the file full: wait...<br />080919 14:29:01 InnoDB: Log file .\ib_logfile1 did not exist: new to be created<br />InnoDB: Setting log file .\ib_logfile1 size to 5 MB<br />InnoDB: Database physically writes the file full: wait...<br />InnoDB: Doublewrite buffer not found: creating new<br />InnoDB: Doublewrite buffer created<br />InnoDB: Creating foreign key constraint system tables<br />InnoDB: Foreign key constraint system tables created<br />080919 14:29:01 InnoDB: Started; log sequence number 0 0<br />080919 14:29:01 [Note] mysqld-nt: ready for connections.<br />Version: '5.0.66a-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server (GPL)<br /><br /><br />create table t1(a varchar(20) primary key, b varchar(20), c varchar(20),key(b),key(c,b))engine=innodb;<br />insert into t1(a,b,c) values ('aaaa','bbbbb','cccccc');<br />insert into t1(a,b,c) values ('aaaaaaa',null,'ccc');<br />insert into t1(a,b,c) values ('a','b',null);<br />insert into t1(a,b,c) values ('aaaaaaaaaa','bbb','c');<br /><br /><br /><br />080919 14:37:59 [Note] mysqld-nt: Normal shutdown<br /><br />080919 14:37:59 InnoDB: Starting shutdown...<br />080919 14:38:14 InnoDB: Shutdown completed; log sequence number 0 48536<br />080919 14:38:14 [Note] mysqld-nt: Shutdown complete<br /><br /><br /><br />we have secondary indexes on this table<br /><br />this is key(c,b) (directly from ibdata1):<br /><br /><br />000d0000h: 35 56 71 04 00 00 00 34 FF FF FF FF FF FF FF FF ; 5Vq....4ÿÿÿÿÿÿÿÿ<br />000d0010h: 00 00 00 00 00 00 BD 92 45 BF 00 00 00 00 00 00 ; ......½’E¿......<br />000d0020h: 00 00 00 00 00 00 00 02 00 C3 80 06 00 00 00 00 ; .........À.....<br />000d0030h: 00 B5 00 05 00 00 00 04 00 00 00 00 00 00 03 05 ; .µ..............<br />000d0040h: 00 00 00 00 00 00 00 00 00 11 00 00 00 00 00 00 ; ................<br />000d0050h: 00 02 15 F2 00 00 00 00 00 00 00 02 15 32 01 00 ; ...ò.........2..<br />000d0060h: 02 00 47 69 6E 66 69 6D 75 6D 00 05 00 0B 00 00 ; ..Ginfimum......<br />000d0070h: 73 75 70 72 65 6D 75 6D 04 05 06 00 00 00 10 FF ; supremum.......ÿ<br />000d0080h: EF 63 63 63 63 63 63 62 62 62 62 62 61 61 61 61 ; ïccccccbbbbbaaaa<br />000d0090h: 07 03 02 00 00 18 FF E9 63 63 63 61 61 61 61 61 ; ......ÿécccaaaaa<br />000d00a0h: 61 61 01 01 01 00 00 20 00 0B 62 61 0A 03 01 00 ; aa..... ..ba....<br />000d00b0h: 00 00 28 FF E3 63 62 62 62 61 61 61 61 61 61 61 ; ..(ÿãcbbbaaaaaaa<br />000d00c0h: 61 61 61 00 00 00 00 00 00 00 00 00 00 00 00 00 ; aaa.............<br /><br /><br /><br /><br />Let's reformat this page into the correct fields as seen by InnoDB:<br /><br />0000: 35567104 -> FIL_PAGE_SPACE_OR_CHKSUM<br />0004: 00000034 -> FIL_PAGE_OFFSET <br />0008: FFFFFFFF -> FIL_PAGE_PREV <br />0012: FFFFFFFF -> FIL_PAGE_NEXT <br />0016: 000000000000BD92 -> FIL_PAGE_LSN<br />0024: 45BF -> FIL_PAGE_TYPE (#define FIL_PAGE_INDEX 17855)<br />0026: 0000000000000000 -> FIL_PAGE_FILE_FLUSH_LSN<br />0034: 00000000 -> FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID<br /><br />0038: 0002 -> PAGE_N_DIR_SLOTS<br />0040: 00C3 -> PAGE_HEAP_TOP (195 ...)<br />0042: 8006 -> PAGE_N_HEAP (6 records in heap (remove 15th bit)<br />0044: 0000 -> PAGE_FREE<br />0046: 0000 -> PAGE_GARBAGE<br />0048: 00B5 -> PAGE_LAST_INSERT<br />0050: 0005 -> PAGE_DIRECTION (PAGE_NO_DIRECTION)<br />0052: 0000 -> PAGE_N_DIRECTION<br />0054: 0004 -> PAGE_N_RECS<br />0056: 0000000000000305 -> PAGE_MAX_TRX_ID (773)<br />0064: 0000 -> PAGE_LEVEL<br />0066: 0000000000000011 -> PAGE_INDEX_ID ( Page may be an index page where index id is 0 277385)<br />0074: 000000000000000215F2 -> PAGE_BTR_SEG_LEAF<br />0084: 00000000000000021532 -> PAGE_BTR_SEG_TOP<br /><br />infimum:<br />0094: 01 -> info_bits=0, n_owned=1 (always 1 for the infimum)<br />0095: 00 -> heap number<br />0096: 02 -> status bits<br />0097: 0047 -> next record (71 bytes)<br />0099: 696E66696D756D00 -> "infimum"<br /><br />supremum:<br />0107: 05000b -> extra bytes<br />0110: 0000 -> next record, zero since supremum is always last<br />0112: 73757072656D756D -> "supremum"<br /><br />index row1:<br />0120: 040506 -> field offsets, starting with the last field.<br />0123: 00000010 -> extra bytes<br />0127: FFEF -> offset to next record (17 bytes back (offset 112))<br />0129: 636363636363 -> 'cccccc' (keypart1)<br />0135: 6262626262 -> 'bbbbb' (keypart2)<br />0140: 61616161 -> 'aaaa' (primary key appended)<br /><br />index row2:<br />0144: 0703 -> field lengths, starting with the last field (excluding null!).<br />0146: 02000018 -> extra bytes<br />0150: FFE9 -> offset to next record (23 bytes back (offset 129))<br />0152: 636363 -> 'ccc' (keypart1)<br />0155: 61616161616161 -> 'aaaaaaa' (primary key appended)<br /><br />index row3:<br />0162: 0101 -> field lengths, starting with the last field (excluding null!).<br />0164: 01000020 -> extra bytes<br />0168: 000B -> offset to next record (11 bytes (offset 181))<br />0170: 62 -> 'b' (keypart2)<br />0171: 61 -> 'a' (primary key appended)<br /><br />index row4:<br />0172: 0A0301 -> field lengths<br />0175: 00000028 -> extra bytes<br />0179: FFE3 -> offset to next record (29 bytes back (offset 152!))<br />0181: 63 -> 'c' (keypart1)<br />0182: 626262 -> 'bbb' (keypart2)<br />0185: 61616161616161616161 -> 'aaaaaaaaaa' (primary key appended)<br /><br /></pre><br /><br />explanation of "extra bytes" still to be doneUnknownnoreply@blogger.com3tag:blogger.com,1999:blog-8132102221957780385.post-56402434909387095362008-08-21T21:25:00.005+02:002008-08-22T09:22:07.831+02:00how to debug a mysqld core file from an rpm installYou have a typical rpm installation of mysql, and the process is crashing. Here are the basic steps needed to find out more info about a crash:<br /><br /><ul><li>Configure the OS to be able to create corefiles. (<a href="http://kbase.redhat.com/faq/FAQ_52_2890.shtm">Redhat details</a>), (<a href="http://developers.sun.com/solaris/articles/manage_core_dump.html">Solaris details</a>)<br /></li><li>Tell mysqld to create a corefile by adding the following options to my.cnf:</li></ul><blockquote><br />[mysqld_safe]<br />core-file-size=unlimited<br /><br />[mysqld]<br />core-file</blockquote>Usually the corefile will be created in the datadir with a name like core.2921 where 2921 was the pid of the running process. The location is configurable on most OS's.<br /><br />You'll need the following to study the core file:<br /><br /><ul><li>exact mysqld binary that created the core file</li><li>the core file</li><li>the glibc version of the original system (rpm -qa|grep -i glibc)</li><li>the debuginfo package corresponding to the original mysql rpms.</li></ul>Let's go through a hypothetical example next.<br />On my server I have installed <a href="http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-server-community-5.0.67-0.rhel5.x86_64.rpm/from/pick">MySQL-server-community-5.0.67-0.rhel5.x86_64.rpm</a> and it's been crashing. I have a corefile called core.12345 which I've moved to a test system because I don't want to impact production while playing around with it.<br /><br />On the production server we have glibc 2.3.4-2.36 installed. So to setup the test box to study the core I do this:<br /><br />From dev.mysql.com download the <a href="http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-community-debuginfo-5.0.67-0.rhel5.x86_64.rpm/from/pick">MySQL-community-debuginfo-5.0.67-0.rhel5.x86_64.rpm</a><br />Download the glibc-2.3.4-2.36.x86_64.rpm from somewhere (in case test system isn't running same version). Next we extract the RPMS and launch gdb and tell it the path to load libraries and symbol files:<br /><blockquote></blockquote><blockquote><br />rpm2cpio MySQL-community-debuginfo-5.0.67-0.rhel5.x86_64.rpm | cpio -idvu<br />rpm2cpio glibc-2.3.4-2.36.x86_64.rpm | cpio -idvu<br /><br />gdb ./mysqld --core ./core.12345<br />set solib-absolute-prefix .<br />file ./usr/lib/debug/usr/sbin/mysqld.debug<br /></blockquote><br />From here you should <a href="http://sourceware.org/gdb/current/onlinedocs/gdb_toc.html">get reasonable output from GDB</a>, such as "thread apply all bt" and "bt full" and continue to examine the corefile...Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-23274992250513451092008-07-04T16:28:00.005+02:002008-08-21T22:20:42.868+02:00gypsy is resumedIn search of better qa tools, i have resumed work on my gypsy. Within hours i verified a bug i thought was not possible any time soon...<br /><br /><a href="http://bugs.mysql.com/bug.php?id=37671"><span style="font-weight: bold;">crash on prepared statement + cursor + geometry + too many open files !</span></a><br /><br />The code is also in launchpad (bzr branch lp:gypsy) if anybody cares.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-30631350251494407202008-04-16T20:50:00.003+02:002008-04-16T20:53:41.304+02:00innodb plugin and new features!check it out:<br /><a href="http://www.innodb.com/wp/2008/04/15/innodb-plugin-announced/">the announcement</a><br /><br /><a href="http://www.innodb.com/doc/innodb_plugin-1.0/">plugin documentation</a><br /><br /><blockquote><br />o) Fast Index Creation in the InnoDB Storage Engine<br />o) InnoDB Data Compression<br />o) InnoDB File Format Management<br />o) InnoDB INFORMATION_SCHEMA tables<br /></blockquote><br /><br />yay!! gonna test the compression immediately :)Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-85467330604879759542007-10-23T20:40:00.000+02:002007-10-23T20:49:39.525+02:00putting it all togetherLast post was about Rainbow testing. So that's old news, and I lost count of the number of bugs it found, and have been fixed. Probably 30+ crashes. <br /><br />Now, I have to put together all these odds & ends of code I wrote disparately over the last 18 months into a single collection to enable complete end-to-end testing.<br /><br />I have now got roughly the following:<br /><br />o) random table maker<br />o) query maker based on any tables, (using predefined rules)<br />o) data generator for any tables<br />o) complete charset collection for each valid character<br />o) database of mysql functions, data types<br />o) multi-threaded testing environment for any queries<br />o) query results comparer for any queries<br />o) rainbow, which will provide help in making good coverage of functions, etc.<br /><br />What's needed still? Well, I would like 56 hours in a day ... More posts later when v0.001 of the integrated is semi-functional :)Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-12515401063886883922007-09-19T17:05:00.000+02:002007-09-19T17:14:18.124+02:00mysql crash log analyzer ?So with over 1000 crashes in my logs from just one night of simple queries, I have a painful time to find new crashes in the 90MB file. So I'm taking a detour on the rainbow query generator for 2 days. Will be writing an error log analyzer instead :)<br /><pre><br />step 1: upload the mysqld and the mysqld.sym file for the version you're working with.<br />step 2: import the binary and symbols into a mysql table<br />step 3: upload the error log<br />step 4: parse the error log into seperate crashes<br />step 5: find the stack traces for each crash, and resolve them using the symbols<br />step 6: determine if this crash is matching any existing crashes.<br /></pre><br />With thousands of crashes on various builds of mysqld, the above system can be useful to me. Also, I'd import all the crashes from reports on bugs.mysql.com for searching purposes. This toy will allow me to identify whether a crash is new, or it's been seen before.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-1668892353957860472007-09-19T13:45:00.000+02:002007-09-19T13:56:29.163+02:00some results of rainbowok folks. here's some results:<br /><br /><pre><br /><blockquote><br />mysql> select last_errno,count(*) from <br />queryqueue group by last_errno;<br />+------------+----------+<br />| last_errno | count(*) |<br />+------------+----------+<br />| 0 | 1600796 |<br />| 1048 | 1971 |<br />| 1053 | 1 |<br />| 1139 | 35 |<br />| 1267 | 19722 |<br />| 1270 | 4243 |<br />| 1271 | 8944 |<br />| 1416 | 2284 |<br />| 1580 | 23225 |<br />| 2003 | 28 |<br />| 2013 | 1606 |<br />+------------+----------+<br />11 rows in set (0.00 sec)<br /></blockquote><br /></pre><br />error 2013 means lost connection to server (read: server crashed).<br />so there are many bugs found already. 1606 crashes out of 1.6 million<br />executed queries, is great.<br /><br />check my rss feed for the exact bugs ...Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-66405221442096104192007-09-12T11:01:00.000+02:002007-09-12T11:03:22.695+02:00good news!my girlfriend's grandfather wants a blog!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8132102221957780385.post-36487652380477226512007-09-07T09:03:00.000+02:002007-09-12T20:57:16.390+02:00sql rainbow tablesI thought of a brilliant QA plan. Create sql rainbow tables! Just trust me, this system of QA will be <span style="font-weight:bold;">very powerful</span> in bug finding. Especially important will be the comparison of old/new versions of mysql. Writing a prototype now, on my day off.<br /><br />Just during prototype development, I've discovered a handful of bugs. I'm testing all functions that are documented in the manual. This is alot that I've written up:<br /><pre><br />mysql> select count(*),category from func group by category;<br />+----------+--------------+<br />| count(*) | category |<br />+----------+--------------+<br />| 6 | arithmetic |<br />| 7 | bit |<br />| 60 | casting |<br />| 47 | comparison |<br />| 52 | datetime |<br />| 18 | encryption |<br />| 127 | geometry |<br />| 17 | information |<br />| 7 | logical |<br />| 32 | mathematical |<br />| 9 | misc |<br />| 47 | string |<br />| 2 | xml |<br />+----------+--------------+<br />13 rows in set (0.00 sec)<br /></pre><br /><br />Let me explain this rainbow tables concept with a simple example.<br />Suppose we have a function, like "GREATEST()".<br /><br />I run a query like this:<br /><br />SELECT GREATEST(col1,col2,col3) FROM table0;<br /><br />The columns col1,col2,col3 will form any combination of column type. So we have int, date, string, blob, spatial! Given a combination of all datatypes and all function parameters, it leaves us with a good few million queries. Of course, a few million queries is not so much for a machine to execute quickly..Unknownnoreply@blogger.com0