Open Query blog

You take a look at someone’s MySQL (or MariaDB) data directory, and see mysql foo bar -> foo What’s the issue? Identify pattern. What does it mean?  Consequences. Is there any way it can be safe and useful/usable? Describe. Good luck!

Filed under: Good practice / Bad practice | by arjen Social tagging: database > datadir > mariadb > mysql > symbolic link > symlink
3 Comments »

So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table! [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: COUNT > index scan > InnoDB > mariadb > MyISAM > mysql > pbxt > reporting
4 Comments »

What’s the difference between INT(2) and INT(20) ? Not a lot. It’s about output formatting, which you’ll never encounter when talking with the server through an API (like you do from most app languages). The confusion stems from the fact that with CHAR(n) and VARCHAR(n), the (n) signifies the length or maximum length of that [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: birthdate > data types > database > date-of-birth > int > integer > mariadb > mysql > year
No Comments »

We don’t often see this option configured (default: unlimited) but it might be a good idea to set it. What it does is limit the amount of disk space the combined relay logs are allowed to take up. A slave’s IO_Thread reads from the master and puts the events into the relay log; the slave’s [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: mariadb > mysql > relay-log-space-limit > replication
No Comments »

Some guidelines to choosing between MySQL’s numeric types, using longitude and latitude as a modelling example.

Filed under: Good practice / Bad practice | by toby Social tagging: accuracy > decimal > double > float > integer > latitude > longitude > mariadb > modelling > mysql > numeric > precision > sql
No Comments »

We got good responses to the “identify this query profile” question. Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”. See the responses and my last note on it below the original post. Got a new one for [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: master > mysql > open query > replication > slave > trivia
5 Comments »

You do SHOW PROCESSLIST, and you see one of your web apps issue the following query:  SELECT … WHERE … AND 1=2 UNION SELECT … What does this tell you, and what do you do next?

Filed under: Good practice / Bad practice | by arjen Social tagging: mysql
6 Comments »

This is a “dogfood” type story (see below for explanation of the term)… Open Query has ideas on resilient architecture which it teaches (training) and recommends (consulting, support) to clients and the general public (blog, conferences, user group talks). Like many other businesses, when we first started we set up our infrastructure quickly and on [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: dogfood > failover > haproxy > lighttpd > Linode > mmm > mysql > open query > redundancy > resilience > zimbra
No Comments »

Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices.

Filed under: Good practice / Bad practice | by toby Social tagging: backup > bug > crash > expire_logs_days > mysql > Open Query > repication > segfault > segmentation fault > strack trace
5 Comments »

In today’s gp/bp an open door will be kicked in: take your backups offsite! I was actually tempted to create a poll to see how many of you do not have proper backups, and how many of you do not take those backups offsite. It is a simple piece of advice and relatively simple to [...]

Filed under: Good practice / Bad practice | by walter Social tagging: backup > mysql > offsite
3 Comments »