PlanetMysql.ru — информация о СУБД MySQL

Introduction
Debugging SQL code is a very important task these days. Unfortunately not all database servers support this feature. And MySQL is one of such servers. In spite of its wide feature set, debugging is not implemented in it. So the problem is that MySQL provides no API functionalities for code debugging. But the necessity to debug SQL code does not disappear and becomes more and more important each year. Some companies that develop tools to access data of MySQL server databases create their own debuggers that solve this problem to some extent.

Types of Debuggers
Now we will consider the types of debuggers that can be implemented for MySQL server and principles of their work.

The first way of debugging stored procedures is to divide their code to separate statements and send each statement separately. This way execution is being emulated, because, for example, the same cycles or branches will be executed not on the server side, but by a client itself. And this brings the accuracy of the debugger work into question.

The second way of debugging stored procedures is that additional control items are placed near every statement. And with the help of these items the procedure execution can be suspended, but in spite of all this the procedure is executed entirely on the server side.

Advantages and Shortcomings of the Debugging Methods
The first method has its advantages and shortcomings. The great advantage is that no additional code is executed at the server side and the code of the procedure is left unchanged. The shortcoming of this method is that the main amount of code is executed on the client side and not on the server side, and that is an emulation of code execution, but not code execution itself. Because of this the result returned after debugging often differs from the result returned by the server after executing the procedure. And this situation is worsened by the fact that the result of code execution can differ depending on the MySQL server version. For example, in MySQL 4.1 “NOT” and “!” operators have identical priority, but in the version 5.0 and higher the priority is different, and it is impossible to take into account all such nuances. That’s why such debugger will return the same result on different server versions, because it will not take into account all refinements of code execution on different servers, and instead will interpret the code on its own.

The advantage of the second method is that the procedure is executed on the server completely, and this guarantees 100% correct result of debugging. Client does not interpret anything and all execution process is controlled by server. That’s why problems associated with different MySQL server versions are not topical for this kind of debugger. But one should “pay” for the high quality of such debugger, because its main shortcoming is that extra operations should be performed to add control items into the procedure body, even in spite of the fact that these items can be easily added and easily deleted.

Conclusion
One should decide on his or her own what kind of debugger is better for him or her. dbForge Studio for MySQL could not allow a user to get wrong results during debugging and that’s why it chose the second method and created a server side debugger. It sacrificed the convenience to some extent, but gained a great advantage in the accuracy of the debugging results.

Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV > call storedprocblahbla(‘I’,'am’,'going’,'crazy’);
ERROR 1052 (23000): Column ‘state_of_mind’ in field list is ambiguous

Sure thats REALLY REALLY helpful thanks :), you know what, lets just mysqldump -d -R -B db1 db2 db3 db4 db5 > /wherever/you/like and grep for column ‘state_of_mind’.

I love a challenge but facing migration of so many stored procs I’d really love to have something “GOOD” to debug them with.

The solutions I found were:
1. Illatis eclipse plugin for $40
works on linux, mac and windows but does show me a lot of:
“An error has occurred when activating this view
com/illatis/parser/lib/k”
2. mydebugger for $50
works on linux and mac via wine
3. dbForge for MySQL for $50
works only on windows due to .net framework (kinda crap because I `hate` using the former)

any other ideas are very welcome, especially opensource ones but I can’t find anything.

I can always try setting up debugging using GDB but would rather try to avoid that.

I had an interesting IRC discussion the other day with Monty Taylor about what turned out to be a limitation in Valgrind with respect to debugging memory leaks in dynamically loaded plugins.

Monty Taylor's original problem was with Drizzle, but as it turns out, it is common to all of the MySQL-derived code bases. When there is a memory leak from an allocation in a dynamically loaded plugin, Valgrind will detect the leak, but the part of the stack trace that is within the plugin shows up as an unhelpful three question marks "???":

==1287== 400 bytes in 4 blocks are definitely lost in loss record 5 of 8
==1287==    at 0x4C22FAB: malloc (vg_replace_malloc.c:207)
==1287==    by 0x126A2186: ???
==1287==    by 0x7C8E01: ha_initialize_handlerton(st_plugin_int*) (handler.cc:429)
==1287==    by 0x88ADD6: plugin_initialize(st_plugin_int*) (sql_plugin.cc:1033)

After trying a couple of things, we found that this is a known limitation in Valgrind in relation to code that is loaded with dlopen() and later unloaded with dlclose():

The basic problem is that Valgrind records the location of the malloc() call as just a memory address. And when the memory leak check is performed after the end of program execution, the plugin has been unloaded with dlclose(), and the recorded memory address is therefore no longer valid.

The problem is specific to memory leak checks, which are done only after the code has been unloaded. Other checks (like use of uninitialised values and use-after-free) work fine with full information in the stack traces, as such checks are done while the plugin code is still loaded into memory. But the memory leak checks are arguably among the most useful cheks Valgrind does, as Valgrind is often the only way to find and fix critical memory leaks efficiently.

Fortunately, once the issue was understood, we had an easy work-around: disable the dlclose() call in the server plugin code, and the leak is then detected with full information in the stack trace. Unfortunately this introduces a leak of its own, since now the memory allocated in dlopen() is never freed, so we get another spurious Valgrind memory leak warning.

Another possible way to get the same effect is to pass the RTLD_NODELETE flag to dlopen() to achieve the same effect, though I did not try this yet.

A possibly better work-around (which I also did not try yet) is one suggested in the above referenced Valgrind feature request. By adding the offending plugin(s) as LD_PRELOAD when starting the server, the plugin code will not actually be unloaded in dlclose(), so stack traces should be available without any spurious leak warnings from Valgrind. However, this will not work well if some of the dynamic plugins need a particular load order (according to the suggestion in the feature request). I also need to check if this actually works for plugins (like storage engines) that has link dependencies to symbols in the main program. But it might be a good option if it can be made to work.

(At first I was surprised to learn that this was a problem in MySQL and MariaDB, as I never saw it before. But I suppose the reason is that we so far have built most plugins as built-in, rather than as dynamically loaded .so files. The problem is likely to occur more frequently as we are moving to do more and more with plugins in MariaDB, so it is nice to know a work-around. Thanks, Monty!)

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

I have now published the Buildbot configuration file that we use for our continuous integration tests in our Buildbot setup. Every push into main and development branches of MariaDB is built and tested on a range of platforms to catch and fix any problems early (and we also test MySQL releases before merging to easily see whether any new problems already existed in MySQL or were introduced by something specific to MariaDB).

The configuration is included in the Tools for MariaDB Launchpad project.

Now, the Buildbot configuration file is not something that most MariaDB users will need or want to care about, of course. But I think it is still very important to have it publicly available, not sitting on some private server of the company Monty Program AB.

The reason is that the whole idea with MariaDB is to make a community branch of MySQL, developed by the community and for the community. We want MariaDB the project to be bigger than Monty Program AB the company. And since the Buildbot testing is so central to the whole MariaDB development process, the Buildbot setup also needs to be available for the community. Want to improve the setup, just see what it is doing, or even set up your own master to show you can do a better job (and yes our Windows setup currently really suck)? Just go ahead! Wondering how the Buildbot setup can be continued if Monty Program AB disappears or turns fascist? Now there is an answer.

Hopefully the configuration can also be useful as an example for people doing fancy things with Buildbot. There is some cool stuff in there. Like creating a source tarball on a linux host, and uploading it to be built on a Windows host (this is how releases are done, so important to check that no files are missing from the source tarball). Another cool thing is the builders that boots op KVM virtual machines on demand to build and test binary packages (.deb, .rpm, and .tar.gz) on all of the 18 Linux platforms we currently release for.

BTW, you do not get the miscellaneous passwords in the published configuration file, sorry! :-)

[The license for the configuration file (which is in fact a sizable Python script, as this is the way Buildbot is configured) is GPL.]

I have now published the Buildbot configuration file that we use for our continuous integration tests in our Buildbot setup. Every push into main and development branches of MariaDB is built and tested on a range of platforms to catch and fix any problems early (and we also test MySQL releases before merging to easily see whether any new problems already existed in MySQL or were introduced by something specific to MariaDB).

The configuration is included in the Tools for MariaDB Launchpad project.

Now, the Buildbot configuration file is not something that most MariaDB users will need or want to care about, of course. But I think it is still very important to have it publicly available, not sitting on some private server of the company Monty Program AB.

The reason is that the whole idea with MariaDB is to make a community branch of MySQL, developed by the community and for the community. We want MariaDB the project to be bigger than Monty Program AB the company. And since the Buildbot testing is so central to the whole MariaDB development process, the Buildbot setup also needs to be available for the community. Want to improve the setup, just see what it is doing, or even set up your own master to show you can do a better job (and yes our Windows setup currently really suck)? Just go ahead! Wondering how the Buildbot setup can be continued if Monty Program AB disappears or turns fascist? Now there is an answer.

Hopefully the configuration can also be useful as an example for people doing fancy things with Buildbot. There is some cool stuff in there. Like creating a source tarball on a linux host, and uploading it to be built on a Windows host (this is how releases are done, so important to check that no files are missing from the source tarball). Another cool thing is the builders that boots op KVM virtual machines on demand to build and test binary packages (.deb, .rpm, and .tar.gz) on all of the 18 Linux platforms we currently release for.

BTW, you do not get the miscellaneous passwords in the published configuration file, sorry! :-)

[The license for the configuration file (which is in fact a sizable Python script, as this is the way Buildbot is configured) is GPL.]

[wizard]$ gdb --args /Applications/drizzle/sbin/drizzled --port=3306 --basedir=/Applications/drizzle --datadir=/Applications/drizzle/var

(gdb)break drizzled/set_var.cc:744

(gdb)break set_var.cc:744

(gdb)break plugin/logging_gearman/logging_gearman.cc:150

(gdb)run

. done
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
090827 23:45:16  InnoDB: highest supported file format is Barracuda.
090827 23:45:16 InnoDB Plugin 1.0.3 started; log sequence number 46439
Listening on :::3306
Listening on 0.0.0.0:3306
/Applications/mysql/enterprise/drizzle/sbin/drizzled: ready for connections.
Version: '2009.08.1124' Source distribution (bug-fixes)

set group_concat_max_len = 4;

bool sys_var_session_uint64_t::update(Session *session, set_var *var)
{
uint64_t tmp= var->save_result.uint64_t_value;

if (tmp > max_system_variables.*offset) // <-- this was line 744 

  throw_bounds_warning(session, true, true, getName(), (int64_t) tmp);

  tmp= max_system_variables.*offset;

if (option_limits)

  tmp= fix_unsigned(session, tmp, option_limits);

if (var->type == OPT_GLOBAL)
{
/* Lock is needed to make things safe on 32 bit systems */
pthread_mutex_lock(&LOCK_global_system_variables);
global_system_variables.*offset= (uint64_t) tmp;
pthread_mutex_unlock(&LOCK_global_system_variables);
}
else
session->variables.*offset= (uint64_t) tmp;
return 0;
}

Breakpoint 1, sys_var_session_uint64_t::update (this=0x4dec60, session=0x1044e00, var=0x10470c8) at set_var.cc:744
744       if (tmp > max_system_variables.*offset)

(gdb) print tmp
$4 = 4

(gdb) step
746         tmp= max_system_variables.*offset;

(gdb) print tmp
$5 = 4
(gdb) step
748       if (option_limits)
(gdb) print tmp
$6 = 4294967295

(gdb)cont

drizzle> show variables like "group_concat_max_len";
+----------------------+------------+
| Variable_name | Value |
+----------------------+------------+
| group_concat_max_len | 4294967295 |
+----------------------+------------+
1 row in set (0.01 sec)