X

Migration

I am leaving Oracle. My last day will be tomorrow. Since Oracle blogs are for Oracle employees only I would not be able to post here. I will write new posts to my personal just created blog http://troubleshootingfreak.blogspot.com/ This blog will stay, so you should be able to access older entries. Therefore I would not migrate them. Just don't expect somebody will manage comments. If you have questions about old entries in this blog use this page. Last years I worked on JSON...

Thursday, March 12, 2015 | MySQL | Read More

JSON UDF functions version 0.4.0 have been released

New version of JSON UDF functions has been just released. This version introduces two new features. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::' Second feature is much bigger. Now JSON functions...

Saturday, March 7, 2015 | MySQL | Read More

BACKUP SERVER command for MySQL with Query Rewrite Pre-Parse Plugin

Few times I heard from MySQL users, customers and Sales consultants about their wish to have SQL command, allowing to backup MySQL server. I asked why calling mysqbackup or mysqldump does not work for them and these people had reasons. One of them is security: this way you don't need to give shell access to MySQL server for the person who is supposed to do backup.MySQL does not have such a command, except for MySQL Cluster. There are instead multiple programs which can make...

Monday, February 9, 2015 | MySQL | Read More

MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas

At the last OOW MySQL Plugin for Oracle Enterprise Manager (OEM) was recognized as most popular MySQL product. If you don't have OEM installed, but want to test the plugin you can download OEM virtual box template. But, althought this is the easiest way to get started, you still need to make few additions. At least I had to do them when deployed such installation for MySQL Support Team. Here they are. I prefer to use command line when possible.0. Import virtual machine image...

Wednesday, December 31, 2014 | MySQL | Read More

Memory summary tables in Performance Schema in MySQL 5.7

One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and corresponding views in sys schema And as troubleshooting freak I have huge reason to greet this feature. Before version 5.7 we had very limited abilities to diagnose memory issues in MySQL. We could use operating system tools, such as vmstat, top, free, but they only showed what MySQL server uses memory, but do not show how. In version 5.7 things changed.Lets examine what can we study...

Wednesday, December 17, 2014 | MySQL | Read More

Performance Schema memory tables and rightless users

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of...

Thursday, December 11, 2014 | MySQL | Read More

JSON UDF functions version 0.3.2 have been released

Today new version of JSON UDF functions: 0.3.2 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website. What is new? New function `JSON_COUNT` added. This function returns number of children of the key path specified. If no key path specified, number of children of the root element is returned. Bug #70580/17584692 ADD FUNCTION `JSON_COUNT` TO COUNT SIZE OF JSON DOCUMENT mysql> select json_count('{"MySQL...

Thursday, September 25, 2014 | MySQL | Read More

JSON UDF functions version 0.3.1 have been released.

Today new version of JSON UDF functions: 0.3.1 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website. What is new? Default value for compile option `WITH_PCRE` now is `bundled` independently from the platform (Bug #71265/18081332 Library name mismatch for PCRE on Ubuntu) New values, passed to functions `JSON_APPEND`, `JSON_REPLACE`, `JSON_SET` as numbers, empty strings or `NULL`s are converted to...

Thursday, March 27, 2014 | MySQL | Read More

Why is important to be active at bugs.mysql.com?

When I presented JSON UDF functions at MySQL Connect last year attendees asked me to implement few features. I quickly wrote their wishes in my notepad. I also created feature requests at bugs.mysql.com  when I was home. During following months I fixed bugs in the functions and implemented feature requests, including those which I got from MySQL Connect. I started from most important and affecting users, such as wrong results bugs of features for which I got more than one...

Wednesday, March 19, 2014 | MySQL | Read More

Why MySQL engineers open bugs in public bug database?

Oracle engineers suppose to open new bugs in its internal bug database until they think opening them in public one makes sense. Example of such a case is Bug #68415 "resolveip and mysqlaccess still use gethostbyaddr" Reason for making it public is that it describes behavior, which was introduced into the tools resolveip and mysqlaccess without intention and they now still can work with NetBIOS name, different or not existent in DNS while MySQL server cannot. Interesting fact...

Saturday, March 15, 2014 | MySQL | Read More

Translated slides from my seminar about using Performance Schema for MySQL troubleshooting at Devconf 2013

Few weeks ago I asked my friends who speak both English and Russian if it is worth translating slides about Performance Schema which I prepared for a seminar at Devconf 2013. They said it is. Today I finished translation and uploaded slides to SlideShare.Strictly speaking simple translation of slides is not enough, because they were created for the seminar where I was going to explain what they mean. I think I need to repeat same seminar, this time in English language. But...

Saturday, March 1, 2014 | MySQL | Read More

JSON UDF functions version 0.2.2 have been released.

New version of JSON UDF functions 0.2.2 have been just released. It is last maintenance release of 0.2 series. However it contains two new features:JSON_VALID now accepts array as a root element of the JSON document (Bug#70567/17583282) JSON functions can now be installed and uninstalled using a script (Bug#71263/18022788 Easy installation sql script). This feature is Community contribution. Thank you, Daniel van Eeden! This release also contains following bug fixes:71050/1788...

Wednesday, January 29, 2014 | MySQL | Read More

MySQL and PostgreSQL JSON functions: do they differ much?

As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.Note: all PostgreSQL examples were taken from PostgreSQL documentation.First topic is syntax sugar.  PostgreSQL MySQL Operator -> postgres=# select '[1,2,3]'::json->2;  ?column?----------...

Thursday, January 23, 2014 | MySQL | Read More

JSON UDF functions version 0.3.0 have been released.

Today new version of JSON UDF functions: 0.3.0 was released. This is major release which contains new functionality. You can download functions from the MySQL Labs website. What was added? Functions JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET now accept both string and integer arguments as array indexes. (Bug #70393/17491709) Now syntax JSON_EXTRACT(doc, 'key', 1); is accepted. New function JSON_SAFE_MERGE checks JSON documents for...

Tuesday, December 31, 2013 | MySQL | Read More

JSON UDFs: is it hard to type keys?

Currently, if you want to search a key using JSON UDFs you have to specify each its part as a separate argument to the function: JSON_CONTAINS_KEY(doc, 'root', 'child', 'child of child', 'child of child of child', etc.....). This way of working with parameters is easy for developer, less error-prone, but can be not very beautiful. I was suggested by some of users to change it to '/parent/child/child-of-child/...' or to 'parent:child:child-of-child:...' There are, probably,...

Tuesday, December 17, 2013 | MySQL | Read More

New cmake options for JSON UDFs.

Two weeks ago I announced new maintenance release of JSON UDFs: 0.2.1. It not only contains bug fixes, how you can expect from a maintenance release, but also contains improvements in build and test scripts.First improvement is the easier way to build JSON UDFs on Windows. In the first version building on Windows was a pane: you had to build PCRE library, copy four files to directories where Visual Studio can find them and only then build JSON functions themselves. Now you...

Wednesday, December 11, 2013 | MySQL | Read More

Last element for JSON array: what do you think?

After I released maintenance release of JSON UDFs last week it is time to think about which features I should implement in upcoming major version. Many users asked me about the possibility to explicitly specify if they want to append last element to a JSON array. This feature can be made for two functions: json_append and json_set. I have four ideas of how to implement this. All have pros and contras.Create new function called json_append_last which will work exactly like jso...

Wednesday, December 4, 2013 | MySQL | Read More

JSON UDF functions version 0.2.1 have been released.

Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build ans test procedures. As usual, you can download source and binary packages at MySQL Labs. Binary packages were build for MySQL server 5.6.14. If you want to use other version of the server, you need to recompile functions. What was changed? Let me quote the ChangeLog.Functionality...

Thursday, November 28, 2013 | MySQL | Read More

To be safe or to be fast?

When I designed first version of JSON UDFs which was reviewed only internally, I let all functions to validate input and output JSON. But my colleagues told me to remove this functionality, because it makes such functions, as json_search, json_replace or json_contains_key deadly slow if they find the occurrence in the beginning of the long document. And first published version of JSON UDFs: 0.2.0 has not this functionality. What we expected is that users would call json_valid ...

Wednesday, November 27, 2013 | MySQL | Read More

Late feedback

MySQL Community team asked me to write about Devconf 2013 few months ago. Conference was in June, 2013, but I remembered about this my promise only now: month later after my participating in MySQL Connect and Expert Troubleshooting seminar (change country to United Kingdom if you see blank page). I think it is too late for the feedback, but I still have few thoughts which I want to record.DevConf (former PHPConf) always was a place where I tried new topics. At first, because...

Tuesday, November 5, 2013 | MySQL | Read More

JSON UDFs have own bugs.mysql.com category

JSON UDFs got own category at MySQL Bugs Database: "Server: JSON UDF" Use this category to post new bug reports and vote for existent.

Wednesday, October 30, 2013 | MySQL | Read More

JSON UDFs first feedback

Yesterday Ulf Wendel created great blog post with deep analysis of JSON UDF functions which I presented at MySQL Connect at September, 21. Ulf found few bugs in these functions, I reported them all at bugs.mysql.com. You can find numbers in my comment to his blog post. But he also raised concerns, which can not be considered pure bugs, rather feature requests, or even design especiallities.* First concern, of course, is the documentation. Ulf writes: "Here’s what the README,...

Wednesday, October 9, 2013 | MySQL | Read More

Vote for bugs which impact you!

Matt Lord already announced this change, but I am so happy, so want to repeat. MySQL Community Bugs Database Team introduced new button "Affects Me". After you click this button, counter, assigned to each of bug reports, will increase by one. This means we: MySQL Support and Engineering, - will see how many users are affected by the bug. Why is this important? We have always considered community input as we prioritize bug fixes, and this is one more point of reference for us....

Thursday, June 27, 2013 | MySQL | Read More

Yet another UDF tutorial

Some time ago I wrote a blog post describing a way I use to verify MySQL Server bugs. But my job consists not only of bugs which can be verified just by passing SQL queries to the server. One of such examples is UDF bugs.MySQL User Reference Manual is good source of information for those who want to write UDF functions, as well as book "MySQL 5.1 Plugin Development" by  Sergei Golubchik and Andrew Hutchings. But while the book describes in details how to write UDFs it was...

Saturday, April 13, 2013 | MySQL | Read More

Troubleshooting Performance Diagrams

Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose: either make full-day seminar and explain people every basic of performance, or rely on their knowledge and make some one and half hours seminar. I prefer short speeches, so I considered latter. But even with such a mature audience you don't always know if they knew some or another...

Tuesday, January 29, 2013 | MySQL | Read More

My way to verify MySQL bug reports

I promised to write this blog post long time ago at one of conferences in Russia. Don't know why I delayed this, but finally I did. We, members of MySQL bugs verification group, have to verify bugs in all currently supported versions. We use not only version reported, but test in development source tree for each of supported major versions and identify recent regressions.You can imagine that even if I would do so for simple bug report about wrong results with perfect test...

Saturday, January 26, 2013 | MySQL | Read More

My eighteen MySQL 5.6 favorite troubleshooting improvements

MySQL 5.6 is in RC state now which means it is going to be GA sooner or later. This release contains a lot of improvements. However, since I am a support engineer, I most amazed by those which make troubleshooting easier. So here is the list of my favorite troubleshooting improvements. 1. EXPLAIN for UPDATE/INSERT/DELETE. This is extremely useful feature.Although prior version 5.6 we, theoretically, could have some kind of explain for them too, for example, if convert DML...

Friday, January 11, 2013 | MySQL | Read More

Slides for MySQL Connect session "Managing and Troubleshooting MySQL for Oracle DBAs"

I just uploaded presentation for session "Managing and Troubleshooting MySQL for Oracle DBAs" here. I also created a guide of EXPLAIN features (URL, which I forgot to put on slide). Here it is.

Monday, October 1, 2012 | MySQL | Read More

That's all about nuances

When I sent a proposal for session "Managing and Troubleshooting MySQL for Oracle DBAs" to MySQL Connect conference org committee it had not any mention of Oracle in its name, but later I was asked to provide more details for former Oracle DBAs who want to use MySQL. I was fast and I said "yes".So my original aim to teach people to troubleshoot MySQL changed to teaching of how different is MySQL from Oracle in troubleshooting aspects. Although both RDBMs have very much in...

Tuesday, September 18, 2012 | MySQL | Read More

Performance Schema in version 5.6 helps to prevent SQL Injection attacks

There were few prominent SQL Injection attacks lately. Such breakages could be possible when an attacker finds that user data, sent to a SQL server, not properly sanitized. Usually successful attack is preceded by queries which cause parse errors. mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/er'ror;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for...

Friday, July 13, 2012 | MySQL | Read More

Warning and error information in stored procedures revisited

Originally way to handle warnings and errors in MySQL stored routine was designed as follows:if warning was generated during stored routine execution which has a handler for such a warning/error, MySQL remembered the handler, ignored the warning and continued execution after routine is executed MySQL checked if there is a remembered handler and activated if any This logic was not ideal and causes several problems, particularly:it was not possible to choose right handler for an...

Wednesday, July 4, 2012 | MySQL | Read More

Story of success: MySQL Enterprise Backup (MEB) was successfully integrated with IBM Tivoli Storage Manager (TSM) via System Backup to Tape (SBT) interface.

Since version 3.6 MEB supports backups to tape through the SBT interface. The officially supported tool for such backups to tape is Oracle Secure Backup (OSB). But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.This month...

Friday, March 23, 2012 | MySQL | Read More

InnoDB Recovery forces

I just answered in yet another bug report where user experiences problem because OS crash damaged InnoDB tablespace. I wonder why users don't use InnoDB recovery. Looks like I need to create an entry in Domas-style: IF YOUR TABLESPACE IS CORRUPTED TRY FORCING INNODB RECOVERY http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Thursday, April 28, 2011 | MySQL | Read More

Warning and error information in stored procedures

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures. I got this question second time this week, so I think it is worth writing a blog post about this topic. Question was "Why I get error in EVENT which calls a STORED PROCEDURE which already has HANDLER?" This changed in version 5.5 after fix of Bug #36185, Bug #5889, Bug #9857 and Bug #23032. Explanation is in this commit: The...

Thursday, April 28, 2011 | MySQL | Read More

From Collaborate 11: Diagram of actions which should be performed when error found.

This year I was speaking at Collaborate 11 about actions one need to perform when find her SQL application behaves wrongly. New thing which was not presented in my earlier notes is simple how-to of how to deal with replication errors. You can find this in second presentation. Slides are here: part 1 and part 2. I also created PDF diagram which can be print and used as visual aid for cases when you meet an error. You can download it here or at the Conference website here (see...

Wednesday, April 20, 2011 | MySQL | Read More

Spring saving time: watch your TIMESTAMPs

Every March we have a lot of bug reports and support issues about Daylight Saving Time changes. General rule here is to remember 1 hour at March, 27 night will be missed in most of European countries: for example in Moscow after 01:59:59 next time will be 03:00:00. And yes, I am too late for America =) MySQL handles such timestamps properly, therefore expect no such time and date will be inserted in STRICT mode and next valid date will be inserted for not existent time in...

Tuesday, March 22, 2011 | MySQL | Read More

Translation of "Methods for searching errors in SQL application" just finished

Translation of "Methods for searching errors in SQL application" just finished, epilogue is at http://sql-error.microbecal.com/en/concl.html It contains list of methods which had been discussed. EpilogueFinally I'd like to repeat methods which we discussed. Unfortunately there are several problems left. I will be glad to know your opinion about what else to descuss. I will be waiting your notes at sveta_dot_smirnova_at_oracle_dot_com or sveta_at_js-client_dot_com List of...

Thursday, October 21, 2010 | MySQL | Read More

Translation of "Appendix. Methods of copying and moving of MySQL databases." of "Methods for searching errors in SQL application" just published

Translation of appendix about methods of copying and moving MySQL databases just published. This is just short overview of possible methods and does not pretend to be detailed guide. It starts as: Appendix. Methods of copying and moving of MySQL databases.In this application I'd like to shortly discuss general methods of backup and moving of mySQL databases. Easier and recommended way of data moving is mysqldump utility. You can copy data with help of following command: $mysqldu...

Wednesday, October 13, 2010 | MySQL | Read More

Translation of Summary of Part 4 of "Methods for searching errors in SQL application" just published

Translation of summary of last part, "Techniques, used for debugging of Production applications" just published. This is almost end, only appendix about backup techniques and epilogue left. Summary.In the last chapter we discussed methods of testing problems which can happen only on production server. Lets repeat them: Method #25: if something unexpected happens check error log first.Method #26: turn InnoDB Monitor to on to have information about all InnoDB transactions in the...

Saturday, September 25, 2010 | MySQL | Read More

Translation of "Chapter 11. Techniques, used for debugging of Production applications" of "Methods for searching errors in SQL application" just published.

English translation of first and only chapter of last part 4 "Techniques, used for debugging of Production applications" just published. Chapter 11. Techniques, used for debugging of Production applications.Unfortunately is not always possible to find error while testing. Often they only happen when high load. How do you know about such problems? One of most important sources of information about problems is errorlog file. There you can find information such as...

Tuesday, September 14, 2010 | MySQL | Read More

Translation of Summary of Part 3 of "Methods for searching errors in SQL application" just published

Not much new this time: just summary of part 3 published and fixed mistake in chapter 10 (thanks, Shane!).Summary. In the third part we discussed methods of application debugging in cases when query plays secondary role in the problem.I'd like to bring your attention we only discussed most frequent cases while MySQL server has a lot of parameters which of them can affect application. Analyze parameters which you use. One of the methods is run problematic query using MySQL...

Wednesday, September 1, 2010 | MySQL | Read More

Translation of "Chapter 10. Lost connection to MySQL server during query." of "Methods for searching errors in SQL application" just published.

This chapter is about possible reasons of "Lost connection to MySQL server" error not discussed in previous one. Chapter 10. Lost connection to MySQL server during queryYou can see error "Lost connection to MySQL server" not only because too small connect_timeout, but because other reasons too. In this chapter we discuss these reasons. $php phpconf2009_4.php string(44) "Lost connection to MySQL server during query" Most likely error log will show what happened: ... Rest of the...

Wednesday, August 11, 2010 | MySQL | Read More

Translation of "Chapter 9. Misterious vanishing of the server." of "Methods for searching errors in SQL application" just published

In this chapter we discuss timeouts which can lead to "MySQL server has gone away" and "Lost connection to MySQL server" errors.  Extract here is short, because there are a lot of code quotes which I can not properly format here. Chapter 9. Misterious vanishing of the server. Often this looks like: $php phpconf2009_3.php string(26) "MySQL server has gone away" Code: ... Rest of the chapter is here

Tuesday, August 3, 2010 | MySQL | Read More

Translation of "Chapter 8. Large amount of data." of "Methods for searching errors in SQL application" just published

I started translation of Part 3 "Other cases". In this part I mostly describe how wrong settings can lead to misterious errors. First chapter in this part about large amount of data. Part 3. Other cases. Chapter 8. Large amount of data.There are cases when query is just symptom of wrong behavior, but true reason is wrong settings. One of the cases is too small max_allowed_packet for data sent. MySQL server variable max_allowed_packet defines maximum possible amount ofdata...

Wednesday, July 21, 2010 | MySQL | Read More

Translation of "Chapter 7. Concurrent transactions. " of "Methods for searching errors in SQL application" just published.

This is short chapter which gives some clue what to do with concurrent transactions. Chapter 7. Concurrent transactions.Yet another frequent example of similar problem is "Lock wait timeout exceeded" error while you use InnoDB tables. Most often running query SHOW ENGINE INNODB STATUS is enough to find the problem, because it will show last transactions. But output of this command does not contain information about all queries in the same transaction, but onlyabout last one....

Wednesday, July 14, 2010 | MySQL | Read More

Translation of "Chapter 6. Locks and deadlocks." of "Methods for searching errors in SQL application" just published.

This is new part which contains information about what to do if problem is repeatable only when queries run concurrently. Chapter 6. Locks and deadlocks.In the last part we discussed how to find cause of the problem in case if it is always repeatable. But there are cases when problem occurs only under particular circumstances. For example, such easy query can run long enough: mysql> select \* from t; +-----+ | a   | +-----+ |   0 | | 256 | +-----+2 rows in set (3 min 18.71...

Wednesday, July 7, 2010 | MySQL | Read More

Translation of Summary of Part 1 of "Methods for searching errors in SQL application" just published

There is not much new content this week. Just summary of what was written before. Summary.In the summary of this part I'd want to recommend following: Try to find a query which causes inconsistent behavior Use log files: General query log Log files in your application MySQL Proxy or any other suitable proxy Others Analyze what is wrong, then solve the problem using your findings Below I place a list of methods which we studied in the first part. Method #1: use output operator to...

Wednesday, June 30, 2010 | MySQL | Read More

"Alternate methods for finding problem query.": chapter 5 of "Methods for searching errors in SQL application" just published

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like: Chapter 5. Alternate methods for finding problem query.I already wrote about using of general query log requires resources. Part of the problem can be solved if use new feature of MySQL 5.1: online logging which allows to turn general query log to on or to off without stopping the MySQL server. Unfortunately this doesn't always work: you can have oldversion of...

Wednesday, June 23, 2010 | MySQL | Read More

Translation of Chapter 4. Miscellaneous. of "Methods for searching errors in SQL application" just published

This chapter starts as: Chapter 4. Miscellaneous.There are cases when wrong output is just symptom of wrong input made before. For example, you start to receive wrong data at particular step of the scenario. After analysis of the SELECT query (or queries) is clear what queries are correct and return exactly same data which exists in the table (or tables). This means wrong data was inserted at the earlier step. How to know when it happened? Start from the step of the scenario...

Wednesday, June 16, 2010 | MySQL | Read More

Chapter 3 of "Methods for searching errors in SQL application" has been published

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.  This is short chapter which discuss single method of dealing with mysterious DML query. Chapter 3. Wrong data in database or what to do with problem DML query. Problems with wrong data happens not only with SELECT queries like cases we discussed in chapter 2, but in cases of querires which modify data: DML queries. Lets...

Wednesday, June 9, 2010 | MySQL | Read More

Chapter 2. Why you get wrong data of "Methods for searching errors in SQL application"

Translation of Chapter 2 of Methods for searching errors in SQL application just posted. Chapter 2. Why you get wrong data. We learned how to find the problem query. We already examined 2 examples of syntax error and one example of logic error. But do include these examples every possibilyty of analysing of any problem query? Of course, not! Although, strictly say, every error in the query can be reduced to one of these subtypes. In this chapter we discuss what else can we do...

Tuesday, June 1, 2010 | Personal | Read More

Methods for searching errors in SQL application

Some time ago I wrote in Russian language guide for finding errors in SQL application. To be honest I wrote it having personal aim to have a text which I can easily use refer in case of user questions about how to find particular thing. But this makes less sense having no English version. So now I started to translate it to English and publish. Introduction and first chapter are ready. You can find it at http://sql-error.microbecal.com/en/index.html  Comments and corrections...

Wednesday, May 26, 2010 | Personal | Read More

Presentation from PHPConf 2009

October, 8 I did master-class about catching error in SQL application at PHPConf 2009. Who speaks Russian can get slides here: pdf and odp. There were several things which were interesting for me during my talk.First is interaction with audience of different educational (or better to say MySQL practice) level. I should make a note for feature how to solve moments when 1/3 of audience listens carefully and is just right for the talk, another 1/3 knows things I am talking at...

Wednesday, October 14, 2009 | Personal | Read More

How to raise error in your MySQL application

Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround. Here it is. Create 2 procedures as following:DROP PROCEDURE IF EXISTS raise_application_error; DROP PROCEDURE IF EXISTS get_last_custom_error; DROP TABLE IF EXISTS RAISE_ERROR; DELIMITER $$CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY...

Friday, September 18, 2009 | MySQL | Read More

Why there is not bug fix in GA version

Reporter of bug #44604 pointed to common problem: "Why don't backport bugfix to previous major version?" Especially if this version is GA. This particular bug seems to be fixed by redesign of MySQL Optimizer which was dramatically improved in version 6.0 At the same time while some feature is improved in new version backport this improvement or even part of it into older one is not easy and can lead to instability or affect other applications. There is always balance between...

Monday, June 1, 2009 | MySQL | Read More

Why EXPLAIN runs forever

Sometimes you need to run EXPLAIN on long running queries. Most time EXPLAIN takes few seconds, but sometimes it looks like it executes query itself instead of using statistic. Like in the example following: mysql> explain select \* from (select sleep(10) as foo) bar; +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra         ...

Sunday, May 17, 2009 | MySQL | Read More

Windows installation problem

Lately we saw many complains of how MySQL Config Wizard works. Most of them are just special case of bug #42820 verified some time ago. I though it has trivial workaround, but one of users complained he read a lot of articles related to this bug, but nobody pointed the workaround. So here you are: if your my.ini is broken after update, copy old saved configuration file (it should be file named my.ini.bak or similar in the installation directory) or fix new one manually, then don...

Monday, May 11, 2009 | MySQL | Read More

Why you can not connect to your fresh installed MySQL database.

I read in Russian the talk which I presented at MySQL Conference & Expo this year. Really it was not exactly same, but some parts existed in both. And there was one of my friends who heard the talk in Russian at the conference. He didn't attend English version, but when it finished he came and asked: "Did you say the most important?" "The most important what?" "Why people can not connect to MySQL" So here is most important part of my talk :) And if go through our bugs database...

Wednesday, May 6, 2009 | MySQL | Read More