tag:blogger.com,1999:blog-62995717926023482592024-02-18T05:17:43.769-05:00MySQL Proxy and other thoughtsFor posts about Scala, Lift and Go (Golang), please go to <a href="http://blog.fmpwizard.com">http://blog.fmpwizard.com</a>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-6299571792602348259.post-48935467753353047882009-09-02T20:20:00.003-04:002009-09-02T20:28:50.985-04:00How GDB helped me fix a Drizzle Bug<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgNMtvoOHzzjiofGMK9fEbz7rIDJfNY2OL6o8EHNx_G0ft2umUj3CYI1krZhMs-4zYrS3MebhzVkG9Ry5oeNTvnqzXVNIslAzEyCk4ztlkjdD8uBA7L8YaX8Nv6Zjwmuty0vlzybMfmvuW/s1600-h/gdb_blog_post.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 210px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgNMtvoOHzzjiofGMK9fEbz7rIDJfNY2OL6o8EHNx_G0ft2umUj3CYI1krZhMs-4zYrS3MebhzVkG9Ry5oeNTvnqzXVNIslAzEyCk4ztlkjdD8uBA7L8YaX8Nv6Zjwmuty0vlzybMfmvuW/s320/gdb_blog_post.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5376940502511714466" /></a><br /><div>The other day I found a nice surprise on my inbox. <a href="http://jpipes.com/">Jay Pipes</a> asked me if I'd like to try fixing a <a href="https://bugs.launchpad.net/drizzle/+bug/377826">small bug on Drizzle</a>. It looked pretty simple, and the bug report included a big part of the fix. I accepted without a doubt.</div><div><br /></div><div>I decided to first change trans_prealloc_size from uint32_t to uint64_t. That was done on <i><a href="http://bazaar.launchpad.net/~diego-fmpwizard/drizzle/bug-fixes/revision/1125#drizzled/session.h">drizzled/session.h</a></i>.</div><div>Then, I went to <i><a href="http://bazaar.launchpad.net/~diego-fmpwizard/drizzle/bug-fixes/revision/1125#drizzled/set_var.cc">drizzle/set_var.cc</a></i> and changed sys_trans_prealloc_size from sys_var_session_uint32_t to sys_var_session_uint64_t (and removed the two extra parameters).</div><div><br /></div><div>At first, that looked like it was everything I needed to do. I compiled drizzle, executed the queries that were included on the bug report, and that almost worked!</div><div><br /></div><div><code>set session transaction_prealloc_size=1024*1024*1024*4;</code></div><div><code></code><code>set session transaction_prealloc_size=1024*1024*1024*5;</code></div><div><code></code>and </div><div><code>set session transaction_prealloc_size=1024*1024*1024*6; </code> were all being truncated to "<b>4294966272</b>"</div><div><br /></div><div>The pending problem was that I no longer received a warning telling me that those values had been truncated.</div><div><br /></div><div>I looked again at the code, used <a href="http://www.gnu.org/software/gdb/documentation/">gdb</a> to <a href="http://drizzle.org/wiki/Debugging_Drizzle_with_GDB">step through the code</a>, but I just couldn't tell what the problem was.</div><div>I was then forced to do what I do when I'm stuck on a computer problem, I had to take a break :).</div><div><br /></div><div>... And that was a great idea, because while away from the computer, I thought of looking at sys_var_session_uint64_t::update, and compare it to sys_var_session_uint32_t::update.</div><div><br /></div><div>It turned out that sys_var_session_uint64_t::update was missing a call to throw_bounds_warning(), no wonder I wasn't getting the truncation warnings.</div><div><br /></div><div>It was time to enable the <a href="http://bazaar.launchpad.net/~diego-fmpwizard/drizzle/bug-fixes/revision/1125#tests/suite/big/t/variables-big.test">test suite that targeted this bug</a>, run this particular test, commit, push .... and I suddenly thought that I should also run the regular tests, you know, make test. And I was glad I did. There were about 7 test cases that failed.</div><div><br /></div><div>The firs thought was that these changes I just made uncovered some mysterious bugs on Drizzle. But after looking closely at the test cases, I noticed that there was something else going on.</div><div><br /></div><div>If I executed this query: <code>set group_concat_max_len = 4;</code>, the session variable <b>group_concat_max_len</b> ended up with a value of <b>4294967295<span class="Apple-style-span" style="font-weight: normal; ">.</span></b></div><div><br /></div><div>It was time for some serious GDB'ing. There are a few articles <a href="http://drizzle.org/wiki/Debugging_Drizzle_with_GDB">here</a>, <a href="http://posulliv.com/?p=113">here</a> and <a href="http://torum.net/2009/03/drizzle-gdb-osx/">here</a> that explain how to use gdb and Drizzle, but I start it a little different.</div><div><br /></div><div><b><span class="Apple-style-span" style="font-size:large;">Starting GDB and Drizzle</span></b></div><div><br /></div><div>On the terminal, I run:</div><div><pre>[wizard]$ gdb --args /Applications/drizzle/sbin/drizzled --port=3306 --basedir=/Applications/drizzle --datadir=/Applications/drizzle/var</pre> </div><div><br /></div>After a few seconds, I am at the (gdb) prompt. This is the time to set breakpoints, which would help me find this little issue.<div>I tried doing this:</div><div><pre>(gdb)break drizzled/set_var.cc:744</pre></div><div>but gdb told me: "<b>No source file named drizzled/set_var.cc</b>", I had this same problem trying to use gdb on the MySQL Proxy, and after trying different things, I found the correct way to specify the file name:</div><div><pre>(gdb)break set_var.cc:744</pre><br />*<b>Note:</b> when debugging plugins, you specify the filenames like this:</div><div><pre>(gdb)break plugin/logging_gearman/logging_gearman.cc:150</pre><br />(include the plugin folder)<br /><br /></div><div>That one breakpoint was all I needed, I then started drizzle by typing:</div><div><pre>(gdb)run</pre></div><div>After pressing enter, you see many lines, but the last few are (similar to)</div><div><div><pre><br />. done<br />InnoDB: The InnoDB memory heap is disabled<br />InnoDB: Mutexes and rw_locks use GCC atomic builtins.<br />090827 23:45:16 InnoDB: highest supported file format is Barracuda.<br />090827 23:45:16 InnoDB Plugin 1.0.3 started; log sequence number 46439<br />Listening on :::3306<br />Listening on 0.0.0.0:3306<br />/Applications/mysql/enterprise/drizzle/sbin/drizzled: ready for connections.<br />Version: '2009.08.1124' Source distribution (bug-fixes)<br /></pre></div></div><div><br /></div><div>Drizzle was ready for my tests. I went to a new terminal and executed</div><div><br /><pre>set group_concat_max_len = 4;</pre></div><div>The drizzle prompt hanged there (waiting on the breakpoint I set earlier)</div><div><br /></div><div>This is the code near the breakpoint</div><div><br /></div><pre>bool sys_var_session_uint64_t::update(Session *session, set_var *var)<br />{<br />uint64_t tmp= var->save_result.uint64_t_value;<br /><br />if (tmp > max_system_variables.*offset) // <-- this was line 744 </pre><pre> throw_bounds_warning(session, true, true, getName(), (int64_t) tmp);</pre><pre> tmp= max_system_variables.*offset;</pre><pre>if (option_limits)</pre><pre> tmp= fix_unsigned(session, tmp, option_limits);</pre><pre>if (var->type == OPT_GLOBAL)<br />{<br />/* Lock is needed to make things safe on 32 bit systems */<br />pthread_mutex_lock(&LOCK_global_system_variables);<br />global_system_variables.*offset= (uint64_t) tmp;<br />pthread_mutex_unlock(&LOCK_global_system_variables);<br />}<br />else<br />session->variables.*offset= (uint64_t) tmp;<br />return 0;<br />}<br /></pre><br /><div><br />Back on the GDB terminal, I saw:<br /><pre><br />Breakpoint 1, sys_var_session_uint64_t::update (this=0x4dec60, session=0x1044e00, var=0x10470c8) at set_var.cc:744<br />744 if (tmp > max_system_variables.*offset)<br /></pre><br />It was time to look at the value of tmp, so I typed<br /><pre>(gdb) print tmp<br />$4 = 4</pre></div><br /><div>So far, so good, I wanted to see 4 as the result, let's step through the code, and see when tmp gets a much higher value.<br /><pre>(gdb) step<br />746 tmp= max_system_variables.*offset;</pre></div><br /><div>Check the value of tmp</div><br /><pre><br />(gdb) print tmp<br />$5 = 4<br />(gdb) step<br />748 if (option_limits)<br />(gdb) print tmp<br />$6 = 4294967295<br /></pre><br /><div>There it was, whatever happened between <b>line 746</b> and <b>748</b> is causing the problem. By now you may have already spotted the issue, but I just didn't see it. I stared at that piece of code for about a minute, and then I realized what the problem was.</div><div>The <b>if statement</b> on line 744 was a <b>single line statement</b>, before I added the call to <b>throw_bounds_warning()</b>. Because I am so used to having brackets, even for single line if statement, I forgot to add a pair of {}.</div><div><br /></div><div><b>*Note:</b> For completeness, once you are done stepping through the code, you can type </div><pre>(gdb)cont</pre><div>and the program will continue to run as normal. On my case, I could then run on the drizzle terminal:</div><div><br /></div><div><br /><pre>drizzle> show variables like "group_concat_max_len";<br />+----------------------+------------+<br />| Variable_name | Value |<br />+----------------------+------------+<br />| group_concat_max_len | 4294967295 |<br />+----------------------+------------+<br />1 row in set (0.01 sec)<br /></pre><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-size:large;"><b>Conclusion</b></span>.</div><div>If you don't have another pair of eyes to look over your code, you could ask gdb for some help :)</div><div><br /></div>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com1tag:blogger.com,1999:blog-6299571792602348259.post-37180726974552367202009-08-27T22:10:00.002-04:002009-08-27T22:14:33.357-04:00Drizzle query monitoring<div><b>Disclaimer:</b> <blockquote>This blog post is about things I did on my own free time, not endorsed by my employer.</blockquote></div><div><br /></div>A little over a month ago, <a href="http://ronaldbradford.com/blog/drizzle-query-logging-2009-07-21/">Ronald posted</a> a blog about the different query logging plug-ins that are available for <a href="http://drizzle.org/wiki/Main_Page">Drizzle</a>. This was pretty exciting news, especially when I saw the details that were included in the logs.<br /><br />Meanwhile, a few weeks ago, I started looking at the <a href="http://coalface.mcslp.com/2008/11/15/feeding-query-analyzer-from-dtrace/">REST API</a> that comes with the<a href="http://www.mysql.com/products/enterprise/monitor.html"> MySQL Enterprise Monitor</a>.<br /><br />The result is that we can now see most of the information returned by the plug-in, on the Dashboard.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi58reO_Ei9iaCLoXD4mx7yhwkdLELA6AGzR6YLK0PvpBwGhDvvErDnE_r1DsVIenFCT5oMsbjAM9ERmvmoKrGaBlpETYAp5YPskfyuumDNlwj4GiBNvTckcyoKyVoWlBtWP53xQM1KfUJJ/s1600-h/drizzle-quan.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 658px; height: 202px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi58reO_Ei9iaCLoXD4mx7yhwkdLELA6AGzR6YLK0PvpBwGhDvvErDnE_r1DsVIenFCT5oMsbjAM9ERmvmoKrGaBlpETYAp5YPskfyuumDNlwj4GiBNvTckcyoKyVoWlBtWP53xQM1KfUJJ/s400/drizzle-quan.png" alt="" id="BLOGGER_PHOTO_ID_5372234713266253586" border="0" /></a><br /><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How?</span></span><br />A <a href="http://darren.oldag.net/">colleague</a> at work, wrote a little Perl script that interacts with the REST API, and I took his work as the foundation for my <a href="http://forge.mysql.com/tools/tool.php?id=226">agent.pl</a> script.<br /><br />The next problem was to find a way to call this script as soon as there was a new entry on the log. After a little Google search, I went ahead and decided to ask my friend <a href="http://www.whisperthis.com/">Adriano Di Paulo</a> (who among other things, introduced me to MySQL).<div>A few minutes later, he showed me a working example of the <a href="http://search.cpan.org/~mgrabnar/File-Tail-0.99.3/Tail.pm">Tail</a> Perl module.<div>That was exactly what I needed, as soon as there is a new entry, I call the function <span style="font-weight: bold;">assemble_queries()</span> and I pass the new log entry as the parameter.<div><br /><pre><br />sub tail_log {<br /> my $file=File::Tail->new(name=>$log_file, maxinterval=>1, reset_tail=>0);<br /> while( defined (my $line=$file->read ) ) {<br /> print "\n" . $line . "\n" if $DEBUG > 3;<br /> assemble_queries( $line );<br /> }<br />}<br /><br /></pre><br /><br />The <span style="font-weight: bold;">assemble_queries()</span> function is mostly based on what MC <a href="http://coalface.mcslp.com/2008/11/15/feeding-query-analyzer-from-dtrace/">blogged</a> about some time ago. On his blog post, he shows how to collect query related data using Dtrace and Perl.<br /><br />Then, every <span style="font-style: italic;">n</span> number of queries, I use <span style="font-weight: bold;">send_json_data()</span> to send the query information to the Dashboard, delete the sent data and it is ready to process more queries.<br /><br /></div><div>Now that I'm writing this, I realized that if <span style="font-weight: bold;">send_json_data()</span> fails, the information related to the queries are lost :|. (Note to self, fix it).<br /><br />There are other functions in there, but they are mostly for housekeeping.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I use it?</span></span><br />Very simple, get the <a href="http://forge.mysql.com/tools/tool.php?id=226">agent.pl</a> script from the <a href="http://forge.mysql.com/tools/tool.php?id=226">MySQL Forge</a> website, edit the credentials, hosts, and ports to fit your needs (Future versions would include some kind of config file).<br /><br />And then you call the script like this:<br /><pre><br />$ DEBUG=1 perl agent.pl --serveruuid="22222222-5555-5555-5555-222222222211" \<br />--serverhostuuid="ssh:{11:11:11:11:11:11:11:11:11:11:11:11:11:11:11:21}"\<br />--serverdisplayname="Main-Drizzle-web2" \<br />--log-file=/path/to/log/file<br /></pre><br />As soon as the scripts starts, it will add the drizzle server to the service manager, and once you start sending queries to drizzle, those queries will end up on the UI.<br /><br /><span style="font-weight: bold;">Next?</span><br />Next is already done :). I modified the agent.pl script to use the <a href="http://bazaar.launchpad.net/~drizzle-developers/drizzle/development/files/head:/plugin/logging_gearman/">gearman logging plugin</a>. I'll write a blog about it very soon.<br /><br />Thanks for reading and enjoy!<br /><br /><br /></div></div></div>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com2tag:blogger.com,1999:blog-6299571792602348259.post-65360998106163160402009-08-24T22:46:00.001-04:002009-08-24T22:49:47.877-04:00More Drizzle plug-insLast weekend, I finally got some time to look around <a href="https://launchpad.net/drizzle">Drizzle</a>. I had already compiled it on my laptop, but hadn't really looked at the <a href="https://code.launchpad.net/drizzle">code</a>.<div>Then, I thought that looking over some of the <a href="https://blueprints.launchpad.net/drizzle">blueprints</a> on Launchpad, would be a good way to get familiar with the code base.</div><div>After a quick search, I found <a href="https://blueprints.launchpad.net/drizzle/+spec/time-functions-as-plugin">move function/time/ functions into plugin(s)</a><br /><div><br /></div><div>This blueprint is basically to create UDF plug-ins for the different time related functions.</div><div>There was no priority assigned and it was on the <i><a href="https://blueprints.launchpad.net/drizzle/+milestone/low-hanging-fruit">low hanging fruit</a></i> milestone. Which was perfect for someone who doesn't really know how much time he could spend, and wants to get to know the code.</div><div><br /></div><div>The first step was to read a bit about the process to contribute to the Drizzle project, I went to the wiki <a href="http://drizzle.org/wiki/Contributing_Code">here</a> and read about the <a href="http://drizzle.org/wiki/Coding_Standards">coding standards</a>. </div><div><br /></div><div>I then, went ahead and saw how <strike>difficult</strike> easy the code looked like. And proceeded to <a href="https://lists.launchpad.net/drizzle-discuss/msg04888.html">email the list</a>, asking for feedback and also to tell others what I was up to. This is important, to avoid duplicating the work of others.</div><div><br /></div><div><b><span class="Apple-style-span" style="font-size:large;">Code?</span></b></div><div>This is where the fun began. I had a fresh branch, and it was time to pick the first function to make into an UDF plugin.</div><div>By luck (and you will know why luck), I picked to move <a href="http://bazaar.launchpad.net/~diego-fmpwizard/drizzle/time-plugins/revision/1123#drizzled/function/time/unix_timestamp.cc">unix_timestamp()</a> first.</div><div><br /></div><div><b><span class="Apple-style-span" style="font-size:large;">The Process</span></b></div><div>There are already some <a href="http://bazaar.launchpad.net/~drizzle-developers/drizzle/development/files/head:/plugin/">great plugins</a> on the Drizzle branch. I went ahead and duplicated the <b>md5</b> plugin (in <i>plugin/md5</i>). Renamed the folder <b>unix_timestamp</b>, also renamed the <i>md5.cc</i> to<i> unix_timestamp.cc</i> and edited the <i>plugin.ini</i> file that was on the same folder.</div><div><br /></div><div>The md5 plugin folder also has a <i>plugin.ac</i> file, but it turned out I didn't need this file, so I just removed it.</div><div><br /></div><div>It was then time to do the actual code moving. To start, I opened <i>drizzled/function/time/unix_timestamp.cc</i> and <i>drizzled/function/time/unix_timestamp.h</i> </div><div>It was pretty much copy and paste from those two files into <i>plugin/unix_timestamp/unix_timestamp.cc</i> </div><div><br /></div><div>And the rest was to replace md5 for unix_timestamp :)</div><div><br /></div><div><b>Notes:</b></div><div>When I first started, I had both, the built-in <b>unix_timestamp()</b> and the plugin version. To make sure the plugin was returning the correct values, I just temporary named the plugin function <b>unix_timestamp2()</b>. And you can do that by just changing code in two lines:</div><div><br /></div><div></div><b>Error messages</b></div><div><b><span class="Apple-style-span" style="font-weight: normal;">Whenever there is an error with your function, the error message will call the plugin function </span>func_name()<span class="Apple-style-span" style="font-weight: normal;">, the string you return there, will be shown on error messages. One way to force this error is by including either too many, or too few parameters.</span><br /></b><pre><br />const char *func_name() const<br />{<br />return "unix_timestamp2";<br />}<br /></pre><br />To tell Drizzle the name of your plugin function, you use this line:<br /><pre><br />Create_function<unix_timestampfunction> unix_timestampudf(string("unix_timestamp2"));<br /></unix_timestampfunction></pre><br /></div><div><unix_timestampfunction>Most (all?) plugins files will start with lib + <<i>name of the plugin</i>> + _plugin.la. You specify this name using this line:<br /><pre><br />drizzle_declare_plugin(unix_timestamp)<br /></pre>The rest should be pretty easy to figure out. </unix_timestampfunction></div><div><br /></div><div><b><span class="Apple-style-span" style="font-size:large;">Tip</span></b></div><div><unix_timestampfunction>Which I wish I knew before. Something that took me way too long to find out, when you add a new plugin folder, you need to run <b><i>./config/autorun.sh</i></b> and ./configure ... && make && make install. This would make sure your new plugin gets compiled., if you skip autorun.sh, your new plugin will not be compiled.</unix_timestampfunction></div><div><br /></div><div><b><span class="Apple-style-span" style=" ;font-size:large;">Final steps</span></b></div><div>Once I compiled the new plugin, and verified that it all worked well. It was time to delete the built-in function.</div><div>1) Went to <i>drizzled/Makefile.am</i> and removed <i><b>function/time/unix_timestamp.h</b></i> from there.</div><div>2) Removed the files <i>d<b>rizzled/function/time/unix_timestamp.cc</b></i> and <i><b>drizzled/function/time/unix_timestamp.h</b></i> </div><div>3) Edited <i>drizzled/item/create.cc</i> and removed <i><b>#include <drizzled/function/time/unix_timestamp.h></drizzled/function/time/unix_timestamp.h></b></i> and some other references to the unix_timestamp function.</div><div>4) <i>drizzled/sql_parse.cc</i> also had to be edited, to remove <b><i>#include <drizzled/function/time/unix_timestamp.h></drizzled/function/time/unix_timestamp.h></i></b>.</div><div>5) Added the new plugin/unix_timestamp/ folder and files to the bzr branch.</div><div>6) Run tests (and here I found a <a href="https://lists.launchpad.net/drizzle-discuss/msg04894.html">new problem</a>)</div><div><br /></div><div>I'm still working on a fix for it. I'm going with using one error message, for built-in functions, as well as plugins. I hope to be pushing those changes soon.</div><div><br /></div><div>Oh, why was I lucky to pick the unix_timestamp() function as the first one to tackle, well, I have been working on timestamp_diff for many hours, and it just does not want to work. It somehow does not see the first parameter. I'm pretty sure I'll be asking the Drizzle-discuss for help :)</div><div><br /></div><div><b><span class="Apple-style-span" style="font-size:large;">The end.</span></b></div><div><br /><pre><br />select * from information_schema.plugins where plugin_name like '%time%';<br />+-------------------------+----------------+---------------+---------------+--------------------------------+----------------+<br />| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE |<br />+-------------------------+----------------+---------------+---------------+--------------------------------+----------------+<br />| unix_timestamp_function | 1.0 | ACTIVE | Diego Medina | UDF for getting unix_timestamp | GPL |<br />+-------------------------+----------------+---------------+---------------+--------------------------------+----------------+<br />1 row in set (0 sec)<br /><br />drizzle><br /></pre>Well, not really the end, I still have plenty of functions to move into plugins.</div><div><br /></div><div>Thanks!</div>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com2tag:blogger.com,1999:blog-6299571792602348259.post-2826884165678643102009-08-10T23:50:00.002-04:002009-08-10T23:51:03.222-04:00Vote for me! ... widget for your blog.Most likely you have seen <a href="http://datacharmer.blogspot.com/2009/08/beefing-up-community-feeds.html">Giuseppe</a>'s post showing the latest feature of <a href="http://planet.mysql.com/">Planet MySQL</a>. Voting from RSS readers, was one feature I was really hoping for, since the day <a href="http://blogs.sun.com/dups/entry/voting_on_planetmysql">voting was announced</a>. As I read most blogs using Google Reader.<br /><br />Now, I don't remember if it was <a href="http://www.facebook.com/duleepa">Dups</a> who asked me, or if I asked him, but all I remember is that I ended up writing a little JavaScript widget, that you can add to your blog. This widget allows readers to vote for your blog on Planet MySQL, all from within your blog.<br /><br />Why would you want to add this JavaScript to your blog?<br />Because you want to make it very easy for your readers to vote if they like or dislike what they just read.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Requirements/Limitations.</span></span><br />Yes, there are a few (small?) things that have to be in place for this widget to work.<br />* Your readers will have to have an account on the <a href="http://www.mysql.com/">MySQL.com</a> website.<br />* But most important, your blog post has to be already on the Planet MySQL database.<br />* If you are using Feedburner, and the url on your feeds is not the same as your post's url, this does not work (which is my case :( ). But I'll look for a workaround.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Code.</span></span><br /><br />All you need to do is add these lines of code to your template:<br /><br /><pre><br /><script language="JavaScript"><!--<br />var planet = "http://planet.mysql.com/entry/vote/?apivote=1&";<br />var lk=encodeURIComponent(document.location);<br />var thumb_up="<img src=\"http://planet.mysql.com/images/thumbs_up_blue.jpg\" border=\"0\" />";<br />var thumb_down="<img src=\"http://planet.mysql.com/images/thumbs_down_blue.jpg\" border=\"0\" />";<br />document.write('Vote on the <br /><a href=\"http://planet.mysql.com/\" >Planet MySQL</a><br />');<br />document.write('<a title=\"Vote me Up on the Planet MySQL\" href=\"' + planet + 'vote=1&url=' + lk + '\">' + thumb_up + '</a>');<br />document.write('&nbsp;');<br />document.write('<a title=\"Vote me Down on the Planet MySQL\" href=\"' + planet + 'vote=-1&url=' + lk + '\">' + thumb_down + '</a>');<br />// --></script><br /></pre><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I add it to Blogger?</span></span><br /><br />1- On the left side of this blog, you will see a "<span style="font-weight: bold;">Add Voting to your blog</span>" button, click on it.<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRtoJsBQ2U-wJI1Q3nFTm4pLSGXPkk6-JzcNYhSS-WpIU0Kmglc7opixRXi-H-F6fbZB6Tq8VyOdCzcYDumURFaq9LaB6iy14u1upqhE2vXG1f9yLu2E_lKfg2xBvaTbqapsKqNQKBmiPo/s1600-h/step_1.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 275px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRtoJsBQ2U-wJI1Q3nFTm4pLSGXPkk6-JzcNYhSS-WpIU0Kmglc7opixRXi-H-F6fbZB6Tq8VyOdCzcYDumURFaq9LaB6iy14u1upqhE2vXG1f9yLu2E_lKfg2xBvaTbqapsKqNQKBmiPo/s400/step_1.png" alt="" id="BLOGGER_PHOTO_ID_5368546343909712690" border="0" /></a><br /><br />2- On the "Add page element" section, select the blog you would like to add this widget to.<br />3- Click "Add widget"<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiywjYYz_mvJQhyVn7FitZJgLVel76mjj2ZHIzdqqNxYBSy6WQ6slWtSvtyJ-JDWJezL1CO8tLLf90leKnKcvEW7btAvddCgHJZ39LGrxYZeEAj4QuFyI7bjsU3jcN5QMcGwFyxK-r17hbw/s1600-h/step_2.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 236px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiywjYYz_mvJQhyVn7FitZJgLVel76mjj2ZHIzdqqNxYBSy6WQ6slWtSvtyJ-JDWJezL1CO8tLLf90leKnKcvEW7btAvddCgHJZ39LGrxYZeEAj4QuFyI7bjsU3jcN5QMcGwFyxK-r17hbw/s400/step_2.png" alt="" id="BLOGGER_PHOTO_ID_5368546350040658658" border="0" /></a><br /><br />4- You will now see a widget under the name "Vote on Planet MySQL", you can go ahead and leave it there, or move it around.<br />This widget will appear on every single post you have.<br />5- Click on save, and you are done!<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJUcOhLa94-x3qCZZSbnc0-rWJj6O3ZwoAAXQS-iwk7IK1slc_jXkcSnnLkjtNFWBdIMqyTSYTUZEU0E98B4b5SK7mwhg9WLF9QPPpD8lsJDyY0T_qyMuQlPIEf6jQUQbQINKrkWn7aR17/s1600-h/step_3.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 218px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJUcOhLa94-x3qCZZSbnc0-rWJj6O3ZwoAAXQS-iwk7IK1slc_jXkcSnnLkjtNFWBdIMqyTSYTUZEU0E98B4b5SK7mwhg9WLF9QPPpD8lsJDyY0T_qyMuQlPIEf6jQUQbQINKrkWn7aR17/s400/step_3.png" alt="" id="BLOGGER_PHOTO_ID_5368546350939772002" border="0" /></a><br /><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I add it to XYZ?</span></span><br /><br />I'll talk to the Community team, and I'll ask them to have either a wiki page on the forge, or some place else the steps to add a widget like this to other blog platforms.<br /><br />Enjoy!Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com3tag:blogger.com,1999:blog-6299571792602348259.post-40396060343006676612009-07-26T07:20:00.001-04:002009-07-26T07:22:35.239-04:00MockLoad on Launchpad - MySQL ProxySeveral months ago, I started a little project at <a href="http://www.mysql.com/products/enterprise/monitor.html">work</a>, called <a href="https://launchpad.net/mockload">Mockload</a>. It started as a fun way of using the MySQL Proxy, to test our monitoring agent, as well as the rules engine and graphs on the <a href="http://dev.mysql.com/doc/refman/5.1/en/mem-server-install-osx.html">Service Manager</a>.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Why?</span></span><br />I needed a tool that would be easy to use, and improve over time. And that it would allow our QA team to send custom values to the service manager. The goal was to <span style="font-weight: bold;">pretend</span> having some very busy MySQL servers.<br /><br />And what better tool, than the MySQL Proxy itself to pretend being a busy MySQL Server!<br />The way our agent collects the MySQL related data, is by issuing different sql queries. So, I thought that I could have a MySQL proxy instance in between our agent, and the MySQL server we were monitoring.<br />This proxy would then intercept the queries that our agent was collecting, and it would return some custom values.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Writing Lua scripts</span></span><br />I started by looking at all the queries that our agent sends to the MySQL server it was monitoring, once I got the list, I went ahead, and looked at what values our graphs use. And finally I looked at the values our rules engine uses.<br /><br />I then started by mocking just two queries, <code>SHOW GLOBAL STATUS</code> and <code>SHOW GLOBAL VARIABLES</code>.<br /><br />What I do is I replace the values from a normal <code>SHOW GLOBAL STATUS</code> query, by Lua variables. And I manipulate those Lua variables to trigger alerts and to produce interesting graphs.<br /><br />In the <a href="http://bazaar.launchpad.net/%7Emockload-developers/mockload/trunk/annotate/head%3A/mockload/utils.lua">utils.lua</a> script, I have this function<br /><br /><br /><pre><br />function increase_val(incoming_val, method, increaser)<br /> if (method == "multiply") then<br /> incoming_val = proxy.global.round( incoming_val * 1.1 , 0)<br /> elseif (method == "add") then<br /> incoming_val = incoming_val + increaser<br /> end<br /> return incoming_val<br />end<br /></pre>Pretty simple, this allows me to increase specific counters by just adding to the original value or by multiplying the original value by 1.1.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Replication.</span></span><br />But the fun does not stop there, with this script, I also mock a replication setup where the slaves show some serious replication lag.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://img.skitch.com/20090726-mws72xta9j7888u8g4871jmykf.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; width: 722px; height: 131px;" src="http://img.skitch.com/20090726-mws72xta9j7888u8g4871jmykf.jpg" alt="" border="0" /></a><br /><br />This was actually pretty neat to implement, I had to make our agent go through the proxy to query the slave servers, so that the master and the slave would report the same binlog names, etc.<br /><br />Oh, and for the most part, the proxy reports having 700 binlogs, 700 users without password, 700 users with root access to the server. These are all values that are supposed to put stress on our monitoring system.<br /><br />The other challenge I had with simulating replication was how to tell each proxy instance, that the backend was a master or a slave server.<br />My first idea was to have two different Lua scripts, but that just did not look clean, I then thought of sending a custom query through each proxy, to tell it what the backend was. But again, I wasn't happy with neither approach. So I finally decided to do some query injection.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Master or Slave?</span></span><br />Whenever our agent sends a query <code>SHOW MASTER STATUS</code>, the lua script injects a <code>SHOW SLAVE STATUS</code> query to the queue. If the proxy finds an empty resultset for the slave status, it assumes that the backend server is a master. While this is not a foolproof method, it works fairly well for now.<br /><br />You can see the replication mocking on the <a href="http://bazaar.launchpad.net/%7Emockload-developers/mockload/trunk/annotate/head%3A/mockload.lua">mockload.lua</a> file.<br /><img src="file:///tmp/Database%20Activity.png" alt="" /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Graphs?</span></span><br />These are just some of the graph our service manager produces when we use MockLoad:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj54jtf9Suq6ImlYTGlHVqGUfYv7JWEl5LSZrjAyFNnl6ZdrQC1RZZxI9jn3VnFgNOkxQIXCzSGBmKa3lys4Qi1vQx3WgjVJupXsI3PczvDd14bN4XA9g8quNL-0E44pQ3GoUoqF_mJkiXJ/s1600-h/Rows+Accesses.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 134px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj54jtf9Suq6ImlYTGlHVqGUfYv7JWEl5LSZrjAyFNnl6ZdrQC1RZZxI9jn3VnFgNOkxQIXCzSGBmKa3lys4Qi1vQx3WgjVJupXsI3PczvDd14bN4XA9g8quNL-0E44pQ3GoUoqF_mJkiXJ/s320/Rows+Accesses.png" alt="" id="BLOGGER_PHOTO_ID_5362614079228845538" border="0" /></a><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibgHnis_uyS_ZtWgOJmxgnzjuYQDC-4AkwOnwrYsXnwAP2ZbJbN_D3kJEBMep06YTc09ixzAv-CuJ4-apxw9UuMgOyP63ZOfQ2rBEhH92z7AwPCBhR3t2P7nkRXdab-nBvnbdOX122U4Ig/s1600-h/Replication+Delay.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 135px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibgHnis_uyS_ZtWgOJmxgnzjuYQDC-4AkwOnwrYsXnwAP2ZbJbN_D3kJEBMep06YTc09ixzAv-CuJ4-apxw9UuMgOyP63ZOfQ2rBEhH92z7AwPCBhR3t2P7nkRXdab-nBvnbdOX122U4Ig/s320/Replication+Delay.png" alt="" id="BLOGGER_PHOTO_ID_5362614073265892434" border="0" /></a><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipUau0dOq_9QnwcjlzqvCVPf0qJ5BUBBRGZ58umkzfciT7GfomWR2BG4Aw44T0ecXZgepxBo-g4NW0Gh0hVm4sttNmM9R2_JrG8aIrukFAUnZ1x-wS4QsLcihXfa-veX1Ni2W07wBuFRbo/s1600-h/Query+Cache.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 134px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipUau0dOq_9QnwcjlzqvCVPf0qJ5BUBBRGZ58umkzfciT7GfomWR2BG4Aw44T0ecXZgepxBo-g4NW0Gh0hVm4sttNmM9R2_JrG8aIrukFAUnZ1x-wS4QsLcihXfa-veX1Ni2W07wBuFRbo/s320/Query+Cache.png" alt="" id="BLOGGER_PHOTO_ID_5362614069020625058" border="0" /></a><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDBV1Bi7thLuDmBH6ythEb237VaxJXJOtQmhoT7Glck6esVZjK46onCNTp5WGJ5xPWg1MXhud43E8Xvnw-WtFbjVHy5iifA9jcXEf6Gqb6Sdpm8i9aTTfef0ocCWxj7ejGYd__g9mjDqPw/s1600-h/Database+Activity-1.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 135px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDBV1Bi7thLuDmBH6ythEb237VaxJXJOtQmhoT7Glck6esVZjK46onCNTp5WGJ5xPWg1MXhud43E8Xvnw-WtFbjVHy5iifA9jcXEf6Gqb6Sdpm8i9aTTfef0ocCWxj7ejGYd__g9mjDqPw/s320/Database+Activity-1.png" alt="" id="BLOGGER_PHOTO_ID_5362614066867276674" border="0" /></a><span style="font-weight: bold;font-size:130%;" >Controlling the counters.</span><br />To prevent the counters from increasing without limit, I use a global variable that sets the maximum value <span style="font-weight: bold;">any</span> counter could get. Once any counter reaches that value, all counters are reset to their initial values.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Source code?</span></span><br />You can find the scripts that make up MockLoad on <a href="https://launchpad.net/mockload">Launchpad</a>. You may ask why I'm hosting them there. The answer is that I still have a long way to go with MockLoad, and I thought that some people would benefit from seeing how this was implemented. Who knows, someone may be able to use it as is, or without many changes.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I use it?</span></span><br />You need at least one MySQL server and a Proxy. You tell the proxy where the lua modules are by doing this:<br /><br />Assuming the main file is<br /><code>/usr/local/scripts/mockload.lua</code><br />and the modules are in<br /><br /><code>/usr/local/scripts/mockload/</code><br /><pre><br />$ export LUA_PATH=/usr/local/scripts/?.lua<br />$ ./sbin/mysql-proxy --defaults-file=/path/to/ini/file/proxy.ini<br /></pre><br />You can then send a query like <code>SHOW GLOBAL STATUS</code> through the proxy port 4040, and each time you send that query, you will notice some counters returning increased values (more than normal for an idle server :) )<br /><br />I hope you all enjoy it and as always, feedback is welcome.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-46507090292505036632009-05-27T07:37:00.000-04:002009-05-27T07:37:00.396-04:00MySQL Proxy => proxydb + replicationA couple of weeks ago I <a href="http://fmpwizard.blogspot.com/2009/05/mysql-proxy-proxydb.html">wrote a lua script</a> to use with the MySQL Proxy that transforms the Proxy into a <span style="font-weight: bold;">key=>value</span> lookup dictionary.<br /><br />But I couldn't just stop there. So I decided to add replication to it :).<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX3m_xjVC7_Ek2ralLuPAxdyFDDFl0DVzFcjmbxQF_iY5GWQKnMtk3MiCK9rrU3x5y11dLRsjS2NCYgGMmiGUx7xBIomS9f0xZVAt3s17gM-teNAAZfr07quGzn9yvT4uwvZDRpxc6J0VD/s1600-h/proxydb.png"><img style="cursor: pointer; width: 400px; height: 74px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX3m_xjVC7_Ek2ralLuPAxdyFDDFl0DVzFcjmbxQF_iY5GWQKnMtk3MiCK9rrU3x5y11dLRsjS2NCYgGMmiGUx7xBIomS9f0xZVAt3s17gM-teNAAZfr07quGzn9yvT4uwvZDRpxc6J0VD/s400/proxydb.png" alt="" id="BLOGGER_PHOTO_ID_5340460816689681298" border="0" /></a><br /><br />The basic idea is that you have one proxy that acts like a <span style="font-weight: bold;">master</span>, it can handle all <span style="font-weight: bold;">write/read</span> operations. Once it receives a <span style="font-weight: bold;">write query</span>, it will send that query to the <span style="font-weight: bold;">slave proxy instances</span>, and after the last slave gets the query, the master will return a confirmation to the mysql client.<br />And of course, you send your read queries to the slave proxy instances.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Show me the code.</span></span><br />It is available on the <a href="http://forge.mysql.com/tools/tool.php?id=199">MySQL Forge</a>. And I'd recommend using <a href="http://darren.oldag.net/2009/04/arbitrary-replicationproxy-topologies.html">Oldag's</a> version of the <a href="https://code.launchpad.net/%7Esandbox-developers/mysql-sandbox/replication-topo">sandbox</a>, which allows you to easily setup different topologies of MySQL servers and Proxies. (It saved me lots of time)<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Complex situation, lots of ins, outs and what have you.</span></span><br />(or how does it work?)<br /><br />It uses the connection pool feature from the MySQL Proxy that is based on the rw-splitting.lua script.<br />This means that you will need to start a few different connections to the master proxy instance to get the poll going.<br />I do this by using this simple command line<br /><pre><br />$ for i in `seq 1 100` ; do ./proxy1/use -e "exit"; done ;<br /></pre>It also makes usage of <a href="http://fmpwizard.blogspot.com/2009/05/mysql-proxy-is-it-mysql-server-or-what.html">mocking a mysql server</a>. The script checks if there is a backend defined (<span style="font-style: italic;">proxy-backend-addresses</span>), if there is one, it will tr to open a connection, otherwise, it is the last node on the chain and it will return an <span style="font-weight: bold;">OK packet</span>.<br /><br />Oh, look at the <span style="font-weight: bold;">proto.to_challenge_packet({})</span> function, which is better than working directly with the protocol.<br /><br />We then have <span style="font-weight: bold;">read_query()</span>, which is pretty easy to understand, I tried to add as many comments as I could to the <a href="http://forge.mysql.com/tools/tool.php?id=199">actual code</a>.<br />We have different functions for each type of query. I think this is a clean way of doing :).<br />You will also see that each function that handles different query types has a line like this one:<br /><pre><br />if proxy.global.last_node == 0 then<br /></pre><br />This tells the proxy that if it is the last node on the chain, it has to return a resultset<br /><br /><span style="font-weight: bold;">read_query_result()</span><br />This was fun to work on. Here is where you send the queries to the slave proxies. The proxy loops over the list of backends and appends the same query. Once it reached the last proxy, it resets the current backend server and returns an OK packet to the mysql client.<br /><br />This implementation could help you duplicate and reroute your queries to a different mysql server. So that you can send them to a server with different values on the my.cnf file, or any other changes that you'd like to test.<br /><br />I hope you find it useful and feedback is always welcome.<br /><br />*I got seq for my mac from this <a href="http://fredrik-rodland.blogspot.com/2008/10/seq-on-mac-os-x.html">post</a>.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-11918455304731838072009-05-25T11:40:00.002-04:002009-05-25T12:31:15.398-04:00MySQL Proxy - is it a MySQL server or what is it?<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD3GTcjKKRX2Ao-MrelUNsKbMGNiVVfGbg26A1QQFg1zE24eBONTsURtC63ZH-JUrynopnGnN5Hvktc4X1XL01vOPfRcT3LEKCiPxnahCWfharDGwgBVD4DF6qaH-j0iCgqhK_VSyDOaJm/s1600-h/Proxy_business_card.png"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 400px; height: 170px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD3GTcjKKRX2Ao-MrelUNsKbMGNiVVfGbg26A1QQFg1zE24eBONTsURtC63ZH-JUrynopnGnN5Hvktc4X1XL01vOPfRcT3LEKCiPxnahCWfharDGwgBVD4DF6qaH-j0iCgqhK_VSyDOaJm/s400/Proxy_business_card.png" alt="" id="BLOGGER_PHOTO_ID_5339783583994900770" border="0" /></a><br /><br />There are times when you don't need a MySQL server to handle your queries, this could come handy when you are automating tests, or writing <a href="http://fmpwizard.blogspot.com/2009/05/mysql-proxy-proxydb.html">interesting Lua scripts</a>.<br /><br />Yes, you can use the <a href="http://forge.mysql.com/wiki/MySQL_Proxy_Cookbook">MySQL Proxy</a> for this as well. And have it pretend to be a MySQL server.<br />Today we'll focus on a simple implementation. We will handle an initial connection, a SHOW DATABASES query and the exit (QUIT) command.<br /><br />You can see all the hooks that the proxy handles on this <a href="http://forge.mysql.com/tools/tool.php?id=109">Lua script written by Giuseppe</a><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Connecting to a server.</span></span><br /><br /><pre><br />function packet_auth(fields)<br /> fields = fields or { }<br /> return "\010" .. -- proto version<br /> (fields.version or "5.0.45-proxy") .. -- version<br /> "\000" .. -- term-null<br /> "\255\255\255\255" .. -- thread-id<br /> "\000\020\000\000" ..<br /> "\000\000\000\000" .. -- challenge - part I<br /> "\000" .. -- filler<br /> "\001\130" .. -- server cap (long pass, 4.1 proto)<br /> "\008" .. -- charset<br /> "\002\000" .. -- status<br /> ("\000"):rep(26) -- filler & challenge - part II<br />end<br /></pre><br />This function prepares the raw response that the MySQL Proxy will return to all clients connecting through the proxy port (default 4040)<br /><br />If you are into protocols and what not, you can see the <a href="http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol">Client - Server protocol on the forge</a>.<br /><br />Then you need to use this function<br /><pre><br />function connect_server()<br /> -- emulate a server<br /> proxy.response = {<br /> type = proxy.MYSQLD_PACKET_RAW,<br /> packets = {<br /> packet_auth()<br /> }<br /> }<br /> return proxy.PROXY_SEND_RESULT<br />end<br /></pre><br />There, you see how we return a RAW packet (<code>proxy.MYSQLD_PACKET_RAW</code>), most of the time you see a <code>proxy.MYSQLD_PACKET_OK</code><br />or <code>proxy.MYSQLD_PACKET_ERR</code> type of result. And then you see that<br /><code>packets</code> gets the result of the <code>packet_auth()</code> function.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Handle the Query types.</span></span><br /><br /><br /><pre><br />function read_query( packet )<br /><br /> if (string.byte(packet) == proxy.COM_QUIT)<br /> or (string.byte(packet) == proxy.COM_INIT_DB) then<br /> -- This gets called when you disconnect from the server<br /> -- or when you specify a database on connection time<br /> proxy.response.type = proxy.MYSQLD_PACKET_OK<br /> return proxy.PROXY_SEND_RESULT<br /> elseif string.byte(packet) == proxy.COM_QUERY then<br /> if packet:sub(2) == "SHOW DATABASES" then<br /> return show_databases()<br /> else<br /> -- Unkown query<br /> return error_result (<br /> "I haven't learnt how to handle that query"<br /> , '101'<br /> , '!101')<br /> end<br /> end<br />end<br /></pre><br />If the query type is either a <code>COM_QUIT</code> or <code>COM_INIT_DB</code>, you can just send an OK packet, and that will be fine.<br /><br />Then we check for <code>COM_QUERY</code>, and once we find a query, we try to match the query text. In this example, we only handle <code>SHOW DATABASES</code> (all upper case), but you can handle any other query, even queries that are not real SQL :) .<br /><br />If the MySQL Proxy got a <code>SHOW DATABASES</code> query, it will call the <code>show_databases()</code> function and return a resultset of 70 databases.<br /><br />For all other queries, the proxy will return an error message back to the mysql client.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Send a result back to the client.</span></span><br /><pre><br />function show_databases()<br /> proxy.response.type = proxy.MYSQLD_PACKET_OK<br /> proxy.response.resultset = {<br /> fields = {<br /> { type = proxy.MYSQL_TYPE_STRING, name = "Database", },<br /> },<br /> rows = {}<br /> }<br /> -- here we add 70 database names to the resulset<br /> for i = 1, 70 do<br /> table.insert(<br /> proxy.response.resultset.rows,<br /> {"db_" .. i})<br /> end<br /> return proxy.PROXY_SEND_RESULT<br />end<br /></pre><br />Here we create the table for <code>proxy.response.resultset</code>, note the empty <code>rows</code> table. And then, we use a for loop to add 70 items that will become 70 rows on the resultset.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Full code.</span></span><br /><br />You can find this and many other lua scripts to use with the MySQL Proxy on the <a href="http://forge.mysql.com/tools/tool.php?id=210">MySQL forge</a> site.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How can I use it?</span></span><br />Because this lua script simulates being a MySQL Server, you do not need to specify a proxy-backend-address value, just start the proxy like this:<br /><pre><br />$ sbin/mysql-proxy --proxy-lua-script=/path/to/server_mock.lua<br /></pre>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com10tag:blogger.com,1999:blog-6299571792602348259.post-43057571716350771222009-05-19T00:33:00.001-04:002009-05-19T00:33:01.186-04:00MySQL Proxy - what if it crashes?<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOGP3KXgx8_NZBfpW9N2LEEZ2bsiw9TC-HtOC9kevvbtN1wsVgOto8z-GT1cnVa-4aawqpHfaBvW9Fxfn40eh5Z4D4weWsji6dRsRXe4njqY-4pZLULqqpgNqKH9Ci2t4G-2R_EcD66ija/s1600-h/Crash.jpg"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 320px; height: 213px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOGP3KXgx8_NZBfpW9N2LEEZ2bsiw9TC-HtOC9kevvbtN1wsVgOto8z-GT1cnVa-4aawqpHfaBvW9Fxfn40eh5Z4D4weWsji6dRsRXe4njqY-4pZLULqqpgNqKH9Ci2t4G-2R_EcD66ija/s320/Crash.jpg" alt="" id="BLOGGER_PHOTO_ID_5337385667832365634" border="0" /></a><br />While I hope the MySQL Proxy never crashes, it will happen, there will be some strange (or maybe not so strange) usage or workload and it will die.<br /><br />To avoid this, you could decide not to use it, or maybe you could use something like <a href="http://www.linux-ha.org/">Linux HA</a> to have more than one MySQL Proxy running at all times. Or you could use one of the new features that comes with the version 0.7.0.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">What is it?</span></span><br />We now have a <span style="font-weight: bold;">--keepalive</span> option. As the name indicates, if the mysql proxy process dies/crashes, it will come back up in a few seconds (less than 5 seconds).<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How does it work?</span></span><br />If you start the MySQL Proxy with the keepalive option, there will be two processes with the same name. One will be very small, about 600KB. and then you will also see the "real" mysql proxy process. The "angel" process has a lower PID value than the "real" mysql proxy process.<br /><br />If there is some kind of error that causes the proxy to die, the "<span style="font-weight: bold;">angel</span>" process will wait about 3 seconds, and try to restart the proxy process.<br /><br />You can "force" this behavior by sending a<br /><pre><br />$ kill -SIGKILL < pid of the proxy ><br /></pre><br />or<br /><pre><br />$ kill -SEGV < pid of the proxy ><br /></pre><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I use it?</span></span><br />Very simple, if you use a <a href="http://blog.zhuzhaoyuan.com/2009/02/using-mysql-proxys-configuration-file/">configuration file</a>, you need to add a new line with<br /><pre><br />keepalive = true;<br /></pre><br />or you can start the mysql proxy like this:<br /><pre><br />sbin/mysql-proxy --proxy-lua-script=/mysql-lua-scripts/trunk/scripts/proxydb.lua --keepalive<br /></pre><br />Enjoy! ... And next time I'll write about using <a href="http://www.gnu.org/software/gdb/">GDB</a> to get some debugging information about the crash.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com3tag:blogger.com,1999:blog-6299571792602348259.post-72223426634115328972009-05-10T22:29:00.000-04:002009-05-10T22:29:00.142-04:00MySQL Proxy - proxydbWhat I really like about having Lua and MySQL Proxy together is that it turns out to be very flexible, you can have the proxy do all kinds of things. And the last thing I made the MySQL Proxy do is to act like memcached.<br /><br />Well, maybe not, but it handles <span style="font-style: italic;">key => value pairs </span>now :P<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivwNrfTnsfEo2uqP0B8JFxdQJtHSSWZLkNam87hWQtYApU5nixQPhGQlJGdOwyxUn6NVinAoDmZl5GDOzyz8izByjNjjSW05MzN7iWCJQ0YddKyRd-CmAPLSoGwjQ0cqlNlIEkKRr23rmZ/s1600-h/proxydb.png"><img style="cursor: pointer; width: 200px; height: 150px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivwNrfTnsfEo2uqP0B8JFxdQJtHSSWZLkNam87hWQtYApU5nixQPhGQlJGdOwyxUn6NVinAoDmZl5GDOzyz8izByjNjjSW05MzN7iWCJQ0YddKyRd-CmAPLSoGwjQ0cqlNlIEkKRr23rmZ/s200/proxydb.png" alt="" id="BLOGGER_PHOTO_ID_5334385085213727362" border="0" /></a><br /><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">What does it do?</span></span><br /><br />It handles 5 basic query types:<br /><pre><br />mysql> INSERT "key" = "value";<br /></pre><br />Very simple, insert a key => value pair on a <code>proxy.global.db</code> table, if there is already a value for that query, it will overwrite it.<br /><pre><br />mysql> SELECT "key";<br /></pre><br />It retrieves the value for the specified key.<br /><pre><br />mysql> DELETE "key";<br /></pre><br />Deletes the key => value pair from the lua table.<br /><pre><br />mysql> SELECT *;<br /></pre><br />It returns all the key=> value pairs.<br /><pre><br />mysql> DROP;<br /></pre><br />It deletes all rows from the table.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Internals.</span></span><br />This time I used the <a href="http://bazaar.launchpad.net/%7Emysql-proxy-developers/mysql-proxy/trunk/annotate/head%3A/examples/tutorial-tokenize.lua">tokenizer</a> to parse the queries, and I'm so glad I did. It is much easier and cleaner that writing regular expressions to match your queries.<br /><br />I also wrote different functions to handle each statement. Which I call based on the tokens I find.<br /><br />Looking at the script, you will find different ways of returning results back to the mysql client. Something that took me a while to figure out was, how to return an empty resulset, but with a number of affected rows, like what you get from an INSERT statement.<br /><br />This is the answer:<br /><pre><br />function add( key, value )<br /> -- We add the key value pair to the global table<br /> proxy.global.db[key] = value<br /> -- we return a nice "affected rows count"<br /> proxy.response.type = proxy.MYSQLD_PACKET_OK<br /> proxy.response.affected_rows = 1<br /> proxy.response.insert_id = 0<br /> return proxy.PROXY_SEND_RESULT<br />end<br /></pre><br />If you call this function, you need to do so like this:<br /><pre><br />return add(tokens[i + 1]["text"], tokens[i + 3]["text"])<br /></pre><br />and not just like this:<br /><pre><br />add(tokens[i + 1]["text"], tokens[i + 3]["text"])<br /></pre><br />* Note the "<span style="font-weight: bold;">return</span>" there. Otherwise, you will not get the custom resulset on the client.<br /><br />I also added the <span style="font-weight: bold;">connect_server()</span> function, which allows you to run the mysql proxy without any mysql server on the backend.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How do I use it?</span></span><br />Get the code from the <a href="http://forge.mysql.com/tools/tool.php?id=199">MySQL Forge</a> site, and save it as proxydb.lua<br />Then, start the MySQL Proxy like this:<br /><pre><br />./bin/mysql-proxy --proxy-lua-script=/src/mysql-lua-scripts/trunk/scripts/proxydb.lua<br /></pre><br />You can then connect to the port 4040 using your mysql client and start sending queries. If you use any of the queries I showed before, the proxydb.lua script will handle them.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Numbers?</span></span><br />Yesterday I run some tests and I found that<br /><ul><li>Having 3,908,492 entries </li><li>Key length was about 11 characters</li><li>Value length was about 17 characters</li><li>MySQL Proxy process used 1.5GB of RAM</li></ul><span style="font-size:130%;"><span style="font-weight: bold;">Next?</span></span><br />Who knows, maybe someone will use this script and let me know how it runs on a busy setup :).Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com2tag:blogger.com,1999:blog-6299571792602348259.post-22319656274307633612009-04-30T23:59:00.002-04:002012-12-05T11:03:07.756-05:00How do I use the MySQL Proxy Admin plugin?We have an Admin plugin for the MySQL Proxy, but people started asking <a href="http://forums.mysql.com/read.php?146,260293,260293#msg-260293">how to use it</a>. I only found one example, thanks to <a href="http://datacharmer.blogspot.com/2009/01/mysql-proxy-is-back.html">Giuseppe</a>, but people wanted <a href="https://answers.launchpad.net/mysql-proxy/+question/67892">more</a>.<br />
<br />
While the admin plugin is somehow limited, it already provides some nice features. One of the use cases is to give access to information to only authorized users. The Admin plugin uses its own username and password to authenticate users. This is not related to any user on your MySQL server.<br />
<br />
<span style="font-size: 130%;"><span style="font-weight: bold;">The example.</span></span><br />
<br />
I went ahead and put two scripts together in about an hour. They are basic, but should give you more of an idea of what you can do.<br />
<br />
<span style="font-size: 130%; font-weight: bold;">Number of queries processed.</span><br />
<br />
Connecting to the admin port 4041, you can execute this query<br />
<br />
<pre>
mysql> show querycounter;
+---------------+
| query_counter |
+---------------+
| 15 |
+---------------+
1 row in set (0.00 sec)</pre>
<br />
And you can see how many queries went through the proxy (does not count the queries to the admin plugin)<br />
<br />
<br />
<span style="font-size: 130%;"><span style="font-weight: bold;">Backends list.</span></span><br />
<br />
You can see some information about the backends. Type 1 means master and type 2 means slave<br />
<br />
<pre>
mysql> SELECT * FROM backends;
+-------------+-----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-----------------+-------+------+
| 1 | 127.0.0.1:22547 | 1 | 1 |
| 2 | 127.0.0.1:22548 | 0 | 2 |
| 3 | 127.0.0.1:22549 | 0 | 2 |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)</pre>
<br />
<br />
<span style="font-size: 130%;"><span style="font-weight: bold;">Client connections list</span></span><br />
<br />
<pre>
mysql> SHOW PROXY PROCESSLIST;
+------+--------------------+--------------------------+
| Id | IP Address | Time |
+------+--------------------+--------------------------+
| 249 | 127.0.0.1:53830 | Fri May 1 00:13:28 2009 |
| 248 | 127.0.0.1:53827 | Fri May 1 00:13:27 2009 |
| 247 | 127.0.0.1:53825 | Fri May 1 00:13:26 2009 |
| 250 | 192.168.0.99:53836 | Fri May 1 00:13:38 2009 |
+------+--------------------+--------------------------+
4 rows in set (0.00 sec)</pre>
<br />
<br />
The way it works is basically by collecting data using the proxy plugin, making that information available using proxy.global.* variables, and then the admin plugin reads those global variables and returns the data on a pretty format :)<br />
<br />
You can start the proxy with a command like this:<br />
<pre>
./sbin/mysql-proxy --admin-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/admin-1.lua \
--proxy-backendddresses=127.0.0.1:22547 \
--proxy-read-only-backend-addresses=127.0.0.1:22548 \
--proxy-read-only-backend-addresses=127.0.0.1:22549 \
--proxy-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/reporter.lua \</pre>
<pre>--plugins=admin</pre>
<br />
<br />
<span style="font-size: 130%;"><span style="font-weight: bold;">Where are the Lua scripts?</span></span><br />
<br />
You can find them on the MySQL Forge site <a href="http://forge.mysql.com/tools/tool.php?id=197">here</a> and <a href="http://forge.mysql.com/tools/tool.php?id=198">here</a>.<br />
<br />
And remember that the default username and password for the admin plugin is root / secret, which you can change by using<br />
<pre>
--admin-username=proxy \
--admin-password=guesswhat</pre>
<br />
<br />
Enjoy!Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com6tag:blogger.com,1999:blog-6299571792602348259.post-73607640100358356642009-04-14T21:35:00.000-04:002009-04-14T21:31:29.587-04:00A feature, a bug, a new feature and a bug fixAfter I wrote about <a href="http://fmpwizard.blogspot.com/2009/04/mysql-proxy-and-masterinfo-contribution.html">reading a master.info</a> file using the MySQL Proxy, I went ahead and added the missing piece. <a href="https://code.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/master_info">Creating a master.info file</a> using the MySQL Proxy.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">A bug?</span></span><br />As I went back to <span style="font-weight: bold;">lib/mysql-proto.c</span> looking for a function that I could duplicate and modify to add the <span style="font-weight: bold;">to_masterinfo_string()</span> function, I realized that I missed a few master_ssl_* fields. It turned out that I was not exporting all the fields from the master.info file.<br /><br />This time, the bug fix was easy enough. After <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/master_info/revision/647?start_revid=647#tests/unit/lua/mysql-proto.lua">modifying the test case</a> to account for the missing fields, I added a few lines of code and I was ready to add the new feature.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">New feature</span></span><br />You can now use the function <span style="font-weight: bold;">to_masterinfo_string()</span>, which takes as a parameter a table. You can create one using <span style="font-weight: bold;">from_masterinfo_string()</span><br /><br />You could read <a href="http://fmpwizard.blogspot.com/2009/04/reading-masterinfo-files-using-mysql.html">this example</a> to get an idea of how it could be used.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Next?</span></span><br />I'll continue my work on <a href="http://fmpwizard.blogspot.com/2009/03/simulating-workload-with-mysql-proxy.html">Mock Load</a>(*) and wait for the <a href="https://code.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/master_info/+merge/5520">merge proposal</a> to be accepted.<br /><br />(*)More about what it does once I finish some legal paperwork here at Sun.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-54269081828800749072009-04-10T06:00:00.000-04:002009-04-10T06:00:00.625-04:00Reading master.info files using the MySQL ProxyAfter I wrote about a <a href="http://fmpwizard.blogspot.com/2009/04/mysql-proxy-and-masterinfo-contribution.html">new feature on MySQL Proxy</a> that helps you read master.info files, I thought that showing an example could come handy.<br /><br />You can find the complete file on the <a href="http://forge.mysql.com/tools/tool.php?id=193">MySQL Forge</a> and once I have a test case for this script, it will be available on <a href="https://launchpad.net/mysql-proxy-lua-scripts">Launchpad</a>.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Explaining the code.</span></span><br />You can see at the top I have<br /><pre>local proto = assert(require("mysql.proto"))<br /></pre>This is important, as it makes the <span style="font-weight: bold;">from_masterinfo_string()</span> function available for use.<br /><br />I also included a function called <span style="font-weight: bold;">get_command()</span>, which is a modification of code found <a href="http://forge.mysql.com/tools/tool.php?id=76">here</a>.<br />It basically does a simple parsing of the statement you send through the proxy and returns two variables.<br /><br /><span style="font-weight: bold;"><span style="font-size:130%;">read_query()</span><br /></span>Here, I look for the query "<span style="font-style: italic;">read masterinfo</span>;" and convert it into <pre>SHOW GLOBAL VARIABLES LIKE "datadir"</pre>I do this so that I could get the location of the master.info file. You can just hardcode it, but that's not fun :)<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">read_query_result()</span></span><br />Here, I read the value for datadir, and use it to load the content of master.info into a lua variable, convert the content into a mysql result set and send it back to the client.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">How to use.</span></span><br />You need to connect to a mysql proxy that is in front of a slave mysql server; (and tell the proxy to use this lua script)(*). Then execute<br /><pre><br />mysql> read masterinfo;<br /></pre><br />And you would get something like this:<br /><pre><br />mysql> read masterinfo;<br />+----------------------+----------------+<br />| Variable_name | Value |<br />+----------------------+----------------+<br />| master_user | slave |<br />| master_ssl | 0 |<br />| master_port | 43309 |<br />| master_log_file | dv1-bin.000005 |<br />| master_connect_retry | 60 |<br />| master_password | slave |<br />| master_log_pos | 272 |<br />| master_host | 192.168.2.1 |<br />+----------------------+----------------+<br />8 rows in set (0.00 sec)<br /></pre><br />And you are done.<br /><br /><br />(*)You can find more information about the MySQL Proxy on the <a href="http://forge.mysql.com/wiki/MySQL_Proxy">MySQL Forge</a> site.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-79003427342061628542009-04-07T11:06:00.002-04:002009-04-07T11:33:37.430-04:00MySQL Proxy and master.info (contribution 2 of N)This time I'll write about a nice featured we now have on the MySQL Proxy.<br /><br /><span style="font-weight: bold;font-size:130%;" >Parsing master.info</span><br />The master.info is a file that the MySQL server creates when you are running a slave server, you can read the <a href="http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html">manual</a> for more details.<br />And now you can use the MySQL Proxy to read that file and parse the data from it.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Background</span></span><br />It all started when Jan emailed the proxy discuss mailing with the <a href="http://jan.kneschke.de/2009/1/30/mysql-proxy-from-0-6-1-to-0-7-0">Changelog</a> from 0.6.1 to 0.7.0. The part that got my attention was:<br />"...<br />Core<br />[skip]<br />* added parser for master.info files"<br />... "<br />As I was working on a Lua script that did some simulation, I <a href="https://lists.launchpad.net/mysql-proxy-discuss/msg00021.html">asked Jan</a> how that worked, and I found out that it was only implemented on C-land, and only compatible with MySQL server 5.1. That meant you could not write a Lua script and use the parser we had.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW9pVl-OcDUbIR21xmoQlXe5FV_xK_V6YtmPpgl5G1xRCgmFB-KfARPWKbGfvGV0VFSgmhAexpQDAfwKdwDAzPMZ-u1ljB-CA4FtONJgDX_yeig2JENIGEGAEOcvZlnSrz_LeYcQRuporL/s1600-h/bridge.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 320px; height: 246px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW9pVl-OcDUbIR21xmoQlXe5FV_xK_V6YtmPpgl5G1xRCgmFB-KfARPWKbGfvGV0VFSgmhAexpQDAfwKdwDAzPMZ-u1ljB-CA4FtONJgDX_yeig2JENIGEGAEOcvZlnSrz_LeYcQRuporL/s320/bridge.jpg" alt="" id="BLOGGER_PHOTO_ID_5321677926151730162" border="0" /></a><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Nothing like motivation</span></span><br />So I went ahead and asked how much work it would take to implement this feature and I was pointed to the lib/mysql-proto.c file. As I really wanted to have this implemented, I couldn't resist and made a local branch to work on it.<br /><br />I first looked at that whole file, trying to figure out how things worked and after reading lib/mysql-proto.c I found out I also needed to look at src/network-mysqld-masterinfo.c. After some more reading, I was ready to write some code.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Thought process</span></span><br />Well, I was ready to modify what was there. The way I usually add code when I'm not very familiar with it is by modifying the current code, little by little, compile, test, fix what I broke and continue. I do this a few times until I have something new working :).<br /><br />This time I took this function:<br /><pre><br />static int lua_proto_get_ok_packet (lua_State *L) {<br /> size_t packet_len;<br /> const char *packet_str = luaL_checklstring(L, 1, &packet_len);<br /> network_mysqld_ok_packet_t *ok_packet;<br /> network_packet packet;<br /> GString s;<br /> int err = 0;<br /><br /> s.str = (char *)packet_str;<br /> s.len = packet_len;<br /><br /> packet.data = &s;<br /> packet.offset = 0;<br /><br /> ok_packet = network_mysqld_ok_packet_new();<br /><br /> err = err || network_mysqld_proto_get_ok_packet(&packet, ok_packet);<br /> if (err) {<br /> network_mysqld_ok_packet_free(ok_packet);<br /><br /> luaL_error(L, "%s: network_mysqld_proto_get_ok_packet() failed", G_STRLOC);<br /> return 0;<br /> }<br /><br /> lua_newtable(L);<br /> LUA_EXPORT_INT(ok_packet, server_status);<br /> LUA_EXPORT_INT(ok_packet, insert_id);<br /> LUA_EXPORT_INT(ok_packet, warnings);<br /> LUA_EXPORT_INT(ok_packet, affected_rows);<br /><br /> network_mysqld_ok_packet_free(ok_packet);<br /><br /> return 1;<br />}<br /></pre><br />I duplicated it and renamed it "lua_proto_get_masterinfo_string". I chose that function as it had some "LUA_EXPORT_INT" lines which sounded like what I needed.<br /><br />As you can see form <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/566">here</a> I started by commenting out some lines and renaming some function calls and other lines of code.<br /><br />I also added a line to "luaL_reg mysql_protolib" which is where things really get exposed to Lua (I found this out by searching the whole branch for "from_ok_packet" which I found on test/unit/lua/mysql-proto.lua)<br /><br />I then compiled the new code and wrote a simple Lua script to test this (which was not the best way, but more on that later).<br />At this point all I wanted to test was that I could call "get_masterinfo()" from Lua, and it worked!<br /><br />I then <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/567#lib/mysql-proto.c">removed</a> the lines that I comentd out and after showing Jan where I was, he suggested renaming the function, so I <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/568#lib/mysql-proto.c">did</a>.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Where I should've started.</span></span><br />We all now that we should first have a test, and then code, well, I wasn't sure what I was doing so I started the other way, but eventualy I got to writing the test for this piece of code.<br />There was already a lua unit test for the mysql-proto.c file, so I added some <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/569#tests/unit/lua/mysql-proto.lua">tests for the master.info parsing.</a><br /><br />At that point, it only worked for master.info files from MySQL 5.1, so the next step was to <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/570">add support for 4.1 and 5.0</a>.<br /><br />The way I implemnted the suport for 4.1 and 5.0 master.info files took me a few days, I really wanted to return a NULL (nil in Lua) value for "master_ssl_verify_server_cert" if it was not present, but I really did not know how to do it. Either Jan or Kay came to the rescue there and we ended up with <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/fmpwizard/revision/573#tests/unit/lua/mysql-proto.lua">this</a>.<br /><br />Basically, if you don't export it, it will be NULL. And having the unit test made it so much easier to test the changes.<br /><br />And as I was done with this, I went ahead and proposed a merge and a few days later it was part of the Main Proxy code.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Next?</span></span><br />So far we can parse a master.info file, but we cannot "create" one, so this is next, I want to have a function that would create the master.info file, I know I could just use the io library from Lua, but it will be more fun to add a "to_masterinfo_string()" function on Lua land :)<br /><br />Thanks and enjoy!Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-13402216890024008492009-04-06T06:00:00.000-04:002009-04-06T06:00:00.109-04:00Contributing to MySQL Proxy (1 of N)Thanks to <a href="http://blogs.sun.com/kay/entry/mysql_proxy_code_now_live">Kay</a>, it is now much easier to contribute to the <a href="https://launchpad.net/mysql-proxy">MySQL Proxy project</a>. And it turns out you don't have to be a super C developer to help out.<br />Yes, you may think, you can report <a href="http://bugs.mysql.com/">bugs,</a> help <a href="https://answers.launchpad.net/mysql-proxy">answering questions</a>, even submit <a href="https://launchpad.net/mysql-proxy-lua-scripts">Lua scripts</a>. But there is another way you can contribute.<br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Blueprints</span></span><br />You can check out the <a href="https://blueprints.launchpad.net/mysql-proxy">blueprints</a> for the Proxy and pick one that seems "simple" to implement. That's what I did. I went there and picked "<a href="https://blueprints.launchpad.net/mysql-proxy/+spec/chassis-short-option-names">Chassis/Proxy should have short names for often used options</a>". It sounded simple enough for me, and I thought it would help me get more familiar with the source code.<br />After a few <a href="https://lists.launchpad.net/mysql-proxy-discuss/msg00077.html">emails</a> on the <a href="https://launchpad.net/%7Emysql-proxy-discuss">mailing list</a>, I gather all the info I needed and created a <a href="https://code.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/short-options">branch</a> to work on it.<br /><br />It turned out not to be <a href="http://bazaar.launchpad.net/%7Ediego-fmpwizard/mysql-proxy/short-options/revision/631#plugins/proxy/proxy-plugin.c">much work</a> at all :), so after I pushed my changes to the branch I created on Launchpad, I proposed a merge and now I'm waiting for a code review and hopefully it will be part of the main Proxy branch.<br /><br /><br /><span style="font-size:130%;"><span style="font-weight: bold;">Conclusion</span></span><br />No matter what your skill set is, there are plenty of ways to get involved on this project.Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com2tag:blogger.com,1999:blog-6299571792602348259.post-10643055062001087652009-03-26T22:56:00.000-04:002009-03-26T22:59:33.213-04:00Lua script repository for MySQL Proxy<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiDfn9f2jTwvJO51YTzJuqRyriTIXP4U_rEDTAOfNfx-Yn0ZnTdvheyB6Q6Og0yjINI64T_odzckHh3emlf1JSYFjDbf6WsNfk0pIMveXXZOnEDUXYWKf2rL_KTOPmz8ISPiKTykVT-q-y/s1600-h/Sakila_proxy_256x298.jpg"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 256px; height: 298px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiDfn9f2jTwvJO51YTzJuqRyriTIXP4U_rEDTAOfNfx-Yn0ZnTdvheyB6Q6Og0yjINI64T_odzckHh3emlf1JSYFjDbf6WsNfk0pIMveXXZOnEDUXYWKf2rL_KTOPmz8ISPiKTykVT-q-y/s320/Sakila_proxy_256x298.jpg" alt="" id="BLOGGER_PHOTO_ID_5314872121985330546" border="0" /></a><br />Doing QA on the <a href="http://forge.mysql.com/wiki/MySQL_Proxy">MySQL Proxy</a> and the Enterprise tools, I started writing a Lua script to use with the proxy. The goal was to tell our monitoring agent that the server it was monitoring was very busy. It basically intercepts queries like <code>SHOW GLOBAL VARIABLES</code> or <code>SHOW GLOBAL STATUS</code> among others and returns a custom resultset. (More details on a future post)<br /><br />It is still a work in progress, but I wanted to give the community access to it. After some emails on the <a href="https://launchpad.net/%7Emysql-proxy-discuss">MySQL Proxy Discuss</a> mailing list, I created a <a href="https://launchpad.net/mysql-proxy-lua-scripts">project on Launchpad</a> that will host this script (one main script and 4 modules so far) and the idea is to make this project a community owned project, so that we can have all kinds of Lua scripts there.<br /><br /><div><span class="Apple-style-span" style="font-weight: bold;">What about the MySQL Forge?</span><br />I know we have many Lua scripts on the <a href="http://forge.mysql.com/tools/search.php?t=tag&k=mysqlproxy">forge site</a>, but having the scripts on Launchpad would enable anyone to maintain those scripts.<br /><br />In the last few month, there have been some changes on the MySQL Proxy code that broke some of those scripts, by moving them to this new repository, we could start fixing them.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVr0QHPpqwUf2hrByz5t726pxsHE34hxan8UvRaf9nCCLGhERbkxLrG7TrlS6bPKchE40421oN-KM6bKUESu-0CawI4VlEVHqnJKptqWLqPaSwX9OCExdN3Bmrwvfj3hZQu6dIWmda1Pm3/s1600-h/lua-logo.gif"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 128px; height: 128px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVr0QHPpqwUf2hrByz5t726pxsHE34hxan8UvRaf9nCCLGhERbkxLrG7TrlS6bPKchE40421oN-KM6bKUESu-0CawI4VlEVHqnJKptqWLqPaSwX9OCExdN3Bmrwvfj3hZQu6dIWmda1Pm3/s200/lua-logo.gif" alt="" id="BLOGGER_PHOTO_ID_5314872554047446850" border="0" /></a><br />Now, the legal department at Sun is doing some work so that this script can be available under the GPLv2 license. This process may take a few weeks :(</div><div><br /></div><div>As soon as I get this approved, I'll blog about it!<br /></div>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0tag:blogger.com,1999:blog-6299571792602348259.post-50415070313568720352009-03-24T22:45:00.006-04:002009-03-25T17:01:46.352-04:00Simulating workload with MySQL ProxyOn April 2nd at 10 AM PST, I'll be giving a webinar with <a href="http://datacharmer.blogspot.com/">Giuseppe</a>. I will be talking about how I use the MySQL Proxy to test itself and to test our Monitoring Agent using some Lua scripts.<br /><br />I'll also talk about a new Launchpad repository for Lua scripts to use with the MySQL Proxy. One of the nice things about it, is that it will be community-own. So even though a Sun employee registered it, the community will be able to make contributions and/or decide what gets included there. In some ways, it will be like the <a href="https://launchpad.net/drizzle">Drizzle project</a>.<br /><br />I hope you see you there!<br /><br />P.S. You can register <a href="http://www.mysql.com/news-and-events/web-seminars/display-306.html">here</a>Anonymoushttp://www.blogger.com/profile/16354403712237460832noreply@blogger.com0